January 7, 2016 at 7:03 am
I am fairly new in sql server database
I am very confused about the index and table maintenance jobs that we need to do daily or weekly.
I am pretty sure that we need to do reorg index and rebuild index for my databases. So do I do the reorg and rebuild base on the fragmentation percentage? I have done some researches that 10-30% for reorg and anything bigger that 30% needs rebuild. Is that the case for a very big database? Is there any other things that I need to pay attention?(non-cluster, cluster)
I hardly find anyone suggested about the table rebuild, so do I need to do table rebuild for me database? What kind of satiation do I need to do Table rebuild?
January 7, 2016 at 7:31 am
Hi there. Personally, rather than look to set up my own solution here, I would (in fact do) use one of those already written by either Michelle Ufford which works right out of the box, but is highly configurable if you need it http://sqlfool.com/2011/06/index-defrag-script-v4-1/
or Ola Hallengren https://ola.hallengren.com/
both are free, robust, flexible and very well regarded
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
January 7, 2016 at 7:39 am
Agreed that rolling your own is very poor decision, especially for somebody new to sql server. You will make far too many mistakes. The one from Ola is a good one. https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
There are a few others out there that are good too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2016 at 8:08 am
I can also strongly support using Minion Reindex. It's free from MinionWare[/url]. It does an excellent job of managing indexes & statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2016 at 9:12 am
so do I need to do table reorg? when do I need to table reorg?
January 7, 2016 at 9:28 am
Maybe you should take a peek at this article about fragmentation. http://www.sqlservercentral.com/articles/Stairway+Series/72443/[/url]
There is a whole series on indexing in the Stairways section on the left side in the links.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2016 at 10:13 am
ypeng5 (1/7/2016)
so do I need to do table reorg? when do I need to table reorg?
Firstly, I would very much agree with the recommendation from Sean on working through the stairway series, it's very good and will help your understanding a lot.
Briefly, in answer to the question. Welcome to SQL Server's commonest answer to a question. "It depends".
This is what it depends on ...
If your table is a heap (it doesn't have a clustered index) then you can't.
If you have a clustered index, then you would reorg/rebuild that as you would any other index (although as the CI key is in all your other indexes, this impacts on how and when to a degree), and this 'reorganises your table' as the CI kinda is the table. This will all make a lot more sense (probably) if you follow Sean's advice.
There again - often, any performance gains you see might actually have little to do with the rebuild of the index - it may actually be due to the fact that a rebuild has updated that table's statistics (information held on the data distribution in the table) which are used to generate your query plans. Sometimes you'll get more value just updating them than a reorg/rebuild for your maintenance overhead second.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
January 7, 2016 at 3:51 pm
ypeng5 (1/7/2016)
so do I need to do table reorg? when do I need to table reorg?
"It Depends" on a ton of things that are frequently not included in anyone's index maintenance routines. For example, how big is the index and, keeping in mind that the size of the log file, empty or not, can have a very significant impact on restores, is log file growth due to defragmenting indexes acceptable? If not, then you need to consider HOW you should do the REBUILDs and REORGANIZEs.
I've also seen where some fairly large tables (measured in 10's of GB) that have no fragmentation (<1%), which would certainly be ignored by most maintenance routines of others, but have a percent of page usage less than 50% (usually caused by a change in VARCHAR() data from something large to something much smaller, but not always). I've recently recovered more than 40GB of disk space on just one table in one database simply by picking up on that nuance and forcing the Clustered Index to rebuild if percent of page usage is 10% less than the given fill factor.
Then there's the fact that REORGANIZE is always done ONLINE and it's ALWAYS fully logged even in the SIMPLE Recovery Model. Although my databases and tables aren't actually huge (1/2 terabyte is both the largest DB and Table), you have to be very careful not to have the log file consume all the remaining hard disk. So, even if an index is < 30% fragmented, it may still be necessary to do a REBUILD in the BULK LOGGED mode (REBUILD is "minimally logged" in such a case) to avoid the explosion that would occur with a REORGANIZE. Again, many of the more popular index maintenance routines don't even consider such a thing.
That leads me to this, which is diametrically the opposite of all the recommendations thus far...
If you don't take the time or are afraid to write your own and you use one of the canned methods offered by some very good people but do so blindly, you are missing a great learning opportunity and, possibly, introducing problems that many have either never considered.
Even if you sit down and study the code in those wonderful open source solutions, you won't study indexes, stats, and backups as well as you would if you did the proper research to write your own. And, yes, they make great study guides to help you write your own but even they miss such things as the problems I've stated on this post.
Now, if you need to, yes... use one of those "canned" scripts to get out of the proverbial woods but make sure that's a temporary solution. Make sure you study all of the nuances of Recovery Models, REBUILDs, REORGANIZEs, Stats Updates, Backups, and Restores and then write you own routines to do these things to permanently drive it all home.
Knowledge is power. Just using someone else's canned code makes you... "just a user". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2016 at 12:29 pm
Thanks for your wonderful suggestions, I will do that! One more question, I have done some researches about table rebuild, but I found nothing useful. Does SQL Server has table rebuild? What's the difference between table rebuild and index rebuild? When do we use table rebuild?
January 11, 2016 at 1:26 pm
ypeng5 (1/11/2016)
Thanks for your wonderful suggestions, I will do that! One more question, I have done some researches about table rebuild, but I found nothing useful. Does SQL Server has table rebuild? What's the difference between table rebuild and index rebuild? When do we use table rebuild?
You're referring to what is called a heap table, a table without a clustered index. The short answer is no, you can't rebuild a table.
You can, if necessary, apply a clustered index to the table and then drop that index. That will result in a "rebuild." However, that's something I would only do in extreme circumstances.
For the most part, the vast majority (and I mean 99.999%) of my tables have a clustered index, so this doesn't come up as an issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2016 at 3:19 pm
Grant Fritchey (1/11/2016)
You can, if necessary, apply a clustered index to the table and then drop that index.
In most cases, the better idea is to stop halfway through that procedure.
(I.e., add a clustered index, then leave it there)
January 11, 2016 at 3:41 pm
Hugo Kornelis (1/11/2016)
Grant Fritchey (1/11/2016)
You can, if necessary, apply a clustered index to the table and then drop that index.In most cases, the better idea is to stop halfway through that procedure.
(I.e., add a clustered index, then leave it there)
Absolute agreement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2016 at 5:28 pm
You can easily rebuild a heap table starting from SQL Server 2008.
ALTER TABLE [TableName] REBUILD
January 11, 2016 at 6:08 pm
Alexander Suprun (1/11/2016)
You can easily rebuild a heap table starting from SQL Server 2008.
ALTER TABLE [TableName] REBUILD
This is bad. Don't do it.
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/
January 12, 2016 at 10:16 am
Steve Jones - SSC Editor (1/11/2016)
Alexander Suprun (1/11/2016)
You can easily rebuild a heap table starting from SQL Server 2008.
ALTER TABLE [TableName] REBUILDThis is bad. Don't do it.
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/
It's not necessarily bad. The other author claims it's bad because non-clustered index(es) must be rebuilt since it's a heap. True enough, but applicable only if there are any non-clustered index(es). Besides, the space reclaimed and the performance gain from scanning the rebuilt heap vs the original heap could easily be more gain than the cost of rebuilding a non-clus index(es). This is another case where a super-simple rule doesn't accurately cover every situation.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply