Post reply

Help understanding NC indexes and separate FileGroup

  • October 23, 2016 at 10:04 am

    #331217

    Hello All,

    I would like some assistance understanding something I read this morning in Grant’s book “SQL Server 2012 Query Performance Tuning”. (I love this book). He mentions moving Non Clustered indexes to a separate file group thus separating the I/O paths. As everything I know these things depend on specific situations. I believe I am having issues with I/O and wonder if this may be a possible remedy. I’m still very new at this so forgive any ignorance.

    My environment is an EDW around 350 gigs

    •SQL Server 2012

    •32 gigs Ram

    •4 physical 8 logical Processors

    •SAN with 15K HDD

    •Write speeds to tempdb a horrible > 300 ms, read speed > 20ms

    •8 temp file groups

    •Simple recovery

    •Indexes are maintained nightly using Brent Ozars maint script with default options

    •Top I/O quires with a I/O of 581125628

    •TOP waits CXPACKET, LATCH_EX, PAGEIOLATCH_SH

    My ultimate question is would moving the indexes to another file group be beneficial ? What can I do short of moving them and testing them to find out if this is a possible solution? Let me know I need to provide any other information. Trying to learn more about this.

    Thanks !

    ***SQL born on date Spring 2013:-)

  • October 23, 2016 at 1:17 pm

    #1908253

    thomashohner (10/23/2016)

    My ultimate question is would moving the indexes to another file group be beneficial ?

    My personal feeling is that it would do little to nothing, especially since you can't limit the physical location of the file group to a separate set of spindles.

    A better thing to do would be to find, troubleshoot, and repair any high I/O queries. Sometimes, it will be as simple as adding the correct index. Other times, it will be to fix non-SARGable queries or fix accidental many-to-many joins, etc.

    The ROI in finding and fixing the queries will help a whole lot.

    The problem with writes to TempDB should also be addressed.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 23, 2016 at 1:45 pm

    #1908256

    Thanks Jeff,

    After I posted I thought about it more and I guess the SAN made it kind of a moot point. Grant basically states at the beginning of the book that most performance issues are related to the query. I guess i was trying to score a easy configuration win. So far query tuning feels more like a art than a science. Going to keep studying and practicing. It feels frustrating sometimes you feel like wow I just solved a major performance problem and then like 2 weeks later its back with a vengeance. You then realize I solved nothing just band aided it. Got my highlighter going to see if I can do another chapter.

    Thanks again !:-)

    ***SQL born on date Spring 2013:-)

  • October 23, 2016 at 11:34 pm

    #1908267

    thomashohner (10/23/2016)

    Thanks Jeff,

    After I posted I thought about it more and I guess the SAN made it kind of a moot point. Grant basically states at the beginning of the book that most performance issues are related to the query. I guess i was trying to score a easy configuration win. So far query tuning feels more like a art than a science. Going to keep studying and practicing. It feels frustrating sometimes you feel like wow I just solved a major performance problem and then like 2 weeks later its back with a vengeance. You then realize I solved nothing just band aided it. Got my highlighter going to see if I can do another chapter.

    Thanks again !:-)

    You're definitely reading the best book on the subject. Grant did a marvelous job on the original and the update.

    Shifting gears... as you read the book, keep in mind the one thing that people forget. Grant didn't explicitly state it but the whole goal of "performance tuning" isn't necessarily to tune for performance. It's much simpler than that. A whole lot of very smart people wrote SQL Server and it works great. The problem is when people write things where SQL Server isn't so great but it will still do it's damnedest to provide what it's being asked for.

    To state it more simply, performance tuning isn't about forcing SQL Server to do great things. It already does that. Performance tuning is all about [font="Arial Black"]letting [/font]SQL Server do what it does best.

    The first step to performance tuning can be found in the paradigm shift that can be found in my signature line below. Then, remember that an index is nothing more than a subset duplication of data with it's own "sort order" that lives in a table with columns (and rows) and Grant's book will get a whole lot easier to understand. At least that's what worked for me.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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