SQL Server Maintenance on Server

Hopefully you have at your DB files installed on some sort of RAIDs not just on phisycal hard disk therefore this is not something you should worry about.
Here's what I would do at a minimum from a dba poit of view:

Make sure if you run antivirus that your db files(mdf, ndf, and log) are not scanned
Make sure you have good (disaster)backup/resore plans and keep a copy your backup files safe - not on the server disks. backup your T-logs periodicaly so the db's don't grow uncontrolled.
Make sure you have a refresh stats/reindex done periodicaly.

We have all of the databases on a SAN so it's all RAID. Are you saying I don't need to defrag those drives then?
Good disaster recovery and backup in place.
Refresh stats/reindex is done periodically

I know there are lots of pros/cons articles on the web about thi topic but in my opinion that is correct - you don't need SAN defrag and I suggest check with your vendor before running it against production LUNs.

In my environment of 7TB of SQL DBs behind a e-commerce  web site I have not heard about SAN defrag and in my oppinion what you should worry about is finding your SQL Indexes fragmentation and REBUILD/REORG if needed:

http://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspx
http://blog.sqlauthority.com/2008/03/27/sql-server-2005-find-index-fragmentation-details-slow-index-performance/

This helps at the table level.

I ended up using this script in order to check fragmentation on indexes of over 30%

After that, I scheduled a weekly index rebuild for items over 30% fragmentation, then a reorganize for those under 20%. I just used the built-in maintenance utility.

SELECT object_id AS ObjectID,
  index_id AS IndexID,
  avg_fragmentation_in_percent AS PercentFragment,
  fragment_count AS TotalFrags,
  avg_fragment_size_in_pages AS PagesPerFrag,
  page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID('JDE_DEVELOPMENT'),
  NULL, NULL, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent < 25
ORDER BY ObjectID, IndexID

Select allOpen in new window

>>After that, I scheduled a weekly index rebuild for items over 30% fragmentation, then a reorganize for those under 20%. I just used the built-in maintenance utility. <<
You could have avoided that by using this script here as it takes care of all of that and more:
http://ola.hallengren.com/

I found that the REORGANIZE doesn't do much (maybe due to the high IO on my OLTP db which is behind a e-commerce website) and I personaly used only REBUILD as it's a ON-LINE operation as you can see in the pseudo code below. Besides these we run a  sp_updatestats against the DB each day after the REBUILD and so far so good - performance is great.

IF @num_recs > 1000000 and @frag > 5.0 and @indexid > 1 -- exclude clustered index
'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.' + @objectname + ' REBUILD WITH(FILLFACTOR = 80, ONLINE=ON, MAXDOP = 1)';

IF @num_recs < 1000000 and @frag > 10.0 and @indexid > 1 -- exclude clustered index
'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.' + @objectname + ' REBUILD WITH(FILLFACTOR = 80, ONLINE=ON, MAXDOP = 1)';

Other than that in terms of maintenance - daily baclups and T-Log backups through the day not just to preserve data but to keep transaction log file under control.

>>Besides these we run a  sp_updatestats against the DB each day after the REBUILD and so far so good - performance is great.<<
That seems redundant.  Unless you are using STATISTICS_NORECOMPUTE = ON,  statistics are updated when you do a REBUILD...

That may be true if we would rebuild ALL indexes but only the ones that fall under criteria described above are rebuilt and sp_updatestats is done at the DB level for all. Should I also mention about
sp_recompile table_name? This would flush from SQL cache all plans tied to that particular table_name but you need to use it with caution as first executions of those queries may be very slow after that.

Unfortunately I can't do anything ONLINE since it's SQL Standard rather than Enterprise. So my REBUILD window on a daily basis is pretty slim. Along with nightly backups and availability, I can only rebuild on indexes that are a certain percentage.

So on another note, I ran the above query to find all indexes fragmented above a certain threshold. Once I checked that I scheduled a job to run and to REBUILD all indices. After the job completed I checked the same database again with the above query. NOTHING CHANGED? I still have some indexes at 100% fragmentation....

Am I doing something wrong? I just used the built-in maintenance utility in SQL Server 2005. Maybe there is a better way with scheduling a certain script?

Let try that again:
The link is here: http:#a36815079

OK. I'll schedule that and see if it helps the performance out. Makes sense on the files with 100% fragmentation not being affected. The number of records in those tables are very small compared to others.