Geek Answers Handbook

Periodic slow query on SQL Server 2008

I am developing a system that periodically (4-5 times a day) runs the select statement, which usually takes less than 10 seconds, but periodically takes up to 40 minutes.

The database is in Windows Server 2008 + SQL Server 2008 R2; both are 64bit.

On a machine that works with a database that checks the database and generates values ​​for the required records, there is a service. Then these records are periodically queried using a multiple connection to a table selected from a service on a second computer written in C ++ (VS 2010), using the MFC CRecordset class to retrieve data. The following is an example of the request that causes the problem.

SELECT DISTINCT "JobKeysFrom"."Key" AS "KeyFrom","KeysFrom"."ID" AS "IDFrom",
"KeysFrom"."X" AS "XFrom","KeysFrom"."Y" AS "YFrom","JobKeysTo"."Key" AS "KeyTo",
"KeysTo"."ID" AS "IDTo","KeysTo"."X" AS "XTo","KeysTo"."Y" AS "YTo",
"Matrix"."TimeInSeconds","Matrix"."DistanceInMetres","Matrix"."Calculated"
FROM "JobKeys" AS "JobKeysFrom"
INNER JOIN "JobKeys" AS "JobKeysTo" ON 
("JobKeysFrom"."Key"<>"JobKeysTo"."Key") AND 
("JobKeysFrom"."JobID"=531) AND 
("JobKeysTo"."JobID"=531)
INNER JOIN "Keys" AS "KeysFrom" ON 
("JobKeysFrom"."Key"="KeysFrom"."Key") AND ("JobKeysFrom"."Status"=4)
INNER JOIN "Keys" AS "KeysTo" ON 
("JobKeysTo"."Key"="KeysTo"."Key") AND ("JobKeysTo"."Status"=4)
INNER JOIN "Matrix" AS "Matrix" ON 
("Matrix"."IDFrom"="KeysFrom"."ID") AND ("Matrix"."IDTo"="KeysTo"."ID")
ORDER BY "JobKeysFrom"."Key","JobKeysTo"."Key"

I tried the following

  • checked the indices and they all look right and they are active and used as requested
  • , .
  • , , , , ( 100 ) , .

, 10 . , , , , , , , . 4 16 . , .

, , , , 40 .

, ( 70-100000 ) .

,

+5
2

.

, set , , .

, (, ), . , , , , .

, .

SSMS-, , SET ..., SSMS

,

select p.query_plan, *
from sys.dm_exec_requests r
cross apply sys.dm_exec_query_plan(r.plan_handle) p
where r.session_id = <session_id>

.

, sniffing , OPTIMIZE FOR, .

+3

, , , .

, , , , , , , , () , , .

0

More articles:


All Articles