Post reply

High Fragmentation - No Indices

  • May 30, 2017 at 6:34 am

    #150580

    Just a caveat before I start: Aside from running queries I know very little about SQL server management/maintenance.

    I've had some performance issues with my scada so I ran the query to look at fragmentation in all of the tables in my DB. I have 50 tables over 30%; some as high as 98%. 

    Everything I've read online states when this occurs to rebuild/reorganize the indices. However it appears that 17 of these tables have no indices setup at all. I've rebuilt the index on other tables (some reduced fragmentation down to under 1%, some only reduced it a few percent). The tables with the highest fragmentation all have over 1000 pages and have no index setup.

    Any suggestions for what else I could do?

  • May 30, 2017 at 6:44 am

    #1944568

    Ignore it.

    Fragmentation is highly unlikely to be the cause of your performance problems.
    Performance problems mostly come down to poor indexing (as in incorrect or no indexes) and bad queries.

    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

  • May 30, 2017 at 6:47 am

    #1944569

    Gail addressed the performance aspect of it; fragmentation is very low on the "fix it" list as far as performance goes.

    If you want to address the fragmentation, here's a bit of help.
    so a table with no clustered index would be referred to as a heap table.
    you can rebuild a heap table, which is the same as defragmenting an index with a command like this:
    Alter table SchemaName.TableName rebuild

    A better practice would be to examine each heap table,and add a clustered index to them. The general rule is every table has a clustered index, unless you have a good reason not to.
    So add that to your to do list: examine your healp tables, and create a clustered index on them.
    there might be edge cases, like a log  table, where an an index might slow it down, but even then , i would doubt you could find a good reason to justify no clustered index.

    here's a decent article on heaps and fragmentation:
    https://www.simple-talk.com/sql/database-administration/sql-server-heaps-and-their-fragmentation/

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 30, 2017 at 7:06 am

    #1944570

    Thanks for the responses.

    Lowell,
    I tried the rebuild command but according to that article it looks like that's only available in SQL Server 2008 and above.

    It looks like I can create an index, then drop the table to accomplish the same thing though? I'll read into it a bit more and see what I can find.

  • May 30, 2017 at 7:24 am

    #1944573

    notredamon - Tuesday, May 30, 2017 7:06 AM

    It looks like I can create an index, then drop the table to accomplish the same thing though? I'll read into it a bit more and see what I can find.

    Or you can just ignore the fragmentation, as it is not going to be the cause of your performance problems, and spend your time on more productive things.

    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

  • May 30, 2017 at 9:10 am

    #1944581

    I absolutely agree with Gail on ignoring the "fragmentation" you're seeing.  Without writing a whole article about it here (Brent Ozar has several, which is where I got the idea from), I'll summarize by saying that stopped all index maintenance on my production boxes way back on the 17th of January, 2016 (almost a year and a half ago).  It was odd to watch performance actually improve over the first 3 months and there isn't a much wasted space due to page splits as you would think... it eventually get's used.

    Ironically and in direct contrast to the above, we used to get a truly significant amount of blocking on Monday mornings (the index maintenance occurs on Sundays) and all of that stopped when I stopped rebuilding indexes.

    To emphasize, performance is in the code, having the correct indexes, and being religious about keeping statistics up to date.  Fragmentation rarely comes into play on GUI related queries and, with today's SANs and the extremely random effects of having sometimes hundreds of users hitting the data, usually causes no ill effects on batch code either.

    --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 6 posts - 1 through 5 (of 5 total)

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