I recently put in a change to tempdb splitting it into 8 data files,
one per each processor over our SAN as well as putting in the -T1118
startup parameter for page allocation within TempDB on our main
datawarehouse server. Since then all runs well except 1 job. This
job uses tempdb heavily, and normally used to take 30-40 minutes to
run. Since the change, it is now running over 11 hours! We are
rolling back the changes tonight, and the job will run this weekend.
I will let everyone know the out come. I have verified that nothing
else has changed other than the tempdb modifications.

Has anyone seen degredation when using the "multiple tempdb files"
practice before?

Without posting the exact code, it's fairly straight forward.

Table1 has roughly 3 million records, and #temp_Work1 had about 1
million. Only two of the functions it calls reach out to other
tables, and those are under 100 records each.


Select
a.fields,
b.fields,
c.functions
into
#temp_Work2
from
Table1 a
left join
#temp_Work1 b on a.skunbr=b.sku

Thanks!

David Hay

Well, do you have 8 physical processors, or are you using dual- or quad-core
or hyper-threading? The typical wisdom is to split it out so there is a
tempdb file for each PHYSICAL processor, not virtual.


On 9/9/08 2:32 PM, in article
a29babe6-7afd-46db-853c-7489ffe56b8d...oglegroups.com, "David
Hay" <david.hay@gmail.com> wrote:

>
> I recently put in a change to tempdb splitting it into 8 data files,
> one per each processor over our SAN as well as putting in the -T1118
> startup parameter for page allocation within TempDB on our main
> datawarehouse server. Since then all runs well except 1 job. This
> job uses tempdb heavily, and normally used to take 30-40 minutes to
> run. Since the change, it is now running over 11 hours! We are
> rolling back the changes tonight, and the job will run this weekend.
> I will let everyone know the out come. I have verified that nothing
> else has changed other than the tempdb modifications.
>
> Has anyone seen degredation when using the "multiple tempdb files"
> practice before?
>
> Without posting the exact code, it's fairly straight forward.
>
> Table1 has roughly 3 million records, and #temp_Work1 had about 1
> million. Only two of the functions it calls reach out to other
> tables, and those are under 100 records each.
>
>
> Select
> a.fields,
> b.fields,
> c.functions
> into
> #temp_Work2
> from
> Table1 a
> left join
> #temp_Work1 b on a.skunbr=b.sku
>
> Thanks!
>
> David Hay

Aaron,

Thanks, and it has 4 dual cores, and not set up for hyper threading,
so 4 data files should be my max.

Can you tell me why typical wisdom conflicts with the KB article.

http://support.microsoft.com/kb/328551

--Quote
The optimal number of tempdb data files depends on the degree of
contention seen in tempdb. As a starting point, you can configure the
tempdb to be at least equal to the number of processors assigned for
SQL Server. For higher end systems (for example, 16 or 32 proc), the
starting number could be 10. If the contention is not reduced, you may
have to increase the number of data files more.

Note A dual-core processor is considered to be two processors.
--

Thanks!

David Hay

I don't believe that the I/O operations will scale as well on virtual
processors, as the KB article seems to think... In other words, if you
already have a bottleneck on I/O operations to tempdb, trying to write to 8
files with 4 "real" processors might just make matters worse. Every
situation is different... it's just something you'll have to test I guess.
My caution is to not always believe that every single word in a KB article
is the gospel. <shrug>


On 9/9/08 4:07 PM, in article
ac8887eb-3563-44c0-b52a-d5ff8382e150...oglegroups.com, "David
Hay" <david.hay@gmail.com> wrote:

> Aaron,
>
> Thanks, and it has 4 dual cores, and not set up for hyper threading,
> so 4 data files should be my max.
>
> Can you tell me why typical wisdom conflicts with the KB article.
>
> http://support.microsoft.com/kb/328551
>
> --Quote
> The optimal number of tempdb data files depends on the degree of
> contention seen in tempdb. As a starting point, you can configure the
> tempdb to be at least equal to the number of processors assigned for
> SQL Server. For higher end systems (for example, 16 or 32 proc), the
> starting number could be 10. If the contention is not reduced, you may
> have to increase the number of data files more.
>
> Note A dual-core processor is considered to be two processors.
> --
>
> Thanks!
>
> David Hay
>
>
>
>
>

