June 25, 2009 at 9:58 am
I'm trying to optimize some stored procedures and using the DTA (database tuning advisor) it is suggesting that I create an index on about 4 fields, then have the include statement with about 25 more fields.
Is this a good idea or not (to have all the fields in the include statement)? The 4 fields in the main part of the index are their because they are either part of a join or a where clause, the other 25 in the include section are there because they are apart of the select clause. Should I proceed with this index? Is there a better way to accomplish this without using the include statement?
TIA!
June 25, 2009 at 10:24 am
It is very difficult to give an answer on this without knowing the structure of the tables, Indexes that are present and query.
But if you just take the face value out of your question, I would not go for a 25 column include Index. Just my opinion. There are other experts here who could give you better advice but they too would want to see the table structure, the query and the present execution plan
-Roy
June 25, 2009 at 10:25 am
Take DTA's recommendations with a large pinch of salt. It tends to suggest wide indexes and way too many of them. I've seen it before now suggest 3 indexes and 8 statistics for a single query (based off two tables)
Without seeing the query, the tables and maybe some data, I can't say for sure, but 25 columns in the include sounds way, way, way too big.
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
June 25, 2009 at 11:35 am
I'd just add on to say, take it's advice with a rather large grain of salt, say about 12 inches square.
It's very intrusive with it's suggestions and, if you test them thoroughly, you'll find that many of them don't work very well.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply