Solved

SQL Maintenance Plan - Check Database Integrity

Posted on 2008-10-26
7
1,057 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
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 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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