SQL Server maximum server memory setting and tempdb behaviour

Question

SQL Server maximum server memory setting and tempdb behaviour

cbr78rr

Newbie

Points: 6
More actions
November 4, 2011 at 3:33 am

#259541

Hello folks,
today I increased up to 20GB (from 12GB) "maximum server memory" on my sql server but unexpectedly tempdb became huge.
Can anybody explain me "maximum server memory" and tempdb size correlation please?
Thx to all for your help
Francesco

Viewing 5 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

There isn't a correlation. If tempDB became huge, some query or queries needed a lot of TempDB space.

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

I don’t think that those 2 things are related. I think that the TempDB’s growth had nothing to do with the increasing the memory of the server.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

cbr78rr Newbie Points: 6 More actions · Answer 3

GilaMonster (11/4/2011)
There isn't a correlation. If tempDB became huge, some query or queries needed a lot of TempDB space.

Effectively query plans comparison on same query are different.

But I cannot understand why tempdb explodes increasing sql server maximum memory.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

cbr78rr (11/4/2011)
GilaMonster (11/4/2011)
There isn't a correlation. If tempDB became huge, some query or queries needed a lot of TempDB space.
Effectively query plans comparison on same query are different.
But I cannot understand why tempdb explodes increasing sql server maximum memory.

What are you calling an "explosion" and "huge"?

--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)