On Sep 9, 10:03pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> I don't believe that the I/O operations will scale as well on virtual
> processors, as the KB article seems to think... In other words, if you
> already have a bottleneck on I/O operations to tempdb, trying to write to8
> files with 4 "real" processors might just make matters worse. Every
> situation is different... it's just something you'll have to test I guess..
> My caution is to not always believe that every single word in a KB article
> is the gospel. <shrug>
>
> On 9/9/08 4:07 PM, in article
> ac8887eb-3563-44c0-b52a-d5ff8382e...@v16g2000prc.googlegroups.com, "David
>
>
>
> Hay" <david....@gmail.com> wrote:
> > Aaron,
>
> > Thanks, and it has 4 dual cores, and not set up for hyper threading,
> > so 4 data files should be my max.
>
> > Can you tell me why typical wisdom conflicts with the KB article.
>
> >http://support.microsoft.com/kb/328551
>
> > --Quote
> > The optimal number of tempdb data files depends on the degree of
> > contention seen in tempdb. As a starting point, you can configure the
> > tempdb to be at least equal to the number of processors assigned for
> > SQL Server. For higher end systems (for example, 16 or 32 proc), the
> > starting number could be 10. If the contention is not reduced, you may
> > have to increase the number of data files more.
>
> > Note A dual-core processor is considered to be two processors.
> > --
>
> > Thanks!
>
> > David Hay- Hide quoted text -
>
> - Show quoted text -

Hi David,

You should have TempDB files equal to 1/4 to 1/2 the number of cores
if you're on SQL 2005 or 1 per core if your on SQL 2000. So that's 4
or 8 files really. You won't get any degredation in performance from
having multiple files as they're used in a round robin not as a
stripe. Make sure that all the files are the same size and there's
enough room that you don't need to autogrow.

-T1118 disables mixed extents for the whole instance to remove
contention on the SGAM page (2:1:3) and shouldn't be needed in 2005.

If you're using 2005 and any sort of loop you should also look at temp
object caching to see if that improves things.

Regards,

Christian

Christian Bolton - MCAatabase, MCM:SQL Server 2008
Database Architect - Coeo Ltd
http://coeo.com
http://sqlblogcasts.com/blogs/christian

Hi Christian,

If I was told the server has 2 CPUs, quad-core, how many files that I can
split, 2 or 8? I'm sorry I'm very confused.

Thanks,
Sarah

"Christian" wrote:

> On Sep 9, 10:03 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten....@dnartreb.noraa> wrote:
> > I don't believe that the I/O operations will scale as well on virtual
> > processors, as the KB article seems to think... In other words, if you
> > already have a bottleneck on I/O operations to tempdb, trying to write to 8
> > files with 4 "real" processors might just make matters worse. Every
> > situation is different... it's just something you'll have to test I guess..
> > My caution is to not always believe that every single word in a KB article
> > is the gospel. <shrug>
> >
> > On 9/9/08 4:07 PM, in article
> > ac8887eb-3563-44c0-b52a-d5ff8382e...@v16g2000prc.googlegroups.com, "David
> >
> >
> >
> > Hay" <david....@gmail.com> wrote:
> > > Aaron,
> >
> > > Thanks, and it has 4 dual cores, and not set up for hyper threading,
> > > so 4 data files should be my max.
> >
> > > Can you tell me why typical wisdom conflicts with the KB article.
> >
> > >http://support.microsoft.com/kb/328551
> >
> > > --Quote
> > > The optimal number of tempdb data files depends on the degree of
> > > contention seen in tempdb. As a starting point, you can configure the
> > > tempdb to be at least equal to the number of processors assigned for
> > > SQL Server. For higher end systems (for example, 16 or 32 proc), the
> > > starting number could be 10. If the contention is not reduced, you may
> > > have to increase the number of data files more.
> >
> > > Note A dual-core processor is considered to be two processors.
> > > --
> >
> > > Thanks!
> >
> > > David Hay- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi David,
>
> You should have TempDB files equal to 1/4 to 1/2 the number of cores
> if you're on SQL 2005 or 1 per core if your on SQL 2000. So that's 4
> or 8 files really. You won't get any degredation in performance from
> having multiple files as they're used in a round robin not as a
> stripe. Make sure that all the files are the same size and there's
> enough room that you don't need to autogrow.
>
> -T1118 disables mixed extents for the whole instance to remove
> contention on the SGAM page (2:1:3) and shouldn't be needed in 2005.
>
> If you're using 2005 and any sort of loop you should also look at temp
> object caching to see if that improves things.
>
> Regards,
>
> Christian
>
> Christian Bolton - MCAatabase, MCM:SQL Server 2008
> Database Architect - Coeo Ltd
> http://coeo.com
> http://sqlblogcasts.com/blogs/christian
>
>

On Sep 10, 2:57pm, sg <s...@discussions.microsoft.com> wrote:
> Hi Christian,
>
> If I was told the server has 2 CPUs, quad-core, how many files that I can
> split, 2 or 8? I'm sorry I'm very confused.
>
> Thanks,
> Sarah
>
>
>
> "Christian" wrote:
> > On Sep 9, 10:03 pm, "Aaron Bertrand [SQL Server MVP]"
> > <ten....@dnartreb.noraa> wrote:
> > > I don't believe that the I/O operations will scale as well on virtual
> > > processors, as the KB article seems to think... In other words, if you
> > > already have a bottleneck on I/O operations to tempdb, trying to write to 8
> > > files with 4 "real" processors might just make matters worse. Every
> > > situation is different... it's just something you'll have to test I guess..
> > > My caution is to not always believe that every single word in a KB article
> > > is the gospel. <shrug>
>
> > > On 9/9/08 4:07 PM, in article
> > > ac8887eb-3563-44c0-b52a-d5ff8382e...@v16g2000prc.googlegroups.com, "David
>
> > > Hay" <david....@gmail.com> wrote:
> > > > Aaron,
>
> > > > Thanks, and it has 4 dual cores, and not set up for hyper threading,
> > > > so 4 data files should be my max.
>
> > > > Can you tell me why typical wisdom conflicts with the KB article.
>
> > > >http://support.microsoft.com/kb/328551
>
> > > > --Quote
> > > > The optimal number of tempdb data files depends on the degree of
> > > > contention seen in tempdb. As a starting point, you can configure the
> > > > tempdb to be at least equal to the number of processors assigned for
> > > > SQL Server. For higher end systems (for example, 16 or 32 proc), the
> > > > starting number could be 10. If the contention is not reduced, you may
> > > > have to increase the number of data files more.
>
> > > > Note A dual-core processor is considered to be two processors.
> > > > --
>
> > > > Thanks!
>
> > > > David Hay- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi David,
>
> > You should have TempDB files equal to 1/4 to 1/2 the number of cores
> > if you're on SQL 2005 or 1 per core if your on SQL 2000. So that's 4
> > or 8 files really. You won't get any degredation in performance from
> > having multiple files as they're used in a round robin not as a
> > stripe. Make sure that all the files are the same size and there's
> > enough room that you don't need to autogrow.
>
> > -T1118 disables mixed extents for the whole instance to remove
> > contention on the SGAM page (2:1:3) and shouldn't be needed in 2005.
>
> > If you're using 2005 and any sort of loop you should also look at temp
> > object caching to see if that improves things.
>
> > Regards,
>
> > Christian
>
> > Christian Bolton - MCAatabase, MCM:SQL Server 2008
> > Database Architect - Coeo Ltd
> >http://coeo.com
> >http://sqlblogcasts.com/blogs/christian- Hide quoted text -
>
> - Show quoted text -

Hi Sarah,

2 quad-core CPU's will give you 8 cores so configure 8 tempdb data
files if you're using SQL 2000 or 4 if you're using SQL 2005. Don't
forget to make sure they're all the same size!

Regards,

Christian

Christian Bolton - MCAatabase, MCM:SQL Server 2008
Database Architect - Coeo Ltd
http://coeo.com
http://sqlblogcasts.com/blogs/christian