USE [msdb]
GO
/****** Object: Job [Collect Lock information] Script Date: 08/16/2012 11:30:41 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/16/2012 11:30:41 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Collect Lock information',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA_ONLY', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [run tsql] Script Date: 08/16/2012 11:30:42 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run tsql',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE [SysAdmin]
GO
/****** Object: Table [dbo].[locks] Script Date: 10/22/2010 11:23:14 ******/
IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[locks]'') AND type in (N''U''))
CREATE TABLE [dbo].[locks](
[databasename] [nvarchar](128) NULL,
[resource_type] [nvarchar](60) NOT NULL,
[requested_object_name] [nvarchar](128) NULL,
[lock_type] [nvarchar](60) NOT NULL,
[request_status] [nvarchar](60) NOT NULL,
[request_session_id] [int] NOT NULL,
[lock_owner_address] [varbinary](8) NOT NULL,
[request_owner_id] [bigint] NULL,
[TimeofOccurence] [datetime] NOT NULL
) ON [PRIMARY]
use master
go
DECLARE @COUNTER int
, @RUN_TIME_MIN int
, @STOPAT int
SET @COUNTER=0;
truncate table SysAdmin..locks
WHILE 1=1
BEGIN
with all_locks (databasename,resource_type, requested_object_name,lock_type,request_status,request_session_id,
lock_owner_address,request_owner_id,TimeofOccurence)
as
(
select Db_name(resource_database_id) databasename,
resource_type,
( case
when resource_type = ''OBJECT'' then Object_name(resource_associated_entity_id, resource_database_id)
when resource_type in ( ''DATABASE'', ''FILE'', ''METADATA'' ) then ''N/A''
when resource_type in ( ''KEY'', ''PAGE'', ''RID'' ) then (select Object_name(object_id)
from sys.partitions
where hobt_id = resource_associated_entity_id)
else ''Undefined''
end ) as requested_object_name,
request_mode as lock_type,
request_status,
request_session_id,
lock_owner_address,
request_owner_id as transaction_id,
getdate() as TimeofOccurence
from sys.dm_tran_locks
where resource_type not in ( ''DATABASE'', ''METADATA'', ''APPLICATION'' )
)
insert into sysadmin..locks
select * from all_locks
where lock_type =''X''
SET @COUNTER=@COUNTER+1;
WAITFOR DELAY ''00:00:01'';
IF @COUNTER = -1 BREAK;
END
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Thursday, August 16, 2012
Wednesday, August 8, 2012
Sp_spaceused
declare @Space TABLE (
tablename sysname,
[rows] bigint,
reserved_S varchar(32),
data_S varchar(32),
index_size_S varchar(32),
unused_S varchar(32)
)
INSERT @Space(tablename,[rows],reserved_s,data_s,index_size_s,unused_s)
EXEC sp_msforeachtable 'exec sp_spaceused [?]'
select
DB_NAME() as DatabaseName,
tablename ,
[rows] ,
type_desc,
cast(replace(reserved_S, 'KB','') as bigint)/1024 as reserved_MB,
cast(replace(data_S, 'KB', '') as bigint)/1024 as datasize_MB ,
cast(replace(index_size_S, 'KB', '') as bigint)/1024 as index_size_MB ,
cast(replace(unused_S, 'KB', '') as bigint)/1024 as unused_MB
from @Space spa
inner join sys.indexes idx on idx.[object_id]=object_id(spa.tablename) and index_id in (0,1)
where (cast(replace(reserved_S, 'KB', '') as bigint)/1024) > 50000
order by unused_MB desc
Blocking Chains
-- http://thesqlguy.wordpress.com/2010/11/15/sql-2005-blocking-chains-a-friendly-display-using-cte-and-recursion/
--Best to output results to text rather than grid -- SQL 2005 and above only
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
DROP TABLE #Processes
DECLARE @results TABLE (
id INT IDENTITY(1,1),
DB_Name VARCHAR(500),
BlockingSPID INT, SPID INT,
BlockingStatement VARCHAR(MAX),
RowNo INT, LevelRow INT,
hostname sysname
)
SELECT
s.spid,
BlockingSPID = s.blocked,
DatabaseName = DB_NAME(s.dbid),
s.program_name,
s.loginame,
ObjectName = OBJECT_NAME(objectid, s.dbid),
Definition = CAST(text AS VARCHAR(MAX)),
hostname
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50;
WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow, DB_Name, Hostname)
AS
(
SELECT
s.SPID,
s.BlockingSPID,
s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID) AS RowNo,
0 AS LevelRow,
s.DatabaseName AS DB_Name,
s1.hostname
FROM #Processes s
INNER JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
WHERE s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID,
r.BlockingSPID,
r.Definition,
d.RowNo,
d.LevelRow + 1,
r.DatabaseName AS DB_Name,
d.Hostname
FROM #Processes r
INNER JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE r.BlockingSPID > 0
)
INSERT INTO @results (
[DB_Name],
[BlockingSPID],
[SPID],
[BlockingStatement],
[RowNo],
[LevelRow],
[Hostname]
)
SELECT
MIN(DB_NAME) AS DB_Name,
BlockingSPID,
SPID,
BlockingStatement,
MIN(RowNo),
LevelRow,
[Hostname]
FROM Blocking
GROUP BY BlockingSPID, SPID, BlockingStatement, LevelRow, [Hostname]
ORDER BY MIN(RowNo), LevelRow
SELECT
CASE
WHEN [BlockingSPID] = 0
THEN '
****Head of Blocking Chain SPID hostname:' + rtrim(hostname) + ' Spid:' + CAST([SPID] AS VARCHAR(5)) + '...
'
+ 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
+ '
'
+ '...is blocking the following SPID(s):'
WHEN [LevelRow] > 1
THEN '
'
+ SPACE(LevelRow * 5) + 'SPID '
+ CAST(BlockingSPID AS VARCHAR(50))
+ ' is, in turn, blocking the following SPID:
'
+ SPACE((LevelRow + 1) * 5) + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
ELSE '
'
+ SPACE(LevelRow * 5) + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
+ '
'
END
FROM @results
IF NOT EXISTS (SELECT TOP 1 * FROM @results)
PRINT 'No Blocking Chains Were Found'
BEGIN TRY
DROP TABLE #Processes
END TRY
BEGIN CATCH
END CATCH
--Best to output results to text rather than grid -- SQL 2005 and above only
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
DROP TABLE #Processes
DECLARE @results TABLE (
id INT IDENTITY(1,1),
DB_Name VARCHAR(500),
BlockingSPID INT, SPID INT,
BlockingStatement VARCHAR(MAX),
RowNo INT, LevelRow INT,
hostname sysname
)
SELECT
s.spid,
BlockingSPID = s.blocked,
DatabaseName = DB_NAME(s.dbid),
s.program_name,
s.loginame,
ObjectName = OBJECT_NAME(objectid, s.dbid),
Definition = CAST(text AS VARCHAR(MAX)),
hostname
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50;
WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow, DB_Name, Hostname)
AS
(
SELECT
s.SPID,
s.BlockingSPID,
s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID) AS RowNo,
0 AS LevelRow,
s.DatabaseName AS DB_Name,
s1.hostname
FROM #Processes s
INNER JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
WHERE s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID,
r.BlockingSPID,
r.Definition,
d.RowNo,
d.LevelRow + 1,
r.DatabaseName AS DB_Name,
d.Hostname
FROM #Processes r
INNER JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE r.BlockingSPID > 0
)
INSERT INTO @results (
[DB_Name],
[BlockingSPID],
[SPID],
[BlockingStatement],
[RowNo],
[LevelRow],
[Hostname]
)
SELECT
MIN(DB_NAME) AS DB_Name,
BlockingSPID,
SPID,
BlockingStatement,
MIN(RowNo),
LevelRow,
[Hostname]
FROM Blocking
GROUP BY BlockingSPID, SPID, BlockingStatement, LevelRow, [Hostname]
ORDER BY MIN(RowNo), LevelRow
SELECT
CASE
WHEN [BlockingSPID] = 0
THEN '
****Head of Blocking Chain SPID hostname:' + rtrim(hostname) + ' Spid:' + CAST([SPID] AS VARCHAR(5)) + '...
'
+ 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
+ '
'
+ '...is blocking the following SPID(s):'
WHEN [LevelRow] > 1
THEN '
'
+ SPACE(LevelRow * 5) + 'SPID '
+ CAST(BlockingSPID AS VARCHAR(50))
+ ' is, in turn, blocking the following SPID:
'
+ SPACE((LevelRow + 1) * 5) + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
ELSE '
'
+ SPACE(LevelRow * 5) + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
+ '
'
END
FROM @results
IF NOT EXISTS (SELECT TOP 1 * FROM @results)
PRINT 'No Blocking Chains Were Found'
BEGIN TRY
DROP TABLE #Processes
END TRY
BEGIN CATCH
END CATCH
dm_exec_query_stats
SELECT TOP 20
SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2) as SQL,
db_name(query_text.DBID) as DBNAME, Object_name(query_text.objectid,query_text.DBID) as OBJECTNAME
,
cast(c.query_plan as XML) as queryplan, cast(c.query_plan as XML).exist(
'declare default element namespace ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'';
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan//MissingIndexes') HASMISSINGINDEX,
--s.min_worker_time / 1000 AS min_cpu_time_ms,
--s.max_worker_time / 1000 AS max_cpu_time_ms,
((s.total_worker_time * 1.00)/ s.execution_count)/ 1000.00 AS avg_cpu_time_ms,
--s.total_elapsed_time / 1000.00 AS total_execution_time_ms,
--s.min_elapsed_time / 1000.00 AS min_execution_time_ms,
--s.max_elapsed_time / 1000.00 AS max_execution_time_ms,
((s.total_elapsed_time)/ s.execution_count)/ 1000.00 AS avg_execution_time_ms,
--s.total_logical_reads,
--s.min_logical_reads,
--s.max_logical_reads,
((s.total_logical_reads * 1.00)/ s.execution_count) AS avg_logical_reads,
--s.total_logical_writes,
--s.min_logical_writes,
--s.max_logical_writes,
((s.total_logical_writes * 1.00)/ s.execution_count) AS avg_logical_writes,
--s.total_physical_reads,
--s.min_physical_reads,
--s.max_physical_reads,
((s.total_physical_reads * 1.00)/ s.execution_count) AS avg_physical_reads,
s.query_hash,
s.query_plan_hash,
s.execution_count,
-- Convert the DMV's ambiguous DATETIME to DATETIMEOFFSET
TODATETIMEOFFSET (s.creation_time, DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS creation_time,
s.plan_generation_num,
TODATETIMEOFFSET (s.last_execution_time, DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS last_execution_time
--s.total_worker_time / 1000 AS total_cpu_time_ms,
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS query_text
cross apply sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) c
--where Object_name(query_text.objectid,query_text.DBID) = 'p_Folders_sel_All_Active_with_Hierarchy'
ORDER BY total_worker_time DESC
--ORDER By (s.total_elapsed_time/ s.execution_count) DESC
Subscribe to:
Posts (Atom)