May 22, 2008 at 1:42 am
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 (SPSQL002) 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 22, 2008 at 5:12 am
Please rather post SQL 7 related questions in the SQL 7 and 2000 forum, rather than the SQL 2005 forum. Here you're going to get a lot of suggestions for things that don't work in SQL 7.
Start with profiler. Run during busy periods (I would recommend you use server-side trace events rather than the profiler GUI)
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?
Have you checked and confirmed that it is sqlservr.exe that's consuming the CPU?
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
May 22, 2008 at 6:45 pm
OPS.. Sorry.. try to repost this at sql 7 and 2000..
>>
server was newly turn over to me so i still dont have idea of what type of RAID they are using.. i'll check on that
not familiar with server-side trace events or how to use it.. (i'll try to find it out..)
i'm 100% sure its the sqlservr.exe that is consuming the CPU Usage.
BTW. all 4 processor are set to SQL..
thanks a lot Gila.. i've been reading some of your post/ replies, and its helping me a lot..:D
May 23, 2008 at 12:25 am
Posted in wrong forum. Please direct replies to the following thread.
http://www.sqlservercentral.com/Forums/Topic505583-65-1.aspx
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply