Search results matching tags 'PowerShell' and 'sql'

Behind the scenes of PowerShell and SQL

rob_farley — Tue, 12 Feb 2013 00:11:46 GMT

Every year, PowerShell increases its stranglehold on the Windows Server system and the applications that run upon it – with good reason too. Its consistent mechanisms for interaction between its scripting interface and the underlying systems make it easy for people to feel comfortable, and there is a discoverability that has been lacking in many other scripting environments.

Of course, SQL Server hasn’t been overlooked at all, and it’s coming up to five years since the SnapIns were made available (even longer since people started to dabble with SQL using PowerShell).

But what’s going on behind the scenes? Does PowerShell present a threat to those amongst us who will always prefer T-SQL? Does PowerShell give us new options that are not available any other way? Well, let’s have a bit of a look, especially since this month’s T-SQL Tuesday (hosted by Wayne Sheffield who tweets as @DBAWayne) is on the topic of PowerShell.

So we know PowerShell is useful. However we spin it up, we can quickly jump into writing commands, whether it be interacting with WMI, hooking into some .Net assembly we’ve loaded up, or simply browsing the file system. I’ve developed a tendency to use it to start whichever SQL instances I’m needing for the day – by default I have all of them turned off, since I don’t know which one I’ll be wanting most.

If we’re going to be interacting with SQL, then it’s easiest to either load up the SQLPS environment directly (there’s a PowerShell shortcut within Management Studio), or else (as I do), start a PowerShell window with the Snapin loaded. I prefer this later option, as the SQLPS environment is a slightly cut-back version of PowerShell. But either way – the stuff I’ll continue on with is essentially the same whichever environment you use.

If you’ve talked about SQL with me long enough, you’ll have come across the fact that I often use SQL Profiler when I’m curious about where to find information. My former colleague Jim McLeod (@Jim_McLeod) blogged about this a few months ago, with an example that I remember looking through with him four or five years ago. It’s a great technique that works on all kinds of things, even across different versions of SQL Server. It also adds as a terrific reminder that Management Studio is not a special application, it simply knows how to ask for the pieces of information that it shows.

But PowerShell (or SMO, for that matter), that’s in the .Net world. Surely that would be able to bypass the clunky T-SQL stuff that Management Studio does... I mean, Management Studio has to be flexible enough to work across remote servers, talking through firewalls that only allow T-SQL interfaces. Surely PowerShell has access to a deeper magic.

Well, no. PowerShell still lets you talk to remote servers, and ends up using the same methods.

Let’s prove it.

Spin up Profiler, and start a trace against your favourite instance. I like to watch for SQL:BatchCompleted, SP:StmtCompleted and RPC:Completed events when doing this kind of thing. I’m using an instance that isn’t doing anything else, but you could apply a ColumnFilter to filter the events to things with an ApplicationName starting with SQLPS if you prefer.

With that running, I jump into PowerShell and do something like:

PS SQLSERVER:\sql\localhost\sql2008r2> dir Databases | ft name

This lists the names of the databases on my SQL2008R2 instances. You don’t need to see the results, you can imagine them for yourself.

If PowerShell were using some secret interface, it’s unlikely we’d see something in Profiler. But it’s not, and we see a bunch of stuff.

We see a bunch of Remote Procedure Calls, each with a Stored Procedure Statement Completed event showing the same information. And look – we see queries against master.sys.databases, asking for the name of each of the databases, passing in the name as a parameter. Brilliant! Notice just a bit earlier though, there’s a SQL:BatchCompleted call. This means that a query has been passed in directly. It’s this:

SELECT
CAST(
serverproperty(N'Servername')
AS sysname) AS [Server_Name],
dtb.name AS [Name]
FROM
master.sys.databases AS dtb
WHERE
(CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=0)
ORDER BY
[Name] ASC

So it grabs the list of database names first, and then makes extra calls to be able to fetch the list of names again, one by one.

The reason why it’s grabbing the list of names one by one isn’t because it’s stupid and is asking to be ridiculed. It’s because we've asked to see that property, and I guess the PowerShell people figured that no matter what property you ask for, it’ll go and fetch it to show you.

When I asked for the CompatibilityLevel property instead, I got some different rows thrown in. Interestingly though, it still asked for the name each time.

Also interestingly, when I asked for the CompatibilityLevel a subsequent time, the calls for “SELECT dtb.compatibility_level…” weren’t in there. They’d been cached by the PowerShell environment – important to note if you ever come across PowerShell giving you old values.

So what about asking something more interesting? Let’s try asking about the IndexSpaceUsage in AdventureWorks.

PS SQLSERVER:\sql\localhost\sql2008r2> gi Databases\AdventureWorks | ft IndexSpaceUsage

The result tells me it’s 62576. Yeah, but today I’m not interested in that, just what happened in the trace.

Four entries. An SP:StmtCompleted with matching RPC:Completed, and two SQL:BatchCompleted.

The SP:StmtCompleted and RPC:Completed were this statement, passing in the parameter value ‘AdventureWorks’. Clearly incredibly informative.

SELECT
CAST(0 AS float) AS [IndexSpaceUsage],
dtb.name AS [DatabaseName]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=@_msparam_0)

This is getting the value zero. Wow. Brilliant stuff.

The last entry – the second of the two SQL:BatchCompleted events is:

select convert(float,low/1024.) from master.dbo.spt_values where number = 1 and type = 'E'

If you run this in Management Studio, you’ll discover it gives the value 8. Ok.

The other entry is more interesting.

use [AdventureWorks]
SELECT
SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [DataSpaceUsage],
SUM(a.used_pages) AS [IndexSpaceTotal]
FROM
sys.allocation_units AS a INNER JOIN sys.partitions AS p ON (a.type = 2 AND p.partition_id = a.container_id) OR (a.type IN (1,3) AND p.hobt_id = a.container_id)

This is more like it! We run this in Management Studio, and we see two values. DataSpaceUsage is 13682, IndexSpaceTotal is 21504. Neither are our value 62576. But we do have clues in the column names, and in that value 8 that came back too. We can easily deduce that it’s actually (IndexSpaceTotal-DataSpaceUsage)*8, and we have ourselves a nice little method for working out the IndexSpaceUsage ourselves now if we need it.

Or we can just ask PowerShell next time as well.

Incidentally – if you’re considering doing the MCM Lab exam some time, then you might find that a familiarity with PowerShell comes in really handy. I’m not saying there are PowerShell questions on the exam at all – I’m just suggesting that you may find that PowerShell becomes a really useful way of getting at some of the information that you’re looking for. If you’re stumbling around the list of DMVs trying to remember which one it is that stores some particular thing, remember that you might be able to get the data out more easily if you use PowerShell instead.

So can we discover secret things about SQL from PowerShell? Are there things we can do in PowerShell that are impossible through other mechanisms? Hooks that let us break the rules even?

Recently, Kendal van Dyke asked a question about this kind of thing on Twitter. He was wondering if you could have a default constraint on a column in a view. The reason for his wondering was that he saw a property on a view column in PowerShell that made him wonder. The answer is no though, and there’s a simple reason.

PowerShell is a programmatic interface. It involves classes and property and methods. It does things row by row, which is why much of what you see in that trace feels amazingly pedantic – asking about things which shouldn’t have to be that complicated. The implication of this though, is that PowerShell reuses the concept of a column, regardless of whether this is a column in a table, a view, or anywhere else it decides to need a column. The fact that columns in tables have some extra properties isn’t enough to make this class re-use pointless. If we try to set a Default constraint for a column in a view though, we get an error, just like if we tried to do it any other way.

The PowerShell I used was:

$db = Get-Item SQLSERVER:\sql\localhost\sql2008r2\Databases\AdventureWorks
$def = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Default –ArgumentList $db, "ViewDefault"
$def.TextHeader = "CREATE DEFAULT ViewDefault AS"
$def.TextBody = "'ABC'"
$def.Create()
$def.BindToColumn("vStateProvinceCountryRegion","StateProvinceCode","Person")

The code that ended up getting called was to the stored procedure sp_bindefault (despite it being deprecated). Naturally, trying to execute this against a view column gives an error regardless of what wrappers you have put around it – PowerShell or not.

So PowerShell is very useful, and it provides a handy way of getting to a lot of things that could be otherwise hard. But looking below the surface, it isn’t able to circumvent the basic restrictions of SQL Server, because it still ends up doing its work using T-SQL.

@rob_farley

PowerShell script to help uninstall SQL Server 2008 R2 Evaluation Edition

rob_farley — Mon, 23 May 2011 14:04:23 GMT

If you haven’t been caught by a client (or even yourself) installing SQL 2008 R2 Evaluation Edition and then letting it expire, you might not realise the pain that this causes. To say it’s frustrating is a serious understatement.

Fellow SQL MVP and SQLBlog blogger Aaron Bertrand (@aaronbertrand) has an excellent post on the matter, which he put together back in October 2010. I had cause to use it recently, but got somewhat put off by searching through the registry.

So I put a line of PowerShell together (which I’ve split across 5 for easier reading):

Get-ChildItem HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall |
select @{Name='Guid';Expression={$_.PSChildName}}, @{Name='Disp';Expression={($_.GetValue("DisplayName"))}} |
where-object {$_.Disp -ilike "*SQL*R2*"} |
where-object {$_.Guid -like '{*'} |
% {"rem " + $_.Disp; 'msiexec /x "' + $_.Guid + '"'; ''} > uninstallR2.bat

If you run this, it will produce the file that Aaron recommended. You can look down it for anything you don’t want to get rid of, and maybe reorder the odd thing, and then run it. Then you should be able to install Developer Edition, and breathe much easier.

The script is quite straight forward, it just lists everything in the uninstall bit of the registry, gets the DisplayName values out, filters them, and outputs the results in a few lines for the batch file. Nothing amazingly complicated, but useful for getting through the list quickly.

(Big thanks to Aaron for working out what was required for the uninstall, and Aaron Nelson (@sqlvariant) for answering a quick question when I was putting the final touches on the script)

Using SQL Execution Plans to discover the Swedish alphabet

rob_farley — Mon, 03 Jan 2011 05:54:00 GMT

SQL Server is quite remarkable in a bunch of ways. In this post, I’m using the way that the Query Optimizer handles LIKE to keep it SARGable, the Execution Plans that result, Collations, and PowerShell to come up with the Swedish alphabet.

SARGability is the ability to seek for items in an index according to a particular set of criteria. If you don’t have SARGability in play, you need to scan the whole index (or table if you don’t have an index). For example, I can find myself in the phonebook easily, because it’s sorted by LastName and I can find Farley in there by moving to the Fs, and so on. I can’t find everyone in my suburb easily, because the phonebook isn’t sorted that way. I can’t even find people who have six letters in their last name, because also the book is sorted by LastName, it’s not sorted by LEN(LastName). This is all stuff I’ve looked at before, including in the talk I gave at SQLBits in October 2010.

If I try to find everyone who’s names start with F, I can do that using a query a bit like:

SELECT LastName FROM dbo.PhoneBook WHERE LEFT(LastName,1) = 'F';

Unfortunately, the Query Optimizer doesn’t realise that all the entries that satisfy LEFT(LastName,1) = 'F' will be together, and it has to scan the whole table to find them.

But if I write:

SELECT LastName FROM dbo.PhoneBook WHERE LastName LIKE 'F%';

then SQL is smart enough to understand this, and performs an Index Seek instead.

To see why, I look further into the plan, in particular, the properties of the Index Seek operator. The ToolTip shows me what I’m after:

You’ll see that it does a Seek to find any entries that are at least F, but not yet G. There’s an extra Predicate in there (a Residual Predicate if you like), which checks that each LastName is really LIKE F% – I suppose it doesn’t consider that the Seek Predicate is quite enough – but most of the benefit is seen by its working out the Seek Predicate, filtering to just the “at least F but not yet G” section of the data.

This got me curious though, particularly about where the G comes from, and whether I could leverage it to create the Swedish alphabet.

I know that in the Swedish language, there are three extra letters that appear at the end of the alphabet. One of them is ä that appears in the word Västerås. It turns out that Västerås is quite hard to find in an index when you’re looking it up in a Swedish map. I talked about this briefly in my five-minute talk on Collation from SQLPASS (the one which was slightly less than serious).

So by looking at the plan, I can work out what the next letter is in the alphabet of the collation used by the column. In other words, if my alphabet were Swedish, I’d be able to tell what the next letter after F is – just in case it’s not G.

It turns out it is… Yes, the Swedish letter after F is G. But I worked this out by using a copy of my PhoneBook table that used the Finnish_Swedish_CI_AI collation. I couldn’t find how the Query Optimizer calculates the G, and my friend Paul White (@SQL_Kiwi) tells me that it’s frustratingly internal to the QO. He’s particularly smart, even if he is from New Zealand.

