DB Inspection Overview

query.sql

################
# 1. Catalog of DB's
################
EXEC sp_helpdb; 

################
# 2. Fetch file structure metadata
################
SELECT  
  fg.name AS [File_Group_Name], 
  df.type, 
  df.name AS [FileAlias], 
  size/128.0 AS [FileSizeInMB], 
  [DataSizeInMB], 
  size/128.0 - [DataSizeInMB] AS EmptySpaceInMB, 
  df.physical_name AS [FilePath] 
FROM sys.database_files df 
  LEFT JOIN sys.filegroups fg ON df.data_space_id=fg.data_space_id 
CROSS APPLY (SELECT CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS [DataSizeInMB]) D;

################
# 3. Default transaction type
################
SELECT CASE transaction_isolation_level  
WHEN 0 THEN 'Unspecified'  
WHEN 1 THEN 'ReadUncommitted'  
WHEN 2 THEN IIF(is_read_committed_snapshot_on = 1, 'ReadCommitted Snapshot', 'ReadCommitted') 
WHEN 3 THEN 'Repeatable'  
WHEN 4 THEN 'Serializable'  
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL  
FROM sys.dm_exec_sessions  
CROSS JOIN sys.databases AS d 
where session_id = @@SPID AND  d.database_id = DB_ID();

################
# 4. Size and Usage stats for each table
################
WITH Sizings AS ( 
    SELECT object_id,  
    index_id,  
    SUM(sPTN.Rows) AS Rows,  
    SUM(total_pages) AS total_pages, 
    SUM(used_pages) AS used_pages, 
    SUM(data_pages) AS data_pages, 
    COUNT(1) AS Parts, 
    MAX(TMask) AS TMask 
    FROM sys.partitions AS sPTN 
    CROSS APPLY ( 
        SELECT  
            SUM(total_pages) AS total_pages,  
            SUM(used_pages) AS used_pages,  
            SUM(data_pages) AS data_pages, 
            SUM(DISTINCT POWER(2,type-1)) AS TMask 
        FROM sys.allocation_units a 
        WHERE a.container_id = sPTN.partition_id 
        GROUP BY a.container_id 
        ) AU 
    GROUP BY object_id,index_id 
) 
,Stats AS ( 
SELECT 
    [Schema],  
    sOBJ.name AS [Table], 
    Rows, 
    I.index_id AS [IndexID], 
    Parts, 
    TMask, 
    I.name AS [Index] 
    ,TotalMB 
    ,UsedMB 
    ,DataMB 
    ,IndexMB 
    ,LastRead 
    ,ius.last_user_update AS LastUpdate 
    ,ius.user_lookups 
    ,ius.user_scans 
    ,ius.user_seeks 
    ,ius.user_updates 
FROM  
    sys.objects AS sOBJ 
    INNER JOIN sys.indexes AS I ON I.OBJECT_ID = sOBJ.object_id 
    INNER JOIN Sizings S ON sOBJ.object_id = S.object_id AND S.index_id = I.index_id 
    CROSS APPLY (SELECT  
        SCHEMA_NAME(sOBJ.schema_id) AS [Schema], 
        total_pages * 8 / 1024.0 AS TotalMB,  
        used_pages * 8 / 1024.0 AS UsedMB, 
        --index_id <2 -- 0:Heap, 1:Clustered 
        CASE WHEN I.index_id < 2 THEN S.used_pages * 8 / 1024.0 ELSE 0 END AS DataMB, 
        CASE WHEN I.index_id >= 2 THEN S.used_pages * 8 / 1024.0 ELSE 0 END AS IndexMB 
        ) DAU 
    LEFT JOIN sys.dm_db_index_usage_stats ius ON ius.index_id = I.index_id AND ius.object_id = I.object_id 
    CROSS APPLY (    
       SELECT MAX(v) LastRead 
       FROM (VALUES 
    (ius.last_user_lookup), 
              (ius.last_user_scan), 
              (ius.last_user_seek)) AS accessDates(v) 
       ) AS DIU 
WHERE sOBJ.type = 'U' 
--ORDER BY [Table],IndexID 
) 
SELECT  
    [Schema], 
    [Table], 
    MAX(Rows) AS Rows, 
    SUM(TotalMB) AS [ReservedMB], 
    SUM(UsedMB) AS [UsedMB], 
    SUM(DataMB) AS [DataMB], 
    SUM(IndexMB) AS [IndexMB], 
    SUM(user_seeks + user_scans + user_lookups) AS ReadCount, 
    SUM(user_updates) AS WriteCount, 
    MAX(LastRead) AS LastRead, 
    MAX(LastUpdate) AS LastUpdate 
FROM Stats S 
GROUP BY [Schema],[Table] 
ORDER BY [Table]

###################
# 5. Connection Logging "extended event session"
###################
CREATE EVENT SESSION [Connections] ON SERVER 
ADD EVENT sqlserver.connection_accept(
    ACTION(sqlos.task_address,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'SQLServerCEIP')),
ADD EVENT sqlserver.connectivity_ring_buffer_recorded(
    ACTION(sqlos.task_address,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'SQLServerCEIP')),
ADD EVENT sqlserver.existing_connection(SET collect_database_name=(1)
    ACTION(sqlos.task_address,sqlserver.server_instance_name,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'SQLServerCEIP')),
ADD EVENT sqlserver.login(SET collect_database_name=(1),collect_options_text=(1)
    ACTION(sqlos.task_address,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'SQLServerCEIP')),
ADD EVENT sqlserver.logout(
    ACTION(sqlos.task_address,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.server_instance_name,sqlserver.session_id,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'SQLServerCEIP'))
ADD TARGET package0.event_file(SET filename=N'c:\xevents\connections')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO