Find age based on date - TSQL

--this query finds the current ( as of the date the query is run) age of the person.

declare @dob datetime

set @dob = '07/12/1966'

select CASE

WHEN dateadd(year, datediff (year, @dob, GETDATE()),@dob) > GETDATE()

THEN datediff (year, @dob, GETDATE()) - 1

ELSE datediff (year, @dob, GETDATE())

END as Age

--select dateadd(year, datediff (year, @dob, GETDATE()),@dob)

--select datediff (year, @dob, GETDATE())

To check the last successful run date and time of the sql job

Declare @jobId as uniqueidentifier

select

j.job_id,j.Name as "Job Name", j.description as "Job Description", CONVERT(DATETIME, RTRIM(h.run_date))

+ ((h.run_time / 10000 * 3600)

+ ((h.run_time % 10000) / 100 * 60)

+ (h.run_time % 10000) % 100) / (86399.9964) AS run_datetime,

case h.run_status

when 0 then 'Failed'

when 1 then 'Successful'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as JobStatus

from sysJobHistory h, sysJobs j

where j.job_id = h.job_id and h.run_date =

(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id ) and h.run_status= 1

and cast(cast(h.run_date as varchar(8)) as date) = cast('06/27/2016' as date)

order by 2

Check if a table name exists in stored procedures

 use [yorDB]
 GO


SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type

FROM sys.sql_modules m

INNER JOIN sys.objects o

ON o.object_id = m.object_id

WHERE m.definition like '%tablename%'









GO

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