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 10:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:01 PM
Points: 2,716, Visits: 2,466
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
Post #562063
Posted Monday, September 1, 2008 10:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
- look for implicit conversions (show xml execution plan)
- how is your sqlservers memory doing (pressure ?)


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #562064
Posted Monday, September 1, 2008 1:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 25, 2014 1:01 PM
Points: 2,716, Visits: 2,466
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
Post #562090
Posted Monday, September 1, 2008 5:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:00 AM
Points: 35,276, Visits: 31,768
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #562113
Posted Tuesday, September 2, 2008 3:55 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 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
Post #562281
Posted Thursday, October 29, 2009 2:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:29 AM
Points: 17, 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.
Post #811186
Posted Friday, October 30, 2009 2:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 284, Visits: 1,078
.
Post #811872
Posted Tuesday, November 3, 2009 3:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 13, 2009 5:46 AM
Points: 2, 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.
Post #813292
Posted Thursday, November 12, 2009 9:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:00 AM
Points: 35,276, Visits: 31,768
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #818230
Posted Tuesday, December 15, 2009 7:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 178, Visits: 626
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
Post #834477
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse