Chad Miller's Blog

I was reading a post by Linchi Shea in which he demonstrates a Perl script to Find the complete call tree for a stored procedure and thought, how would I do this in PowerShell?

Before we dissect a PowerShell approach, let’s look at a basic Perl approach. In Perl you typically find a command-line tool that produces the output you want albeit not in a usable format (Sure, sometimes you might get lucky and find a Perl module that does what you want, however when I used Perl I was never that lucky). This may involve several command lines tools. You’ll then parse the output of the tool and perhaps even use the output as input to other command-line tools. This is one of the strengths of a scripting language, you can quickly glue together tools to make a new tool--The scripter is a tool smith.

The Perl solution to getting a stored procedure call tree involves executing osql.exe and using some regular expressions to parse the output of sp_helptext, looking for EXECUTE statements. When I wrote Perl I would do much the same thing. As an example when I needed to report the share and NTFS permissions using Perl for all SQL Servers, I would execute rmtshare.exe parse the output which was then used as input to fileacl.exe and the output was then parsed.

We could do much the same thing in PowerShell, execute osql.exe and parse the output, however in the vast majority of cases if you’re parsing something in PowerShell you’re doing it the hard way. This is because PowerShell works with .NET, WMI and COM and you’ll typically find a .NET/WMI class or COM interface to do exactly what you want to do. Of course the hard part is knowing which class to use, if you’re not sure search and then ask. If you’re unable to find the answer yourself, both ServerFault and StackOverflow are good places to post these types of questions.

There is a nice set of .NET classes for parsing T-SQL code included with Visual Studio Team System 2008 Database Edition (VSDB) in the classes Microsoft.Data.Schema.ScriptDom and Microsoft.Data.Schema.ScriptDom.Sql. I’ve blogged about these classes before and even use them in SQL Server PowerShell Extensions (SQLPSX). At this point, you’re probably thinking I don’t have VSDB, well that’s OK, because the assemblies are redistributable I’ve included them with SQLPSX and the accompanying download to this post. There’s only one problem with the VSDB assemblies, they are implemented as interfaces. Let me explain the issue.

When writing PowerShell scripts you will often work with .NET classes. You’ll create an object from a .NET class and start working directly with it’s properties and methods. SQL Server Management Objects or SMO is great example of this. In one line of PowerShell code you have instant access to hundreds of properties and methods for a SQL Server object:

$srv = new-object ("Microsoft.SqlServer.Management.Smo.Server") Z002\SQL2K8

As you can see with the example above its very simple to start working with a .NET class. There is however, one notable exception when dealing with interfaces.  This is kind of fringe use case, in most instances you won’t have to deal with interfaces in PowerShell. The best way I can describe an interface is a fancy abstraction thing developers use when creating classes. Only interfaces aren’t real classes and in order to use an interface you have to implement a class. The important thing to note, as a scripter an interface just means we need to create a class to use it and this requires a .NET language other than PowerShell. Fortunately PowerShell V2 through the add-type cmdlet provides a way for us to do this within PowerShell.

Using add-type we can create a dynamic type in  .NET languages like C#. This means you can write C# within PowerShell. The first time I saw this I thought,”And why would I want to do that?” And then I remembered the problem with interfaces and in a way this functionality goes back to the main purpose of scripting, to glue together tools to create a new tool. Only in this case the tool is a snippet of C# code! So, I created a script called SQLParser that implements the Microsoft.Data.Schema.ScriptDom and Microsoft.Data.Schema.ScriptDom.Sql interfaces with a basic C# class.

To use SQLParser, source the script file and create a SQLParser object by specifying the SQL version, whether quoted identifiers are used and some valid T-SQL:

. ./SQLParser.ps1

$sqlparser = new-object SQLParser Sql100,$false,"Select * from dbo.authors"

The SQLParser class returns a fragment, which then is made up of batches and finally statements. We can iterate through the statements looking for a particular statement type. Having accomplished the hard part of finding and implementing a .NET class for T-SQL parsing, I then created a PowerShell script called Get-ProcedureCallTree.ps1 to return a stored procedure call tree:

If you run the script with the following parameters for HumanResources.uspUpdateEmplyeeHireInfo in the AdventureWorks database replacing Z002\SQL2K8 with your server name, you’ll should see the following output:

.\Get-ProcedureCallTree.ps1 uspUpdateEmployeeHireInfo "Z002\SQL2K8" AdventureWorks HumanResources            
            
Server    : Z002\SQL2K8            
Database  : AdventureWorks            
Schema    : dbo            
Procedure : uspLogError            
Source    : Z002\SQL2K8.AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo            
Target    : Z002\SQL2K8.AdventureWorks.dbo.uspLogError            
            
Server    : Z002\SQL2K8            
Database  : AdventureWorks            
Schema    : dbo            
Procedure : uspPrintError            
Source    : Z002\SQL2K8.AdventureWorks.dbo.uspLogError            
Target    : Z002\SQL2K8.AdventureWorks.dbo.uspPrintError

Not bad, but what I really want is to visualize the call tree. One method that works well for dependency maps is a script called Show-NetMap created by Doug Finke featured in his blog post PowerShell, Visualize the Peanut Butter Recall Data.. I’ve previously blogged about this script using it to create a complete database dependency map. To use Show-NetMap, source the script and pipe the source and target properties to Show-NetMap:

. .\Show-NetMap.ps1            
            
./Get-ProcedureCallTree.ps1 uspUpdateEmployeeHireInfo "Z002\SQL2K8" AdventureWorks HumanResources | `

Select Source,Target | Show-NetMap F

That’s better! The scripts and assemblies demonstrated in this post are available here:

A few tips

• I find myself using the alert script just to export what I'm seeing in my Operations Console (GUI) to a CSV file. I'll then send the simple PowerShell generated CSV reports to my team to address. If you know of an easier way to do this through the GUI, let me know.
• The maintenance mode scripts are not as useful, it's easier to use the GUI, right-click, and select maintenance mode. Even multiple computers can be selected. I suppose if I had a regularly scheduled maintenance, I would find more uses for a scripted maintenance window solution.
• It is surprising what SCOM finds, the most common warning condition I've seen include databases with autoclose or autoshrink on. In an active environment with thousands of databases a few inherited databases from SQL Express installation are bound to exist with either autoclose or autoshrink on. Once identified these warning are easy to fix.
• One other interesting warning is logical disk defragmentation. I had do a little research about this warning and posted a question to ServerFault. I won't repost here; see the ServerFault Q&A for detailed explanation. As part of the question I created a short script to retrieve logical fragmentation through WMI and PowerShell:

 $vols = Get-WmiObject -computername "Z002" Win32_Volume -filter "DriveType=3"           
$defragInfo = $vols | %{$_.DefragAnalysis() | add-member -membertype noteproperty vname $_.name -passThru}           
$defragInfo | %{$_.DefragAnalysis | add-member -membertype noteproperty DefragRecommended $_.DefragRecommended -passThru | add-member -membertype noteproperty vname $_.vname -passThru} | out-file ./tmp.txt

I wrote an article for SQLServerCentral entitled "Database Space Capacity Planning" that demonstrates a database and volume (disk) capacity planning solution I use in my production environment. Some highlights of the solution:

• Powershell is used to collect database and volume space information from a list of SQL Servers, which is then loaded into a consolidated reporting database
• A series of queries are used to calculate a days remaining metric i.e. the number of days until volume or database file runs out of space.
• Uses SQL Server Reporting Services (SSRS)  to provide self-service reports

See the full article for details.

SQL Saturday #21 on October 17th in Orlando

Tampa Code Camp 2009 on November 7th

• ETL
• Testing (PSUnit and WatIN)
• Deployment Automation
• Management API

• Changes in help files do not require changing your script files
• Use language settings
• Easily create HTML or CHM standalone help documention

 

Getting Started

1. Download WatIN
2. Install WatIN Recorder

I will present a session on "PowerShell and SQL Server Administration: In this sesssion we will look at automating common DBA tasks through Powershell. An overview of the CodePlex project SQL Serer Powershell Extensions will be provided. Specific topics covered include SSIS administration, replication and agent monitoring, performing simple ETL with Powershell and building graphs, charts and diagrams using Powershell."  

If you can attend both sessions we're planning a part 1 and part 2 format. This should be another great community event with plenty of PowerShell content and there's still time to register.

• Pipe the output of a Powershell command to automatically create a chart.
• Display the chart in a Windows Form
• Save the chart to an image file
• Specify either bar, column, line or pie chart types
• Display real-time automatic updating charts by passing scriptblock to the function. The scriptblock will execute at the specified interval and display chart updates (think Perfmon)
• Works with Powershell V1

To use the Library (Note: Requires .NET 3.5 framework):

• Download and install Microsoft Chart Controls for Microsoft .NET Framework 3.5
• Download LibraryChart
• If you want to extend/use MS Chart Control grab the document and samples. I referred to the C# WinForm samples frequently.

Here are a few examples:

Create a column chart of process workingset information:

Get-Process | Sort-Object -Property WS | Select-Object Name,WS -Last 5  | out-chart -xField 'name' -yField 'WS'

Save the chart to a file instead of displaying:

Get-Process | Sort-Object -Property WS | Select-Object Name,WS -Last 5 | out-chart -xField 'name' -yField 'WS' -filename 'c:\users\u00\documents\process.png'

Get-Process | Sort-Object -Property WS | Select-Object Name,WS -Last 5  | out-chart -xField 'name' -yField 'WS' -chartType 'Pie'

Produce a real-time line chart of process working set by passing a scriptblock i.e. the Powershell command between the two curly brackets. (Image note shown):

out-chart -xField 'name' -yField 'WS' -scriptBlock {Get-Process | Sort-Object -Property WS | Select-Object Name,WS -Last 1} -chartType 'line'

I'm not entirely happy with the script (uses global variable, hash generation code repeated, pie and line chart appearance could be improved), so if anyone would like to take the charting library even further go for it!

• Load the SMO version into a global variable $smoVersion which is then used in later sections of the code
• Load the SMOExtended assembly in all cases. On a system with just SMO 9.0 the SMO Extended assembly will not be present, but that's OK. Loading a non-existent assembly does not produce an error in Powershell. Surprising, but true, try it yourself by misspelling an assembly name.