Archive for February, 2013

How to create SQL Server Severity Alerts?

In hospitals or clinics, there are pain charts for the doctors or nurses to ask the patients the level of pain. Similarly, when an error is raised by SQL Server, there are levels of severity associated with error to indicate the type of problem encountered by SQL Server.

AS per Microsoft, severity levels 0 to 9 are informational messages or errors that are not severe. Severity levels 11 to 16 indicate errors that can be corrected by the user. Severity levels 17 to 19 are errors that cannot be correct by the user and should inform the SQL Server DBAs. Severity levels 20 to 24 are fatal system errors that are written to the error log and should be escalated to the attention of the SQL Server DBAs. 823, 824, and 825 are I/O errors. 823 is associated with disk corruption. 824 indicates logical consistency I/O errors. 825 indicates incorrect checksum errors.

Prior to creating these SQL Server severity alerts, you should have first configured database mail and an operator. To create the severity alerts, you can use the following script:

-- Creating Severity alerts from 17-25
-- The operator should be created prior to creating the severity alerts.
-- Replace OperatorName with the name of your operator.
-- Error messages with a severity level from 19 through 25 are written to the error log.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 17 Alert: Insufficient Resources',
  @message_id=0,
  @severity=17,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 17 Alert: Insufficient Resources',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 18 Alert: Nonfatal Internal Error',
  @message_id=0,
  @severity=18,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 18 Alert: Nonfatal Internal Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 19 Alert: Fatal Error in Resource',
  @message_id=0,
  @severity=19,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 19 Alert: Fatal Error in Resource',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 20 Alert: Fatal Error in Current Process',
  @message_id=0,
  @severity=20,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 20 Alert: Fatal Error in Current Process',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 21 Alert: Fatal Error in Database Processes',
  @message_id=0,
  @severity=21,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 21 Alert: Fatal Error in Database Processes',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 22 Alert: Fatal Error - Table or Index Integrity Suspect',
  @message_id=0,
  @severity=22,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 22 Alert: Fatal Error - Table or Index Integrity Suspect',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 23 Alert: Fatal Error - Database Integrity Suspect',
  @message_id=0,
  @severity=23,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 23 Alert: Fatal Error - Database Integrity Suspect',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 24 Alert: Fatal Error - Hardware Error',
  @message_id=0,
  @severity=24,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 24 Alert: Fatal Error - Hardware Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 25 Alert: Fatal Error - Hardware Error',
  @message_id=0,
  @severity=25,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 25 Alert: Fatal Error - Hardware Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 823 Alert: Fatal Error - I/O Error',
  @message_id=0,
  @severity=25,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 823 Alert: Fatal Error - I/O Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 824 Alert: Fatal Error - I/O Error',
  @message_id=0,
  @severity=25,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 824 Alert: Fatal Error - I/O Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 825 Alert: Fatal Error - I/O Error',
  @message_id=0,
  @severity=25,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 825 Alert: Fatal Error - I/O Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

REFERENCES:

Database Engine Error Severities
http://msdn.microsoft.com/en-us/library/ms164086(v=sql.105).aspx

Error message 823
http://support.microsoft.com/kb/828339

Error message 824
http://support.microsoft.com/kb/2015756

Error message 825
http://support.microsoft.com/kb/2015757

How to Create SQL Server Performance Reports Using Excel?

Once you had run the perfmon to capture the objects and counters for a scheduled time period, you will need to analyze the results as the natural next step. Wouldn’t it be nice if you can create charts and graphs and present your findings to your team or clients? Yes. It would. It can be done using Excel and it is easier than you think.

You will first need to convert the perfmon counter log file(s) to csv file(s) using the Windows relog program. Then format the file to add in the bells and whistles to find the averages, min, max, standard deviation, and creating charts and graphs.

To convert the perfmon counter log file(s) to csv file(s):
1. Locate the perfmon counter file the .blg file.
2. On the command prompt, browse to the directory that contains the .blg file.
3. To convert the .blg file to a .csv file: relog logfile.blg –f CSV –o newfile.csv. Replace logfile.blg with the name of your .blg file and newfile.csv with the name of the .csv you wish to save as.
4. The relog program will do the conversion and let you know when the command is completed successfully.

To format the newly converted csv file into presentable details:
1. Open the perfmon log csv file using Excel.
2. Replace all the occurrences of \\SERVERNAME\ with an empty string (“ “). Where SERVERNAME is the server name you had run the perfmon on.
3. In Cell A1, replace “(PDH-CSV 4.0) (Eastern Daylight Time)(240)” with “Time”.
4. Delete the second row because very often the first data row has invalid data.
5. Format Column A to “Date” – 3/14/01 1:30 PM format.
6. Formatting the numbers: select all of the columns except column A, right-click and select “Format Cells”, choose the Number format, zero decimals, and check the box for use 1000 Separator.
7. Create a copy of the worksheet for Summary, PivotChart, and Details. Name them accordingly. Eg.: Summary, PivotChart, Details.

To create a Summary report for Average, Min, Max, and Standard Deviation for all the performance counters:
1. On the Summary worksheet insert 6 rows on top of row 1.
2. For cells A2 to A5, type in Average, Min, Max, and Standard Deviation.
3. In the B column, put in the formulas. The assumption here is the data goes from row 8 to row 100. But, make sure to change the row 100 to the # for your data row.

  • B2 (Average) = AVERAGE(B8:B100)
  • B3 (Min) = MIN(B8:B100)
  • B4 (Max) = MAX(B8:B100)
  • B5 (Standard Deviation) = STDEV(B8:B100) – shows how much variation or “dispersion” exists from the average (mean, or expected value). A low standard deviation indicates that the data points tend to be very close to the mean, whereas high standard deviation indicates that the data points are spread out over a large range of values.

