Sort warnings (last hour)

| 1 Comment | 4,793 views
Categories: ,

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)

This metric measures the number of sort warning events that occurred in the last hour. Sort warning operations that do not fit into memory can degrade query and server performance because multiple passes over the data are required to sort the data. It is important to optimize queries by removing them.

For more information, see http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week—sort/.

Note: This metric relies on SQL Server default trace. If this is not enabled, the metric will not work.

Metric definition

Metric Name:
Sort warnings (last hour)
Note: This metric relies on SQL Server default trace. If this is not enabled, the metric will not work.
Description:

This metric measures the number of sort warnings events that occurred in the last hour. Sort warning operations that do not fit into memory can degrade query and server performance because multiple passes over the data are required to sort the data. It is important to optimize queries by removing them.

For more information, see http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week---sort/.

Note: This metric relies on SQL Server default trace. If this is not enabled, the metric will not work.

Enter the T-SQL query that will collect data:
DECLARE @Str NVARCHAR(MAX);

SELECT @Str = CONVERT(NVARCHAR(MAX), Value)
  FROM :: FN_TRACE_GETINFO(DEFAULT) AS Tab
  WHERE Tab.Property = 2
    AND Traceid = 1;
 
SELECT COUNT(*) AS Cnt
  FROM FN_TRACE_GETTABLE(@Str, DEFAULT) AS trace 
  INNER JOIN sys.trace_events te
  ON te.trace_event_id = trace.EventClass
  WHERE te.name LIKE 'Sort Warnings'
    AND trace.StartTime >= DATEADD(hh, -1, GETDATE());
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every hour
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
Sort warnings
Description:

This alert is raised when the number of sort warnings events that occurred in the last hour goes above a specified threshold. Sort warning operations that do not fit into memory can degrade query and server performance because multiple passes over the data are required to sort the data. It is important to optimize queries by removing them.

For more information, see http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week---sort/.

Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:50
Medium:10
Low:0
Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)