SysInternal Process Explorer with SQL Server

Another SysInternals (www.sysinternals.com) tool I use quite a bit is the Process Explorer, which has just had a new release, taking it to V. 15.3. Process Explorer allows you to correlate CPU thread activity with the SQL Server threads. I find this a very quick and easy way to go from CPU utilisation into SQL and identify the query which is burning CPU. Now, this technique is not new – in fact the guys at SQLSkills (www.sqlskills.com) have blogged about this far better than I can – but…. I use this in anger, and it hasn’t let me down yet. Consequently, I find this a VERY useful tool in my troubleshooting kit bag.

e.g….   First I create an AdventureWorks workload which hammers a single CPU due to the MAXDOP 1 setting on the query.

Now, with the “problem” at hand, I can open up Process Explorer and sort by CPU usage.

 

This can very quickly show me the SQL Server process is responsible for using CPU resources (which is the intention), but then right clicking on the SQL Server process and selecting “Properties” will give you a window such as this:

The threads tab shows you the Operating System level threads which are running within the SQL Server process, complete with the ability to sort by thread utilisation and the Thread Id which is actually running the user query.

Armed with this information, we can query SQL Server itself using:

To see all the details of the query which is causing the CPU load. These details can expose everything about the query, from the actual statement, the plan, the connection details – all the usual goodness from the SQL Server DMVs. With the Process Explorer tool and SQL Server coupled in such a way, finding the full details about a hard hitting query takes less than a minute. Happy Days!..

 

Linked Server Mayhem…

Background:
I recently encountered an issue with an ETL process pulling in data from an Excel Spread-Sheet for use within an SSAS cube. The ETL process uses the Microsoft.ACE.OLEDB.12.0 provider which opens up the SpreadSheet on a remote UNC share to read the data into a temporary table.

The Problem:
Seemingly randomly, the ACE Provider would crash and leave an open transaction running with no hope of ever completing. The SQL Server ‘Kill’ command would mark the transaction and the session as “Killed / RolledBack”, but the session would still be present, and the SpreadSheet would still be locked and inaccessible.

The Solution:
TCPView (www.sysinternals.com) seems to help out significantly here. Given that there doesn’t seem to be any way of killing the session (and hence freeing up the SpreadSheet) without re-starting SQL Server, and SQL Server is sitting waiting for a response through the linked server which is never going to arrive, the options are a little limited.

It’s not a walk in the park though – TCPView and SQL Server’s sys.dm_exec_connections DMV don’t seem to offer a direct means of correlating which session / connection and which TCPView entry is the offending connection, so a little bit of consideration (guesswork) is in order – but given that the alternative is to re-start an active production SQL Server instance, I know which one I prefer!