Solved

SQL Server Maintenance on Server

Posted on 2011-09-22
12
265 Views
Last Modified: 2012-05-12
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
Comment
Question by:AmericanBridge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
12 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36583270
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
 
LVL 1

Author Comment

by:AmericanBridge
ID: 36587608
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36587903
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 1

Author Comment

by:AmericanBridge
ID: 36814587
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36815079
>>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
 
LVL 40

Expert Comment

by:lcohan
ID: 36892881
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36893208
>>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
 
LVL 40

Expert Comment

by:lcohan
ID: 36893267
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
 
LVL 1

Author Comment

by:AmericanBridge
ID: 36927421
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36928087
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36928090
Let try that again:
The link is here: http:#a36815079
0
 
LVL 1

Author Comment

by:AmericanBridge
ID: 36931094
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question