Execution plan shows enormous number of records
-
The problem was reported to us by a customer. They shared a graphic execution plan, fragment of which I'm attaching, that shows the output of 2.5 B of records after an "Index Seek" element. It's in the right-bottom corner of the plan, so there's nothing that happened before. The ELIGIBILITY table, against which the "Index Seek" was performed, has "only" some 200 M of records. How could this be possible that ten times as many records were pushed from it after an index SEEK?
The customer reported this as a bug, so we need to address this effect, but I'm struggling to understand what could have caused such an output. The table is referenced in the query in this way:
AND NOT EXISTS
(
SELECT 1
FROM ELIGIBILITY
WHERE [conditions]
)
Please share your thoughts.
-
How many rows in dbo.ELIGIBILITY? What is definition of dbo.ELIGIBILITY (including id_ELIGIBILITY_PREMIUM_GR...)?
What's actually in the where clause? Any user-defined functions? functions wrapping columns (ISNULL, COALESCE, CAST/CONVERT FORMAT, etc.)? Views (especially if nested)?
We probably need actual query (obfuscated as necessary) and/or actual execution plan (XML, not a picture), please.
-
- outdated statistics?
- implicite conversions?
- functions in where clause?
- did you analyse the sqlplan using the free Solarwinds Plan Explorer ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- 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
-
Thank you all for looking at this issue and sharing your insights! I was able to reproduce the issue, even on a much smaller scale (in the DEV environment), and I think I know the answer now. The ELIGIBILITY table, the one in center of this issue, has been created without a clustered key. I don't know why - someone did this 10+ years ago. It has a primary key, and more than a dozen non-clustered indexes that different processes needed, apparently, to make up for the missing clustered key.
In the DEV the table has 11031 records, but as you can see in the graph, it outputted 30008261 records. I found a very close number in the query's statistics:
Table 'ELIGIBILITY'. Scan count 3562, logical reads 30102777
3562 is a number of eligible records in the data I tried to process! So for each one of them the query performed an individual scan of the whole table: 11031 * 3562 = 39292422 (which is a little more but still in a ballpark of 30102777). I believe this is the explanation of the enormous number of records.
I was not able to receive a permission to create a clustered index, which might have been the optimal solution, but at least I have an idea now on how to modify the query (I think)
-
It would be good to see the actual SELECT from that table. As mentioned before there could be function(s) there. Another possibility is a cartersian join.
----------------------------------------------------
-
The number of 30102777 is NOT a row count... it's a PAGE COUNT (1 logic read = 1 8KB page) If you divide that number by 128 (the number of 8K pages in a megabyte), you see that you're reading 30102777/128.0 or 235,177.9453125 megabytes with is ~235 Giga Bytes or a Quarter of a Terabyte of I/O.
I'd say you''ve correctly identified the problem. There may be other issues but that "accidental cross join" looks like the brunt of the issue.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thanks for the related information
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply