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.