I got a question from one of our UG member. He asked whether we can use SQL Server 2005 performance dashboard with SQL Server 2008. When we tried at the session we failed to implement it in SQL2K8. I was just thinking this today and planned to try this. I found out the reason why we failed to implement and what has changed in the query.
Query tuning is an interested topic in SQL Server. Deepak has written an article on query tuning in our main website, please read the article below and make use of it.
Most of the DBA’s new to query tuning would wonder where to start in order to tune a query. I hope this article would probably guide them in understanding the steps to begin with.
Deepak has written an aritcle on Index Usage in www.sql-articles.com. You can check out the article in the link below.
I have used the Contact table with person schema which is residing in AdventureWorks database for testing the index usage. I am describing the scenarios and places where index seek or scan will be used.
The Database Engine Tuning Advisor is a new tool in Microsoft SQL Server 2005 that enables you to tune databases for improved query processing. Database Engine Tuning Advisor examines how queries are processed in the databases you specify and then it recommends how you can improve query processing performance by modifying physical design structures such as indexes, indexed views, and partitioning. It replaces the Index Tuning Wizard from Microsoft SQL Server 2000, and offers many new features.
If Perhaps an index was created using the wrong columns, or may be users have started querying different data over time, which would require the creation of new indexes. If any of this is true, your databases need tuning. To do that, you need to use the Database Engine Tuning Advisor. Before you can run the Database Engine Tuning Advisor, you need to create a workload file. You get this by running and saving a trace in Profiler (usually by creating a trace with the Tuning template) or the query file(.sql file) or XML format file. Maximum time profiler is considered to be the best, since it will provide you the actual work flow. If you are using profiler then it is best to get this workload during times of peak database activity to make sure you give the advisor an accurate load. First you need to create a workload file to use with the advisor as said above.
Here in this article I’m going to check DTA with Adventureworks db and ProductDescription table. I’ve deleted the index and primary key to check whether DTA is capable to detect this. After this step I’ve executed a select query on the above table and saved the query as a SQL file. I’ve used the above file in DTA as workolad file. When DTA finishes its work, it perfectly detects the missing index and recommended to create the same.
Deleting Index & primary key on ProductDescription table:
I’ve deleted the primary key and index “PK_ProductDescription_ProductDescriptionID” available on the table ProductDescription.
Creating workload file
Save the below query as SQL file. I’ve saved it as workload.sql.
select ProductDescriptionID,ModifiedDate from Production.ProductDescription
where Description like ‘%frame%’
You can also use profiler to get workload file.
Using DTA
Provide the sessionname, databasename and workload file as show in the figure above
Then goto “Tunning Options” tab and you can see the default settings to create index or partitions as per recommendations as show below
Conclusion:
DTA a good utility which can be used to make \ alter the database objects to work more efficient with respect to the given query (workload file). It will recommend for index and partition details.