| Author |
Topic |
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
 Posted - 2007-01-04 : 15:17:53
|
Just a quick question on defragging databases, as I am not sure about this.First, what exactly does this do? I am not quite sure. I am still learning the terminology.For instance, what does it mean when you "rebuild the indexes"? Is that the same as defragging?When you want to defrag a database, do you defrag the entire thing?What is the proper way to do this?I have been told that our production database is really fragmented and it would be wise to defrag it. However, I am not quite sure.The terms are confusing a bit (im still learning  ), so I wanted to make sure I was mixing things up.I appreciate the help.TCG |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-04 : 16:33:12
|
"what exactly does this do?"It rearranges the indexes / data so that the pages are more densely packed. That in turn means that some pages are no longer required. I think it also tries to make the pages more contiguous."what does it mean when you "rebuild the indexes"?"The whole of the index (and, for a Clustered Index, the data too presumably) is copied to a new area in the database, and then the original is released. This needs a lot of space, and there is as-much-space again left as "spare" when the operation is completed.In theory this should be able to make the table/index more contiguous than a Defrag, but in practice (according to Microsoft's white-paper) there is very little discernible different on performance between the two - even on very large tables.Reindex (under 2000) locks the table, takes longer than Defrag, and needs lots more disk space (and TLog space). But it also updates the statistics, which you have to do separately with Index Defrag. Index Defrag can be interrupted (i.e. the work done will be retained, whereas Reindex work will be rolled-back if interrupted)"When you want to defrag a database, do you defrag the entire thing?"Sadly no, you have to do it table by table, index by index. However, it only really makes sense to actually defrag objects that are fragmented  - and there are ways of interrogating how fragmented tables & indexes are. You could also choose to do tables A-M on Monday, and N-Z on Tuesday etc. to spread the load through the week."I have been told that our production database is really fragmented and it would be wise to defrag it. However, I am not quite sure."Have a go with DBCC SHOWCONTIG (See BoL for details) which will tell you how fragmented the tables/indexes are.Your physical database files may also be fragmented around the O/S & Disk. It makes sense to defrag them at a physical level too (well, supposedly that isn;t necessary for a SAN, but it is for a RAID, or if they are just on plain hard disks). Have a look at CONTIG.EXE from Sysinternals for that job. SQL Server service has to be stopped, or the database detached, to be able to defrag the physical file - and back it up first!! - so you need some scheduled downtime for that one)Kristen |
 |
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-04 : 19:38:12
|
| "Sadly no, you have to do it table by table, index by index. However, it only really makes sense to actually defrag objects that are fragmented - and there are ways of interrogating how fragmented tables & indexes are. You could also choose to do tables A-M on Monday, and N-Z on Tuesday etc. to spread the load through the week."Hmm, bummer. Here is a conundrum I am in.For this particular database, there are 3940 tables in it! Not all of them are used. We use a product (Siebel) and the way they setup the use of tables is funky (long story really.) In a nutshell, there are all those tables, but we don't use them all.What I am looking to do is figure out exactly which ones I need to defrag (when I saw defrag, is saying "rebuilding the indexes" the same, or different?)I ran DBCC SHOWCONTIG and have the output. Now I need to figure out how to sort it to see exactly what I need. Anyone know of any tricks?"Have a look at CONTIG.EXE from Sysinternals for that job. SQL Server service has to be stopped, or the database detached, to be able to defrag the physical file - and back it up first!! - so you need some scheduled downtime for that one)."Good to know. I didn't know that. I will make note of it.BoL is turning into a favorite resource.My knowledge has increased dramatically over the past month.I have to admit, I really enjoy working with databases. Thanks! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-04 : 22:15:27
|
| "well, supposedly that isn;t necessary for a SAN"i physically defrag drives on SAN when physical fragmentation warrants.curious...why supposedly not necessary? in my case, it gets fragmented (occasionally), i defrag (using windows 2k3 defrag) and performance is improved. i do not defrag at the level of fragmentation the utility recommends tho (i wait for even worse fragmentation :) ). also, need fairly large maint window... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-05 : 01:15:19
|
"why supposedly not necessary"Hehehe ... I should have put an emoticon on that.I read the manufacturers literature that it isn't necessary (but I hear also that DEFRAG tools still work ...)I remember when Windows NT first launched, with NTFS, hearing that defragging would no longer be necessary .... Yeah Right!We don't have any SANs so my knowledge is umpteenth-hand ..."there are 3940 tables in it!"Well, if you built something that only defragged/reindexed the ones that were fragmented the unused tables would get done at worst just once, wouldn't they?And if they are unused AND empty (or very small) the time to rebuild them will be trivial, so it wouldn't hurt if you did them every time.Alternatively have a list of tables to be Ignored."I ran DBCC SHOWCONTIG and have the output"I think that the Reindex/Defrag utilities in Tara's Blog store the output from Show Contig into a Temp file and use it as the basis for deciding what to house-keep.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Tara%20Blog%20Houskeeping%20RoutinesI store the results into a permanent, rather than temporary file, with an extra column to indicate whether I Reindexed or Defragged that index that time around. Then I can analyse how often tables are getting rebuilt - that shows bugs in my routine where I wrongly hit tables that don't need it  as well as tables that are fragmenting very quickly that I wasn't aware of - which on occasion has led to changes in Admin or Application.Of course you have to also them build a routine to purge the stale data after some time ..."I have to admit, I really enjoy working with databases."Telling you had sad you are is going to be a bit of a self-indictment, isn't it?!!Kristen |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-05 : 01:39:07
|
| "I remember when Windows NT first launched, with NTFS, hearing that defragging would no longer be necessary .... Yeah Right!' yeah, and we'd never have to reboot either... |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-01-05 : 07:35:25
|
| I setup a routine on one of our servers using the scripts that I Found here [url]http://www.sqlservercentral.com/columnists/ncain/checkingyourdatabasefragmentationlevel.asp[/url]. This had the effect of reducing the time to reindex of all tables in one of our DB's (as recomended by the application supplier) from 90 minutes (Weekly) down to 1 or two minutes per day. My next step is to modify this to use indexdefrag instead of reindex when its more appropriate.SAN Defragging - Yeah thats what the Storage consultant tells us, along with the fact that Log FIles don't need their own disk group, and we can store filestore and exchange files (well they are database files) in the same disk group ad the database files. After all teh Microsoft Guidelines ore generalisations as they can't make recomendations for individual SAN solutions . . . Oh Yeah? Well your new SAN's have twice the throughput of your existing one and you don't have problems with the existing one . . . . Oh No? . . . And its not as if we are going to be puting 4 times as much storage on the new ones in the future . . . .-- RegardsTony The DBA |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2007-01-05 : 08:41:39
|
quote:
"I have to admit, I really enjoy working with databases."
thats what I once said before I started loosing my hair, friends, girlfriend and everything else that meant something to me  |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-07 : 13:44:11
|
quote:
Originally posted by KristenHave a look at CONTIG.EXE from Sysinternals for that job. SQL Server service has to be stopped, or the database detached, to be able to defrag the physical file - and back it up first!! - so you need some scheduled downtime for that one)Kristen
I was reading up on the contig.exe. Very interesting little utility. Just out of curiosity, how would you recommend using it? Do you actually use it against the actual *.mdf file?Would you also use against *.ldf files as well?THanks |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-07 : 20:10:47
|
| "My next step is to modify this to use indexdefrag instead of reindex when its more appropriate."no concrete evidence that it is ever more appropriate. in fact, i will argue that if u can re-index in less than 2 minutes, then u should abandon all thoughts of ever defragging indexes"Yeah thats what the Storage consultant tells us, along with the fact that Log FIles don't need their own disk group, and we can store filestore and exchange files (well they are database files) in the same disk group ad the database files."time for a new consultant |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-08 : 02:58:17
|
| "contig.exe"I use it on the physical LDF and MDF files - when they are offline, and only once they have grown to "reasonable working size".After restoring to a new server and before putting it live is a good time! otherwise every 2 months or so when we have scheduled maintenance.Kristen |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-08 : 13:02:27
|
quote:
Originally posted by TonyTheDBAYeah thats what the Storage consultant tells us, along with the fact that Log FIles don't need their own disk group, and we can store filestore and exchange files (well they are database files) in the same disk group ad the database files.
That is like a receipe for disaster.  |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-01-08 : 13:24:53
|
Managements view is that we are paying them a lot of money so they must be right, after all they have access to a lot of resources that we don't have. I have just noticed some symptoms that might be due to the IO Subsystem being swamped on our existing SAN, SO I'm hoping to run the IO Stress test stuff that was posted here a couple of weeks ago and see what figures I get.Problem is that they are configuring the disk groups on the new SAN this week, and If we have to re do it I am not going to be Mr Popular (*), due to the number of projects waiting for the SAN(*) I know its in the job description to be obstructive and Uhhelpful At least I'll be able to run a SQL Bechline when we move to the New SAN, and hopefully before the second site gets built, we can have some meaningful stats that will prove that we need to do it another way.  -- RegardsTony The DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-08 : 14:08:05
|
| "Managements view is that we are paying them a lot of money so they must be right"If you want to pay me EVEN MORE money I'm happy to be EVEN MORE right! |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2007-01-09 : 17:23:36
|
| Just a follow up.I was reading this article:[url]http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspx[/url]When something caught my eye. This snippet here:Paul Randal's response: “All you need to do is see which queries are using which indexes for range scans rather than singleton lookups. These are the indexes that you should concentrate on“.Seems like a good idea.However, I am not quite sure how do find out what queries are using which indexes. SQL Profiler would seem to fit the bill here. Since I am young and learning, does anyone have suggestions on how to go abou this?Thanks,TCG |
|
|
|
|
Question on defragging databases