{"name":"Number of ascending statistics","description":"<p>This metric returns the number of ascending statistics per database. A common problem relating to distribution statistics is associated with \u201cascending value columns\u201d 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.<\/p>\r\n<p>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.<\/p>\r\n<p>For more information, see <a href=\"http:\/\/www.simple-talk.com\/sql\/database-administration\/statistics-on-ascending-columns\/\">http:\/\/www.simple-talk.com\/sql\/database-administration\/statistics-on-ascending-columns\/<\/a><\/p>\r\n\r\n<p>The following query can be used to help you analyze which statistics are \u201cbranded\u201d as ascending:<\/p>\r\n<pre class=\"sql\">DBCC TRACEON(2388) WITH NO_INFOMSGS\r\nSET NOCOUNT ON\r\nIF OBJECT_ID('tempdb.dbo.#TMP_stats') IS NOT NULL \r\n  DROP TABLE #TMP_stats\r\n\r\nIF OBJECT_ID('tempdb.dbo.#TMP') IS NOT NULL \r\n  DROP TABLE #TMP\r\n\r\nCREATE TABLE #TMP\r\n  (\r\n   ROWID INT IDENTITY(1, 1)\r\n             PRIMARY KEY,\r\n   TableName NVARCHAR(800),\r\n   StatsName NVARCHAR(800),\r\n   \"Updated\" NVARCHAR(800),\r\n   \"Table Cardinality\" BIGINT,\r\n   \"Snapshot Ctr\" BIGINT,\r\n   \"Steps\" BIGINT,\r\n   \"Density\" FLOAT,\r\n   \"Rows Above\" FLOAT,\r\n   \"Rows Below\" FLOAT,\r\n   \"Squared Variance Error\" FLOAT,\r\n   \"Inserts Since Last Update\" FLOAT,\r\n   \"Deletes Since Last Update\" FLOAT,\r\n   \"Leading column Type\" NVARCHAR(200)\r\n  );\r\nWITH  CTE_1\r\n        AS (SELECT OBJECT_SCHEMA_NAME(a.object_id) schemaname,\r\n                OBJECT_NAME(a.object_id) AS 'TableName',\r\n                a.name AS 'StatsName',\r\n                STATS_DATE(a.object_id, stats_id) AS stats_last_updated_time,\r\n                (SELECT SUM(p.rows)\r\n                  FROM sys.partitions p\r\n                  WHERE a.object_id = p.object_id\r\n                    AND index_id <= 1\r\n                ) AS number_of_rows\r\n              FROM sys.stats AS a \r\n              INNER JOIN sys.objects AS b\r\n              ON\r\n                a.object_id = b.object_id\r\n              WHERE b.type = 'U'\r\n           )\r\n  SELECT IDENTITY( INT , 1,1 ) ROWID,\r\n      *,\r\n      'DBCC SHOW_STATISTICS (\"' + schemaname + '.' + tablename + '\",'\r\n      + statsname + ') WITH NO_INFOMSGS' AS SQL\r\n    INTO #TMP_stats\r\n    FROM CTE_1\r\n    WHERE number_of_rows > 1000\r\nCREATE CLUSTERED INDEX ix ON #TMP_stats(ROWID)\r\n\r\nDECLARE @SQL NVARCHAR(MAX),\r\n  @TableName NVARCHAR(800),\r\n  @StatsName NVARCHAR(800),\r\n  @ROWID INT,\r\n  @LastID INT,\r\n  @MaxID INT\r\n\r\nSELECT @ROWID = 0,\r\n    @SQL = '',\r\n    @TableName = '',\r\n    @StatsName = '',\r\n    @LastID = 0,\r\n    @MaxID = 2147483647\r\n\r\nSELECT TOP 1 @ROWID = ROWID,\r\n    @SQL = SQL,\r\n    @TableName = TableName,\r\n    @StatsName = StatsName\r\n  FROM #TMP_stats\r\n  WHERE ROWID > @ROWID\r\n  ORDER BY ROWID\r\n\r\nWHILE @@ROWCOUNT > 0 \r\n  BEGIN\r\n    PRINT @SQL\r\n\r\n    INSERT INTO #TMP\r\n        (\"Updated\",\r\n         \"Table Cardinality\",\r\n         \"Snapshot Ctr\",\r\n         \"Steps\",\r\n         \"Density\",\r\n         \"Rows Above\",\r\n         \"Rows Below\",\r\n         \"Squared Variance Error\",\r\n         \"Inserts Since Last Update\",\r\n         \"Deletes Since Last Update\",\r\n         \"Leading column Type\")\r\n        EXEC (@SQL)\r\n\r\n    SELECT @LastID = MIN(ROWID),\r\n        @MaxID = MAX(ROWID)\r\n      FROM #TMP\r\n      WHERE ROWID BETWEEN (SELECT MIN(a.ROWID)\r\n                            FROM #TMP a\r\n                            WHERE a.TableName IS NULL\r\n                          )\r\n                  AND     (SELECT MAX(b.ROWID)\r\n                            FROM #TMP b\r\n                            WHERE b.TableName IS NULL\r\n                          )\r\n\r\n    UPDATE #TMP\r\n      SET TableName = @TableName,\r\n          StatsName = @StatsName\r\n      WHERE ROWID BETWEEN @LastID AND @MaxID\r\n\r\n    SELECT TOP 1 @ROWID = ROWID,\r\n        @SQL = SQL,\r\n        @TableName = TableName,\r\n        @StatsName = StatsName\r\n      FROM #TMP_stats\r\n      WHERE ROWID > @ROWID\r\n      ORDER BY ROWID\r\n  END\r\nDBCC TRACEOFF(2388) WITH NO_INFOMSGS\r\n\r\nSELECT TableName,\r\n    StatsName,\r\n    (SELECT SUM(p.rows)\r\n      FROM sys.partitions p\r\n      WHERE OBJECT_ID(TableName) = p.object_id\r\n        AND index_id <= 1\r\n    ) AS number_of_rows\r\n  FROM #TMP\r\n  WHERE \"Leading column Type\" = 'Ascending'\r\n  ORDER BY number_of_rows DESC<\/pre>","tsql":"DBCC TRACEON(2388) WITH NO_INFOMSGS;\r\nSET NOCOUNT ON;\r\nIF OBJECT_ID('tempdb.dbo.#TMP_stats') IS NOT NULL \r\n    DROP TABLE #TMP_stats;\r\n\r\nIF OBJECT_ID('tempdb.dbo.#TMP') IS NOT NULL \r\n    DROP TABLE #TMP;\r\n\r\nCREATE TABLE #TMP\r\n    (\r\n      ROWID INT IDENTITY(1, 1)\r\n                PRIMARY KEY ,\r\n      TableName NVARCHAR(800) ,\r\n      StatsName NVARCHAR(800) ,\r\n      \"Updated\" NVARCHAR(800) ,\r\n      \"Table Cardinality\" BIGINT ,\r\n      \"Snapshot Ctr\" BIGINT ,\r\n      \"Steps\" BIGINT ,\r\n      \"Density\" FLOAT ,\r\n      \"Rows Above\" FLOAT ,\r\n      \"Rows Below\" FLOAT ,\r\n      \"Squared Variance Error\" FLOAT ,\r\n      \"Inserts Since Last Update\" FLOAT ,\r\n      \"Deletes Since Last Update\" FLOAT ,\r\n      \"Leading column Type\" NVARCHAR(200)\r\n    );\r\nWITH    CTE_1\r\n          AS ( SELECT   OBJECT_SCHEMA_NAME(a.object_id) schemaname ,\r\n                        OBJECT_NAME(a.object_id) AS 'TableName' ,\r\n                        a.name AS 'StatsName' ,\r\n                        STATS_DATE(a.object_id, stats_id) AS stats_last_updated_time ,\r\n                        ( SELECT    SUM(p.rows)\r\n                          FROM      sys.partitions p\r\n                          WHERE     a.object_id = p.object_id\r\n                                    AND index_id <= 1\r\n                        ) AS number_of_rows\r\n               FROM     sys.stats AS a\r\n                        INNER JOIN sys.objects AS b ON a.object_id = b.object_id\r\n               WHERE    b.type = 'U'\r\n             )\r\n    SELECT  IDENTITY( INT , 1,1 ) ROWID ,\r\n            * ,\r\n            'DBCC SHOW_STATISTICS (\"' + schemaname + '.'\r\n            + tablename + '\",' + statsname\r\n            + ') WITH NO_INFOMSGS' AS SQL\r\n    INTO    #TMP_stats\r\n    FROM    CTE_1\r\n    WHERE   number_of_rows > 1000;\r\nCREATE CLUSTERED INDEX ix ON #TMP_stats(ROWID);\r\n\r\nDECLARE @SQL NVARCHAR(MAX) ,\r\n    @TableName NVARCHAR(800) ,\r\n    @StatsName NVARCHAR(800) ,\r\n    @ROWID INT ,\r\n    @LastID INT ,\r\n    @MaxID INT;\r\n\r\nSELECT  @ROWID = 0 ,\r\n        @SQL = '' ,\r\n        @TableName = '' ,\r\n        @StatsName = '' ,\r\n        @LastID = 0 ,\r\n        @MaxID = 2147483647;\r\n\r\nSELECT TOP 1\r\n        @ROWID = ROWID ,\r\n        @SQL = SQL ,\r\n        @TableName = TableName ,\r\n        @StatsName = StatsName\r\nFROM    #TMP_stats\r\nWHERE   ROWID > @ROWID\r\nORDER BY ROWID;\r\n\r\nWHILE @@ROWCOUNT > 0 \r\n    BEGIN\r\n        PRINT @SQL;\r\n\r\n        INSERT  INTO #TMP\r\n                ( \"Updated\" ,\r\n                  \"Table Cardinality\" ,\r\n                  \"Snapshot Ctr\" ,\r\n                  \"Steps\" ,\r\n                  \"Density\" ,\r\n                  \"Rows Above\" ,\r\n                  \"Rows Below\" ,\r\n                  \"Squared Variance Error\" ,\r\n                  \"Inserts Since Last Update\" ,\r\n                  \"Deletes Since Last Update\" ,\r\n                  \"Leading column Type\"\r\n                )\r\n                EXEC ( @SQL\r\n                    );\r\n\r\n        SELECT  @LastID = MIN(ROWID) ,\r\n                @MaxID = MAX(ROWID)\r\n        FROM    #TMP\r\n        WHERE   ROWID BETWEEN ( SELECT  MIN(a.ROWID)\r\n                                FROM    #TMP a\r\n                                WHERE   a.TableName IS NULL\r\n                              )\r\n                      AND     ( SELECT  MAX(b.ROWID)\r\n                                FROM    #TMP b\r\n                                WHERE   b.TableName IS NULL\r\n                              );\r\n\r\n        UPDATE  #TMP\r\n        SET     TableName = @TableName ,\r\n                StatsName = @StatsName\r\n        WHERE   ROWID BETWEEN @LastID AND @MaxID;\r\n\r\n        SELECT TOP 1\r\n                @ROWID = ROWID ,\r\n                @SQL = SQL ,\r\n                @TableName = TableName ,\r\n                @StatsName = StatsName\r\n        FROM    #TMP_stats\r\n        WHERE   ROWID > @ROWID\r\n        ORDER BY ROWID;\r\n    END;\r\nDBCC TRACEOFF(2388) WITH NO_INFOMSGS;\r\n\r\nSELECT  COUNT(*) AS cnt\r\nFROM    #TMP\r\nWHERE   \"Leading column Type\" = 'Ascending';\r\n","instances":true,"frequency":86400,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Increased number of ascending statistics","alertdescription":"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 \u201cascending value columns\u201d 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.<\/p>\r\n<p>After finding these columns, look at the queries on those tables to make sure you are getting an optimized plan for the query.<\/p>\r\n<p>For more information, see <a href=\"http:\/\/www.simple-talk.com\/sql\/database-administration\/statistics-on-ascending-columns\/\">http:\/\/www.simple-talk.com\/sql\/database-administration\/statistics-on-ascending-columns\/<\/a><\/p>\r\n\r\n<p>The following query can be used to help you analyze which statistics are \u201cbranded\u201d as ascending:<\/p>\r\n<pre class=\"sql\">DBCC TRACEON(2388) WITH NO_INFOMSGS\r\nSET NOCOUNT ON\r\nIF OBJECT_ID('tempdb.dbo.#TMP_stats') IS NOT NULL \r\n  DROP TABLE #TMP_stats\r\n\r\nIF OBJECT_ID('tempdb.dbo.#TMP') IS NOT NULL \r\n  DROP TABLE #TMP\r\n\r\nCREATE TABLE #TMP\r\n  (\r\n   ROWID INT IDENTITY(1, 1)\r\n             PRIMARY KEY,\r\n   TableName NVARCHAR(800),\r\n   StatsName NVARCHAR(800),\r\n   \"Updated\" NVARCHAR(800),\r\n   \"Table Cardinality\" BIGINT,\r\n   \"Snapshot Ctr\" BIGINT,\r\n   \"Steps\" BIGINT,\r\n   \"Density\" FLOAT,\r\n   \"Rows Above\" FLOAT,\r\n   \"Rows Below\" FLOAT,\r\n   \"Squared Variance Error\" FLOAT,\r\n   \"Inserts Since Last Update\" FLOAT,\r\n   \"Deletes Since Last Update\" FLOAT,\r\n   \"Leading column Type\" NVARCHAR(200)\r\n  );\r\nWITH  CTE_1\r\n        AS (SELECT OBJECT_SCHEMA_NAME(a.object_id) schemaname,\r\n                OBJECT_NAME(a.object_id) AS 'TableName',\r\n                a.name AS 'StatsName',\r\n                STATS_DATE(a.object_id, stats_id) AS stats_last_updated_time,\r\n                (SELECT SUM(p.rows)\r\n                  FROM sys.partitions p\r\n                  WHERE a.object_id = p.object_id\r\n                    AND index_id <= 1\r\n                ) AS number_of_rows\r\n              FROM sys.stats AS a \r\n              INNER JOIN sys.objects AS b\r\n              ON\r\n                a.object_id = b.object_id\r\n              WHERE b.type = 'U'\r\n           )\r\n  SELECT IDENTITY( INT , 1,1 ) ROWID,\r\n      *,\r\n      'DBCC SHOW_STATISTICS (\"' + schemaname + '.' + tablename + '\",'\r\n      + statsname + ') WITH NO_INFOMSGS' AS SQL\r\n    INTO #TMP_stats\r\n    FROM CTE_1\r\n    WHERE number_of_rows > 1000\r\nCREATE CLUSTERED INDEX ix ON #TMP_stats(ROWID)\r\n\r\nDECLARE @SQL NVARCHAR(MAX),\r\n  @TableName NVARCHAR(800),\r\n  @StatsName NVARCHAR(800),\r\n  @ROWID INT,\r\n  @LastID INT,\r\n  @MaxID INT\r\n\r\nSELECT @ROWID = 0,\r\n    @SQL = '',\r\n    @TableName = '',\r\n    @StatsName = '',\r\n    @LastID = 0,\r\n    @MaxID = 2147483647\r\n\r\nSELECT TOP 1 @ROWID = ROWID,\r\n    @SQL = SQL,\r\n    @TableName = TableName,\r\n    @StatsName = StatsName\r\n  FROM #TMP_stats\r\n  WHERE ROWID > @ROWID\r\n  ORDER BY ROWID\r\n\r\nWHILE @@ROWCOUNT > 0 \r\n  BEGIN\r\n    PRINT @SQL\r\n\r\n    INSERT INTO #TMP\r\n        (\"Updated\",\r\n         \"Table Cardinality\",\r\n         \"Snapshot Ctr\",\r\n         \"Steps\",\r\n         \"Density\",\r\n         \"Rows Above\",\r\n         \"Rows Below\",\r\n         \"Squared Variance Error\",\r\n         \"Inserts Since Last Update\",\r\n         \"Deletes Since Last Update\",\r\n         \"Leading column Type\")\r\n        EXEC (@SQL)\r\n\r\n    SELECT @LastID = MIN(ROWID),\r\n        @MaxID = MAX(ROWID)\r\n      FROM #TMP\r\n      WHERE ROWID BETWEEN (SELECT MIN(a.ROWID)\r\n                            FROM #TMP a\r\n                            WHERE a.TableName IS NULL\r\n                          )\r\n                  AND     (SELECT MAX(b.ROWID)\r\n                            FROM #TMP b\r\n                            WHERE b.TableName IS NULL\r\n                          )\r\n\r\n    UPDATE #TMP\r\n      SET TableName = @TableName,\r\n          StatsName = @StatsName\r\n      WHERE ROWID BETWEEN @LastID AND @MaxID\r\n\r\n    SELECT TOP 1 @ROWID = ROWID,\r\n        @SQL = SQL,\r\n        @TableName = TableName,\r\n        @StatsName = StatsName\r\n      FROM #TMP_stats\r\n      WHERE ROWID > @ROWID\r\n      ORDER BY ROWID\r\n  END\r\nDBCC TRACEOFF(2388) WITH NO_INFOMSGS\r\n\r\nSELECT TableName,\r\n    StatsName,\r\n    (SELECT SUM(p.rows)\r\n      FROM sys.partitions p\r\n      WHERE OBJECT_ID(TableName) = p.object_id\r\n        AND index_id <= 1\r\n    ) AS number_of_rows\r\n  FROM #TMP\r\n  WHERE \"Leading column Type\" = 'Ascending'\r\n  ORDER BY number_of_rows DESC<\/pre>","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":50},"_thresholds_medium":{"selected":true,"value":10},"_thresholds_low":{"selected":true,"value":0},"thresholds_comment":"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. ","tsql_comment":"Note: This T-SQL query runs the DBCC TRACEON, command and you\u2019ll 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.","targetentitytype":0}