All,

I am still trying to find some script or set of actions that will
prove the best practice of having 1 data file for each processor on a
multi processor box. Any or direction is greatly appreciated!

David Hay

What about this one?

Linchi

"David Hay" wrote:

>
> All,
>
> I am still trying to find some script or set of actions that will
> prove the best practice of having 1 data file for each processor on a
> multi processor box. Any or direction is greatly appreciated!
>
> David Hay
>

Am I missing something???

What about trying:
http://www.microsoft.com/technet/pro...ithtempdb.mspx
http://blogs.msdn.com/weix/archive/2...13/464907.aspx
http://msdn.microsoft.com/en-us/library/ms175527.aspx


John

"Linchi Shea" <LinchiShea@discussions.microsoft.com> wrote in message
news:4CDF809D-C540-4259-843E-6DAFEBFCAF29@microsoft.com...
> What about this one?
>
> Linchi
>
> "David Hay" wrote:
>
>>
>> All,
>>
>> I am still trying to find some script or set of actions that will
>> prove the best practice of having 1 data file for each processor on a
>> multi processor box. Any or direction is greatly appreciated!
>>
>> David Hay
>>

Sorry, I should have noted I am working with SQL2000.

Thanks!

Linchi,

There was no link...

Thanks!

David Hay

Most of what is in the link John supplied about Tempdb is still valid for
2000 even though the article was written for 2005. But here is the original
KB dealing with the tempdb contention issues.
http://support.microsoft.com/default...&Product=sql2k

Please note that adding multiple files and even the trace flags will not
improve performance if you don't have contention there to begin with.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"David Hay" <david.hay@gmail.com> wrote in message
news:a2195463-f040-46cc-b2c6-d3e76d0354f1@c58g2000hsc.googlegroups.com...
> Sorry, I should have noted I am working with SQL2000.
>
> Thanks!
>

Also note that you can actually get much WORSE performance if you have, say,
a single drive that you are putting multiple files on! the head thrashing
that results will be unkind to IO throughput.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:u7X0Vie5IHA.2260@TK2MSFTNGP03.phx.gbl...
> Most of what is in the link John supplied about Tempdb is still valid for
> 2000 even though the article was written for 2005. But here is the
> original KB dealing with the tempdb contention issues.
> http://support.microsoft.com/default...&Product=sql2k
>
> Please note that adding multiple files and even the trace flags will not
> improve performance if you don't have contention there to begin with.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "David Hay" <david.hay@gmail.com> wrote in message
> news:a2195463-f040-46cc-b2c6-d3e76d0354f1@c58g2000hsc.googlegroups.com...
>> Sorry, I should have noted I am working with SQL2000.
>>
>> Thanks!
>>
>

Andrew,

Thanks for the link. Based on what I am seeing I do have some
contention issues, but before I can implement a change, I need to
prove it in a dev/qa environment before I can push it to production no
matter what "best proactices" says, or other peoples observations. I
need to be able to prove it on one of our boxes. My issue is that our
Dev and QA environments do not have the same set up, much less the
volume that the production server has. That is why I am looking for
some kind of script that I can run against either configuration and
show those results as proof that the concept works.

Thanks again!

David Hay

Not the first time this happened, but I meant to include this;

http://www.microsoft.com/technet/pro...ithtempdb.mspx


"Linchi Shea" wrote:

> What about this one?
>
> Linchi
>
> "David Hay" wrote:
>
> >
> > All,
> >
> > I am still trying to find some script or set of actions that will
> > prove the best practice of having 1 data file for each processor on a
> > multi processor box. Any or direction is greatly appreciated!
> >
> > David Hay
> >

Hmmm... This is an interesting question. I'm not sure to be honest. If there
is head thrashing, one would think there'll be head thrashing even with a
single file???

Linchi

"TheSQLGuru" wrote:

> Also note that you can actually get much WORSE performance if you have, say,
> a single drive that you are putting multiple files on! the head thrashing
> that results will be unkind to IO throughput.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:u7X0Vie5IHA.2260@TK2MSFTNGP03.phx.gbl...
> > Most of what is in the link John supplied about Tempdb is still valid for
> > 2000 even though the article was written for 2005. But here is the
> > original KB dealing with the tempdb contention issues.
> > http://support.microsoft.com/default...&Product=sql2k
> >
> > Please note that adding multiple files and even the trace flags will not
> > improve performance if you don't have contention there to begin with.
> >
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> >
> >
> > "David Hay" <david.hay@gmail.com> wrote in message
> > news:a2195463-f040-46cc-b2c6-d3e76d0354f1@c58g2000hsc.googlegroups.com...
> >> Sorry, I should have noted I am working with SQL2000.
> >>
> >> Thanks!
> >>
> >
>
>
>

