Thanks, Jason and Stacia, for allowing me to present tonight!
Here are the materials from the presentation, including bonus favorite scripts/functions.
PowerShell: The Way of the DBA Dragon – presentation scripts and slide deck.
I used Start-Demo to play back the commands to avoid typing.
“Hey, can you run this for me in prod?”
There are many reasons the wording in this request is a red flag/indicator of broken processes, but let’s put those aside for a moment.
The dev that asked you to run this query may have a legitimate reason.
Let’s say you check the estimated execution plan, and the query seems to be low-impact, and unlikely to cause blocking. You run the query. Yep, it only modified a few rows and ran in less than a second. A few minutes later an alert indicates that the query has become a long running statement and is still active.
It turns out there were two BEGIN TRAN statements in the t-sql, and a COMMIT was only executed once. The transaction is still open – uncommitted!
In the future you can guard against this scenario by just checking @@TRANCOUNT after the commit and the time of the commit. If @@TRANCOUNT has a non-zero value, a transaction is still open.
Placing the following two commented lines in SQLfile.sql or pasting the second line at the end of the query can help:
--BEGIN TRAN
[paste query to be executed here, and uncomment the above line if the query modifies data and lacks a BEGIN TRAN]
After successfully running the t-sql, commit the transaction with the below statement by selecting and executing the remainder of the line after the two dashes:
--COMMIT;SELECT @@TRANCOUNT [Open Transactions], Getdate() [timestamp]
A zero value in the Open Transactions column indicates this session has committed the transaction, and the timestamp lets you know when the transaction was committed, just in case you return to this session later on.
In November of 2012 I presented to the Utah Valley SQL Server User Group on finding performance issues within SQL Server instances by focusing on storage and i/o for data and log files.
The Identifying i/o Bottlenecks slide deck is now available. 
I used some slides from Wes Brown’s May 2011 SQL Rally presentation on Understanding Storage Systems and SQL Server, and included some PowerShell snippets.
There is a PowerShell script mentioned in a previous post I frequently use to view the storage used by a particular server referenced in the deck.
