I am using SQL Server 2008 and I need to optimize my queries.For that purpose I am using Database Engine Tuning Advisor.
My question is can I check the performance of only one SQL query at a time or more than one suing new session?
-
You should also get your hands dirty with SQL Profiler - msdn.microsoft.com/en-us/library/ms181091.aspxStuartLC– StuartLC2011-02-14 10:31:15 +00:00Commented Feb 14, 2011 at 10:31
-
But what I am asking is that.... can I check the performance of only one SQL query at a time or more than one suing new session?Sachin Patil– Sachin Patil2011-02-14 10:36:36 +00:00Commented Feb 14, 2011 at 10:36
-
But what I am asking is that.... can I check the performance of only one SQL query at a time or more than one using new session ?Sachin Patil– Sachin Patil2011-02-14 10:37:00 +00:00Commented Feb 14, 2011 at 10:37
Add a comment
|
2 Answers
To analyze one query at a time right click it in the SSMS script window and choose the option "Analyze Query in DTA" For this workload select the option "keep all existing PDS" to avoid loads of drop recommendations for indexes not used by the query under examination.
To do more than one first capture a trace file with a representative workload sample then you can analyse that with the DTA.
1 Comment
Sachin Patil
but how to capture a trace file with trace file with a representive workload sample?
There are simple steps that must follow when writes SQL Query:-
1-Take the name of the columns in the select query instead of *
2-Avoid sub queries
3-Avoid to use operator IN operator
4-Use having as a filter in in Group By
5-Don not save image in database instead of this save the image Path in database Ex: saving image in the DB takes large space and each time needs to serialization when saving or retrieving images in the database.
6-Each table should have a primary key
7-Each table should have a minimum of one clustered index
8-Each table should have an appropriate amount of non-clustered index Non-clustered index should be created on columns of table based on query which is running
9-Following priority orders should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d)SELECT clause
10-Do not to use Views or replace views with original source table
11-Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
12-Remove any adhoc queries and use Stored Procedure instead
13-Check if there is atleast 30% HHD is empty it will be improves the performance a bit
14-If possible move the logic of UDF to SP as well
15-Remove any unnecessary joins from the table
16-If there is cursor used in a query, see if there is any other way to avoid the use of this (either by SELECT … INTO or INSERT … INTO, etc)
