July 31, 2008 at 12:45 pm
Carl Federl (7/31/2008)
spid dbid ObjId IndId Type Resource Mode Status
----------------------------------------------------------------
163 2 2 1 KEY (500065c893b9) S WAIT
This is a block on the tempdb sysindexes table caused because the creation of the temporary tables are within a transaction. I do recall that tempdb blocking like this would occur with SQL Server 7 but have not seen this blocking with 2000. Please check your SQL Server version and service pack level.
We are on SQL 2000 SP4 actually, as modern as it gets with SQL 2000...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 31, 2008 at 12:59 pm
Actually, I can not see why you even need a transaction for this. If everything is in TempDB anyway, why do you need Transactions, Commit or Rollback?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 31, 2008 at 1:13 pm
rbarryyoung (7/31/2008)
Actually, I can not see why you even need a transaction for this. If everything is in TempDB anyway, why do you need Transactions, Commit or Rollback?
Good point.
The script I have posted has been re-worked from several stored procedures and massaged to eliminate unneccessary complexity.
I will ask our devs that same question.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 31, 2008 at 2:34 pm
Marios Philippopoulos (7/31/2008)
rbarryyoung (7/31/2008)
Actually, I can not see why you even need a transaction for this. If everything is in TempDB anyway, why do you need Transactions, Commit or Rollback?Good point.
The script I have posted has been re-worked from several stored procedures and massaged to eliminate unneccessary complexity.
I will ask our devs that same question.
It turns out that the query hangs even when the temp tables are substituted with real tables created on the same db as the rest.
So it looks like the issue is not with tempdb.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 31, 2008 at 2:52 pm
In sysprocesses table for my SPID I see PAGELATCH_SH as the lastwaittype.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 31, 2008 at 3:06 pm
The problem is definitely a wait on tempdb system tables as reproduced in the below SQL. On sysobjects, syscolumns and sysindexes, there are "Intent eXclusive" TABLE locks and "exclusive" key locks.
Alternative solutions are:
1. Write the SQL without temporary tables.
2. Create all tables before beginning the transaction.
use tempdb
go
-- Show locks for the connection:
select convert (smallint, req_spid) As spid
,db_name(rsc_dbid) As DatabaseName
,object_name(rsc_objid) As ObjectName
,rsc_indid As IndId
,substring (v.name, 1, 4) As Type
,substring (rsc_text, 1, 16) as Resource
,substring (u.name, 1, 8) As Mode
,substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo L
joinmaster.dbo.spt_values v
on v.number = L.rsc_type
and v.type = 'LR'
joinmaster.dbo.spt_values x
on x.number = L.req_status
and x.type = 'LS'
joinmaster.dbo.spt_values u
on u.number = L.req_mode + 1
and u.type = 'L'
wherereq_spid = @@spid
begin tran
create table #w (i int )
select convert (smallint, req_spid) As spid
,db_name(rsc_dbid) As DatabaseName
,object_name(rsc_objid) As ObjectName
,rsc_indid As IndId
,substring (v.name, 1, 4) As Type
,substring (rsc_text, 1, 16) as Resource
,substring (u.name, 1, 8) As Mode
,substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo L
joinmaster.dbo.spt_values v
on v.number = L.rsc_type
and v.type = 'LR'
joinmaster.dbo.spt_values x
on x.number = L.req_status
and x.type = 'LS'
joinmaster.dbo.spt_values u
on u.number = L.req_mode + 1
and u.type = 'L'
wherereq_spid = @@spid
rollback
SQL = Scarcely Qualifies as a Language
July 31, 2008 at 3:58 pm
Thanks for the suggestion. It looks like the contention involves the sysobjects table on both the user and tempdb databases.
Moving the temp table creation and deletion statements outside the transaction has resulted in the query completing after 6.5 min (it still takes only a few seconds with the explicit transaction removed!).
In sysprocesses I see lastwaittype: PAGELATCH_SH and waitresource: 13:1:6396860.
A bit before the query completes execution I see lastwaittype: PAGEIOLATCH_SH and waitresource: 2:1:274.
I know the first number is the database id: 13 is the id of my user database and 2 is the id of tempdb.
Also the second number - 1 - is the sysobjects table in both databases.
But I forget what the 3rd number means. Any ideas?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 31, 2008 at 4:14 pm
"But I forget what the 3rd number means. Any ideas? " The Page number.
Have you optimized tempdb? See "Concurrency enhancements for the tempdb database" at http://support.microsoft.com/kb/328551
SQL = Scarcely Qualifies as a Language
July 31, 2008 at 5:49 pm
Carl Federl (7/31/2008)
"But I forget what the 3rd number means. Any ideas? " The Page number.Have you optimized tempdb? See "Concurrency enhancements for the tempdb database" at http://support.microsoft.com/kb/328551
Thanks for the link, I'm following the data file recommendation (4 datafiles of equal size and no autogrowth corresponding to 4 CPUs), but not the hotfix (we are already on a later version, 8.0.2187, that's SP4) and not the trace flag.
Should I implement the trace flag, T1118?
I think I'll give that a try.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 31, 2008 at 6:08 pm
Marios Philippopoulos (7/31/2008)
Carl Federl (7/31/2008)
"But I forget what the 3rd number means. Any ideas? " The Page number.Have you optimized tempdb? See "Concurrency enhancements for the tempdb database" at http://support.microsoft.com/kb/328551
Thanks for the link, I'm following the data file recommendation (4 datafiles of equal size and no autogrowth corresponding to 4 CPUs), but not the hotfix (we are already on a later version, 8.0.2187, that's SP4) and not the trace flag.
Should I implement the trace flag, T1118?
I think I'll give that a try.
I tried the trace flag, but it had no effect...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 31, 2008 at 6:30 pm
OK, here is the latest info. For the longest time, the lastwaittype is PAGELATCH_SH and waitresource is 13:1:6275751.
I have been able to find the table and index this resource corresponds to through this:
DBCC TRACEON (3604)
DBCC PAGE (13, 1, 6275751, 0)
DBCC TRACEOFF(3604)
GO
Output (partial):
PAGE HEADER:
------------
Page @0x72398000
----------------
m_pageId = (1:6275751) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 320720195 m_indexId = 10 m_prevPage = (1:6275750)
Find object name, index name:
select object_name(320720195)
select name from sysindexes
where id = 320720195 and indid=10
What's next? Rebuild this index?
Shoot myself?
I'm running out of ideas... :crazy:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 31, 2008 at 8:04 pm
In sysprocesses I see lastwaittype: PAGELATCH_SH and waitresource: 13:1:6396860.
A bit before the query completes execution I see lastwaittype: PAGEIOLATCH_SH and waitresource: 2:1:274.
I know the first number is the database id: 13 is the id of my user database and 2 is the id of tempdb.
Also the second number - 1 - is the sysobjects table in both databases.
A correction: the second number is the fileid not an object id.
I am trying to reproduce the poor performance and not having any success. I am assembling a simplier test case for you to run.
In the meantime, please check the autogrow specifications for tempdb.
"BUG: File growth value for the tempdb database is not persistent when changed from fixed increments to percentage" at http://support.microsoft.com/default.aspx?scid=kb;en-us;816939
SQL = Scarcely Qualifies as a Language
July 31, 2008 at 8:15 pm
Carl Federl (7/31/2008)
In sysprocesses I see lastwaittype: PAGELATCH_SH and waitresource: 13:1:6396860.
A bit before the query completes execution I see lastwaittype: PAGEIOLATCH_SH and waitresource: 2:1:274.
I know the first number is the database id: 13 is the id of my user database and 2 is the id of tempdb.
Also the second number - 1 - is the sysobjects table in both databases.
A correction: the second number is the fileid not an object id.
I am trying to reproduce the poor performance and not having any success. I am assembling a simplier test case for you to run.
In the meantime, please check the autogrow specifications for tempdb.
"BUG: File growth value for the tempdb database is not persistent when changed from fixed increments to percentage" at http://support.microsoft.com/default.aspx?scid=kb;en-us;816939
Thanks for trying...:)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 1, 2008 at 3:02 am
Are you running another transaction or query during the execution of the slow transaction? I mean is it the only query/transaction executed on the server without any other query/transaction execution on the run. If this is the only transaction that you run in the server then checks first for locks in the database before running the transaction. You should not see any locks on the tables involved. If you see them, then it must be the left over of not finished transactions (not committed and yet not rollback). Remove all the suspected locks first. If there are no locks and you still faced with the problem then most probably locks are not the problem.
Running multiple transactions may delay the execution of the query since locks may be held by other transactions, and therefore it needs to wait until the holding transaction releases the locks.
Ivan Budiono
August 1, 2008 at 5:02 am
i6004835 (8/1/2008)
Are you running another transaction or query during the execution of the slow transaction? I mean is it the only query/transaction executed on the server without any other query/transaction execution on the run. If this is the only transaction that you run in the server then checks first for locks in the database before running the transaction. You should not see any locks on the tables involved. If you see them, then it must be the left over of not finished transactions (not committed and yet not rollback). Remove all the suspected locks first. If there are no locks and you still faced with the problem then most probably locks are not the problem.Running multiple transactions may delay the execution of the query since locks may be held by other transactions, and therefore it needs to wait until the holding transaction releases the locks.
Thanks, I'll take a look
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply