what are the basics things I need to check to fix If a stored procedure of my database is running slow ?
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
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 ...