Which data to baseline before and after adding an index

Question

Which data to baseline before and after adding an index

webrunner

SSC-Dedicated

Points: 31722
More actions
September 21, 2018 at 8:20 am

#363020

Hello experts,
While troubleshooting a poorly performing query, I was able to have some success in development by adding a table index that was recommended in the execution plan. Now I hope to request a production change to add the index.
Can anyone suggest which stats to baseline before and after adding the index that I could provide to our operations team to document the improvement and (even for myself) see what side-effects elsewhere in the database might actually make the added index cause more issues than it solves?
One database-specific metric that helped me (using SentryOne Plan Explorer) was logical reads, so I can track that. But I'm more concerned about "unknown unknowns" that I as an accidental DBA may not even think to track.
Thanks for any help.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 6 posts - 1 through 5 (of 5 total)

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

Travis Halsell SSC Eights! Points: 896 More actions · Answer 1

Logical reads / execution time

What kind of index are you looking at? Do you have a way to simulate a production workload on your dev environment? My main concern would be how this affects other queries vs just the one you are specifically tuning for.

I would highly recommend taking a look at other existing indexes on the table to see if you can't consolidate a bit. This topic is highly circumstantial. # of indexes, what the other indexes already cover, etc.

Unforeseen things you want to look for after creating the index are locks / blocks depending on how often this table is queried and written to.

If this table is primarily written to and seldom queried, an index could and will slow down write performance (by how much, that is something to determine and measure before and after)

sp_blitzindex is a tool I use to get some data on indexes.

Hope this helps.

webrunner SSC-Dedicated Points: 31722 More actions · Answer 2

Thanks for your reply! I'll look into simulating a production workload in dev - I had totally forgotten about that - as well as the other things you suggested.

Thanks again,
webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

You can also show the execution plans with and without the index.

webrunner SSC-Dedicated Points: 31722 More actions · Answer 4

ZZartin - Friday, September 21, 2018 11:09 AM
You can also show the execution plans with and without the index.

Great - thanks!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 5

webrunner - Friday, September 21, 2018 8:20 AM
Hello experts,
While troubleshooting a poorly performing query, I was able to have some success in development by adding a table index that was recommended in the execution plan. Now I hope to request a production change to add the index.
Can anyone suggest which stats to baseline before and after adding the index that I could provide to our operations team to document the improvement and (even for myself) see what side-effects elsewhere in the database might actually make the added index cause more issues than it solves?
One database-specific metric that helped me (using SentryOne Plan Explorer) was logical reads, so I can track that. But I'm more concerned about "unknown unknowns" that I as an accidental DBA may not even think to track.
Thanks for any help.
- webrunner

Reads and CPU are ok to track and, if you really dig, compile time. But, noneof that actually matters. What really matters is duration. If you don't think so, ask the users. 😉 Not so surprisingly, reads and CPU usually follow improvements in duration (but not always).

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