Useful T-SQL Scripts

  • [アーティクル]

A couple of very useful and heavy used T-SQL scripts... Finally, I have decided to post both in order to find them every time I need with out having to re-author:

1. Kill all existing connections to a certain database:

USE master

DECLARE @DataBaseName varchar(255) = '<YOUR_DATABASE_NAME>'

DECLARE @spid INT, @cnt INT, @sql VARCHAR(255)
SELECT @spid = MIN(spid), @cnt = COUNT(*) FROM master..sysprocesses WHERE dbid = DB_ID(@DataBaseName) AND spid != @@SPID

PRINT 'Cleaning up process...'
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'

    
WHILE @spid IS NOT NULL
BEGIN
    PRINT 'About to KILL '+RTRIM(@spid) 
    SET @sql = 'KILL '+RTRIM(@spid)
    EXEC(@sql) 
    SELECT @spid = MIN(spid), @cnt = COUNT(*)
        FROM master..sysprocesses
        WHERE dbid = DB_ID(@DataBaseName)
        AND spid != @@SPID 
    PRINT RTRIM(@cnt)+' processes remain.'
END

 

2. The "What's going on" query: Which are the queries are currently executed?, How much resources are using?. Very useful to understand where are the server resources spent.

SELECT
        master.dbo.fn_varbintohexstr(sql_handle) + '|' + master.dbo.fn_varbintohexstr(plan_handle) + convert(varchar(30),statement_start_offset) + '|' + convert(varchar(30),statement_end_offset) as uniqueid,
        creation_time,
        last_execution_time as last_run,
        execution_count as execs,
        total_logical_reads as logical_reads,
        total_elapsed_time as elapsed,
        total_physical_reads as phys_reads,
        total_worker_time as CPU,
        total_rows as rows,
        total_clr_time as clr,
        total_logical_writes as log_writes,
        last_worker_time,
        min_worker_time,
        max_worker_time,
        last_physical_reads,
        min_physical_reads,
        max_physical_reads,
        last_logical_reads,
        min_logical_reads,
        max_logical_reads,
        last_logical_writes,
        min_logical_writes,
        max_logical_writes,
        last_clr_time,
        min_clr_time,
        max_clr_time,
        last_elapsed_time,
        min_elapsed_time,
        max_elapsed_time,
        last_rows,
        min_rows,
        max_rows,
        current_timestamp,
        master.dbo.fn_varbintohexstr(sql_handle) as sql_handle,
        master.dbo.fn_varbintohexstr(plan_handle) as plan_handle,
        statement_start_offset,statement_end_offset,plan_generation_num,
        replace(replace(SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
                 ((CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(qt.text)
                        ELSE qs.statement_end_offset END
                        - qs.statement_start_offset)/2) + 1),char(10),''),char(13),'') as statement_text
FROM sys.dm_exec_query_stats as qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY 5 desc

 

Hope this helps!

Comments

  • Anonymous
    February 06, 2013
    The comment has been removed

  • Anonymous
    February 06, 2013
    Mattie, the problem is related to your version of SQL Server; Those columns are only available form SQL Server 2008 R2 (msdn.microsoft.com/.../ms189741(v=sql.105).aspx)

  • Anonymous
    February 15, 2013
    Good article