Thursday, August 16, 2012

Collect Lock information

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


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

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