Number of ascending statistics

| 0 comments | 1,451 views
Categories:

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

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

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.

Metric definition

Metric 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
Enter the T-SQL query that will collect data:
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  COUNT(*) AS cnt
FROM    #TMP
WHERE   "Leading column Type" = 'Ascending';
Note: This T-SQL query 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.
Select instances to collect from:
Select all
Choose databases to collect from:
All user databases
Set collection frequency:
Collect data every day
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:
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 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)