We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [database-tuning-advisor]

For questions about the SQL Server Database Engine Tuning Advisor - a Microsoft tool that recommends changes to database structures to improve query processing performance.

Filter by
Sorted by
Tagged with
0
votes
0answers
35 views

SQL Server inconsistently reporting missing indexes

I have a stored procedure that is not performing well and I am trying to tune it. I am confused by what I see in SSMS and the Database Tuning Engine Advisor. In SSMS I have 'Include Actual ...
2
votes
1answer
61 views

Should SQL Server statistics be included in versioning?

Recently I ran the SQL Server Tuning Advisor. It suggested to add some statistics. Currently we have no statistics checked in our database versioning. My question is: Should these suggested ...
0
votes
2answers
49 views

Change of index key columns order affects performance

I have the following table in my database: create table [dbo].[tb_StatusLog]( [RequestID] [int] not null, [statusID] [int] not null, [startTime] [datetime] not null, [endTime] [datetime] null, ) ...
1
vote
2answers
103 views

Views created by SQL server Database Tuning advisor

Several years ago I ran Database Tuning Advisor on our database. Then the database wasn't very big so it was easy. I implemented the suggestions, and it created several views with names like ...
1
vote
1answer
132 views

SQL Server Database Tuning Advisor - Temp index being dropped

Recently I ran a trace on our database so I could then plug it into Database Engine Tuning Advisor and see what queries needed to be improved and where there might be a real need for indexes. While ...
2
votes
2answers
947 views

Can I use Database Engine Tuning Advisor for Azure?

I am trying to use "Database Engine Tuning Advisor" for queries in Azure SQL. According to https://docs.microsoft.com/en-us/sql/relational-databases/performance/start-and-use-the-database-engine-...
1
vote
1answer
3k views

Database Tuning Advisor error: …% of consumed workload has syntax errors

Hi I've generated a trace file using SQL Server Profiler and save that trace as a table in SQL server DB. The trace file workload was generated by executing a query directly from SQL Serve Management ...
1
vote
1answer
1k views

Database Tuning Advisor user permissions

I was asked to create an account to use Database Tuning Advisor. I red on technet but I don't understand which permission do I have to give. I tried to login with this new user, but trying to list ...
2
votes
1answer
90 views

Why would an index covering only half of primary key be useful?

We have a primitive junction table to store a many-to-many relation between two other tables, their primary keys being bigint: CREATE TABLE [dbo].[JunctionTable] ( [ID-1] [bigint] NOT NULL, [...
1
vote
1answer
294 views

Database Tuning Advisor Index Recommendations

How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation: creating trace file using ...
5
votes
2answers
5k views

Can I trust the Database Engine Tuning Advisor recommendations?

Our database has become large, and we are trying to optimize performance. Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
8
votes
3answers
5k views

Dropping Hypothetical Indexes

In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes. I have a database that is ...
6
votes
2answers
1k views

Should I rely on Database Engine Tuning Advisor for creating indexes?

How should I rely on SQL Server 2012 Database Engine Tuning Advisor for creating indexes and statistics? I find more often than not, it generates overly complex indexes that (I think) would be more ...
2
votes
1answer
943 views

MS SQL 2008 Express - Database Tuning Advisor

I want to use DTA, but on my website I have an Express Server version. Can I backup my DB on Express server, restore it on my personal computer with Enterprise Version, make some tuning by DTA and ...
5
votes
3answers
511 views

Using DTA vs. evaluating DMVs?

Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA. Form How to determine if an Index is required or necessary I have ...