Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query suddenly goes slow - Worktable logical reads high


query suddenly goes slow - Worktable logical reads high

Author
Message
crichardson-782748
crichardson-782748
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 136
I just got a support call for a procedure that is running slow in production but goes fast in the test environment. I isolated the statement within the sproc that was the cause of the slow down. I ran the same query in production and test with SET STATISTICS IO ON and the only significant difference is: -

Production : Table 'Worktable'. Scan count 1, logical reads 1054329, physical reads 0
Test : Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0

I tried updating statistics and clearinging the proc cache but didn't make any difference. Any suggestions on a way forward with this?
kevriley
kevriley
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2751 Visits: 2606
And does the test query return exactly the same results as the production query?


Kev
kevriley
kevriley
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2751 Visits: 2606
Another thought - are the indexes the same? and is the data the same?

Kev
crichardson-782748
crichardson-782748
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 136
Same db restored to test. Resultset is the same apart from a few extra rows inserted into the production server.

What seems to be the issue is that SQL made a decision on the production server to spool out the record set to Worktable and this dramatically increased the IO and, consequently, cpu time. Some things I've read about this indicate bad joins being a factor. I can't find what triggers this behavior though. I've look at wait stats but not sure what is exceptional or not.

Clive
Christopher Stobbs
Christopher Stobbs
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 2232
have you looked at the stats on that table in live?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
kevriley
kevriley
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2751 Visits: 2606
crichardson (9/1/2008)
I tried updating statistics and clearinging the proc cache but didn't make any difference.


Was this on both the production(gulp) and test servers?



Kev
Christopher Stobbs
Christopher Stobbs
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 2232
Sorry about my first post, I see you have already looked at the stats.

Is there anything different in the EXECUTION PLANS.
For Example a SCAN in test where there is a SEEK in live?

Also is this a proc? if so it could be param sniffing!!!
thanks
Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
crichardson-782748
crichardson-782748
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 136
The execution plans are about the same. It's on the IO stats that throw up the difference with all those reads to Worktable in tempdb. Something is obviously triggering that but have no idea how to find out what.

I cut out a part of the sproc which I identified in Profilier as the statment that was causing most of the duration. So, the io stats are from a query in SSMS, no parameters or whatever.

Clive
crichardson-782748
crichardson-782748
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 136
SSCommitted,

The same query on a test (VM) server run in less than 0.5 second. On a very well specified production server, anywhere between 40 and 140 seconds. No issue with lack of cpu, memory or i/o bandwidth. Just seems to be this issue with SQL deciding to spool out the record set to Worktable in tempdb. Logically, I would have thought that SQL Server would do this if it had to wait on a resource but, having examing waitstats, I can't conclude anything.

Clive
Christopher Stobbs
Christopher Stobbs
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 2232
HI,

Could you give us the code cause the problem please.
I'm also concerned that you say.
The query plans are almost the same and not the same!

Thanks
Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

w00t
Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search