Solved

Improve tempdb Performance (SQL Server BPA)

Posted on 2009-07-01
3
573 Views
Last Modified: 2012-05-07
this is bpa recommendation :
 If the previous steps do not significantly reduce the allocation  contention and the contention is on SGAM pages, implement trace flag  -T1118. Under this trace flag, SQL Server allocates full extents to each  database object, thereby eliminating the contention on SGAM pages. Note
 that this trace flag affects every database on the instance of SQL
 Server.

 For information about how to determine whether the allocation contention
 is on SGAM pages, see "Monitoring contention caused by DML operations" in
 Working with tempdb in SQL Server 2005. To address immediate problems,
 use trace flag -T1118; however, we recommend that you consider
 implementing longer-term, scalable, solutions. For example, you might have
 to modify queries to limit the use of table variables, temp tables, or
 cursor

how can we do this , or there is better ways to handle this. if so please though your ideas.

thanks '
0
Comment
Question by:Audi08
3 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 250 total points
ID: 24754464
check
Troubleshooting Performance Problems in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc966540.aspx
--
Tempdb Configuration Best Practices in SQL Server
http://www.mssqltips.com/tip.asp?tip=1432
--
Optimizing tempdb Performance
http://msdn.microsoft.com/en-us/library/ms175527(SQL.90).aspx 
0
 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 250 total points
ID: 24760890
At the moment my favourite way of optimising tempdb is to put it on a Solid State Disk. With much faster reads & writes, it helps a lot.

And if the drive fails, remember that tempdb isn't used as a permanent data store anyway. You just restart the service and tempdb gets wiped anyway (but heck - get two in RAID1 and you're basically as safe as you need).

Rob
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now