Number of ascending statistics

This metric returns the number of ascending statistics per database. A common problem relating to distribution statistics is associated with “ascending value columns” in a table.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

This metric returns the number of ascending statistics per database. A common problem relating to distribution statistics is associated with “ascending value columns” in a table. This generally happens when a large table has ascending values, and the most recent rows are the ones most commonly being accessed. When data values in a column ascend, most new insertions are beyond the range covered by the distribution statistics. This can lead to poorly performing plans.

Use this metric if you want to monitor the columns marked as ascending. After finding these columns, look at the queries on those tables to make sure you are getting an optimized plan for the query.

For more information, see http://www.simple-talk.com/sql/database-administration/statistics-on-ascending-columns/

The following query can be used to help you analyze which statistics are “branded” as ascending:

[sql]DBCC TRACEON(2388) WITH NO_INFOMSGS
SET NOCOUNT ON
IF OBJECT_ID(‘tempdb.dbo.#TMP_stats’) IS NOT NULL
DROP TABLE #TMP_stats

IF OBJECT_ID(‘tempdb.dbo.#TMP’) IS NOT NULL
DROP TABLE #TMP

CREATE TABLE #TMP(ROWID Int IDENTITY(1,1) PRIMARY KEY,
TableName NVarChar(800),
StatsName NVarChar(800),
"Updated" NVarChar(800),
"Table Cardinality" BigInt,
"Snapshot Ctr" BigInt,
"Steps" BigInt,
"Density" Float,
"Rows Above" Float,
"Rows Below" Float,
"Squared Variance Error" Float,
"Inserts Since Last Update" Float,
"Deletes Since Last Update" Float,
"Leading column Type" NVarChar(200))

;with CTE_1
as
(
select object_schema_name(a.object_id) schemaname,
object_name(a.object_id) as ‘TableName’,
a.name as ‘StatsName’,
stats_date(a.object_id, stats_id) as stats_last_updated_time,
(SELECT SUM(p.rows)
FROM sys.partitions p
WHERE a.object_id = p.object_id
and index_id <= 1) as number_of_rows
from sys.stats as a
inner join sys.objects as b
on a.object_id = b.object_id
where b.type = ‘U’
)
select IDENTITY(Int , 1,1) ROWID, *, ‘DBCC SHOW_STATISTICS ("’ + schemaname + ‘.’ + tablename + ‘",’ + statsname + ‘) WITH NO_INFOMSGS’ AS SQL
INTO #TMP_stats
from CTE_1
where number_of_rows > 1000
CREATE CLUSTERED INDEX ix ON #TMP_stats(ROWID)

DECLARE @SQL NVarChar(MAX),
@TableName NVarChar(800),
@StatsName NVarChar(800),
@ROWID Int,
@LastID Int,
@MaxID Int

SELECT @ROWID = 0,
@SQL = ”,
@TableName = ”,
@StatsName = ”,
@LastID = 0,
@MaxID = 2147483647

SELECT TOP 1
@ROWID = ROWID,
@SQL = SQL,
@TableName = TableName,
@StatsName = StatsName
FROM #TMP_stats
WHERE ROWID > @ROWID
ORDER BY ROWID

WHILE @@ROWCOUNT > 0
BEGIN
PRINT @SQL

INSERT INTO #TMP ("Updated","Table Cardinality","Snapshot Ctr","Steps","Density","Rows Above","Rows Below","Squared Variance Error","Inserts Since Last Update","Deletes Since Last Update","Leading column Type")
EXEC (@SQL)

SELECT @LastID = MIN(ROWID), @MaxID = MAX(ROWID)
FROM #TMP
WHERE ROWID BETWEEN (SELECT MIN(a.ROWID) FROM #TMP a WHERE a.TableName IS NULL) AND (SELECT MAX(b.ROWID) FROM #TMP b WHERE b.TableName IS NULL)

UPDATE #TMP SET TableName = @TableName, StatsName = @StatsName
WHERE ROWID BETWEEN @LastID AND @MaxID

SELECT TOP 1
@ROWID = ROWID,
@SQL = SQL,
@TableName = TableName,
@StatsName = StatsName
FROM #TMP_stats
WHERE ROWID > @ROWID
ORDER BY ROWID
END
DBCC TRACEOFF(2388) WITH NO_INFOMSGS

SELECT TableName,
StatsName,
(SELECT SUM(p.rows)
FROM sys.partitions p
WHERE OBJECT_ID(TableName) = p.object_id
AND index_id <= 1) as number_of_rows
FROM #TMP
WHERE "Leading column Type" = ‘Ascending’
ORDER BY number_of_rows DESC[/sql]

Note: The T-SQL query for this metric runs the DBCC TRACEON, command and you’ll need a sysadmin privilege to run this hint. It also uses undocumented features that may not be supported by future service packs or SQL Server version.

Number of ascending statistics, 4.0 out of 5 based on 1 rating

Metric definition

Name

Number of ascending statistics

Description

This metric returns the number of ascending statistics per database. A common problem relating to distribution statistics is associated with “ascending value columns” in a table. This generally happens when a large table has ascending values, and the most recent rows are the ones most commonly being accessed. When data values in a column ascend, most new insertions are beyond the range covered by the distribution statistics. This can lead to poorly performing plans.

Use this metric if you want to monitor the columns marked as ascending. After finding these columns, look at the queries on those tables to make sure you are getting an optimized plan for the query.

For more information, see http://www.simple-talk.com/sql/database-administration/statistics-on-ascending-columns/

The following query can be used to help you analyze which statistics are “branded” as ascending:

DBCC TRACEON(2388) WITH NO_INFOMSGS
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#TMP_stats') IS NOT NULL 
  DROP TABLE #TMP_stats

IF OBJECT_ID('tempdb.dbo.#TMP') IS NOT NULL 
  DROP TABLE #TMP

CREATE TABLE #TMP
  (
   ROWID INT IDENTITY(1, 1)
             PRIMARY KEY,
   TableName NVARCHAR(800),
   StatsName NVARCHAR(800),
   "Updated" NVARCHAR(800),
   "Table Cardinality" BIGINT,
   "Snapshot Ctr" BIGINT,
   "Steps" BIGINT,
   "Density" FLOAT,
   "Rows Above" FLOAT,
   "Rows Below" FLOAT,
   "Squared Variance Error" FLOAT,
   "Inserts Since Last Update" FLOAT,
   "Deletes Since Last Update" FLOAT,
   "Leading column Type" NVARCHAR(200)
  );
WITH  CTE_1
        AS (SELECT OBJECT_SCHEMA_NAME(a.object_id) schemaname,
                OBJECT_NAME(a.object_id) AS 'TableName',
                a.name AS 'StatsName',
                STATS_DATE(a.object_id, stats_id) AS stats_last_updated_time,
                (SELECT SUM(p.rows)
                  FROM sys.partitions p
                  WHERE a.object_id = p.object_id
                    AND index_id <= 1
                ) AS number_of_rows
              FROM sys.stats AS a 
              INNER JOIN sys.objects AS b
              ON
                a.object_id = b.object_id
              WHERE b.type = 'U'
           )
  SELECT IDENTITY( INT , 1,1 ) ROWID,
      *,
      'DBCC SHOW_STATISTICS ("' + schemaname + '.' + tablename + '",'
      + statsname + ') WITH NO_INFOMSGS' AS SQL
    INTO #TMP_stats
    FROM CTE_1
    WHERE number_of_rows > 1000
CREATE CLUSTERED INDEX ix ON #TMP_stats(ROWID)

DECLARE @SQL NVARCHAR(MAX),
  @TableName NVARCHAR(800),
  @StatsName NVARCHAR(800),
  @ROWID INT,
  @LastID INT,
  @MaxID INT

SELECT @ROWID = 0,
    @SQL = '',
    @TableName = '',
    @StatsName = '',
    @LastID = 0,
    @MaxID = 2147483647

SELECT TOP 1 @ROWID = ROWID,
    @SQL = SQL,
    @TableName = TableName,
    @StatsName = StatsName
  FROM #TMP_stats
  WHERE ROWID > @ROWID
  ORDER BY ROWID

WHILE @@ROWCOUNT > 0 
  BEGIN
    PRINT @SQL

    INSERT INTO #TMP
        ("Updated",
         "Table Cardinality",
         "Snapshot Ctr",
         "Steps",
         "Density",
         "Rows Above",
         "Rows Below",
         "Squared Variance Error",
         "Inserts Since Last Update",
         "Deletes Since Last Update",
         "Leading column Type")
        EXEC (@SQL)

    SELECT @LastID = MIN(ROWID),
        @MaxID = MAX(ROWID)
      FROM #TMP
      WHERE ROWID BETWEEN (SELECT MIN(a.ROWID)
                            FROM #TMP a
                            WHERE a.TableName IS NULL
                          )
                  AND     (SELECT MAX(b.ROWID)
                            FROM #TMP b
                            WHERE b.TableName IS NULL
                          )

    UPDATE #TMP
      SET TableName = @TableName,
          StatsName = @StatsName
      WHERE ROWID BETWEEN @LastID AND @MaxID

    SELECT TOP 1 @ROWID = ROWID,
        @SQL = SQL,
        @TableName = TableName,
        @StatsName = StatsName
      FROM #TMP_stats
      WHERE ROWID > @ROWID
      ORDER BY ROWID
  END
DBCC TRACEOFF(2388) WITH NO_INFOMSGS

SELECT TableName,
    StatsName,
    (SELECT SUM(p.rows)
      FROM sys.partitions p
      WHERE OBJECT_ID(TableName) = p.object_id
        AND index_id <= 1
    ) AS number_of_rows
  FROM #TMP
  WHERE "Leading column Type" = 'Ascending'
  ORDER BY number_of_rows DESC

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

1

Collection frequency

86400

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Increased number of ascending statistics

Description

This alert is raised when the number of ascending statistics per database matches the set thresholds. A common problem relating to distribution statistics is associated with “ascending value columns” in a table. This generally happens when a large table has ascending values, and the most recent rows are the ones most commonly being accessed. When data values in a column ascend, most new insertions are beyond the range covered by the distribution statistics. This can lead to poorly performing plans.

After finding these columns, look at the queries on those tables to make sure you are getting an optimized plan for the query.

For more information, see http://www.simple-talk.com/sql/database-administration/statistics-on-ascending-columns/

The following query can be used to help you analyze which statistics are “branded” as ascending:

DBCC TRACEON(2388) WITH NO_INFOMSGS
SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#TMP_stats') IS NOT NULL 
  DROP TABLE #TMP_stats

IF OBJECT_ID('tempdb.dbo.#TMP') IS NOT NULL 
  DROP TABLE #TMP

CREATE TABLE #TMP
  (
   ROWID INT IDENTITY(1, 1)
             PRIMARY KEY,
   TableName NVARCHAR(800),
   StatsName NVARCHAR(800),
   "Updated" NVARCHAR(800),
   "Table Cardinality" BIGINT,
   "Snapshot Ctr" BIGINT,
   "Steps" BIGINT,
   "Density" FLOAT,
   "Rows Above" FLOAT,
   "Rows Below" FLOAT,
   "Squared Variance Error" FLOAT,
   "Inserts Since Last Update" FLOAT,
   "Deletes Since Last Update" FLOAT,
   "Leading column Type" NVARCHAR(200)
  );
WITH  CTE_1
        AS (SELECT OBJECT_SCHEMA_NAME(a.object_id) schemaname,
                OBJECT_NAME(a.object_id) AS 'TableName',
                a.name AS 'StatsName',
                STATS_DATE(a.object_id, stats_id) AS stats_last_updated_time,
                (SELECT SUM(p.rows)
                  FROM sys.partitions p
                  WHERE a.object_id = p.object_id
                    AND index_id <= 1
                ) AS number_of_rows
              FROM sys.stats AS a 
              INNER JOIN sys.objects AS b
              ON
                a.object_id = b.object_id
              WHERE b.type = 'U'
           )
  SELECT IDENTITY( INT , 1,1 ) ROWID,
      *,
      'DBCC SHOW_STATISTICS ("' + schemaname + '.' + tablename + '",'
      + statsname + ') WITH NO_INFOMSGS' AS SQL
    INTO #TMP_stats
    FROM CTE_1
    WHERE number_of_rows > 1000
CREATE CLUSTERED INDEX ix ON #TMP_stats(ROWID)

DECLARE @SQL NVARCHAR(MAX),
  @TableName NVARCHAR(800),
  @StatsName NVARCHAR(800),
  @ROWID INT,
  @LastID INT,
  @MaxID INT

SELECT @ROWID = 0,
    @SQL = '',
    @TableName = '',
    @StatsName = '',
    @LastID = 0,
    @MaxID = 2147483647

SELECT TOP 1 @ROWID = ROWID,
    @SQL = SQL,
    @TableName = TableName,
    @StatsName = StatsName
  FROM #TMP_stats
  WHERE ROWID > @ROWID
  ORDER BY ROWID

WHILE @@ROWCOUNT > 0 
  BEGIN
    PRINT @SQL

    INSERT INTO #TMP
        ("Updated",
         "Table Cardinality",
         "Snapshot Ctr",
         "Steps",
         "Density",
         "Rows Above",
         "Rows Below",
         "Squared Variance Error",
         "Inserts Since Last Update",
         "Deletes Since Last Update",
         "Leading column Type")
        EXEC (@SQL)

    SELECT @LastID = MIN(ROWID),
        @MaxID = MAX(ROWID)
      FROM #TMP
      WHERE ROWID BETWEEN (SELECT MIN(a.ROWID)
                            FROM #TMP a
                            WHERE a.TableName IS NULL
                          )
                  AND     (SELECT MAX(b.ROWID)
                            FROM #TMP b
                            WHERE b.TableName IS NULL
                          )

    UPDATE #TMP
      SET TableName = @TableName,
          StatsName = @StatsName
      WHERE ROWID BETWEEN @LastID AND @MaxID

    SELECT TOP 1 @ROWID = ROWID,
        @SQL = SQL,
        @TableName = TableName,
        @StatsName = StatsName
      FROM #TMP_stats
      WHERE ROWID > @ROWID
      ORDER BY ROWID
  END
DBCC TRACEOFF(2388) WITH NO_INFOMSGS

SELECT TableName,
    StatsName,
    (SELECT SUM(p.rows)
      FROM sys.partitions p
      WHERE OBJECT_ID(TableName) = p.object_id
        AND index_id <= 1
    ) AS number_of_rows
  FROM #TMP
  WHERE "Leading column Type" = 'Ascending'
  ORDER BY number_of_rows DESC

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

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

1 collection

Alert is

Enabled