June 4, 2012 at 12:55 pm
I'm not sure, but a while ago I heard that beginning with 2008 version it is possible to somehow simulate an impact of an index on a query performance without actually creating the index in the schema. Can anyone confirm ar dismiss this? maybe provide some details, implementation pointers, if possible?
Thank you.
June 4, 2012 at 3:49 pm
Creating new indexes can have some negative impact when adding or updating data in the table. Often times this is negligible.
As for SQL Server offering impact estimates on specific queries, I don't know of any way to designate a hypothetical index. However, if you use the estimated query plan feature, you can get advice as to indexes to add that could improve performance.
I use that feature when I am stumped as to what could help a query perform better.
The other option is to stage your database on a test server/instance, add the index and compare execution statistics. Ultimately, that may be the most reliable indication as to how much impact an index could have.
June 5, 2012 at 9:29 am
thank you for your answer.
June 5, 2012 at 9:47 am
It's been possible since SQL 2000 or before, but it's undocumented and needs options that you, as a user, don't have. It's how DTA works. It creates hypothetical indexes and then asks the QO to optimise queries as if those indexes really existed.
Better to just create the indexes on a test server and run your tests there.
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 5, 2012 at 11:26 am
GilaMonster (6/5/2012)
It's been possible since SQL 2000 or before, but it's undocumented and needs options that you, as a user, don't have. It's how DTA works. It creates hypothetical indexes and then asks the QO to optimise queries as if those indexes really existed.Better to just create the indexes on a test server and run your tests there.
Thanks, Gail. How do you know what type of user I am? 😉
Pls tell me what *options* I need to run this DTA. I have a sysadmin role on SQL Server 2008 R2 Enterprise Ed. You say it's undocumented, any way to get any literature on creating these *hypothetical indexes*?
Thanks again for your time,
Sergei Z
June 5, 2012 at 11:58 am
Sergei Zarembo (6/5/2012)
GilaMonster (6/5/2012)
It's been possible since SQL 2000 or before, but it's undocumented and needs options that you, as a user, don't have. It's how DTA works. It creates hypothetical indexes and then asks the QO to optimise queries as if those indexes really existed.Better to just create the indexes on a test server and run your tests there.
Thanks, Gail. How do you know what type of user I am? 😉
Easy, you're not an application that was written by Microsoft and included with SQL Server.
Pls tell me what *options* I need to run this DTA.
Start -> Microsoft SQL Server 2008 -> Performance Tools -> Database Engine Tuning Adviser
I have a sysadmin role on SQL Server 2008 R2 Enterprise Ed. You say it's undocumented, any way to get any literature on creating these *hypothetical indexes*?
You can profile trace what DTA does, but I don't think it will be of any use. It's designed for a tool to use, not a person.
Best bet for users (ones who aren't tools written by Microsoft) is to create the indexes on a test DB and run their tests.
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 5, 2012 at 12:03 pm
RE: Best bet for users (ones who aren't tools written by Microsoft) is to create the indexes on a test DB and run their tests.
Thanks, I'll follow your advice.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply