SQL Server Tip: Optimizer using Index Scan and not Seek!

Posted by on in SQL Development

Other than coding, most of the time developers have to troubleshoot problems from their environments. Developers in SQL world would mostly face problems around error message, getting required output via T-SQL query and unpredictable performance of query. 

One fine day somewhere in the world a one developer was troubleshooting a performance problem – just like any one of us. He noticed, even after a table has index on a column and if they use that column in where clause, SQL Server was not doing Index seek? He researched more and found that it might happen depending on number of rows returned vs total rows? His main problem was that there was a screen which shows data for single employee and it takes lot of time to load. There were 5 similar queries which are used behind the scene and all of them are showing same behavior. He was fetching only matching record 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

 

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

 

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

 

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

 

01_plan.jpg

 

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

 

b2ap3_thumbnail_02_warning.jpg

 

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. The fix for 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)

 

03_Good_plan.jpg

 

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

 

04_Comparison.jpg

 

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.

 

Click to learn more about Embarcadero database tools related to this post:

 



About
Gold User, Rank: 12, Points: 285
Embarcadero Field Marketing - North America

Comments

Check out more tips and tricks in this development video: