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;

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