SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 tempdb gone wild
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tcronines
Starting Member

USA
8 Posts

Posted - 06/05/2014 :  09:48:23  Show Profile  Reply with Quote
supporting 3rd party app, over weekend new patch put in. Performance took a dive, tempdb went from about 8 gig to stuck at 175 gig, any ideas where best place to look might be?

James K
Flowing Fount of Yak Knowledge

3633 Posts

Posted - 06/05/2014 :  12:17:24  Show Profile  Reply with Quote
quote:
Originally posted by tcronines

supporting 3rd party app, over weekend new patch put in. Performance took a dive, tempdb went from about 8 gig to stuck at 175 gig, any ideas where best place to look might be?

Not to sound flippant, but the third party software vendor should be called on the carpet for releasing dangerous software on the unsuspecting public.

This page might be very useful - there are a number of queries there that let you get some insight into what might be causing the problem. http://msdn.microsoft.com/en-us/library/ms176029.aspx

If their software and usage patterns have not be radically modified in the patch, ask the vendor if they have turned on RCSI on the database with this patch. Version store can sometimes take up a lot of space.

Edited by - James K on 06/05/2014 12:18:02
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36892 Posts

Posted - 06/05/2014 :  12:46:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
On top of what James said, run a trace/extended event session to capture the long-running queries.

If it is due to RCSI, you also need to check for transactions left open too long.


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tcronines
Starting Member

USA
8 Posts

Posted - 06/05/2014 :  16:34:53  Show Profile  Reply with Quote
may have found culprit sp which called another sp had a table variable created inside of it. Caused 111000 logical reads. Changed their code to use standard temp table insted of table variabel , went to 17 logical reads
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000