BottleNecks of Performance in SQL Server

Here i provide a complete tutorial on sql server performance tuning starting with an introduction to the problems or causes of performance degradtion and provide solution to those performance problems along with the tools in sql server like performance monitor,DBCC etc.for improving the performance.
It’s not uncommon to experience occasional slowdown in sql server database.A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problems.We need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. Here we discuss the step-by-step guidelines for diagnosing and troubleshooting common performance problems like
1) Resource Bottlenecks
2) CPU Bottlenecks
3) Memory Bottlenecks
4) I/O Bottlenecks
5) TempDB problems
6) Slowrunning Queries
Using various tools of SQL Server such as
1) System Monitor
2) Database Engine Tuning Advisor
3) Sql Server Profiler and also other techniques of using
1) DBCC
2) Dynamic Management Views
3) Index Tuning
Here we discuss the performance problems as well as monitoring the performnace using the performance tool System monitor.
There can be many reasons for a slowdown in SQL Server. We use the following three key symptoms to start diagnosing problems.
Resource bottlenecks: CPU, memory, and I/O bottlenecks are the main resource problems faced . We do not consider network issues rather than a brief view. For each resource bottleneck, we describe how to identify the problem and then iterate through the possible causes and then use the tools present. For example, a memory bottleneck can lead to excessive paging that ultimately impacts performance.
1) CPU Bottlenecks:
A CPU bottleneck that happens suddenly and unexpectedly, without additional load on the server, is commonly caused by a nonoptimal query plan, a poor configuration, or design factors, and not insufficient hardware resources. Before rushing out to buy faster and/or more processors, we should first identify the largest consumers of CPU bandwidth and see if they can be tuned.System Monitor is generally the best means to determine if the server is CPU bound. We should look to see if the Processor:% Processor Time counter is high; values in excess of 80% processor time per CPU are generally deemed to be a bottleneck. We can also monitor the SQL Server schedulers using the sys.dm_os_schedulers view to see if the number of runnable tasks is typically nonzero. A nonzero value indicates that tasks have to wait for their time slice to run; high values for this counter are a symptom of a CPU bottleneck. You can use the query below to list all the schedulers and look at the number of runnable tasks
Monitoring hidden and nonhidden schedulers: The following query outputs the state of workers and tasks in SQL Server across all schedulers. This query was executed on a computer system that has the following
SELECT scheduler_id, cpu_id, parent_node_id, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count, work_queue_count FROM sys.dm_os_schedulers;


Here is the result set.
scheduler_id cpu_id parent_node_id current_tasks_count
------------------------------------------------------------
0 1 0 9
257 255 0 1
1 0 1 10
258 255 1 1
255 255 32 2
runnable_tasks_count current_workers_count
------------------------------------------------------
0 11
0 1
0 18
0 1
0 3
active_workers_count work_queue_count
6 0
1 0
8 0
1 0
Excessive compilation and recompilation
When a batch or remote procedure call (RPC) is submitted to SQL Server, before it begins executing, the server checks for the validity and correctness of the query plan. If one of these checks fails, the batch may have to be compiled again to produce a different query plan. Such compilations are known as recompilations. These recompilations are generally necessary to ensure correctness and are often performed when the server determines that there could be a more optimal query plan due to changes in underlying data. Compilations by nature are CPU intensive and hence excessive recompilations could result in a CPU-bound performance problem on the system.
SQL Server 2005 introduces statement-level recompilation of stored procedures. When SQL Server 2005 recompiles stored procedures, only the statement that caused the recompilation is compiled—not the entire procedure. This uses less CPU bandwidth and results in less contention on lock resources such as COMPILE locks. Recompilation can happen due to various reasons, such as:
• Schema changed• Statistics changed• Deferred compile• Temporary table changed• SET option changed• Stored procedure created with the RECOMPILE query hint or which uses OPTION (RECOMPILE)