Improve tempdb Performance (SQL Server BPA)

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 '
LVL 1
Audi08Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rob_farleyCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.