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

Cardinality Issue Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 9:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:14 AM
Points: 78, Visits: 306
I have a 100,000,000 row table with a GUID primary key. (This is an IBM FileNet/ECM database, and utility query.)

The following query:
SELECT TOP 9999 * -- Edited for brevity
FROM DocVersion T0
WHERE (T0.home_id IS NULL
AND ( ( storage_area_id = N'{18CDA09D-00B2-427F-9016-C78844AA92AE}' OR
storage_location =N'FNFS:/{18CDA09D-00B2-427F-9016-C78844AA92AE}'))
AND (( object_id > N'{42D1BB3B-53F2-462D-A277-1AFF39548364}')))
ORDER BY object_id ASC
option (recompile)

is making a cardinality error on object_id. It estimates it will have 10,000 - 20,000 rows on the greater-than comparison, but the actual row count is 46M.

Based on this error, it goes for a seek of the primary key, and as such, it takes 4-6 hours to seek 46M times. The scan would be 13 minutes.

What I don't understand is 1) Why it is reaching this error, and 2) How to resolve it.

The primary key is on the object_id, which is a GUID.
This historgram details on the object_id are:
RANGE_HI_KEY                          RANGE_ROWS   EQ_ROWS   DISTINCT_RANGE_ROWS     AVG_RANGE_ROWS  
ff4c80d6-98b7-46ef-97f0-0000003c6313 0 1 0 1
9da7159c-a90e-48e3-84f1-01ef6ded77e4 319998 1 319998 1
40b75dcf-55c1-4371-83e5-04b0ea3cea3f 267887 1 267887 1
e2aed122-0e41-40a2-9296-0ea384fd1a1d 1608631 1 1608190 1.000275
e0fbf953-9a39-4d49-bbc6-132a32003da4 536035.9 1 536036 1
e9b6e490-233e-40ba-a4e4-19179b42e6c 5536035.9 1 536036 1
--------------------------------------------------
0b8fc800-047f-5eb2-bfb9-1b3e9999571f 536035.9 1 536036 1
0aa19a05-047f-5eb2-bfb9-1b3e9999571f 804184.8 1 804094 1.000112
(continued...169 steps total)


I believe GUIDs are ordered by the last segment of the guid, and that belief is reflected in this histo. I put a dotted line in the histo: Everything below the line (including the remainding 161 steps) would match the query predicate on object_id.

Why in the world does the PK seek think there are only 20k rows? Each step has over 100,000 rows.

Thanks for your consideration,
@SixStringSQL
Post #1437487
Posted Monday, April 1, 2013 9:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
Stale statistics?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1437509
Posted Monday, April 1, 2013 10:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:14 AM
Points: 78, Visits: 306
Nah, I full-scanned all of them...It made the cardinality error get a little bit worse.

There are 196 steps in the histogram...I wonder if getting that close to the limit is a clue.
Post #1437529
Posted Tuesday, April 2, 2013 8:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:59 AM
Points: 1,060, Visits: 2,564
@SixStringSQL, I think this probably has something to do with your use of the TOP 9999 clause and/or the fact that the WHERE clause includes conditions on columns other than the PK. Can you post an actual execution plan so we can see what happens in detail?

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1437915
Posted Tuesday, April 2, 2013 8:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:14 AM
Points: 78, Visits: 306
I'll try....I need to find 3 hours where I can destroy I/O run the query without affecting prod.
Post #1437931
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse