notification server 7 and sql 2005 high cpu

[Migration User]

Hi everybody,
 
We have box1= w2003 server that host notification server 7 and a box2= w2003 server hosting sql 2005.
 
I am been monitoring the server during last two week and box2, in task manager under processes, sqlsvr.exe is using between 80 to 100 % of cpu most of the time
 
I do not know much about sql and I am wondering about a simple query or other task to find out what is causing this cpu high demand from Notification server, I checked the log files, defrag server but no changes
 
I will appretiate any guideline
 
thanks

[Migration User]

Sounds normal.  Are you seeing any performance problems in Altiris?  SQL server will usually expand to use as much processor and CPU as are available.

If you are concerned about maintenance and tuning, check out this KB:
https://kb.altiris.com/article.asp?article=38917&p=1

High CPU/memory utilization is normal.

[ianatkin]

SQL Server does tend to sponge up any free memory, as it likes to keep the db and previous queries in cache where possible. This is so that when there is work to do, it can be more efficient. The fact that you are sweating your box at a constant 80-100% CPU is however something I'd be worried about. Generally, I'd be thinking that the specification of your SQL Server isn't upto the job.

What is the spec of your SQL Server? And how many clients do you have?

For starters, I'd be thinking about at least a 64-bit server, with dual 3GHz box with 8GB of RAM. Fast storage too, to keep up a good I/O.

Kind Regards,
Ian./

[Migration User]

Thank for the reply,

I am going to look into the 38917 article, thank mclesmson,

Ianatkin, The sql server is in a windows 2003 enterprise server, in a virtual environment (vspher 4) it is using 4 cpu, and 8GB of memory in a tier1 storage similar to NS. There are about 2500 clients

Also we notice that If we want to run a report under application metering in Notification server, the right pannel just stay on "Please wait" indefinably until time out or we just cancel, I am not sure if these two issues are related somehow.....

Thanks for your help in advance

[ianatkin]

Hi,

It sounds to me that your spec should be fine. I take it that as you've got 8GB in the server, its 64-bit .... ;-)

I'd reckon the SQL server is being given a lot of work to do, could be a bug, or could be a configuration problem. How often are your clients configured to report in?

[Migration User]

Is your SQL server dedicated to Altiris?  If not, how many other services have DBs hosted there?

[Migration User]

Hi,

sorry I did not mention that it was 32 bit server, and the clients configured to report in every  hour..and the Altiris sql is dedicated to Altiris DBs only

any query I could run in sql or in ns to narrow down!!!

Thanks

[ianatkin]

I think you might be running still on the default update schedules. Please look at the following:

1. Check you collection update frequency. In "Settings -> Notification Server Settings -> Resource Membership Update" confirm that you've changed you delta and policy update schedules from the default value of 5 minutes. Configure this to be hourly.
    
2. In your agent settings, configure them to update with the server every 2 hours.

If you were at the 5 minute defaults, and you should notice SQL usage drop off pretty quickly.

And are you saying that your SQL Server is 32-bit? If so, in the longer term I would recommend an upgrade to 64-bit Windows and 64-bit SQL. You'll notice a goodly performance gain if you do.

Kind Regards,
Ian./

[Migration User]

I went to update the Resource Membership Update to be hourly (it shows 5 min as you mentioned), but I got the following error (so I can not modify the entry at this time): In the webpage: Request time out exception details: system.web.httpexception: Request timed out An unhandled exception was generated during the execution of the current web request........... Strack Trace: [HttpException (0x80004005): Request timed out.] .............................. In the event log of altiris notifcation server: Event code: 3001 Event message: The request has been aborted Application information: Application domain: /LM/W3SVC/1/Root/Altiris-1/NS-7-129322832103269025 Trust level: Full Application Virtual Path: /Altiris-1/NS Application Path: C:\Program Files\Altiris\Notification Server\ Machine name: ALTIRIS-1 Process information: Process ID: 5600 Process name: w3wp.exe Account name: NT AUTHORITY\NETWORK SERVICE ........................................... I am surfing the net for that error at this time, have you experienced similar problem.... Thank for your help so far, I think I am getting close to solve the problem

[ianatkin]

It's likely that the server is just to busy. Stop NS processing ("Settings -> Notification Server -> Notification Server", wait a minute for that to action and  try again. 

Kind Regards,
Ian,.

[Migration User]

just updating the entry::

I update the resource membership Update successfuly, I monitored the DB, but no much change at this time.....then I went to monitor the viewer and I found this two issues that could be affecting......
 

 

1.  Unable to generate policy XML for item..........Source: altiris patchmanagement core policies softwawre update advertisement.ON buildClientXml2..
 

log file:
Delivery Advertisement "WindowsXP-KB918899-x86-ENU.exe for MS06-042", Guid = WindowsXP-KB918899-x86-ENU.exe for MS06-042 (2fd2cc6d-6a94-438d-86f5-e223ff9b0e88) from the SWD tables. ---> Altiris.NS.Exceptions.AeXException: Did not get a row for Software Delivery Advertisement "WindowsXP-KB918899-x86-ENU.exe for MS06-042", Guid = WindowsXP-KB918899-x86-ENU.exe for MS06-042 (2fd2cc6d-6a94-438d-86f5-e223ff9b0e88) from the SWD tables.

at Altiris.NS.StandardItems.SoftwareDelivery.AdvertisementItem.OnBuildClientConfigXml2(Guid workstationGuid, XmlNode requestDocumentElement, XmlTextWriter xmlBuilder)

--- End of inner exception stack trace ---

at Altiris.NS.StandardItems.SoftwareDelivery.AdvertisementItem.OnBuildClientConfigXml2(Guid workstationGuid, XmlNode requestDocumentElement, XmlTextWriter xmlBuilder)

at Altiris.PatchManagementCore.Policies.SoftwareUpdateAdvertisement.OnBuildClientConfigXml2(Guid workstationGuid, XmlNode requestDocumentElemen........................................


2. Unable to generate policy XML for item.xxxxxxxxxxx...........Source: altiris ns agent management policyrequest loaditem policy.... proces: w3wp

log:
 

Delivery Advertisement "WindowsXP-KB970437-x86-ENU.exe for MS09-018", Guid = WindowsXP-KB970437-x86-ENU.exe for MS09-018 (52a64e20-02ed-4f48-9bf7-2ed319af9ba1) from the SWD tables. ---> Altiris.NS.Exceptions.AeXException: Did not get a row for Software Delivery Advertisement "WindowsXP-KB970437-x86-ENU.exe for MS09-018", Guid = WindowsXP-KB970437-x86-ENU.exe for MS09-018 (52a64e20-02ed-4f48-9bf7-2ed319af9ba1) from the SWD tables.

at Altiris.NS.StandardItems.SoftwareDelivery.AdvertisementItem.OnBuildClientConfigXml2(Guid workstationGuid, XmlNode requestDocumentElement, XmlTextWriter xmlBuilder)

--- End of inner exception stack trace ---

at Altiris.NS.StandardItems.SoftwareDelivery.AdvertisementItem.OnBuildClientConfigXml2(Guid workstationGuid, XmlNode requestDocumentElement,

..................

Thanks in advance for your help...



 

[ianatkin]

Hi,

Sorry -my reply was rushed. Yes, in the Altiris Console, navigate to the page "Settings -> Notification Server -> Notification Server". The screenshot below will help,

[Migration User]

thanks Ian,

I just update the entry after I submitted above, where I mentioned  error in the viewer that popup...

1 Unable to generate policy XML for item
2 Unable to generate policy XML for item

see above entry for more information

Thank you for the quick reply

Marcos


 

[Migration User]

I have has a similar issue where the my CPU on my SQL box ran at 80 – 100%. We found that it was cause buy the patch inventory. Where were applying patches from 2005 onwards we have dropped this down to 2008 and found that SQL CPU is now idling. If you also have a lot of Patch packages reducing them may help.

[ianatkin]

Hi Nick & Marcos,

With a 5 minute collection refresh, NS will be ground into the dirt in a production environment. This is a test setting only, and to be honest perhaps should not be configured so short by default.

Once we've got NS native processing calmed down, we can then see if there is a problem with patch management. It might be that with the server so busy, we will also have to recreate packages, and yes perhaps delete the ones that are so old they'll never be used in practice!  ;-)

Kind Regards,
Ian./

[Migration User]

Hi NIck, Ian

I looked into the patches and from 2007 onwards and I am going through to see if I can reduce some of them

 I am also trying to see if I can find or scan for currupted packages, policies or tasks that are affecting the server.....but so far no luck

Thanks for your comments....

[Migration User]

Did you modify the tasks schedule from 5m to 60m?

[Migration User]

Hi mclemson,

I modified the task schedule to 60 min, which should be a reasonable time....but  there was not much of a change as should be...

Then, I followed some advise from Ian Atkin (thanks Ian), and I realised that sql server is not using the proper amount of memory that should be according to sql range (curent reading is just over a 1GB only) and this is a virtual sql server with 8GB available... so I will troubleshoot into that before I continue looking into other things....

Thanks

[Migration User]

Hi,

So far no much luck!!!!!!!!!!! I have upgrade to windows 2008 server and sql 2008, restored database, refresh notification server and talking fine to sql

I checked cpu usaged= over 95 % and memory around 1GB

I am wondering if there is something else with the database itself that is affecting the cpu overload by sql constantly

Any comment will be welcome

Thanks

[ianatkin]

Can you confirm a couple of extra things for me?

1) Can you confirm your actual database size (just take the mdf size)
2) Are you  using the full version of SQL Server 2008? The express version is limited to 1GB of RAM you see.

[Migration User]

Hi,

the database mdf size is 25GB

The new server is a Windows 2008 standard 64 bits with 2 vCPU, Memory allocated at this time 8 GB memory in virtual environment with MS SQL server 2008 standard edition 64 bits

I ve been monitoring the Performance (task manager) this morning and the reading are:  processes:39   CPU Usage: 100%   PHysical Memory: 80% (6 GB approx.)


thanks

[Migration User]

 so far still same picture with high cpu usage, we are waiting for a respond from support to check some data on NS, if still no luck probably we would need to create a clean DB for NS and see how it perfoms

Thanks

[ianatkin]

Hi Marcos,

Have been away for a few days, so apologies for lack of response. It looks like you have multiple problems.

• 
  sqlserv.exe not being able to consume more that 1GB of RAM
• Huge database
• something else!!

The problem is to tackle the something else, its best to get resolved the known issues.

SQLSERVE.EXE : To tackle the SQL problem, you really need to talk to a SQL admin and see if anything is limiting SQL.

25GB DB : From the database point of view I think a good old fashioned  purge is required. The original 'Test and Dev' schedules you originally might have bloated your database.

Take a look at Settings -> Notification Server -> Purging Maintenance -> Resource Event Data Purge Settings

The default retention is 6 months. You'll want to reduce this in stages of one month each day to get your DB down to size.  Then bring this down right down to 1 week data retention if need be. When the database size is under control, you can put the retention period back  up to 6 months.

In the light of your CPU utilisation, I would see if you could execute this purging whilst NS processing is paused. This is going to need a lot of hand holding.

Once the above is sorted, it will probably be a lot easier to sort out the remaining issue(s). And when all this is done, make sure you've got good database maintenance plans in place!

The approach of a clean DB has its merits. Will certainly be a faster resolution to your DB size issues. Though you'd still in my mind need to figure out what's been limiting your sqlserver to 1GB RAM.
 
Kind Regards,
Ian./

[Migration User]

Hi Ian, thanks for the reply...

Regarding to sqlserver memory is reaching around 7MB more or less (I allocated 8GB), so that is good news somehow. the Cpu still the same 100%.

I went to update the Purging Maintenance page this afternoon (it was 6 months), but I am getting "offsetParent" is null or not an object error in the webpage, so I will find out about that and try to solve before I can continue....as you mentioned it needs a lot of house cleaning... :)

Thanks for your comments

[ianatkin]

Hi Marcos, have Altiris support asked you to run SQL profiler to see why SQL Server is so busy?

[Migration User]

Hi Ian,

No at this time, but is one of the things I was reading about it

Thanks
 

[Migration User]

This KB article should be a sticky I think!

https://kb.altiris.com/article.asp?article=51819&p=1

I read the the reponses above and assumed this was not a known issue when in fact it is and the fix is there.
I'm attaching our performance graphs to illustrate the cpu usage before and after applying the fix. Night and day.
I started the migration around June 18th and you can see the usage immediately spiked to 80% and remained high until today at
around 8:15 when I applied the fix.

Also, I'm doing a staged migration, and have only 400 PCs talking to the new NS so far. So I can imaging it would have been close to crippling if I had done all 2500 at once.
 

[ianatkin]

Hi Artusod and Marcos,

Wow -bit of a bug there and am too surprised there hasn't been some escalation on this, and that Altiris Support didn't point Marcos in the right direction.

Marcos -I'd certainly give this one go. 

Kind Regards,
Ian./

[Migration User]

Firt of all, thanks for your comment Artusod and Ian for you patient, and regarding to "Run the updated stored procedure", I will do it after business hour and also looking into sql profiler..

By the way, one of the option that was rised today was to rebuild the database and the first thing I asked myself is: if I have to recreate de database, how is going to impact the current machine that have altiris agent, these are going to communicate back to NS as normal and populate de database again or there is something else I have to be aware off.....I do not know if you have that experience where you needed to rebuild the database...

Thanks you, and I let you know how it is going with the test

 

[Migration User]

We have been through different test and so far No good news. We also received a msg from symantec support that suggested two options, continue escalated or rebuild db. Unfortunately the issue has been there for a while and we need to take a resolution, therefore we are going to rebuild (to stop affecting daily environment)  the system and hopefully that will fix all the bugs......

I will update the outcome and a big thanks for your support Ian, Artusod...

have a good weekend

[Migration User]

Hi,

We rebuild the NS server, fresh DB, so far running ok and client machines reporting back to the server, monitoring sql server and so far cpu usage performing ok

Therefore, we could say that all the bugs are gone at this time (finger cross) and thanks for your help, much appreciated

Regard
Marcos

[ianatkin]

Good to hear it, and sorry you've had to go the route of a full rebuild to resolve. Let's hope it get's easier from here on out...

[Migration User]

Or are you running the exact same configuration as before?

[Greg_Net]

Hey I just had a chance to read all of the concerns surrounding high CPU on your SQL server.

I too have set my Agent check in to 2 hours and Resource Membership to an hour.  The following are additional steps:

Build Maintenance Plans on the SQL server for your Altiris db
  Check your options to Rebiuld Indexes and Update Statistics.


I have an XML error but it's only after I attempt to open an Alert Detail of the Hierarchy Server Allerts - I have not bee able resolve this concern.