September 12, 2010 at 9:41 am
Hi, we are having blocking issues with tempdb database, blocking transaction is open for 30 minutes and has create table #Table statements in it, because of this tempdb system catalogs: sys.partitions and sys.allocation_units are locked until the transaction is committed. (Is this the intended behavior, locks on system views?)
The blocked query is:
select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
which is I guess part of sp_spaceused system sp, this is called by SCOM db size monitor, as sys.partitions and sys.allocation_units are locked, this query waits for 30 minutes and causing a blocking alert at SCOM monitor.
How can I resolve this blocking? Is there a way to make the system tables free of lock? Especially for tempdb, Does every open transaction creates #table causes the tempdb system tables locked?
Any answers appreciated,
September 12, 2010 at 9:05 pm
sporoy (9/12/2010)
Hi, we are having blocking issues with tempdb database, blocking transaction is open for 30 minutes and hascreate table #Tablestatements in it, because of this tempdb system catalogs: sys.partitions and sys.allocation_units are locked until the transaction is committed. (Is this the intended behavior, locks on system views?)The blocked query is:
select @reservedpages = sum(a.total_pages),@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
which is I guess part of sp_spaceused system sp, this is called by SCOM db size monitor, as sys.partitions and sys.allocation_units are locked, this query waits for 30 minutes and causing a blocking alert at SCOM monitor.
How can I resolve this blocking? Is there a way to make the system tables free of lock? Especially for tempdb, Does every open transaction creates #table causes the tempdb system tables locked?
Any answers appreciated,
Whenever possible use table variables a.k.a. @Tables rather than temporary tables a.k.a. #Tables.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 12, 2010 at 9:09 pm
PaulB-TheOneAndOnly (9/12/2010)
sporoy (9/12/2010)
Hi, we are having blocking issues with tempdb database, blocking transaction is open for 30 minutes and hascreate table #Tablestatements in it, because of this tempdb system catalogs: sys.partitions and sys.allocation_units are locked until the transaction is committed. (Is this the intended behavior, locks on system views?)The blocked query is:
select @reservedpages = sum(a.total_pages),@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
which is I guess part of sp_spaceused system sp, this is called by SCOM db size monitor, as sys.partitions and sys.allocation_units are locked, this query waits for 30 minutes and causing a blocking alert at SCOM monitor.
How can I resolve this blocking? Is there a way to make the system tables free of lock? Especially for tempdb, Does every open transaction creates #table causes the tempdb system tables locked?
Any answers appreciated,
Whenever possible use table variables a.k.a. @Tables rather than temporary tables a.k.a. #Tables.
Owch! Sorry... That's just some plain ol' bad advice, Paul. Table Variables can be a huge performance problem if any size to them occurs especially since the optimizer sees them as a single row. I'll agree that "It Depends" but making a blanket statement to use table variables whenever possible is just flat out wrong. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2010 at 9:13 pm
sporoy (9/12/2010)
Hi, we are having blocking issues with tempdb database, blocking transaction is open for 30 minutes and hascreate table #Tablestatements in it, because of this tempdb system catalogs: sys.partitions and sys.allocation_units are locked until the transaction is committed. (Is this the intended behavior, locks on system views?)The blocked query is:
select @reservedpages = sum(a.total_pages),@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
which is I guess part of sp_spaceused system sp, this is called by SCOM db size monitor, as sys.partitions and sys.allocation_units are locked, this query waits for 30 minutes and causing a blocking alert at SCOM monitor.
How can I resolve this blocking? Is there a way to make the system tables free of lock? Especially for tempdb, Does every open transaction creates #table causes the tempdb system tables locked?
Any answers appreciated,
I see the blocked query... what does the entire blocking query look like? Also, you mention "transactions"... are you populating a temp table within an explicit transaction???
--Jeff Moden
Change is inevitable... Change for the better is not.
September 12, 2010 at 9:40 pm
Jeff Moden (9/12/2010)
Owch! Sorry... That's just some plain ol' bad advice, Paul. Table Variables can be a huge performance problem if any size to them occurs especially since the optimizer sees them as a single row. I'll agree that "It Depends" but making a blanket statement to use table variables whenever possible is just flat out wrong. 😉
Agreed Jeff, point taken. I should have included some wording to better describe the "whenever possible" part of it. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 13, 2010 at 5:23 am
Hi Jeff, the blocking query from all blocking transactions report is:
INSERT #StateInterval (
ManagedEntityMonitorRowId
,IntervalStartDateTime
,IntervalEndDateTime
,HealthState)
SELECT
l.ManagedEntityMonitorRowId
,CASE WHEN l.[DateTime] <
@IntervalStartDateTime
THEN @IntervalStartDateTime
ELSE l.[DateTime]
END
,CASE WHEN ISNULL(r.
[DateTime], '99991231') >
@IntervalEndDateTime
THEN @IntervalEndDateTime
ELSE r.[DateTime]
END
,CASE WHEN l.NewHealthState >
ISNULL(r.OldHealthState,
l.NewHealthState)
THEN l.NewHealthState
ELSE ISNULL(r.OldHealthState,
l.NewHealthState)
END
FROM #StateEvent l
LEFT JOIN #StateEvent r ON
(l.ManagedEntityMonitorRowId =
r.ManagedEntityMonitorRowId) AND (l.
[DateTime] < r.[DateTime])
WHERE (NOT EXISTS (SELECT *
FROM #StateEvent m
WHERE
(l.ManagedEntityMonitorRowId =
m.ManagedEntityMonitorRowId)
AND (m.[DateTime] >
l.[DateTime])
AND (m.[DateTime] <
ISNULL(r.[DateTime], '99991231'))
)
)
AND (l.[DateTime] <
@IntervalEndDateTime)
-- "WHEN MAX(i2.HealthState) IN (1, 50) THEN 1" added below to aid
-- in cases where many HSs are unavailable - it takes to long
-- to figure out if monitor/me still exist and we know it'll
-- be figured out anyways on next interval since "previous"
-- interval end states 1 and 50 are eliminated from calculations
This is the longest statement in explicit transaction, there are 400 statements in the transaction. (I captured the statements in profiler and looked between begin tran and commit from this SPID.)
Is there a possibility to rescue the system tables from being locked?
September 13, 2010 at 9:13 am
I'd wait for others to comment but I'm not sure I would perform a transaction of that size on temporary tables to begin with..how long does it take for the whole process to run? and is your transaction being blocked by anything also?
September 13, 2010 at 9:46 am
are you creating the temp table(s) itself within an open transaction ? (avoid if possibible)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 13, 2010 at 12:01 pm
I absolutely agree with the above. You should just about never include a temp table in a transaction especially (but not limited to) the creation of a temp table.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2010 at 12:05 pm
Yes, its create #temp in an explicit transaction, at one point an insert to temp table takes 30 minutes, so locking sys.partitions.. The queries are coming from System Center Operations Manager itself..
September 13, 2010 at 12:08 pm
What I'm trying to figure out is if the blocking unavoidable in these circumtances? Should I contact with SCOM support? This does not look as SQL's fault..
September 13, 2010 at 12:09 pm
Is the SCOM query being blocked by anything?
I don't recall any inserts (or anything really) taking 30 minutes in my setup.
September 13, 2010 at 12:17 pm
The case is, there is an sp named OperationsManagerDW.dbo.StandardDatasetMaintenance in OperationsManagerDW, which is called periaodically every 1 hour or so, this call blocks tempdb because of temp tables, and meanwhile SCOM db monitor tries sp_spaceused for tempdb (to give alert about tempdb size), but it is blocked, then we have an BlockedSP alert in SCOM each 15 minutes.. (a bit complicated I guess if I did not got it wrong)
September 13, 2010 at 10:28 pm
sporoy (9/13/2010)
What I'm trying to figure out is if the blocking unavoidable in these circumtances? Should I contact with SCOM support? This does not look as SQL's fault..
It's not SCOM's fault either. It's the fault of who ever that made the mistake of putting a temp table in an explicit transaction. That code MUST be repaired or removed.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2010 at 3:26 pm
Use this statement at the top of your query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
This will make any table look up perform the same as the Hint (With NOLOCK) on each Join
The only drawback is that it allows for Dirty reads on the table you are referencing so if your data is highly time sensitive, this may or may not work.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply