August 1, 2008 at 5:17 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.
There are no locks in a WAIT status for the SPID of the query. Status is GRANT for all associated locks of that SPID.
However, another SPID is blocked by the query SPID:
Login: NT AUTHORITY\SYSTEM
dbname: tempdb
program name: SQLDMO_1
What does that do?
__________________________________________________________________________________
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 5:31 am
Marios Philippopoulos (8/1/2008)
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.
There are no locks in a WAIT status for the SPID of the query. Status is GRANT for all associated locks of that SPID.
However, another SPID is blocked by the query SPID:
Login: NT AUTHORITY\SYSTEM
dbname: tempdb
program name: SQLDMO_1
What does that do?
Here is the info from sp_lock on the waiting lock (NT AUTHORITY\SYSTEM):
spid dbid objid indid type resource mode status
78221KEY(110080472a72) SWAIT
__________________________________________________________________________________
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 7:56 pm
I'm not quite sure also about SQLDMO_1. But it seems from other posts in the internet to be a system process. Some say that It is linked with Enterprise Manager. But in my computer running 2000 version the Enterprise Manager is shown as SQL EM not SQLDMO. SQLDMO is COM objects to manipulate SQL Server (I used that when still using Visual Basic 6). Try to close all the client tools (Enterprise Manager, Query Analyzer, etc) as well as any other application that uses COM to connect to SQL Server. Then run your transaction via .NET code or your application.
Some other people out there also has the problem with SQLDMO_1 as well.
You can googling for SQLDMO_1.
Hope that you will find the solution.
Ivan Budiono
August 2, 2008 at 5:18 am
i6004835 (8/1/2008)
I'm not quite sure also about SQLDMO_1. But it seems from other posts in the internet to be a system process. Some say that It is linked with Enterprise Manager. But in my computer running 2000 version the Enterprise Manager is shown as SQL EM not SQLDMO. SQLDMO is COM objects to manipulate SQL Server (I used that when still using Visual Basic 6). Try to close all the client tools (Enterprise Manager, Query Analyzer, etc) as well as any other application that uses COM to connect to SQL Server. Then run your transaction via .NET code or your application.Some other people out there also has the problem with SQLDMO_1 as well.
You can googling for SQLDMO_1.
Hope that you will find the solution.
Thank you, I've opened a case with MS, and will post any developments here. It's likely to be a tempdb issue, perhaps something to do with the LUN hosting the tempdb files. But the SQLDMO piece is definitely worth investigating further as well.
__________________________________________________________________________________
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 25, 2008 at 2:28 pm
Well, after a long while, I have an answer to this, thanks to Microsoft.
To remind everyone of the issue, I had the following query (just the scheleton shown for simplicity):
CREATE TABLE #temp1...
CREATE TABLE #temp2...
CREATE TABLE #temp3...
INSERT INTO #temp1 SELECT ...
INSERT INTO #temp2 SELECT ...
INSERT INTO #temp3
SELECT ...
--multi-join query here involving #temp1, #temp2 and other permanent tables is user db
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;
Run as is, the query took about 20 sec.
When wrapped inside a BEGIN TRAN...COMMIT TRAN statement, it completed after about 20 min!
The main reason why the query behaves so differently when within and outside an explicit transaction is because of statistics. When the query is run without an explicit transaction, the Statistics Manager, STATMAN, builds statistics for 4 columns in table #temp1 and 2 columns in table #temp2. Stats is not being built for these columns when the explicit transaction BEGIN TRAN...COMMIT TRAN is in place. These stats modifications are the cause of the vastly different behavior of the query inside and outside the transaction.
If statistics need to be created/updated within an explicit transaction, and the AutoCreate, AutoUpdate-Statistics options are enabled for the database in question - as they are by default for tempdb - STATMAN will simply not run!
To get around the issue, Microsoft recommended that I modify the query into individual transactions, as follows:
begin tran
CREATE TABLE #temp1...
CREATE TABLE #temp2...
CREATE TABLE #temp3...
COMMIT
update statistics #temp1;
begin tran
INSERT INTO #temp1 SELECT ...
COMMIT
update statistics #temp2;
begin tran
INSERT INTO #temp2 SELECT ...
COMMIT
update statistics #temp3;
begin tran
INSERT INTO #temp3
SELECT ...
--multi-join query here involving #temp1, #temp2 and other permanent tables is user db
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;
COMMIT
__________________________________________________________________________________
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 25, 2008 at 2:43 pm
Why not explicitly run statistics on the tables before you start your transaction?
[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]
August 25, 2008 at 3:10 pm
rbarryyoung (8/25/2008)
Why not explicitly run statistics on the tables before you start your transaction?
For some reason, the following query also hangs. When I abort it, I get this error 3 times, one for each UPDATE STATS statement:
Msg 226, Level 16, State 2, Line 30
UPDATE STATISTICS statement not allowed within multi-statement transaction.
Here is the query:
CREATE TABLE #temp1 ...
CREATE TABLE #temp2 ...
CREATE TABLE #temp3 ...
UPDATE STATISTICS #temp1;
UPDATE STATISTICS #temp2;
UPDATE STATISTICS #temp3;
BEGIN TRAN
INSERT INTO #temp1 SELECT ...
INSERT INTO #temp2 SELECT ...
INSERT INTO #temp3 SELECT ...
DROP TABLE #temp1
DROP TABLE #temp2
DROP TABLE #temp3
COMMIT
__________________________________________________________________________________
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 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply