SQL Server 2012 : Measuring SQL Server Performance (part 7) - Viewing the Data Collector Data - Query Statistics History

2/20/2014 8:59:37 PM
Query Statistics History

The most common source of performance problems in SQL Server is poorly written code. Gathering information on the performance metrics of queries is a vital part of performance monitoring and tuning. Opening the Query Statistics History report will display a window similar to Figure 15.


Figure 15. Query Statistics History report

At the top of the report is the (by now familiar) time control so that you can determine the time period you’re interested in viewing. The graph shows queries ordered by different criteria, such as CPU, Duration, Total I/O, Physical Reads, and Logical Writes. Clicking any one of these will change the display to show the top ten queries for that particular metric. In Figure 15, Duration has been selected, so the queries are ordered by the total duration of the query for the time period selected. This ability makes this tool incredibly useful because each of these metrics represents a possible bottleneck in your system. So, you may be seeing a number of waits on your system on the CPU in the Server Activity History report. If you then open the Query Statistics History report and sort by CPU, you can see the top ten worse offenders and begin to tune the queries (query tuning is covered in a later section called “Tuning Queries”).

Clicking one of the queries will open the Query Details report, shown in Figure 16. At the top of the report is the text of the query. Depending on what was run inside the query, this can be quite long. Figure 16 shows only the bottom half of the report.


Figure 16. Query Details report, bottom half

The Query Details report shows a lot of information about the query selected. Starting at the top of Figure 16, you can see the Query Execution Statistics area. This includes various information such as the average CPU per execution, the average executions per minute, or the total number of executions for the time period. All this information provides you with details to enable you to understand the load that this particular query places on the system. The graph in the middle of Figure 16 shows data about the different execution plans that have been created for this query. Selecting each of the different rankings will reorder the execution plans just as it did the queries in the Query Statistics History report. Execution plans are the way that SQL Server figures out how to perform the actions you’ve requested in the query. They’re covered in more detail in the section “Understanding Execution Plans.” Being able to look at the information in the Query Details report for a query and compare it to previous entries will be a powerful tool when you begin to tune queries.

  •  SQL Server 2012 Security : How Hackers Attack SQL Server
  •  SQL Server 2012 Security : Partially Contained Databases
  •  SQL Server 2012 : SQL Server Audit (part 3) - Viewing Audited Events,Querying Audit Catalog Views
  •  SQL Server 2012 : SQL Server Audit (part 2) - Auditing Server Events, Auditing Database Events
  •  SQL Server 2012 : SQL Server Audit (part 1) - Creating an Audit Object, Recording Audits to the File System
  •  SQL Server 2012 : Encryption Support (part 4) - Transparent Data Encryption - Enabling TDE, Backing Up the Certificate
  •  SQL Server 2012 : Encryption Support (part 3) - Transparent Data Encryption - Creating Keys and Certificates for TDE
  •  SQL Server 2012 : Encryption Support (part 2) - Encrypting Data at Rest
  •  SQL Server 2012 : Encryption Support (part 1) - Encrypting Data on the Move
  •  SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th