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?
Microsoft SQL Server

Avatar of undefined
Last Comment
EugeneZ

8/22/2022 - Mon
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??
mastoo

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?
DireOrbAnt

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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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..

EugeneZ

BTW:
what is your box OS\SP; RAM, Etc ?
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
EugeneZ

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question