Solved

SQL Server Maintenance on Server

Posted on 2011-09-22
12
218 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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
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
 
LVL 1

Author Comment

by:AmericanBridge
Comment Utility
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
Comment Utility
>>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 39

Expert Comment

by:lcohan
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
Let try that again:
The link is here: http:#a36815079
0
 
LVL 1

Author Comment

by:AmericanBridge
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Having clause with Case 2 30
Problem with SqlConnection 5 108
Need help with a query 6 53
Grid querry results 41 51
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now