Usefull Queries for Sql Server data base Monitoring

I have collected a list of useful queries  for database  Monitoring ,  for the purpose of these posts,   i'm   running  few queries on adventure works   databases  , i will be using these views to demonstrate the impact of those queries   .

Note:Use of these queries are up to the  user  responsibilities,so i do not recommend to use these views without  testing on a test server,  as the impact of these views on system resources  can be intense at  times, and i do not own some of  these views and for those views  total credit goes to the individual contributors  and companies , the purpose of these posting  is to systematically bring different views at one place for troubleshooting  in sql server
.--------------------------------------------------------------------------------
Index Fragmentation  in Sql Server

---------------------------------------------------------------------------------

IF EXISTS ( SELECT *

    FROM [tempdb].[dbo].[sysobjects]

    WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))

    DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]

GO



CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](

    [DatabaseName]                  [nvarchar] (100) NULL,

    [ObjectName]                    [nvarchar] (100) NULL,

    [Index_id]                      INT,

    [indexName]                     [nvarchar] (100) NULL,

    [avg_fragmentation_percent]     float NULL,

    [IndexType]                     [nvarchar] (100) NULL,

    [Action_Required]               [nvarchar] (100) default 'NA'

) ON [PRIMARY]



DECLARE @dbname varchar(1000)

DECLARE @sqlQuery nvarchar(4000)



DECLARE dbcursor CURSOR for

SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb','model', 'msdb')



OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @dbname



WHILE @@FETCH_STATUS = 0

BEGIN

    SET @sqlQuery = '

    USE [' + @dbname + '];



    IF EXISTS

    (

        SELECT compatibility_level

       FROM sys.databases

        WHERE

            name  = N'''+ @dbname +'''

            AND compatibility_level >= 90

    )

    BEGIN

        INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]

        (

            DatabaseName

            , ObjectName

            , Index_id

            , indexName

            , avg_fragmentation_percent

            , IndexType

        )

        SELECT

            db_name() as DatabaseName

            , OBJECT_NAME (a.object_id) as ObjectName

            , a.index_id, b.name as IndexName

            , avg_fragmentation_in_percent

            , index_type_desc

        FROM

            sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

            JOIN sys.indexes AS b

        ON

            a.object_id = b.object_id

            AND a.index_id = b.index_id

        WHERE

            b.index_id <> 0

            AND avg_fragmentation_in_percent <> 0

    END;'

   

    EXEC sp_executesql @sqlQuery

   

FETCH NEXT FROM dbcursor

INTO @dbname

END



CLOSE dbcursor

Deallocate dbcursor




UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]

SET Action_Required = 'Rebuild'

WHERE avg_fragmentation_percent >30

GO



UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]

SET Action_Required = 'Reorganize'

WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5

GO



SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]

ORDER BY databasename

This  view is great  in the sense that it gives a clear view of what to do  and on which object the impact will be ,as you can see fragmentation  anything over 30%  an   index rebuild is specified  and fragmentation between  30% to to %5 a index reorganization  is  specified