We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Improve tempdb Performance (SQL Server BPA)

Medium Priority
661 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 '
Comment
Watch Question

SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob FarleyConsultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.