Brad M McGehee

Comments

1. Matt Laxton said March 1, 2012, 3:49 am:

   DMVs and query plans are usually my first points of call. I do use the Database Engine Tuning Advisor sporadically, but with a pinch of salt.

2. Zach M said March 1, 2012, 9:44 am:

   SQL Sentry Performance Advisor is my primary tool.

   Still use tracing, execution plans, DMVs frequently

3. Kimberly said March 1, 2012, 3:01 pm:

   I’m just scratching the surface of performance tuning, but so far I have used SQL Server built in reports and DMVs.

4. Gokhan Varol said March 1, 2012, 3:33 pm:

   DMV’s. I don’t like leaving SSMS environment, I simply don’t like needing to use mouse etc to when I need to get something fast, and all the tools I have tested for monitoring need time to refresh, did not understand our multiple mounting points etc, they were not trusted in our environment, too many false alarms they generated etc. So far I have not seen any tool that can grab me the data I need compared to DMVs.

5. IJeb Reitsma (subscribed) said March 2, 2012, 3:13 am:

   I use many different tools. I use these the most:
   - Extended events
   - SQL Trace
   - Execution plans
   - Plan cache
   - DMO’s
   - Management Data Warehouse

6. Fabricio Lima said March 3, 2012, 11:45 am:

   I use traces, dmvs and execution plans to identify and tune slow performance queries.

7. John Pertell (subscribed) said March 3, 2012, 2:25 pm:

   I use Adam Machanic’s sp_WhoIsActive and other DMVs to identify issues. I read execution plans with SQL Sentry Plan Explorer

8. Stephen Whitehead said March 4, 2012, 6:27 pm:

   I use a combination of DMV’s, traces, execution plans, SQL Server Consolidated Monitoring Tool (Jeff Carrington & Rodney Landrum).

9. Nate said March 5, 2012, 12:24 pm:

   I primarily use SSMS, Profiler and SQL Sentry Plan Explorer – SSMS and Profiler because it comes with SQL Server and Plan Explorer because it makes deciphering execution plans so much simpler.

10. steve said March 6, 2012, 2:29 pm:

    DMV’s, execution plans, SQL profiler traces, and a couple home grow scripts mostly.

11. Hardik said March 12, 2012, 5:33 am:

    I mostly use DMVs to get retrieve worst performing queries by I/0, CPU, execution count. I also use SQL profiler traces to identify slow running queries. Once identified I would check execution plan, DTA, query optimization to further troubleshoot slow running queries.

12. Brad Griffin said March 14, 2012, 12:08 am:

    I have found that SQL Index Manager has helped significantly resolve a number of performance issues by allowing us to reindex/rebuild indexes on the fly….

13. Dirk Hondong said March 17, 2012, 9:47 am:

    I´ve implemented Adam Machanic´s “Who is Active” on every SQL Srv I have to take care of.
    That´s often my first approach if someone informs me that a particular query doesn´t perform very well.
    Then I use the typical DMVs to pull out long running queries.
    Sometimes, if it isn´t a particular query but a whole workload, I´ll use the PerfStatScripts, pull out a SQL Trace and PerfCounters and just consume every little information with SQLNexus to get these nice Reports.
    That helps a lot to pinpoint particular issues, mostly bad perferoming queries.

14. Randy Wilde said March 23, 2012, 5:17 pm:

    DMV’s are my first go to. I also use profiler traces and execution plans.

15. Nathan said March 27, 2012, 4:41 am:

    I have used a lot of Glen Berry’s scripts to find performance problems. I have used the built in reports with 2008 R2 and I have used sqlnexus as it is a great addition to sqldiag and tracing to identify problems with my servers. Other than that I use an open source monitoring tools such as Cacti to perform queries on the databases then report back to a central hub for analysis. It is hard finding time to monitor everything and even when your ‘supposedly’ monitoring everything you still miss something or you find that you need to monitor more! But thats monitoring in a nutshell. Brad your awesome!

    

16. Rajaram Aiyar said March 27, 2012, 4:44 am:

    I use a combination of tools like the Sql Profiler along with system monitor and show plan stats with index tuning wizards, but the one I found the best was the latest sql monitor ( trial version) which truly is awesome

17. Paul Tormey (subscribed) said March 27, 2012, 5:49 am:

    The most useful tool that I have is Idera’s Diagnostic Manager.

    Sorry about that but that’s where my small budget was spent.

18. Pat Jones said March 27, 2012, 5:53 am:

    I use the freely downloadable Performance Dashboard reports (which use the dmv) and have them available as custom reports on most servers. They give most expensive queries in a friendly form and I can then take the worst offenders and examine the execution plans and apply various techniques for improvement.
    The Microsoft Tuning and Optimizing Queries course was very useful too.

