Here is a collection of five stored procedures that are very useful for monitoring the overall health of a SQL Server 2005/2008 instance. These are instance level queries, not database level queries. They are very easy to periodically call from a Windows or Web client to display these items:

Average SQL Task Count            High values can indicate blocking

Average Runnable Count            High values can indicate CPU pressure

Average Pending I/O Count        High values indicate I/O pressure

Page Life Expectancy                  Low values indicate memory pressure

SQL Cache sizes                         Can indicate issues with cache growth

SQL Connection Counts              Can indicate changes in workload

These queries give you a pretty good idea of the state of your SQL Server instance, especially if you are familiar with their baseline values and you run them often enough to notice significant changes. I wrote a little Windows application that updates every thirty seconds with the results, so I am very aware of their normal value ranges.

-- Glenn Berry
-- Several SQL Server monitoring stored procedures
-- http://glennberrysqlperformance.spaces.live.com
-- Twitter: GlennAlanBerry
/* DBAdminGetAvgPendingIOCount ==========================================================================
Description : Get Avg Pending I/O Count
                        
Author: Glenn Berry    
Date: 6/24/2007    
Input:                            
Output:    
Used By: Only used to monitor the database instance                
Last Modified          Developer        Description
-----------------------------------------------------------------------------------------------------------
6/24/2007            Glenn Berry        Created
=========================================================================================================*/CREATE PROCEDURE [dbo].[DBAdminGetAvgPendingIOCount]    
AS
    SET NOCOUNT ON;
    SELECT AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
    FROM sys.dm_os_schedulers WITH (NOLOCK);
    
    RETURN;
GO    
/* DBAdminGetAvgSQLTaskCounts ===========================================================================
Description : Get Avg SQLTaskCounts
                        
Author: Glenn Berry    
Date: 2/13/2007    
Input:                            
Output:    
Used By: Only used to monitor the database instance               
Last Modified          Developer        Description
-----------------------------------------------------------------------------------------------------------
2/13/2007            Glenn Berry        Creation
=========================================================================================================*/CREATE PROCEDURE [dbo].[DBAdminGetAvgSQLTaskCounts]
AS
    SET NOCOUNT ON;
    SELECT AVG(current_tasks_count) AS [Avg Task Count], 
           AVG(runnable_tasks_count) AS [Avg Runnable Task Count]
    FROM sys.dm_os_schedulers WITH (NOLOCK)
    WHERE scheduler_id < 255;
    
    RETURN;
GO
/* DBAdminGetPageLifeExpectancy ==========================================================================
Description : Get Page Life Expectancy value
                        
Author: Glenn Berry    
Date: 2/13/2007    
Input:                            
Output:    
Used By: Only used to monitor the database instance               
Last Modified          Developer        Description
-----------------------------------------------------------------------------------------------------------
2/13/2007            Glenn Berry        Creation
=========================================================================================================*/CREATE PROCEDURE [dbo].[DBAdminGetPageLifeExpectancy]    
AS
    SET NOCOUNT ON;
    SELECT cntr_value AS [Page Life Expectancy]
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE object_name = 'SQLServer:Buffer Manager'  -- This is for the default instance
    AND counter_name = 'Page life expectancy'; 
    
    RETURN;
GO
/* DBAdminGetSQLCacheSize ================================================================================
Description : Get SQL Cache Size for a given type of cache
    CACHESTORE_SQLCP - Ad-hoc cache
    CACHESTORE_OBJCP - SP cache
    USERSTORE_TOKENPERM - cumulative permission checks for queries (caused issues with early builds of SQL 2005)
                        
Author: Glenn Berry    
Date: 7/26/2007    
Input:                            
Output:    
Used By: Only used to monitor the database instance               
Last Modified          Developer        Description
-----------------------------------------------------------------------------------------------------------
7/26/2007            Glenn Berry        Creation
=========================================================================================================*/CREATE PROCEDURE [dbo].[DBAdminGetSQLCacheSize]
( @CacheType nvarchar(60))    
AS
    SET NOCOUNT ON;
    SELECT SUM(single_pages_kb) AS [SPAMemoryKB] 
    FROM sys.dm_os_memory_clerks WITH (NOLOCK)
    WHERE [type] = @CacheType;
    
    RETURN; 
    
GO
/* DBAdminGetUserConnectionCount =========================================================================
Description : Get connection count for a database user. Works with SQL and Windows authentication.
                        
Author: Glenn Berry    
Date: 6/29/2007    
Input:                            
Output:    
Used By: Only used to monitor the database instance               
Last Modified          Developer        Description
-----------------------------------------------------------------------------------------------------------
6/29/2007            Glenn Berry        Creation
=========================================================================================================*/CREATE PROCEDURE [dbo].[DBAdminGetUserConnectionCount]
(@UserName nvarchar(128))
AS
    SET NOCOUNT ON;
    SELECT COUNT(session_id) AS [session_count] 
    FROM  sys.dm_exec_sessions WITH (NOLOCK)
    WHERE login_name = @UserName;
    
    RETURN;
    
GO