July 28, 2008 at 2:28 pm
I have 2 insert statements inserting records into 2 temp tables.
When the 2 statements are placed inside a transaction - BEGIN TRAN...ROLLBACK TRAN - the script hangs.
However, when the BEGIN TRAN...ROLLBACK TRAN wrapper is removed, the SQL script completes in a few sec.
Anyone know what could be the cause of this?
__________________________________________________________________________________
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 28, 2008 at 2:34 pm
Post code?
Could it be blocking? If you query sysprocesses, what does it show for the 'hung' query? (specifically last wait type and blocked by)
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
July 28, 2008 at 2:48 pm
Also:
We appear to be under phys. memory pressure on the server: RAM utilization is 14 GB (out of a total of 16 GB).
The instance is configured with AWE (Lock working set at 13.2 GB).
__________________________________________________________________________________
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 28, 2008 at 2:57 pm
I'm also seeing a lot of SP:CacheRemove events in Profiler. Is the cache being flushed because of memory pressure?
__________________________________________________________________________________
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 28, 2008 at 3:17 pm
Perfmon counter "Buffer Manager\Page life expectancy" is below 200. I think that indicates memory pressure 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]
July 28, 2008 at 3:22 pm
Will take a look tomorrow (if no one else solves it in the meantime, that is). Is past bed time here.
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
July 28, 2008 at 3:23 pm
GilaMonster (7/28/2008)
Will take a look tomorrow (if no one else solves it in the meantime, that is). Is past bed time here.
No worries, it's much appreciated.
__________________________________________________________________________________
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 28, 2008 at 6:02 pm
Where's the COMMIT?
[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 28, 2008 at 7:06 pm
I changed my post above to include a COMMIT TRAN (had a ROLLBACK TRAN earlier).
Same outcome:
With the transaction, the query completes in about 15 min.
Without the transaction (with BEGIN TRAN... COMMIT TRAN commented out), query completes in about 1 min.
What could be causing such a significant difference in performance?
__________________________________________________________________________________
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 28, 2008 at 10:19 pm
When serveral big inserts are combined in one transaction, there is a "hot" demand for the log space. Without "begin tran / commit tran", as soon as one insert is done, the log is released inside tempdb.
When you run the script (with begin tran/commit trans), open another session, and use
dbcc sqlperf(logspace)
to monitor the log space usage for tempdb.
HTH,
Jeff Yao
July 29, 2008 at 4:15 am
jeffrey yao (7/28/2008)
When serveral big inserts are combined in one transaction, there is a "hot" demand for the log space. Without "begin tran / commit tran", as soon as one insert is done, the log is released inside tempdb.When you run the script (with begin tran/commit trans), open another session, and use
dbcc sqlperf(logspace)
to monitor the log space usage for tempdb.
HTH,
Jeff Yao
Thank you! I'll try that.
__________________________________________________________________________________
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 29, 2008 at 4:33 am
Using the DBCC command, I find that the log space utilization for tempdb is about 43%, but, what do I do next?
__________________________________________________________________________________
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 29, 2008 at 11:57 pm
Check the log space usage before and during the exectuion (wiht begin tran / commit tran), if you find the log space usage is increased by your insert sqls, I think you should consider optimize your tempdb, which you can find tons of articles by doing a quick google.
July 31, 2008 at 11:12 am
Both the data and log files of tempdb are on the same drive location.
Could this be caused by a slow drive?
__________________________________________________________________________________
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:37 pm
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.
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply