Most active database in sql server

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

Read more »
No comments:

Index--Missing Index on database Notification Mail

 --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


Read more »
No comments:

DBA_Blocking_Alerts script

 Please find the below mentioned script help you get an alert..


SET NOCOUNT ON

DECLARE @xml nvarchar(max)

SELECT @xml = Cast((SELECT HOST_NAME() AS 'td','',b.session_id AS 'td',

'',

(b.wait_duration_ms/1000)/60 AS 'td',

'',

b.wait_type AS 'td',

'',

b.blocking_session_id AS 'td',

'',

t.text AS 'td'

FROM sys.dm_os_waiting_tasks b inner join sys.dm_exec_requests r on r.session_id= b.session_id 

OUTER APPLY 

sys.dm_exec_sql_text(sql_handle) t 

WHERE b.blocking_session_id <> 0 and b.wait_duration_ms>300000

FOR xml path('tr'), elements) AS NVARCHAR(max))

Declare @body nvarchar(max)

SET @body =

'<html>

 <head>

 <style>

 table, th, td 

 {

 border: 1px solid black;

 border-collapse: collapse;

 text-align: center;

 }

 </style>

 </head>

 <body>

 <H2>

 Blocking queries

 </H2>

 <table> 

 <tr>

 <th>Instance Name</th><th> Blocked Session ID </th> <th> Wating in minutes </th> <th> Wait type</th> 

 <th> Blocking Session ID</th><th>Query waiting to execute</th>

 </tr>'

 SET @body = @body + @xml + '

 </table>

 </body>

</html>'

if(@xml is not null)

BEGIN

EXEC msdb.dbo.Sp_send_dbmail

@profile_name = 'dbmail',

@body = @body,

@body_format ='html',

@recipients = 'ajay@sqldbanow.com',

@copy_recipients ='bandarucreations@sqldbanow.com', -- list of Email recipients

@subject = 'Blocking queries Alert-SQLDBANOW01';

END

SET NOCOUNT OFF

No comments:
Subscribe to: Posts (Atom)