October 5, 2009 at 3:24 pm
Hi guys,
Im facing a issue!
our production servers cpu is hitting 100% coming down to 50 or 60 %.
we have identified the stored procedure that is causing the high CPU when comment the proc out the cpu drops instantly, obviously we cannot leave it like this.
we noticed high amounts of cxpackets wait type on the procedure. The next move will try to and MAXDOP hint into the proc....
Is it fine to place it multiple times after where statements ie
begin
select * from t1 where c1 =1
OPTION (MAXDOP 1)
end
begin
select * from t1 where c1 =1
OPTION (MAXDOP 1)
end
i have never set it within a procedure before any advise appriceated even any other ideas?
is it possible to tell the whole procedure to run with a MAXDOP 1 ? with having to hint
Cheers
October 5, 2009 at 3:35 pm
In the times I have had to use MAXDOP, yes, you can specify it multiple times within a procedure. I have also worked with changing server-wide MAXDOP options for certain maintenance routines run, etc.
I am not however sure if there's a "Stored Procedure-wide" option; I believe it's at the query level.
MJM
October 5, 2009 at 3:53 pm
Thanks for the info, we adjusted the max dop server config from 5 to 2 the server has 4 proccessors
but is having a impact on performance but keeping the cpu from flat lining.
i would like to just isolate the proc then set the server maxdop to original settings
are there any dangers other than performance degradation in putting max dop hints in a stored procedure?
October 6, 2009 at 6:57 am
Try tuning your query before you jump on to MAXDOP settings. Might be the missing index case here. Missing index might lead to parallelism selection by optimizer as stated at http://blogs.infosupport.com/blogs/robp/archive/2009/09/01/cxpacket-maxdop-and-parallelism.aspx
There is no harm in using MAXDOP at stored procedure level.
MJ
October 7, 2009 at 3:18 am
I have experienced the same problem in a SharePoint _SharedServices1_Search_DB with the stored procedure proc_MSS_GetCrawlHistory. It was running for >120 seconds, 100% cpu on a 4 CPU machine.
I'm convinced that situation occurs when the query optimizer is running the query in parallel, assuming this is optimal ... and it isn't.
I ran the query from the SQL Server management Studio and included the execution plan. In my case, the actual number of rows in most of the steps was far away from the estimated number of rows. With this in mind it is not a big surprise that the query optimizer chooses a "bad" plan.
My suggestion willl be to look at the query to check for missing indexes. The right indexes should help the optimizer doing a better job.
Next step will be to rewrite the query if possible. In my case I wouldn't dare to rewrite SharePoint sprocs.
Finally add "option (maxdop 1)" to your query.
I solved my problem with the SharePoint sproc by adding 2 indexes:
create index idx_1 on MSSCrawlContent(ContentSourceID)
create index idx_2 on MSSCrawlHistory(STartTime)
and finally adding " option (maxdop 1)" to the end of the query.
October 7, 2009 at 3:25 am
I would try and isolate within the stored procedure the query that is causing the issue, if you view the execution plan, you should be able to work out if adding indexes might help you in this situation.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 1:56 pm
Before creating any new indexes you may wish to consider updating the statistics on the tables involved in the troublesome queries:
http://msdn.microsoft.com/en-us/library/ms187348.aspx
If this doesn't have the desired effect then view the execution plan for the stored procedure, supplying appropriate input parameters as required - try to supply the parameters that are causing the stored procedure to perform poorly (depending on how the stored procedure is executed you may be able to use SQL Profiler to determine the values of the parameters). As part of the execution plan SQL Server may suggest one or more 'missing indexes' - create these using the suggested statements and run the query again to see if performance is improved.
You may actually find that parallelism improves your queries' performance for certain parameter values, so I'd be inclined to use OPTION (RECOMPILE) in preference to OPTION (MAXDOP 1). OPTION (RECOMPILE) will cause queries to be compiled with the actual values of any variables used, so could result in a more efficient plan than if you were to force a value of MAXDOP.
Chris
October 8, 2009 at 2:20 am
Thanks for you replies.
Well i am really struggling with this issue..
This Procedure has a lot of things going on, its executing nested extended stored procedures creating temp tables inserting updating.
It runs every couple of seconds and its duration is 0.6 secs no more than 1 sec.
So i added OPTION (MAXDOP 1) to most of the stored procedure where i saw "where" clauses.
set the server maxdop to default of 5 and everything is going great guns.
.....but wait i have found a something i cannot explain....cpu is now between 1% and spiking to 40% nice and healthy.
all signs are good but when i execute the stored procedure which a took from a trace on this machine, in the query window the cpu within 1 minute climbs to 20% where it sits for 10 to 15 mins then to 40, 50, 60, 70, 100 by this stage i found that executing sp_recompile stored procedure once, starts to bring the cpu down only slightly.....repeating that exercise 5 to 10 times within 1 minute soon returns the cpu back to normal levels which it looks like it will stay until i execute the procedure from the query window again.
So i thought adding with recompile would sort this out but it makes things worse, blocking and waits start appearing because of the recompilation time as this proc is fired very frequently.
I have left the maxdop still in the procedure, but now i cannot tell what is setting this thing off???
i am running low on ideas....still haven't studied the index usage yet will tomorrow, would that have caused the affect im describing if a index was missing?
What i have done...
updated the stats on the tables that the proc touches with FULLSCAN.
made sure i have minimal fragmentation on indexes...
Once again thanks for your replies
October 9, 2009 at 7:40 am
There are probably a dozen or more things you need to investigate and rule out before doing something like hinting maxdop. You should consider getting a professional tuner to take a look at what is going on here, whilst teaching you how to do the investigations necessary to analyze such things yourself in the future.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 9, 2009 at 7:48 am
This looks more like you are having problems with getting the right execution plan. Check for parameter sniffing. If sp_recompile is bringing the CPU down that would mean that it is creating a new plan that suits that query. I think you should concentrate on trying to find out if you are having parameter sniffing issues for that stored proc.
-Roy
October 11, 2009 at 3:11 pm
Sure, it is something with query plan....i can see from the trace when i execute my proc from the query window within 1 or 2 mintures the logical reads and cpu starts to climb, signs of a bad query plan
will be investigating further
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply