none
Performance Tuning

    Pergunta

  • I want to start monitoring performance of  my sql server 2005. I am confused with shall I start doint it with DMVs or PerfMon or SQL Profiler.

     Can anybody please shed some light ?


    • Editado nadirsql sexta-feira, 8 de junho de 2012 14:16
    sexta-feira, 8 de junho de 2012 14:16

Respostas

  • I use all of them , each has a contribution

    1st : when I'm not sure what the system suffers from , like when a complain about performance in general. I'll start with a perfmon trace for few days with the most important counters like

    CPU total , Page life expectancy  , disk queue length , index scans & seeks , batch requests/sec , compilations , re-compilations ...etc

    Not only perfmon trace can help you identify "some" symptoms @ your system but can also work as a baseline if you commit enhancements/changes and want to see how good these changes did.

    2nd : the DMVs , like Gulliaume outlined, provide a ready repository of  information about various areas since "the instance start". You can see cumulative stats about queries  like : most frequently run , top CPU total/avg , top logical reads total/AVG , top recompiled ....etc ; they identify the queries and that's the good part here.

    I use them anyhow because it never hurts to tune a query even if it's the not the most visible culprit. Memory usage reduction is always good since SQL server is a memory hog; I'd pay higher priority to CPU if Task manager and/or Perfmon shows a constant (minutes) spikes (>80%) of CPU

    DMVs provide also valuable information in other areas that perfmon won't show , examples:

    ## Missing indexes

    ## most congested indexes

    ## Unused indexes (those can make your database larger for no value)

    ## Blockings

    ## Cached plans usage

    ## Tempdb usage

    ## Waits

    ## indexes fragementation

    You'll always need to use DMVs , they provide rich info about plenty of SQL server areas and you can save this info in case SQL server instance is restarted (then the DMVs info will be purged)

    ## 3rd  , profiler

    Profiler will gather a lot of information that can be saved later on disk or to a database table for further analysis. I also use it when I want to profile something over an extended time. I can filter it against logins , apps , hosts and apply filters against queries footprint like reads/CPU usage.

    I'd go and create a server-side trace and leave it running for some time gathering the events I'm interested into and later I can import it and aggregate data. Advantage over DMVs is that profiler will always record all the queries against SQL server while DMVs can kick some queries based on cache size. You can also correlate perfmon data with profiler trace to see the status of resources at specific time correlated with running queries.

    As others already said , Perf. tuning needs some knowledge of SQL server and hands on experience on stuff like query rewrite , indexes design and execution plans

    Books can take a long time to read , still advised to go through, so I advise starting with this whitepaper

     http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

    HTH


    Please mark as answer if you think this answers your questions

    • Marcado como Resposta nadirsql quinta-feira, 14 de junho de 2012 00:05
    domingo, 10 de junho de 2012 23:41

Todas as Respostas

  • Hi,

    DMV provide instantaneous view of SQL Server state, as well as some cumulative data (but they get discarded at each service restart).

    Perfmon give you aggregated performance view over time which enables trend and synthetic analysis but doesn't allow to drilldown to individual operations.

    Profiler is a tracing utility that gives you finest grain analysis capability but lacks high level view... if you stick to basic usage. Export to SQL table along with aggregation queries or use of meta tools enable this.

    Background permanent monitoring should likely be DMV exports + perfmon, which is virtually impactless on performance.

    The PAL tool can help a lot with perfmon analysis : http://pal.codeplex.com/

    Full/Detailed analysis (eg baseline collection or acute problem analysis) can use a 'meta-tool' for data collection like PSSdiag that will encompass dmv, perfmon and profiler collection : http://support.microsoft.com/kb/830232. This should not be a permanent monitoring as there is a performance cost. (and you wouldn't know what to do of the huge amount of data generated anyway).

    And then another meta-tool for data analysis, like SQL Nexus : http://sqlnexus.codeplex.com/ .

    But like Chuck said, monitoring performance requires some minimal knowledge of the product being monitored (SQL) and monitoring and tuning methods, and getting a few good books on the topic seems very valid.

    The alternative is to have dedicated software do most of the job in your stead : MOM is one of them http://www.microsoft.com/en-us/download/details.aspx?id=23391

    HTH

    Guillaume

    sexta-feira, 8 de junho de 2012 15:37
  • Which of the queries are taking more than 15secs to run?

    During these queries run what are the other queries or processes running?

    What was the individual and total memory, CPU, network and I/O usage during that time?

    What are the Wait Types for these queries?

    Is it possible to query against DMVs and answer following questions as a single dataset?  Or, shall I be looking at profiler to do this ?

    Thanks!

    sexta-feira, 8 de junho de 2012 18:08
  • As outlined there are plenty of tools , documentation to monitor performance.   Consider creating a baseline for performance - that way you have something to compare .

    Jack Vamvas sqlserver-dba.com

    sábado, 9 de junho de 2012 16:05
  • I use all of them , each has a contribution

    1st : when I'm not sure what the system suffers from , like when a complain about performance in general. I'll start with a perfmon trace for few days with the most important counters like

    CPU total , Page life expectancy  , disk queue length , index scans & seeks , batch requests/sec , compilations , re-compilations ...etc

    Not only perfmon trace can help you identify "some" symptoms @ your system but can also work as a baseline if you commit enhancements/changes and want to see how good these changes did.

    2nd : the DMVs , like Gulliaume outlined, provide a ready repository of  information about various areas since "the instance start". You can see cumulative stats about queries  like : most frequently run , top CPU total/avg , top logical reads total/AVG , top recompiled ....etc ; they identify the queries and that's the good part here.

    I use them anyhow because it never hurts to tune a query even if it's the not the most visible culprit. Memory usage reduction is always good since SQL server is a memory hog; I'd pay higher priority to CPU if Task manager and/or Perfmon shows a constant (minutes) spikes (>80%) of CPU

    DMVs provide also valuable information in other areas that perfmon won't show , examples:

    ## Missing indexes

    ## most congested indexes

    ## Unused indexes (those can make your database larger for no value)

    ## Blockings

    ## Cached plans usage

    ## Tempdb usage

    ## Waits

    ## indexes fragementation

    You'll always need to use DMVs , they provide rich info about plenty of SQL server areas and you can save this info in case SQL server instance is restarted (then the DMVs info will be purged)

    ## 3rd  , profiler

    Profiler will gather a lot of information that can be saved later on disk or to a database table for further analysis. I also use it when I want to profile something over an extended time. I can filter it against logins , apps , hosts and apply filters against queries footprint like reads/CPU usage.

    I'd go and create a server-side trace and leave it running for some time gathering the events I'm interested into and later I can import it and aggregate data. Advantage over DMVs is that profiler will always record all the queries against SQL server while DMVs can kick some queries based on cache size. You can also correlate perfmon data with profiler trace to see the status of resources at specific time correlated with running queries.

    As others already said , Perf. tuning needs some knowledge of SQL server and hands on experience on stuff like query rewrite , indexes design and execution plans

    Books can take a long time to read , still advised to go through, so I advise starting with this whitepaper

     http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

    HTH


    Please mark as answer if you think this answers your questions

    • Marcado como Resposta nadirsql quinta-feira, 14 de junho de 2012 00:05
    domingo, 10 de junho de 2012 23:41
  • This is a good article that I reference for SQL Server 2005 performance tuning.

    http://technet.microsoft.com/en-us/library/cc966413.aspx

    segunda-feira, 11 de junho de 2012 20:42