SQL Performance

 

checkpoint  [seconds]

DBCC DROPCLEANBUFFERS

 

#TABLES WITHOUT PRIMARY KEY

USE incrLifeproNew;
SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0
ORDER BY [Schema], [Table];

 

#DATA/LOG FILE 

with fs 
as (
select database_id, type, round(size * 8.0 / 1024,2) size from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
where name not in ('db1','db2')
order by LogFileSizeMB desc;


select DB_NAME(database_id)dbname,name,round(size * 8.0 /1024,2) size,type_desc,physical_name
from sys.master_files where type=1
order by size desc;

 

#EXPANSIVE QUERIES

SELECT TOP 50
t.text ,
execution_count ,
statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_logical_writes / execution_count AS avg_logical_writes ,
total_physical_reads / execution_count AS avg_physical_reads
FROM    sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
--WHERE    DB_NAME(t.dbid) = 'SQLMonTest'
ORDER BY avg_physical_reads DESC;

 

#Table in buffer

SELECT
    SCHEMA_NAME(objects.schema_id) AS SchemaName,
    objects.name AS ObjectName,
    objects.type_desc AS ObjectType,
    COUNT(*) AS [Total Pages In Buffer],
    COUNT(*) * 8 / 1024 AS [Buffer Size in MB],
    SUM(CASE dm_os_buffer_descriptors.is_modified 
                WHEN 1 THEN 1 ELSE 0
        END) AS [Dirty Pages],
    SUM(CASE dm_os_buffer_descriptors.is_modified 
                WHEN 1 THEN 0 ELSE 1
        END) AS [Clean Pages],
    SUM(CASE dm_os_buffer_descriptors.is_modified 
                WHEN 1 THEN 1 ELSE 0
        END) * 8 / 1024 AS [Dirty Page (MB)],
    SUM(CASE dm_os_buffer_descriptors.is_modified 
                WHEN 1 THEN 0 ELSE 1
        END) * 8 / 1024 AS [Clean Page (MB)]
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions ON
    ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))
    OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))
INNER JOIN sys.objects ON partitions.object_id = objects.object_id
WHERE allocation_units.type IN (1,2,3)
    AND objects.is_ms_shipped = 0 
    AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY objects.schema_id, objects.name, objects.type_desc
ORDER BY [Total Pages In Buffer] DESC;

 

#Indexes Ananize 

select schema_name(t.schema_id) + '.' + t.[name] as table_view, 
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type],
    i.index_id,
    case when i.is_primary_key = 1 then 'Primary key'
        when i.is_unique = 1 then 'Unique'
        else 'Not unique' end as [type],
    i.[name] as index_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    case when i.[type] = 1 then 'Clustered index'
        when i.[type] = 2 then 'Nonclustered unique index'
        when i.[type] = 3 then 'XML index'
        when i.[type] = 4 then 'Spatial index'
        when i.[type] = 5 then 'Clustered columnstore index'
        when i.[type] = 6 then 'Nonclustered columnstore index'
        when i.[type] = 7 then 'Nonclustered hash index'
        end as index_type
from sys.objects t
    inner join sys.indexes i
        on t.object_id = i.object_id
    cross apply (select col.[name] + ', '
                    from sys.index_columns ic
                        inner join sys.columns col
                            on ic.object_id = col.object_id
                            and ic.column_id = col.column_id
                    where ic.object_id = t.object_id
                        and ic.index_id = i.index_id
                            order by col.column_id
                            for xml path ('') ) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0 and t.name='ExpandoValue'
order by schema_name(t.schema_id) + '.' + t.[name], i.index_id

 

#DB growth rate

SELECT DISTINCT
    A.[database_name]
,   AVG( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Avg Size Diff From Previous (MB)]
,   MAX( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Max Size Diff From Previous (MB)]
,   MIN( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Min Size Diff From Previous (MB)]
,   A.[Sample Size]
FROM 
(
    SELECT
        s.[database_name]
    --, s.[backup_start_date]
    ,   COUNT(*) OVER ( PARTITION BY s.[database_name] ) AS [Sample Size]
    ,   CAST ( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [Backup Size (MB)]
    ,   CAST ( ( LAG(s.[backup_size] ) 
            OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ) AS [Previous Backup Size (MB)]
    FROM 
        [msdb]..[backupset] s
    WHERE
        s.[type] = 'D' --full backup
    --ORDER BY
    --  s.[database_name]
    --, s.[backup_start_date]
) AS A
ORDER BY
    [Avg Size Diff From Previous (MB)] ASC;

 

 #Index use frequncy

select   
    DB_NAME(d.database_id) db,  
    OBJECT_NAME(d.object_id)tab,  
      
    i.name,  
    user_seeks,  
    user_scans,  
    user_lookups,        
    user_updates  --通过用户查询执行的更新次数     
from sys.dm_db_index_usage_stats d  
inner join sys.indexes i  
        on d.object_id = i.object_id  
           and d.index_id = i.index_id  
where database_id = DB_ID('CLIO_MSCRM') 
order by user_seeks+user_scans+user_lookups+user_updates desc 

 

#sp_msforeachdb

declare @command varchar(5000)='use ?;if db_name() not in ("master","model","msdb","tempdb","ReportServer","ReportServerTempDB")
SELECT 
  DB_NAME() db_name,
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, "TableHasPrimaryKey") = 0
ORDER BY [Schema], [Table];'

exec sp_msforeachdb @command

 

 

https://blog.sqlauthority.com/2019/06/14/sql-server-clean-pages-and-dirty-pages-count-memory-buffer-pools/

https://dataedo.com/kb/query/sql-server/list-table-indexes