How to Monitor Index Fragmentation in Spotlight on SQL Server Enterprise

1
How to Monitor Index Fragmentation in Spotlight
on SQL Server Enterprise
SQLDBApros
2
Why should you care?

• Index fragmentation happens when a new record is
  inserted into a current database.
• It is like adding a new name to a phone book,
  there is no room and the name has to be placed
  out of order.
• Or taking a name out of a phone books, then there
  is a blank space, making the phone book longer
  than it needs to be.
• Therefore taking longer to read it, back it up,
  and recover it.
• If you do not defragment your indexes, your
  database has to cache all of those blank records,
  wasting memory.
• Many defragmentation scripts are set to run as
  part of a maintenance routine, whether they need
  it or not.
• This causes extra items on the transaction log,
  the longer the backups take, the longer the
  restores take.
• Spotlight on SQL Server alerts you when
  defragmentation needs to occur

3
Monitoring index fragmentation

• Bad internal fragmentation (a lot of empty
  spaces) and bad external fragmentation (a lot of
  records out of order) make the index longer than
  it needs to be and therefore storage performance
  is slower.
• Setting a lower page fill filter (such as 50)
  makes your inserts incredibly fast, but your
  reads are twice as slow.
• Tweaking this fill filter is a dangerous game
• Spotlight on SQL Server will show you if the root
  of your problem is the fragmented indexes and how
  critical they are
• Requiring an entire rebuild
• Or just a defragmentation

4
Learn more with the following video

• The following video teaches you how to monitor
  index fragmentation in Spotlight on SQL Server
  10.0
• New features in version 10.0
• Alarm that tracks whether large indexes require
  rebuilding
• Home page metrics that allow you to easily
  drilldown for details
• Quickly generate defragmentation script
• How to set up customizable alarms
• How to view easy home page metrics
• How to get tailored scripts for your
  defragmentation jobs

5
Click to watch the video now
6
Learn MoreDownload Spotlight on SQL ServerView
Tutorial on YouTube