Slow running applications and selects
-
We have a 2 node win2000 advanced server/sql sever 2000 cluster that appeared to run inserts, writes and selects extremly slow during our holiday peak season. The servers are dual 700 MHz cpus with 1.5 GB mem. Disk space did not appear to be an issue. We have 12 databases, 2 of which are about 25GB. We also have IBM MQ and messaging engines on the servers, as well as a reporting database. Many applications using these databases ran extremly slow sometimes with two minute response times. One select that normally took less than a second ran a minute. I saw the cpu usage peaking into 100%. We had users also running adhoc reports. Our AD team does not believe it could be inadequate hardware. Any ideas?
-
Adhoc queries are evil!
I suspect that you have locking issues.
How are the adhoc queries being run? Are they being run from Access? We had an issue where someone would open in Access a table linked to a SQL Server table. They would change one record, which locked the table against any other users.
I suspect that your applications may be causing the locking as well. When there are few transactions, they run fine. Then crunch time hits. All of the contention causes the queries to drag.
Could there be problems elsewhere in the network?
Just some ideas. My understanding of SQL Server is that many of the problems that people attribute to hardware problems can be best fixed by fixing the applications. And move the adhoc queries to another server or restrict them altogether during the holidays.
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
-
Thanks, most of the adhocs were ran from Query Analyzer by technical staff. others via reporting somehow, the slower the systems ran, the more the reports were requested.
-
In the system indexes table we found the 14 indexes we created for this table, but also found 70 other names like _WA_Sys_xxxxxxxxxxxx. related to the same table. Could theses be auto generated indexes that have to be inserted as well? Some manuals call them statistics but class them as indexes, any idea if these effect performance?
l -
When you observe ther performance degradation, run sp_who and sp_lock to observe the locks (if any)
Also run a profiler trace during such period to see if any particular query is causing a large i/o or cpu . If so, optimize it.
_WA_Sys_xxxxxxxxxxxxxxxxxxx are the statistics that are used internally by the SQL Query optimizer to retrieve data. Outdated stats could also be a problem for performance degrade.
-- Amit
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply