Question: I'm reading with interest your answer(s) to Partitioned Views vs. Partitioned Tables in your Q&A. My question is how can I prove to myself I have everything configured correctly and partition elimination is indeed occurring? I tried SET STATISTICS XML ON, and then searched the XML Showplan for 'PartitionsAccessed' and 'PartitionRange,' but didn't find either.
First, check out the two prior Q&As here for background and important design considerations:
- Partitioned Tables v. Partitioned Views–Why are they even still around?
- Solutions to VLT concerns around statistics and maintenance!
Answer: PartitionsAccessed and PartitionRange are solely for partitioned tables. For partitioned views what you’re looking for are “executes” against a table. Only the table(s) that aren’t eliminated should have an execute that’s greater than zero. Probably the easiest way to see this is SET STATISTICS IO ON. On SQL Server 2005 and higher, this will ONLY list the tables against which you execute (I seem to remember there was a bug against SQL Server 2000 where it would list ALL of the tables in the statistics io output but the executes were zero and the Scan Count was zero as well).
And, by using statistics io you can also gauge the relative efficiency of the reads. With SHOWPLAN you can see how SQL Server did the reads (for example: scan vs. seek).
So, in summary:
- I use SET STATISTICS IO ON to see which table (or tables) were accessed.
- I use the graphical or XML showplans to see HOW the tables were accessed.
If SQL Server is still accessing a table where you don’t think it should then check these things:
1. Check to make sure that the query supplied has some form of predicate against the partitioning key. If you don’t reduce rows specifically on the partitioning column then SQL Server cannot eliminate over it. If you have your data partitioned by date, every query (ideally) should supply a limited date range.
2. If you have supplied a predicate over the partitioning key and SQL Server still doesn’t seem to be eliminating your partitions then you might have an untrusted constraint. To check to see if a constraint is trusted check the property state using:
SELECT OBJECTPROPERTY(object_id(‘ConstraintName’), 'CnstIsNotTrusted')
If the constraint returns 1 (true) then the constraint is NOT trusted. No, I’m not sure why they used a double negative here but that’s what the property is checking. You could even run this against all of your constraints within your database.
SELECT OBJECT_NAME (parent_object_id) AS [Table Name],
name AS [Constraint Name],
OBJECTPROPERTY([object_id], 'CnstIsNotTrusted') AS [NOT Trusted]
FROM sys.objects
WHERE type_desc = 'CHECK_CONSTRAINT'
So, check those objects and make sure that your constraints are trusted. If they aren’t – you might be accessing partitions where you really don’t need to. This wastes resources and most importantly, time!
And, we skipped a week in between posts… Why? Especially because I still have A LOT more to post on partitioning… I’m just, er, (pardon the pun) partitioning it over multiple Q&As. OK, that was bad. Seriously, though, we took last week off and did some diving (and whale watching) off the Kona coast of Hawaii. As you may have read in our bio, one of our “other” passions is seeking out frogfish and we were highly successful there (Paul was the ultimate frogfish finder having found more than 5 and these guys know how to hide!!). I’ve created a small album of just a few of my favorite shots. I thought you might want to see them and enjoy a little non-SQL time. I know we sometimes do!
Thanks for reading and enjoy a few shots!
Cheers,
Kimberly & Paul
The album doesn't seem to show up here on the blog entry but if you click through - you can see them. Here's a direct link. Enjoy!