CPU 100%, current queries show "CREATE PROCEDURE ... "

Question

CPU 100%, current queries show "CREATE PROCEDURE ... "

Boris Pazin

Ten Centuries

Points: 1036
More actions
September 27, 2012 at 6:16 am

#282129

Hi,
I have a SQL Server 2005, which works fine and on some time it starts to use 100% of CPU. Then website is slow, get timeout messages etc.
I used this query to find current queries:
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
When I run query above, it shows me about 40 records, and most of them are code for creating stored procedures. Same like when you go in Management Studio "Script stored procedure As", and then select "CREATE to".
So, it looks like these 3 stored procedures are constantly re-created.
After a while, I restarted the server and now it works fine. But, it repeats this behavior every day.
Anyone have an idea what could cause this?
Thanks!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Restarting SQL is not a good idea as it flushes it's cache and when it restarts has to build the cache again which makes performance worse (usually)

A good place to start would be here http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ - Chapter 3 High CPU Utilisation.

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 2

If you have access to SSMS 2008 you can right click on the instance name and select Activity Monitor.

This will give you a good place to start troubleshooting.

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Boris Pazin Ten Centuries Points: 1036 More actions · Answer 3

anthony.green (9/27/2012)
Restarting SQL is not a good idea as it flushes it's cache and when it restarts has to build the cache again which makes performance worse (usually)

It is production server. Users can't login, can't browse website and things become urgent. :unsure:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The stored procedures are not being recreated. They're being executed.

Find the worst performing code, tune it. Repeat until performance acceptable.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Boris Pazin Ten Centuries Points: 1036 More actions · Answer 5

D.Post (9/27/2012)
If you have access to SSMS 2008 you can right click on the instance name and select Activity Monitor.
This will give you a good place to start troubleshooting.

I checked current activity, and found that there is a lot of queries for creating of procedures.

The problem is, that queries should not be executed at all. That stored procedures already exist on server for a long time. No need to run CREATE PROCEDURE query for them at all.

I have no idea why these queries are executed on the first place :sick:

So, in certain time, SQL Server executes a lot of same queries for creating of same procedures (procedures already exist on server) and CPU goes 100%.

The queries which are executed in that time are useless, I don't need them at all, and don't know from where they are coming.

How that could be possible?

Boris Pazin Ten Centuries Points: 1036 More actions · Answer 6

GilaMonster (9/27/2012)
The stored procedures are not being recreated. They're being executed.
Find the worst performing code, tune it. Repeat until performance acceptable.
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gila,

thanks for replay.

Website is using procedures for showing data, that part works OK.

The problem is that current queries are scripts for creating procedures, not for executing them.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Boris Pazin (9/27/2012)
The problem is that current queries are scripts for creating procedures, not for executing them.

The procedures are not being created. They are being executed.

When you execute a procedure, the sql_text DMV shows the create so that you can use the statement offsets to get the exact statement executing.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Boris Pazin Ten Centuries Points: 1036 More actions · Answer 8

GilaMonster (9/27/2012)
Boris Pazin (9/27/2012)
The problem is that current queries are scripts for creating procedures, not for executing them.
The procedures are not being created. They are being executed.
When you execute a procedure, the sql_text DMV shows the create so that you can use the statement offsets to get the exact statement executing.

Wow, that is interesting!

GilaMonster, you say, if I call query above to see current queries, it will show CREATE PROCEDURE text???

Must check this immediately 🙂

Boris Pazin Ten Centuries Points: 1036 More actions · Answer 9

GilaMonster (9/27/2012)
Boris Pazin (9/27/2012)
The problem is that current queries are scripts for creating procedures, not for executing them.
The procedures are not being created. They are being executed.
When you execute a procedure, the sql_text DMV shows the create so that you can use the statement offsets to get the exact statement executing.

Thanks!!!!

Things finally make sense. :Wow:

Boris Pazin Ten Centuries Points: 1036 More actions · Answer 10

Ok, that mystery is solved, thanks again :cool:, but I still have that daily outages.

Every day, in almost same time, SQL Server takes 100% of CPU and not going down until I reset it. I am looking to current queries, and that are common queries from website pages. After reset, everything is OK.

Anyone have an idea what could be possible cause?

I know now how to monitor current queries. Is there anything else except of queries, which could possibly cause that sqlservr.exe start to take all CPU?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Potentially, but I suspect it's more likely to be queries. The restart will clear the cache, so if you've got inappropriate plans being cached, the restart will 'fix' the problem

See the articles I referenced above and/or chapter 3 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Identify what is being run at the time that the CPU is high, what out of that is using lots of 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Boris Pazin Ten Centuries Points: 1036 More actions · Answer 12

I found something interesting.

Shrink job is running before the re-indexing job. That is not good.

I suppose it should be database shrinking first, re-indexing after, right?

One more interesting thing, I started re-indexing job manually, when SQL Server was working OK. Then, after re-indexing job is finished, CPU started to go on 100% again. I was waiting some time, and then restarted SQL Service.

How is possible to CPU go 100% after re-indexing is done?

Later, I ran re-indexing again to see if SQL will crush again, but nothing bad happened.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Boris Pazin (9/28/2012)
I found something interesting.
Shrink job is running before the re-indexing job. That is not good.
I suppose it should be database shrinking first, re-indexing after, right?
One more interesting thing, I started re-indexing job manually, when SQL Server was working OK. Then, after re-indexing job is finished, CPU started to go on 100% again. I was waiting some time, and then restarted SQL Service.
How is possible to CPU go 100% after re-indexing is done?
Later, I ran re-indexing again to see if SQL will crush again, but nothing bad happened.

Goodness, no! You should almost never shrink a database.

You're CPU is going to 100% because of the stored procedures or queries it runs. You need to find out what those sprocs and queries are and fix them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Boris Pazin (9/28/2012)
Shrink job is running before the re-indexing job. That is not good.
I suppose it should be database shrinking first, re-indexing after, right?

No, it should be reindex only, never shrink.

One more interesting thing, I started re-indexing job manually, when SQL Server was working OK. Then, after re-indexing job is finished, CPU started to go on 100% again. I was waiting some time, and then restarted SQL Service.
How is possible to CPU go 100% after re-indexing is done?

Why would it not be?

You have queries running your CPU to 100%. Just because you've rebuilt the indexes doesn't mean that badly written queries are suddenly going to behave.

You need to identify the queries that are using lots of CPU and fix them. Any other random shotgun solution is not going to provide a permanent fix. See the articles I linked, see the book chapter I referenced.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass