July 9, 2012 at 10:21 pm
1. Is the Database Tuning Advisor a great tool? Or is it not all that useful in practice? I have read about it extensively. I'm curious about what consultant's think.
2. If I use SQL Server profiler to create a trace that writes to a file of a production database, will it impact the performance of the production database?
July 10, 2012 at 2:50 am
Golfer22 (7/9/2012)
1. Is the Database Tuning Advisor a great tool? Or is it not all that useful in practice? I have read about it extensively. I'm curious about what consultant's think.
BLAAAAARRRFFF!
But seriously, the DTA is a weak tool at best. In general practice, it's actually quite dangerous. I've seen it make completely useless recommendations more often than it makes helpful ones. However, if you feed it a very good representative sample of captured metrics and you can test it against a system with very good statistics, you can get some helpful suggestions for tuning your database rather quickly. However, I've found that getting all that put together doesn't take a lot less time than just tuning the queries myself. But, if you've never spent time learning to tune queries, it could be helfpul.
2. If I use SQL Server profiler to create a trace that writes to a file of a production database, will it impact the performance of the production database?
It will impact performance, but, depending the events and the columns you capture, that impact will be very, very small. Capturing some of the standard events such as SQL Batch Complete or RPC Complete have an almost negligible impact on the server... That is, as long as you're using trace events, a server-side trace, trace set up using T-SQL. Capturing events using the Profiler GUI can have a negative impact on the server above and beyond the trace events impact because of how the GUI gathers information. My recommendation, don't use the GUI against production. Use trace events in a T-SQL script. You can use the GUI to read from a file for later browsing if you want to.
----------------------------------------------------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
July 11, 2012 at 3:20 am
In my experience the DTA tends to recommend covering indexes for practically every query, and if you were to follow its instructions to the letter, you'd end up with a database that was 90% indexes! 🙂 By all means use it as a guide to see where indexes are needed, but implement the indexes yourself rather than relying on the DTA.
July 11, 2012 at 6:07 am
I don't find DTA very useful. If we want to get the missing index recommendations, DMVs (such as sys.dm_db_missing_index_details) are more helpful.
July 11, 2012 at 7:37 am
Divine Flame (7/11/2012)
I don't find DTA very useful. If we want to get the missing index recommendations, DMVs (such as sys.dm_db_missing_index_details) are more helpful.
And the missing index information is not entirely trustworthy. Plus the fact that you can't directly connect the missing index information with any particular query.
----------------------------------------------------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
July 11, 2012 at 8:04 am
Grant Fritchey (7/11/2012)
Divine Flame (7/11/2012)
I don't find DTA very useful. If we want to get the missing index recommendations, DMVs (such as sys.dm_db_missing_index_details) are more helpful.And the missing index information is not entirely trustworthy. Plus the fact that you can't directly connect the missing index information with any particular query.
Absolutely correct. It needs good analysis of the data that has been provided by the DMVs prior to creating any suggested index.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply