This blog is to learn and share SQL DBA and Azure SQL knowledge among people by Bandaru Ajeyudu & Sudheer Thota
——- KILL all the connections for a database ——
Use Master
Go
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
SELECT
DB_NAME(mf.database_id) AS databaseName,
name AS File_LogicalName,
CASE
WHEN type_desc = 'LOG' THEN 'Log File'
WHEN type_desc = 'ROWS' THEN 'Data File'
ELSE type_desc
END AS File_type_desc
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
,size_on_disk_bytes/ 1024 AS size_on_disk_KB
,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB
,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.FILE_ID = divfs.FILE_ID
ORDER BY num_of_Reads DESC
--Index--Missing Index Notification Mail
DECLARE @email_profile_name VARCHAR(100);
DECLARE @email_recipients VARCHAR(100);
set @email_profile_name = 'SQLajay' ;
set @email_recipients = 'bandarucreations@sqldbanow.com';
If(OBJECT_ID('tempdb..#TempMissingIndex') Is Not Null)
Begin
Drop Table #TempMissingIndex
End
create table #TempMissingIndex
(
index_advantage int,
DB_info Varchar(350),
Equality_columns Varchar(350),
Inequality_columns Varchar(350),
Included_columns Varchar(350)
)
--EXEC sp_MSforeachdb '
--IF ''?'' NOT IN(''master'',''tempdb'',''msdb'', ''model'')
BEGIN
USE [dba]; -- here you can the database which you want to see
Insert into #TempmissingIndex
SELECT
user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,
dbmid.[statement] AS [Database.Schema.Table] ,
dbmid.equality_columns ,
dbmid.inequality_columns ,
dbmid.included_columns
FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle
WHERE dbmid.[database_id] = DB_ID()
and (user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) > 50000
ORDER BY index_advantage DESC ;
END
IF EXISTS (SELECT * FROM #TempMissingIndex)
begin
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML =
N'<h1>Missing Index</h1>'
+ N'<table border="1">'
+ N'<tr><th>index_advantage</th><th>DB_info</th><th>Equality_columns</th><th>Inequality_columns</th><th>Included_columns</th></tr>'
+ CAST (
( Select
td = [PP].[index_advantage] , ''
, td = [PP].[DB_info] , ''
, td = [PP].[Equality_columns] , ''
, td = [PP].[Inequality_columns] , ''
, td = [PP].[Included_columns]
FROM #TempMissingIndex PP
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)
)
+ N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name =@email_profile_name ,
@recipients = @email_recipients ,
@subject = 'Report',
@body = @tableHTML,
@body_format = 'HTML';
END;
Drop Table #TempMissingIndex