Video Screencast Help

Excessive Database Usage - What is normal?

Created: 29 Mar 2011 | 4 comments
mickwearn's picture

color:black">Hi Folks,

color:black">I am trying to gather some information about what is normal database activity for Altiris and also the tempdb

color:black">What we are seeing in our environment is the SQLServer: SQL Statistics: Batch Requests/Sec Counter is peeked constantly at 100% (see attached)

color:black">Also when we look at the temp database activity via Resource Monitor (Server 2008) we can see the Read/Write is around 4,800,000 (B/sec)

color:black">However we moved the temp database from local disk to the NAS (via SLAN) and this decreased performance as the NSA disk is slower.. when the temp database was on local disk the Read/Write is around 45,000,000 (B/sec)

color:black">So I am very interested to know how this looks on other peoples installations?  is this normal activity for Altiris? or perhaps do we have a miss configuration?



Comments 4 CommentsJump to latest comment

Lery's picture

A couple of things come to mind.

1)  You moved tempdb to a SAN.  Has your server team used SQLIO or any tool to determine the load on the SAN before you actually put tempdb on it?

2)  Where is the physical database located?  Same disk as SMP or different disks?  What kind of disks.

3)  What kind of RAID is setup for the hard disks that hold the database?

5)  How many clients are you supporting

6)  VM or physical

Those are just quick off the top of my head.  I am sure there are more questions that need to be asked.

mickwearn's picture

Well we would love to move to a SAN, but we don't have one,

The SQL server is off box

Raid 10

1600 Clients

its all physical. (Server 2008 R2 with SQL 2008 R2)

I guess my question is more about what is normal performance.. I understand that performance could be affected by any number of things.  I would really love to hear from people who are supporting about 1600 clients, with a typical CMS installation... if they could run the SQLServer: SQL Statistics: Batch Requests/Sec Counter and report on the statistics

To see if we are even close, to perhaps provide some idea on whats going on

Lery's picture

I apologize, you wrote NAS but my brain said SAN.

I cannot say what performance you should see, but, with 1600 clients you should easily not see too much overhead.  It all depends on what you are doing with the server.

Do you have at least one site server running Task Services?  If not, then I would have to assume your NS is also servicing tasks.  This will absolutely kill your Notification Server.  So if Task Services is not off loaded to a site server, consider that.

How much memory is on the server?

Finally the disk subsystem on the server is important.  I would like to refer you over to an excellent article by ianatkin, located here:

The important thing I would try to suggest is that everyone is setup differently which could dramatically change your experience with the product.  Person A could have 1600 clients and could see great performance, while the next person has terrible performance.

mclemson's picture

According to Microsoft, a high batch requests/sec means good thoroughput:

What problem are you experiencing?

I would look at this document for references to SQL articles on performance tuning:

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner