How to put Indexes on different disks to increase performance?

  • Everywhere I read, I notice that if the filegroups (partitioned) and indexes are placed on different disks, it enhances performance because more disks can be read at the same time.

    Now, my question is very basic, and can't get the answer. How do I make sure that I put them on different disks?

    Sometimes we divide our harddisk into different partitions, giving us different mapping names (G:, H:, I: etc). Are these different disks? I thought these are just partitions. Again looking at the code below:

    USE AdventureWorks;

    GO

    IF EXISTS (SELECT name FROM sys.indexes

    WHERE name = N'IX_TransactionHistory_ReferenceOrderID')

    DROP INDEX IX_TransactionHistory_ReferenceOrderID

    ON Production.TransactionHistory;

    GO

    CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID

    ON Production.TransactionHistory (ReferenceOrderID)

    ON TransactionsPS1 (TransactionDate);

    GO

    Yes, this code will tell me that the index INDEX_IX_TransactionHistory_ReferenceOrderID will be created on the partition TransactionPS1. But how do I know that this will be creatd on a different disk?

    Also is the max_degree_of_parallelism of any help?

  • Look at the definition of the file group.

    Filegroups are logical collections of one or more files. If you look at the definition of the file group, it will say what files that file group consists of, and hence what drive its on.

    If you're using table partitioning, the partition scheme will list the filegroups involved.

    Sometimes we divide our harddisk into different partitions, giving us different mapping names (G:, H:, I: etc). Are these different disks? I thought these are just partitions.

    They are hard drive partitions, completely unrelated to table partitions. Since they are the same physical drive, spreading tables/indexes across them is unlikely to improve performance. Performance gains from using multiple disks comes from having multiple spindles and been able to do reads from different disks simultaneously.

    Max degree of parallelism just limits the total no of CPU that can be used for any one query

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ok...I'm going to go with some assumptions here...

    (1) when you talk of partitions '...Sometimes we divide our harddisk into different partitions, giving us different mapping names (G:, H:, I: etc). Are these different disks? I thought these are just partitions. Again looking at the code below:...' you are refering to Windows volumes.

    (2) when talking of indexes, you are refering to non-clustered indexes (and not clustered indexes).

    With these assumptions, it is possible to create non-clustered indexes that sit on separate volumes to the data. You can do this by creating a filegroup, say IndexFG using ALTER DATABASE ... ADD FILEGROUP IndexFG, create files to be linked to this filegroup and placed on the volume you require, and when creating the index specifying this CREATE NONCLUSTERED INDEX...ON IndexFG.

    Doing this may improve performance, depending on your system and the way it works (ie that the indexes are used, etc)...and the best way of finding out if it does improve performance is to baseline before changing and then take another sample after the change to compare.

    [edit] also, forgot to mention, this will only be useful if the volumes are truely separate disks. If the volumes are partitions across the same set of physical disks, then you will gain nothing, and may actually adversely impact performance.

    HTH

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply