SQL SERVER DBA and Azure SQL

——- KILL all the connections for a database ——

Use Master

Declare @dbname sysname

Set @dbname = 'Put-Your-DB-Name'

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

select top 10 *

from sys.dm_os_wait_stats

where wait_type not in --remove common waits to identify worst bottlenecks

(

'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',

'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',

'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',

'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP',

'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',

'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',

'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',

'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'

)

order by wait_time_ms desc

SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,

( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))

/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )

AS PercentageSignalWaitsOfTotalTime

FROM sys.dm_os_wait_stats

Pages