March 30, 2012 at 6:59 am
hello guys, i have a sql 2008 r2 instance database that is around 40 GBs. In this database there is one table that is constantly used by my biztalk instance. I have a reindex/reorg job that runs everynight and once it gets to that table it just gets stuck. Therefore i decided to skip that table and the job ran successfully. I ran the rebuild iindex online for this table with the script below, but my question is when the rebuild index online is running i see it with a -2. the rebuilt completes successfully after a few hours, but i am worried once i put this in the job it might block other processes in the middle of the night. doesn't the -2 mean the spid is an orphaned transaction? , Is this normal for a rebuild index online? would this spid cause blocking or performance issues?
alter index IDX_myindex_UserID_CourseID_ItemID on DBName.dbo.tablename rebuild with (sort_in_tempdb=on, online=ON, statistics_norecompute=off, maxDOP=4)
March 30, 2012 at 7:46 am
There's data about online index operations here: http://msdn.microsoft.com/en-us/library/ms191261.aspx
Specifics about an online rebuild are here: http://msdn.microsoft.com/en-us/library/ms188388.aspx
It specifically says:
ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
So, in other words, operations against the underlying table will be possible and it won't block things, except during the very beginning and the very end of the rebuild (and those phases are normally very short).
What you're more likley to run into is queries not being able to use the index while it's being rebuilt. That means recompiling execution plans, and probably slower execution time (if they're faster when they can't use the index, that's a whole different issue that you need to look into).
Does that help?
- 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
March 30, 2012 at 9:39 am
thanks, but you still have not answered my question, why does the spid which is doing the rebuild index a Negative spid?, (blkd column has a -2) , which is Caused by orphaned distributed transaction?
March 30, 2012 at 9:47 am
This is not normal.
It sounds like there may have been an IO problem during the rebuild of an index that affected it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2012 at 4:19 pm
the weird thing is that it happens everytime the script to rebuild is ran, could it be something wrong with that certain table?
March 31, 2012 at 4:59 pm
DBA-640728 (3/30/2012)
thanks, but you still have not answered my question, why does the spid which is doing the rebuild index a Negative spid?, (blkd column has a -2) , which is Caused by orphaned distributed transaction?
Is the spid that is doing the rebuild -2 or is the index rebuild blocked by spid 2? If the latter, what locks that orphaned session hold?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2012 at 10:29 pm
yes the same spid that is doing the rebuild is the one with -2, I keep checking and is not really blocking any spids. Could it be because there are many sharepoint processes hitting the table that is being rebuilt?
this is the script the spid is running:
alter index IDX_AuditEvent_UserID_LabelID_ItemID on Distrib.dbo.AuditUser rebuild with (sort_in_tempdb=on, online=on, statistics_norecompute=off, maxDOP=4)
when i run select * from sys.sysprocesses:
last waittype: LCK_M_S
kpid: -26424
blocked: -2
waittypewaittime
0x00031816979
April 1, 2012 at 5:22 am
DBA-640728 (3/31/2012)
yes the same spid that is doing the rebuild is the one with -2
How did did you see that? Spid -2 won't appear in sys.dm_exec_requests, and DBCC InputBuffer can't take -2 as a parameter. So what it is that tells you that the index rebuild is running under spid -2?
when i run select * from sys.sysprocesses:
last waittype: LCK_M_S
kpid: -26424
blocked: -2
waittypewaittime
0x00031816979
Whatever that process is, it is getting blocked by the orphaned transaction. What query is that, that has those values in sysprocesses.
[/quote]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2012 at 7:50 am
when i run select * from sys.sysprocesses , i find the spid#, with the blocked column with the -2
then i use that spid and run dbcc inputbuffer (spid#)
and i see that is that spid running the alter index command.
Also i have my own script i use to see the current running spids:
select
spid
,open_tran 'o_txn'
,case when blocked = spid then -1 else blocked end 'blkd'
,cmd
,left(p.status,10) 'status'
,left(hostname,12) 'hostname'
, loginame = case when CHARINDEX('\', loginame)=0 then left(loginame,12) else substring(loginame,CHARINDEX('\', loginame)+1,12) end
--,waittype
,waittime_ms=waittime, left(lastwaittype,15) 'lastwaittype'
,left(convert(varchar,isnull(last_batch,login_time),121),23) 'last_batch', datediff(second,isnull(last_batch,login_time),getdate()) 'batch_runtime_ss' , login_time
,left(program_name,130) 'program_name'
,d.name 'db_name'
,physical_io
,cpu
,waitresource
,spidd=spid
,SUBSTRING(st.text, (p.stmt_start/2)+1,((CASE p.stmt_end WHEN -1 THEN DATALENGTH(st.text) ELSE p.stmt_end END - p.stmt_start )/2) + 1) AS statement_text, st.text
--,*
from master..sysprocesses p (nolock)
left join master..sysdatabases d (nolock) on d.dbid = p.dbid
OUTER APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
where
(
cmd not in ( 'AWAITING COMMAND','TASK MANAGER','LOCK MONITOR')
and spid > 50
and spid <> @@spid
)
or blocked <> 0
--in the blocked column i see that -2.
Please let me know if you have any ideas why this happens, this a hightly used table, is around 4 million rows. 1GBs of data space.
April 1, 2012 at 8:27 am
DBA-640728 (4/1/2012)
when i run select * from sys.sysprocesses , i find the spid#, with the blocked column with the -2then i use that spid and run dbcc inputbuffer (spid#)
and i see that is that spid running the alter index command.
Right, so the alter index is running normally on a normal session ID. What's happening is that you have an orphaned DTC transaction of some form (the spid -2) that is blocking the alter index.
The alter index is the session that is being blocked. It is not causing the blocking, in it not the problem.
The solution here is the normal one for orphaned DTC transactions, kill the UOW and check check application code to ensure that it is handling DTC transactions properly, especially focus on any java apps you have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2012 at 8:34 am
Thank you Gila, i will find out what the -2 spid is running, it appears it happens constantly.
April 2, 2012 at 9:03 am
You won't be able to find what it was running from SQL, all that SQL can tell you is the locks held. That might provide a hint as to where the queries are coming from, but that's all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2012 at 4:46 pm
you are right, I tried running select req_transactionUOW
from master..syslockinfo
where req_spid = -2
and it only told me the transaction but not really what it was running, is there any other way where i can get more information?
April 3, 2012 at 1:25 am
Unfortunatley not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply