none
Filegroups on disks with different performance

    Question

  • Hi all

    One of our db is growing fast and we need more space for it, our dba said that they can increase the space of our db (double it)  immediately but on a disk (partition/san I don't know exactly where) with slower performance of the one where the db is actually situated.

    Our db consists of about 10 huge table (hundred millions records), about 30 tables with millions of records, and some lookups.

    All tables has indexes.

    Now my question is witch table / index place on the fast disk and witch on the slower one?

    regards

    -g


    Gian Paolo Santopaolo - softwarelab.it - thedarksideof.net
    Disclaimer: This post is provided "AS IS" with no warranties, and confer no rights.

    Wednesday, September 25, 2013 8:28 PM

Answers

  • ... our dba said that they can increase the space of our db (double it)  immediately but on a disk (partition/san I don't know exactly where) with slower performance of the one where the db is actually situated.

    ...

    Now my question is witch table / index place on the fast disk and witch on the slower one?

    ...

    I don't understand the first phrase. How can someone improve performance by putting data on a "slower" volume?

    I guess either my english is bad or it's a typo.

    For the second part find out where the hotspots in your database are, which indexes are used the most often, and move those indexes/tables onto the faster drive by using filegroups - if not the whole database fits there.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, September 25, 2013 8:50 PM
  • More frequently queried tables/indexes on a faster disk, and less frequently queried on a slower disk. Usually huge tables are partitioned by date and older partitions are used less frequently and can be placed on a slower, cheaper storage.

    Which tables/indexes are more frequently used, you can determine by examining these dm objects:

    sys.dm_db_index_usage_stats
    sys.dm_db_index_operational_stats

    You can analyze disk IO activity by file with querying:

    sys.dm_io_virtual_file_stats

    But partitioning the data would probably help you the most.

    It is also a good approach to measure IO characteristics of both storages and see actual read vs write performance, random vs sequential performance etc. I use SQLIO for that. For example, RAID5 is slow on writes, but very fast for reads making it ideal for read-only or read-intensive workload, while RAID10 is ideal for both write intensive and read-intensive workloads. Many small disks = good random IOPS performance. There is a whole science behind the IO optimization. You can also compensate to some degree with adding more RAM which is now very cheap, and by optimizing IO intensive queries (sys.dm_exec_query_stats) with appropriate indexes. You can consolidate indexes to decrease the number of indexes and the space they occupy. Compression of rarely used partitions is also one interesting option.

    I wish you all the best with this interesting optimization task.

    Wednesday, September 25, 2013 8:58 PM
  • Hello Gian,

    Actually I agree with Andreas.Can you ask you storage admin that giving slower disk will actually be beneficial to whole database.Its general saying is you are as fast as your slowes thing.So if your Disk is slow it will ultimately slow down queries and might be a bottleneck in long run.

    I would suggest you to create New filegroup and move your less accessed tables to that and this Fg will be on Slower disk.And let frequest accessed one be on Faster one.

    you can use below query to see read/write on your database.

    select * from sys.dm_io_virtual_file_stats(Null,Null) where database_id=DB_ID(N'Db_name')

    you can use below discussion to move your table to new FG which you will create on Slow partition

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/025825ee-ecbc-4d7c-94ff-0595afc48347/how-to-move-a-table-from-a-file-groupprimary-to-another

    But in end you have to decide which Tables you want to move on slower one.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, September 26, 2013 5:51 AM

All replies

  • ... our dba said that they can increase the space of our db (double it)  immediately but on a disk (partition/san I don't know exactly where) with slower performance of the one where the db is actually situated.

    ...

    Now my question is witch table / index place on the fast disk and witch on the slower one?

    ...

    I don't understand the first phrase. How can someone improve performance by putting data on a "slower" volume?

    I guess either my english is bad or it's a typo.

    For the second part find out where the hotspots in your database are, which indexes are used the most often, and move those indexes/tables onto the faster drive by using filegroups - if not the whole database fits there.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, September 25, 2013 8:50 PM
  • More frequently queried tables/indexes on a faster disk, and less frequently queried on a slower disk. Usually huge tables are partitioned by date and older partitions are used less frequently and can be placed on a slower, cheaper storage.

    Which tables/indexes are more frequently used, you can determine by examining these dm objects:

    sys.dm_db_index_usage_stats
    sys.dm_db_index_operational_stats

    You can analyze disk IO activity by file with querying:

    sys.dm_io_virtual_file_stats

    But partitioning the data would probably help you the most.

    It is also a good approach to measure IO characteristics of both storages and see actual read vs write performance, random vs sequential performance etc. I use SQLIO for that. For example, RAID5 is slow on writes, but very fast for reads making it ideal for read-only or read-intensive workload, while RAID10 is ideal for both write intensive and read-intensive workloads. Many small disks = good random IOPS performance. There is a whole science behind the IO optimization. You can also compensate to some degree with adding more RAM which is now very cheap, and by optimizing IO intensive queries (sys.dm_exec_query_stats) with appropriate indexes. You can consolidate indexes to decrease the number of indexes and the space they occupy. Compression of rarely used partitions is also one interesting option.

    I wish you all the best with this interesting optimization task.

    Wednesday, September 25, 2013 8:58 PM
  • it's hard to tell but here are my suggetions

    • create multiple file groups  with multiple data and indexes files.
    • Put  CLUSTERED INDEX files on fast disk becasue most of the data in structured and SQL server reads the pages fast.
    • advantage for multiple file groups is you can move each file in different locations also.

    Wednesday, September 25, 2013 9:00 PM
  • Hello Gian,

    Actually I agree with Andreas.Can you ask you storage admin that giving slower disk will actually be beneficial to whole database.Its general saying is you are as fast as your slowes thing.So if your Disk is slow it will ultimately slow down queries and might be a bottleneck in long run.

    I would suggest you to create New filegroup and move your less accessed tables to that and this Fg will be on Slower disk.And let frequest accessed one be on Faster one.

    you can use below query to see read/write on your database.

    select * from sys.dm_io_virtual_file_stats(Null,Null) where database_id=DB_ID(N'Db_name')

    you can use below discussion to move your table to new FG which you will create on Slow partition

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/025825ee-ecbc-4d7c-94ff-0595afc48347/how-to-move-a-table-from-a-file-groupprimary-to-another

    But in end you have to decide which Tables you want to move on slower one.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, September 26, 2013 5:51 AM