Script: Performance Counters

--Buffer cache hit ratio : - close to 100%

--PLE : 300 or more

--Compilations : -- for every 10 batch requests / sec there is 1 compilations

--REcompilations : - less than 10% of compilations

-- Locks : -- Total ( 0)

--page splits : Less thann 20% of batch req/secs  

 
  
DECLARE @old_cntr_value INT;

DECLARE @first_sample_date DATETIME;



　

SELECT object_name, counter_name, ((cntr_value*1)/1024)*1.00/1024 AS 'Total Server Memory (GB)'

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Total Server Memory (KB)'



　

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

FROM (SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio'

AND object_name LIKE '%:Buffer Manager%') a

CROSS JOIN

(SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio base'

and object_name LIKE '%:Buffer Manager%') b



　

　

　

SELECT @old_cntr_value = cntr_value,

@first_sample_date = getdate()

select *

FROM sys.dm_os_performance_counters

where counter_name = 'page splits/sec'





-- Time frame to wait before collecting second sample


WAITFOR DELAY '00:00:01'

select *

FROM sys.dm_os_performance_counters

where counter_name = 'page splits/sec'




-- Collect second sample and calculate per-second counter


SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as PageSplitsPerSec

FROM sys.dm_os_performance_counters

WHERE counter_name = 'page splits/sec'



　

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec'

AND object_name LIKE '%SQL Statistics%';

WAITFOR DELAY '00:00:01'



　

SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as [Batch Requests/sec]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec'

AND object_name LIKE '%SQL Statistics%';

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec'

AND object_name LIKE '%SQL Statistics%';

WAITFOR DELAY '00:00:01'



　

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Compilations/sec'

AND object_name LIKE '%SQL Statistics%';

WAITFOR DELAY '00:00:01'

SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as [compilations per sec]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Compilations/sec'

AND object_name LIKE '%SQL Statistics%';



　

　

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Re-Compilations/Sec'

AND object_name LIKE '%SQL Statistics%';

WAITFOR DELAY '00:00:01'

SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as [RE-compilations per sec]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Re-Compilations/Sec'

AND object_name LIKE '%SQL Statistics%';



　

　

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Lock Waits/sec'

AND object_name LIKE '%SQLServer:Locks%'

and instance_name = '_Total' ;

WAITFOR DELAY '00:00:01'



　

　

　

　

SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as [Lock Waits/sec]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Lock Waits/sec'

AND object_name LIKE '%SQLServer:Locks%'

and instance_name = '_Total' ;



　

　

　

SELECT numa_node = ple.instance_name, ple_sec = ple.cntr_value, db_node_mem_GB = dnm.cntr_value/1048576,

ple_per_4gb = ple.cntr_value * 4194304 / dnm.cntr_value

FROM sys.dm_os_performance_counters ple join sys.dm_os_performance_counters dnm

on ple.instance_name = dnm.instance_name

and ple.counter_name='Page life expectancy' -- PLE per NUMA node

and dnm.counter_name='Database Node Memory (KB)' -- buffer pool size per NUMA node



　

Script: To find the size of the tables in a database

SELECT
table_name = SCHEMA_NAME(o.[schema_id]) + '.' + o.name

, data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(8,4))

FROM sys.objects o

JOIN (




SELECT
p.[object_id]

, total_rows = SUM(p.[rows])

, total_pages = SUM(a.total_pages)

, usedpages = SUM(a.used_pages)

, pages = SUM(




CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0

WHEN a.[type]! = 1 AND p.index_id < 2 THEN a.used_pages

WHEN p.index_id < 2 THEN a.data_pages ELSE 0




END
)
FROM sys.partitions p

JOIN sys.allocation_units a ON p.[partition_id] = a.container_id

LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]

GROUP BY p.[object_id]

) do ON o.[object_id] = do.[object_id]

WHERE o.[type] = 'U'