Adding Indexes to Tables

Question

Adding Indexes to Tables

rjjh78

SSCrazy

Points: 2784
More actions
August 6, 2018 at 3:53 pm

#368632

Hello,
How can I determine if an Index should be added to a table? Should this be done by running each PROC in my database using the Execution Plan? Or is there a better best practice and way to do this?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server-part-1/
https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server-part-2/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

rjjh78 - Monday, August 6, 2018 3:52 PM
Hello,
How can I determine if an Index should be added to a table? Should this be done by running each PROC in my database using the Execution Plan? Or is there a better best practice and way to do this?

Adding indexes is a great way to cause a world of hurt. They can also be a Godsend. The things that you need to possibly add indexes to are things that either take a long time to execute or things that may run "fast" but run a whole lot (thousands of times per hour, for example).

There are a lot of tools out there to help you find such queries. A very smart fellow by the name of Adam Machanic wrote one called sp_WhoIsActive that a whole lot of people rave about. That would be a good place to start to find the queries in need of the most help.

Brent Ozar actually wrote about sp_WhoIsActive and provides a couple of neat tricks you can do with it. He also includes the links as to where to get it from and where the documentation for it is. I've added links for Brent's stuff and the primary link for sp_WhoIsActive below.

Brent's series of sp_Blitz* stored procedures are great helpers for other things, as well.

https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/
https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/
http://whoisactive.com/

--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: 398707 More actions · Answer 3

rjjh78 - Monday, August 6, 2018 3:52 PM
Hello,
How can I determine if an Index should be added to a table? Should this be done by running each PROC in my database using the Execution Plan? Or is there a better best practice and way to do this?

I'd like to suggest you read through my book on query tuning (link down below). Knowing which index to add, what kind of index, row store or columnstore, whether or not make it unique, add INCLUDE columns, this is complex stuff. Should you modify an existing index? Should you leave the indexes alone and focus on the code? There's so much to this that no one is going to give you an adequate answer in a forum post.

"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

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 4

Grant Fritchey - Tuesday, August 7, 2018 6:24 AM
rjjh78 - Monday, August 6, 2018 3:52 PM
Hello,
How can I determine if an Index should be added to a table? Should this be done by running each PROC in my database using the Execution Plan? Or is there a better best practice and way to do this?
I'd like to suggest you read through my book on query tuning (link down below). Knowing which index to add, what kind of index, row store or columnstore, whether or not make it unique, add INCLUDE columns, this is complex stuff. Should you modify an existing index? Should you leave the indexes alone and focus on the code? There's so much to this that no one is going to give you an adequate answer in a forum post.

Hmmm... I'm definitely remiss in my duties... I actually did a review on the execution plans book back in it's early days but haven't ever read the query tuning book. Considering the incredible quality and breadth of knowledge shared in the execution plan book, I'm going to try to make reading the query tuning book a priority. Thanks, Grant.