January 6, 2009 at 3:45 pm
We had the auto update stats and auto create stats turned on in our production.approx after that our CPU usage on our db server spiked up.So we decided to turn it off in the prod.But even after we unchecked the options in Enterprise Manager we still see a similar CPU Usage.So is just unchecking the option on the db in EM enough or do we need to do any additional steps to bring back our db to is original state?
thanks,
SSM
January 7, 2009 at 12:14 am
You should leave those on unless you know exactly what turning them off will do, you're happy with the risks and you have some manual stats update job in place.
http://sqlinthewild.co.za/index.php/2008/11/04/what-are-statistics-and-why-do-we-need-them/
I would suggest that as soon as you get some maintenance time, go through and manually update the statistics on all tables. If you're still seeing high CPU, find the queries that are taking the most CPU (profiler's good for that) and see if you can see what they're doing.
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
January 7, 2009 at 12:29 am
Like Gail said, you should first figure out if that is the cause of you cpu hog.
Use SQLserver profiler and Windows perfmon to figure out what's goin on !
You could also get a first (rough) impression about your consumers using this:
(by executing it the first time, it creates a snapshot, re-execute it (same query analyser session !!) it shows the differences)
-- Who consumes what ??
if (object_id('tempdb..#tmpVerbruik') is null)
begin
-- create temptable
select min( @@Servername ) as ServerName
, A.hostname
, A.loginame
, A.program_name
, min(login_time) as min_login_time, max(last_batch) as max_last_batch , sum(cpu) as sum_cpu, sum(physical_io) as sum_physical_io, sum(memusage) as sum_memusage
--, login_time, last_batch , cpu, physical_io, memusage
into #tmpVerbruik
FROM master..sysprocesses A
--WHERE loginame = 'mailsweeper'
group by A.hostname, A.loginame, A.program_name
end
select A1.*
--, T.min_login_time
--, T.max_last_batch
--, T.sum_cpu
--, T.sum_physical_io
--, T.sum_memusage
, A1.sum_cpu - T.sum_cpu as Delta_cpu
, A1.sum_physical_io - T.sum_physical_io as Delta_physical_io
, A1.sum_memusage - T.sum_memusage as Delta_memusage
, A1.sum_physical_io - T.sum_physical_io as Delta_physical_io
, A1.sum_memusage - T.sum_memusage as Delta_memusage
from
(select min( @@Servername ) as ServerName
, A.hostname
, A.loginame
, A.program_name
, min(login_time) as min_login_time, max(last_batch) as max_last_batch , sum(cpu) as sum_cpu, sum(physical_io) as sum_physical_io, sum(memusage) as sum_memusage
--, login_time, last_batch , cpu, physical_io, memusage
-- into #tmpVerbruik
FROM master..sysprocesses A
--WHERE loginame = 'mailsweeper'
group by A.hostname, A.loginame, A.program_name
) A1
left join #tmpVerbruik T
on A1.hostname = T.hostname
and A1.loginame = T.loginame
and A1.program_name = T.program_name
where T.sum_cpu <> A1.sum_cpu
or T.sum_physical_io <> A1.sum_physical_io
or T.sum_memusage <> A1.sum_memusage
or T.sum_physical_io <> A1.sum_physical_io
or T.sum_memusage <> A1.sum_memusage
order by Delta_CPU desc, A1.hostname, A1.loginame, A1.program_name
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply