Menu
I've been having it analyze my queries and pushing the recommendations for some time now. Are there any potential pitfalls with doing this? Would I be better of creating my own indexes by hand?
231
Apply SQL Server index key column best practices Columns with text, image, ntext, varchar(max), nvarchar(max) and varbinary(max) cannot be used in the index key columns. It is recommended to use an integer data type in the index key column. It has a low space requirement and works efficiently.
A unique index is automatically created when you define a primary key or unique constraint: Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view.
In general, tuning indexing for an instance of Directory Server means maintaining only those indexes for which the benefits from faster search processing offset the costs of more update processing and of more space needed.
You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server.
Should one blindly trust the DTA advise and implement all recommendations without considering the implications? No. Should one ignore the DTA and just analyze everything from scratch? No. DTA (or whatever it's later incarnation is called) it's a tool. Like any other tool, is how you use it...
The tunning advisor will give advice based on the workload you present to it. As a general rule, the DTA advice is good quality and solid advice. DTA cannot advise on other types of workload, that was not captured for the analysis replay. It cannot advise on the implications of adding extra indexes on the storage capacity and long term retention policies. It cannot detect stupid queries, it will give advise to improve the access path of those stupid queries but an inteligent human analysis might give advise to improve the query syntax or redefine the requirements.
For someone immersed in a project for months and working with the project data schema and queries every day, the DTA should not reveal any surprise. So it may be said that is a superflous tool in that case. But I'd say that it should still be used exactly to validate that it does not give any stelar new advice.
154
The tuning wizard does a good job for many queries, but there are still plenty of failure modes. Nothing beats a human brain to evaluate the best query plan and the indices required to achieve it. If there's a hot spot, the tuning wizard often turns out to be useless in my experience.
41
It's not easy to answer, the tuning advisor can get you close to what you probably want using a very scientific approach to the index creation - but it is very scientific about what it thinks it needs and can not make the jumps of optimisation that you can from practise.
Index creation and performance tuning is somewhat of an art as well as a science, so whilst it gives you that scientific start of where the issues are, I would never implement them without first reviewing every suggestion and checking whether it really was what I wanted, or whether I could come up with a better / more suitable approach.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
