January 6, 2009 at 1:00 am
Dear All,
Can anyone give me some tips on creating indexes in sql server 2005. when to created index on single/multiple columsn etc...
I need to create some indexes very urgently.
Thanks..San Digo..
January 6, 2009 at 2:44 am
There are few thing you need to keep in mind
-How you query the database? Means which fields do you use in WHERE clause and which fields do you use in SELECT caluse. Fields used in WHERE caluse should be used as index column and fields used in SELECT caluse should be used in INCLUDE list.
-What is the INSERT/UPDATE/DELETE frequency on table. You should be carefull while creating the many indexex on single table.
-And for single or multi column index you should check execution plan for major queries of your application. And Try applying the different column combination for good result.
and at last, there is no proven way to create best index for any application. Index creation always depends upon current database usage.
You can try DTA tool to get the index recommendation for current database usage.
Regards,
Nitin
January 6, 2009 at 6:19 am
nairsanthu1977 (1/6/2009)
Dear All,Can anyone give me some tips on creating indexes in sql server 2005. when to created index on single/multiple columsn etc...
I need to create some indexes very urgently.
Thanks..San Digo..
It's going to be very hard to give you enough information to create indexes "very urgently." But here are a few tips.
First, make sure you've got automatic statistics turned on. It's on by default, but you never know. Second, run some statistics maintenance (you can look that up in BOL or do a search here) to be sure the statistics are up to date. You need these to make indexes workable.
Next, get to know the execution plans for your queries. That's going to tell if you an index is being used properly. Seeks are good, scans are bad. There's a lot more to it, but that'll get you going.
Next, you only get one clustered index per table. Pick it carefully. My own suggestion is to put the cluster on the most frequently access data path. For example, the table is a parent and the ID is always passed in. There's your clustered index. But, let's say the table is a child that is almost always accessed through the FK, there's the cluster.
After you've got the cluster picked, go back to the execution plans. You may be done. If not, you may need some non-clustered indexes. Pick these based on the WHERE criteria, like the last poster said, and the JOIN criteria. You may or may not want to use columns in the INCLUDE statement to try to make a covering index.
Keep your indexes as small as you can. As few fields as possible and the fields themselves should be as small as possible.
I've just given you enough information to be dangerous.
"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
January 6, 2009 at 6:34 am
Grant Fritchey (1/6/2009)
Keep your indexes as small as you can.
But no smaller than necessary. If the index needs to be four columns wide to be useful, making it only 3 columns wide will probably mean it won't be used.
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
January 6, 2009 at 12:25 pm
You say you need to create some indexes very urgently, but how can you be sure indexes are what you need if you don't know how to create them?
What problem are you trying to solve with indexes? What makes you think they will solve that problem?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply