SQL Server Tip: Optimizer Using Index Scan and Not Seek!

Author: Timur T7253

Introduction

Other than coding, the next biggest task for developers is to troubleshoot problems in their environments. Developers in the SQL world mostly face problems around error messages, getting required output via T-SQL query, and unpredictable performance of queries.

The Problem

One fine day somewhere in the world one developer was troubleshooting a performance problem – just like any one of us. He noticed, even after a table has an index on a column and if they use that column in a where clause, SQL Server was not doing an Index seek. He researched more and found that it might happen depending on the number of rows returned vs total rows. His main problem was that there was a screen which showed data for single employee and it took a long time to load. There were five similar queries which were used behind the scene and all of them were showing the same behavior. He was fetching only matching records for a given employee code and essentially each select statement returns just one row.

His query was as simple as it can get:

declare @Lname nvarchar(10) = ‘Trolen’

select top 1 [FirstName], [MiddleName], [LastName]

from dbo.Person

where LastName = @Lname

 

The Answer

He started reading many blogs and understood that a SQL performance expert would always look at the execution plan, so he thought of checking the query plan all by himself. This was a great way to learn SQL Server behavior but was quite challenging.

Note: If you want to follow along with the developer troubleshooting techniques, go to the home page of AdventureWorks database and choose the format or download via direct link AdventureWorks2012-Full Database Backup.zip. After restoring, run the script below.

USE [AdventureWorks2014]

GO

CREATE TABLE [dbo].[Person](

      [BusinessEntityID] [int] NOT NULL,

      [PersonType] [char](2) NOT NULL,

      [NameStyle] [dbo].[NameStyle] NOT NULL CONSTRAINT [DF_Person_NameStyle] DEFAULT ((0)),

      [Title] [varchar](8) NULL,

      [FirstName] varchar(50) NOT NULL,

      [MiddleName] varchar(50) NULL,

      [LastName] varchar(50) NOT NULL,

      [Suffix] [varchar](10) NULL,

      [EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_EmailPromotion] DEFAULT ((0)),

      [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,

      [Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,

      [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Person_rowguid] DEFAULT (newid()),

      [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_ModifiedDate] DEFAULT (getdate()),

CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED

(

      [BusinessEntityID] ASC

) ON [PRIMARY]

 

)

 

INSERT INTO dbo.Person

select * from Person.Person

go

 

USE [AdventureWorks2014]

GO

 

CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON dbo.[Person]

(

       [LastName] ASC,

       [FirstName] ASC,

       [MiddleName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)

GO

 

Here is the code which he was running along with the execution plan.

 

He quickly noticed yellow exclamation mark on select statement and clicked on that.

After this he went back to the query again and found that that there is a mismatch of data type between the variable and the table column on which there was a predicate.

Person.LastName in the table was defined as below:

[LastName] varchar(50) NOT NULL,

 

…and in select statement:

declare @Lname nvarchar(10) ‘Trolen’

 

We can clearly see a mismatch of data type in table and query (varchar vs. nvarchar).

As per books online Data Type Precedence: When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

Note that CONVERT_IMPLICIT does happen in our query and it is shown from above figure too. To fix the above problem, there are two approaches:

1. Modify query and use varchar (match it with table
2. Modify table and use nvarchar (match it with query

 

Is that better? Let’s run them together and find the cost.

 

We can clearly see that same data type is the winner.

Moral of the story

Always be careful for implicit conversions – don’t make assumptions – particularly when there are strings data types. Developers should always try to stick to matching types. Also as a developer we can see there is performance overheads if the datatypes are not matching. This learning can go a long way and next time you see an exclamation in Execution Plans, we are sure you know what to lookout for.

Next Steps

For more coding tips, you may also be interested in this this on-demand webinar, Top 10 Rapid SQL Tips and Techniques by industry expert, Dan Hotka. This webinar includes methods to:

• Improve coding efficiency
• Streamline your database development
• Improve team collaboration

Learn more about Embarcadero Rapid SQL, the intelligent IDE for SQL development, and try Rapid SQL for free.