SQL Server Performance issues with Fragmentation and heavy usage of TEMPDB?

Whenever a performance issue occurs on the SQL Server database best option for diagnosing and troubleshooting common problems by using publicly available tools such as Profiler, System Monitor (Perfmon), and Dynamic Management Views (DMVs) in SQL Server 2005 (onwards).

What to detect?

• Use SYSMON to detect excessive compiles and recompiles. Technical documentation refers that for this kind of problem use SQL Statistics object that provides counters to monitor compilation and the type of requests that are sent to an instance of SQL Server. The general trend will be the ratio of SQL Recompilations/sec to Batch Requests/sec should be low unless users are submitting ad hoc queries.

What if high value of compiles & recompiles are found?

• If the PerfMon counters indicate a high number of recompiles, then the problem is due to recompilation of stored procedures and also underlying defragmentation of indexes (this will be discussed below). It is obvious that you have seen the recompiles are contributing to the high CPU consumed by SQL Server. So using Profiler trace to find the stored procedures that were being recompiled, this helps to obtain information along with the reason for the recompilation.
• Further one of the Microsoft technical document gives the following TSQL to see all recompile events that were capture in the trace:

select 
    spid,
    StartTime,
    Textdata,
    EventSubclass,
    ObjectID,
    DatabaseID,
    SQLHandle 
from 
    fn_trace_gettable ( 'e:\recompiletrace.trc' , 1)
where 
    EventClass in(37,75,166)

Going forward you can take help of other counters in SYSMOn such as Access Methods object counters that will to monitor how the logical data within the database is accessed. Additionally you could also find the physical access to the database pages on disk is monitored using the Buffer Manager counters. With this monitoring methods in place can help you to determine whether query performance can be improved by adding or modifying indexes, adding or moving partitions, adding files or file groups, defragmenting indexes, or by rewriting queries. So to find out the overall usage of amount of data, indexes and free space within the database you could make use of Access Methods counters. This is where the data volume and fragmentation information can be obtained to see whether they are contributing to the performance issues in addition to excessive recompilations above, by default excessive index fragmentation can impair performance.  

So on the subject to find out the performance issues with a quick execution of steps you could make use of DMVs and for more detailed information about the data volume usage, index fragmentation you can execute the following ones:

• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_partition_stats
• sys.dm_db_index_usage_stats

Also the usage of TEMPDB is high from SQL Server 2005 onwards, that is explained more from these Working with tempdb in SQL Server 2005 & Optimizing tempdb Performance Technet articles in addition to viewing the following DMVs results:

• sys.dm_db_file_space_usage
• sys.dm_db_task_space_usage
• sys.dm_db_session_space_usage

That will be starting point for you to analyze the performance issue for further assessment.