September 26, 2011 at 11:32 am
I am creating a script to rebuild/reorganize all indexes in our database, so I have 3 parameters for it : threshold for reorganize, rebuild (in percentage values) and for page count (we do not care about small tables).
What are the most optimal values for them? What do you use in your environments ?
Thanks
September 26, 2011 at 11:37 am
SQL Guy 1 (9/26/2011)
I am creating a script to rebuild/reorganize all indexes in our database
Why are you re-inventing the wheel?
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 26, 2011 at 12:05 pm
I am not re-inventing the wheel. I just create a script around sys.dm_db_index_physical_stats DMV with two outcomes: rebuild / reorg.
The script to optimize indexes by Ola Hallengren that you are reffering here, is extremely huge, it was written for like "one-size-fits-all". It would take me a lot more time to adopt it to my environment and throw away 90% of it, rather than to "invent my wheel". Moreover, it is already written.
And BTW, I did not find anywhere in it the answers to my question: what is most appropriate values for rebuild , reorganize, and page count threshold?
September 26, 2011 at 12:10 pm
SQL Guy 1 (9/26/2011)
The script to optimize indexes by Ola Hallengren that you are reffering here, is extremely huge, it was written for like "one-size-fits-all". It would take me a lot more time to adopt it to my environment and throw away 90% of it, rather than to "invent my wheel". Moreover, it is already written.
The point is, you don't have to adapt it to your environment. It's written, tested and used in many places from small systems right up to the large enterprises. Michelle's script is the same. You can just create the jobs to run them with no additional time expenditure.
The guideline thresholds are documented in Books Online and on Paul Randal's site
http://msdn.microsoft.com/en-us/library/ms189858.aspx
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 26, 2011 at 12:34 pm
I totally disagree with you, GilaMonster.
You don't take in the account such important things as maintainability and support. Not Ola, by I will receive a call at 2:00 am if something will go wrong. The fact that it was adapted by others, does not mean anything. I still must learn and figure out all these 1000+ lines of code, versus mine simple (and therefore more reliable) S.P. of just 120 lines.
And I could not connect to sqlfool, because of some error on their side.
I just re-checked again B.O.L. for "alter index" and "sys.dm_db_index_physical_stats" articles, but could not find answers to my questions.
September 26, 2011 at 1:18 pm
While I agree with Gail (it's already been done), the answers to your questions are...it depends on your system, uptime, SLA, and so forth.
IMHO, you would REORG anything between 10% and 30%, REBUILD anything > 30% and leave anything under 10% alone as fragmentation would/should be minimal anyways.
As far as page count thresholds...again, it depends, if you have a non-clustered index with 5 pages in it, it will show up as being highly fragmented no matter how much you perform matintenance on it. At our company I omit anything below 50 pages, as in my experience these indexes never cause performance issues (but this may be different at your place of employment)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 26, 2011 at 1:45 pm
Thanks, Jessie. Finally I got some meaningful numbers to start with.
September 26, 2011 at 2:00 pm
Removed
September 26, 2011 at 2:08 pm
SQL Guy 1 (9/26/2011)
Thanks, Jessie. Finally I got some meaningful numbers to start with.
I take it you either didn't follow or ignored the links I posted?
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 26, 2011 at 4:40 pm
Ninja, we are here not to argue or curse each other, but to give constructive answers. We are all here to share our experience. If you don't know the answer or by some reason not in the mood to give it, please don't answer. Your post did not give any value. I will not call "bullshit" your statement that every SQL code published on internet, should be blindly copied into your environment without learning and understanding how it works, just because it "ran successfully somewhere in the XX millions of runs". That simply unprofessional and uneducated approach. Your statement that "Code doesn't rust & doesn't break down with time" proves it one more time.
As a professional having more tnan "zero understanding", you must understand that even the very best code must be learned, checked before adopting it into your environment. The code referred by previous poster is overwhelmingly huge and perhaps 90% of it is not applicable to my situation. I, even I have "zero understanding", have developed a stored procedure that rebuilds/reorganize what I need. I don't intend to state that my code is better. But it is simple and exactly what I want, and what's more important is that I know how it works, so if I will be waked up by call at 3:00 am in the morning I will not need to spend hours undestanding the code. That's because it's mine.
Code does not rust, but it can cause miriad of problems, like excessive log, disk space, CPU overutilization, and many more. My code can cause the same problems, but the point is that in MY code I will figure out the problem immediately.
And in the end, guys, please try to follow just simple etiquette of communicating on professional forum. The previous poster, instead of giving some meaningful numbers, sarcastically asked if I "reinvent the wheel ?". Yes, I must say, we all "reinventing wheels" in certain degree. For nearly all our tasks there is plenty of code on internet, should we just copy them without examining them? Just because it worked everywhere else ?
September 27, 2011 at 3:02 am
SQL Guy 1 (9/26/2011)
The previous poster, instead of giving some meaningful numbers, sarcastically asked if I "reinvent the wheel ?".
There was no sarcasm whatsoever in my statement, and I did provide you with links to those 'meaningful numbers', one of which even explains where those meaningful numbers come from.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply