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'

script: Change the owner of the sql jobs

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs 

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END 

CLOSE My_Cursor

DEALLOCATE My_Cursor

Work adventure : MoveSQL Agent Jobs to another instanse

It is very easy to move (recreate) the sql agent job that you want to export to another instance of SQL server

1. Highlight the job you want o export
2. Right click
3. Choose Scrip Job as CREATE TO

The script is created and you can run that in the new instance that will create the job.

To move multiple jobs
You can also script multiple jobs to a single file. In SSMS

Click on Jobs,
hit F7 to get the 'Object Explorer Details' tab.
Highlight the jobs you want,
Script job as...

CPU consumption

--from SQLAuthority website
--SQL SERVER – Who is consuming CPU on my SQL Server?

WITH DB_CPU_Statistics




AS


(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY (SELECT CONVERT(INT, value) AS [DatabaseID]

FROM sys.dm_exec_plan_attributes(qs.plan_handle)

WHERE attribute = N'dbid') AS pa

GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking],

[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],

CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]

FROM DB_CPU_Statistics

WHERE DatabaseID <> 32767 -- ResourceDB

ORDER BY [CPU Ranking] OPTION (RECOMPILE);