August 11, 2008 at 1:19 am
Hi All,
At present my mdf and ldf files are in the same logical drive. so plz suggest it should be in different physical location to improve Disk queue length and I/O and others for better performance.
If you have any doc or tools for that suggest me.
Thanx,
Regards,
Jayesh Pavar
August 11, 2008 at 1:31 am
Generally the log file should be on a separate physical drive from the data files, TempDB should have its own physical drive and the OS+ swap file should be on a separate physical drive.
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 3, 2008 at 8:31 am
You can go even further with splitting the data files between physical drives according to their usage patterns (Read, Read\Write, LOB), but it has better explained in the webcast 'A Primer to Proper SQL Server Development (Part 01 of 10): Creating a Recoverable Database (Level 200)' by Kimberly Tripp, which I would strongly recommend watching.
Webcast URL:
BTW: Different optimization techniques would apply if you would choose SAN as storage scenario.
January 27, 2009 at 2:38 am
Having inherited a SQL 2000 database configured as single data and log files (albeit on different SAN volumes), I'm faced with the task of splitting it into multiple data and log files to try improve performance as the server has 8 single-core Xeons.
Ideally I'd like to end up with at least 8 data and 8 log files, but I haven't been able to find how this can be done using backup/restore or any of the T-SQL file or filegroup management commands. The only option I can think of is to create a new database, correctly configured, and then do a bulk copy data migration ... which is complicated by the need to move hundreds of SPs, is error-prone and would probably overshoot any downtime that may be available for such an exercise.
Am I missing something?
January 27, 2009 at 3:55 am
There is absolutely no reason to have multiple log files. It gains you nothing. Log files are written in sequence, not in parallel. The only reason to have more than one log file would be if there's not enough space on a drive for the entire log.
Regarding the data files, it is not recommended for user databases to be split into one file per processor core. That recommendation is given for TempDB and for reasons that have to do with contention on the allocation pages.
There is little benefit to having multiple data files unless those files are on separate physical drives, and the system is under an IO bottleneck. The recommendations that I've seen for OLTP databases is max 1/2 the number of data files as you have cores. For datawarehouses, the fewer files the better. However before splitting, you should ensure that the system bottlenecks are related to allocation or IO. If they're not, splitting will not help you.
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
January 27, 2009 at 9:47 am
Many thanks for the prompt response and the very informative links.
Nevertheless, just looking at the issue of how to split an existing database into several data files, am I right in thinking that this can only be done by adding files and moving objects manually into the new files? (remember, I'm stuck with SQL 2000).
The main bottleneck appears to be I/O and although I'm using W2K3, I haven't moved to the StorPort driver yet and it might be beneficial to have multiple data files.
January 27, 2009 at 9:59 am
John Reid (1/27/2009)
Nevertheless, just looking at the issue of how to split an existing database into several data files, am I right in thinking that this can only be done by adding files and moving objects manually into the new files?
Yup. That's how it's done, though you need multiple filegroups as well to do that. If you just add files to the primary filegroup, there's no way of moving data. SQL will just put new data down in the new files according to the proportional fill algorithm that it has.
Regarding IO bottlenecks. You will only get an advantage from multiple files if they are on separate physical drives (separate SAN luns if you're using a SAN). Before going that route, consider looking at the queries that are running, finding the ones doing the most IO and tuning them (code changes, index changes). The benefit from that should heavily outweigh that of adding more files.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply