January 28, 2013 at 5:29 am
it is charging few dollars, i have to take approvals from client.
January 28, 2013 at 5:34 am
purushottam2 (1/28/2013)
it is charging few dollars, i have to take approvals from client.
I take it you missed the "Get your free eBook download here." link on the webpage then?
January 28, 2013 at 5:38 am
anthony.green (1/28/2013)
purushottam2 (1/28/2013)
it is charging few dollars, i have to take approvals from client.I take it you missed the "Get your free eBook download here." link on the webpage then?
and even if it wasn't free (which it is), why would you have to get approval from a client to buy a book for your career?
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
January 28, 2013 at 5:44 am
GilaMonster (1/28/2013)
anthony.green (1/28/2013)
purushottam2 (1/28/2013)
it is charging few dollars, i have to take approvals from client.I take it you missed the "Get your free eBook download here." link on the webpage then?
and even if it wasn't free (which it is), why would you have to get approval from a client to buy a book for your career?
Very true and with something as so useful as what is covered in the book, it is worth the $29.99, I would argue its worth more than that, the amount of times I have forgotten something small and this re-jogs the memory, certainly has saved me a few times.
January 28, 2013 at 7:09 am
Great advice (ebook)...
But first, just be absolutely sure your cpu isn't being consumed by the os before deep diving into wait stats, etc. I know you said this was not the case, but how did you check cpu? Here's a direct cut & paste from Glen Berry's diagnostic checks. This one gives you cpu history split out by SQL/non-SQL usage:
-- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 25)
-- This version works with SQL Server 2008 and SQL Server 2008 R2 only
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);
January 28, 2013 at 7:22 am
I have checked by Query, And also at time when our IIS server was stop CPU usage was 0 %, if we start IIS server and user accessing application it leads to 100%.
January 28, 2013 at 7:44 am
Then between the web server and the user app, they're sending enough stuff to SQL to run the CPU up to 100%.
Check out that book chapter I recommended. It's all about high CPU usage.
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
January 28, 2013 at 4:36 pm
purushottam2 (1/28/2013)
it is charging few dollars, i have to take approvals from client.
You're seriously suggesting that it's not worth it for you to invest a few bucks in your own education? Are you making that little from your clients?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2013 at 4:41 pm
purushottam2 (1/28/2013)
I have checked by Query, And also at time when our IIS server was stop CPU usage was 0 %, if we start IIS server and user accessing application it leads to 100%.
What happens if you start the IIS Server but don't let any users in? I have seen bad installations of IIS Server drive SQL Server absolutely nuts and that would be the proof you need.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2013 at 9:29 pm
Once user start accessing application then only it lead to 100% cpu consumption.
January 28, 2013 at 11:12 pm
purushottam2 (1/28/2013)
Once user start accessing application then only it lead to 100% cpu consumption.
then sql profiler trace might help you , set the trace and try to catch the queries which are spiking the CPU.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 29, 2013 at 10:12 pm
Bhuvnesh (1/28/2013)
purushottam2 (1/28/2013)
Once user start accessing application then only it lead to 100% cpu consumption.then sql profiler trace might help you , set the trace and try to catch the queries which are spiking the CPU.
It might actually be easier than that. From SSMS, open the object explorer {f8 key}. Right click on the server instance name in object explorer and select {Reports}{Standard Reports}{Performance - Top Queries by Total CPU Time}. It takes you right down to the actual query in a stored procedure or ad hoc code and will even help you find very quick queries that are used a whole lot of times per second.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply