Solved

Improve tempdb Performance (SQL Server BPA)

Posted on 2009-07-01
3
578 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

734 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