Query Running Slow
-
I an SP that used to take 5 minutes to run and now its taking 8 minutes to run, this is on my local machine, zero maintance has been done, up until now, not an issue, now it's becoming an issue? Any suggestions on where to start? Any help would be greatly appericated, even if it's pointing me in a different direction.
-
I would recommend that you look at the execution plan for the query and try to determine which tables - if any - are doing scan operations instead of seeks. Once you know this, you can then look at indexing accordingly to reduce the length of time to run the stored proc.
Be wary of the indexes you create, as they do have an effect on the insert of data into the table you create the index on. If the table is not highly transactional, this won't be much of a problem.
That is a very simplistic starting point but is certainly where I would begin.
-
Forgot to mention also that if the stored procdure uses multiple queries to get its results, break it out into query analyser and set some timings between each query.
This way you will know which one is actually slow, rather just not performing as well as it might. For instance, a non-performant query on a couple of small tables is not going to cause you trouble, but the execution plan may lead you to believe you have a problem.
Understanding which query takes the longest allows you to focus on where you will get the best results for the initial effort.
-
Thank you for the advice, I have found the 'problem child' queries and now it's a matter of putting my found skills of indexing and a few other minor things to the test to see how much I can stream line things.
-
A little something for future reference ...
... my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Good point Rudy.
It might be worth explaining to Richard that existing indexes can become a little less useful if the statistics surrounding the data usage get out of date.
SQL Qerver query engine will look at the stats to determine if an index should be used for a given query. If the stats are rubbish, it just might opt for using table/index scans instead of the much faster seek operations.
The indexes themselves get out of sorts as they get more and more data added to them, so rebuilding them optimises the time it takes for query operations to find the data it requires.
Ideally, you would have regular maintenance jobs on your DB that keep the stats and indexes up to date
-
Outstanding list, Rudy... I'd like to bump your rounds down 1 and add a new round 1... "Write scalable code to start with."
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The list has been dutifully uodated Jeff
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I have gotten side tracked a little on a different part of the same project, my question is what will the short list for tuning actually do?
-
I have gotten side tracked a little on a different part of the same project, my question is what will the short list for tuning actually do?
-
It's an outline of steps to take in order to address poorly performing queries and databases.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Review the comments I put in after Rudy added his list, then go to BOL (books on line) and look up anything that is not clear to you. Then if you still have questions, come back to this forum and ask for clarity.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply