Server Performance During Job Execution

Question

Server Performance During Job Execution

Hawkeye_DBA

SSCarpal Tunnel

Points: 4365
More actions
December 12, 2008 at 12:50 pm

#125757

I have a question about server performance monitoring.
I notice that when I am not running a job my server performance is within acceptable limits.
However, once I kick off a job to run I notice a spike in processor time, cpu time, disk queue length, and pages/sec. The spike lasts the duration of the job run.
I just began monitoring to establish a baseline, however I am still new to performance monitoring and wish to know if this is normal?
I am running SQL Server 2005 SP3 on an aggregate 14 SATA Drive SAN. For connectivity we are using NFS.
I have more stats if needed to help with my request.
I appreciate any and all feedback!
Thanks,
Sandy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Well, it is normal for resource usage to increase as new jobs/requests are being processed by the server. This could be bad of course depending on the size and duration of the spikes. Can you tell us more about what the resource values were before you ran the job and what they were as the job was running?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 2

Absolutely!

Here is the before averages:

LogicalDisk__TotalDrives__Percent_Free_Space: 95.210

Memory__Available_Bytes: 1971428112

Memory__Pages_Sec: 0.045

NetworkInterface__MS_TCP_Loopback__Bytes_Received_Sec: 0.000

NetworkInterface__MS_TCP_Loopback__Bytes_SentSec: 0.000

PhysicalDisk__TotalDrives__Percent_Disk_Time: 0.104

PhysicalDisk__TotalDrives__Avg_Disk_Queue_Length: 0.003

PhysicalDisk__TotalDrives__Disk_Reads_Sec: 0.004

Processor__TotalDrives__Percent_Processor_Time: 1.660

SQLServerBufferCacheManager__Buffer_Cache_Hit_Ratio: 99.864

SQLServerGeneralStatistics__User_Connections: 10

SQLServerPlanCache__TotalDrives__Cache_Hit_Ratio: 94.417

System_Processor_Queue_Length: 0.000

Here are the "during" numbers:

LogicalDisk__TotalDrives__Percent_Free_Space: 86.029

Memory__Available_Bytes: 1581303685

Memory__Pages_Sec: 0.000 (however I have seen this spike to 1000!)

NetworkInterface__MS_TCP_Loopback__Bytes_Received_Sec: 80.256

NetworkInterface__MS_TCP_Loopback__Bytes_SentSec: 80.256

----------------these 2 stats I did not capture before---

NetworkInterface__VMware_Accelerated_AMD_PCNet_Adapter__Bytes_Received_Sec: 52979.904

NetworkInterface__VMware_Accelerated_AMD_PCNet_Adapter__Bytes_Sent_Sec: 29090.646

--------------------------------------------------------

PhysicalDisk__TotalDrives__Percent_Disk_Time: 5.647

PhysicalDisk__TotalDrives__Avg_Disk_Queue_Length: 0.169

PhysicalDisk__TotalDrives__Disk_Reads_Sec: 19.923

Processor__TotalDrives__Percent_Processor_Time: 40.161 (I have seen this go up to > 60)

SQLServerBufferCacheManager__Buffer_Cache_Hit_Ratio: 99.822 (only a small drop)

SQLServerGeneralStatistics__User_Connections: 25 (this is higher)

SQLServerPlanCache__TotalDrives__Cache_Hit_Ratio: 96.535

System_Processor_Queue_Length: 0.000

John Rowan SSC Guru Points: 56440 More actions · Answer 3

I don't see anything alarming here. All of the values are within acceptable ranges. The CPU usage seems a bit high for just one process, but it is still within acceptable limits. Are you saying that you have a concern regarding the job's performance?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 4

Thanks for looking this over,

I guess I'm just trying to figure out if this is "normal" performance - this is my first server on a SAN - and I'm planning on adding another 10 databases to it. I want to be sure so I don't overload, of course I am considering additional connections, etc. in all of that 🙂

I don't have any special software to help me plan so it's on me to determine what is acceptable, you know?

I really appreciate another dba looking at the numbers - I was siding with "ok it's normal" but needed someone else's opinion! LOL

Thanks again - and if anyone else has recommendations, please post them!

Happy Holidays

John Rowan SSC Guru Points: 56440 More actions · Answer 5

If you are wanting to get some measurements on baseline IO throughput on your SAN, you may want to look into using some third party tools to help with that such as SQLIO.exe or IOMETER.exe. It may be worth checking out.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 6

Thanks! I will look into it - I'm also working with the SAN administrator - I'm going to ask him what each disk can handle for IOPs - anything else I should ask him?