Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

SQL Server Maintenance on Server

We have a Windows 2003 Server running SQL Server 2005, 32-bit all around. I am wondering what types of regular maintenance should be done on the server itself. For example, is defragmenting the disk itself ok? Will this affect the SQL databases? should the databases be offline, etc? What other maintenance should be run on SQL Server?
0
AmericanBridge
Asked:
AmericanBridge
  • 4
  • 4
  • 4
1 Solution
 
lcohanDatabase AnalystCommented:
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.
0
 
AmericanBridgeAuthor Commented:
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
0
 
lcohanDatabase AnalystCommented:
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/
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AmericanBridgeAuthor Commented:
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

Open in new window

0
 
Anthony PerkinsCommented:
>>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/
0
 
lcohanDatabase AnalystCommented:
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.

0
 
Anthony PerkinsCommented:
>>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...

0
 
lcohanDatabase AnalystCommented:
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.
0
 
AmericanBridgeAuthor Commented:
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?
0
 
Anthony PerkinsCommented:
>>NOTHING CHANGED? I still have some indexes at 100% fragmentation....<<
And that will not change.  They are probably too small to be significant.

>>Maybe there is a better way with scheduling a certain script? <<
Absolutely, I gave you a link to the script here: http#:#a36815079
0
 
Anthony PerkinsCommented:
Let try that again:
The link is here: http:#a36815079
0
 
AmericanBridgeAuthor Commented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now