Stack Overflow
Stack Overflow

Questions tagged [database-tuning-advisor]

26 questions
43
votes
5 answers

What does "Create Statistics" do in SQL Server 2005?

The Database Tuning Advisor is recommending that I create a bunch of statistics in my Database. I'm something of a SQL n00b, so this was the first time I'd ever come across such a creature. The entry in MSDN was a little obtuse - could someone…
Electrons_Ahoy
  • 36,743
  • 36
  • 104
  • 127
7
votes
2 answers

Database Engine Tuning advisor "Performing analysis" fails with STOPPED

Whenever I run Database Engine Tuning advisor I get no recommendations (even when I set up scenarios with table scans that would be avoided by an index). I see "Performing analysis" shows as "Stopped" with no errors or explanation. What is the cause…
Ken Lassesen
  • 125
  • 9
7
votes
3 answers

Should I delete Hypothetical Indexes?

I have noticed that Hypothetical indexes exist in a certain database. I have searched around and it appeared that this type of indexes are created by Tuning Advisor and are not always deleted. There are several topics including official…
gotqn
  • 42,737
  • 46
  • 157
  • 243
3
votes
3 answers

Including nvarchar(max) columns in select clause dramatically increases execution time

I have simple table that consists of the columns and indexes as shown at below. Here is my index created upon recommendation of sql database engine tuning advisor.It includes all columns. CREATE NONCLUSTERED INDEX…
erhan355
  • 806
  • 11
  • 23
3
votes
0 answers

Database Engine Tuning Advisor and sp_execsql

I'm developing an ASP.NET app using Entity Framework 6 and Sql Server 2012 and want to optimise the indexes. EF6 wraps the queries in a sp_execsql call which seems to thwart the Database Engine Tuning Advisor. I'm capturing all my queries with Sql…
Ben Fidge
  • 203
  • 3
  • 10
3
votes
3 answers

Database Engine Tuning Advisor Crashes Constantly

Microsoft SQL Database Engine Tuning Advisor seems to crash constantly for me... on multiple different servers, for multiple different databases, and throughout multiple different versions of SQL server (and DTA)... I know this is probably a…
Novox
  • 774
  • 2
  • 7
  • 24
2
votes
0 answers

DTA - Error while tuning using a Test Server

I have 2 servers, both have SQL Server 2016 Developer edition installed. I want to tuning the production server using a test server, a method described here - Reducing the Production Server Tuning Load. Basically it uses the Test Server to Tune the…
Deepan
  • 95
  • 7
2
votes
0 answers

"A heap has been corrupted" error when running Database Tuning Analyser?

I am running SQL database tuning analyser to investigate an issue with particular SQL queries running extremely slowly and at ~20% I consistently get the following error: Unhandled exception at 0x777daa3c in dtaengine.exe: 0xC0000374: A heap has…
Flyk
  • 245
  • 2
  • 8
  • 19
2
votes
0 answers

Database Tuning Advisor: Naming of Recommended Indexes and Statistics

When using the SQL Server Database Tuning Advisor, it generates default names for the objects (indexes, statistics and views) it recommends. Here are a few: These names are horrible. Is there an efficient method to assign reasonable names to all…
usr
  • 168,620
  • 35
  • 240
  • 369
1
vote
1 answer

Need suggestion for POSTGRES Tuning

I am in a need to run the few complicated queries frequently on my database, one of my queries is taking around 57 sec to execute, I am trying a way to tune my Postgres database, so, I can cut the execution time to half or more than that. I have…
Sujeet Chaurasia
  • 97
  • 8
1
vote
1 answer

Dropping Unused Indexes

I am referring the Unused Index Script blog. I got to know that if Seek = 0, Scan = 0, Lookup = 0, User Update = 0 then we should delete index as it is not needed. The unused script gives me many indexes but should I delete all those indexes? Can…
Yatin
  • 21
  • 5
1
vote
1 answer

Mysql 5.7 performance tuning. Stored procedure taking too much time to respond

Mysql stored procedure taking too much time after migration to new server. Stored procedure taking too much time to return results. My cnf file as follows [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user =…
rakesh pakkam
  • 144
  • 11
1
vote
2 answers

Database Engine Tuning Advisor "Failed to parse XML file"

When loading a .sql file in Database Engine Tuning Advisor you get "Failed to parse XML file"
Leo Gurdian
  • 2,109
  • 17
  • 20
1
vote
1 answer

Partitioning strategy : Full partitioning vs aligned partitioning in Sql Server (tuning options in database engine tuning advisor)

In Database engine tuning advisor, there is an option of Partitioning strategy: Full partitioning vs aligned partitioning. What is meant by full partitioning and aligned partitioning and what are its pros and cons?
mianimraniqbal
  • 61
  • 5
1
vote
0 answers

SQL Database Engine Tuning Advisor returns recommendations but Tuning Log is empty

Please can someone explain to me why the tuning log returns no results even though the tuning advisor suggests lots of recommendations?! I'm new to this tool so think there is something obvious that I'm missing. Many thanks in advance for your…
Mmccoy
  • 11
  • 1
1
2