Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

query suddenly goes slow - Worktable logical reads high Expand / Collapse
Author
Message
Posted Monday, September 1, 2008 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 2:05 AM
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?

Post #561986
Posted Monday, September 1, 2008 7:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 2,684, Visits: 2,431
And does the test query return exactly the same results as the production query?


Kev
Post #562002
Posted Monday, September 1, 2008 8:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 2,684, Visits: 2,431
Another thought - are the indexes the same? and is the data the same?

Kev
Post #562004
Posted Monday, September 1, 2008 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 2:05 AM
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
Post #562031
Posted Monday, September 1, 2008 9:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
have you looked at the stats on that table in live?

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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #562038
Posted Monday, September 1, 2008 9:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 2,684, Visits: 2,431
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
Post #562042
Posted Monday, September 1, 2008 9:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #562043
Posted Monday, September 1, 2008 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 2:05 AM
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
Post #562056
Posted Monday, September 1, 2008 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 2:05 AM
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
Post #562060
Posted Monday, September 1, 2008 10:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #562061
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse