September 4, 2014 at 6:01 am
We have sql 2008 r2 standard. In one of our databases, the indexes get fragmented quickly causing performance degradation. Because std edition does not allow rebuilding indexes on-line, I schedule the rebuilding off hours but it's very time consuming and i'm concerned that I run the risk of corrupting my db everything I switch from full recovery to simple mode. To switch between recovery modes takes about 40 minutes to complete the switch.
So I thought if I caught the index between 10-30% fragmented I could just reorganize the index to avoid the rebuilding process and downtime. In dev, I ran the script to just reorganize those indexes between 10-30% however the reorg either did not change the % fragmented or made little difference.
Besides purchasing Enterprise edition, Can anyone offer advice on how I can manage fragmentation better?
And is it normal for switching between recovery mode full to simple or bulk load to take 40 minutes??
Your advice is greatly appreciated!!
September 4, 2014 at 6:03 am
Why are you switching recovery modes to perform maintenance on your indexes?
September 4, 2014 at 6:05 am
LadyDardar (9/4/2014)
i'm concerned that I run the risk of corrupting my db everything I switch from full recovery to simple mode.
Um....
SQL does not corrupt its own databases. Corruption is (~99% of the time) IO subsystem related. As in a faulty IO subsystem. Not that you should be switching to simple anyway, as that breaks the log chain. Switching to bulk-logged is usually OK, providing the small risk from the lack of point-in-time restores is acceptable.
If you use maintenance plans, switch to Ola's index maintenance script(http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html). Indexes that fragment fast you can decrease the fill factor bit by bit.
Also, are you sure it's the fragmentation causing the performance problems?
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
September 4, 2014 at 6:06 am
As I don't have experience with indexes, I've read that the rebuilding process fills up the log file and can consume a ton of disk space. It was recommended to switch to either simple or bulk.
September 4, 2014 at 6:09 am
LadyDardar (9/4/2014)
It was recommended to switch to either simple or bulk.
Probably recommended by someone who doesn't know what recovery models do...
Does your system require point-in-time recovery in the case of a disaster (restore to point of failure)? If so, then by switching to simple recovery you're ensuring you can't meet that recovery requirement.
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
September 4, 2014 at 6:09 am
Rebuilding indexes will consume disk space, but I would recommend that you backup the log regularly instead of switching the database's recovery mode.
September 4, 2014 at 6:30 am
Yes we need point in time recovery. Before I switch to simple, I do a full backup, switch to simple, rebuild indexes, switch to full recovery, do another full backup. Yes I know.... probably not the best approach; however just trying to do the best with what I've read. I appreciate you pointing me in the right direction.
I don't believe the fragmentation is the root cause of our problems. however after I rebuild, performance improves. I believe our performance problems are from other sources; however until I can analyze the situation, I'm stuck with this solution. The database application where i'm experiencing this problem is Dynamics AX. They have a SQL Performance tool that I've installed in dev but haven't completed the setup. Another possible problem is we are using one database for transactions and reporting tools. We are a small shop and i'm under water.
The maintenance plan you suggested can you tell me what I can expect when using it?
also you mentioned I should not switch to simple or bulk. Will STD edition allow me to REBUILD an index while on-line? I tried that in dev and it threw an error that it couldn't be done online.
Thanks!!
September 4, 2014 at 6:38 am
LadyDardar (9/4/2014)
Yes we need point in time recovery. Before I switch to simple, I do a full backup, switch to simple, rebuild indexes, switch to full recovery, do another full backup. Yes I know.... probably not the best approach;
Definitely not. If the full backup after ends up not being restorable (and I'm guessing it's not tested to see if it does restore) you could end up losing hours and hours of data.
Change those full backups to log backups and switch to bulk-logged before and back to full after. Way better than what you're doing now and won't blow the log files way up.
I don't believe the fragmentation is the root cause of our problems. however after I rebuild, performance improves.
Which means it could be related to poor plans in cache or to statistics. Quite unlikely to be fragmentation itself that's the problem, and rebuilding indexes just to get a few plans out of cache is somewhat overkill.
That said, dynamics is a performance problem. Not really much you can do with dynamics other than give it a server to itself, throw hardware at it and hope it behaves (and regularly update statistics, but that goes for all systems)
The maintenance plan you suggested can you tell me what I can expect when using it?
What do you mean?
also you mentioned I should not switch to simple or bulk. Will STD edition allow me to REBUILD an index while on-line? I tried that in dev and it threw an error that it couldn't be done online.
Online rebuilds are Enterprise-only, so no you can't do them in standard edition. Not sure what that's got to do with the recovery model recommendations
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
September 4, 2014 at 7:04 am
The db server has 48gb RAM. We will be adding more RAM as the workload will be increasing within the next year.
Regarding Dynamics, any experience with their SQL Performance Tool? i'm so hoping it will provide some insight to where our problems are. any advice about that app in general is appreciated. It's a beast!
This was posted to my blog (not sure if it was you), "If you use maintenance plans, switch to Ola's index maintenance script(http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html). Indexes that fragment fast you can decrease the fill factor bit by bit." Just wanted to know what to expect. Of course i'll run it in dev first after I read and read!
Thank you! I will stop switching to simple and will do log backups before and after switching to bulk.
I really appreciate all the feedback! You are awesome!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply