April 12, 2012 at 12:21 am
Hi , im using sql server 2008 r2 on windows server 2008 r2....
im recently shifted to dba ,the very first my manager asked me to tune the queries and stored procedures in the test server.
im doing by running the query in ssms and capture it in profiler and then running the workload in dta
but
i seen another one in online videos
by running the query in ssms and by right clicking on the query selecting dta will also lead to tuning.
1) can u guys tell me which is best option?
and
2)
is there any way to tune the whole database instead of running each and every query once and get the recommendations??
thanks
April 12, 2012 at 4:24 am
First, let's note, the DTA is notorious for offering bad advice, so be very careful in implementing it's suggestions. Test your stuff very carefully.
The best way to run the DTA is to capture a workload from your production server and then use that to play back. That way you get more realistic calls and parameter values which will lead to a better set of recommendations from the DTA. This also means you should be using the production data with the DTA in order to arrive at more realistic suggestions.
Personally, I don't recommend using it. Instead, capture the query metrics using a server-side trace, then load those metrics into a database back in your development environment where you can run queries to identify the poorest performers and then tune them by hand. You'll do a better job than the DTA, even as rookie. If you need help, take a look at the books in my signature. The 2008 version will be completely applicable to 2005 in most regards.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 12, 2012 at 1:43 pm
Grant Fritchey (4/12/2012)
Personally, I don't recommend using it. Instead, capture the query metrics using a server-side trace, then load those metrics into a database back in your development environment where you can run queries to identify the poorest performers and then tune them by hand. You'll do a better job than the DTA, even as rookie. If you need help, take a look at the books in my signature. The 2008 version will be completely applicable to 2005 in most regards.
I agree, I have used the DTA but typically it is just a quick overview.
I have not tried the server side trace. Thanks Grant I will try that next week on a database.
I have a tendency to watch for blocking, high cpu procedures, fragmentation, forwarded records, missing indexes, non used indexes (overhead) and various other things. From this I first i look at if some regular maintenance should be scheduled to keep stats up to date or reduce index fragmentation. Then the missing indexes and this I have seen fix 90% of performance issues but like the DTA you need to evaluate the suggestions before implementing. Then I manually flag procedures for more specific tuning or rewrites.
April 12, 2012 at 1:51 pm
DBA_Dom (4/12/2012)
Grant Fritchey (4/12/2012)
Personally, I don't recommend using it. Instead, capture the query metrics using a server-side trace, then load those metrics into a database back in your development environment where you can run queries to identify the poorest performers and then tune them by hand. You'll do a better job than the DTA, even as rookie. If you need help, take a look at the books in my signature. The 2008 version will be completely applicable to 2005 in most regards.I agree, I have used the DTA but typically it is just a quick overview.
I have not tried the server side trace. Thanks Grant I will try that next week on a database.
I have a tendency to watch for blocking, high cpu procedures, fragmentation, forwarded records, missing indexes, non used indexes (overhead) and various other things. From this I first i look at if some regular maintenance should be scheduled to keep stats up to date or reduce index fragmentation. Then the missing indexes and this I have seen fix 90% of performance issues but like the DTA you need to evaluate the suggestions before implementing. Then I manually flag procedures for more specific tuning or rewrites.
If you're 2008 or better, skip the server-side trace and instead set up extended events. They're more light weight, making for easier & faster data collection.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 25, 2012 at 8:41 am
There are several free tools you can use to analyze your trace files and quickly identify problematic queries. One of the popular ones is Qure Analyzer, available here:
Free SQL Server Trace Analysis Tool
For actual tuning recommendations across your entire workload (including executable scripts for applying the recommendations and benchmarks showing the performance improvements), check out the companion product called Qure Optimizer. It's not free, but has a free trial that is pretty powerful. The product espouses an approach called "workload tuning" -- automatically tuning millions of queries in one go. Worth checking out when the manual, query-by-query method becomes overwhelming.
Hope this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply