Hello newbie,

During the peak time, the first basic which I would like to check is what is running inside. These are the most basic queries i'll use:

select top 10 * from sys.sysprocesses order by cpu desc

select top 10 * from sys.sysprocesses order by physical_io desc

select top 10 * from sys.sysprocesses order by memusage desc

You might want to check the code associated with these top running spids using following code:

dbcc inputbuffer(<spid>)

Also there is famous piece of code from GlennAlanBerry's brilliance:

SELECT TOP(25) qt.[text] AS [SP Name], total_logical_reads, qs.max_logical_reads,

total_logical_reads/qs.execution_count AS [AvgLogicalReads], qs.execution_count AS [Execution Count],

qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second],

qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],

qs.total_worker_time AS [TotalWorkerTime],

qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],

qs.total_logical_writes,

qs.max_logical_writes, qs.total_physical_reads,

DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]

FROMsys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

WHERE qt.[dbid] = DB_ID() -- Filter by current database

ORDER BY total_logical_reads DESC OPTION (RECOMPILE);