August 5, 2008 at 11:20 am
Hi All,
I am tunning and this query and there is lots of blocking with Patchiolatch_up. Need your suggestion in tuning it.
Find the query and execution plan.
Thanks and awaiting for the reply.
"More Green More Oxygen !! Plant a tree today"
August 6, 2008 at 8:46 am
What query?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2008 at 10:00 am
While the query is running, run the following script from master. One of the fields returned will be the xml query plan for the entire batch, and if you're running SP2, one field will return text containing XML for the query statement executing.
If you click on the batch xml, you can simply save it as a .sqlplan file, close it, and open it with management studio to see the entire batch query plan. If you're using management studio, make sure to change the "files of type" drop down to *.* while saving it or it will append a .sql to the end of the filename.
More helpful, however, is the statement xml. Just copy/paste it into a blank window, and save it as a .sqlplan file. You'll be able to target the query plan for the exact statement that's running within the batch.
Once you have the particular statement that's causing the issue, you'll be able to tune it. Pretty much whenever you see any PAGEIOLATCH operations, those are disk I/O related events. So in the query plan look for any expensive scans or bookmark lookups, and target those for your tuning.
-- What's up script
-- Jeremy Brown
-- 6/19/2008
-- Notes: Returns the actively running queries, the batch and statement query plans.
-- sys.dm_exec_text_query_plan REQUIRES SQL 2005 SP2 to be installed.
-- For more details see http://blogs.msdn.com/sqlprogrammability/archive/2007/01/12/5-0-retrieving-query-plans-from-plan-cache-dmv-s.aspx
-- To view query plan, click the XML into its own document and save as a .sqlplan file, reopen with management studio
-- requires VIEW SERVER STATE server level privelage
SELECT
R.session_id
,R.start_time
,R.status
,R.command
,DB_NAME(R.database_id) as database_name
,R.blocking_session_id
,R.wait_type
,R.wait_time / 1000 as wait_time
,R.last_wait_type
,R.wait_resource
,R.percent_complete
,R.estimated_completion_time
,R.cpu_time
,R.total_elapsed_time / 1000 as total_elapsed_time
,R.reads
,R.writes
,R.logical_reads
,R.row_count
,C.connect_time
,C.net_transport
,C.auth_scheme
,C.num_reads AS packet_reads
,C.num_writes AS packet_writes
,C.net_packet_size
,C.last_read AS last_packet_read
,C.last_write AS last_packet_write
,C.client_net_address
,S.HOST_NAME
,S.program_name
,S.client_interface_name
,S.login_name
,S.nt_domain
,S.nt_user_name
,SUBSTRING(ST.text, (R.statement_start_offset/2) + 1, ((CASE R.statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE R.statement_end_offset END - R.statement_start_offset)/2) + 1) AS query_text
,QP.query_plan AS xml_batch_query_plan
,TQP.query_plan AS xml_statement_query_plan --Comment out if you do not have SQL 2005 SP2 or higher.
FROM
sys.dm_exec_requests R
CROSS APPLY
sys.dm_exec_sql_text(R.sql_handle) ST
CROSS APPLY
sys.dm_exec_query_plan(R.plan_handle) QP
CROSS APPLY
sys.dm_exec_text_query_plan(R.plan_handle, R.statement_start_offset, R.statement_end_offset) TQP --Comment out if you do not have SQL 2005 SP2 or higher.
JOIN
sys.dm_exec_connections C ON R.connection_id = C.connection_id AND R.session_id = C.most_recent_session_id
JOIN
sys.dm_exec_sessions S ON C.session_id = S.session_id
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply