none
Sense of partition disk

    Question

  • Hi,

    I have to install the SQL for our small clinic. I understand that for better performance and disaster consideration it is a good idea to put data and log on different disks. However we are the small company and I have possibility setup only one disk system-it would be RAID 10 with 4 disks.
    My question is any advantage in term of performance we can get if divide that disk on two partitions and put Data and Log file on a different partitions of one disk system?

    Our DB data will be approximately 10 GB.
    Any other suggestions on setting the Server would be appreciated.

    Thank you in advance,

    Wednesday, December 21, 2016 4:04 PM

All replies

  • Since the two partitions would be on the same physical disk with same controller head, there won't be any performance gain in placing the data file & log file on separate partitions (ie: IO distribution/separation), however, it may still be advisable approach from a manageability perspective and safety, in the event one partition becomes lost or damaged.

    Couple of other storage recommendations, split tempdb to multiple files = # of CPU cores and format disks to 64k block size.

    Thanks,


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, December 21, 2016 4:12 PM
  • You would be able to monitor in perfmon using logical disk counters with an extended partition like you describe. I doubt there would be any performance benefit. On high end systems you would want to eliminate IO contention by splitting out the files on different IO paths.

    I would caution you that if the single drive fails you have lost your database files no matter how you partition or split the database files between partitions. You would need to ensure that your backups are moved off the computer as soon as they complete.

    Wednesday, December 21, 2016 4:31 PM
  • Thank you Phil!

    So I am going to have the processor with the 10 cores. I understood it could be nice to split tempdb to 10 files.

    Is there a way to do this via GUI (Management Studio)?

    Wednesday, December 21, 2016 4:32 PM
  • Hilary, I will have the RAID 10 with 4 disks.
    • Edited by alilbas Wednesday, December 21, 2016 4:36 PM
    Wednesday, December 21, 2016 4:34 PM
  • Even though 1:1 core-to-tempdb (data) file is the rule of thumb, the point of diminishing return for additional files having significant performance benefit is about 8, typically. You may even want to start out with just 4 from a manageability perspective, and only add more files, as-needed. Only ONE tempdb Log file is needed, so don't add more of those.

    Yes, you can add tempdb file in SSMS GUI: In Object Explorer, go to tempdb > Properties > Files > Add [button].

    Be sure to make initial size the same for all files and choose a fixed MB value (instead of %) for autogrowth increment, (like 24, 48, 96 MB, etc) so the files will grow at an equal rate. Once you've done that, you will need to restart SQL Service for change to take effect.

    Take care,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, December 22, 2016 3:03 AM
    Thursday, December 22, 2016 2:06 AM
  • Thank you Fill. After that you reply I had read recommendation to have number of data files 1/4  the number of cores. But I pickup the processor with 10 cores. How many TempDB you recommend at this point?

    Again. the DB itself is not big and about 10 GB.

    Thank you in advance,

    Alex

    Thursday, December 22, 2016 4:22 PM
  • Hi alilbas,

    >>How many TempDB you recommend at this point?

    As recommended in KB2154845, ‘As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.’ In this case, I would suggest you start with 8 tempdb files at this moment.

    For more information, please review this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 23, 2016 5:01 AM
    Moderator
  • Before you go installing SQL Server directly on your server, have you considered installing enabling Hyper-V, and running SQL Server in a Virtual Machine?

    David


    Microsoft Technology Center - Dallas

    My Blog


    Friday, December 23, 2016 1:32 PM
  • I believe 8 would be the good figure to go with as in this case we have more than 10 cores and 10 GB of database.

    Also are using same Drive for TempDB files or dedicated disk as TempDB has more IO operations.


    Thanks, Dev SQL DBA

    Friday, December 23, 2016 3:10 PM
  • Hi David - what would the advantage of running hyper-v be? He has a small clinic, a 10 Gig database.  We know of no other server (host) so he could cluster. While he can carve out luns using hyper-v, they will still reside on his raid 10 drive? Just curious as to if I am missing something - which I likely am:)
    Friday, December 23, 2016 3:29 PM
  • >Hi David - what would the advantage of running hyper-v be?

    There probably is, or will be, a need for other servers.  A file and print server, or domain controller. An application server, etc.  Hyper-V lets them all share hardware. 

    Additionally with Hyper-V you don't have to rebuild the VM to replace the hardware.  Just bring up a new server and perform a Migration from the old host to the new host.  You can also perform a side-by-side upgrade of SQL Server with a single box.

    Additionally with SQL Server in a VM you can license just the virtual cores presented to the VM (with a minimum of 4).  So if the solution doesn't need 10 cores, you don't have to pay for 10 cores.  And you don't have to know your SQL Server sizing exactly when you buy hardware.

    Generally I wouldn't want to see anyone build a physical SQL Server in a small office/branch office scenario.

    David


    Microsoft Technology Center - Dallas

    My Blog



    Friday, December 23, 2016 3:59 PM
  • Thank you David,

    We have two VM Servers (FS and DC) on other Host. Of cause I considered to have VM on as well. But I didn't find any sense for us for that. We are small and even do not have a money for the second Disk set with Controller to divide DB from log file, Our Server would have Internal disks and probably no any SANs in a future. We never probably go to the cluster. And Our DB would be around 10 GB.  I do not see any need in one more Server.
    So can you please tell me what would be the advantage of VN in that case?


    • Edited by alilbas Friday, December 23, 2016 4:05 PM
    Friday, December 23, 2016 4:04 PM
  • >Our Server would have Internal disks and probably no any SANs in a future

    Hyper-V does not require a SAN or a cluster.  You can have a stand-alone Hyper-V host.  It literally costs nothing and adds important manageability and flexibility to the solution.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Friday, December 23, 2016 4:13 PM
  • Sorry David I didn't see your last reply. I have already HOST with DC and FS on VMs.  I do not see so far what Server we need more.

    I picked up this processor because we probably need all 10 cores.

    That is why I decided to dedicate all resources to our production DB application. So if I understand your properly there is just one benefit at this point for VM- easy Hardware Upgrade in future.  Is that correct?

    Thank you in advance,

    Alex

    Friday, December 23, 2016 4:14 PM
  • "You can also perform a side-by-side upgrade of SQL Server with a single box."

    Sorry David, I am new to that. What does that means?

    Alex



    • Edited by alilbas Friday, December 23, 2016 4:29 PM
    Friday, December 23, 2016 4:28 PM
  • >Sorry David, I am new to that. What does that means?

    Say you want to upgrade from SQL 2014 to 2016.  You can build a new SQL 2016 VM and copy (or restore) the databases from SQL 2014.  Then you can perform application testing, and ensure that the configuration is good before cutting over.

    This is how professional DBAs typically upgrade SQL Server in enterprise environments. 

    see eg

    Choose a Database Engine Upgrade Method

    http://sqlmag.com/blog/reasons-avoid-sql-server-place-upgrades

    David


    Microsoft Technology Center - Dallas

    My Blog


    Friday, December 23, 2016 4:39 PM
  • I understood. Thank you.

    “Generally I wouldn't want to see anyone build a physical SQL Server in a small office/branch office scenario.”

    Again I do not in position of argue, I just had read a lot of “theoretical” articles and in one of them I was warned about Security issue for VMs in Branch / Small office. Some security guy said there is a problem if somebody with a simple laptop physically obtain access to this Server with VMs-it very easy not only just still the data or destroy Server but take over this Server with all data bring it to other location and run on behalf. I just share this warning

     Is that true?

    Friday, December 23, 2016 4:57 PM