In my previous post, I mentioned the use of functions, specifically ISNULL and the effects on SARGability of the query. There is another one that often catches out developers and DBAs.
To start off I have created the following two tables and populated them with the same 3 million rows:
 |
 |
Both have clustered index on Id and a non-clustered on DisplayName
|
1 2 3 |
CREATE NONCLUSTERED INDEX [IX_MyUnicodeUsers_DisplayName] ON [dbo].[MyUnicodeUsers] ([DisplayName]) GO |
|
1 2 3 |
CREATE NONCLUSTERED INDEX [IX_MyUsers_DisplayName] ON [dbo].[MyUsers] ([DisplayName]) GO |
The tables are very similar with the only difference being that AboutMe and DisplayName columns in MyUnicodeUsers are of data type nvarchar, unlike varchar data type in MyUsers table. Before running any queries, please select “Include Actual Execution Plan”. This can be easily done using the keyboard shortcut, “Ctrl + M”.
Now let’s run the following two queries against MyUnicodeUsers table:
|
1 2 3 4 5 6 7 |
SELECT Id, DisplayName FROM MyUnicodeUsers WHERE DisplayName = 'Paul' SELECT Id, DisplayName FROM MyUnicodeUsers WHERE DisplayName = N'Paul' |
The execution plans look as follows:
Nothing to worry there. Now let’s run the same two queries against MyUsers table:
|
1 2 3 4 5 6 7 |
SELECT Id, DisplayName FROM MyUsers WHERE DisplayName = 'Paul' SELECT Id, DisplayName FROM MyUsers WHERE DisplayName = N'Paul' |
And here’s the execution plan:
Huh? What happened there?
When the last query ran, SQL Server decided to scan the non-clustered index instead of doing a seek, like it did in the three previous queries. With one of the queries run against MyUnicodeUsers table, I did compare the varchar parameter to nvarchar data type column. And that seemed fine. So why the difference?
The answer to that lies in this MSDN article.
The nvarchar data type has higher precedence than varchar data type. So when the operator compares the DisplayName varchar column with the nvarchar parameter, it will implicitly convert the DisplayName column, making the predicate non-sargable and, thereby, hindering the seek on the non-clustered index. Well, well… that wasn’t quite what I expected.
If you looks closely, and hover your mouse over the execution plan that did a seek on MyUnicodeUsers table. even though the two data types – column and parameter – were different, you will notice:
There was an implicit conversion, although it did not affect the sargability of our query as it wasn’t the column that got converted, but the parameter passed.
Hmmmm, I wonder how many queries running on my database are suffering from scans due to implicit conversions on the column.
Fortunately, Jonathan Kehayias has provided a script in this article, which is as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @dbname SYSNAME SET @dbname = QUOTENAME(DB_NAME()); WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT stmt.value('(@StatementText)[1]', 'varchar(max)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'), ic.DATA_TYPE AS ConvertFrom, ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, t.value('(@Length)[1]', 'int') AS ConvertToLength, query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1 and ic.DATA_TYPE = 'varchar' |
This will display details of any queries suffering from scans due to implicit conversions to a column. Fantastic!
AlexB says
Thanks Pankaj! another great article. Good insight, well presented. A great read.
Keep it up.
Thanks again
Bogdan Sahlean says
This happens if column’s collation is SQL. Windows collations doesn’t have this behaviour.
Juergen says
Hi.
I found this very useful for me…
Do you now why the SQL-Server ansers the data type of serveral views (out of sys.columns) with nvarchar while the underline table is varchar?
This could cause some performance issues as well in the query, isn’t it?
Regards
Juergen
Pankaj Manek says
Hi Juergen,
I find it a bit strange that the datatype of a column in your view is different to the underlying table. One reason I can think of is that the view is not schema binding and column definition of the underlying table could have been changed retrospectively.
I don’t think it matters too much that your underlying table is varchar while your view is nvarchar. It really depends on what parameter type you use as part of the query to the view. If you pass nvarchar datatype then yes, the query would be a candidate to suffer from this.
Hope that helps.