May 22, 2008 at 6:47 pm
(Posted it at wrong tread.. so try to repost it here..)
Hi
I'm currently having a problem with my database and need your inputs. One of my Servers is running slow.
any inputs are highly appreciated.
Many Thanks
PROBLEM:
Platinum Server is running slow, sqlserver is consuming a constant of 40-60% (Peak and Off Peak hrs) CPU Usage and hit 100% from time to time. Disk Usage also hit 100% usage on one of the drives during peak hours.
CHECK ON:
System Performance:
Processor - %Processor time _total; -- constant 40-60% and goes 100% during peak hrs
Memory - Pages/sec: -- normal..
PhysicalDisk - %Disk Time _total -- running at 100% during peak hrs
Profiler Current -- seen .Net Data Provider consumes lots of CPU, Physical i/0 and duration
Activity (sysprocesses) -- almost 200 connected users
SERVER SPECS:
(4x) 3GHz Processor Speed
4GB Memory Capacity (3GB allotted to MS SQL)
Win 2000 SP4
MS SQL 7 SP4
ADDITIONAL POINTERS:
20G database and reside at one physical drive.
May 23, 2008 at 12:26 am
(Reposting my earlier comments here to keep all the discussion in one place)
Start with profiler. Run during busy periods (I would recommend you use server-side trace events rather than the profiler GUI. See the sp_trace procedures in Books online. Profiler gan generate the script for you)
Good events to start with - T-SQL:Batch Completed and Stored Procedures:RPC completed
Save the trace somewhere once you've run it for a while and look for the queries that are taking longest, using the most CPU and/or doing the most reads.
Once you've indentified the culprits, have a look at them and see if you can see why they are slow. It will probably be one of:
Badly written queries
Poor indexing
Both
You can also look at moving the database's log file onto a separate physical disk. It may help a little.
What RAID level are you running on the drive?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply