Ok, so I may have exaggerated and jumped a few just slightly with the numbering there for effect, but they all matter.
Assume a heavily redacted query such as this.
The table is partitioned on Category
The query is not covered by an index as some returned are hot columns. (although the same partition behavior can be seen even when it is).
All indexes are aligned correctly.
The query also aligns, so partition elimination is expected.
Category and ID make up the PK.
The ORDER BY clause is the Clustered index key.
There are no parameter sniffing issues in play here.
exec sp_executesql N'SELECT [Category] ,[Id],[NoncoveredColumn],[AnotherNoncoveredColumn],[YetAnotherNoncoveredColumn]
FROM [dbo].[MyfatPartitionedTable] WITH (NOLOCK)
WHERE Category = @Category AND Id = @Id
ORDER BY [ClusteringKey]
',N'@Category nvarchar(4000),@Id nvarchar(4000)',@Category='Text',@Id='More Text values'
Eagle eyed readers may see something to make you say 'Yuk' already with this query, and suspect what sort of system it originated from.
Behaviour
Statistics IO output.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'XXXXXXXXXX'. Scan count 105, logical reads 683, physical reads 0, read-ahead reads 0, lob logical reads 294, lob physical reads 0, lob read-ahead reads 0.Hmmm... why worktables and 105 scans for something that should be small and innoculous?
Aha, a Sort operator... that may explain the worktable. But that plan looks more complex than it needs to be?
Lets have a look at the Properties of the Index Seek
Aha again, there are our 105 Scans accounted for, we're accessing all 105 partitions (1-105).
OK, why... lets have a look at the Predicates for that operator.
Seek Predicate
Seek Keys[1]: Start: PtnId1000 >= Scalar Operator((1)), End: PtnId1000 <= Scalar Operator((105)), Seek Keys[2]: Start: [xxxxxxxxxxxxxxxxx].Id > Scalar Operator([Expr1008]), End: [xxxxxxxxxxxxxxxxx].Id < Scalar Operator([Expr1009])
Nothing particularly nasty there, we're seeking to a valid place in the index
Predicate
CONVERT_IMPLICIT(nvarchar(200),[xxxxxxxxxxxxxxxxxxx].[Category,0)=[@Category] AND CONVERT_IMPLICIT(nvarchar(200),[xxxxxxxxxxxxxxxxxxx].[Id],0)=[@Id]
Oh dear, what's all that then, we have a fat Residual Predicate, where SQL is performing an Implicit conversion on our query predicates.
Time to look at our datatypes.
They aren't silly... varchar(200), not the ORM defaulting nvarchar(4000)s , which are silly.
Code Take 2.
exec sp_executesql N'SELECT [Category] ,[Id],[NoncoveredColumn],[AnotherNoncoveredColumn],[YetAnotherNoncoveredColumn]
FROM [dbo].[MyfatPartitionedTable] WITH (NOLOCK)
WHERE Category = @Category AND Id = @Id
ORDER BY [ClusteringKey]
',N'@Category varchar(200),@Id varchar(200)',@Category='Text',@Id='More Text values'Matched Baby!
Now, the plan looks like this. Same indexes being used, just a lot less work.
And lets now check the properties on the Index Seek operator.
Only a single partition (68) was accessed, exactly what we want and would expect.
Performance Impact
We see our logical reads have dropped lots from Statistics IO...what about other metrics?
Oh, hello....
Datatype | CPU | Duration |
Mismatched | 4776 | 13563 |
Matched | 1365 | 5001 |
Thats quite a drop for a minor change. And this is just out and out performance metrics, just think of the benefits of fewer locks, fewer latches, lower resource usage, happier users. On a busy OLTP system, that can be a nice little bonus....
Implicit Conversion removal only
What happens if we just remove the conversion, use the same basic datatype, but still get the sizings wrong? ie... we use wildly oversized varchars in our code. Remember, our column is still varchar(200)
exec sp_executesql N'SELECT [Category] ,[Id],[NoncoveredColumn],[AnotherNoncoveredColumn],[YetAnotherNoncoveredColumn]
FROM [dbo].[MyfatPartitionedTable] WITH (NOLOCK)
WHERE Category = @Category AND Id = @Id
ORDER BY [ClusteringKey]
',N'@Category varchar(2000),@Id varchar(2000)',@Category='Text',@Id='More Text values'Ok, plan looks a little tastier than originally... still a Sort operator though.
And still the 105 scans
Table 'xxxxxxxxxxx'. Scan count 105, logical reads 683, physical reads 0, read-ahead reads 0, lob logical reads 294, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Because we're still hitting every partition, so about as much elimination as seen on an Imperial Stormtroopers resume.
Seek Predicate
Seek Keys[1]: Start: PtnId1000 >= Scalar Operator((1)), End: PtnId1000 <= Scalar Operator((105)), Seek Keys[2]: Prefix: [xxxxxxxxxxxx].Id = Scalar Operator([@Id])
Predicate
[[xxxxxxxxxxxxx].[Category]=[@Category]
So this time, we seek for the ID
The moral of this story?
Well, its the same one as always really, check your datatypes, especially when using ORMs to generate code. Something that may seem harmless, may be doing you damage in far more ways than you think.
And Table Partitioning?
It can be picky to perform partition elimination sometimes, and this shows another reason for it to decide to not behave as you may think.... but you knew that already.
Check, Check, Check those datatypes!
As always, thanks for reading.
N'Rod'
Comments