TempDB Settings

Question

TempDB Settings

Mani-584606

SSChampion

Points: 11788
More actions
March 29, 2010 at 1:09 am

#139566

Hi,
We have SQL Server 2005 EE x64 with SP3 and the storage is on SAN. We have 2 quad core CPU's i.e 8 processors & 16 GB RAM.
We have a separate drive 'T' for TempDB where it's mdf & ldf files are placed and I set the data file size to 5 Gb & log file size to 1 GB
I was suggested by a DBA that "Tempdb data and log drives (single drive if SAN, dual if RAID)"
If we have SAN, keeping the TempDB's mdf & ldf files on single drive .If we do not have SAN then keep the mdf & ldf on different drives
Is that right that keeping the TempDB's mdf & ldf files on single drive If we have SAN?
please advcie

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

If your SQL Server’s tempdb database is heavily used by your application(s),consider locating it on an array of its own (such as RAID 1 or RAID 10). This will allow disk I/O to be more evenly

distributed, reducing disk I/O contentionissues, and speeding up SQL Server’s overall performance.

Paul White SSC Guru Points: 150467 More actions · Answer 2

Mani-584606 (3/29/2010)
Is that right that keeping the TempDB's mdf & ldf files on single drive If we have SAN?

Generally, tempdb is not happiest on the SAN at all. Direct-attached storage is often a better solution. If your SQL Server is clustered, however, you currently have no choice. See https://connect.microsoft.com/SQLServer/feedback/details/532759/support-local-disk-location-for-tempdb-in-failover-cluster-installation

Given that it is on the SAN, how much difference it makes keeping both the data and log files on the same presented drive, depends entirely on your SAN's capabilities, and how it is configured. Test the latency and performance using SQLIOSim in both configurations.

There is some very good advice from the SQL Server Customer Advisory Team:

http://blogs.msdn.com/sqlcat/archive/2005/10/11/479887.aspx

http://blogs.msdn.com/sqlcat/archive/2005/11/21/495440.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mani-584606 SSChampion Points: 11788 More actions · Answer 3

thank you,

If your SQL Server’s tempdb database is heavily used by your application(s)

How to know whether tempDB is heavily used by the application or not? where to check this?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Mani-584606 (3/29/2010)
How to know whether tempDB is heavily used by the application or not? where to check this?

You can refer this link

Sys.dm_exec_requests DMVs to find the currently active requests, their associated TSQL statement, and the corresponding query plan that is allocating most space resources in tempdb. I grab below sql from this.

SELECT t1.session_id, t1.request_id, t1.task_alloc,

t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,

t2.statement_end_offset, t2.plan_handle

FROM (Select session_id, request_id,

SUM(internal_objects_alloc_page_count) AS task_alloc,

SUM (internal_objects_dealloc_page_count) AS task_dealloc

FROM sys.dm_db_task_space_usage

GROUP BY session_id, request_id) AS t1,

sys.dm_exec_requests AS t2

WHERE t1.session_id = t2.session_id

AND (t1.request_id = t2.request_id)

ORDER BY t1.task_alloc DESC

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Mani-584606 SSChampion Points: 11788 More actions · Answer 5

We have mdf & ldf files of TempDB are on single drive T & 8 processors

For best performance, place the tempdb on a RAID 10 array. The number of tempdb data files should equal the number of core CPUs, and the tempdb data files should be set at an equal size. Count dual core processors as two CPUs for this purpose

So can I create 7 more ndf files of equal size on same T drive? Will it improve the performance?

what should I give the ldf file size? I read somewhere that size of the ldf file for tempdb as below:

Size of ldf file for tempdb = Sum of all mdf & ndf files of tempDB

Suppose if I create 7 datafiles of size2 GB for Tempdb, then the ldf file size should be 14 GB

Please tell me how you guys did Tempdb Configuration for Share point databases?

Thanks

Mani-584606 SSChampion Points: 11788 More actions · Answer 6

Generally, tempdb is not happiest on the SAN at all. Direct-attached storage is often a better solution. If your SQL Server is clustered, however, you currently have no choice. See https://connect.microsoft.com/SQLServer/feedback/details/532759/support-local-disk-location-for-tempdb-in-failover-cluster-installation
Given that it is on the SAN, how much difference it makes keeping both the data and log files on the same presented drive, depends entirely on your SAN's capabilities, and how it is configured. Test the latency and performance using SQLIOSim in both configurations

Hi Paul,

We have CLustered SQL Server and have 1 mdf & 1 ldf file for TempDB on Separate dedicated drive. As we have 8 processors, can I create 7 ndf files of equal size on same drive? or it's NO use of keeping secondary files on same Drive?

please advice

Paul White SSC Guru Points: 150467 More actions · Answer 7

Mani-584606 (3/29/2010)
We have CLustered SQL Server and have 1 mdf & 1 ldf file for TempDB on Separate dedicated drive. As we have 8 processors, can I create 7 ndf files of equal size on same drive?

You could do, but I probably wouldn't. You will only see a benefit from doing this if you are currently experiencing DDL contention in tempdb. This is less likely than it used to be. The query used to determine if you are experiencing this issue can be found here:

http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-ddl-bottleneck.aspx

If you don't see any results from the query contained in that link, don't bother - you may end up making tempdb performance worse.

More stuff to read to fully understand the issues involved:

http://technet.microsoft.com/en-au/library/cc966545.aspx

http://sqlkpi.com/BLOGS/PAUL/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx

Be careful. Don't just go ahead and create multiple files - understand the issues, and test thoroughly to show that you get any improvement you might expect.

You might get more benefit by sitting down with your SAN administrator and getting him or her to understand the issues involved from a SQL Server perspective.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mani-584606 SSChampion Points: 11788 More actions · Answer 8

Thanks Paul,

We have MOSS 2007 (Share point) databases in our clustered SQL Server 2005 and the MOSS 2007 documentation in the link http://technet.microsoft.com/en-us/library/cc298801.aspx

says that Share point uses the TempDB a lot and we need to configure TempDB properly

So considering share point what is the best way to configure my TempDB mdf & ldf files taking into account of having Separate SAN drive for TempDB

Thank you so much

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

In general tlogs should not be with data due to a) the importance of tlog throughput and b) different nature of data access between data and log files

But all this talk is fairly pointless, at least unless you can show that you actually have problems with tempdb IO stalls. If you don't, then I bet you have more important things to go fix. 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 10

TheSQLGuru (3/31/2010)
In general tlogs should not be with data due to a) the importance of tlog throughput and b) different nature of data access between data and log files

True, true, true. Always more complicated on a SAN though.

But all this talk is fairly pointless, at least unless you can show that you actually have problems with tempdb IO stalls. If you don't, then I bet you have more important things to go fix. 😀

Like ditching SharePoint, perhaps 😉

I may be alone in this, but I have never has a positive experience with SharePoint.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

Paul White NZ (3/31/2010)
TheSQLGuru (3/31/2010)
In general tlogs should not be with data due to a) the importance of tlog throughput and b) different nature of data access between data and log files
True, true, true. Always more complicated on a SAN though.
But all this talk is fairly pointless, at least unless you can show that you actually have problems with tempdb IO stalls. If you don't, then I bet you have more important things to go fix. 😀
Like ditching SharePoint, perhaps 😉
I may be alone in this, but I have never has a positive experience with SharePoint.

You are not alone in the Sharepoint feelings. The 'softies that developed the architecture and database schema/processes for that were horrible and clearly screwed the pooch in many ways. The problem now is that they are stuck with 'legacy' design crap for the next few decades that we will all have to suffer through. I take the silver-lining view however and simply consider it as making more business for me. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 12

TheSQLGuru (3/31/2010)
You are not alone in the Sharepoint feelings. The 'softies that developed the architecture and database schema/processes for that were horrible and clearly screwed the pooch in many ways. The problem now is that they are stuck with 'legacy' design crap for the next few decades that we will all have to suffer through. I take the silver-lining view however and simply consider it as making more business for me. 😎

Oh good - I feel better for not being alone 🙂

"Screwed the pooch" - LOL!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi