SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cardinality Issue


Cardinality Issue

Author
Message
@SixStringSQL
@SixStringSQL
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 383
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27282 Visits: 17557
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 Modens 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)
@SixStringSQL
@SixStringSQL
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 383
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.
wolfkillj
wolfkillj
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 2582
@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
@SixStringSQL
@SixStringSQL
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 383
I'll try....I need to find 3 hours where I can destroy I/O run the query without affecting prod.
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