Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server Maintenance on Server

Posted on 2011-09-22
12
Medium Priority
?
276 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline

916 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