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):

• Resource Bottlenecks: concentrate on server resource usage such as CPU, Memory and disk I/O issues.
• Network Bottlenecks: many times DBAs seems to forgot about this point, this is essential component of your platform as the data is transfered using network only.
• Slow-running query: a FAQ within forums referring that a query runs quick when it tested, but it slows or longer-execution times when it is deployed in Production platform. Not a single point of source we can look for such problems. Various reasons for slowdown of performance could be:
  • Out of date table statistics which can lead to a poor query plan.
  • Ad hoc TSQL statements (usage of Dynmaic SQL)
  • Missing indexes or heavy table scans
  • Excessive or unprecedented blocking from application (due to poor schema design)
  • Improper isolation level usages
  • Transaction log file size (user database)
  • Application services on SQL Server machine or anti-virus scanning
  • Improper TEMPDB configuration, a major problem which can dictate the performance at large. A simple application query can overload tempdb through excessive DDL or DML operations and by taking too much space.
  • TEMPDB sizing - data and log file (do not leave the defaults) 

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:

• Look at the fillfactor value on your indexes.
  • Having said that if the application is highly transactional and data modificiations are volatile then choosing a LOW fill-factor with PAD_INDEX ON for the indexes. Such as set the fillfactor between 10 and 15. You can further monitor the page latch contention and waits using the DMV: 

    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

    SELECT 

    * FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'   

     

    * FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'   

    * FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGELATCH%'   

• Look at the RECOVERY INTERVAL server configuration.
  • As you may be aware that automatic checkpoints occurs in the databases with respect to the recovery model (FULL/BULK-LOGGED/SIMPLE) setting. The automatic checkpoint interval is based on the amount of log space used and time elapsed since the last checkpoint. When there are huge updates within the data and the log gets filled up the automatic checkpoint will kick in.
  • Use the RECOVERY INTERVAL option that will specify the maximum time that DB engine should use to recover a database during a restart. The ideal value will be between 6 and 10 minutes (based on the activity on your database, ensure to test). Do not simply change the option when you are hosting database on a failover clustering environment.
• DO not attempt to change the system default DEGREE OF PARALLELISM option. Just back to basics on this point, DB engine will determine whether the current workload and configuration can allow for parallel execution. If in case the parallel execution is performed then number of threads are generated based on the system's availability. You will see such a behaviour when a large REINDEX or REORG operation is in progress.
• Do not change MAX WORKER THREADS option, see the relevant blogpost: http://sqlserver-qa.net/blogs/perftune/archive/2011/05/10/10089.aspx for more information.
• Make best use of QUERY and TABLE hints, SQL Server 2008 gets a new feature called plan freezine which allows you to freeze a plan exactly as it is cached. You can refer to USE PLAN query hint within the TSQL/SP that you see the plan is optimal. Whereever necessary you can use KEEP PLAN query hint too and also specify KEEPFIXED PLAN.
• Avoid recompilations on the frequently executing stored procedures and to avoid recompilations do not interleave DML and DDL/DDL  from the conditional constructs such as IF or CASE statements. Whenever the query is having batches then make sure to use fully qualified object names with schema-name.
• Run UPDATE STATISTICS on the tables that are suffering with performance loss, and execute the query to see whether problem persists.
• Ensure that sufficient Disaster Recovery (DR) options are followed, the database backup strategy for all the databases is important. Do not assume that once the database backup is scheduled (to disk or tape) the DR is guaranteed, the recovery point of time is more important. That means how quickly you can get back your database ONLINE when the complete system is down due to unprecedented activity. The business SLA is more important here and depending upon the quicker recovery need you must employ the HIGH AVAILABILITY features from SQL Server.
• Database Mirroring is the best available and easily managed feature to obtain HA on your data platform, if a selective data is required to keep the application online then REPLICATION is also a better option. Pay important attention for the connection bandwidth between the primary and DR sites, a slow network connection can reduce the data recovery & availability options. 
• As per the KBA 920093 use of specific SQL Server trace flags can optimize the performance. Such as for the servers with 64 or more CPU cores you might consider trace flag 834, which will enable the SQL Server to use Windows large-page memory allocations for the buffer pool to boost the performance. See KBA http://support.microsoft.com/kb/920093 for more information.
• Look at the following PERFMON counters and their offerings from the collected data: