One of the top 10 misstakes in SQL server is the use of SELECT *. By using SELECT *, you can’t make efficient use of nonclustered convering index and you are doomed to a clustered index scan or index seek + key lookup. A covering index may be a hundred times faster The sad part of this story is that too many applications and developers are using SELECT * and many databases are suffering from bad performance because of this. And the worst part is when the code isn’t inside of stored procedures and you have to rebuild your application to remove this bad behavior. You get unhappy users complaining about the application and the developers are complaining about the database performance. But how are you going to tune the database when you have no control of the code inside the application? Well the only option you have as the DBA is to get faster hardware! (but its no problem to consume all the resources in any hardware in the world, by writing terrible code) It doesn’t have to be like this!
Try this query: USE AdventuresWorks2008R2 GO SELECT * FROM Sales.SalesOrderHeader soh WHERE soh.SalesOrderNumber = ‘SO44009’ You will get an execution plan like this. Take a look at the Key Lookup, this indicates that you don’t have all the columns you need (in this case all columns) in the non clustered index, and SQL server need to do a key lookup in the clustered index (where all your data exists). This query plan will work for you if the number of records found in the index seek is few, but imagine a couple of 100 000 records or more!
The key look up will execute once for every record found, or SQL server will hopefully change the plan to a index scan. But if the plan is cached with key lookup because the first query only returned a few record you’ll get an inefficient plan for large results. Lets say you only want a couple of the columns in your application, like the following query: SELECT soh.SalesOrderNumber, soh.OrderDate, soh.DueDate, soh.TotalDue FROM Sales.SalesOrderHeader soh WHERE soh.SalesOrderNumber = ‘SO44009’ You still get the same execution plan because OrderDate, DueDate and TotalDue doesn’t exist in the index neither as key column and neither as included column.
But you have the option to tune your index like this: CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderHeader_SalesOrderNumber] ON [Sales].[SalesOrderHeader] ( [SalesOrderNumber] ASC ) INCLUDE ( [OrderDate], [DueDate], [CustomerID], [TotalDue]) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85 ) ON [PRIMARY] GO And you will get a query plan like this: As you can see, the Key lookup is gone and you have a faster query. If the query will return lots of records you’ll get and index scan on this nonclustered index which is smaller than the clustered index. If you suffer from SQL server performance, feel free to contact any of our SQL server experts and we do what we can to help you.