tempdb / sizing storage

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Doug - 27 Mar 2008 15:57 GMT

If I split the tempdb mdb to a separate drive on our SAN, which I
understand is a good practice, how can I determine the amount of space to
allow on the SAN for this? We don't want to exceed the space of course, but
also wouldn't want to assign more space if it'll never be used. How would
you go about determining something like this?

Doug

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 27 Mar 2008 15:58 GMT

What is the largest you have ever seen tempdb grow? What is its current
size? Ideally, you would create multiple /identically-sized/ files on
multiple drives. It's hard to answer your question, other than to suggest
observing how much space it has needed, and guess how much more space you
might envision it will need.

> If I split the tempdb mdb to a separate drive on our SAN, which I
> understand is a good practice, how can I determine the amount of space to
[quoted text clipped - 4 lines]
>
> Doug

Reply to this Message

Bulent - 27 Mar 2008 17:05 GMT

On Mar 27, 8:58 am, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:

> What is the largest you have ever seen tempdb grow? What is its current
> size? Ideally, you would create multiple /identically-sized/ files on
[quoted text clipped - 12 lines]
>
> - Show quoted text -

I did just what Aaron said and I have watched tempdb on my system to
grow up to 1 GB at times during maintenance. Per performance best
practice then I created one tempdb file per core and size it to 1 GB.
So watch your tempdb and create at least one additional tempdb per
socket or even better per core.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 27 Mar 2008 17:06 GMT

So watch your tempdb and create at least one additional tempdb per
socket or even better per core.

Note that this doesn't really help at all if you put them all on the same
physical drive.

Reply to this Message

Linchi Shea - 27 Mar 2008 18:50 GMT

> Note that this doesn't really help at all if you put them all on the same
> physical drive.

Well, the main reason for using X number of tempdb data files per socket is
not really to address any disk storage performance problem per se. Rather,
it's to resolve a SQL Server internal contention issue on the shared tempdb
metadata structures (e.g. contention on the allocation pages). So if that
metadata contention is the problem, having a better storage system doesn't
help, but having multiple data files on the save drive still helps.

Linchi

> So watch your tempdb and create at least one additional tempdb per
> socket or even better per core.
>
> Note that this doesn't really help at all if you put them all on the same
> physical drive.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 27 Mar 2008 18:52 GMT

>> Note that this doesn't really help at all if you put them all on the same
>> physical drive.
[quoted text clipped - 7 lines]
> metadata contention is the problem, having a better storage system doesn't
> help, but having multiple data files on the save drive still helps.

True, though I haven't encountered that yet...

Reply to this Message