Monitor General Health of SQL Server Database

1.Check the slow queries

The reason why it's slow might be because of blocked by other processes. But it's a good start place for those running slow.

select getdate() runtime,st.text,qs.plan_handle,qs.last_execution_time,qs.last_elapsed_time/1000000.00/60 Duration_minutes from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(QS.sql_handle) as ST order by last_elapsed_time desc

Take  a look at the slowest queries and try to optimize them. Their execution plan can be retrieved from sys.dm_exec_query_plan ( plan_handle ). use cross apply to add query plan into the result.
Or just pick up on and examine individually.
 
- get the plan_handle of the query whose plan is going to be examined.
- retrieve the plan
  select * from  sys.dm_exec_query_plan ( plan_handle )
- click on the link to get graphic execution plan for easy reading.

Note:
For an execution of procedure, each statement will end up one entry in the sys.dm_exec_query_stats.
the query can be changed to work on the query plan for that proc. then you can focus on the one that uses most of the time to execute.

For example, Change the above query further to work on one specific query plan.

select st.text,qs.plan_handle,qs.last_execution_time,qs.last_elapsed_time/1000000.00/60 Duration_minutes
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(QS.sql_handle) as ST
where qs.plan_handle=0x05000900C0DF911440A3FAAF000000000000000000000000
order by last_execution_time asc,last_elapsed_time desc

Checking its execution plan, you will see query 18 is the most costly one, that is also corresponding to the execution duration in the query statistics. Then you will be sure that is the performance bottleneck in that procedure. The rest of the work is to focus on it and tune it.

2. Find idle sessions that have open transactions.
An idle session is one that has no request currently running.

SELECT s.* FROM sys.dm_exec_sessions
AS s
WHERE
--has open transaction
EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id )
--no request
AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id ); 

3. Check the index usages

3.1 check how the indexes are used.

select db_name(iu.database_id)DBName,OBJECT_NAME(iu.object_id) ObjectName,i.name IndexName,iu.*
from sys.dm_db_index_usage_stats iu join sys.indexes i on iu.object_id=i.object_id and iu.index_id=i.index_id
join sys.objects o on iu.object_id=o.object_id
where database_id=9 and o.type='U'
...

3.2 Check  size and fragmentation information: sys.dm_db_index_physical_stats
--the following statement check the index on object 1464392286 in database with id 9.
select * from sys.dm_db_index_physical_stats(9,1464392286,null,null,null)
  
Reducing Fragmentation in an Index(http://msdn.microsoft.com/en-us/library/ms188917.aspx)

When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:

• Drop and re-create the clustered index.
  Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. If the index creation is interrupted, the index is not re-created. For more information, see CREATE INDEX (Transact-SQL).
  
• Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. Because this is an online operation, the index is available while the statement is running. The operation can also be interrupted without losing work already completed. The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation, and it does not update statistics.
  
• Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. For more information, see ALTER INDEX (Transact-SQL).

Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times. If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect.

Reducing Fragmentation in a Heap

To reduce the extent fragmentation of a heap, create a clustered index on the table and then drop the index. This redistributes the data while the clustered index is created. This also makes it as optimal as possible,

4. Identifying missing, duplicate or unused indexes

5. monitoring remaining disk space

6. monitoring performance

7. Check database integrity
DBCC CHECKDB

8. Remove older data from msdb
sp_delete_backuphistory
sp_purge_jobhistory
sp_maintplan_delete_log

and much more......