Link to home
Create AccountLog in
Avatar of ULDISAP
ULDISAP

asked on

CPU overload on MS SQL 2005

SQL database is causing a CPU overload by locking CPU resources with query

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512),
[Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(83)')
select [Event Info] from #tmpDBCCinputbuffer

What is possible reason and solution for this problem?
Avatar of udayshankar
udayshankar

how many records are we talking about here?? roughly..

select clount([event info]) from # tmpDBCCinputbuffer??

is the updates into that talble from the source and your insert happening at the same time??
He creates the temp table and then the insert would do a single record.  Two questions:

1. What is spid 83?
2. What are you looking at that makes you think it is this sql that clobbers the server?
Run these lines one after another. The insert into is probably causing the load, but just in case.
So before you run this everything is fine and as soon as you run it it spikes the CPU?
Avatar of EugeneZ
<What is possible reason and solution for this problem?
what is edition\SP of your sql server 2005?
run sp_who
and see blocks..

BTW:
what is your box OS\SP; RAM, Etc ?
Avatar of ULDISAP

ASKER

MS SQL Server 2005 Standart  Edition with SP1.
Windows 2003 Server Standart R2
4 x 3.0Ghz CPU`s, 3Gb RAM

The problem is that tihs query is internal sql server process when log cash must be flushed on replication. The process not hungs up but it runs slowly and locks other queries and log flush wait time starts to grow on realtime. This is not happening everytime but suddenly on one of sql server replications on normal daily work load.
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account