High CPU Usage
-
Guys,
I'm having a performance issue with one of our production database. I've setup performance monitoring using perfmon and profiler. Here's the result of the perfmon:
Counter Name Avg
Full Scans 30
SQL Re-compilations 16
Cache Hit Ratio 38
Buffer Cache Hit Ratio 99
Memory Grants Pending 0
SQL Compilations 24
Processor Time 40
Disk Reads(C,D) 0
Disk Reads(I) 0
Disk Reads(H) 15
Disk Reads(G) 0
Disk Reads(F) 0
Disk Writes(C,D) 5
Disk Writes(I) 0
Disk Writes(H) 5
Disk Writes(G) 1
Disk Writes(F) 0
%Privileged Time 0.6
Processor Queue Length 10
Can you tell me or interpret the result for me? I think there's a bottle with the CPU and I think there's alot of recompilation/compilation going on to affect CPU usage. What do you guys think? I really need to figure out by today so please SQL GURUs help me out.
Thanks!
Dex
-
You may need to back up a bit and I would bet that you don't have a quick fix on your hands. From my experience, most performance issues are not related as much to hardware as they are the application and database design. From what I see in your results there are two things that jump out at me.
- Full Scans/sec - Your number seems awfully high. How are your indexes established? Was this in in house application or was it bought from a vendor? What do the query plans look like?
- Compiles and Recompiles - This looks high to me to. How is the code being executed in the database? Stored Procedures, dynamic SQL, ad hoc queries, etc? Are there joins to temp tables...blah, blah, blah. This list of questioning could go on
Your CPU average doesn't jump out and cause any alarm. At least as far as I am concerned. Are you seeing spikes in CPU utilization? Anyway, if the CPU is having problems then it is most likely application and design driven.
You probably need to be more specific with the applications and the performance issues. One bad query can slow every one else. I hear the "database is slow" so many times and 9 times out of 10 it is application and database design specific.
One more thing...I like how your disks are spread out. Maybe you could take more advantage of this and spread the i/o around with filegroups and files. Why the writes on the C:\ drive? Are you paging?
- Do you have specific performance issues?
"Keep Your Stick On the Ice" ..Red Green -
As Jeff just told you, at the really first step you have to check the indexes.
Use the Profiler to check which are the statements using the most of the ressources (either Duration or CPU or disk).
Afterwords you can execute those queries in QA where you can check the access plan. So you can see, whether your query is usung indexes or not.
Bye
Gabor -
I'm using this to determine I/O, CPU and memory issues (we have performance issues with SQL trace). Just change the job to run at a time of your performance issues and you can also change the execution time to every minute (I'm running every 2 minutes at this stage). Note that only sessions connected for 4 minutes (if you leave the job to run every 2 minutes) will be logged in the stats table and I'm using GBEMonitor as a database (change or create) ... you can figure the rest out ... have included a SQL to display the results at the end of the post.
Christo Pretorius
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Database_session_perf]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Database_session_perf]
GO
CREATE TABLE [dbo].[Database_session_perf] (
[spid] [smallint] NOT NULL ,
[dbid] [smallint] NOT NULL ,
[cpu] [int] NOT NULL ,
[physical_io] [bigint] NOT NULL ,
[memusage] [int] NOT NULL ,
[hostname] [nvarchar] (128) NOT NULL ,
[hostprocess] [nchar] (8) NOT NULL ,
[loginname] [nvarchar] (128) NOT NULL ,
[statdatetime] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_Database_session_perf] ON [dbo].[Database_session_perf]([statdatetime]) WITH FILLFACTOR = 98 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Database_session_perf] WITH NOCHECK ADD
CONSTRAINT [DF_Database_session_perf_statdatetime] DEFAULT (getdate()) FOR [statdatetime]
GO
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Monitoring') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Monitoring'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Database Session Perf')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Database Session Perf'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Database Session Perf'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Database Session Perf', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Monitoring', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'set nocount on
go
insert into GBEMonitor.dbo.database_session_perf
([spid], [dbid], [cpu], [physical_io], [memusage], [hostname], [hostprocess], [loginname])
select [spid], [dbid], [cpu], [physical_io], [memusage], [hostname], [hostprocess], [loginame]
from sysprocesses (nolock)
go
set nocount off
go', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20050219, @active_start_time = 0, @freq_interval = 97, @freq_subday_type = 4, @freq_subday_interval = 2, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
and then to sellect the results (examples and you need to customize):
SELECT a.spid, b.statdatetime, a.dbid, a.hostname, a.loginname,
b.cpu - a.cpu as CPU,
b.physical_io - a.physical_io as PHYSICAL_IO,
b.[memusage] - a.[memusage] as MEMORY
FROM GBEMonitor.dbo.Database_session_perf a (nolock), GBEMonitor.dbo.Database_session_perf b (nolock)
WHERE
--(a.dbid = 5)
--(a.hostname='B2CAPP06')
(b.dbid=a.dbid)
and (b.spid=a.spid)
and (b.hostname=a.hostname)
and (b.loginname=a.loginname)
and (a.statdatetime<b.statdatetime)
and (b.statdatetime-0.0017<a.statdatetime)
and (b.cpu-a.cpu>=0)
and (b.physical_io-a.physical_io>=0)
and (b.[memusage]-a.[memusage]>=0)
and ((b.cpu - a.cpu > 0)
or (b.physical_io - a.physical_io > 0)
or (b.[memusage] - a.[memusage] > 0)
)
--b.statdatetime > '2005-02-19 14:15:00.000'
--b.statdatetime < '2005-02-19 14:19:00.000'
ORDER BY b.statdatetime, a.dbid, a.hostname, a.loginname
--select * from sysdatabases
select * from Database_session_perf (nolock)
where dbid = 5 and spid = 75 and hostname = 'B2CAPP06' and statdatetime > '2005-02-19 14:15:00.000' and statdatetime < '2005-02-19 14:19:00.000'
-
Thanks Guys! I really appreciate your responses! I think I've narrow down the problems. Since our other DBA put all the system databases on the same drive as the production database(H) and our third party program creates alot of temporary tables on most of the SP's, I think moving the tempDB or all the system databases will alleviate the disk contention a little bit. After that, I'll do a SQL trace to find all long running queries and we'll get back to the vendors to try to see if they can do anything on there end since I can't change anything on the DB side because it'll void our contract. I'll confirm it tomorrow if it's really a disk contention on drive H because I'll be talking to a SAN engineer to if there's any hotspots on the spindles that makes up drive H.
-
Dexter...
Could you keep us posted as to your findings and the type of SAN your using? I am guessing...and bear in mind this is just a guess..that there aren't issues with the spindles since the SAN Luns are made of of so many smaller disks. What I am hoping you might find is that there is either and issue from the nic via fiber to the SAN and/or the i/o threads on the server itself are bottlenecking.
I need to test a proof of concept myself, but would enjoy reading about what you find.
Thanks...
"Keep Your Stick On the Ice" ..Red Green -
Sure do Jeff...I think we're using DMX 800 SAN Storage System. For the H drive, it's in a raid 5 configuration and the log files(G) is raid 1. I agree on you on that spindles are made up of so many smaller disks. I think and i'm guessing also that the i/o threads on the server are bottlenecking. The program is doing it's business rule implementation and processing on majority of it's SP's....in short doing alot of processing on a temporary table. Because tempdb resides on the same drive as the prod DB, i think it's safe to way it's one of the cause of the performance degration on the server. I did a sql trace yesterday and I'm analyzing all the SP's and SqL statement right now to see if it's optimize or not.
Also, the front end is a web app so the combination of reasons above and # of concurrent users have to do with the performance degradation.
-
ok our SAN engineer looked at all the hypers and yes there were a couple of them that have a lot of hotspots around that time of performance degradation. I told him about the situation and he told me that could be the problem. He's creating two new volumes for me so that I can put the system database to one volume and system logs to another volume for performance reason. Thanks for all your help SQL Gurus! I'll do baseline analysis before and after I move the system databases to see the benefits of it. Thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply