MS SQL - полезные запросы

[Scar]

Убить все коннекты к определенной базе:

Код: [Выделить]

USE master
go

DECLARE @dbname sysname

SET @dbname = 'name of database you want to drop connections from'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

Размеры всех таблиц:

Код: [Выделить]

DECLARE @pagesizeKB int
SELECT @pagesizeKB = low / 1024 FROM master.dbo.spt_values
WHERE number = 1 AND type = 'E'

SELECT
  table_name = OBJECT_NAME(o.id),
  rows = i1.rowcnt,
  reservedKB = (ISNULL(SUM(i1.reserved), 0) + ISNULL(SUM(i2.reserved), 0)) * @pagesizeKB,
  dataKB = (ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0)) * @pagesizeKB,
  index_sizeKB = ((ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0))
    - (ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0))) * @pagesizeKB,
  unusedKB = ((ISNULL(SUM(i1.reserved), 0) + ISNULL(SUM(i2.reserved), 0))
    - (ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0))) * @pagesizeKB
FROM sysobjects o
LEFT OUTER JOIN sysindexes i1 ON i1.id = o.id AND i1.indid < 2
LEFT OUTER JOIN sysindexes i2 ON i2.id = o.id AND i2.indid = 255
WHERE OBJECTPROPERTY(o.id, N'IsUserTable') = 1 --same as: o.xtype = 'IsView'
OR (OBJECTPROPERTY(o.id, N'IsView') = 1 AND OBJECTPROPERTY(o.id, N'IsIndexed') = 1)
GROUP BY o.id, i1.rowcnt
ORDER BY 1 DESC

Поиск отсутствующих индексов с выводом кода для создания индекса (SQL 2005+):

Код: [Выделить]

SELECT 
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
    + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Текущие активные запросы с большим количеством параметров, включая текст запроса (SQL 2005+)

Код: [Выделить]

select  
    r.session_id,  
    r.start_time,  
    r.status,  
    r.command,  
    db_name(r.database_id) as db,  
    r.blocking_session_id,  
    r.wait_type,  
    r.wait_time,  
    r.wait_resource,  
    r.percent_complete,  
    r.estimated_completion_time,  
    r.cpu_time,  
    r.total_elapsed_time,  
    r.scheduler_id,  
    r.reads,  
    r.writes,  
    r.logical_reads,  
    r.row_count,  
    r.granted_query_memory,  
    case r.statement_end_offset  
    when -1 then NULL  
    else object_name(s2.objectid, s2.dbid)  
    end,  
    case r.statement_end_offset  
    when -1 then s2.text  
    else substring(s2.text, r.statement_start_offset/2, (r.statement_end_offset/2) - (r.statement_start_offset/2))  
    end,  
    s3.query_plan  
from sys.dm_exec_requests r  
    cross apply sys.dm_exec_sql_text(r.sql_handle) as s2  
    cross apply sys.dm_exec_query_plan (r.plan_handle) as s3  
where r.status <> 'background'  
    and r.command <> 'task manager'  
    and r.session_id <> @@SPID  
    and r.database_id <> db_id('msdb')  
order by r.cpu_time desc  

Наиболее длительные ожидания по серверу (SQL 2005+):

Код: [Выделить]

    WITH Waits AS 
    (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,  
        100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,  
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn  
     FROM sys.dm_os_wait_stats  
     WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP',   
      'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',  
      'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits  
    SELECT W1.wait_type,   
      CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,  
      CAST(W1.pct AS DECIMAL(12, 2)) AS pct,  
      CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct  
    FROM Waits AS W1  
    INNER JOIN Waits AS W2  
    ON W2.rn <= W1.rn  
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct  
    HAVING SUM(W2.pct) - W1.pct < 90; -- percentage threshold 

[stavgreengo]

-- Просмотр детальной информации по всем блокировкам
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  p.spid, kpid, blocked, d.name, hostname, cmd, program_name, lastwaittype, waittype, waittime, waitresource, p.dbid, uid, cpu, physical_io,
      memusage, login_time, last_batch, ecid, open_tran, p.status, p.sid, hostprocess, nt_domain, nt_username, net_address, net_library, loginame,
      context_info, sql_handle, stmt_start, stmt_end
FROM         sysprocesses p left outer join sysdatabases d on d.dbid = p.dbid
--WHERE p.status = 'runnable' ORDER BY cpu desc
--WHERE hostname = 's04-sp01'
order by hostname desc,p.spid

declare @sysprocesses CURSOR
SET @sysprocesses = CURSOR LOCAL STATIC FOR
SELECT SPID FROM master..sysprocesses
--WHERE status = 'runnable' ORDER BY cpu desc
--WHERE hostname = 's04-sp01'


DECLARE @handle binary(20)
DECLARE @SPID smallint
declare @SQL table
(
   SPID smallint,
   Sql_Query text
)
open @sysprocesses
   WHILE (1 = 1)
   BEGIN
      FETCH @sysprocesses INTO @SPID
      IF @@FETCH_STATUS <> 0 BREAK
         print @SPID
         SELECT @handle = sql_handle FROM master..sysprocesses WHERE spid = @SPID
         insert @SQL
         (
            SPID,
            Sql_Query
         )
         SELECT @SPID,[text] FROM ::fn_get_sql(@handle)
   END
   
select sq.SPID, blocked, sp.hostname, d.name, sp.last_batch, datediff(mi/*ss*/,sp.last_batch,getdate()) mi /*sec*/, sp.cmd, sp.status, program_name, sq.Sql_Query
from @SQL sq inner join
    sysprocesses sp ON sq.SPID = sp.SPID left outer join sysdatabases d on d.dbid = sp.dbid
--where     sp.hostname = 'srv-moss'
/*
select sp.hostname, count(sp.hostname) cnt
from @SQL sq inner join
    sysprocesses sp ON sq.SPID = sp.SPID
group by sp.hostname
order by cnt desc
--DBCC INPUTBUFFER(@SPID)
--kill 138; kill 177
kill 79
kill 123
kill 124
kill 125
kill 77
kill 57
*/

[stavgreengo]

-- Просмотр невыполненых джобов, удобно к нему прикручивать требуемые процедуры при выявлении оных
SELECT     JJ.instance_id, sj.job_id, sj.name AS JOB_NAME, sjt.step_name, JJ.run_status, JJ.sql_message_id, JJ.sql_severity, JJ.message, JJ.exec_date, JJ.run_duration,
                      JJ.server, sjt.output_file_name, JJ.run_code
FROM         (SELECT     ssh.instance_id, sjh.job_id, sjh.step_id, sjh.sql_message_id, sjh.sql_severity, sjh.message,
                                              (CASE sjh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END)
                                               AS run_status, SUBSTRING(CAST(sjh.run_date AS VARCHAR(), 5, 2) + '/' + SUBSTRING(CAST(sjh.run_date AS VARCHAR(), 7, 2)
                                              + '/' + SUBSTRING(CAST(sjh.run_date AS VARCHAR(), 1, 4) + ' ' + SUBSTRING(REPLICATE('0', 6 - LEN(CAST(sjh.run_time AS varchar)))
                                              + CAST(sjh.run_time AS VARCHAR), 1, 2) + ':' + SUBSTRING(REPLICATE('0', 6 - LEN(CAST(sjh.run_time AS VARCHAR))) + CAST(sjh.run_time AS VARCHAR), 3,
                                              2) + ':' + SUBSTRING(REPLICATE('0', 6 - LEN(CAST(sjh.run_time AS varchar))) + CAST(sjh.run_time AS VARCHAR), 5, 2) AS exec_date, sjh.run_duration,
                                              sjh.run_status AS run_code, sjh.retries_attempted, sjh.server
                       FROM          msdb.dbo.sysjobhistory AS sjh INNER JOIN
                                                  (SELECT     job_id, step_id, MAX(instance_id) AS instance_id
                                                    FROM          msdb.dbo.sysjobhistory AS sjh
                                                    GROUP BY job_id, step_id) AS ssh ON sjh.instance_id = ssh.instance_id
                       WHERE      (sjh.run_status <> 1)) AS JJ INNER JOIN
                      msdb.dbo.sysjobs AS sj ON JJ.job_id = sj.job_id INNER JOIN
                      msdb.dbo.sysjobsteps AS sjt ON JJ.job_id = sjt.job_id AND JJ.step_id = sjt.step_id

[WingDog]

stavgreengo,
тэг CODE осильте как нибудь пожалуйста =)