March 7, 2011 at 2:06 am
My SQL server peek between 90 to 100 % for several days now.
I tryed to restart and also Windows Upgrade. But when the server stats again
it will go up to around 90 - 100% and it's Sqlserver.exe Cpu 97 memory 88240
Sql server is running on an virtual server with 4 core and 8GB Memory.
I read somewhare that you can select from sys.dm_exec_query_stats
I see some rows who have high total_elapsed_time and its same type of query.
Is't a way to release this queue so the Sqlserver will act normal again.
Probably it some session hangs and will not release.
Some tips would be great.
Thanks in advance
Gert Lindholm
March 7, 2011 at 2:19 am
Do a short trace (5 seconds will probably be enough) with sql profiler, with event "SP:Starting" and "SP:StmtStarting".
You will probably find function or computed column that is called zillion times.
March 7, 2011 at 2:22 am
How do i do that?
March 7, 2011 at 2:50 am
I find this at MSDN:
SELECT TextData, Duration, CPU
FROM trace_table_name
WHERE EventClass = 12 -- SQL:BatchCompleted events
AND CPU < (Duration * 1000)
Where shall i put this query don't find this from table in my SQL Server?
Best Regards
Gert
March 7, 2011 at 3:18 am
Go to youtube.com and put this into search box "Using SQL Profiler"
March 7, 2011 at 5:35 am
Hi again,
I see an SP p_getMatchSchemeNowAndNextRound with some @parm.
The thing is that @parmRoundId is werry high number.
From our .NET solution i have an max of 30 i RoundId. but here i see 403946:
declare @p5 int
set @p5=1
exec p_getMatchSchemeNowAndNextRound @parmSeasonId=2010,@parmDivisionId=23,@parmRoundId=403946,@parmCupGroup=0,@parmOK=@p5 output
select @p5
To get some rows from SP @parmRoundId should have an value of 20 from .NET and then go to end.
When i put in 403946 i get an quickly respons zero rows.
Have you another clue?
//Gert
March 7, 2011 at 5:50 am
SP:Starting gives you all stored procedure calls. E.g. your .net appliaction calls SP (event RPC:Start), and this procedure calls other stored procedure (event SP:Start). It is possible that your app is calling it with low id, but procedure called from this procedure calls it with higher param value.
Try with RPC:Start event, and exclude all other events to see exactly what your app is calling. Include RPC:Completed event to see total CPU cost (include that column with "see all columns" checkbox of profiler).
Another very usefull thing you can do is go to Adam Mechanic site: http://sqlblog.com/blogs/adam_machanic/[/url]
and download his WhoIsActive procedure. It is similar to sp_who2, but much more powerful. You can see current code executing by each db session, cpu usage, etc.
March 8, 2011 at 2:20 am
Thank you all for your help.
I found an loop in .net code with no stop, therefor the server peeked.
Tanks:-)
Gert Lindholm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply