Tag Archives: SQL Server

When are memory dumps generated in SQL Server

Posted on by

If your SQL Server instance encounters the following conditions, a mini-dump is created in the SQL Server LOG folder:

  1. Non-yielding scheduler (SQL Server 2005 and above for the first occurrence. This is equivalent to a 17883 dump in SQL Server 2000.)
  2. Non-yielding resource monitor (SQL server 2005 and above for the first occurrence)
  3. Non-yielding IOCP listener (SQL server 2005 and above for the first occurrence)
  4. Deadlocked Schedulers (SQL server 2005 and above. This is equivalent to a 17884 in SQL Server 2000.)
  5. Exceptions/Assertions
  6. Database Corruption
  7. Latch Timeout
  8. Other conditions (eg: when DUMPTRIGGER is used to generate a dump for a particular exception)

The first 4 conditions are documented under the following whitepaper:

How To Diagnose and Correct Errors 17883, 17884, 17887 and 17888

In the SQL Server Errorlogs, you will see a message which will indicate which of the following conditions listed above resulted in the generation of the dump. You can use the KB Article mentioned below to list the stack using the Public Symbols with the help of WinDbg:

Use the Microsoft Symbol Server to obtain debug symbol files

http://support.microsoft.com/kb/311503

Addendum: April 14, 2011

Paul Randal wrote a blog post on how to download a PDB file using symchk utility which is installed along with Windows Debugging Tools:

http://www.sqlskills.com/BLOGS/PAUL/post/How-to-download-a-sqlservrpdb-symbol-file.aspx

Technorati Tags: ,

Collecting Server Side Profiler Traces

Posted on by

When we want to monitor the kind of queries that hit across your SQL Server, most of us set up a Profiler Trace. But what most of us are not aware of is that there are two ways to setup a Profiler trace:

1. A GUI based Profiler trace

2. A Server side Profiler trace

A GUI based profiler trace causes a performance impact on the server as high as 25% depending on the events being captured. So if you are monitoring SQL Server with the help of profiler traces on a long term basis then it would always be advisable to setup a server side profiler trace.

Steps to setup a Server side Profiler Trace
1. Create a Profiler Trace using the GUI and then script out the trace to a file called say sql_trace.sql. Open the SQL_TRACE.SQL file in query analyzer/management studio query window and make the following changes

2. In the line “exec @rc = sp_trace_create @TraceID output, 2, N'<save location of trace file>’, @maxfilesize, NULL”, change the location where you want to store the trace file.

3. Make sure @maxfilesize parameter is set to 350 or a desirable size. (This makes sure that you roll over to the next trace file once the current size hits 350 MB)

4. Execute the script to create the trace. This will generate a Trace ID. The trace information can be obtained from fn_trace_getinfo function

5. To stop the trace, use sp_trace_setstatus ,0

6. To close the specified trace and delete its definition from the server us sp_trace_setstatus ,2

The events that you had configured while in the GUI would be present in the generated script and will be set using the Stored Prodecure sp_trace_setevent.

Remember that server side traces shouldn’t be writing to the same disk on which the SQL files are residing on and your profiler traces shouldn’t be writing to network drives/mapped drives or any other UNC shares. The destination file location should be a local disk. These are some recommendations that you need to keep in mind when configuring a server side profiler trace.

Useful Articles

283790 INF: How to Create a SQL Server 2000 Trace

283786 How to monitor SQL Server 2000 traces

Setting up Perfmon Logs

Posted on by
One of best ways to monitor your system performance for disk contention, high CPU, memory crunch etc. is to use Windows Performance Monitor Logs and Alerts. These logs can be used to capture historical information about different performance objects and would help us understand what was happening on the system at that point of time. In Windows Server 2008 & Windows Vista, Perfmon has a cool new utilty (a management MMC snap-in) called Reliability Monitor which helps you a bird’s eye view of your system stability.

Steps to setup Perfmon data collection for Windows Server 2003/XP
This can be done by opening up Perfmon:

  1. Click on “Performance Logs and Alerts”
  2. Click on “Counter Logs”
  3. Right click on the same and click on “New Log Settings”
  4. Give the log a name
  5. Click on “Add Objects” and add all the objects that are needed for your data collection
  6. Click on the “Log Files” tab
  7. You can change the log file location by clicking on the “Configure” button
  8. Click on the “Schedule” tab and then for “Start Log” and “Stop Log”, select the “Manually (Using the Shortcut menu)” option
  9. Click on the “OK” button to create the log
  10. Then right-click on the Log and click on “Start” to begin the logging
  11. Right-click on the log file and click on “Stop” to end the logging

How to setup a Perfmon data collection for Windows Server 2008/ Vista/ Windows Server 2008 R2

  1. Open up the Performance Monitor snap-in (Start -> Run -> perfmon)
  2. Expand the “Monitoring Tools” option and Right Click on “Perfomance Monitor” -> “New” -> “Data Collector Set“
  3. Give the data collector set a name and click on Next
  4. Give the location where you want to save the Perfmon Logs and click on Next
  5. Here you can provide the Run As user of leave that as the Default user
  6. Select the option “Save and Close” and click on Finish
  7. Then go back to the Perfmon snap-in main window and you should see a Data Collector set with the same name that you created under Data Collector Sets -> User Defined
  8. Click on the Data Collector Set and on the right hand pane, you should see a System Monitor Log Performance Counter. Right click on it and click on properties.
    • Under the Performance Counters tab, add the relevant performance counters required for your data collection, set the log format (binary, SQL, CSV, comma separated) and the sample collection interval time.
    • Under the File tab specify the file format name and logging properties for the file.
  9. Now you can start the Perfmon data collection by right clicking on the Data Collector Set and click on Start or you could do some more work for maintaining disk space by setting up some rules using the Data Manager to create .CAB files or delete older files in case we are setting up perfmon logs for long term monitoring.

There is hardly any performance impact in collecting perfmon logs on any server. Any perfmon data collection unlike other forms of data collection don’t generate voluminous data in terms of size but contain hordes of infromation which can provide valuable insight to an issue which is impacting critical business applications running on a server.

Windows Reliability and Performance Monitor

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