Indices fragmented

• 
  I should also add that once a week on a Saturday we run a weekly maintenance plan that rebuilds the indices (amongst other tasks) of all db's in that instance. SSMS reports the plan has run successfully for the last month.

  Markb February 26, 2008 09:30

  - 0 +
• 
  Hi Mark,
  SQL Response currently uses a DBCC SHOWCONTIG (DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, FAST to be exact) and we then look at the logical scan fragmentation and use that to send a recomendation if an index or table is above a certain size and the fragmenation is "too high".
  
  
  (Note that query can create locks on SQL Server 2000 which can cause blocks so we'll be modifying it slightly for final release to one that won't)

  Nigel Morse February 27, 2008 05:29

  - 0 +
• 
  Hi Nigel
  
  i work alongside Mark and have read your reply but the following query has now arisen.
  
  i rebuilt all the indexes on one of the tables reporting a high logical fragmentation when running the T-SQL
  
  DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, FAST
  
  I tried using a fillfactor of 85 (instead of SQL instance default of 0) and whilst this did make a difference it was very minimal (from 87.5 down to 77.66), what is the size value you are using to determine fragmentation?
  
  DBCC SHOWCONTIG now uses a different algorithm as opposed to SQL2000 and the way fragmentation is calculated has changed drastically, the values will always appear higher. Is this something that the software takes into account?
  
  It may be that we need to re visit our settings for fillfactor usage but it doesnt seem to have a huge impact, do you have any recommendations?
  
  Regards
  
  Perry

  perrywhittle February 27, 2008 06:33

  - 0 +
• 
  Hi - sorry to take so long to get back to you.
  
  Could you possibly send me the output of the DBCC SHOWCONTIG for the table or indicies in question please?
  
  Also could you let me know what you used to rebuild the indices?
  
  Thanks,
  Nigel

  Nigel Morse February 29, 2008 09:29

  - 0 +
• 
  i will get those details and post back, in relation to my original question is the application taking into account the algorithm changes in SQL2005?

  perrywhittle March 03, 2008 10:44

  - 0 +
• 
  Thanks for looking at getting me the values.
  
  As for your question the short answer is that no, we don't allow for any changes.
  
  However as I understand it the 2005 algorithm is simply more accurate in it's calculations of fragmentation when pages are in different extents, which means it may report higher, but more "accurate" values.
  
  So if my understanding is correct then perhaps we should use a lower threshold for 2000 rather than a higher one for 2005?

  Nigel Morse March 03, 2008 11:54

  - 0 +
• 
  Nigel
  
  the following statement was ran against a database that flagged in SQL Response as having high fragmentation.
  
  DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, FAST
  
  I found many tables that reported high fragmentation and picked one of these at random.
  
  The following T-SQL made no difference to the logical fragmentation
  
  ALTER INDEX ALL ON DAA_Adequacy REBUILD
  
  i then tried specifying the fillfactor rather than use the sql server default (set to 0)
  
  ALTER INDEX ALL ON DAA_Adequacy
  REBUILD WITH (FILLFACTOR = 80)
  
  this made a difference of about 10 dropping from 87.5 to 77.77777778
  
  not a substantial drop and may not be enough to stop sql response picking it up again. If you would like the output from dbcc showcontig please could you supply me your email address. I would appreciate your advice
  
  Regards
  
  Perry

  perrywhittle March 03, 2008 12:16

  - 0 +
• 
  Nigel
  
  upon further investigation i think we may need to use a different fill factor to ensure that pages have ample free space for the indexes to grow. What is the threshold value sql response is comparing too for fragmentation alerts too occur, i can get the logical frag down to 26 by specifying the fill factor leave 70% of each page free but i dont want to go to that extreme, Thank you for your help
  
  Regards
  
  Perry

  perrywhittle March 03, 2008 12:28

  - 0 +
• 
  in my initial for Sql Response I noticed that many of it's initial tasks seem to be simply very slow. both Open Inidents and Recommendations take a very long time to load up. Is the system monitoring independently or only while I select the server?
  
  btw, it may be possible that it is slow because my database has > 68K tables. Perhaps a way to cache some of this data would be helpful???

  fhtapia March 11, 2008 19:08

  - 0 +
• 
  Hi,
  The Incident Repository is monitoring all the time and stores the incidents locally to itself. When you open the client it then asks the repository for the incidents/recomendations. The speed here depends on mainly incidents/recomendations you have and how fast the machine with incident repository is. (It will also depend on many servers it's monitoring and how busy those servers are). Having lots of tables shouldn't be a problem - it might take a while for some recomendations to be checked (indices being a good example)
  
  When you say "initial" tasks do you find it runs ok after it has done the first load of incidents when starting the client?

  Nigel Morse March 12, 2008 04:25

  - 0 +
• 
  Well it seems to be slightly faster once it's loaded for the time. The initial time it takes over 30seconds, but after that I would say it takes about 5 seconds.
  
  
  One question on the recommendation of excessive Free space. Some of these databases are pre-grown out so that the data will grow to the size of the file. We generally grow the database once a month. Can this tool trend the growth? or provide a report of current growth, I don't see that anywhere in the menus.

  fhtapia March 12, 2008 12:48

  - 0 +
• 
  The monitoring process, when does it collect the stats for the server? How do I change the timing?

  fhtapia March 12, 2008 12:49

  - 0 +

Add comment

Please sign in to leave a comment.