SQLTeam.com | Weblogs | Forums

What to check if my database stored procedure running slow?

sql2008

#1

what are the basics things I need to check to fix If a stored procedure of my database is running slow ?


#2

I usually do the following:

SET STATISTICS IO ON; SET STATISTICS TIME ON	-- Just Logical Reads and SCANs

BEGIN TRANSACTION
-- ... put EXEC MySProc here ...
ROLLBACK

SET STATISTICS IO OFF; SET STATISTICS TIME OFF; SET STATISTICS PROFILE OFF
GO

then I look at the various table stats (for each SQL statement) to see which ones have a lot of scans / logical reads

Then I rerun the Sproc to see the Query Plan

SET SHOWPLAN_TEXT ON
GO

BEGIN TRANSACTION
-- ... put EXEC MySProc here ...
ROLLBACK

SET SHOWPLAN_TEXT OFF
GO

and I find the query plan(s) associated with the high Scan/Logical Reads. (I cut and paste them to a separate Window as I tend to make changes and then want to refer back to earlier outputs to see if there is any improvement compared to my "baseline"

In the Query Plan I look for unexpected index usage (most commonly the Clustered Index used to find a match on Column-X when I a) would have expected it to use an existing index or b) I slap my forehead!! and realise that I don't have an index for Column-X :slight_smile:

Usually its a bit more complicated than that ...

Once I've got some decent stats I use RECOMPILE on the Sproc and see if that fixes anything. I also check that the STATS are up to date (and indexes defragmented / recently rebuilt) and THEN I set about building indexes / changing code to see if I can improve things. I want to be sure that all the physical stuff is in Best Possible Shape before I get my stopwatch out and start timing any improvements I try)

Most people seem to use the GUI Visual Query Plan but to me that tells me very little - I don't understand how knowing that 98% of the query was in Process-12 is helpful ... and I have to keep hovering over each little box to see what the Raw Data is behind it ... but I expect I'm using the GUI all wrong ... I'm a Very Old Dog after all ...