Index

Question

Index

balaji.ganga

SSCrazy

Points: 2280
More actions
September 9, 2010 at 4:06 am

#229087

Hi All,
My database consists of 40 tables. I have analyzed those tables and picked out some
critical tables. I checked those tables. there is no index and statistics is out of age. Each table having 1 lac and above rows. My question is, Now i'm going to create an index for that tables.
How can i select a column to create an index?
If anybody suggest me, it would be greatly helpful for us..
Thanks
Balaji.G

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Without knowing the queries that run against those tables, there's no way to make sensible suggestions on indexes. Take a look at the queries that run frequently against the tables and create indexes that support those queries.

This series may be useful. http://www.sqlservercentral.com/articles/Indexing/68439/

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

DMVs could give you a starting point.

Look up sy.dm_missing_index_* in BOL.

Hope this helps

Gianluca

-- Gianluca Sartori

balaji.ganga SSCrazy Points: 2280 More actions · Answer 3

balaji.ganga

SSCrazy

Points: 2280

September 9, 2010 at 4:43 am

#1218485

Excellent Article Gail

Thanks a lot

Balaji.G

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

Thanks. It's a 3-part series, so check parts 2 and 3 if you haven't already.

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

NickDBA Hall of Fame Points: 3792 More actions · Answer 5

You could also create a workload trace file and run it through the Database tuning Adviser

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

BaddaBing (9/9/2010)
You could also create a workload trace file and run it through the Database tuning Adviser

If you do, test carefully all of its recommendations (same goes for missing indexes) as both are far from perfect.

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

NickDBA Hall of Fame Points: 3792 More actions · Answer 7

Thats a good point, well said. You'd want to judge each recommendation on its own merits and ideally apply in a test/dev environment first to assess its impact.

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

Yeah, I like using trace events to see what's being run against the database and then determine where to spend my time tuning.

"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