Rank: Administration
Medals: Groups: Administrators
Joined: 10/20/2012 Posts: 106 Points: 327 Location: Tbilisi
Thanks: 0 times Was thanked: 0 time(s) in 0 post(s)
|
თუ გვაინტერესებს რომელი ბრძანებები ტვირთავს SQL Server-ს ყველაზე მეტად და ოპტიმიზაციისათვის გვინდა დავათვალიეროთ ამ ბრძანებების შესრულების გეგმები (query plans), ამისათვის შეგიძლიათ შეასრულოთ შემდეგი ბრძანება. დანარჩენი კი ანალიზისა და ოპტიმიზაციის საქმეა.... BB კოდი: with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql) select top(10) qst.text as sql_text, qp.query_plan , qst.statement_id , qst.statement_text as select_statement , qps2.statement_optimization_level , qps2.statement_optimization_early_abort_reason , ( select sum(ro.SubTreeCost.value(N'@EstimatedTotalSubtreeCost', 'float')) from qp.query_plan.nodes( N'//sql:Batch/sql:Statements/sql:StmtSimple/sql:QueryPlan[1]/sql:RelOp[1]' ) as ro(SubTreeCost) ) as Totalcost , qps2.statement_sub_tree_cost , qst.creation_time, qst.last_execution_time, qst.execution_count , qst.total_elapsed_time, qst.last_elapsed_time, qst.min_elapsed_time, qst.max_elapsed_time , qst.total_worker_time, qst.last_worker_time, qst.min_worker_time, qst.max_worker_time , qst.total_physical_reads, qst.last_physical_reads , qst.min_physical_reads, qst.max_physical_reads , qst.total_logical_writes, qst.last_logical_writes , qst.min_logical_writes, qst.max_logical_writes , qst.total_logical_reads, qst.last_logical_reads , qst.min_logical_reads, qst.max_logical_reads , qst.total_clr_time, qst.last_clr_time, qst.min_clr_time, qst.max_clr_time , qst.sql_handle, qst.plan_handle from ( select * , substring(st.text, (qs.statement_start_offset/2)+1 , ((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as statement_text , ROW_NUMBER() OVER(PARTITION BY qs.plan_handle ORDER BY qs.statement_start_offset) as statement_id from sys.dm_exec_query_stats as qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as st ) as qst cross apply sys.dm_exec_query_plan (qst.plan_handle) as qp cross apply ( select ROW_NUMBER() OVER(ORDER BY qps1.statement_id) as rel_statement_id , qps1.statement_optimization_level, qps1.statement_sub_tree_cost , qps1.statement_optimization_early_abort_reason from ( select sel.StmtSimple.value('@StatementId', 'int') , sel.StmtSimple.value('@StatementSubTreeCost', 'float') , sel.StmtSimple.value('@StatementOptmLevel' , 'varchar(30)') , sel.StmtSimple.value('@StatementOptmEarlyAbortReason', 'varchar(30)') from qp.query_plan.nodes( N'//sql:Batch/sql:Statements/sql:StmtSimple[@StatementType = "SELECT"]' ) as sel(StmtSimple) ) as qps1(statement_id, statement_sub_tree_cost , statement_optimization_level, statement_optimization_early_abort_reason) ) as qps2 where qps2.rel_statement_id = qst.statement_id order by Totalcost desc, qst.plan_handle, qst.statement_id;
|
|
Apex ltd. http://www.apex.ge
|