any general approaches to troubleshooting a sudden long running query?
-
I'll try chasing down some of the Bookmark Lookups and see if I can put a covering index in place to handle them. I was able to remove some of the outer joins, but saw no improvement.
Is it worth taking the time to make sure that every column involved in a join has at least a nonclustered index on it?
here's the statistics:
Application Profile Statistics
Timer resolution (milliseconds)00
Number of INSERT, UPDATE, DELETE statements00
Rows effected by INSERT, UPDATE, DELETE statements00
Number of SELECT statements22.27778
Rows effected by SELECT statements591371.167
Number of user transactions65.94444
Average fetch time00
Cumulative fetch time00
Number of fetches00
Number of open statement handles00
Max number of opened statement handles00
Cumulative number of statement handles00
Network Statistics
Number of server roundtrips34.44444
Number of TDS packets sent34.44444
Number of TDS packets received8959.6667
Number of bytes sent2627707.78
Number of bytes received330699215497
Time Statistics
Cumulative client processing time81.44444
Cumulative wait time on server replies56232.93435e+007
-
oops, maybe you meant these stats:
(521 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'tbCurvePoint'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'tbTransCounterparty'. Scan count 84, logical reads 168, physical reads 0, read-ahead reads 0.
Table 'tbTransBroker'. Scan count 84, logical reads 168, physical reads 0, read-ahead reads 0.
Table 'tbTrans'. Scan count 84, logical reads 169, physical reads 0, read-ahead reads 0.
Table 'tbInstPut'. Scan count 743, logical reads 743, physical reads 0, read-ahead reads 0.
Table 'tbInstIndustrySector'. Scan count 741, logical reads 1482, physical reads 0, read-ahead reads 0.
Table 'tbInstIndustryGroup'. Scan count 741, logical reads 1482, physical reads 0, read-ahead reads 0.
Table 'tbInstIndustrySubgroup'. Scan count 741, logical reads 1486, physical reads 0, read-ahead reads 0.
Table 'tbInstCompany'. Scan count 743, logical reads 1489, physical reads 0, read-ahead reads 0.
Table 'tbInstCurrency'. Scan count 1064, logical reads 2128, physical reads 0, read-ahead reads 0.
Table 'tbInstTranche'. Scan count 1872, logical reads 6390, physical reads 0, read-ahead reads 0.
Table 'tbInst'. Scan count 1872, logical reads 5758, physical reads 0, read-ahead reads 0.
Table 'tbMark'. Scan count 1486, logical reads 4469, physical reads 0, read-ahead reads 0.
Table 'tbRiskManualInstOutput'. Scan count 704, logical reads 3497, physical reads 0, read-ahead reads 0.
Table 'tbMarkInput'. Scan count 1043, logical reads 4855, physical reads 0, read-ahead reads 0.
Table 'tbEnum'. Scan count 1043, logical reads 2086, physical reads 0, read-ahead reads 0.
Table 'tbInstRegion'. Scan count 743, logical reads 1486, physical reads 0, read-ahead reads 0.
Table 'tbBizBusinessUnit'. Scan count 1522, logical reads 3044, physical reads 0, read-ahead reads 0.
Table 'tbBizSubBusinessUnit'. Scan count 1522, logical reads 3044, physical reads 0, read-ahead reads 0.
Table 'tbBizStrategy'. Scan count 1522, logical reads 3058, physical reads 0, read-ahead reads 0.
Table '#5F95008E'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.
-
Yup
. -
ok, now that we have this, can you try to catch the stats of the long execution (or the short one if this is the long running one)?
-
As suggested - use SQL Server Profiler to monitor missing statistics (errors and warnings) while you run the query. My theory is that your statistics could be missing altogether. While you are at it, capture the execution plans of the two variations.
You may want to update the statistics with a full-scan as well to ensure that your statistics are accurate.
It may also help to mark the stored procedure for re-compile after the statistics have been recomputed since this will force SQL Server to create a new query plan from scratch.
In some of the cases where I have seen this happen, it has been necessary to find the correct execution plan (i.e. the one being used when the query executes quickly) using SQL profiler, and use hints to help the Query optmizer do its job efficiently. In most cases where I needed to do this, it was enough to specify which index needed to be used - e.g. (NOLOCK index=my_index).
In summary, having sufficient and accurate statistics often solves these problems, where it doesn't use hints.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply