Solved

SQL Maintenance Plan - Check Database Integrity

Posted on 2008-10-26
7
1,069 Views
Last Modified: 2012-06-27
I have a new database server with 64 Gigs Ram.
I created a Maintenance plan which runs "Check Database Integrity" every night.
When that check runs, my Ram useage goes from 30 Gigs to 60 gigs.
It then remains at 60 gigs unless I stop/stop SQL.

My question:
Is it overkill to run the checks every night?
Is it normal to use so much ram to do the checks.
Does use of that ram affect performance?  (It appears that I start getting more paging after the checks)

My system is Windwos 2003 x64  with MSSQL 2008 x64
0
Comment
Question by:superbrian
  • 4
  • 2
7 Comments
 
LVL 4

Accepted Solution

by:
randy_knight earned 500 total points
ID: 22807675
If you are seeing paging, I'd recommend reducing the Max memory for SQL Server.  How big is your database?  Your memory usage is growing because it's moving all those data pages into cache as it runs the integrity checks.

Yes, it is normally overkill to run all integrity checks every night.  However, which optiosn do you have chosen?
0
 

Author Comment

by:superbrian
ID: 22808749
This server was just setup and installed and I just created the maint plan.
There are several databases on this server totally 46 gigs.
The server has 64 gigs or ram and is dedicated to sql, so it's using more ram that the total of all databases combined.  When Sql is started (or restarted) ram usage climbs slowly to about 40 gigs then stops, until integrity checks run.
Not sure what you mean by options but it says include indexes.
I can simply set this up to run weekly, but without a sql restart it may just continue to cause paging?
As for reducing max sql memory, to what would you suggest?  60 gigs?
0
 

Author Comment

by:superbrian
ID: 22808757
I also do not have AWE enabled, I did on my prior server, since it was the only way to address over 2 gig ram.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 63

Expert Comment

by:SysExpert
ID: 22808831
THe server should be self balancing, so it really should not matter if it sits at 60 GB the whole time, as long as there is no paging to disk.

I would check the MS site for any additional info on how often  to run checks if it worries you, but if you watch long term, and no disk paging occurs, leave it alone.

just my $.02

 
I hope this helps !
0
 

Author Comment

by:superbrian
ID: 22811302
I changed the checks to run weekly, I guess I'll need to wait a week to see if the check consumes all the memory and causes paging again.
0
 
LVL 4

Expert Comment

by:randy_knight
ID: 22813639
I assumed this was a 64-bit OS/SQL Server.  True or False?
0
 

Author Comment

by:superbrian
ID: 22815152
Yes.  2003x64  sql2008x64
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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