SQL Server Hanging - 2012 Enterprise Edition

The server does not allow me to access it via RDP, or via the VMware console.  The windows Welcome screen displays.  Our monitoring threshold does not reveal/indicate any issue with RAM, CPU, etc., but our support team states that they've seen an SSIS package pegging the CPU at 100%.  Is there a perfmon or any other utility native to Windows that we can use for troubleshooting/isolating this issue?  Right now it feels as if we are searching for a needle in a hay stack.
LVL 1
amazon4lifeAsked:
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.

Gaurav SinghHead - Managed ServicesCommented:
Check the performance of VM via VMware Performance report.
ZberteocCommented:
Use Adam Mechanic's procedure to check what Is going on that server when the SSIS is executed:

http://sqlblog.com/files/folders/beta/entry42453.aspx

You can install it on the master database or on a user database. Then just execute it with:

EXEC databasename..sp_whoisactive


Where you will replace "databasename" with the actual database name. It will show you what are the current active processes at the moment of execution. You will be able to see the CPU column value and if any process takes too long to execute.


CPU 100% can have lots of reason but in the SQL server world you will have to first check if is about a missing index or a query poorly written and is not necessarily related to the hardware or software on the server.

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
amazon4lifeAuthor Commented:
Looks like the issue has been isolated to SSIS - the ISServerExec.exe is spiking the CPU and memory over 99%.  I would like to add more memory, but don't think that will resolve it.  Does anyone have any suggestions?  I'm thinking a DMV query to isolate the issue further.  Any thoughts?
ZberteocCommented:
I have give you already some thoughts but you  seem to ignore them. :)

The sp_whoisactive procedure already incorporates any DMVs and queries in the system tables to help you find the problem.
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
SSIS

From novice to tech pro — start learning today.