"David Hay" <david.hay@gmail.com> wrote in message
news:0274c162-df2f-4ded-ac0b-00121d94a843@a70g2000hsh.googlegroups.com...
> Andrew,
>
> Thanks for the link. Based on what I am seeing I do have some
> contention issues, but before I can implement a change, I need to
> prove it in a dev/qa environment before I can push it to production no
> matter what "best proactices" says, or other peoples observations. I
> need to be able to prove it on one of our boxes. My issue is that our
> Dev and QA environments do not have the same set up, much less the
> volume that the production server has. That is why I am looking for
> some kind of script that I can run against either configuration and
> show those results as proof that the concept works.
>
> Thanks again!
>
> David Hay

Hi

If you don't have something like LoadRunner or Rational Performance Tester
or if you Web Application Visual Studio Team System, you will be able to put
different loads on the system and monitor the changes. Of course over use of
tempdb by your application could actually be the issue rather than how they
system is configured. If you only want to prove the discs are slow then look
at SQLIOStress or the newer SQLIOSim. http://support.microsoft.com/kb/231619

John

If you don't have a test env that will stress the db similar to the prod
then it will be hard to prove that the changes will actually make a
difference. But if you can prove you have contention issues in tempdb then
that should be enough. Do you have pagelatch_UP waits? If you do that is a
very good sign you have contention and the additional files and or trace
flags should . In my experience with lots of contention this can make a
dramatic difference. There is an extremely low if any risk you will see any
decrease in performance even if this wasn't the issue.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"David Hay" <david.hay@gmail.com> wrote in message
news:0274c162-df2f-4ded-ac0b-00121d94a843@a70g2000hsh.googlegroups.com...
> Andrew,
>
> Thanks for the link. Based on what I am seeing I do have some
> contention issues, but before I can implement a change, I need to
> prove it in a dev/qa environment before I can push it to production no
> matter what "best proactices" says, or other peoples observations. I
> need to be able to prove it on one of our boxes. My issue is that our
> Dev and QA environments do not have the same set up, much less the
> volume that the production server has. That is why I am looking for
> some kind of script that I can run against either configuration and
> show those results as proof that the concept works.
>
> Thanks again!
>
> David Hay

On Mon, 14 Jul 2008 14:45:24 -0500, "TheSQLGuru"
<kgboles@earthlink.net> wrote:

>Also note that you can actually get much WORSE performance if you have, say,
>a single drive that you are putting multiple files on! the head thrashing
>that results will be unkind to IO throughput.

A very interesting point - not mentioned in any of the Microsoft white
papers I've seen! Let's see, do we benefit by reducing contention at
the cost of more head movement? I suppose you can find scenarios that
are better one way or the other.

J.

On Mon, 14 Jul 2008 13:08:00 -0700, Linchi Shea
<LinchiShea@discussions.microsoft.com> wrote:

>Hmmm... This is an interesting question. I'm not sure to be honest. If there
>is head thrashing, one would think there'll be head thrashing even with a
>single file???

Could be less thrashing, but more waiting, depending on scenario.

The danger is that more concurrency produces relatively more
thrashing!

J.

I had a client that created a 16 file tempdb on a 2 drive raid1 set.
Dropping back to (IIRC) a single file improved performance of their typical
workload between 10 and 15%. Note that they wrote a fairly large amount of
data to tempdb for work tables, etc, but they did NOT have large amounts of
create/drops (i.e. no SGAM issues). Also note that a conforming benchmark
was not done before/after - just timing metrics of report runs which clearly
could have been affected by other loads on server.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Linchi Shea" <LinchiShea@discussions.microsoft.com> wrote in message
news:C27840DB-BFCD-4F65-87FB-144496B27311@microsoft.com...
> Hmmm... This is an interesting question. I'm not sure to be honest. If
> there
> is head thrashing, one would think there'll be head thrashing even with a
> single file???
>
> Linchi
>
> "TheSQLGuru" wrote:
>
>> Also note that you can actually get much WORSE performance if you have,
>> say,
>> a single drive that you are putting multiple files on! the head
>> thrashing
>> that results will be unkind to IO throughput.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
>> news:u7X0Vie5IHA.2260@TK2MSFTNGP03.phx.gbl...
>> > Most of what is in the link John supplied about Tempdb is still valid
>> > for
>> > 2000 even though the article was written for 2005. But here is the
>> > original KB dealing with the tempdb contention issues.
>> > http://support.microsoft.com/default...&Product=sql2k
>> >
>> > Please note that adding multiple files and even the trace flags will
>> > not
>> > improve performance if you don't have contention there to begin with.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> > Solid Quality Mentors
>> >
>> >
>> > "David Hay" <david.hay@gmail.com> wrote in message
>> > news:a2195463-f040-46cc-b2c6-d3e76d0354f1@c58g2000hsc.googlegroups.com...
>> >> Sorry, I should have noted I am working with SQL2000.
>> >>
>> >> Thanks!
>> >>
>> >
>>
>>
>>

Thanks for all the input. There are some pieces that do extensive
create/drops in TempDB, then I have the business objects users who are
doing alot of sorting/grouping etc which also takes place in tempdb.
I do see the pagelatch_up waits Andrew mentioned. I might just have
to do my best to convince them to let me try it if I cannot replicate
the production volume/configuration. Once again, many thanks to all!

David Hay