query suddenly goes slow - Worktable logical reads high

Question

Post reply

query suddenly goes slow - Worktable logical reads high

crichardson-782748

Mr or Mrs. 500

Points: 589
More actions
September 1, 2008 at 6:58 am

#197719

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?

Viewing 15 posts - 1 through 15 (of 25 total)

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

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 1

And does the test query return exactly the same results as the production query?

Kev

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 2

Another thought - are the indexes the same? and is the data the same?

Kev

crichardson-782748 Mr or Mrs. 500 Points: 589 More actions · Answer 3

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 SSC-Insane Points: 21098 More actions · Answer 4

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[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 5

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 SSC-Insane Points: 21098 More actions · Answer 6

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[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

crichardson-782748 Mr or Mrs. 500 Points: 589 More actions · Answer 7

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 Mr or Mrs. 500 Points: 589 More actions · Answer 8

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 SSC-Insane Points: 21098 More actions · Answer 9

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[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 10

Can you force a recompile on the Production server (WITH RECOMPILE)?

Is parallelism turned on? Is it configured (MAXDOP). Does it appear in either of the execution plans?

Any chance of posting the code?

If not what about the execution plans (right click and save as .sqlplan)

Kev

Johan Bijnens SSC Guru Points: 135356 More actions · Answer 11

- look for implicit conversions (show xml execution plan)

- how is your sqlservers memory doing (pressure ?)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 12

ALZDBA (9/1/2008)
- look for implicit conversions (show xml execution plan)
- how is your sqlservers memory doing (pressure ?)

wouldn't implicit conversions affect both the production and test environments?

memory pressure - Clive has already said that this isn't an issue.

Kev

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 13

As previously suggested, have you looked into "parameter sniffing"? Google it.

Also, are there any settings differences between the two servers like "auto statistics", etc. Last but not least, is one server using something like a SAN while the other is using it's own disks?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

crichardson-782748 Mr or Mrs. 500 Points: 589 More actions · Answer 14

I won't post the code now as the issue has been resolved. Basically, when you suddenly see Scans and Logical Reads to 'Worktable' from the output of 'SET STATISTICS IO ON' when this didn't happen before, it is down to bad joins, aggregates, views and so on... but data growth or server resource issues can all probably trigger it. The solution is to re-write the code. In our case, remove the views and directly reference the underlying tables.

We have a similar poor performing query that also uses the Worktable a lot but in that case SQL has no choice because the query uses UNIONs and SQL has to use Worktable to remove duplicate rows in the result set. UNION ALL doesn't try to remove dup rows so is a better performer.

Thanks,

Zarty