Multiple TempDB files for Multi_CPU

Hi all,
I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
have as many files for tempDB as I have CPU's - ie, eight.
I have a few questions about this:
1. I am assuming that these files are created in SSMS by right-clicking
tempdb and going to the Properties\Files tab.
--Should I add one tempdev_n and one templog_n for each CPU, or just the
tempdev file (the mdf)?
--Does it make sense to leave all of the new tempdev_n files in the
PRIMARY filegroup (same as Partition?), or should I create a new filegroup
for each tempdev_n instance?
2. Will performance actually have the potential to improve if all of these
tempdev_n files are on the same RAID 1 disc array? Does anyone actually
spread these files to dedicated discs per instance?
Thanks. These questions probably expose my experience level (rank amateur),
but that's why I am asking them. I appreciate any help.
JT
--
JohnHi
Increasing the number of data files will reduce the contention in SGAM and
PFS pages in tempdb. This is described in
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Also check out
http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/WorkingWithTempDB.doc
This is more likely to occur if your application uses an excessive number of
temporary table or other features that require tempdb.
You do not have to increase the number of log files. You can use management
studio or use the ALTER DATABASE command to increase the number of files.
Also make sure that the initial size of the tempdb files are large enough for
normal use so you avoid it having to grow.
John
"JT" wrote:
> Hi all,
> I have a clustered sql server 2005 64 bit on a SAN. The two nodes each
> have quad dual core XEON CPU's. TempDB is currently on a dedicated RAID 1
> array in the SAN (two 72GB 15K rpm discs). I have read that it is wise to
> have as many files for tempDB as I have CPU's - ie, eight.
> I have a few questions about this:
> 1. I am assuming that these files are created in SSMS by right-clicking
> tempdb and going to the Properties\Files tab.
> --Should I add one tempdev_n and one templog_n for each CPU, or just the
> tempdev file (the mdf)?
> --Does it make sense to leave all of the new tempdev_n files in the
> PRIMARY filegroup (same as Partition?), or should I create a new filegroup
> for each tempdev_n instance?
> 2. Will performance actually have the potential to improve if all of these
> tempdev_n files are on the same RAID 1 disc array? Does anyone actually
> spread these files to dedicated discs per instance?
> Thanks. These questions probably expose my experience level (rank amateur),
> but that's why I am asking them. I appreciate any help.
> JT
> --
> John|||Hello John,
I agree with john that it is necessary if you do have load that requres big
tempdb. Also, setting the database size to a large value is suggested.
For SQL Server 2005 it is recommended that you create tempdb files striped
across fast disks. Since you have raid1 disk, the performance improvement
may not as much as you expect though it should improve performance when the
problem is caused by tempdb. Generally, you may want to monitor the system
by using performon as described in the articles recommended by John and add
files when necessary. Also, you may want to configure trace flag 1118 to
eliminate mixed extent allocations. Please see the following article for
more details:
328551 FIX: Concurrency enhancements for the tempdb database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
If you have further questions or concerns on the issue, please feel free to
let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Peter and John. One follow-on question...
Given current resource restrictions, I have two FC disks to devote to the
tempdb for this SQL instance on the SAN. For reliability sake, I choose to
go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
array). Would it make sense to go with RAID 0? What would happen if one of
the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
Thanks.
--
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> I agree with john that it is necessary if you do have load that requres big
> tempdb. Also, setting the database size to a large value is suggested.
> For SQL Server 2005 it is recommended that you create tempdb files striped
> across fast disks. Since you have raid1 disk, the performance improvement
> may not as much as you expect though it should improve performance when the
> problem is caused by tempdb. Generally, you may want to monitor the system
> by using performon as described in the articles recommended by John and add
> files when necessary. Also, you may want to configure trace flag 1118 to
> eliminate mixed extent allocations. Please see the following article for
> more details:
> 328551 FIX: Concurrency enhancements for the tempdb database
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> If you have further questions or concerns on the issue, please feel free to
> let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi
With Raid 0 you would have some down, while you either added a new disc or
reconfigured tempdb (I think SQL Server would not restart until you did!) .
The database should roll back any usage of tempdb when the system stopped so
your database should remain consistent but you would need to reapply the
transactions that were in progress.
John
"JT" wrote:
> Thank you Peter and John. One follow-on question...
> Given current resource restrictions, I have two FC disks to devote to the
> tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> array). Would it make sense to go with RAID 0? What would happen if one of
> the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> Thanks.
> --
> John
>
> "Peter Yang [MSFT]" wrote:
> > Hello John,
> >
> > I agree with john that it is necessary if you do have load that requres big
> > tempdb. Also, setting the database size to a large value is suggested.
> >
> > For SQL Server 2005 it is recommended that you create tempdb files striped
> > across fast disks. Since you have raid1 disk, the performance improvement
> > may not as much as you expect though it should improve performance when the
> > problem is caused by tempdb. Generally, you may want to monitor the system
> > by using performon as described in the articles recommended by John and add
> > files when necessary. Also, you may want to configure trace flag 1118 to
> > eliminate mixed extent allocations. Please see the following article for
> > more details:
> >
> > 328551 FIX: Concurrency enhancements for the tempdb database
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> >
> > If you have further questions or concerns on the issue, please feel free to
> > let's know. Thank you.
> >
> > Best Regards,
> >
> > Peter Yang
> > MCSE2000/2003, MCSA, MCDBA
> > Microsoft Online Community Support
> > ==================================================> > Get notification to my posts through email? Please refer to
> > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > ications
> > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > where an initial response from the community or a Microsoft Support
> > Engineer within 1 business day is acceptable. Please note that each follow
> > up response may take approximately 2 business days as the support
> > professional working with you may need further investigation to reach the
> > most efficient resolution. The offering is not appropriate for situations
> > that require urgent, real-time or phone-based interactions or complex
> > project analysis and dump analysis issues. Issues of this nature are best
> > handled working with a dedicated Microsoft Support Engineer by contacting
> > Microsoft Customer Support Services (CSS) at
> > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > ==================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hello John,
As John mentioned, raid 0 disk failure of tempdb shall not bring data
consistency issue. However, transactions related to tempdb will roll back
and you shall reapply these transactions.
Usually it is suggested that you use RAID 1 or RAID 1+0, not RAID 0 for
tempdb, unless you are prepared to lose SQL Server access should either
drive fail in a RAID 0 striped array.
Please see:
http://www.microsoft.com/sql/prodinfo/previousversions/scalabilityfaq.mspx
and the section on tempdb
http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx
917047 Microsoft SQL Server I/O subsystem requirements for the tempdb
database
http://support.microsoft.com/default.aspx?scid=kb;EN-US;917047
If you have further questions, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"any usage of tempdb" does not read well! Try substituting "any uncommitted
transactions in progress"
John
"John Bell" wrote:
> Hi
> With Raid 0 you would have some down, while you either added a new disc or
> reconfigured tempdb (I think SQL Server would not restart until you did!) .
> The database should roll back any usage of tempdb when the system stopped so
> your database should remain consistent but you would need to reapply the
> transactions that were in progress.
> John
> "JT" wrote:
> > Thank you Peter and John. One follow-on question...
> > Given current resource restrictions, I have two FC disks to devote to the
> > tempdb for this SQL instance on the SAN. For reliability sake, I choose to
> > go with RAID 1 config (although the other instance mdf's are on a RAID 1+0
> > array). Would it make sense to go with RAID 0? What would happen if one of
> > the RAID 0 discs supporting tempdb (and ONLY tempdb) blew up?
> >
> > Thanks.
> > --
> > John
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> > > Hello John,
> > >
> > > I agree with john that it is necessary if you do have load that requres big
> > > tempdb. Also, setting the database size to a large value is suggested.
> > >
> > > For SQL Server 2005 it is recommended that you create tempdb files striped
> > > across fast disks. Since you have raid1 disk, the performance improvement
> > > may not as much as you expect though it should improve performance when the
> > > problem is caused by tempdb. Generally, you may want to monitor the system
> > > by using performon as described in the articles recommended by John and add
> > > files when necessary. Also, you may want to configure trace flag 1118 to
> > > eliminate mixed extent allocations. Please see the following article for
> > > more details:
> > >
> > > 328551 FIX: Concurrency enhancements for the tempdb database
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551
> > >
> > > If you have further questions or concerns on the issue, please feel free to
> > > let's know. Thank you.
> > >
> > > Best Regards,
> > >
> > > Peter Yang
> > > MCSE2000/2003, MCSA, MCDBA
> > > Microsoft Online Community Support
> > > ==================================================> > > Get notification to my posts through email? Please refer to
> > > http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> > > ications
> > > <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> > > Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> > > where an initial response from the community or a Microsoft Support
> > > Engineer within 1 business day is acceptable. Please note that each follow
> > > up response may take approximately 2 business days as the support
> > > professional working with you may need further investigation to reach the
> > > most efficient resolution. The offering is not appropriate for situations
> > > that require urgent, real-time or phone-based interactions or complex
> > > project analysis and dump analysis issues. Issues of this nature are best
> > > handled working with a dedicated Microsoft Support Engineer by contacting
> > > Microsoft Customer Support Services (CSS) at
> > > <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> > > ==================================================> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >|||Hello John,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================