SQL server/ LDF and MDF file ratio
Hi,Is there any standard ratios suggested for LDF and MDF files in MS SQL server, for better performance
You must Sign In to post a response.
Category: Windows 8
Is there any standard ratios suggested for LDF and MDF files in MS SQL server, for better performance
#768113
Hi Sathish,
As per my understanding on SQL Server for better performance Check the following points.
1. MDF should be more than LDF. If you are looking for ratio kind something like 85%-MDF and 15%-LDF
2. Your file growth rate is important in fixing this ratio. Higher file growth higher MDF
3. Hope you heard about RAID concepts, We need to keep RAID -5 or RAID -10 for MDF
4. RAID -0 or RAID -1 for LDF for the better performance of the server.
Thanks,
Mani
As per my understanding on SQL Server for better performance Check the following points.
1. MDF should be more than LDF. If you are looking for ratio kind something like 85%-MDF and 15%-LDF
2. Your file growth rate is important in fixing this ratio. Higher file growth higher MDF
3. Hope you heard about RAID concepts, We need to keep RAID -5 or RAID -10 for MDF
4. RAID -0 or RAID -1 for LDF for the better performance of the server.
Thanks,
Mani
#768120
The recommended best practice is to assign a SQL Server Transaction log file its' very own disk or LUN.
This is to avoid fragmentation of the transaction log file on disk, as other posters have mentioned, and to also avoid/minimise disk contention.
The ideal scenario is to have your DBA allocate sufficient log space for your database environment ahead of time i.e. to allocate say x GB of data in one go. On a dedicated disk this will create a contiguous allocation, thereby avoiding fragmentation.
If you need to grow your transaction log, again you should endeavour to do so in sizeable chunks in order to endeavour to allocate contiguously.
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
This is to avoid fragmentation of the transaction log file on disk, as other posters have mentioned, and to also avoid/minimise disk contention.
The ideal scenario is to have your DBA allocate sufficient log space for your database environment ahead of time i.e. to allocate say x GB of data in one go. On a dedicated disk this will create a contiguous allocation, thereby avoiding fragmentation.
If you need to grow your transaction log, again you should endeavour to do so in sizeable chunks in order to endeavour to allocate contiguously.
Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]
Return to Return to Discussion Forum