November 11, 2015 at 12:45 pm
Good day,
Should I create all the proposed missing index by the SQL Execution Plan? Could these slow other queries?
What is the best approach for this?
Thanks in advance!
November 11, 2015 at 12:53 pm
sgrimard (11/11/2015)
Should I create all the proposed missing index by the SQL Execution Plan?
NO! Absolutely not.
The suggestion is based solely on that single query and it the single, absolute best index the query optimiser thinks would be useful for that query and no other. If you create blindly, you'll end up with hundreds of duplicate indexes, incredibly slow data modifications and a very bloated database.
They're a place to start index analysis. Not a place to end.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2015 at 1:01 pm
Any suggestion on good books or site to learn how to create good index?
November 11, 2015 at 3:36 pm
sgrimard (11/11/2015)
Any suggestion on good books or site to learn how to create good index?
SQL Server 2012 Query Performance Tuning (Expert's Voice in SQL Server) by Grant Fritchey is a great read. Chapters 4 through 8 discuss Indexing and/or important topics you need to know when creating indexes.
Also check out:
Performance Tuning with SQL Server Dynamic Management Views you can actually download this for free. See Chapter 5.
The book is not about designing indexes but it talks about how to see how your indexes are being used and which ones are giving you the most "bang for your buck". Its been a vital tool for me when deciding which indexes to create and which ones to get rid of.
-- Itzik Ben-Gan 2001
November 11, 2015 at 3:55 pm
But if you're in a hurry and cannot wait for enlightenment, I suggest you find and download sp_BlitzIndex and run it in table mode on the table in question, preferably after hours in a production environment, or in an environment that shares indexes and statistics with a production environment. It will tell you which indexes you DON'T need, which is an incredibly useful thing to know.
It will also tell you all the indexes that would be good to have. Plan on 4-6 indexes at maximum. if it tell you you need several indexes for a given column, try making a single index on the column with the included fields for all the related indexes. Try making those indexes in dev, let things run for a few days then run sp_BlitzIndex in table mode in dev and see how much that helped.
If the column is already very selective, just alter the index and add the included fields sp_BlitzIndex suggests. Try to keep big varchar fields out of the index, even if they're in included columns.
November 11, 2015 at 4:46 pm
But if you're in a hurry and cannot wait for enlightenment, I suggest you find and download sp_BlitzIndex
+1 😉
-- Itzik Ben-Gan 2001
November 12, 2015 at 4:30 am
Alan.B (11/11/2015)
sgrimard (11/11/2015)
Any suggestion on good books or site to learn how to create good index?SQL Server 2012 Query Performance Tuning (Expert's Voice in SQL Server) by Grant Fritchey is a great read. Chapters 4 through 8 discuss Indexing and/or important topics you need to know when creating indexes.
Also check out:
Performance Tuning with SQL Server Dynamic Management Views you can actually download this for free. See Chapter 5.
The book is not about designing indexes but it talks about how to see how your indexes are being used and which ones are giving you the most "bang for your buck". Its been a vital tool for me when deciding which indexes to create and which ones to get rid of.
Instead of the 2012 version, I'd suggest getting the updated version here. It's a better book overall. Very soon now, an updated version of Jason Strate's excellent book on indexing will be available. That has a lot of information about indexing.
The main thing to remember is that those index suggestions are just that, suggestions. They're not always right or appropriate. You can consider them and it's a good choice for looking at testing, but absolutely do not apply them blindly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 12, 2015 at 6:00 am
Before you go out and buy books, I would first recommend the stairways first.http://www.sqlservercentral.com/stairway/72399/
Then once you know enough, Gail's http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/
Creating indexes is a multivariable mathematical problem in maximizing performance while minimizing cost so there is no easy get-rich-quick way as there are too many variables.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply