SQL Server 2012 : Measuring SQL Server Performance (part 2) - Performance Monitor

2/20/2014 8:52:24 PM

2. Performance Monitor

The Performance Monitor tool comes installed with all versions of the Windows operating system. This tool provides a graphical interface for accessing the performance counters introduced in the preceding section. The easiest way to access the Performance Monitor tool, often referred to as Perfmon because of the name of the executable file, is to click the Start menu on your server and click the Run icon. Type perfmon, and then click the OK button. This will open a window that looks like Figure 1.


Figure 1. Performance Monitor suite

A number of tasks are possible with the Perfmon tool, including viewing performance monitor counters, creating logs of counters, and scheduling the capture of counters to files that can then be viewed through the Perfmon tool or imported into databases for more sophisticated data manipulation. I’ll simply show how to view a few of the counters introduced in the previous section.

You first have to access the Performance Monitor tool. Click the icon on the left side of the screen labeled Performance Monitor. This will display a screen similar to Figure 2.


Figure 2. Initial Performance Monitor window

To add counters to the Performance Monitor window, click the plus icon near the center of the toolbar at the top of the window. This will open the Add Counters window, shown in Figure 3.


Figure 3. The Add Counters window in Perfmon

To select counters for a particular computer, you’ll need to supply the name of the computer in the “Select counters from computer” combo box, or you can simply let the tool select from the local computer, as displayed in Figure 3. To supply the name, you can either type it or select from a list of computers on your network. Once that’s done, you’ll need to select one of the performance objects. As shown in Figure 2, the % Processor Time object has already been selected for you. To select additional counters, scroll within the “Available counters” window. Scroll down until you find the User Connections counter, and then click it. Click the Add button to add this counter to the “Added counters” list on the right. When you’re done adding counters, click the OK button.

Now the Perfmon window will show activity from the two counters selected. The window shows a set period of time, and you can see the variations in data across the period, as shown in Figure 4.


Figure 4. Perfmon displaying performance counters and activity

Looking at the screen displayed in Figure 4, you can see how the performance counters change over time. The data is collected and aggregated so that you can see important information such as the Last, Average, Maximum, and Minimum values. The duration, the amount of time on display, is also shown. You can see the list of counters that is currently on display. You can even highlight a counter by selecting it in the list and then clicking the lightbulb icon in the toolbar at the top of the screen.

With the Perfmon tool, you can further manipulate the display to show different types of graphs or raw data and change the properties of the counters displayed to adjust the color they’re displayed in or the scale on which they display. You can also choose to have the Perfmon tool output to a log. There are other ways to get at performance counters, and one of them is within SQL Server using T-SQL.

  •  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