To investigate further, I decided to do some PowerShell, leveraging the Get-SqlPlan function that I blogged about recently (make sure you also have the SqlServerCmdletSnapin100 snap-in added. To do that, run get-pssnapin -reg to see if it's on the machine, and get-pssnapin -reg | add-pssnapin to make the cmdlet available).

I started by indicating that I was going to use Finnish_Swedish_CI_AI as my collation of choice, and that I’d start whichever letter came straight after the number 9. I figure that this is a cheat’s way of guessing the first letter of the alphabet (but it doesn’t actually work in Unicode – luckily I’m using varchar not nvarchar. Actually, there are a few aspects of this code that only work using ASCII, so apologies if you were wanting to apply it to Greek, Japanese, etc). I also initialised my $alphabet variable.

$collation = 'Finnish_Swedish_CI_AI';
$firstletter = '9';
$alphabet = '';

Now I created the table for my test. A single field would do, and putting a Clustered Index on it would suffice for the Seeks. [Edit: You may need to insert some rows if you find that it's producing an Index Scan. Theoretically it should prefer to Scan, knowing there's no rows, but my tests showed it would always Seek]

Invoke-Sqlcmd -server . -data tempdb -query "create table dbo.collation_test (col varchar(10) collate $collation primary key);"

Now I get into the looping.

$c = $firstletter;
$stillgoing = $true;
while ($stillgoing)
{

I construct the query I want, seeking for entries which start with whatever $c has reached, and get the plan for it:

$query = "select col from dbo.collation_test where col like '$($c)%';";
[xml] $pl = get-sqlplan $query "." "tempdb";

At this point, my $pl variable is a scary piece of XML, representing the execution plan. A bit of hunting through it showed me that the EndRange element contained what I was after, and that if it contained NULL, then I was done.

$stillgoing = ($pl.ShowPlanXML.BatchSequence.Batch.Statements.StmtSimple.QueryPlan.RelOp.IndexScan.SeekPredicates.SeekPredicateNew.SeekKeys.EndRange -ne $null);

Now I could grab the value out of it (which came with apostrophes that needed stripping), and append that to my $alphabet variable.

if ($stillgoing)
{
$c=$pl.ShowPlanXML.BatchSequence.Batch.Statements.StmtSimple.QueryPlan.RelOp.IndexScan.SeekPredicates.SeekPredicateNew.SeekKeys.EndRange.RangeExpressions.ScalarOperator.ScalarString.Replace("'","");
$alphabet += $c;
}

Finally, finishing the loop, dropping the table, and showing my alphabet!

}
Invoke-Sqlcmd -server . -data tempdb -query "drop table dbo.collation_test;";
$alphabet;

When I run all this, I see that the Swedish alphabet is ABCDEFGHIJKLMNOPQRSTUVXYZÅÄÖ, which matches what I see at Wikipedia. Interesting to see that the letters on the end are still there, even with Case Insensitivity. Turns out they’re not just “letters with accents”, they’re letters in their own right.

I’m sure you gave up reading long ago, and really aren’t that grabbed about the idea of doing this using PowerShell. I chose PowerShell because I’d already come up with an easy way of grabbing the estimated plan for a query, and PowerShell does allow for easy navigation of XML.

I find the most interesting aspect of this as the fact that the Query Optimizer uses the next letter of the alphabet to maintain the SARGability of LIKE. I’m hoping they do something similar for a whole bunch of operations.

Oh, and the fact that you know how to find stuff in the IKEA catalogue.

Footnote:

If you are interested in whether this works in other languages, you might want to consider the following screenshot, which shows that in principle, it should work with Japanese. It might be a bit harder to run this in PowerShell though, as I’m not sure how it translates. In Hiragana, the Japanese alphabet starts あ, ぃ, ぅ, ぇ, ぉ, ...

PowerShell to fetch a SQL Execution Plan

rob_farley — Tue, 07 Dec 2010 01:14:32 GMT

With PowerShell becoming the scripting language of choice for many people, I’ve occasionally wondered about using it to analyse execution plans. After all, an execution plan is just XML, and PowerShell is just one tool which will very easily handle xml.

The thing is – there’s no Get-SqlPlan cmdlet available, which has frustrated me in the past. Today I figured I’d make one.

I know that I can write T-SQL to get an execution plan using SET SHOWPLAN_XML ON, but the problem is that this must be the only statement in a batch. So I used go, and a couple of newlines, and whipped up the following one-liner:

function Get-SqlPlan([string] $query, [string] $server, [string] $db)
{ return ([xml] (invoke-sqlcmd -Server $server -Database $db -Query "set showplan_xml on;`ngo`n$query").Item( 0)) }

(but please bear in mind that I have the SQL Snapins installed, which provides invoke-sqlcmd)

To use this, I just do something like:

$plan = get-sqlplan "select name from Production.Product" "." "AdventureWorks"

And then find myself with an easy way to navigate through an execution plan!

At some point I should make the function more robust, but this should be a good starter for any SQL PowerShell enthusiasts (like Aaron Nelson) out there.

Fetching Latitude and Longitude Co-ordinates for Addresses using PowerShell

rob_farley — Sun, 23 May 2010 06:59:00 GMT

Regular readers of my blog (at sqlblog.com – please let me know if you’re reading this elsewhere) may be aware that I’ve been doing more and more with spatial data recently. With the now-available SQL Server 2008 R2 Reporting Services including maps, it’s a topic that interests many people.

Interestingly though, although many people have plenty of addresses in their various databases (whether they be CRM systems, HR systems or whatever), my experience shows that many people do not store the latitude and longitude co-ordinates for those addresses.

Luckily, the Bing Maps API provides everything you need!

Start by going to bingmapsportal.com, logging in using a LiveID and creating an account:

Then you can create a key using the link on the left. This key will be attached to a website, and looks something like: Apsjm7zVthPFMxlfpQqKhPPZrAupI-_aGH-CvT2b... Now you can use the Bing Maps API to fetch the information you need. Obviously check the terms and conditions to see if you will need to pay for your usage or not. The Bing Maps API works through web services, so it’s easy enough to use almost any system for this. You could easily make a CLR Function for use within T-SQL, but I’m going to show you how to do it using PowerShell.

Let’s start by creating a Web Service Proxy to the URL of the webservice.

$ws = New-WebServiceProxy -uri http://dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl;

By passing $ws into Get-Member (using the command: $ws | Get-Member), we can see that there is a Geocode method, which requires a parameter of type GeocodeRequest. Actually, the type to use is much more complicated, but it’s easy to create a variable for it using:

$wsgr = new-object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.GeocodeRequest;

This variable will take the address to look up in its Query property, but we’ll do that in a moment. First we need to provide credentials, which is that key we created on the website. I’ve stored mine in a variable called $key, so that I don’t have to display it in demonstrations that might be recorded.

$wsgrc = new-object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.Credentials;
$wsgrc.ApplicationId = $key;
$wsgr.Credentials = $wsgrc;

Now when we call the method on our web service, Bing Maps will know that it’s us that have called it.

Now I can make a request. If I use a single address, I can just use the Query property of the GeocodeRequest object, as I mentioned earlier. When I get my results from the Geocode() call, I can get multiple lines, and each of them has a bunch of useful information including (as I find most useful), the Formatted Address, and location co-ordinates. I can easily display this by passing the Results into a Select-Object call. I’m just handling the first result of each call, as will become clear in a moment.

$wsgr.Query = 'Adelaide, Australia';
$wsr = $ws.Geocode($wsgr);
$wsr.Results[0] | select {$_.Address.FormattedAddress}, {$_.Locations[0].Longitude}, {$_.Locations[0].Latitude};

As readers familiar with PowerShell will already appreciate, there is a good potential for looping through many addresses. I did this with locations in the world that have PASS chapters recently, but to simplify this, my example uses just four.

$uglist = 'Adelaide, AU', 'Aukland, NZ', 'South Africa', 'San Deigo, USA';
$uglist | % {$wsgr.Query = [string] $_; $wsr = $ws.Geocode($wsgr); $wsr.Results[0] | select {$_.Address.FormattedAddress}, {$_.Locations[0].Longitude}, {$_.Locations[0].Latitude};}

This gives the following results:

$_.Address.FormattedAddress                           $_.Locations[0].Longitude                $_.Locations[0].Latitude
---------------------------                           -------------------------                ------------------------
Adelaide, Australia                                            138.599731698632                        -34.925769791007
Auckland, New Zealand                                          174.765734821558                       -36.8473847955465
South Africa                                                   25.0630002468824                       -29.0459994971752
San Diego, CA                                                 -117.161724865437                        32.7156852185726

You’ll notice that the FormattedAddress property shows the address in a standard format. This is great, because it will handle spelling mistakes (see how I left the ‘c’ out of ‘Auckland’ when I wrote it – for me I did this on purpose, but in most user-input systems, spelling mistakes are a common problem), and it will provide a consistency for punctuation, abbreviations, etc. Notice that I used ‘AU’, ‘NZ’ and ‘USA’, which were all transformed into something else in the web-service call.

With the Lat/Long details here, it’s very simple to get this into a database, or a file, or whatever format is required. For me, I put them into a database along with all the other PASS Chapter locations I had looked up (using the public data from the website), and using the Bing Maps Silverlight control, came up with something like this:

It was a bit more work to colour the pushpins by the region, and putting tooltips in with extra information, but bridging the gap between a pile of addresses and a map is actually remarkably straight-forward with the Bing Maps API.