SQL Server Hanging - 2012 Enterprise Edition

amazon4life
amazon4life used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gaurav SinghSolutions Architect

Commented:
Check the performance of VM via VMware Performance report.
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.

Author

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?
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial