justinpitts

-- Listing 7.12: Returning the values of "per second average" PerfMon counters.
DECLARE @PERF_COUNTER_BULK_COUNT INT  
SELECT  @PERF_COUNTER_BULK_COUNT = 272696576 

--Holds initial state
DECLARE @baseline TABLE
    (
      object_name NVARCHAR(256) ,
      counter_name NVARCHAR(256) ,
      instance_name NVARCHAR(256) ,

justinpitts

DECLARE @PERF_AVERAGE_BULK INT ,
    @PERF_LARGE_RAW_BASE INT 

SELECT  @PERF_AVERAGE_BULK = 1073874176 ,
        @PERF_LARGE_RAW_BASE = 1073939712 

SELECT  dopc_avgBulk.object_name ,
        dopc_avgBulk.instance_name ,
        dopc_avgBulk.counter_name ,
        CAST(dopc_avgBulk.cntr_value AS FLOAT) 

justinpitts

DECLARE @ts_now BIGINT = ( SELECT   cpu_ticks / ( cpu_ticks / ms_ticks ) FROM     sys.dm_os_sys_info ) ; 

SELECT TOP ( 30 )
        SQLProcessUtilization AS [SQL Server Process CPU Utilization] ,
        SystemIdle AS [System Idle Process] ,
        100 - SystemIdle - SQLProcessUtilization
                             AS [Other Process CPU Utilization] ,
        DATEADD(ms, -1 * ( @ts_now - [timestamp] ), GETDATE())
                             AS [Event Time]
FROM    ( SELECT    record.value('(./Record/@id)[1]', 'int') AS record_id ,

justinpitts

SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time

justinpitts

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  

justinpitts

-- Listing 2.20: A better sp_who2.
SELECT  des.session_id ,
        des.status ,
        des.login_name ,
        des.[HOST_NAME] ,
        der.blocking_session_id ,
        DB_NAME(der.database_id) AS database_name ,
        der.command ,
        des.cpu_time ,
        des.reads ,

justinpitts

SELECT transaction_id, DB_NAME(database_id) DatabaseName,
database_transaction_begin_time TransactionBegin,
CASE database_transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read only'
WHEN 3 THEN 'System' END AS TransactionType,
CASE database_transaction_state
WHEN 1 THEN 'Not Initialized'
WHEN 3 THEN 'Transaction No Log'
WHEN 4 THEN 'Transaction with Log'

justinpitts

WITH 
TABLES AS ( select  TABLE_SCHEMA, TABLE_NAME , case table_type when 'view' then 'view' else 'table'  end as type from INFORMATION_SCHEMA.TABLES)
, procs as ( select ROUTINE_SCHEMA, routine_name, ROUTINE_TYPE  from INFORMATION_SCHEMA.ROUTINES )
, domains as (select DOMAIN_SCHEMA, DOMAIN_NAME, 'TYPE' as type, '[' + DOMAIN_SCHEMA + '].[' + DOMAIN_NAME + ']' as q_name from INFORMATION_SCHEMA.DOMAINS)
, c as ( select '[' + table_schema + '].[' + table_name + ']' as q_name, type from ( select * from tables union select * from procs ) a )
select 'if object_id(''' + q_name + ''') is not null drop ' + type + ' ' + q_name +';' from c union select 'drop ' + type + ' ' + q_name +';' from domains

justinpitts

#set terminal png size 1200,800
set xdata time
set timefmt "%H:%M:%S"
set format x "%H:%M"
set output "load.png"
# time range must be in same format as data file
#set xrange ["08:20:00":"08:50:00"]
#set yrange [0:50]
set grid
set xlabel "Time"