0

2-weeks ago one of our web servers CPU Usage started bottle-necking lot at 100% for long periods of time when an application runs and requests "sqlserver.exe"..When this app runs and does its integration (~5-mins) the CPU shoots up and locks the webpage.

Background:

  • Qquest Software Clock Server Version 1.2.20.0
  • The off-the-shelf unsupported version app database is on the same partition as the OS and Sql Server (DB size 17.19 MB and 0.78 MB Free)
  • SQL Server version 8.0
  • MS Server 2003 SP 2 ( 51.01 GB Capacity, 3.34 GB Free)
  • 1536 MB in memory
  • 3.00 GHz Intel Xeon X5365

What I've done so far....

  • Just added a extra processor to the server ( it now has 2 CPU's )
  • Just added extra memory ( it now has 192.84 MB)
  • And yes, did a reboot

Could so little free space be my problem with out shutting down the server and moving the app to a new home?

Seems like the problem stopped after the upgrade, but throwing hardware at the problem is not the answer for bad system architecture.

So I guess the new question is now; how to isolate problem applications from using all of your system resources?

Improve this question
7
  • Are you sure you have your units correct for the drive space? Did you mean GB instead of MB?
    – Chris McKeown
    Dec 2, 2013 at 20:09
  • I know that seems small...but that's what I'm getting from the DB properties in SQL Enterprise Manger---It's mind boggleing because its so small, but cause such a large effect.
    – AAH-Shoot
    Dec 2, 2013 at 20:20
  • Disk space problems are likely not the cause of that kind of slowness, unless your disk subsystem is just performing horridly. Are there other databases in this SQL instance?
    – squillman
    Dec 2, 2013 at 20:26
  • @squillman yes, there are 6 other databases in this instance (don't ask me why before my time). But, it's clear when the clock server establishes a connection the bottle neck start's, it moves files from a staging area to a archive directory (while doing so it inserts the time punches into the DB)
    – AAH-Shoot
    Dec 2, 2013 at 20:35
  • Can you clarify what the actual figures are? Is that free space in the data file(s) for that particular DB? How much space is free on the drive hosting the DB?
    – Chris McKeown
    Dec 2, 2013 at 20:46

2 Answers 2

Reset to default
0

Datetime updates in a SQL database should be trivial and last only milliseconds at most, unless there is a lot of other activity on the tables being updated.

I would start by getting some perfmon counters on physical memory and disk IO. With the extremely low amount of physical memory you have you should also check to make sure that SQL Server is set to have a value for max server memory configured. See this article for info on that. SQL Server will consume as much memory as it can unless you tell it not to which is a very bad thing for a server such as yours with extremely limited hardware resources.

Improve this answer
0

Although the hardware upgrade solved the problem you had, a bad system configuration, bad application and schema design can make the same situation occur again

Therefore, it’s recommended to find the cause of the problem, and fix it permanently Start with Performance Monitor (System Monitor) and check the most common issues:

• Processor

• Memory

• File I/O

• Locking, blocking, deadlocking

The metrics you should start with are:

  • % Processor Time
  • Memory – Available MBytes
  • Processor Queue Length
  • Checkpoint pages/sec
  • Lazy writes/sec
  • Buffer Cache Hit Ratio shows
  • Average Disk Queue Length
  • Average Disk Sec/Read
  • Average Disk Sec/Write
  • %Disk Time
  • Batch Requests/sec
  • SQL Compilations/sec
  • SQL Recompilations/sec

You can find the recommended values here: Optimizing SQL Server CPU Performance

Performance Monitor allows you to monitor various process metrics (Processor time, page faults, I/O data, read, write bytes, etc.) per process, which can help you determine the process that uses most processor resources

enter image description here

More useful info about using PerfMon: SQL Server Perfmon (Performance Monitor) Best Practices

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .