SQL 2012 Running really slow high cpu usage

Bit stumped on this one.

SQL 2012 installed on a VM under Server 2012, host is 2012 as well. It is the only guest VM. It runs fine for several weeks, then out of the blue it starts to balloon up to 99%+ CPU usage on the guest.  Problem is that I cannot run activity monitor on SSMS as it times out (due to the high cpu usage).  So what script(s) can I use to show my who my culprit is?
bhiebAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You must have some query blocking the others processes.
Open a new query window in SSMS, paste and run the following query to find the culprit SPID:
SELECT * FROM sys.dm_exec_requests 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
WHERE session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests)

Open in new window

Having the session_id number you can decide or not to kill the process. If afirmative just run KILL <spid_number> for each of blocking SPIDs.
0
bhiebAuthor Commented:
No blocks. No results to the query.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. How about a simple query to list all processes?
SELECT * FROM sys.dm_exec_requests

How many rows does it returns?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bhiebAuthor Commented:
32 Rows, what column would indicate a high cpu usage query? cpu_time?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, it's cpu_time.
The server only has SQL Server installed or have more stuffs than can be running at same time?
0
bhiebAuthor Commented:
Few other, but OS cpu is clearly showing 70-80% is SQL. The other is an app that accesses SQL.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, but 70%-80% is usual for an OLTP system when stressed.
By the way, what is the configuration of the server (CPU and RAM)?
0
bhiebAuthor Commented:
8 cores 120GB ram on SSD's.  I stopped the GP Management Reporter services. I think it may have been the culprit (it was the 2nd highest cpu eater). Dropped my SQL cpu usage to 20%. Testing and I'll get back with you.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
8 cores 120GB ram on SSD's.
This is the VM specification? Are you using all physical server resources?
0
bhiebAuthor Commented:
Yes I is the only guest, it is really only virtualized to easy mobility if I ever needed to change hardware.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I stopped the GP Management Reporter services
It's not good idea to share MSSQL engine with other application, especially reporting applications. MSSQL should be installed in a dedicated box.
0
bhiebAuthor Commented:
I'll discuss that with the vendor and get it moved to it's own VM, he talked me into it being "ok" clearly it isn't. However if that particular app has a buggy query that gets hung, be it on the same install or not it would have still hung (since killing it reduced the SQL agent Cpu% by ~50% as soon as I did)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
be it on the same install or not it would have still hung
Not really. Many reporting systems uses a lot of resources for data rendering. And not sharing the same resources will help to keep a good performance.
0
bhiebAuthor Commented:
Part of the problem is that when this happens I cannot use the normal tool I would to kill jobs, that is Activtiy monitor.  Here is what I get, I'm still getting this even though CPU usage is back to semi normal.

TITLE: Microsoft SQL Server Management Studio
------------------------------

The Activity Monitor is unable to execute queries against server SQLPROD1.
Activity Monitor for this instance will be placed into a paused state.
Use the context menu in the overview pane to resume the Activity Monitor.

------------------------------
ADDITIONAL INFORMATION:

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5058&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

------------------------------

The wait operation timed out

------------------------------
BUTTONS:

OK
------------------------------

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Personally I don't use Activity Monitor. It takes time and consume more resources.
I usually use the query I posted above. Alternatively you can also run sp_who2 stored procedure:
EXEC sp_who2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bhiebAuthor Commented:
Ok thanks for you input, I'll get with the vendor and see if I can pin point the exact issue with the app running a muck.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.