How to: Create a SQL Server Performance Log

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Speech Server Analytics and Tuning Studio uses a Microsoft SQL??Server database to manage and analyze the Speech Server event logs. When using Analytics and Tuning Studio, SQL??Server can place a significant load on the computer resources. You can monitor SQL??Server performance objects to determine whether the load is acceptable or responsible for performance issues on the system.

To monitor SQL??Server, create a performance log and analyze the results. The Performance Logs and Alerts tool, which is part of the Performance console, provides a graphical interface for creating performance logs. You can also use the logman command to create performance logs from the command line.

Note

The size of performance logs can grow to excessive levels. Because the computer on which you save the performance data does not have to be the computer running SQL??Server, you should consider saving the performance data to a computer that is not a production computer running SQL??Server.

Note

Typically, you create a performance log for a 24-hour period to collect data across an entire day's activity. In this case, set the sampling rate for collecting performance data to a 15-second or 30-second interval. However, if you want to extend the logging period, increase the sampling rate accordingly to keep the size of the log manageable. For example, if you want to collect data for a 1-week period, increase the sample rate to 15 minutes.

Creating a SQL??Server Performance Log

To create a SQL??Server performance log using the Performance console

  1. Choose a location in which to save all the performance logs, and then create the PerfLogs directory at that location.

  2. Click Start, point to Administrative Tools, and then click Performance.

  3. In the Performance console, expand Performance Logs and Alerts, and then click Counter Logs.

    Existing logs are listed in the details pane. A green icon indicates that a log is running; a red icon indicates that a log has been stopped.

  4. Right-click Counter Logs, and then click New Log Settings.

  5. In the Name box, type the name of the counter log (such as SQL??Server), and then click OK.

  6. On the General tab, click Add Objects, and then select the following counter objects:

    • SQLServer:Exec Statistics
    • SQLServer:General Statistics
    • SQLServer:Memory Manager
    • SQLServer:SQL Statistics
    • SQLServer:Transactions
    • SQLServer:Wait Statistics

  7. Click Add, and then click Close.

  8. Click the Log Files tab, and then select Text file (comma-delimited) in the Log file type box.

  9. Click Configure, type the name of the directory you created to save the performance logs in the Location box, and then click OK.

  10. Click the Schedule tab, specify the logging period in the Start log and Stop log boxes, and then click OK.

To create a SQL??Server performance log using the command line

  1. Choose a location in which to save all the performance logs, and then create the PerfLogs directory at that location.

  2. Open Notepad, and then copy and paste the following lines into a new .txt file.

    \\<serverName>\SQLServer:Exec Statistics(*)\*
\\<serverName>\SQLServer:General Statistics\*
\\<serverName>\SQLServer:Memory Manager\*
\\<serverName>\SQLServer:SQL Statistics\*
\\<serverName>\SQLServer:Transactions\*
\\<serverName>\SQLServer:Wait Statistics(*)\*

  3. Replace the string <serverName> with the name of the computer running SQL??Server that you want to monitor.

  4. Save the document on the computer running SQL??Server using the name Sql_counters.txt.

  5. Open a command prompt, change the directory to the location of Counters.txt, and then run the following command, where outputPath is the path to the location of the PerfLogs directory that you created:

    logman create counter SQLServer -cf sql_counters.txt -f csv -si sec:30 -o outputPath\PerfLogs

  6. Verify that the counter log was successfully created by running the following command at the command prompt:

    logman query SQLServer

  7. Start performance logging by running the following command at the command prompt:

    logman start SQLServer

  8. After the logging period (for example, 24 hours), stop performance logging by running the following command at the command prompt on the computer running Speech Server:

    logman stop SQLServer

See Also

Tasks

How to: Create a Speech Server Performance Log