Hello and Good day,

I am using SQL SERVER 2012 With SP2 with product version 11.0.5058.0

I have 26 GB RAM, its not a virtual machine. Its a normal Windows Server 2012 R2.

As service of Sql Server running the CPU usage goes behind 90+%. If is stop MSSQLSserver service, the CPU Usage comes back to 8% to 10%

Help needed to diagnose and resolve

Regards,

Sohaib Cheema

Do not worry on the memory usage - unless you had max memory limit, sql will use  as much memory as she needs.

regarding cpu, to keep it simple - you need to figure out what queries are consuming high cpu - you allready some builtin reports that can provide you this information -- Right clikc on the servername in object explorer -- Reports --standard reports - top performance by cpu..

or, you can download - sp_whoisactive by adam machanic -- google for the download link --- and run that.. I would prefer sp_whoisactive -so, you get the better picture

Top query used by CPu:-

SELECT TOP(10)
creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 AS total_worker_time
,       (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime]
,       execution_count
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time > 0
ORDER BY total_worker_time DESC

SQL Server has a nature to acquire memory as much as needs, until you restrict SQL by setting Max memory.

So, you don't worry about memory until you have IO issues or SQL Server performance degradation. Even if you 1 TB RAM, SQL will try to use that by keeping data in RAM for faster access. 

Let us know if you are facing any performance issue due to that. Stan210 & VIMD has already provided you ways to find culprit query. 

Sohaib,

You included memory in your heading but in actual question you are talking about high CPU usage.

Your question is open ended and can have many reasons to provide answer one need to fully analyze what is causing high CPU few reasons I know is

1. Incorrectly configured MAX DOP setting

2. Lot of queries running in parallel

3. Missing index and outdated stats

4. MARS activated from application

You can find similar blogs

http://blogs.msdn.com/b/batala/archive/2011/07/23/troubleshoot-high-cpu-issue-without-using-profile-traces.aspx

https://technet.microsoft.com/en-us/library/dd672789%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396

As a default SQL Server is configured to use 100% of the server resources as required.

Is there an actual problem?  Are you running something else on the server causing an issue?