September 24, 2013 at 4:18 pm
Friends,
I want to know if my datafiles (mdf and ldf) should be the same disk or disks distinct.
ex:
Example: 1
DISK T
tempdb.mdf
tempdbdev2.ndf
tempdbdev3.ndf
...
tempdb.ldf
OR
Example 2:
DISK T
tempdb.mdf
tempdbdev2.ndf
tempdbdev3.ndf
...
ANOTER DISK
DISK K
tempdb.ldf
Thanks.
September 25, 2013 at 2:29 am
Personally, for TEMPDB i have all of the files on the same drive by default. They are always on their own dedicated drive though.
For the Data and log files for user databases, they are on different drives.
September 25, 2013 at 4:01 am
use tempdb
go
select filename, * from sysfiles
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 25, 2013 at 4:27 am
It depends on the setup.
I would prefer to put the datafiles on separate drives.If i have 4 data files then 2 datafiles on one drive and 2 on other.
You need to constantly check which data/log file is experiencing bottleneck.
SELECT DB_NAME(vfs.database_id) AS database_name ,
vfs.database_id ,
vfs.FILE_ID ,
io_stall_read_ms / NULLIF(num_of_reads,0) AS avg_read_latency ,
io_stall_write_ms / NULLIF(num_of_writes,0)
AS avg_write_latency ,
io_stall / NULLIF(num_of_reads +num_of_writes,0)
AS avg_total_latency ,
num_of_bytes_read / NULLIF(num_of_reads,0)
AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF(num_of_writes,0)
AS avg_bytes_per_write ,
vfs.io_stall ,
vfs.num_of_reads ,
vfs.num_of_bytes_read ,
vfs.io_stall_read_ms ,
vfs.num_of_writes ,
vfs.num_of_bytes_written ,
vfs.io_stall_write_ms ,
size_on_disk_bytes /1024 /1024. AS size_on_disk_mbytes ,
physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id =mf.database_id
AND vfs.FILE_ID =mf.FILE_ID
ORDER BY avg_total_latency DESC
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
September 25, 2013 at 5:29 am
SQLSteve (9/25/2013)
Personally, for TEMPDB i have all of the files on the same drive by default. They are always on their own dedicated drive though.For the Data and log files for user databases, they are on different drives.
There is never an advantage to having the same files on the same drive. You would be as well having one large TEMPDB in that case. Better would be to have several TEMPDB datafiles (.mdf and .ndf) spread across multiple drives/spindles or LUNs.
September 25, 2013 at 5:40 am
kevaburg (9/25/2013)
There is never an advantage to having the same files on the same drive. You would be as well having one large TEMPDB in that case.
That's not true for TempDB. For user databases it is (mostly), but TempDB is different.
TempDB is usually split into multiple files to reduce allocation contention, latch contention on the first GAM/SGAM/PFS page of the file. Splitting TempDB into multiple files means there are more GAM/SGAM/PFS pages and hence reduces that latch contention. When splitting tempDB to reduce latch contention, there's no requirement that the files be on different drives. The time when you want to have TempDB files on multiple drives is when you have IO contention on TempDB.
If splitting TempDB for allocation contention, files can be on the same drive
If splitting TempDB for IO contention, files need to be on separate IO paths.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 5:53 am
Gila,
so... in the case, it's recommend a put datafiles .mdf, .ndf and .ldf in the same disk, correct?
September 25, 2013 at 6:01 am
GilaMonster (9/25/2013)
kevaburg (9/25/2013)
There is never an advantage to having the same files on the same drive. You would be as well having one large TEMPDB in that case.That's not true for TempDB. For user databases it is (mostly), but TempDB is different.
TempDB is usually split into multiple files to reduce allocation contention, latch contention on the first GAM/SGAM/PFS page of the file. Splitting TempDB into multiple files means there are more GAM/SGAM/PFS pages and hence reduces that latch contention. When splitting tempDB to reduce latch contention, there's no requirement that the files be on different drives. The time when you want to have TempDB files on multiple drives is when you have IO contention on TempDB.
If splitting TempDB for allocation contention, files can be on the same drive
If splitting TempDB for IO contention, files need to be on separate IO paths.
Would it not be a best practice though in instances with lots of databases (or in general) to spread the TEMPDB files over multiple spindles? I could not imagine that there could be a disadvantage with regards to allocation contention in doing so and you have catered for any problems that might occur in the future with databases that have issues with allocation or IO contention.
September 25, 2013 at 8:34 am
LOVER OF SQL (9/25/2013)
so... in the case, it's recommend a put datafiles .mdf, .ndf and .ldf in the same disk, correct?
Maybe. Maybe not. See my earlier posts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 8:39 am
kevaburg (9/25/2013)
Would it not be a best practice though in instances with lots of databases (or in general) to spread the TEMPDB files over multiple spindles?
Maybe. Maybe not. If all I'm splitting for is allocation contention, on the same drive is fine. If I'm splitting for IO contention, separate IO paths is required.
Bear in mind it's usually a hell of a lot easier to convert one TempDB file into multiple TempdB files totalling the same size than it is to get multiple different LUNs from the storage team, and that's with a SAN. With direct attached storage it's even more of a nightmare. Why do stuff if you don't need to do?
I have one client where TempDB is on an SSD. It's got 8 data files and one log file all on that same SSD and TempDB gets hammered. Current IO contention - 0. Likely IO contention in the future - close to 0. Why would I go to the expense of multiple SSDs in that case just to satisfy 'TempDB files on different drives'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2013 at 10:08 am
My interest in this theme is based on the fact I have an upcoming migration to SQL Server 2008R2 and I have 2 NetApp storage appliances with 20TB each. Essentially one will mirror the other and will be based in an active/passive cluster. Because this storage is ONLY going to be used for the SQL Server I have the option to start with a clean sheet, something not many admins have the luck to experience.
To that end, I want to do it in the most efficient and future-proof way that I can.
I have the data (now!) to show whether IO-based or allocation-based contention is going to be a problem but from what I understand, by placing multiple tempdb datafiles over several LUNS I can mitigate both problems in one sweep. Have I understood that correctly?
Another problem that I face is that this migration is part of a large ERP project and a lot of databases are currently under development to facilitate that. These databases represent a huge unknown and data about growth, their transactional nature and so on is obviously not available.
Currently planned is to reserve 10TB initially. Five LUNs will hold the .mdf files, five for the transaction logs and five for the tempdb at first. That leaves a lot of room on top to extend this arrangement is it is felt necessary.
To that end, tempdb has to be right from the start. I expect a lot of activity from this cluster and for me in my situation with the arrangement I have to work with (and the kit!) I want to try to plan as far ahead as I can from the off!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply