När man träffa kunder så
är det vanligt att man inte riktigt vet om sina databaser faktiskt används
eller inte och i vilken utsträckning. För att få en överblick av hur det ser ut
så brukar jag schemalägga ett jobb som samlar information från DMV´en sys.dm_db_index_usage_stats.
Denna dynamic viewe visa löpande information om hur index och tabeller (HEEP´s)
används. Jag anser att detta bör kunna ge en bra bild på hur din databas
används. Du kan även använda denna information för att se om index faktiskt kan
tas bort.
Börja med att skapa en tabell i valfri databas för att logga datat över tiden.
CREATE TABLE [dbo].[IndexUsage](
[CollectedTime]
[datetime] NULL,
[LastCollectedTime]
[datetime] NULL,
[DatabaseName]
[varchar](256) NULL,
[SchemaName]
[varchar](50) NULL,
[TableName]
[varchar](256) NULL,
[IndexName]
[varchar](256) NULL,
[user_seeks]
[bigint] NULL,
[init_user_seeks]
[bigint] NULL,
[user_scans]
[bigint] NULL,
[init_user_scans]
[bigint] NULL,
[user_updates]
[bigint] NULL,
[init_user_updates]
[bigint] NULL,
[row_count]
[bigint] NULL,
[init_row_count]
[bigint] NULL,
[size_in_mb]
[decimal](18, 2) NULL,
[init_size_in_mb]
[decimal](18, 2) NULL
) ON [PRIMARY]
Sedan använder vi
sp_MSForEachDB för att köra mot samtliga databaser. Det är två olika skript jag
slår ihop i en union. Det ena är för att hämta användande från sys.dm_db_index_usage_stats och det andra är
för att hämta samtliga index från sys.indexes, även dom som då aldrig används. Kör följande kod via ett SQL jobb.
CREATE TABLE #Temp1 (
CollectedTime DATETIME,
DatabaseName VARCHAR(256),
SchemaName VARCHAR(50),
TableName VARCHAR(256),
IndexName VARCHAR(256),
user_seeks BIGINT,
user_scans BIGINT,
user_updates BIGINT,
row_count BIGINT,
size_in_mb DECIMAL(18,2)
)
EXEC sp_MSForEachDB 'USE [?];
WITH CTE AS (
SELECT d.name as
DatabaseName,
OBJECT_SCHEMA_NAME(i.object_id)
AS SchemaName,
t.name AS TableName,
ISNULL(i.name,
''HEEP'') AS IndexName,
ISNULL(s.user_seeks,
0) as user_seeks,
ISNULL(s.user_scans,
0) as user_scans,
ISNULL(s.user_updates,
0) as user_updates,
ps.row_count,
CAST((ps.reserved_page_count
* 8)/1024. as decimal(12,2)) AS size_in_mb
FROM
sys.dm_db_index_usage_stats AS s
INNER JOIN sys.databases d
ON d.database_id =
s.database_id
INNER JOIN sys.indexes as i
ON s.object_id =
i.object_id
AND s.index_id =
i.index_id
INNER JOIN sys.tables t
ON s.object_id =
t.object_id
INNER JOIN sys.dm_db_partition_stats
ps
ON i.object_id =
ps.object_id
AND i.index_id =
ps.index_id
WHERE
objectproperty(s.object_id,''IsUserTable'') = 1 AND s.database_id = DB_ID()
UNION
SELECT DB_NAME() AS
DatbaseName,
OBJECT_SCHEMA_NAME(i.object_id)
AS SchemaName,
OBJECT_NAME(i.object_id)
AS TableName,
ISNULL(i.name,
''HEEP'') AS IndexName,
0 as user_seeks,
0 as user_scans,
0 as user_updates,
ps.row_count,
CAST((ps.reserved_page_count
* 8)/1024. as decimal(12,2)) as size_in_mb
FROM sys.indexes
i
INNER JOIN sys.objects o
ON i.object_id =
o.object_id
LEFT OUTER JOIN
sys.dm_db_index_usage_stats s
ON s.object_id =
i.object_id
AND i.index_id =
s.index_id
INNER JOIN sys.dm_db_partition_stats
ps
ON i.object_id =
ps.object_id AND i.index_id = ps.index_id
WHERE
OBJECTPROPERTY(i.object_id, ''IsUserTable'') = 1
AND s.object_id IS
NULL
)
INSERT INTO #Temp1
SELECT GETDATE(),
DatabaseName, SchemaName, TableName, IndexName,
SUM(user_seeks) as
user_seeks, SUM(user_scans) as user_scans, SUM(user_updates) as user_updates,
SUM(row_count) as row_count, SUM(size_in_mb) as size_in_mb
FROM CTE
GROUP BY
DatabaseName, SchemaName, TableName, IndexName
ORDER BY
DatabaseName, TableName'
GO
MERGE INTO dbo.IndexUsage AS trg
USING #Temp1 AS src
ON trg.DatabaseName = src.DatabaseName
AND trg.SchemaName = src.SchemaName
AND trg.TableName = src.TableName
AND trg.IndexName = src.IndexName
WHEN MATCHED THEN
UPDATE SET
trg.LastCollectedTime = src.CollectedTime,
trg.user_seeks = src.user_seeks,
trg.user_scans = src.user_scans,
trg.user_updates = src.user_updates,
trg.row_count = src.row_count,
trg.size_in_mb = src.size_in_mb
WHEN NOT MATCHED BY TARGET THEN
INSERT (CollectedTime, DatabaseName,
SchemaName, TableName,
IndexName, init_user_seeks, init_user_scans,
init_user_updates, init_row_count, init_size_in_mb)
VALUES (src.CollectedTime, src.DatabaseName, src.SchemaName, src.TableName, src.IndexName, src.user_seeks, src.user_scans, src.user_updates, src.row_count, src.size_in_mb);
DROP TABLE #Temp1
För att analysera datat
kan man tex använda dessafrågor för att se förändring sedan starten.
SELECT CollectedTime, LastCollectedTime,
DatabaseName, SchemaName, TableName, IndexName
,(user_seeks - init_user_seeks) AS user_seeks
,(user_scans - init_user_scans) AS user_scans
,(user_updates - init_user_updates) AS user_updates
,(row_count - init_row_count) AS row_count
,(size_in_mb - init_size_in_mb) AS size_in_mb
FROM [IndexUsage]
ORDER BY user_seeks DESC, user_scans DESC, user_updates DESC
WITH CTE AS (
SELECT DatabaseName, SchemaName,
TableName, IndexName
,(User_seeks + user_scans +
user_updates) as
Activity
FROM IndexUsage
WHERE DatabaseName NOT IN ('Master','Tempdb','Model','MSDB')
)
SELECT DatabaseName, SchemaName,
TableName, IndexName
,SUM(Activity) as Activity
FROM CTE
GROUP BY DatabaseName,
SchemaName, TableName,
IndexName
ORDER BY DatabaseName,
SchemaName, TableName,
IndexName
