Learning SQL Tuning

This is the post excerpt.

Advertisements

I’ve always wanted to learn more about SQL tuning, so I “tuned in” to Kendra Little’s blog to get started.

She says the best way to do this is to experiment with SQL Server anti-patterns to create slow queries that you can then try to optimize. Turns out it’s actually challenging to create queries that will perform poorly every time.

To prepare, I created the Adventure Works OLTP database from downloaded scripts, but I wasn’t able to run scripts related to full-text searches since SQL Express doesn’t support that. Let’s hope that’s the only “gotcha” I’ll run into with SQL Express.

Now I’m working through an article called The Seven Sins against TSQL Performance that Kendra suggested. My execution plan showed an index scan on a query the author, Grant Fritchey, used to demonstrate the performance hit taken when comparing disparate data types in the WHERE clause. But the execution plan displayed an index seek when the data types were the same. So what’s the difference?

Well, an index scan has to read every record in a table to satisfy the query condition because the optimizer couldn’t find a useful index. But an index seek reads only qualifying rows since there is a useful index.

Best Practice Tip: USE THE CORRECT DATA TYPES

Why couldn’t the optimizer find a useful index when we compared disparate data types in the WHERE clause? Because most of the functions we use in WHERE and ON clauses will prevent the optimizer from making proper use of indexes!

post

So that leads to another performance tip:

Best Practice Tip: DON’T USE FUNCTIONS IN WHERE AND ON CLAUSES

Here are some of the queries I’ve been testing against the Adventure Works database:

-- example of not using the correct data type
-- we get a nonclustered index scan that reads all 290 rows in the table

SELECT e.BusinessEntityID,
 e.NationalIDNumber
FROM HumanResources.Employee AS e
WHERE e.NationalIDNumber = 112457891;

-- now we use the correct data type
-- we get a nonclustered index seek that reads only one row in the table
SELECT e.BusinessEntityID,
 e.NationalIDNumber
FROM HumanResources.Employee AS e
WHERE e.NationalIDNumber = '112457891';

set statistics time on
-- this query, with a function in the WHERE clause, generates an index scan
SELECT a.AddressLine1,
 a.AddressLine2,
 a.City,
 a.StateProvinceID
FROM Person.Address AS a
WHERE '4444' = LEFT(a.AddressLine1, 4) ;
set statistics time off

set statistics time on
-- but this query produced an index seek
SELECT a.AddressLine1,
 a.AddressLine2,
 a.City,
 a.StateProvinceID
FROM Person.Address AS a
WHERE a.AddressLine1 LIKE '4444%' ;
set statistics time off

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s