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 Tuesday, December 15, 2009 8:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 7,001, Visits: 8,439
BobMcC (12/15/2009)
Too bad CRICHARDSONS last reply was in September.
...



euhm .... September 2008


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 #834528
Posted Tuesday, December 15, 2009 8:11 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:03 PM
Points: 178, Visits: 614
ALZDBA (12/15/2009)
BobMcC (12/15/2009)
Too bad CRICHARDSONS last reply was in September.
...



euhm .... September 2008


ha ha
Hey, I said september! So I off by a year. :)
Post #834537
Posted Tuesday, December 15, 2009 10:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 7,001, Visits: 8,439
Ever calculated how many milliseconds that are ???

Milliseconds because that's what we are used to take into account



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 #834645
Posted Tuesday, December 15, 2009 3:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 201, Visits: 642
Try to rebuild the indexes on the tables that are used in that query

(If it suddenly starts working slower and recompiling doesn't help, set statistics io shows big numbers it is very likely that indexes on some of the tables used in the query are heavily fragmented)
Post #834778
Posted Tuesday, January 12, 2010 10:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 13, 2012 10:05 AM
Points: 1, Visits: 32
It can happen that you have HIGH logical reads for empty table (or small table), and query processing time is way too long. The reason is your table has so many empty pages. You simply drop and re-create your table.

Optimizer does not use index when table is empty or has only few rows. Instead, it does logical reads for thousands and thousands of empty pages. I believe Microsoft is still not aware of this problem.

DBCC checktable(<tablename>) will tell you how many pages.

Hope this helps,
Thinh Ho
Post #846335
Posted Wednesday, June 12, 2013 10:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 19, 2014 7:17 PM
Points: 11, Visits: 338
I had same problem. The same query does massive logical reads on 2008 R2.

Stats on 2008.
Table 'Worktable'. Scan count 5, logical reads 5763460, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '******'. Scan count 1, logical reads 1295, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Stats on 2005
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.
Table '*******'. Scan count 1, logical reads 1306, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

When I compared the two execution plans. On 2008, it uses merge join and there is a sort. On 2005, it uses hash join.
So I forced hash join on 2008. sql then generated same query plan as 2005 and there was no sort no logical reads any more. My query was then 3 times faster.

Not sure why there is such a difference. I can confirm index fragmentation on tables involved was very low. I also even updated stats with full scan before enforcing hash join. 2008 still uses different query plan.

In a nutshell, in my case, I had to force a hash join to resolve the issue.
Post #1462902
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse