August 5, 2008 at 10:31 am
Hi all,
I did my first install of an enterprise version of SQL 2005 a couple of weeks ago for a software package relative to our email system (Specifically for archiving emails)...
Everything seemed to go good and there were no issues...
Fast forward to this week and I started to notice backup failures...ect so I started to look around the server and noticed the CPU Utilization was 99% consistently by the sqlservr.exe process. Jobs that were previously taking a small amount of time were now taking double or triple the time.
jumped into Q.A. and did a sp_who2 and it didn't show to much...only one or two processes with High CPU and Disk I/O
Doing a DBCC inputbuffer () on the processes didn't yield much info...
SQL server is utilizing 7.2 gb's out of the 8 gb's available...
The exchange admin is thinking it may be an issue with a recent update from the software vendor and he is dealing with them on that end...
From my end I would like to take this opportunity to learn how to dig in and see WHAT is making SQL server us 99% of the CPU...
If it is a sum of a bunch of small processes...that are running over and over...or if it is something bigger...
Can any of the seasoned vets here give me some guidance? I would really appreciate it!
EDIT Here is some additional information regarding the physical server setup
Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Thanks in advance,
Lee
August 5, 2008 at 11:34 am
Have you set the max memory for the SQL Server? I had the same issue and that resolved it. It will starve the OS if given the chance. I left around 1.5 GB reserved for the OS. I didn't notice the issue until we installed SP2.
August 5, 2008 at 11:43 am
Initially I didn't set anything, forgetting for a moment that SQL server Enterprise edition uses more than the standard install...
Once i realized that so much of the available memory was being eaten up by the SQL server I went into the server properties and changed it to 6 GB's max
I haven't restarted the SQL service yet
At the moment there is around 200 mb's free in the System Cache...
Question: So you experienced the same symptoms as I am right now and setting a max limit on the memory usage fixed the HIGH CPU usage issue?
Or were you experiencing a different type of issue that was fixed by setting the max memory limit?
Thanks,
Lee
August 5, 2008 at 11:49 am
I wouldn't say that the CPU was 99% all the time, but when there was heavy usage on the SQL Server, it would have large spikes in CPU usage that would take a while to settle down. You shouldn't have to restart the service. That is one of those properties that doesn't require it. The slowdowns were getting pretty substantial and we noticed pretty significant performance gains once the max memory was set. I also read somewhere that you should set min memory as well, so I did that too and we are running fine now.
August 5, 2008 at 11:57 am
By the way, I don't know if you use this or not, but Idera SQL Diagnostic Manager has been really useful in determining where the issues are. It works a lot better than the performance monitor of the server in that it shows you internally what the SQL Server is using.
August 5, 2008 at 12:50 pm
I did set the MIN and MAX memory limits and I am noticing a SMALL change in the amount of CPU usage down to 90% from 100%...
I ran the PROFILER to see what sp's were being called...and there are a couple of re-occurring calls that are taking over 10 to 15 seconds...
I can't evaluate the code as they are delivered procedures from the application and are encrypted
August 5, 2008 at 1:54 pm
A couple of things you should look at:
1) Setting MIN/MAX memory (which you have already done)
a) Make sure MIN is at least 1GB less than MAX
b) Make sure MAX leaves enough for the OS
2) Lock pages in memory
a) This needs to be defined for the account running SQL Server
3) If your system utilizes tempdb a lot, create additional files. Up to 1 per CPU is the recommendation, but you can start with half. For example, if you have 2 quad-cores (8 physical) - add 3 files to tempdb (all the same size, with the same autogrowth setting).
See: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
4) Make sure the network connections are set to 'Maximize data throughput for network applications'. Go to network connection properties, File and Printer Sharing for Microsoft Networks Properties.
5) Make sure memory usage settings are set to: Adjust for best performance of Programs
Right-click My Computer, Properties, Advanced, Performance, Advanced
That should give you a good start at making sure the system is configured appropriately.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2008 at 2:17 pm
One of my coworkers reminded me that another thing we did to fix the CPU spikes was to patch the SP2. SP2 by itself seemed to have performance issues. The version we are using that seems to be working pretty well is 9.0.3239.0.
That is cumulative update package 7 for SQL Server 2005 SP2.
August 5, 2008 at 2:43 pm
Jeffrey Williams (8/5/2008)
A couple of things you should look at:1) Setting MIN/MAX memory (which you have already done)
a) Make sure MIN is at least 1GB less than MAX
b) Make sure MAX leaves enough for the OS
2) Lock pages in memory
a) This needs to be defined for the account running SQL Server
3) If your system utilizes tempdb a lot, create additional files. Up to 1 per CPU is the recommendation, but you can start with half. For example, if you have 2 quad-cores (8 physical) - add 3 files to tempdb (all the same size, with the same autogrowth setting).
See: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
4) Make sure the network connections are set to 'Maximize data throughput for network applications'. Go to network connection properties, File and Printer Sharing for Microsoft Networks Properties.
5) Make sure memory usage settings are set to: Adjust for best performance of Programs
Right-click My Computer, Properties, Advanced, Performance, Advanced
That should give you a good start at making sure the system is configured appropriately.
1. I had already set the Min and Max values for RAM (1 GB's & 6 GB's)
2. I did configure the domain account for the "Lock Pages In Memory" setting
3. The Tempdb hasn't grown since I originally sized it, but thanks for the article
4. I believe these are set correctly per our network team "says so: 😀
5. I believe these are set correctly per our network team "says so: 😀
August 5, 2008 at 2:51 pm
Lee Hart (8/5/2008)
1. I had already set the Min and Max values for RAM (1 GB's & 6 GB's)2. I did configure the domain account for the "Lock Pages In Memory" setting
3. The Tempdb hasn't grown since I originally sized it, but thanks for the article
4. I believe these are set correctly per our network team "says so: 😀
5. I believe these are set correctly per our network team "says so: 😀
Well, you've got all of the basics covered. Now, it's probably a matter of isolating what queries are causing the problems.
BTW - do you have any SSIS packages running on this server?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2008 at 2:56 pm
Jeffrey Williams (8/5/2008)
Lee Hart (8/5/2008)
1. I had already set the Min and Max values for RAM (1 GB's & 6 GB's)2. I did configure the domain account for the "Lock Pages In Memory" setting
3. The Tempdb hasn't grown since I originally sized it, but thanks for the article
4. I believe these are set correctly per our network team "says so: 😀
5. I believe these are set correctly per our network team "says so: 😀
Well, you've got all of the basics covered. Now, it's probably a matter of isolating what queries are causing the problems.
BTW - do you have any SSIS packages running on this server?
No SSIS packages running at all on the server...
I believe this has something to do with a recent update to the software package but I have to wait to hear more from the exchange person I am working with on this issue...
I am not the greatest when running the profiler...and an added layer of complexity is that all the procedures are encrypted so I don't think I can review the queries from the stored procedures...I can't even right click on them and Modify or Create...
That is just part of the software package for compliance of the archiving process, but that doesn't help me figure out what is going on with the server!
Thanks For the references Jeffery
Lee
August 5, 2008 at 3:21 pm
Lee Hart (8/5/2008)
forgetting for a moment that SQL server Enterprise edition uses more than the standard install...
both SQL 2005 Std and Ent address RAM up to the OS limit.
Lee Hart (8/5/2008)
noticed the CPU Utilization was 99% consistently by the sqlservr.exe process.From my end I would like to take this opportunity to learn how to dig in and see WHAT is making SQL server us 99% of the CPU...
the only thing i have seen before similar to this is when you first install SQL 2005 it tries to go out and contact MS. Either allow the server out to the internet or disable the SQL customer response (otherwise it whacks the CPU like you wouldnt believe).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 5, 2008 at 3:44 pm
I still think part of the issue is the SQL Server version. We were on the version you are on when we were having issues.
August 5, 2008 at 10:25 pm
Perry Whittle (8/5/2008)
Lee Hart (8/5/2008)
forgetting for a moment that SQL server Enterprise edition uses more than the standard install...both SQL 2005 Std and Ent address RAM up to the OS limit.
I have to admit I wasn't aware of that in SQL 2005, I have had most of my experience with SQL 2000 standard edition and Windows 2000 and 2003 standard...where the most SQL Server would take is 1.8 GB's in my experience...I am kind of running without a net on 2005 which is always exciting...NOT
here is an article confirming your statement
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Perry Whittle (8/5/2008)
Lee Hart (8/5/2008)
noticed the CPU Utilization was 99% consistently by the sqlservr.exe process.From my end I would like to take this opportunity to learn how to dig in and see WHAT is making SQL server us 99% of the CPU...
the only thing i have seen before similar to this is when you first install SQL 2005 it tries to go out and contact MS. Either allow the server out to the internet or disable the SQL customer response (otherwise it whacks the CPU like you wouldnt believe).
How do you double check to make sure that customer response is disabled... I believe I killed that off initially but I certainly would like to make sure
hill4m (8/5/2008)
I still think part of the issue is the SQL Server version. We were on the version you are on when we were having issues.
I briefly looked into that cumulative security patch and have to talk with my manager regarding the application...maybe it will yield some positive results...
Thanks guys for steering me in the right directions!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply