Challenge Me! Capturing SQL server data with PowerShell

Hello PowerShell lovers! If you are reading this blog post, then you are likely interested in attending my extended session at SQLBITS SUPERHEROES ( Honored to be selected)

Any data professional who is responsible for performance tuning, has their way of gathering data to troubleshoot and solve issues. But what if I told you there was a different way, a better way, to collect the data you need without consuming additional SQL Server resources?

Attend my session and learn how to leverage PowerShell to collect data from Perfmon, DMVs, and more. This session will feature demos where attendees will learn about some .Net classes to control timer events and the PowerShell Register-Objectevent cmdlet.

Attendees are encouraged to send their challenges to me by email at [email protected]. I will review their scripts and select some of them to demonstrate in the session by offering an alternative using PowerShell and the event  subsystem.

I will split your challenges into three categories:

1. Perfmon Counters
2. DMVs, DMFs, etc.
3. Other

Using one challenge from each category, here are examples of the kinds challenges I will demonstrate:

Example 1: Perfmon Counters

Hello Laerte,

My name is James Tiberius Kirk, I am Captain of the starship USS Enterprise and we are testing a new warp drive and our SQL Server´s servers are very, very busy with that. Our DBAs need a way to collect some performance counters, in an interval of time of 15 minutes and the data stored in a repository for further analysis. This process need to be done remotely and we cannot use any kind of SQL Server resources for that. We don’t want to use any kind of GUI for that as well. The performance counters are:SQLServer: Buffer Manager: Buffer cache hit ratio

SQLServer: Buffer Manager: Page life expectancy
SQLServer: SQL Statistics: Batch Requests/Sec
SQLServer: SQL Statistics: SQL Compilations/Sec
SQLServer: SQL Statistics: SQL Re-Compilations/SecCan you help us?  Thanks!

Example 2: DMVs, DMFs, etc.

Hello Laerte,

My name is Anakyn Skywalker, (a.k.a. Darth Vader, the most powerful Sith Master ever.) We are building a new Death Star and our SQL Server´s servers are extremely busy with inserts and updates. We need to capture and store the fragmentation data from all indexes in a  table called BlowUpThePlanet in a server called DeathStarProd Database VaderIsTheBest. As I told you before and I will not say again, it is a very busy server and because of that, the interval of gathering needs to be hourly. I am awaiting your help today. *FORCE CHOKING*

Example 3: Other

Hello Laerte,

My Name is Neo. Everything you know, or you think know, actually does not exist—but we can talk about that later.  We are trying to inject a new code in the Matrix (don’t worry about that right now either) and I need to capture some data. I will send to you the TSQL and all information that I need. Can you help me?  Thanks.

TSQL
Insert into XXXX Select XXXX
Interval – 10 seconds
Server Name – XXX
Database Name – YYY

Please send your challenges to [email protected]. I will select three of them, one from each category and I will demonstrate how to do them using PowerShell without using any kind of SQL Server resources, after  I explain the Timer class and the register-objectevent cmdlet.

That is it! And remember: Always… if it is PowerCool, it is PowerShell.