It is not uncommon to see the intermittent performance slowdown within your data platform, in order to tackle the problem in early stages you must deploy the proactive practices to minimize the problem. When they occur you will not have time to diagnose the root cause, rather fix the problem and then perform a corrective actions to ensure it will not occur again. So the way of diagnosis and troubleshooting common performance problems using available tools such as Profiler and SYSMON (*PERFMON) is quite common, by combining the strength of data-collector (MDW) dashboard reports & Dynamic Management Views (DMV) (and Extended Events - XEvents from SQL 2008 onwards) can powerup the chances of reducing the problem on a greater scale.
Once the problem is identified it is easy to fix, there can be many reasons for the slowdown within the SQL Server data platform. When it comes to monitoring and diagnosis you must start from the following points (not in any particular order of preference):
The above are few list of points can be classed as symptoms that are not necessarily dependent or independent to each other, which can get the performance down drastically or slowlly over a period of time due to data modifications. In addition to the above here are few gotchas (notes) that I have collected/implemented during my performance tuning consulting projects:
SELECT * FROM sys.dm_db_index_operational_stats (DB_ID('<dbid>'), NULL, NULL, NULL) ORDER BY [page_latch_wait_in_ms] DESC, tree_page_latch_wait_in_ms DESC
* FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'
Database File Sizes (including tempdb)
Need for expansion of files or storage.
Log File Sizes
Need to backup transaction log more often.
Processor/% Processor Time: All Instances
Average Disk Queue Length
Storage configuration too slow.
Average Disk sec/transfer
May indicate a large amount of disk fragmentation, slow disks, or disk failures. (Should be 10 ms or less.)
Disk Bytes/sec for each LUN
Need to spread data across more LUNs.
Paging in Pages/sec
Need for additional memory.
Network Interface Bytes Received/sec and Network Interface Bytes Sent/sec
Need for increased network capacity or segmentation.
Tags: best practices, sql server, performance monitoring, BRH, recovery, notes, beginners, sql master, configuration, memory, cookbook,