SQL Server Performance Tuning

Transferred from: http://www.cnblogs.com/MR_ke/archive/2010/08/25/1807856.html

SQL 2005 Performance Tuning

SQL Server runs for a period of time, and as data accumulates, SQL runs less efficiently, and in order to use business system normal actions, IT departments often need to spend a high price on SQL tuning experts to solve. In fact, tuning is not complex, mainly to find the impact of the efficiency of SQL, and then the right remedy, here are a few tips, I believe it is very practical for everyone.

1, check the reason for SQL blockingSelectblocking_session_id, Wait_duration_ms, session_id fromsys.dm_os_waiting_taskswhereblocking_session_id is  not NULL 2, check the first 10 SQL statements waiting for a resourceSelect Top Ten * fromsys.dm_os_wait_statsOrder  byWait_time_msdesc 3, the query shows the top 50 SQL statements with the highest average CPU usageSELECT TOP  -Total_worker_time/Execution_count as [AVG CPU time],(SELECT SUBSTRING(text, Statement_start_offset/2,( Case  whenStatement_end_offset= -1  Then LEN(CONVERT(nvarchar(Max),text))* 2 ELSEStatement_end_offsetEnd -Statement_start_offset)/2) fromSys.dm_exec_sql_text (sql_handle)) asQuery_text,* fromsys.dm_exec_query_statsORDER  by [AVG CPU time] DESC 4, CPU bottlenecks are usually caused by the following reasons: The query plan is not optimal, improperly configured, poorly designed, or insufficient hardware resources. The following common queries can help you determine the cause of the CPU bottleneck. The following query gives you insight into which batches or processes are currently cached that consume most of the CPU resources. SELECT TOP  -SUM(Qs.total_worker_time) asTotal_cpu_time,SUM(Qs.execution_count) asTotal_execution_count,COUNT(*) asNumber_of_statements,qs.sql_handle fromSys.dm_exec_query_stats asQSGROUP  byQs.sql_handleORDER  by SUM(Qs.total_worker_time)DESC 5, the following query shows the total CPU usage (with SQL text) consumed by the cache plan. SELECTtotal_cpu_time,total_execution_count,number_of_statements,s2.textROM (SELECT TOP  -SUM(Qs.total_worker_time) asTotal_cpu_time,SUM(Qs.execution_count) asTotal_execution_count,COUNT(*) asNumber_of_statements,qs.sql_handle fromsys.dm_exec_query_stats asQSGROUP  byQs.sql_handleORDER  by SUM(Qs.total_worker_time)DESC) asStats CrossAPPLY sys.dm_exec_sql_text (Stats.sql_handle) asS26, the following sample query shows the pre-recompiled -a stored procedure. Plan_generation_num indicates the number of times that the query has been recompiled. Select Top  -Sql_text.text, Sql_handle,plan_generation_num,execution_count,dbid,objectid fromsys.dm_exec_query_stats a CrossApply Sys.dm_exec_sql_text (sql_handle) asSql_textwherePlan_generation_num> 1Order  byPlan_generation_numdesc 7, a less efficient query plan may increase CPU utilization. The following query shows which query consumes the most CPU cumulative usage. 

SELECTHighest_cpu_queries.plan_handle, Highest_cpu_queries.total_worker_time, Q.dbid, Q.objectid, Q. Number, q.encrypted, Q.[text] from(Select Top  -Qs.plan_handle, Qs.total_worker_time fromSys.dm_exec_query_stats QSOrder  byQs.total_worker_timedesc) ashighest_cpu_queries CrossApply Sys.dm_exec_sql_text (plan_handle) asQOrder  byHighest_cpu_queries.total_worker_timedesc

8, the following query shows some operators that may be consuming a lot of CPU usage (for example '%Hash Match%’、‘%Sort%') to identify suspicious objects. Select * fromSys.dm_exec_cached_plans Crossapply Sys.dm_exec_query_plan (plan_handle)wherecast(Query_plan as nvarchar(Max)) like '%sort%'or cast(Query_plan as nvarchar(Max)) like '%hash match%' 9, if you have detected a query plan that is inefficient and that causes high CPU usage, run the tables that are involved in the queryUPDATE STATISTICSTo see if the problem persists. Then, collect the relevant data and report the issue to PerformancePoint planning support staff. If your system has excessive compilation and recompilation, it can cause CPU-related performance issues with your system. You can run the following DMV query to find out the excessive compilation/Recompile . Select *  fromSys.dm_exec_query_optimizer_infowhereCounter= 'Optimizations' orCounter= 'Elapsed Time'There are mistakes in the place to welcome everyone to shoot bricks, hope to exchange and share. 

SQL Server Performance Tuning