MSSQL Server Kullanılmayan Tablo-View ve Veritabanlarını Tespit Etmek

Bu makalede Sql Server üzerinde Kullanılmayan Tablo-View-Veritabanlarını görmüş olacağız.

1-Aşağıdaki komut veritabanı altın da bulunan tabloların için yapılan değişikliği göstermektedir.

use [AdventureWorks2012]
go
declare @SystemStartDate datetime
set @SystemStartDate =(select create_date from sys.databases where name='TempDB')
;With Unused_Tables (Table_Name,  Created_Date, LastModifiedDate,
LastUserSeek, LastUserScan, LastUserLookup, LastUserUpdate) 
AS ( 
  SELECT [Objects].name AS TableName
     ,[Objects].create_date AS CreatedDate
     ,[Objects].modify_date AS LastModifiedDate 
     ,[IndexUsage].last_user_seek AS LastUserSeek
 ,[IndexUsage].last_user_scan AS LastUserScan
      ,[IndexUsage].last_user_lookup AS LastUserLookup
     ,[IndexUsage].last_user_update AS LastUserUpdate
  FROM sys.all_objects  [Objects] 
     LEFT JOIN sys.dm_db_index_usage_stats [IndexUsage] ON OBJECT_NAME([IndexUsage].object_id)=[Objects].name
  WHERE [Objects].type ='U' 
  
)
SELECT  '' + @SystemStartDate + '' SQLStartTime,* FROM Unused_Tables
order by LastUserSeek, LastUserScan, LastUserLookup, LastUserUpdate asc

Sorgu sonucunda aşağıdaki gibi bir sonuç dönmektedir. Sql Start time kısmında sorgunun çalıştırıldığı tarih, tablonun ne  zaman oluşturulduğu, düzenlenme tarihini, tablo üzerinde tarama veya arama yapıldığı tarihi ve en son tablonun ne zaman güncellendiğini görebiliriz.

Şimdi belirlenen Adress adındaki  tablonun indexs tanımlı olan bir kolunu üzerinde  sorgu çalıştırdığımızda LastUserSeek bölümüne tarih değerinin geldiğini görüyoruz.

Başka bir kod ise veritabanı altındaki tabloların oluşturulma ve en son düzenlenme tarihini bulabiliriz. Ayrıca tablo içerisinde kaç satır olduğunu da ilgili komut bize vermektedir.

use AdventureWorks2012
; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate ) 
AS ( 
  SELECT DBTable.name AS TableName
     ,PS.row_count AS TotalRowCount
     ,DBTable.create_date AS CreatedDate
     ,DBTable.modify_date AS LastModifiedDate
  FROM sys.all_objects  DBTable 
     JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
  WHERE DBTable.type ='U' 
     AND NOT EXISTS (SELECT OBJECT_ID  
                     FROM sys.dm_db_index_usage_stats
                     WHERE OBJECT_ID = DBTable.object_id )
)
-- Select data from the CTE
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate 
FROM UnUsedTables
ORDER BY LastModifiedDate desc

Aşağıdaki komut ile instance altında bulunan veritabanlarında kullanılmayan tabloları göstermektedir.

EXEC sp_MSforeachdb '
USE [?];

IF DB_ID() NOT IN (1, 2, 3, 4) -- Sistem veritabanlarını atla
BEGIN
    SELECT 
        DB_NAME() AS DatabaseName,
        s.name AS SchemaName,
        t.name AS TableName,
        t.create_date,
        t.modify_date,
        ius.last_user_seek,
        ius.last_user_scan,
        ius.last_user_lookup,
        ius.last_user_update
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    LEFT JOIN sys.dm_db_index_usage_stats ius 
        ON t.object_id = ius.object_id AND ius.database_id = DB_ID()
    WHERE 
        ius.object_id IS NULL OR (
            ius.last_user_seek IS NULL AND 
            ius.last_user_scan IS NULL AND 
            ius.last_user_lookup IS NULL AND 
            ius.last_user_update IS NULL
        )
    ORDER BY t.name
END
'

Tablo için modify_date ne anlama gelir. Bu tarih, tablonun şemasal bir değişiklik geçirdiği (örneğin kolon eklendi, silindi veya değiştirildiği) zamanı ifade eder. Veri değişikliklerini yansıtmaz. Yani tablonun içeriği değişse bile (INSERT, UPDATE, DELETE), bu tarih değişmez.
– Aşağıdaki işlemler bu tarihi değiştirir:
– Kolon eklemek veya silmek
– Kolon veri tipi değiştirmek
– Constraint (primary key, foreign key vs.) eklemek veya kaldırmak
– Index eklemek/silmek gibi yapısal değişiklikler

2-Aşağıdaki komut ile instance altında bulunan veritabanlarında kullanılmayan View‘leri göstermektedir.

EXEC sp_MSforeachdb '
USE [?];
IF DB_ID() NOT IN (1, 2, 3, 4) -- Skip system databases
BEGIN
    SELECT 
        DB_NAME() AS DatabaseName,
        s.name AS SchemaName,
        v.name AS ViewName,
        v.create_date,
        v.modify_date,
        ius.last_user_seek,
        ius.last_user_scan,
        ius.last_user_lookup,
        ius.last_user_update,
        CASE 
            WHEN ius.object_id IS NULL THEN ''Never used''
            ELSE ''No usage recorded''
        END AS UsageStatus
    FROM sys.views v
    INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
    LEFT JOIN sys.dm_db_index_usage_stats ius 
        ON v.object_id = ius.object_id AND ius.database_id = DB_ID()
    WHERE 
        ius.object_id IS NULL OR (
            ius.last_user_seek IS NULL AND 
            ius.last_user_scan IS NULL AND 
            ius.last_user_lookup IS NULL AND 
            ius.last_user_update IS NULL
        )
    ORDER BY s.name, v.name
END
'

3-Aşağıdaki komut sayesinde veritabanının en son ne zaman kullanıldığını görebiliriz.

SELECT 
    DB_NAME(database_id) AS VeriTabaniAdi, 
    MAX(last_user_update) AS Son_Guncelleme, 
    MAX(last_user_seek) AS Son_Arama, 
    MAX(last_user_scan) AS Son_Tarama, 
    MAX(last_user_lookup) AS Son_Lookup
FROM sys.dm_db_index_usage_stats
GROUP BY database_id
ORDER BY Son_Guncelleme DESC;

Kullanılmayan veritabanlarını bulan 2. komut:

SELECT
d.name AS DatabaseName,
MAX(ius.last_user_seek) AS LastSeek,
MAX(ius.last_user_scan) AS LastScan,
MAX(ius.last_user_lookup) AS LastLookup,
MAX(ius.last_user_update) AS LastUpdate
FROM
sys.databases d
LEFT JOIN
sys.dm_db_index_usage_stats ius ON d.database_id = ius.database_id
WHERE
d.database_id > 4 -- Sistem veritabanlarını hariç tutar
GROUP BY
d.name
ORDER BY
COALESCE(MAX(ius.last_user_seek), MAX(ius.last_user_scan), MAX(ius.last_user_lookup), MAX(ius.last_user_update)) ASC;

Bu makalede kullanılmayan tabloları ve veritabanlarını tespit etmiş olduk. Başka bir makalede görüşmek dileğiyle..

Onlar, ” Yolda Kalmışlara Yardım ederler. ” Bakara-177