increasing duration for the same query over time

Question

increasing duration for the same query over time

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

yuwei98 Grasshopper Points: 14 More actions · Answer 1

it might be possible that the same query plan became suboptimal with parameters value change ?

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

I really dont think it's stats or plan related. I was able to recreate the problem on another machine, taking a lot of variables out of the problem. I simply took 10000 instances of the EXEC procname statement and ran it in managment studio against a stripped down version of the db with updated stats. Same thing- duration and reads increased over time. There was no other activity on the box.

I think this has something to do with sp_reset_connection not being issued over such a long duration... perhaps something in memory incrementing or somethign like that.

NJ-DBA SSChampion Points: 13834 More actions · Answer 3

Testing revealed that the primary factor influencing the duration of the “if exists” query is lack of an index with th filename column in the key. Particularly, this column should be the second column in the key.

Durations were not impacted by any of the following factors:

-presence or lack or sp_reset_connection as tested using C# code which uses a single connection (like production) or opens and closes connections (and issues sp_reset_connection)

-permissions of user (sysadmin vs app user with somewhatlimited rights)

-fragementation of indexes (marginally fragmented, realistic vs. just after indexes are rebuilt).

-statistics- (tested by running update statistics with fullscan before, and during runs and recompiling proc).

I understand why the missing index would make it run much slower, but not why it would progressively get worse during a long iterative run of the same proc over and over again. could this come down to work spent updating missing index stats?

yxl1108 SSC Rookie Points: 26 More actions · Answer 4

I have similar issues here in my system, which is a sql server 2008 sp1 database with 80 compatibility. Queries duration time gradually increase, yet CPU time stays around the same, meaning CPU wait time increases. The query runs 1000 times a second. It would eventually reach to a point that end user started to experience timeout. The only thing we are doing is to periodically restart the instance.

Anyone knows why? and how to correct it?

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 5

NJ-DBA (8/1/2012)
Testing revealed that the primary factor influencing the duration of the “if exists” query is lack of an index with th filename column in the key. Particularly, this column should be the second column in the key.
Durations were not impacted by any of the following factors:
-presence or lack or sp_reset_connection as tested using C# code which uses a single connection (like production) or opens and closes connections (and issues sp_reset_connection)
-permissions of user (sysadmin vs app user with somewhatlimited rights)
-fragementation of indexes (marginally fragmented, realistic vs. just after indexes are rebuilt).
-statistics- (tested by running update statistics with fullscan before, and during runs and recompiling proc).
I understand why the missing index would make it run much slower, but not why it would progressively get worse during a long iterative run of the same proc over and over again. could this come down to work spent updating missing index stats?

Have you solved your problem yet? If not, could it be that the application is repeatedly calling the stored proc so quickly that SQL Server can't return results fast enough to keep up, causing subsequent calls to "queue up" while waiting for earlier calls to return results? Monitoring the wait stats would help you pinpoint any bottlenecks that might be causing this. Capture the data from the sys.dm_os_wait_stats DMV periodically while your problematic process runs and look for sharp increases in particular wait types as a start. This MS whitepaper will help you interpret what you see there (most of the information is still applicable even though the paper was written for SQL Server 2005):

http://technet.microsoft.com/en-us/library/cc966413.aspx

Jason Wolfkill

NJ-DBA SSChampion Points: 13834 More actions · Answer 6

wolfkillj (8/9/2012)
NJ-DBA (8/1/2012)
Testing revealed that the primary factor influencing the duration of the “if exists” query is lack of an index with th filename column in the key. Particularly, this column should be the second column in the key.
Durations were not impacted by any of the following factors:
-presence or lack or sp_reset_connection as tested using C# code which uses a single connection (like production) or opens and closes connections (and issues sp_reset_connection)
-permissions of user (sysadmin vs app user with somewhatlimited rights)
-fragementation of indexes (marginally fragmented, realistic vs. just after indexes are rebuilt).
-statistics- (tested by running update statistics with fullscan before, and during runs and recompiling proc).
I understand why the missing index would make it run much slower, but not why it would progressively get worse during a long iterative run of the same proc over and over again. could this come down to work spent updating missing index stats?
Have you solved your problem yet? If not, could it be that the application is repeatedly calling the stored proc so quickly that SQL Server can't return results fast enough to keep up, causing subsequent calls to "queue up" while waiting for earlier calls to return results? Monitoring the wait stats would help you pinpoint any bottlenecks that might be causing this. Capture the data from the sys.dm_os_wait_stats DMV periodically while your problematic process runs and look for sharp increases in particular wait types as a start. This MS whitepaper will help you interpret what you see there (most of the information is still applicable even though the paper was written for SQL Server 2005):
http://technet.microsoft.com/en-us/library/cc966413.aspx

Was not able to prove out an answer, but the waits stats didnt point me anywhere- only sos_scheduler_yeild waits. I allowed myself to call it done with the following theoretical explanation which I never proved out:

the first time we do the "if exists" it didnt exist, so we inserted them... in order... one after another. The next time, we are doing the if exist - in the same order- it does exist... ... since it is an if exists, we stop the seek when we find the first value that meets the criteria... so we are always seeking from start to finish... and since the index we are using does not include the selective value (filename) we are in essence doing work just like a scan. As we proceed through the queries, the value is found further and further into the index... we seek/scan through more and more pages each time.