4. Move your cursor to cell B8, which should be your first cell with data. Then select the View menu then select Freeze Panes. That way we can move around our spreadsheet while we still see the dates on the left and the formulas and headers across the top.
5. Click on the cell B1, key in the value “=B7” to reference it to B7 where the heading is shown.
6. Make row 1 from B1 all the way to the end on the right referencing row 7 by dragging the + on the lower right-hand corner of B1 all the way to the right end of the worksheet.
7. Do the same for the rows: Average, Min, Max, and Standard Deviation.
8. Make row 1 bold and format it to fit the contents if needed.



To create PivotTable with PivotChart:
1. On the PivotChart worksheet, click on the Insert menu, select PivotTable and then select PivotChart.
2. Accept the default settings and click the “OK” button.
3. To generate graph for CPU usage for example (you can use the same approach for all other objects):

  1. From the “PivotTable Field List” select “Time” and drag it into the “Axis Fields (Categories)” area.
  2. From the “PivotTable Field List” select “Process(_Total)\% Process Time” and drag it into the “Values” area.
  3. You can modify if you wish to see the values being presented as Average, Min, Max, and STDEV by clicking the drop-down arrow on the value box and selecting the “Value Field Settings…” option.
  4. You can close the “PivotTable Field List” by clicking the x button on its row to have more space for the graph.
  5. You can expand the graph by clicking on the lower right-hand corner and drag to the right and down to re-size.
  6. You can remove the “total” box on the right by clicking it and then delete it.
  7. You can format the heading by clicking the heading and then key in the desired heading.

4. You can also create the averages, min, max, and standard deviations by creating a Memory averages from the PivotChart as the template and then copy it and rename it as Processor, Network, Disk, and SQL Server and then change and add in the required counters from the Field List.
5. Save the .csv as an Excel workbook file to preserve the formatting.

How to capture the performance monitor counters and objects using PERFMON?

In the previous blogs, we have reviewed the performance objects and counters to identify memory, processor, disk, and overall SQL Server performance pressures and bottlenecks. In this blog post, we are going to review the steps in running the performance monitor (PERFMON) in both Windows Server 2003 and 2008 to capture the performance monitor counters and objects.

To minimize performance monitor overhead, it is usually recommended that you limit the number of counter selectively and save the counter logs locally on a local disk other than the ones that are being monitored instead of transferring the performance data across the network.

Windows Server 2003:

  1. Open Performance Monitor by running PERFMON in the “Run…” textbox.
  2. Expand the “Performance Logs and Alerts” node.
  3. Right-click on “Counter Logs.”
  4. Select “New Log Settings…” from the popup menu.
  5. Assign a named for the new log settings. Eg.: “ServerName_Perfmon.
  6. Click the “Add Counters…” button to start adding counters for each “Performance object” you wish to add counters from. Add the desired counters. Please see my previous blog posts on identifying memory, processor, disk, and overall SQL Server performance pressures and bottlenecks for suggestions.
  7. Once you have added all the required counters, click on the “Log files” tab and click on the “Configure…” button to specify the location for the log file to be stored in. Make sure the format stays in “Binary file” because you can parse them into different formats later.
  8. Click on the “Schedule” tab to schedule it.

To make the PERFMON file as a template to make it reusable, you can save the list of counters as an .htm file so that you can use it on other SQL Server hosts. You can right-click on the new log created and select the “Save Setting As…” option to save the file as .htm into the designated location. To use this list of objects and counters for other SQL Server hosts, open the .htm file in an editor such as Notepad and replace all instances of \\SQLServerMachineName with ‘ ‘ (a blank string) without the quotes.

To use the template PERFMON counter log file in a new host server for Windows Server 2003, open the PERFMON, in the “Performance Logs and Alerts” node right-click “Counter Logs” and select the option “New Log Settings From…” and then browse to the .htm template PERFMON file.

After the scheduled PERFMON completes, you can convert the .blg files to .csv files for further analysis. To convert perfmon .blg files to .csv file, you can use the command prompt and navigate to the directory where the binary file is located and type:

relog logfile.blg -f CSV -o newfile.csv

Windows Server 2008 and Windows Server 2008 R2

  1. Open Performance Monitor by running PERFMON in the “Run…” textbox.
  2. Under “Reliability and Performance”, expand “Data Collector Sets”.
  3. Right-click on “User Defined”
  4. Select “New” from the popup menu then select “Data Collector Set”.
  5. Assign a name for the new log settings. Eg.: “ServerName_Perfmon.
  6. Under the “Create data logs” section, check the checkbox for “Performance counter”.
  7. Click the “Add…” button to start adding counters for each “Performance object” you wish to add counters from. Add the desired counters. Please see my previous blog posts on identifying memory, processor, disk, and overall SQL Server performance pressures and bottlenecks for suggestions.
  8. Once you have added all the required counters, click the “OK” button and then click the “Next” button.
  9. Specify the location to save the file.
  10. Click the “Finish” button.
  11. Right-click the new file under the “User Defined” folder and select “Properties”.
  12. Click on the “Schedule” tab.
  13. Click on the “Add” button to schedule a “Beginning date:” and “Start time:” and then click on the “Stop Condition” tab to specify the “Overall duration.”

To make the PERFMON file as a template to make it reusable, you can save the list of counters as an .xml file so that you can use it on other SQL Server hosts. Right-click on the new log folder under the “User Defined” folder and select “Save Template…” to save the file as .xml in to the designated location.

To use a template perfmon counter log file in a Windows Server 2008 or 2008 R2 host server, open PerfMon > Reliability and Performance > Data Collector Sets > right-click on “User Defined” > New > “Data Collector Set” > name the file > select the “Create from a template (Recommended)” radio button > click the “Browse” button to browse to the template file > select the template .xml file > click the “Next” button > specify the location to save the file > the “Save and close” radio button will be selected > hit the “Finish” button.

To modify the new “DataCollector01” set, right-click on it and select “Properties” to add or remove the counters as necessary. Click the “OK” button when done.

To schedule the new log file, right-click on the new file under the “User Defined” section, click “Properties”, then click the “Schedule” tab.

To convert perfmon .blg files to .csv files, you can use the command prompt and navigate to the directory the binary file is located and type:

relog logfile.blg -f CSV -o newfile.csv

REFERENCES:

How to create a log using System Monitor in Windows
http://support.microsoft.com/kb/248345

Using Performance Monitor
http://technet.microsoft.com/en-us/library/cc749115.aspx

What are the performance counters to capture to analyze SQL Server pressure?

You want to capture a system baseline for performance assessment before tuning. In the previous blogs, we have reviewed the performance counters to analyze memory, processor, and disk bottlenecks. Now, you want to know the most important performance counters to capture if you wish to make an assessment and/or analyze SQL Server bottleneck.

Before selecting the objects and counters, we need to define objects and counters in Windows Performance Monitor (PERFMON). A performance object is an entity for which performance data is available and performance counters define the type of data that are available for a performance object. One performance object will have one or more associated performance counters. When added in the Performance Monitor, they will appear in the form of \Performance Object\Performance Counter. For example, \SQLServer: General Statistics\Processes Blocked and \SQLServer: General Statistics\User Connections, where SQLServer: General Statistics is the performance object and Processes Blocked and User Connections are the performance counters.

To analyze the SQL Server bottlenecks, you can capture the performance monitor objects such as SQLServer:Access Methods, SQLServer:Latches, SQLServer:Locks(_Total), SQLServer:SQL Statistics, and SQLServer:General Statistics and the associated counters for these objects.

For the SQLServer:Access Methods object, you can add counters such as FreeSpace Scans/sec, Table Lock Escalations/sec, and Workfiles Created/sec. The following describes each of the counters and the recommended values for each:

  • \SQLServer:Access Methods\FreeSpace Scans/sec: Number of scans per second needed to search for free space within pages. This is usually caused by heap tables. Tables without a clustered index.
  • \SQLServer:Access Methods\Table Lock Escalations/sec: Number of times per second locks on a table was escalated to the table lock granularity.
  • \SQLServer:Access Methods\Workfiles Created/sec: Number of work files created per second. Workfiles are created in the Tempdb and are used for processing operations where the amount of data to be processed is too big to fit into the available memory.

For the SQLServer:Latches object, you can add the counter Total Latch Wait Time (ms). The following describes the counter and the recommended value:

  • \SQLServer:Latches\Total Latch Wait Time (ms): Total latch wait time in milliseconds for latch requests in the last second. Latches are used internally by SQL Server to protect the integrity of internal structures and they are not directly controlled by users.

For the SQLServer:Locks(_Total) object, you can add counters such as Number of Deadlocks/sec, Lock Timeouts/sec, and Lock Wait Time (ms). The following describes each of the counters and the recommended values for each:

  • \SQLServer:Locks(_Total)\Number of Deadlocks/sec: Number of lock requests per second that resulted in a deadlock.
  • \SQLServer:Locks(_Total)\Lock Timeouts/sec: Number of lock requests per second that timed out.
  • \SQLServer:Locks(_Total)\Lock Wait Time (ms): Total wait time in milliseconds for locks in the last second.

For the SQLServer:Statistics object, you can add counters such as Batch Requests/sec, SQL Compilations/sec, and SQL Re-Compilations/sec. The following describes each of the counters and the recommended values for each:

  • \SQLServer:Statistics\Batch Requests/sec: Number of command batches received per second. It is usually a good indicator of the load on SQL Server.
  • \SQLServer:Statistics\SQL Compilations/sec: Number of SQL compilations per second. It measures the number of times SQL Server compiles an execution plan per second. What is compilation? Before a query, batch, stored proc, triggers, or any SQL statements get executed on SQL Server, they get compiled into a plan. The plan is then executed to produce the results. These compiled plans are stored in SQL Server’s memory of which known as plan cache. Usually, it is recommended that plan reuse should be used to avoid compilation cost.
  • \SQLServer:Statistics\SQL Re-Compilations/sec: Number of statement recompiles per second. Re-Compilation occurs when SQL Server checks the current query plans before executing any of the individual query plans for SQL statement executions, if the checks for validity of the query plans fail or they are not in satisfactory of SQL Server, the query plans will get compiled again, hence the term Re-Compilation. Re-Compilations slow down query executions and should be reduced when possible.

For the SQLServer:General Statistics object, you can add counters such as Processes Blocked and User Connections. The following describes each of the counters and the recommended values for each:

  • \SQLServer:General Statistics\Processes Blocked: Identifies the number of currently blocked processes at the time the sample was taken.
  • \SQLServer:General Statistics\User Connections: Identifies the number of users connected to SQL Server at the time the sample was taken.

Common SQL Server overall performance bottleneck resolutions if the system is having performance bottleneck:

  • Check for missing indexes and outdated statistics
  • Make sure there are scheduled maintenance jobs for index maintenance in addition to updating statistics
  • Check for long running queries and report to the development team for possible revision
  • Check for blocking and report to the development team for possible revision
  • Check for deadlocks and report to the development team for possible revision
  • Minimize ad hoc queries if there are any and rewrite them into stored procedures
  • Resolve the processor, memory, and disk bottlenecks if there are any

 

REFERENCES:

SQL Server, Access Methods Object
http://msdn.microsoft.com/en-us/library/ms177426(v=sql.105).aspx

SQL Server, Latches Object
http://msdn.microsoft.com/en-us/library/ms177421(v=sql.105).aspx

SQL Server, Locks Object
http://msdn.microsoft.com/en-us/library/ms190216(v=sql.105).aspx

SQL Server, General Statistics Object
http://msdn.microsoft.com/en-us/library/ms190697(v=sql.105).aspx

SQL Server, SQL Statistics Object
http://msdn.microsoft.com/en-us/library/ms190911(v=sql.105).aspx

Object and Counter Design
http://msdn.microsoft.com/en-us/library/windows/desktop/aa372191(v=vs.85).aspx

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc966425.aspx

What are the performance counters to capture to analyze disk pressure?

You want to capture a system baseline for performance assessment before tuning. In the previous blogs, we have reviewed the performance counters to analyze memory and processor pressure. Now, you want to know the most important performance counters to capture if you wish to make an assessment and/or analyze disk bottleneck.

Before selecting the objects and counters, we need to define objects and counters in Windows Performance Monitor (PERFMON). A performance object is an entity for which performance data is available and performance counters define the type of data that are available for a performance object. One performance object will have one or more associated performance counters. When added in the Performance Monitor, they will appear in the form of \Performance Object\Performance Counter. For example, \PhysicalDisk\% Disk Time and \PhysicalDisk\Current Disk Queue Length, where PhysicalDisk is the performance object and % Disk Time and Current Disk Queue Length are the performance counters.

To analyze disk pressure and bottlenecks, you can capture the performance monitor object PhysicalDisk and the associated counters for this object. The following describes each of the counters and the recommended values for each:

  • \PhysicalDisk\% Disk Time: Shows the percentages of time the disks are busy servicing read or write requests. The recommended average should be less than 85%.
  • \PhysicalDisk\Avg. Disk Queue Length: Shows the number of disk requests that are waiting for a disk during the sample interval. The recommended average should be less than 2 per disk.
  • \PhysicalDisk\Current Disk Queue Length: Shows the number of disk requests that are currently waiting at the time the performance data is collected. The recommended average should be less than 2 per disk.
  • \PhysicalDisk\Disk Transfers/sec: Shows the number of read and writes per second regardless of the amount of data. The recommended average should be less than 100 per disk.
  • \PhysicalDisk\Disk Bytes/sec: Shows the amount of data transmitted per disk per second.
  • \PhysicalDisk\Avg. Disk Sec/Read: Shows the average in milliseconds (ms) to read from disk. The recommended average should be less than 10 milliseconds (ms).
  • \PhysicalDisk\Avg. Disk Sec/Write: Shows the average in milliseconds (ms) to write to disk. The recommended average should be less than 10 milliseconds (ms).

What if you had ran the Performance Monitor and found the averages for these disk performance counters are in less than desired values? What can you do? You can review the following recommendations as starting points in troubleshooting or resolving the disk bottlenecks:

  • Find and review the queries that use cause the disk pressure and optimize with rewrite to reduce the disk I/O.
  • Consider using SAN and RAID array if not already done so.
  • Get faster hard disks.
  • Relocate the log files to separate disks from the data files.
  • Create multiple files and filegroups in separate disk drives.
  • Increase the host server memory to reduce paging to the disks.

REFERENCES:

Examining and Tuning Disk Performance
http://technet.microsoft.com/en-us/library/cc938959.aspx

Monitoring Disk Usage
http://technet.microsoft.com/en-us/library/ms175903(v=sql.105).aspx

Object and Counter Design
http://msdn.microsoft.com/en-us/library/windows/desktop/aa372191(v=vs.85).aspx

Performance Monitor Counters
http://technet.microsoft.com/en-us/library/cc768048.aspx#XSLTsection131121120120

What are the performance counters to capture to analyze processor pressure?

You want to capture a system baseline for performance assessment before tuning. In a previous blog, we have reviewed the performance counters to capture to analyze memory pressure. Now, you want to know the most important performance counters to capture if you wish to make an assessment and/or analyze processor pressure.

Before selecting the objects and counters, we need to define objects and counters in Windows Performance Monitor (PERFMON). A performance object is an entity for which performance data is available and performance counters define the type of data that are available for a performance object. One performance object will have one or more associated performance counters. When added in the Performance Monitor, they will appear in the form of \Performance Object\Performance Counter. For example, \System\ Processor Queue Length and \Processor(_Total)\% Processor Time, where System and Processor(_Total) are the performance object and Processor Queue Length and % Processor Time are the performance counters.

