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!
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