Average Size of Data In A Column

A script to return the average size of the values in a column. It also provides a simple histogram of the data distribution.

SELECT AVG(DATALENGTH(MyColumn)) AS 'Avg Size'
  FROM MyDB.dbo.MyTable

SELECT AVG(DATALENGTH(MyColumn))           AS 'Avg Size',
       FLOOR(LOG10(DATALENGTH(MyColumn)))  AS 'Log',
       COUNT(*)                            AS 'Count in Log' 
  FROM MyDB.dbo.MyTable
 GROUP BY FLOOR(LOG10(DATALENGTH(MyColumn)))
 ORDER BY FLOOR(LOG10(DATALENGTH(MyColumn))) DESC

A Good Habit

Most of us have a standard set of commands we place at the start of any script we write. Common ones are SET NOCOUNT ON and SET ANSI_NULLS ON. Of course, there are countless settings you could employ "just in case", but knowing your database's default setup can usually guide you.

I've just learned of another setting, though, that could be used to program defensively in certain environments:

SET ROWCOUNT 0

This statement resets the number of returned rows back to "all of them", and protects your script from being called from somewhere that had ROWCOUNT set to a value, say, 100 rows, but never got reset. This is overkill in most cases, but in other cases, remember that just because you're paranoid doesn't mean they're not out to get you.

Show Indexes' Fragmentation

This script will display fragmentation info on every index in a specified table.

-- ShowIndexFragmentation.sql
SET NOCOUNT ON

-- Set variables.
DECLARE @sDatabase  sysname
SET     @sDatabase  = 'YourDB'           --  Change this to your database!

DECLARE @sTableName sysname
SET     @sTableName = 'YourTable'        -- Change this to your table!

DECLARE @sCmd       NVARCHAR(4000)
SET     @sCmd       = ''

-- Create temp table.
IF OBJECT_ID('tempdb..#ShowContigRaw') IS NOT NULL  DROP TABLE #ShowContigRaw

CREATE TABLE #ShowContigRaw
(
   TableName       CHAR(255),
   ObjectId        INT,
   IndexName       CHAR(255),
   IndexId         INT,
   Lvl             INT,
   CountPages      INT,
   CountRows       INT,
   MinRecSize      INT,
   MaxRecSize      INT,
   AvgRecSize      INT,
   ForRecCount     INT,
   Extents         INT,
   ExtentSwitches  INT,
   AvgFreeBytes    INT,
   AvgPageDensity  DECIMAL(38,14),
   ScanDensity     INT,
   BestCount       INT,
   ActualCount     INT,
   LogicalFrag     DECIMAL(38,14),
   ExtentFrag      DECIMAL(38,14)
)

-- Call DBCC SHOWCONTIG on all indexes on the table to populate the temp table.
SET @sCmd = 'USE ' + @sDatabase +
            ' DBCC SHOWCONTIG (''' + @sTableName +
            ''') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'

RAISERROR(@sCmd, 10, 1) WITH NOWAIT
INSERT INTO #ShowContigRaw  EXEC(@sCmd)

-- Create temp table.
IF OBJECT_ID('tempdb..#ShowContigCooked') IS NOT NULL  DROP TABLE #ShowContigCooked

CREATE TABLE #ShowContigCooked
(
   TableName       NVARCHAR(255),      -- Table Name
   ObjectId        NVARCHAR(255),      -- Object ID
   IndexName       NVARCHAR(255),      -- Index Name
   IndexId         NVARCHAR(255),      -- Index ID
   Lvl             NVARCHAR(255),      -- Level
   CountPages      NVARCHAR(255),      -- Page Count
   CountRows       NVARCHAR(255),      -- Row Count
   MinRecSize      NVARCHAR(255),      -- Min Row Size
   MaxRecSize      NVARCHAR(255),      -- Max Row Size
   AvgRecSize      NVARCHAR(255),      -- Avg Row Size
   ForRecCount     NVARCHAR(255),      -- Forwarded Record Count
   Extents         NVARCHAR(255),      -- Extent Count
   ExtentSwitches  NVARCHAR(255),      -- Extent Switches
   AvgFreeBytes    NVARCHAR(255),      -- AvgFreeBytes
   AvgPageDensity  NVARCHAR(255),      -- Avg Page Density
   ScanDensity     NVARCHAR(255),      -- Extent Contiguousness aka Scan Density
   BestCount       NVARCHAR(255),      -- Extent Changes Best Count
   ActualCount     NVARCHAR(255),      -- Extent Changes Actual Count
   LogicalFrag     NVARCHAR(255),      -- Logical Fragmentation
   ExtentFrag      NVARCHAR(255)       -- Extent Fragmentation
)

INSERT INTO #ShowContigCooked
   SELECT * FROM #ShowContigRaw

-- Massage values.
UPDATE #ShowContigCooked
   SET TableName       = RTRIM(TableName)

UPDATE #ShowContigCooked
   SET IndexName       = 'N/A'
 WHERE IndexName       = ''

UPDATE #ShowContigCooked
   SET IndexName       = RTRIM(IndexName)
 
UPDATE #ShowContigCooked
   SET CountPages      = REPLACE(CONVERT(NVARCHAR,
                            CONVERT(MONEY, CountPages),     1),  '.00', '')

UPDATE #ShowContigCooked
   SET CountRows       = REPLACE(CONVERT(NVARCHAR,
                            CONVERT(MONEY, CountRows),      1),  '.00', '')

UPDATE #ShowContigCooked
   SET MinRecSize      = REPLACE(CONVERT(NVARCHAR, 
                            CONVERT(MONEY, MinRecSize),     1),  '.00', '')

UPDATE #ShowContigCooked
   SET MaxRecSize      = REPLACE(CONVERT(NVARCHAR,
                            CONVERT(MONEY, MaxRecSize),     1),  '.00', '')

UPDATE #ShowContigCooked
   SET AvgRecSize      = REPLACE(CONVERT(NVARCHAR, 
                            CONVERT(MONEY, AvgRecSize),     1),  '.00', '')

UPDATE #ShowContigCooked
   SET ForRecCount     = REPLACE(CONVERT(NVARCHAR,
                            CONVERT(MONEY, ForRecCount),    1),  '.00', '')

UPDATE #ShowContigCooked
   SET Extents         = REPLACE(CONVERT(NVARCHAR, 
                            CONVERT(MONEY, Extents),        1),  '.00', '')

UPDATE #ShowContigCooked
   SET ExtentSwitches  = REPLACE(CONVERT(NVARCHAR, 
                            CONVERT(MONEY, ExtentSwitches), 1),  '.00', '')

UPDATE #ShowContigCooked
   SET AvgPageDensity  =         CONVERT(NVARCHAR, 
                            CONVERT(MONEY, AvgPageDensity), 1) + '%'

UPDATE #ShowContigCooked
   SET BestCount       = REPLACE(CONVERT(NVARCHAR,
                            CONVERT(MONEY, BestCount),      1),  '.00', '')

UPDATE #ShowContigCooked
   SET ActualCount     = REPLACE(CONVERT(NVARCHAR,
                            CONVERT(MONEY, ActualCount),    1),  '.00', '')

UPDATE #ShowContigCooked
   SET ScanDensity     =         CONVERT(NVARCHAR, 
                            CONVERT(MONEY, ScanDensity),    1) + '%'

UPDATE #ShowContigCooked
   SET LogicalFrag     =         CONVERT(NVARCHAR, 
                            CONVERT(MONEY, LogicalFrag),    1) + '%'

UPDATE #ShowContigCooked
   SET ExtentFrag      =         CONVERT(NVARCHAR, 
                            CONVERT(MONEY, ExtentFrag),     1) + '%'

-- Done.  Display results.
SELECT
   'Name of the table or view.'
       AS 'Table Name',
   'OBJECT_ID of the table or view.'
       AS 'Object ID',
   'Name of the index.'
       AS 'Index Name',
   'ID of the index.'
       AS 'Index ID',
   'Level of the index. Level 0 is the leaf (or data) level of the index.
       The level number increases moving up the tree toward the index root.
       Level is 0 for a heap.'
       AS 'Level',
   'Number of pages comprising that level of the index or entire heap.'
       AS 'Page Count',
   'Number of data or index records at that level of the index.
       For a heap, this is the number of data records in the entire heap.'
       AS 'Row Count',
   'Minimum record size in that level of the index or entire heap.'
       AS 'Min Row Size',
   'Maximum record size in that level of the index or entire heap.'
       AS 'Max Row Size',
   'Average record size in that level of the index or entire heap.'
       AS 'Avg Row Size',
   'On UPDATES, if there is insufficient space on the page to fit the new record,
       the record is moved to a new page, and a forwarding record is left in the
       original location to point to the new location.'
       AS 'Forwarded Record Count',
   'Number of extents in that level of the index or entire heap.'
       AS 'Extent Count',
   'Number of jumps from one extent to another while traversing all the pages.'
       AS 'Extent Switches',
-- 'Average number of free bytes on the pages scanned. The higher the number,
-- the less full the pages are. Lower numbers are better. This number is also
-- affected by row size; a large row size can result in a higher number.'
--     AS 'AvgFreeBytes',
   'Measure of how full the pages are. The higher the percentage, the better.'
       AS 'Avg Page Density',
   '"BestCount" / "Actual count"   Is 100% if all extents are contiguous; else,
       some fragmentation exists.'
       AS 'Extent Changes Best Count',
   'The ideal number of extent changes if the extents are perfectly contiguous.'
       AS 'Extent Changes Actual Count',
   'Actual number of extent changes while traversing.'
       AS 'Extent Contiguousness aka Scan Density',
   'Percentage of out-of-order pages returned from scanning the leaf pages of
       an index. This number is not relevant to heaps and text indexes.'
       AS 'Logical Fragmentation',
   'Percentage of out-of-order extents in scanning the leaf pages of an index.
       This number is not relevant to heaps.'
       AS 'Extent Fragmentation'

UNION

SELECT
   TableName       AS 'Table Name',                   -- YourTable
   ObjectId        AS 'Object ID',                    -- 613577224
   IndexName       AS 'Index Name',                   -- tank_data_index
   IndexId         AS 'Index ID',                     -- 2
   Lvl             AS 'Level',                        -- 0
   CountPages      AS 'Page Count',                   -- 48,139
   CountRows       AS 'Row Count',                    -- 7,271,509
   MinRecSize      AS 'Min Row Size',                 -- 51
   MaxRecSize      AS 'Max Row Size',                 -- 61
   AvgRecSize      AS 'Avg Row Size',                 -- 51
   ForRecCount     AS 'Forwarded Record Count',       -- 0
   Extents         AS 'Extent Count',                 -- 9,233
   ExtentSwitches  AS 'Extent Switches',              -- 26,012
-- AvgFreeBytes    AS 'AvgFreeBytes',
   AvgPageDensity  AS 'Avg Page Density',             -- 98%
   BestCount       AS 'Extent Changes Best Count',    -- 6,018
   ActualCount     AS 'Extent Changes Actual Count',  -- 26,013
   ScanDensity     AS 'Scan Density',  -- 23%
   LogicalFrag     AS 'Logical Fragmentation',        -- 19%
   ExtentFrag      AS 'Extent Fragmentation'          -- 70%
  FROM #ShowContigCooked
 WHERE TableName NOT LIKE 'dt%' AND
       TableName NOT LIKE 'sys%'
 ORDER BY 'Extent Fragmentation' DESC

DROP TABLE #ShowContigCooked

Convert INT to Comma-Separated NVARCHAR

A quick expression to change an INT like 1234567 to a comma-separated NVARCHAR like '1,234,567'.

REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, CountPages), 1), '.00', '')

Rebuild All Indexes on All Tables

Here's a small script to rebuild all the indexes on all the tables in a given database:

EXEC sp_MsForEachTable 'USE [YourDatabase]  PRINT ''?''  DBCC DBREINDEX (''?'', '' '', 70)'

Be sure to read Books OnLine about "DBCC DBREINDEX" - it's probably not something you want to do during peak hours.

Get Row Counts for all Tables

Here's a quick way to get the row counts for every table in a given database:

DBCC UPDATEUSAGE(YourDatabase)

SELECT so.name AS 'Table Name',
       si.rows AS 'Row Count'
  FROM sysobjects so
  JOIN sysindexes si
    ON so.id   = si.id
 WHERE so.type = 'U'
   AND si.indid IN (0, 1)
 ORDER BY si.rows DESC

This is quick because it looks in the system tables instead of at the tables themselves. (If you include the DBCC UPDATEUSAGE statement, it will take a while, but is sure to be accurate.)

Jeff's SQL Server Blog

It just doesn't get any better than this:

Jeff's SQL Server Blog: "The Joy of Blog Feedback"