SQL: More Table Statistics
Author: jason
Date: 2014-04-11 12:07:50
Category: Technical

I wanted to put together the number of times a table was accessed, along with the size of the table and number of rows. One of the ways to do this is by putting everything into a Temp Table. I haven't used MERGE before. So I though I would give it a try. Its probably overkill for this scenario, but I think I like it!

Here are the results of the whole script (listed below)





Here is where I'm using MERGE with TARGET and SOURCE





Code

USE SQLConfig;

DECLARE @Temp AS TABLE (DBName VARCHAR(255)
, TableName VARCHAR(255)
, NumReads INT
, NumWrites INT
, NumRows INT
, UsedSpaceMB INT)

MERGE @Temp AS Target
USING (--Table Reads (groups indexes)
SELECT
DB_NAME(DB_ID()) DBName
, TableName = object_name(s.object_id)
, NumReads = SUM(user_seeks + user_scans + user_lookups)
, NumWrites = SUM(user_updates)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE
objectproperty(s.object_id,'IsUserTable') = 1
GROUP BY object_name(s.object_id)
--ORDER BY NumReads DESC
) AS Source
ON (Target.DBName = Source.DBName
AND Target.TableName = Source.TableName)
WHEN MATCHED THEN
UPDATE SET Target.NumReads = Source.NumReads, Target.NumWrites = Source.NumWrites
WHEN NOT MATCHED BY TARGET THEN
INSERT (DBName, TableName, NumReads, NumWrites)
VALUES (Source.DBName, Source.TableName, Source.NumReads, Source.NumWrites);


MERGE @Temp AS Target
USING (--Table Sizes
SELECT
DB_NAME(DB_ID()) DBName
,t.NAME AS TableName
,NULL NumReads
,NULL NumWrites
,MAX(p.rows) AS NumRows
,(SUM(a.used_pages) * 8)/1024 AS UsedSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY t.Name, t.schema_id--, p.Rows
--ORDER BY SUM(a.used_pages) DESC
) AS Source
ON (Target.DBName = Source.DBName
AND Target.TableName = Source.TableName)
WHEN MATCHED THEN
UPDATE SET Target.NumRows = Source.NumRows, Target.UsedSpaceMB = Source.UsedSpaceMB
WHEN NOT MATCHED BY TARGET THEN
INSERT (DBName, TableName, NumReads, NumWrites)
VALUES (Source.DBName, Source.TableName, Source.NumReads, Source.NumWrites);

WITH [MOST] AS (
SELECT
DBName, TableName, NumReads, NumWrites, NumRows, UsedSpaceMB
, ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY NumReads DESC) AS Rank_HighestReads
, ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY NumWrites DESC) AS Rank_HighestWrites
, ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY UsedSpaceMB DESC) AS Rank_MostSpace
FROM @Temp
) SELECT *
FROM [MOST]
WHERE
Rank_HighestReads <= 10
OR Rank_HighestWrites <= 10
OR Rank_MostSpace <= 10
ORDER BY Rank_HighestReads ASC





jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009