How to improve performance when data is scattered using filegroup - irrespective of whether using independent disks or SAN?



  • Link: https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15#filegroups

    For example: Data1.ndf, Data2.ndf, and Data3.ndf, can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks; it will improve performance. The same performance improvement can be accomplished by using a single file created on a RAID (redundant array of independent disks) stripe set.

    Since file group uses proportional fill strategy the data is scattered amongst the 3 files.

    Similarly when I use a SAN (RAID), and a single data file, then the table data will be scattered around the SAN disks.

    In both cases the data is scattered so how can the performance be further improved?



  • Disks are slow, so having more than one working simultaneously for you improves performance. SQL Server does asynchronous I/O operations (tell the OS it wants the data, and the OS gets back to that thread when the I/O is done). Having more than one disk mean you have several disks working simultaneously for you instead of only one disk.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2