To analyze processor pressure, you can capture the performance monitor objects such as Processor(_Total), System, and SQLServer: Access Methods and the associated counters for these objects.

For the Processor(_Total) object, you can add counters such as % Processor Time and % Privileged Time. The following describes each of the counters and the recommended values for each:

  • \Processor(_Total)\% Processor Time: The percentage of elapsed time that the processors spend on executing active threads. Simply put, it is the percentage of time that the processors are busy. The recommended average value should always be below 80%.
  • \Processor(_Total)\% Privileged Time: Note that there are two modes for all activities inside a Windows Operating System: user mode and kernel mode. Kernel mode is also known as privileged mode. All system activities are classified as kernel mode (privileged mode). Disk access or activity is a privileged mode. The recommended average value should always be below 15%.

For the System object, you can add counters such as Processor Queue Length and Context Switches/sec. The following describes each of the counters and the recommended value for each:

  • \System\Processor Queue Length: Note that regardless of the number processors, there will always be only one processor queue. This counter measures the number of threads in the processor queue. Simply put, it is the number or requests waiting on the processor. The recommended average value should always be below 2.
  • \System\Context Switches/sec: This counter measures the rate in which the processors are switched from one thread to another. The recommended average value should always be below 1,000.

For the SQLServer: Access Methods object, you can add counters such as Full Scans/sec, Worktables Created/sec, and Page Splits/sec. The following describes each of the counters and the recommended value for each:

  • \SQLServer: Access Methods\Full Scans/sec: This counter shows the full scans for tables and indexes. Both are undesirable results for large tables and indexes. The recommended average should be less than 1.
  • \SQLServer: Access Methods\Worktables Created/sec: Worktables are built in tempdb and are dropped automatically at the end of the statement. They are usually generated for GROUP BY, ORDER BY, or UNION queries. If you see the value for this counter is high, you will need to review the stored procedure and/or ah-hoc queries for optimization and possible rewrite.
  • \SQLServer: Access Methods\Page Splits/sec: The index fillfactor determines the fullness of a data page. If you find that the number of page splits is high, you should consider increasing the fillfactor of your indexes.  Specifying a higher number for the fillfactor to reduce page splits to allocate more room in data pages before it fills up a page and causes page split.

What if you had ran the Performance Monitor and found the averages for these processor performance counters are in less than desired values? What can you do? You can review the following recommendation as starting points in troubleshooting or resolving the processor bottlenecks:

  • Find and review the queries that use the most CPU time and rewrite them if possible in addition to checking for missing indexes and outdated statistics.
  • Relocate the CPU intensive software to a separate host server.
  • Increase the number of processor or upgrade to faster processors.

 

REFERENCES:

Optimizing SQL Server CPU Performance
http://technet.microsoft.com/en-us/magazine/2007.10.sqlcpu.aspx

Processor Counters
http://technet.microsoft.com/en-us/library/cc938593.aspx

Object and Counter Design
http://msdn.microsoft.com/en-us/library/windows/desktop/aa372191(v=vs.85).aspx

Performance Monitor Counters
http://technet.microsoft.com/en-us/library/cc768048.aspx#XSLTsection131121120120

What are the performance counters to capture to analyze memory pressure?

You wish to capture a system baseline for performance assessment before tuning so that you can do a before and after comparison to quantify improvements in addition to having a baseline to establish on what’s considered as normal behaviors for the memory usage for the SQL Server instance and host. What are the most important performance counters to capture if you wish to make an assessment and/or analyze memory pressure?

Before selecting the objects and counters, we need to define objects and counters in Windows Performance Monitor (PERFMON). A performance object is an entity for which performance data is available and performance counters define the type of data that are available for a performance object. One performance object will have one or more associated performance counters. When added in the Performance Monitor, they will appear in the form of \Performance Object\Performance Counter. For example, \Memory\Available Bytes and \Memory\Pages/sec, where Memory is the performance object and Available Bytes and Pages/sec are the performance counters.

To analyze memory pressure, you can capture the performance monitor objects such as memory, SQLServer: Buffer Manager, SQLServer: Memory Manager and the associated counters for these objects.

For the memory object, you can add counters such as Available Bytes, Pages/sec, Page Faults/sec, Pages Input/sec, and Pages Output/sec. The following describes each of the counters and the recommended value for each:

  • \Memory\Available Bytes: Indicates the free physical memory in the host server. This value is system dependent. If the value stays consistently low, it is usually a sign of memory pressure or stress.
  • \Memory\Page Faults/sec: Page fault is an undesirable condition where the required data pages are not in cache and they have to be read from memory or disk. When read from memory, it is known as soft page fault. When read from disk, it is known as hard page fault. Page Faults/sec measure the rate of total page faults per second, both the soft page faults and hard page faults.  This value is usually used for comparison with the baseline for analysis.
  • \Memory\Pages Input/sec: The rate of reading pages from the disk. We need to compare the average value with the baseline for further analysis.
  • \Memory\Pages Output/sec: The rate of dirty pages that need to be written to disk. This is not a desirable condition. Usually this indicates memory pressure. We need to compare the average value with the baseline for further analysis.
  • \Memory\Pages/sec: The number of pages read from or written to disk for hard page faults. The recommended average value should be less than 50.

For the SQLServer:Buffer Manager object, you can add counters such as Buffer cache hit ratio, Page Life Expectancy, Checkpoint Pages/sec, and Lazy writes/sec. The following describes each of the counters and the recommended value for each:

  • \SQLServer:Buffer Manager\Buffer cache hit ratio: This counter measures the percentage of pages that are read from the buffer cache without having to read from disk. The buffer cache is the pool of buffer pages and it is usually the biggest part of the SQL Server memory pool. This value should stay consistently high or as close to 100% as possible. If it averages below 97%, you may need to consider increasing more memory.
  • \SQLServer:Buffer Manager\ Checkpoint Pages/sec: This counter represents the number of pages that are moved to disk when a checkpoint operation occurs. A high value indicates a high number of writes. The recommended average should be below 30 per second.
  • \SQLServer:Buffer Manager\ Lazy writes/sec: This counter measures the dirty buffers, data pages that have been modified, that need to be written to disk to free up memory. The recommended average value should be less than 20.
  • \SQL Server:Buffer Manager\ Page Life Expectancy: This counter indicates the duration a data page can stay in the buffer pool without being referenced. The higher the value the better. A low average value is a sign of memory pressure. Years ago, when the host servers were running under less than optimum machines, experts would usually recommend the average value should be 300 (5 minutes). With advanced in the power of the host server, the average should be 480 (8 minutes or more).

For the SQLServer:Memory Manager object, you can add counters such as Memory Grants Pending, Target Server Memory (KB), and Total Server Memory (KB). The following describes each of the counters and the recommended value for each:

  • \SQLServer:Memory Manager\Memory Grants Pending: This counter represents the number of processes waiting for memory grant within SQL Server memory. The recommended average is 0. If this counter’s value is high, it is a clear indication that SQL Server is short of memory.
  • \SQLServer:Memory Manager\Target Server Memory (KB): Shows the amount of memory that SQL Server wishes to consume.
  • \SQLServer:Memory Manager\Total Server Memory (KB): The name may be misleading.  This is not the total memory used by SQL Server. This counter shows the amount of memory that SQL Server is current using and it will grow to the value that’s close to or equal to Target Server Memory (KB). If this counter’s value is much less than the value for the Target Server Memory (KB), the most likely causes can be the SQL Server memory requirement is low or the max server memory configuration is set to a value that’s too low.

What if you had ran the PERFMON and found the averages for these memory performance counters are in less than desired values? What can you do? You can review the following recommendation as starting points in troubleshooting or resolving the memory bottlenecks:

  • Review and optimize the expensive queries to check for possible missing indexes, outdated statistics, and rewrite them to reduce unnecessary reads.
  • If possible, increase memory for the host server, and then set the max server memory (MB) to a higher value.
  • Consider migrating over to a 64-bit processor host server if running under a 32-bit processor host server as 64-bit processors expands the usage of memory exponentially.

REFERENCES:

SQL Server, Buffer Manager Object
http://technet.microsoft.com/en-us/library/ms189628(v=sql.105).aspx

SQL Server, Memory Manager Object
http://technet.microsoft.com/en-us/library/ms190924(v=sql.105).aspx

Object and Counter Design
http://msdn.microsoft.com/en-us/library/windows/desktop/aa372191(v=vs.85).aspx

Performance Monitor Counters
http://technet.microsoft.com/en-us/library/cc768048.aspx#XSLTsection131121120120

How to enable backup compression?

You wish to increase backup speed and reduce the amount of disk space required for all the database backups. You had wondered if SQL Server has any compression functions or features. The good news is yes. But, it depends on what version and edition of SQL Server you are running.

As per Microsoft, backup compression was made available back in the SQL Server 2008 Enterprise edition. In SQL Server 2008 R2, all editions with Standard and above will also have backup compression as a feature. In addition, all editions of SQL Server 2008 and later can restore a compressed backup.

You can enable the backup compression on the instance level by configuring the ‘backup compression default’ option and you can override this instance level option on the backup statements. In addition, you can query the backupset system table to find the backup compression ratio.

To enable the backup compression on the instance level:

-- enable backup compression on the instance level
USE master
GO
EXEC sp_configure 'backup compression default', 1 -- 0 to turn off
RECONFIGURE WITH OVERRIDE

-- verify the current setting for backup compression
select c.name
, c.description
, c.value
, c.value_in_use
from sys.configurations c
where c.name like 'backup compression default'

To use the backup statement to override the instance level option for backup compression:

-- backup with compression
BACKUP DATABASE AdventureWorks -- replace with your database here
TO DISK = 'C:\SQLBackup\aw.bak' -- replace with your backup path here
WITH COMPRESSION -- NO_COMPRESSION to turn off compression on the statement level

To query the backupset system table to find the backup compression ratio:

-- querying the backup compression ratio
SELECT bs.database_name
, bs.backup_start_date
, bs.backup_finish_date
, convert(decimal(10,2),bs.backup_size/1024/1024) [Backup Size in MB]
, convert(decimal(10,2), bs.compressed_backup_size/1024/1024) [Compressed Size in MB]
, convert(decimal(10,2), bs.backup_size/bs.compressed_backup_size) [Compression Ratio]
FROM msdb..backupset bs

