July 25, 2012 at 8:49 am
I have a job that does the following via C sharp code:
-opens a connection to sql server (keeps it open for the entire run)
-enters a loop, executing a stored proc with some parameters
inside the loop, is is doing a test like "if exists (select * from where <conditions from parameters>)"
While watching this in profiler, I can see that the duration of this "if exists" statement is gradually increasing starting at 10000 microseconds, ending at 200000 microseconds 4 hours later. Durin the run, no data in the table changes and the plan is being reused. The wait type on the spid running this code is "SOS_SCHEDULER_YIELD". I expect we are seeing conditions like those described by Paul Randal and Kimberly Tripp here:
but I can not explain why the duration is increasing over time. All of the obvious answers have been ruled out. there is no pattern to the parameters that would suggest we are doing "different" work as time progresses...
not sure where to go from here.
August 1, 2012 at 5:14 am
Have you stepped through your C# code to see when your procedure is called and what parameters are being passed to it? Can you run the stored procedure in SSMS successfully using those parameter values and see the changes in your data that you are expecting to see? Is your stored procedure leaving any transactions uncommited?
August 1, 2012 at 5:56 am
William Melcher (8/1/2012)
Have you stepped through your C# code to see when your procedure is called and what parameters are being passed to it? Can you run the stored procedure in SSMS successfully using those parameter values and see the changes in your data that you are expecting to see? Is your stored procedure leaving any transactions uncommited?
This is the angle I'm looking at now. It took a while to pull together a workable test harness I can use in another environment to recreate the problem. We are not leaving any transactions open, but my suspicion is that some other item is accumulating... I noticed that the C# app keeps a single connection open and does not issue "sp_reset_connection", so I'm thinking something is not reset that should be.
Also, in some prior posts, I mentioned that logical reads appear to be increasing with each run of the proc. This was incorrect. The "reads" counter captured by the trace is a cummulative measure from the start of the connection, so logical reads go up, but each iteration is doing the same number of reads. Duration is not cumulative however, so I am still working to determine why each run takes more time.
August 1, 2012 at 6:12 am
Can you run the below query and post the output.
select scheduler_id, runnable_tasks_count,status from sys.dm_os_schedulers where scheduler_id < 255
Sometimes, a busy CPU may give this wait type.
August 1, 2012 at 7:12 am
dbasql79 (8/1/2012)
Can you run the below query and post the output.select scheduler_id, runnable_tasks_count,status from sys.dm_os_schedulers where scheduler_id < 255
Sometimes, a busy CPU may give this wait type.
never goes above 0. Testing on a 24 core box with no other activity.
August 1, 2012 at 10:10 am
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.
Testing revealed that 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?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply