April 1, 2013 at 9:15 am
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
April 1, 2013 at 9:58 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 1, 2013 at 10:55 am
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.
April 2, 2013 at 8:14 am
@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
April 2, 2013 at 8:29 am
I'll try....I need to find 3 hours where I can destroy I/O run the query without affecting prod.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply