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

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.




"Doug" <Doug@discussions.microsoft.com> wrote in message
news:4ED804AE-1D9A-4C7F-9AE0-571ABA8A0C36@microsoft.com...
> 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

On Mar 27, 8:58am, "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
> 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.
>
> "Doug" <D...@discussions.microsoft.com> wrote in message
>
> news:4ED804AE-1D9A-4C7F-9AE0-571ABA8A0C36@microsoft.com...
>
>
>
> > 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- Hide quoted text -
>
> - 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.

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

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

> Note that this doesn't really 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
, but having multiple data files on the save drive still s.

Linchi

"Aaron Bertrand [SQL Server MVP]" wrote:

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

>> Note that this doesn't really 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
> , but having multiple data files on the save drive still s.

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