19. Robert said March 27, 2012, 6:48 am:

    As funds are VERY limited in my current positions, query execution plans are my “go-to” diagnosis tool. I have used SQL Profiler and Query Tuning Advisor in the past and they usually do an excellent job identifying the “hot-spots” but then you have to figure out the “cure” so to speak. I am starting to learn to use DMVs more and more as well.

20. Jim Young (subscribed) said March 27, 2012, 7:45 am:

    Fortunately we’ve carved out time for performance tuning so that the primary sources of identifying slow queries are no longer the telephone and email. We’re still getting traction, but we use a combination of:
    - DMVs (for missing index, unused index & heap identification)
    - SQL Profiler (filtered to look for the CPU/IO/Duration heavyweights)
    - Execution Plans
    - Tuning Advisor (also with a pinch of salt & caution)
    - PerfMon
    - Red Gate SQL Monitor (although we use it more for alerting at present)

21. JamesLawrence said March 27, 2012, 8:02 am:

    We use Idera SQL Diagnostic Manager.

22. Chris McGowan said March 27, 2012, 8:07 am:

    SQL Sentry Performance Advisor and a selection of DMV queries taken and modified from “Performance Tuning with SQL Server Dynamic Management Views” book by Louis Davidson and Tim Ford.

23. COOLDBA said March 27, 2012, 9:14 am:

    Native tool like profiler and query plan.

24. Kanaka Thamizhavel said March 27, 2012, 10:49 am:

    I use a mix of DMVs, SQL Profiler and DTA.

25. Chris Walker said March 27, 2012, 4:20 pm:

    Mainly profiler. I check for queries using high cpu and/or high disk I/O.
    Check the plan for table scans or lots of index scans running a long time. also the join types.
    Normaly it is lack of indexes.
    Then I would check small queries running many many times.
    Also uniqune sql’s not using plan chaching.

26. Calico said March 27, 2012, 6:40 pm:

    To tune slow queries:

    - SSMS (event monitor)
    - Execution plan

    To identify slow queries

    - Profiler +

27. Francesco Quaratino said March 28, 2012, 5:16 am:

    basically .. I use a mix of all these tools:

    - SQL Server Profiler
    - Qure Workload Analyzer
    - SQL Sentry Plan Explorer

28. Rajan (subscribed) said March 29, 2012, 2:49 am:

    - Dyamic Management Views to identify the unused indexes for droping them, Finding required indexes to optimize query execution.

    - Execution Plans – to prefer Index seeks rather using table scan and index scan. This techinique is used only for tables having more rows.
    -Verifying the Table zise and determining the requirement of Indexes :-Tables having less rows are not recommented for the creation of Idexes and tables which are modified very often is not indexed since the Index Maintenance requires more system resources.

    –Locking :- Appling proper locking helps to improve the performace of queries.
    – Maintaing Short transactions in stored pocedures, since small transactions won’t create much loging and it considerable reduce IO bottle neck.
    - SQL Profiler – filtered to look helps to indentify the usage of CPU/IO/Duration etc.
    – Automatted log shipping process will help to improve the performace of SQL server.
    –Verifying the Database status, Auto-Grow, Recovery model, Logging mode etc.

29. Rajan (subscribed) said March 29, 2012, 2:51 am:

    i) Used Dyamic Management Views to identify the unused indexes for droping them, Finding required indexes to optimize query execution.

    ii) SSMS Execution Plans – to prefer Index seeks rather using table scan and index scan. This techinique is used only for tables having more rows.

    iii) Verifying the Table zise and determining the requirement of Indexes :-Tables having less rows are not recommented for the creation of Idexes and tables which are modified very often is not indexed since the Index Maintenance requires more system resources.

    iv) Locking :- Appling proper locking helps to improve the performace of queries.
    v) Maintaing Short transactions in stored pocedures, since small transactions won’t create much loging and it considerable reduce IO bottle neck.
    vi) SQL Profiler – filtered to look helps to indentify the usage of CPU/IO/Duration etc.
    vii) Automatted log shipping process will help to improve the performace of SQL server.
    viii) Verifying the Database status, Auto-Grow, Recovery model, Logging mode etc.

30. Jon Russell said April 2, 2012, 10:22 am:

    SQL Monitor, DMVs, traces

31. Admin (subscribed) said April 2, 2012, 11:31 am:

    Thanks for everyone who participaterd in this month’s Question of the Month. The randomly selected winner of this month’s question was Zach M. He won a $50 Amazon gift certificate and a license to SQL Response 3.0. Don’t forget to enter the April Question of the Month.