Note that third party backup software such as Idera SQL safe backup, Redgate SQL Backup Pro, EMC AVAMAR, Quest Software LiteSpeed, and etc will usually have a higher backup compression ratio than the native SQL Server backup compression. But, the native SQL Server backup compression is already available to you with no extra charge if the version and edition of SQL Server you are running has the backup compression feature. You must evaluate your budget, backup time requirement, and disk space availability to make more informative decisions on selecting a backup strategy.

REFERENCES:

Backup Compression
http://technet.microsoft.com/en-us/library/bb964719(v=sql.105).aspx

Backup compression default Option
http://technet.microsoft.com/en-us/library/bb677250(v=sql.105).aspx

Backupset
http://msdn.microsoft.com/en-us/library/ms186299.aspx

How to get notified on SQL Server Agent and/or SQL Server Service restarts in addition to identifying the current active node for the failover cluster?

Unlike your TV, SQL Server can’t be turned off or on as per your desire. For mission critical SQL Servers, the uptime is extremely critical as one or more applications are connected to the databases to support the application users at all times throughout the day and possibly throughout many regions around the world. Planned maintenance downtime required for version upgrades, service pack upgrades, hot fixes, and production deployments or releases of which will require services or host reboots must be coordinated, planned, and approved by the management team. In case of unexpected service interruptions such as SQL Server Agent and SQL Server Service restarts and/or failover for the clusters, you must be notified and find out and document the root cause to prevent future surprises. How can we do that?

As we all know, when we schedule a SQL Server Agent job, one of the possible schedule types is “Start automatically when SQL Server Agent starts.” We can use this feature to notify us via database mail on SQL Server Agent and/or SQL Server Service restarts. When a failover on a cluster occurs, SQL Server service will also get restarted. We will identify the current active node for the failover clusters.

Here are the overview steps and concepts:

  1. Develop the T-SQL code to detect SQL Server Agent and/or SQL Server Service restarts in addition to finding the current active node in the cluster.
  2. Schedule a SQL Server Agent job to run the T-SQL code whenever SQL Server Agent starts.

