Suggested Index With All fields? | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Suggested Index With All fields?I have a data warehouse that I inherited. When I inherited this data warehouse it had no indexes to speak of on 90% of the tables in the DB. As I worked on rectifying this I got some of the queries the users used on the DB and looked at the data relationships to best-guess where the indexes should be on the tables. I was allowed to redesign the tables and reload all of the data on a new server, from scratch.
The data came in text files from a mainframe that were in a format of one line has all parent and child records. There were varying multiple occurrences of child records. The parent record section had a field which told me the number of occurrences for a given child record and I parsed out the line, for X number of bytes to get each record. All child records went into separate tables with a key field to the parent table. In some cases there was a natural key, but in most cases I used a surrogate key.
I put a clustered index on everything, using what should be the key field between all of the tables in a given data set, whether it was a natural key or independent key. I put additional indexes on the tables according to my best guess from available usage data. The performance improvement was dramatic. Now that it has been in production a while, I am looking at the index usage statistics and SQL Server’s recommendations for indexes to verify my guessing and improve the design, if possible.
What I have found is that SQL Server recommends some indexes that are a combination of certain fields plus all the rest of the fields in that table to be included, with the Include clause. Curiously, the surrogate key for a given table may not be included in this index, but it IS the ‘Includes’ section of said index. I realize that I should pay attention to this since it is using real stats of how the data is being used, but if I see and index recommendation like this, does that signify a need to change my clustered index to be this index of a combination of fields, less the includes?
I’m also trying to decide if I should be dropping some of my existing indexes, if they have low usage, to put in place the indexes SQL Server suggests. I need to watch how much space I am allocating to indexes, as, for instance, with a 22G table I am using 10G in indexes already, and if I were to create one of the indexes it suggests, with every field included, that index would match the size of the table.
Welcome to the forums.
Are you using Enterprise Edition, if so then you can collect trace using PROFILER or set server side trace where the trace can be submitted to database tuning advisor for recommended indexes.
There are few links in web to talk about indexes:
Personally, I’d be very cautious about using the Tuning Advisor. It frequently gives very careless recommendations, and it’s not uncommon to see what you have witnessed – an index with some key columns, plus every other column in the table present as an INCLUDED column. In other words, you’re duplicating your entire table in the index, possibly several times if it makes similar recommendations with the columns in different orders. You seem to me to have adopted a good starting method, and reviewing the indexes now some stats have accumulated is sensible. I didn’t totally follow what you were saying about the included columns and clustered indexes – could you clarify? One thing to be aware of (if you didn’t already know), is that if your table has both clustered and non-clustered indexes on it, every non-clustered index will also automatically contain the columns that are present in the clustered index; there is no need to add them again (in fact, if you try they’ll be ignored). You won’t see these columns displayed in the index properties – you just have to know and understand that they will be there in the non-clustered indexes. (If you have no clustered index, then the non-clustered indexes will only contain the columns that you specifically create)
I wouldn’t agree with your comments about DTA that tool in SQL 2008!!!
I don’t know the tool in 2008, but out of priniciple I would not trust blindly any tool.
well not to follow it blindly…but it will be a starting point to see the index suggestions…
Sorry Satya, but I still hate DTA. It suggests way too many indexes, generally with far too many key columns and covering columns, which if you implemented them would just bloat the database and very probably impede performance rather than improving it. For an inexperienced user (and don’t forget, that’s who the DTA recommendation was being given to), they don’t have the knowledge to decide whether the index recommendations are good or not, so what are they to do? They can either ignore them, in which case there was no point in running the DTA at all, or implement them all (which we both agree is bad, blindly following a tool). Any experienced DBA should be able to make informed decisions about indexes using query plans and profiler, so in my opinion the DTA is really a redundant tool that is of no real value.
Oh well I’m not saying I like it either… its good to have an opinion and support the cause good to see your insights
I don’t hate DTA.
Is a suggestion tool. You can test index suggestion, compare with your own analysis, and so on.
Of course if you leave all the work to DTA, well… is not a good idea.