what might be causing queries to run painfully slow all the sudden?
-
What might be causing queries to run painfully slow all the sudden?
We use third party app that uses sql server 2000. The developers of the application recently made changes and now the queries run very slow. processor in server maxed out. The software developers for the third party app said it was our network traffic , and we needed a faster network. So we improved the network speed but no change in the queries going to the server.
what could be causing this and how maybe to fix it? please help
-
Can you trace the sql statements sent to sql server using sql profiler?
You can then analyze the query plans in the query analyzer (display estimated query plan)
Possible causes:
a query is returning more data than needed (missing where clause, extra columns, using views based on views)
a query is holding locks longer than necessary (especially when someone is using cursors to do the logic instead of a set based solution)
an index is missing causing the optimizer to use a table scan instead of an index
was a clustered index being introduced?
statistics are out of date
...
Can you contact the vendor on what exactly they have changed? Can it be reproduced easily?
-
Do they use SP's or ADHOC SQL? I agree with grabbing data with PROFILER.
IF SP then looks for the ones that changed recently and you can look at ESTIMATED execution in QA.
Has you data volume gone up ? Ran into problem where data volume below X ran fine but when exceed X caused perf problems... Was <> 'D' vs IN (X, Y). The <> started causing scans when the day before was using INDEX. Changed to IN and started using index again....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Not necessarily a fix for this situation, but a good reminder just the same:
After altering a stored procedure, execute with recompile.
[font="Courier New"]ZenDada[/font]
-
Not gonna add much to this discussion .... just a comment about ur supplier .... GET A NEW ONE!? If their first comment is "It's YOUR Network" when the new system is actually maxing out the CPU on the server, they have alot (Too much!) to learn to be a decent software supplier!
Do your profiling and have a run through the execution plans of the sprocs or SQL being run ... shove this back at ur supplier and tell them to sort it out! Jeez ... some companies just give Developers a bad name!?
-
Check to see if they dropped any indexes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply