identify missing index for query

Question

identify missing index for query

newbie389

Valued Member

Points: 69
More actions
January 19, 2018 at 2:55 am

#325501

hi
im trying to determine if a new query benefits from adding an index. is there tool for sql server to determine missing index ? and analyse how much permanence gain will be there?
thanks in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Start by typing "missing index" into your favourite search engine. There'll be plenty of material there for you to consider. Post back if there's anything in particular that you don't understand.

John

newbie389 Valued Member Points: 69 More actions · Answer 2

John Mitchell-245523 - Friday, January 19, 2018 3:16 AM
Start by typing "missing index" into your favourite search engine. There'll be plenty of material there for you to consider. Post back if there's anything in particular that you don't understand.
John

Hi John I have ran db tuning advisor it has recommended 21 indexes to be created and estimated 20% improvement performance gain , query takes 23 minutes at the moment ,im concerned creating that many index may impact existing production. is it the case for us to create and drop indexes if they introduce performance issues?
is it possible to predict if new index will introduce performance issues for other part of the system ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

newbie389 - Friday, January 19, 2018 4:13 AM
John Mitchell-245523 - Friday, January 19, 2018 3:16 AM
Start by typing "missing index" into your favourite search engine. There'll be plenty of material there for you to consider. Post back if there's anything in particular that you don't understand.
John
Hi John I have ran db tuning advisor it has recommended 21 indexes to be created and estimated 20% improvement performance gain , query takes 23 minutes at the moment ,im concerned creating that many index may impact existing production. is it the case for us to create and drop indexes if they introduce performance issues?
is it possible to predict if new index will introduce performance issues for other part of the system ?

Can you post up the "actual" (not estimated) execution plan of your query, as a .sqlplan attachment? An estimated plan would suffice until you've had time to run the query.
Folks will help you decide on which indexes are likely to make a decent difference to performance, based on the execution plan.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Don't run DB tuning advisor. Use the missing index DMVs instead. Query tuning isn't easy - I hope you don't mind my saying that I think you might be in slightly over your head here. Do you have a test system? You should try any changes there first. Feel free to post the query and the query plan (a .sqlplan file) and we'll have a look and see whether there are any obvious improvements you can make. Post the actual execution plan, please - click on the Include Actual Execution Plan button and then run the query.

John

Thom A SSC Guru Points: 99126 More actions · Answer 5

newbie389 - Friday, January 19, 2018 4:13 AM
John Mitchell-245523 - Friday, January 19, 2018 3:16 AM
Start by typing "missing index" into your favourite search engine. There'll be plenty of material there for you to consider. Post back if there's anything in particular that you don't understand.
John
Hi John I have ran db tuning advisor it has recommended 21 indexes to be created and estimated 20% improvement performance gain , query takes 23 minutes at the moment ,im concerned creating that many index may impact existing production. is it the case for us to create and drop indexes if they introduce performance issues?
is it possible to predict if new index will introduce performance issues for other part of the system ?

Are we talking about 21 indexes on a single table here? I'll admit, that does seem a little excessive.

As for checking for performance, the best way is to test. Do you have a Development/Test Environment on which to do so? If not, you might want to consider it; they're invaluable.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

newbie389 - Friday, January 19, 2018 4:13 AM
Hi John I have ran db tuning advisor it has recommended 21 indexes to be created and estimated 20% improvement performance gain , query takes 23 minutes at the moment ,im concerned creating that many index may impact existing production. is it the case for us to create and drop indexes if they introduce performance issues?
is it possible to predict if new index will introduce performance issues for other part of the system ?

Both the missing index information and the tuning advisor (even more so the tuning advisor) are just suggestions. They are not hard measures. There is no guarantee that they will help, at all, let alone the 20% suggested. In fact, they may very well hurt performance. You should only, ever, take this as suggestions for testing and then evaluate them prior to any release to production.

Strong suggestion, get someone in to help who already knows how to do this. Secondarily, pick up my book (link in the signature).

"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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Grant Fritchey - Friday, January 19, 2018 5:10 AM
newbie389 - Friday, January 19, 2018 4:13 AM
Hi John I have ran db tuning advisor it has recommended 21 indexes to be created and estimated 20% improvement performance gain , query takes 23 minutes at the moment ,im concerned creating that many index may impact existing production. is it the case for us to create and drop indexes if they introduce performance issues?
is it possible to predict if new index will introduce performance issues for other part of the system ?
Both the missing index information and the tuning advisor (even more so the tuning advisor) are just suggestions. They are not hard measures. There is no guarantee that they will help, at all, let alone the 20% suggested. In fact, they may very well hurt performance. You should only, ever, take this as suggestions for testing and then evaluate them prior to any release to production.
Strong suggestion, get someone in to help who already knows how to do this. Secondarily, pick up my book (link in the signature).

Hah! I'll offer an opinion on both books, since they're in front of me right now. Buy them!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 8

ChrisM@Work - Friday, January 19, 2018 5:18 AM
Hah! I'll offer an opinion on both books, since they're in front of me right now. Buy them!

EXCELLENT!

Actively in the middle of rewriting both of them right now. The updates are going to be amazing, especially the Execution Plans book.

"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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Grant Fritchey - Friday, January 19, 2018 7:05 AM
ChrisM@Work - Friday, January 19, 2018 5:18 AM
Hah! I'll offer an opinion on both books, since they're in front of me right now. Buy them!
EXCELLENT!
Actively in the middle of rewriting both of them right now. The updates are going to be amazing, especially the Execution Plans book.

The goalposts never stop moving. I've been playing with columnstore indexes over three days or so, first time in anger, and the plans are well strange.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

ChrisM@Work - Friday, January 19, 2018 5:18 AM
Grant Fritchey - Friday, January 19, 2018 5:10 AM
newbie389 - Friday, January 19, 2018 4:13 AM
Hi John I have ran db tuning advisor it has recommended 21 indexes to be created and estimated 20% improvement performance gain , query takes 23 minutes at the moment ,im concerned creating that many index may impact existing production. is it the case for us to create and drop indexes if they introduce performance issues?
is it possible to predict if new index will introduce performance issues for other part of the system ?
Both the missing index information and the tuning advisor (even more so the tuning advisor) are just suggestions. They are not hard measures. There is no guarantee that they will help, at all, let alone the 20% suggested. In fact, they may very well hurt performance. You should only, ever, take this as suggestions for testing and then evaluate them prior to any release to production.
Strong suggestion, get someone in to help who already knows how to do this. Secondarily, pick up my book (link in the signature).
Hah! I'll offer an opinion on both books, since they're in front of me right now. Buy them!

I did a review right here on SSC of Grant's first release of his RedGate-provided book on execution plans and also have a copy (thank you Grant) of his other book. I don't have many physical books anymore but I have these two. There's an amazing amount of incredibly useful information in both books and I strongly second Chris' recommendation of "BUY THEM"! They're an incredible read and worth every penny as "must have" reference books.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 11

ChrisM@Work - Friday, January 19, 2018 7:13 AM
Grant Fritchey - Friday, January 19, 2018 7:05 AM
ChrisM@Work - Friday, January 19, 2018 5:18 AM
Hah! I'll offer an opinion on both books, since they're in front of me right now. Buy them!
EXCELLENT!
Actively in the middle of rewriting both of them right now. The updates are going to be amazing, especially the Execution Plans book.
The goalposts never stop moving. I've been playing with columnstore indexes over three days or so, first time in anger, and the plans are well strange.

Yeah, lots and lots of stuff added to both books about columnstore and batch mode. If you have specific questions, don't hesitate to reach out.

"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