Erratic SP Performance

Question

Erratic SP Performance

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Dave Mason SSCrazy Points: 2751 More actions · Answer 1

Jeff Moden (2/28/2012)
With that thought in mind and taking a shot in the dark, what are the initial size and growth settings of TempDB?

tempdev: initial size 8MB, autogrowth by 10% (unrestricted growth).

templog: initial size 1MB, autogrowth by 10% (unrestricted growth).

These are the defaults IIRC.

Update:

I found the tempdb files to have grown to the following:

tempdev: 480.23MB

templog: 82.31MB

Dave MasonSeminole County, FL

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Ouch.

So tempDB is reaching almost 500MB in 1MB chunks (well, to start with). Not pretty. Set the default size of TempDB to something in the general vecinity of the size you found it at. I'd probably go 500MB, 100MB

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Mason SSCrazy Points: 2751 More actions · Answer 3

I ended up opening a support ticket with Microsoft. During initial correspondence with the MS rep, it came to light that SP1 was not installed on the server (Win Server 2008 R2). After installing the SP, performance has been quite good. 4 - 5 days afterwards, I rebooted the server again. Hopefully, performance will remain good...

UPDATE:

Just to clarify, I installed SP1 for the OS. (The Sql 2008 R2 instance already had SP1 installed.)

Dave MasonSeminole County, FL

Brian.cs Default port Points: 1407 More actions · Answer 4

There's 5 CU's to SP1 already, something else to look into. We've been debating whether to (and when to) start moving to the latest CU.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Dave Mason (3/7/2012)
I ended up opening a support ticket with Microsoft. During initial correspondence with the MS rep, it came to light that SP1 was not installed on the server (Win Server 2008 R2). After installing the SP, performance has been quite good. 4 - 5 days afterwards, I rebooted the server again. Hopefully, performance will remain good...
UPDATE:
Just to clarify, I installed SP1 for the OS. (The Sql 2008 R2 instance already had SP1 installed.)

That's good but you still need to fix the growth and initial size settings of TempDB. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Mason SSCrazy Points: 2751 More actions · Answer 6

Jeff Moden (3/8/2012)
That's good but you still need to fix the growth and initial size settings of TempDB. 😉

Yes, I fixed that on my "problem" server, and on two other production servers as well.

Thanks, Jeff and Gail!

Dave MasonSeminole County, FL