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

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

SP_INDEXINFO

http://www.karaszi.com/sqlserver/util_sp_indexinfo.asp


/*
Parameters:
@tblPat sysname = '%'
Name of table. Default is all tables in current database.

@missing_ix tinyint = 1
Whether or not to include missing indexes information. Default is "yes".

Usage examples
USE Adventureworks
EXEC sp_indexinfo
EXEC sp_indexinfo 'Product'
EXEC sp_indexinfo 'Product', 0
EXEC sp_indexinfo 'Product%'
EXEC sp_indexinfo DEFAULT, 0
EXEC pubs..sp_indexinfo


WARNING:
In order for this to be treated as a regular system procedure we need to create it in the master database and also mark it as a system procedure.

JEOB: Added information for compression and filtered index - 2008 ONLY
*/



USE master
GO
IF OBJECT_ID('sp_indexinfo') IS NOT NULL DROP PROC sp_indexinfo
GO

CREATE PROCEDURE sp_indexinfo
 @tblPat sysname = '%'
,@missing_ix tinyint = 0
AS
--Written by Tibor Karaszi 2008-07-07
--Last modified by Tibor Karaszi 2008-07-09
WITH key_columns AS
(
SELECT c.OBJECT_ID, c.name AS column_name, ic.key_ordinal, ic.is_included_column, ic.index_id, ic.is_descending_key
FROM sys.columns  AS c with (nolock)
 INNER JOIN sys.index_columns  AS ic with (nolock) ON c.OBJECT_ID = ic.OBJECT_ID AND ic.column_id = c.column_id
)
, physical_info AS
(
SELECT p.OBJECT_ID, p.index_id, ds.name AS location, SUM(p.rows) AS rows, SUM(a.total_pages) AS pages, p.data_compression_desc as Data_Compression
FROM sys.partitions  AS p with (nolock)
 INNER JOIN sys.allocation_units AS a with (nolock) ON p.hobt_id = a.container_id
 INNER JOIN sys.data_spaces  AS ds with (nolock) ON a.data_space_id = ds.data_space_id
GROUP BY OBJECT_ID, index_id, ds.name, p.data_compression_desc
)
SELECT
 OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS schema_name
,OBJECT_NAME(i.OBJECT_ID) AS table_name
,i.name AS index_name
,CASE i.type WHEN 0 THEN 'heap' WHEN 1 THEN 'cl' WHEN 2 THEN 'nc' WHEN 3 THEN 'xml' ELSE CAST(i.type AS VARCHAR(2)) END AS type
,i.is_unique
,CASE
  WHEN is_primary_key = 0 AND is_unique_constraint = 0 THEN 'no'
  WHEN is_primary_key = 1 AND is_unique_constraint = 0 THEN 'PK'
  WHEN is_primary_key = 0 AND is_unique_constraint = 1 THEN 'UQ'
 END
 AS cnstr

,(SELECT
  CAST(kc.column_name + CASE kc.is_descending_key WHEN 0 THEN '' ELSE ' DESC' END AS VARCHAR(MAX)) + ', '  AS [text()]
  FROM key_columns AS kc
  WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 0
  ORDER BY key_ordinal
  FOR XML PATH('')
 ) AS key_columns
,(SELECT CAST(column_name AS VARCHAR(MAX)) + ', '  AS [text()]
  FROM key_columns AS kc
  WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 1
  ORDER BY key_ordinal
  FOR XML PATH('')
 ) AS included_columns
 ,i.filter_definition
,p.location
,p.rows
,p.pages
,CAST((p.pages * 8.00) / 1024 AS decimal(9,2)) AS MB
,p.Data_Compression
,s.user_seeks
,s.user_scans
,s.user_lookups
,s.user_updates
,STATS_DATE(i.object_id, i.index_id) as Statistics_last_collected
FROM sys.indexes AS i with (nolock)
INNER JOIN physical_info AS p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS s with (nolock) ON s.OBJECT_ID = i.OBJECT_ID AND s.index_id = i.index_id AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsMsShipped') = 0
AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat
ORDER BY table_name, index_name

IF @missing_ix = 1
BEGIN
SELECT
 OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name
,OBJECT_NAME(d.OBJECT_ID) AS table_name
,'CREATE INDEX ON ' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '.' + OBJECT_NAME(d.OBJECT_ID) + ' '
 + '(' + COALESCE(d.equality_columns, '') + COALESCE(d.inequality_columns, '') + ')'
 + COALESCE(' INCLUDE(' + d.included_columns + ')', '')
 AS ddl
,s.user_seeks
,s.user_scans
,s.avg_user_impact
FROM sys.dm_db_missing_index_details  AS d with (nolock)
INNER JOIN  sys.dm_db_missing_index_groups  AS g with (nolock) ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS s with (nolock) ON g.index_group_handle = s.group_handle
WHERE OBJECT_NAME(d.OBJECT_ID) LIKE @tblPat
AND d.database_id = DB_ID()
ORDER BY avg_user_impact DESC
END
GO

EXEC sp_MS_marksystemobject sp_indexinfo

-- CALL WHO IS ACTIVE

------select * from sys.traces
--MASTER..XP_readerrorlog

--SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'Physical Name',
--SERVERPROPERTY('Servername') AS 'Server Name',
--SERVERPROPERTY('MachineName') AS 'Machine Name',
--SERVERPROPERTY('Edition') AS 'Edition',
-- SERVERPROPERTY('ProductVersion') AS 'Version'


----select wait_type, count(*) waiting_tasks
----from sys.dm_os_waiting_tasks
----group by wait_type
----order by count (*) desc

exec sysadmin.dbo.usp_WhoIsActive

--~
    --Filters--Both inclusive and exclusive
    --Set either filter to '' to disable
    --Valid filter types are: session, program, database, login, and host
    --Session is a session ID, and either 0 or '' can be used to indicate "all" sessions
    --All other filter types support % or _ as wildcards
    --@filter  = '',
    --@filter_type  = 'session',
    --@not_filter  = '',
    --@not_filter_type  = 'session',

    --Retrieve data about the calling session?
    --@show_own_spid  = 0,

    --Retrieve data about system sessions?
    --@show_system_spids  = 1,

    --Controls how sleeping SPIDs are handled, based on the idea of levels of interest
    --0 does not pull any sleeping SPIDs
    --1 pulls only those sleeping SPIDs that also have an open transaction
    --2 pulls all sleeping SPIDs
    @show_sleeping_spids  = 0,

    --If 1, gets the full stored procedure or running batch, when available
    --If 0, gets only the actual statement that is currently running in the batch or procedure
    @get_full_inner_text  = 0,

    --Get associated query plans for running tasks, if available
    --If @get_plans = 1, gets the plan based on the request's statement offset
    --If @get_plans = 2, gets the entire plan based on the request's plan_handle
    @get_plans  = 1,

    --Get the associated outer ad hoc query or stored procedure call, if available
    @get_outer_command  = 1,

    --Enables pulling transaction log write info and transaction duration
    @get_transaction_info  = 1,

    --Get information on active tasks, based on three interest levels
    --Level 0 does not pull any task-related information
    --Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
    --Level 2 pulls all available task-based metrics, including:
    --number of active tasks, current wait stats, physical I/O, context switches, and blocker information
    @get_task_info  = 1,

    --Gets associated locks for each request, aggregated in an XML format
    @get_locks  = 0,

    --Get average time for past runs of an active query
    --(based on the combination of plan handle, sql handle, and offset)
    --@get_avg_time  = 0,

    --Walk the blocking chain and count the number of
    --total SPIDs blocked all the way down by a given session
    --Also enables task_info Level 1, if @get_task_info is set to 0
    @find_block_leaders  = 1,

    --Pull deltas on various metrics
    --Interval in seconds to wait before doing the second data pull
    --@delta_interval  = 0,

    --List of desired output columns, in desired order
    --Note that the final output will be the intersection of all enabled features and all
    --columns in the list. Therefore, only columns associated with enabled features will
    --actually appear in the output. Likewise, removing columns from this list may effectively
    --disable features, even if they are turned on
    --
    --Each element in this list must be one of the valid output column names. Names must be
    --delimited by square brackets. White space, formatting, and additional characters are
    --allowed, as long as the list contains exact matches of delimited valid column names.
    --@output_column_list  = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',

    --Column(s) by which to sort output, optionally with sort directions.
        --Valid column choices:
        --session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
        --tempdb_current, CPU, context_switches, used_memory, physical_io_delta,
        --reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta,
        --tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta,
        --tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,
        --percent_complete, host_name, login_name, database_name, start_time
        --
        --Note that column names in the list must be bracket-delimited. Commas and/or white
        --space are not required.
    @sort_order = '[start_time] DESC'

    --Formats some of the output columns in a more "human readable" form
    --0 disables output format
    --1 formats the output for variable-width fonts
    --2 formats the output for fixed-width fonts
    --@format_output  = 1

    --If set to a non-blank value, the script will attempt to insert into the specified
    --destination table. Please note that the script will not verify that the table exists,
    --or that it has the correct schema, before doing the insert.
    --Table can be specified in one, two, or three-part format
    --@destination_table  = '',

    --If set to 1, no data collection will happen and no result set will be returned; instead,
    --a CREATE TABLE statement will be returned via the @schema parameter, which will match
    --the schema of the result set that would be returned by using the same collection of the
    --rest of the parameters. The CREATE TABLE statement will have a placeholder token of
    -- in place of an actual table name.
    --@return_schema  = 0,
    --@schema VARCHAR(MAX) = NULL OUTPUT,

    --Help! What do I do?
    --@help  = 0