Here are the implementation steps:

  1. Develop the T-SQL code to detect SQL Server Agent and/or SQL Server Service restarts in addition to finding the current active node in the cluster.
  2. -- Alert on SQL Server Agent and/or SQL Server service restarts
    -- Replace your recipients in line # 14
    
    -- declare the required variables
    DECLARE @Message VARCHAR(MAX)
    , @Subject VARCHAR(123)
    , @Recipients VARCHAR(123)
    , @IsDBMailEnabled BIT
    , @MailProfile VARCHAR(123)
    , @IsClustered VARCHAR(1) -- this is because SERVERPROPERTY('IsClustered') is a sql_variant data type
    , @CurrentNodeName VARCHAR(123)
    , @InstanceName VARCHAR(123)
    , @RestartTime VARCHAR(123)
    
    set @Recipients = 'email@company.com' -- specify the recipients here, separate with ; 
    
    SELECT @InstanceName = CONVERT(VARCHAR, SERVERPROPERTY('ServerName'))
    , @IsClustered = CONVERT (VARCHAR, SERVERPROPERTY('IsClustered'))
    , @CurrentNodeName = CONVERT (VARCHAR, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
    
    -- SQL Server service has been restarted
    IF (
    	SELECT DATEDIFF(MI, CREATE_DATE,GETDATE() ) 
    	FROM SYS.DATABASES 
    	WHERE NAME = 'TEMPDB'
    	) <= 3 -- SQL Server service restarted in the past 3 minutes
    	BEGIN
    		SELECT @RestartTime = CONVERT(VARCHAR, create_date, 13)
    		FROM SYS.databases
    		WHERE NAME = 'TEMPDB'
    		
    		SET @Message = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted at: ' +@RestartTime
    		SET @Subject = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted!'
    
    		
    		IF @IsClustered = '1'
    			BEGIN
    				SET @Message = @Message + ' and the current active node is: ' +@CurrentNodeName+ '.'
    				SET @Subject = @Subject + ' The current active node is: ' +@CurrentNodeName+ '.' 
    			END
    	END
    ELSE 	
    	BEGIN
    		SET @Message = @InstanceName + ' SQL Server Agent service has been restarted!'
    		SET @Subject = @InstanceName + ' SQL Server Agent service restarted!'
    	END 
    
    SET @Message = @Message + CHAR(10)
    SET @Message = @Message + 'If this is not a planned maintenace. Please verify the status of the databases before restarting any application services and review the log files to find the possible root causes!'	
    
    -- Find the database mail profile name
    create table #dbmail_profile
    (
    profileid int null,
    profilename varchar(125) null,
    accountid int null,
    accountname varchar(125) null,
    sequencenumber int null
    )
    insert #dbmail_profile
    EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
    
    select @MailProfile = profilename
    from #dbmail_profile
    where sequencenumber = 1
    
    drop table #dbmail_profile
    
    EXEC msdb.dbo.sp_send_dbmail
    	@profile_name = @MailProfile,
    	@recipients = @Recipients,
    	@subject = @Subject,
    	@body_format = 'TEXT',
    	@body = @Message;
    
  3. Schedule a SQL Server Agent job to run the T-SQL code whenever SQL Server Agent starts.
  4. Create a SQL Server Agent job the way as you have always been doing, then on the schedule page when choosing a “schedule type,” select the “Start automatically when SQL Server Agent starts” option.
    New Job Schedule

If you need to also identify the previous active node for the failover cluster in addition to the current active node, you will need to create a table in a “utility” database or the master database to store the cluster nodes information, then on SQL Server service restarts make a comparison of the previous active node and the current active node and embed the information on the notification email.

If you wish to check if other instances of SQL Server are running and you don’t have a third party monitoring system, you can schedule a SQL Server Agent job to ping the host of the other instances or check for their SQL Server Agent and SQL Server services statuses.

 

REFERENCES:

SERVERPROPERTY
http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx

DATEDIFF
http://msdn.microsoft.com/en-us/library/ms189794(v=sql.105).aspx

How to get notified on new database(s) creation(s)?

You wish to document the databases in your SQL Server environment for future references in addition to compile a run-book for troubleshooting, disk space planning, and capacity planning purposes. But, due to the number of databases and servers in your SQL Server environment, it is not an easy task. The application support team can install new applications without your knowledge or your team’s approval and new databases will be created when the new applications are installed. There was and still is no standard procedure on documentations. While you can’t time-travel back to undo this to help the team and yourself understand the importance of documentations, it is never too late to start doing the right things. You can start by documenting the new databases whenever they are created and then document the databases created in the past when you get a chance. In addition, it is critical to backup the newly created database(s) to have a baseline full backup or else the transaction log backups will fail for the newly created database(s) that are in full recovery model and no full backups have been ran (if your transaction log backup job doesn’t have the mechanism to check of missing full backups and then run the full backups first before running the transaction log backups).  Sure, you can try to ask the application team to give you advanced notices before installing the new applications that will have new databases to be created. Most likely due to their heavy workload, they will be too busy or wouldn’t be able to remember to notify you or the DBA team. Wouldn’t it be nice if you can automate the process of notifying the team on new database(s) creations?

Here are the overview steps and concepts:

  1. Develop the T-SQL code to find new databases in the past hour.
  2. Schedule a SQL Server Agent job to run the T-SQL code on an hourly basis and notify the designated recipients if there are new database(s).

Here are the implementation steps:

  1. Develop the T-SQL code to find new databases in the past hour.
  2. -- Alert on the new database(s) created in the past hour
    -- Replace with your recipients in line #24
    
    -- declare the variable for interval hours to detect the new database(s)
    DECLARE @IntervalInHours TINYINT -- check new database(s) in the past hour(s)
    SET @IntervalInHours = 1 -- replace with the number of hours you wish to check for new database(s)
    
    -- check for new database(s)
    IF EXISTS(	
    	SELECT 1 
    	FROM master.sys.databases db 
    	WHERE DATEDIFF(HOUR, db.create_date, GETDATE()) <= @intervalInHours 
    		AND db.name <> 'tempdb') 
    BEGIN -- new database(s) exist(s)
    
    -- declare the required variables 
    DECLARE @Recipients VARCHAR(123) -- recipients for this alert
    , @IsDBMailEnabled BIT
    , @MailProfile VARCHAR(123)
    , @MailSubject VARCHAR(123)
    , @Subject VARCHAR(123)
    , @TableHTML VARCHAR(MAX)
    
    SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;
    
    -- check if database mail is enabled
    SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
    FROM sys.configurations
    WHERE name = 'Database Mail XPs'
    
    IF @IsDBMailEnabled = 0
    	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)
    
    ELSE
    	BEGIN
    		SELECT @TableHTML = 
    		'<html><table border = "1">' +
    		'<tr>
    			<th>Database Name</th>
    			<th>Create Date</th>
    			<th>DB Owner</th>
    			<th>Compatibility Level</th>
    			<th>Recovery Model</th>
    		</tr>'
    		
    		SELECT @TableHTML = @TableHTML +
    			'<tr>' +
    			'<td>' + name + '</td>' +
    			'<td>' + CONVERT(VARCHAR, create_date, 13) + '</td>' +
    			'<td>' + suser_sname(owner_sid) + '</td>' +
    			'<td>' + CONVERT(VARCHAR(3),compatibility_level) + '</td>' +
    			'<td>' + recovery_model_desc + '</td></tr>'
    		FROM master.sys.databases db 
    		WHERE (DATEDIFF(HOUR, db.create_date, GETDATE()) <= @IntervalInHours)
    				AND (db.name <> 'tempdb') 
    		SELECT @TableHTML = @TableHTML + '</table></html>'
    		
    		-- Send email notification
    		SELECT @MailSubject = CONVERT(VARCHAR(50),@@servername) + ' New database(s) alert!'
    		
    		-- Find the database mail profile name
    		create table #dbmail_profile
    		(
    		profileid int null,
    		profilename varchar(125) null,
    		accountid int null,
    		accountname varchar(125) null,
    		sequencenumber int null
    		)
    		insert #dbmail_profile
    		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
    
    		select @MailProfile = profilename
    		from #dbmail_profile
    		where sequencenumber = 1
    
    		drop table #dbmail_profile
    
    		EXEC msdb.dbo.sp_send_dbmail
    			@profile_name = @MailProfile,
    			@recipients = @Recipients,
    			@subject = @MailSubject,
    			@body_format = 'HTML',
    			@body = @TableHTML;
    	END
    END
    
  3. Schedule a SQL Server Agent job to run the T-SQL code on an hourly basis and notify the designated recipients if there are new database(s).

REFERENCES:

SUSER_SNAME()
http://msdn.microsoft.com/en-us/library/ms174427(v=sql.105).aspx