SQL Clone
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
kevriley
kevriley
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4338 Visits: 2635
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
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30911 Visits: 8986
- look for implicit conversions (show xml execution plan)
- how is your sqlservers memory doing (pressure ?)

Johan


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
kevriley
kevriley
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4338 Visits: 2635
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
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221904 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
crichardson-782748
crichardson-782748
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 136
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
sheridan-499329
sheridan-499329
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 187
Hi -

I don't know if this will help or not, but it's something I've seen in the past. Is your tempdb configured differently between production and test? Specifically, is production configured at too low an initial setting and is forced to grow to accommodate your request? And is test configured high enough to handle it? You should look at the growth factor on the data and log files too. If it's at 8MB to start and growing by 1MB at a time, it would cause slowness while tempdb grows. I hope that your production environment is configured correctly for tempdb, but you never know and should confirm it.

Best of luck.
Chris Howarth-536003
Chris Howarth-536003
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 1167
.
YasmeenC
YasmeenC
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 15
I have the same problem, execute query in mgmt studio and in stored proc producing different statistic results. Shouldn't they have the same results since they are both identical queries executed in the same server and db?
The one in stored proc is taking much longer than the mgmt studio.
Can anyone explain to me why? Thanks.

query from mgmt studio:
SQL Server Execution Times:
CPU time = 2497 ms, elapsed time = 4542 ms.
Table 'JS_Vacancy'. Scan count 9, logical reads 39924, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JS_FinalTable'. Scan count 9, logical reads 251245, physical reads 0, read-ahead reads 15054, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 8, logical reads 15428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

same query from stored proc:
SQL Server Execution Times: CPU time = 1243750 ms, elapsed time = 189595 ms.
Table 'JS_FinalTable'. Scan count 7700, logical reads 297912779, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JS_Vacancy'. Scan count 9, logical reads 40700, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 15598, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221904 Visits: 42003
Can't tell just from what you posted but it has all the smackings of "parameter sniffing".

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
BobMcC
BobMcC
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 834
Too bad CRICHARDSONS last reply was in September.
This sounds related to a problem I've having w/ SQL 2008. We upgraded last month (Db > 1TB).
A query that used to take about 2 minutes now takes 8. When I look at IO Stats, one thing that really stuck out was:
Table 'Worktable'. Scan count 7116008, logical reads 53304197, physical reads 0, read-ahead reads 0

The rest of my scancounts and logical reads are much much smaller.
Ala...
Table 'tbldEncntr'. Scan count 2, logical reads 29960, physical reads 53, read-ahead reads 14975
Table 'tbldAdj'. Scan count 1, logical reads 72430, physical reads 166, read-ahead reads 72407
Table 'tbllAdjCd'. Scan count 1, logical reads 8, physical reads 2, read-ahead reads 5
Table 'tbldChrg'. Scan count 4, logical reads 123750, physical reads 189, read-ahead reads 61863
Table 'tbllSite'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0
Table 'tbllSiteGrp'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0
Table 'tbldPaym'. Scan count 1, logical reads 78286, physical reads 197, read-ahead reads 78262
Table 'tblsSitePaycl_Perf'. Scan count 1, logical reads 138, physical reads 5, read-ahead reads 134
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