SQL Server Performance Forum – Threads Archive
Indexes – yet another question
Ive read the books online and the pages on this great site. I am now trying to determine why my server’s performance is horrible. I am now trying to take theory (what i have read) and translate it into reality. The index tuning wizard improved my databse’s speed, but a few seem questionable and I would like to i understand it.this:http://www.sql-server-performance.com/clustered_indexes.asp suggests both avoid redundant indexes and avoid multi-column clustered indexes. Which should be by clustered index (all fields used often)
CREATE CLUSTERED INDEX [IX_Host1] ON [dbo].[Host]([HostId])
OR
CREATE CLUSTERED INDEX [IX_Host1] ON [dbo].[Host]([HostId], [source], [field3], [field4] ) The tuning wizard created the single column index as clustered and the second index as non-clustered. Can anyone explain why this is better? Context for the question:
My DB has a lot of writes and few reads. I has a table, called host, that contains information on computers. This table is refrenced often, most often based on the ‘hostid’, a sequential identifier for the host. Since several systems generate the data used in this DB it is not truely sequential on its’ own. There are multiple ID ‘1’, multiple ID ‘2’, and so on. The hostid paired with the ‘source’ creates a unique refrence. Because of this EVERY refrence to the host table will pair the required information with the ‘source’ field. — Tom
Motivation: If pretty poster and a cute saying are all that it takes to motivate you, you probably have a very easy job. The kind robots will be doing soon.
When I say avoid redundant indexes, what I was going for was to not duplicate the same index. The most common cause of this is if you add an index to a column that already has index automatically created for it, such as a primary key or unique key. When I say you should avoid using clustered indexes on multiple columns, the reason for this is because it can make the clustered index quite large, which can often hurt performance because of the additional I/O that is needed to read through the index. But in both cases, these are general rules of thumb, and in some cases, there may be good reasons to break this advice. Now let’s take a look at your particular questions about the two clustered indexes suggested by the Index Wizard. While its hard to say exactly why the Index Wizard came up with its particular recommendations (other than to say it is based on the trace it was fed) the clustered index on the hostid column is most likely because it had more queries run against is that used the hostid column in the WHERE clause. In the second case, where it recommended a non-clustered index (I assume your example above should read non-clustered, not clustered) is because there were additional queries that were found in the work load that used two or three of the columns in the WHERE clause, and I am guessing, that is using this index as a covering index. This is only speculation, but an educated guess. You are probably wondering if there is any problem with the hostid column being indexed more than once. No, not really. This is because each index is serving a different purpose, and seeing redundant indexes like this is common, and is different than the example where I suggest you shouldn’t have duplicate indexes on the same column. I hope this is clear. If not, let me know, and I will try to do a better job. Using the Index Wizard is just your first step. You will want to take many more steps after this one. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>