April 6, 2015 at 9:32 am
This is running on sql server 2012, we have seup the databases on Availability group. The strange behavior i have been seeing the past few weeks is task manager has been blocked from index Re-org but have not found that what that task manager doing.. it won't shows the statement that running but shows as command type= 'Task manager'. Does any one have any idea? Index is so big and i have been stopping this because of triple thread blocking.
April 6, 2015 at 10:24 am
Are you using the DMVs to look at the blocked and blocking processes? If not, sys.dm_exec_requests will show you which processes are blocked and which are blocking. You can then join that to sys.dm_exec_sql_text to understand which query is being run.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2015 at 10:35 am
i did use it but statement shows NULL
April 6, 2015 at 10:55 am
Quick question, are you using Full Text Search / Indexing?
😎
April 6, 2015 at 11:33 am
i was using this
SELECT
DB_NAME(er.[database_id]) [DatabaseName]
,er.start_time
,sp.memusage
,sp.cpu
,es.host_name
,es.logical_reads
,es.lock_timeout
,es.reads
,sp.loginame
,es.row_count
,er.[session_id] AS [SessionID]
,er.[command] AS [CommandType]
,est.[text] [StatementText]
,er.[status] AS [Status]
,er.blocking_session_id
,er.[last_wait_type] [LastWait]
,er.[wait_resource] [CurrentWait]
FROM sys.dm_exec_requests AS er
INNER JOIN sys.dm_exec_sessions AS es ON er.[session_id] = es.[session_id]
OUTER APPLY sys.dm_exec_sql_text(er.[sql_handle]) est
inner JOIN sys.sysprocesses sp
ON sp.spid=er.session_id
April 6, 2015 at 11:36 am
Quick suggestion, try Adam Machanic's Who is Active
😎
April 6, 2015 at 12:17 pm
Thanks for the quick suggestion. i will check that when it run next time. but is there any idea why re-org should block the( it had schema lock), i was thinking re-org would not block to other process.
April 6, 2015 at 12:19 pm
Sagar-636902 (4/6/2015)
Thanks for the quick suggestion. i will check that when it run next time. but is there any idea why re-org should block the( it had schema lock), i was thinking re-org would not block to other process.
It has to. It's writing to the index. Even if the writes are less than rebuild, it's still writes. Writes block other processes. It's to be expected.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2015 at 12:27 pm
it was blocking for more then 12 hours.. and which crazy that create triple thread block and re-org blocked 2 other process in chain.. since having large table i cannot do rebuild at all so only option was re-org and which was not blocking before... and not sure what is that task manager that was getting blocked..looking for the solution to that block.
April 6, 2015 at 12:36 pm
Sagar-636902 (4/6/2015)
it was blocking for more then 12 hours.. and which crazy that create triple thread block and re-org blocked 2 other process in chain.. since having large table i cannot do rebuild at all so only option was re-org and which was not blocking before... and not sure what is that task manager that was getting blocked..looking for the solution to that block.
Is partitioning an option here?
😎
April 6, 2015 at 12:57 pm
that is under way, which takes while to do that.. any other solution?
April 6, 2015 at 1:13 pm
Sagar-636902 (4/6/2015)
that is under way, which takes while to do that.. any other solution?
Grab a copy of dbMonitor, connect it to the server and make certain that it is capturing the blockage, it will then save the blocking instances in an Excel spreadsheet. Pretty handy tool for this type of ad hoc diagnostics.
😎
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply