This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 , |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 , |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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) , |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Listing 7.9: Which deprecated features are still in use? | |
DECLARE @object_name SYSNAME | |
SET @object_name = CASE WHEN @@servicename = 'MSSQLSERVER' THEN 'SQLServer' | |
ELSE 'MSSQL$' + @@serviceName | |
END + ':Deprecated Features' | |
DECLARE @PERF_COUNTER_LARGE_RAWCOUNT INT | |
SELECT @PERF_COUNTER_LARGE_RAWCOUNT = 65792 | |
SELECT object_name , | |
counter_name , |
OlderNewer