Search results matching tag 'T-SQL Tuesday'

Part of the journey: failure

rob_farley — Tue, 14 May 2013 00:09:00 GMT

The topic for this month’s T-SQL Tuesday is about the journey. Wendy Pastrick’s choice (I’m hosting again next month!).

There are a lot of journeys. There are some that just keep going, and others that seem to finish (some in success; some in failure). Of course, many of the ones that finish end up being the start of new journeys, but sometimes they don’t need to continue – they just need closure. There are things that can be learned regardless of how things went, whether or not goals were reached, and whether or not there was failure.

There’s been a few things recently to remind me of this...

I visited a company recently who has put a video together promoting the idea of failure. It wasn’t asking that people fail, but said “Go ahead and fail,” because failure happens. They had been through a rough time, but were persisting and seeing things turn around.

Just the other night, we saw the musical Chitty Chitty Bang Bang (you probably know the movie – the Bond film where Dick van Dyke stars as the guy with the gadget-car, who takes on Goldfinger and falls for the girl with the inappropriate name). Anyway, there’s a brilliant song in that called “The Roses of Success” (YouTube link there). It has the same sentiment – “…from the ashes of disaster grow the roses of success!”

A few years ago, my kids started saying “FAIL!” when someone did something wrong. I can’t say I liked the insult. Far worse would’ve been “DIDN’T TRY!” It would be very easy to just stay in bed and ‘avoid failure’ that way, but anyone who fails has at least done something. To fail, you must at least be active.

I talk to a lot of people about Microsoft Certification, particularly people who have failed an exam. I tell everyone (not just those who have failed before) to try the exams before they feel they’re ready for them, . What’s the worst that can happen? Worst case, they don’t pass. But how is that a bad thing? It might feel less than brilliant (I know, it’s happened to me before), but it gives an opportunity to target the weak areas before having a subsequent attempt. It doesn’t matter how many attempts it takes to get a passing score – the wrong option would be to give up. Studying can be excellent, but not to the point of causing extra stress.

There are things in life we do easily, and there are things that we struggle with. I know there’s a bunch of stuff in my own life that falls into both categories. I don’t want this post to be a list of the things that I’m not doing well – I simply want to point out that I want to keep trying.

With God’s help, I can improve in the areas in which I’m not excelling, and start to smell the roses of success.

T-SQL Tuesday #41 - Presenting and Loving it!

AllenMWhite — Tue, 09 Apr 2013 15:56:00 GMT

For this T-SQL Tuesday Bob Pusateri asked us to share how we came to love presenting.

Before I ever got involved in computing technology I had (and still have) a love for the theatre, specifically musical theatre. When I was little the majority of albums (this was the 1950s, kids) we had were cast albums from Broadway shows my parents had seen at Musicarnival. I performed in shows all through school, and was a Theatre Major at Kent State University before I realized I needed to make a living.

It was this love of "performing" that had employers sending me to the trade shows to talk with customers. I wasn't the typical programmer, I could talk with people, even people I didn't know! One company was so impressed in my performance when I'd played Harold Hill in the show The Music Man that they made me a salesman. That failed miserably.

About eight years ago I started attending meetings of the Cleveland SQL Server group, at the Microsoft office in Independence, Ohio. As people had questions I'd pipe up and answer when I could, and as they needed someone to present I offered to put together my materials as a presentation. The first few times were a bit rough - ok, they were very rough - but the group was gracious and I learned to organize the material better. In addition to the user group presentations I'd been giving training presentations to the staff at work, getting them to understand SQL Server better to make my job as the DBA easier. This helped me develop my skills a lot.

I was first selected to speak at the PASS Summit in 2006, and I presented a session on SMO (Server Management Objects) and my demos all used Visual Basic. After the presentation a number of people came up to me and said they were administrators and weren't allowed to have Visual Studio on their desktop. PowerShell had just been introduced and I adjusted my material to use PowerShell.

Also in 2006 I became a Microsoft Certified Trainer (MCT). In teaching the official Microsoft courses I learned how to work with material I hadn't created myself, which then helped me build better presentations of my own material. I also learned that having to teach material forced me to learn it better myself. Someone will always ask questions about an aspect of the topic I'd never encountered. I found the best way to learn any topic is to teach it.

In the course of events I became the leader of the Ohio North SQL Server Users Group and at each meeting I ask everyone there to think about putting together a presentation for the group, so we can learn from them, and they can learn it better. We've got a great group of people who now present not just at our group but at others in the area and at SQL Saturdays and even the PASS Summit as well! I can't tell you how pleased I am at how many from our group are regular presenters in the SQL Server community now. These people include Erin Stellato ( b | t ), Sarah Dutkiewicz ( b | t ), Brian Davis ( b | t ), Adam Belebczuk ( b | t ), Craig Purnell ( b | t ), and Colleen Morrow ( b | t ), but more are stepping up regularly, and for that I thank each one of them.

Presenting is one of those magic activities in which everyone benefits. I'm fortunate that I have a natural inclination towards it, but love to see new people stepping up and sharing their experience and knowledge with the rest of the community.

See you at the next event!

Allen

Why I present

rob_farley — Tue, 09 Apr 2013 00:09:13 GMT

Louis Davidson just asked me why I write, and now Bob Pusateri (@sqlbob) is asking me why I present, which is his question for this month’s T-SQL Tuesday.

If you didn’t follow the link to see his actual question, you’ll need to know that he actually posed the question “How did you come to love presenting?”

Well, sometimes I don’t, but on the whole, I have to admit that presenting is part of who I am, and I miss it if I’m not presenting. It’s why despite being a PASS board member (that link will only seem relevant if you’re reading this while I’m still one) and having plenty of reason to NOT present at the PASS Summit in 2013, I’ve submitted the maximum number of abstracts for consideration. It’s why I want to be teaching more, both online and in the classroom, and so on.

It’s not that I think I have anything important to say (although I do only ever teach / present on things that I think are important).

It’s not that I think I’m good at presenting (my feedback scores beg to differ).

It’s not that I’m comfortable presenting (I still get ridiculously nervous most of the time).

I’m just addicted to it.

It’s a drug – it really is.

I spend my time walking around the room, or around the stage, explaining things to people, watching for those moments when the audience gets it, and... well, I’m addicted to it.

If you watch http://www.sqlbits.com/Sessions/Event5/Designing_for_simplification, you’ll see a few things. I was in Wales, and had started with the few words in Welsh that I know (but that’s been edited out – hopefully when I thought I was saying ‘hello’ I wasn’t actually insulting anyone). I nearly fell off the stage. I broke the microphone. I typed some things wrong in my queries. People complained that I didn’t say anything significant…

But around 33:10 in, you hear the audience almost start clapping. IN THE UK (where people don’t clap for presentations). It’s a moment where people see something they weren’t expecting, and (hopefully) realise the potential in what they’ve heard.

Phil Nolan wrote nicely about me on his blog, and said “Those of you who know Rob Farley will know he’s a funny guy with an enormous armoury of shockingly bad jokes.” More importantly though, he wrote “His design tips challenged a number of our ideas and meant I took away many valuable techniques,” which helped me know why I present.

…because it’s not about me, it’s about you. I present because at least one of the people in the audience will benefit from it. And that’s addictive.

@rob_farley

T-SQL Tuesday #040: Files, Filegroups and Visualizing Interleaved Objects

merrillaldrich — Tue, 12 Mar 2013 05:08:52 GMT

Early in my career as a DBA, I have to admit I didn’t quite “get” what all the fuss was about with the multiple file and multiple filegroup capability in SQL Server. Over the years, though, as I learned more about partitioning, backup strategies for large databases and, most importantly, storage internals I’ve started to catch on. For today’s T-SQL Tuesday, I thought I would share an early lesson, using the newly released SQL File Layout Viewer utility available here.

Adam Machanic started T-SQL Tuesday several years ago and it’s been a great way to motivate bloggers and readers to share ideas around a topic. This month’s even is hosted by Jen McCown / MidnightDBA. An awesome idea, as ever.

What I hope to illustrate today is a simple but vital concept about files and file groups: files, on their own, use a proportional fill algorithm in SQL Server and will receive new data from any object that needs more space. If an object needs additional storage, an allocation will be made in an empty area of whichever file has the most space available. Under the multi-file feature alone, there is no attempt whatsoever to separate objects in the database logically into different files. The result is that objects can appear to “round robin” through the files as more space is allocated – it’s not really a round-robin algorithm, but if the files are all about equally full, it looks similar.

Filegroups, on the other hand, allow logically grouping objects in a way that specifically directs them into separate files, and so controls the physical layout of those objects on disk. Using filegroups it’s possible to direct an object into a file (or set of files) and physically separate it from the rest of the database.

Because of this distinction, it’s been my experience that filegroups are far and away the more useful of the two features, and if I have a database that merits splitting up, I go straight for filegroups, with one physical file per group. (There are some edge case exceptions like TempDB allocation contention that are outside the scope of this post.)

Interleaving

There are a few reasons we’d want to physically separate objects, but, because it so clearly illustrates the difference, I want to show interleaving today. I made a demo database using the following script:

USE [master]
GO

:setvar datapath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA"
:setvar logpath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA"

CREATE DATABASE [VizDemo3] ON  PRIMARY 
( 
    NAME = N'VizDemo3', FILENAME = N'$(datapath)\VizDemo3.mdf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
FILEGROUP [MultiFileFG] 
( 
    NAME = N'VizDemoMFGF1', FILENAME = N'$(datapath)\VizDemoMFGF1.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
( 
    NAME = N'VizDemoMFGF2', FILENAME = N'$(datapath)\VizDemoMFGF2.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
( 
    NAME = N'VizDemoMFGF3', FILENAME = N'$(datapath)\VizDemoMFGF3.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
FILEGROUP [SingleFileFG1] 
( 
    NAME = N'VizDemoSFG1', FILENAME = N'$(datapath)\VizDemoSFG1.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
), 
FILEGROUP [SingleFileFG2] 
( 
    NAME = N'VizDemoSFG2', FILENAME = N'$(datapath)\VizDemoSFG2.ndf' , 
    SIZE = 25600KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB 
)
LOG ON 
( 
    NAME = N'VizDemo3_log', FILENAME = N'$(logpath)\VizDemo3_log.ldf' , 
    SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB 
)
GO

ALTER DATABASE [VizDemo3] SET RECOVERY SIMPLE 
GO

USE VizDemo3
GO

CREATE TABLE dbo.SampleCustomers_on_Primary  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON [PRIMARY] ;
GO

CREATE TABLE dbo.SampleOrders_on_Primary  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON [PRIMARY] ;
GO

INSERT dbo.SampleCustomers_on_Primary DEFAULT VALUES;
INSERT dbo.SampleOrders_on_Primary DEFAULT VALUES;
GO 20000


CREATE TABLE dbo.SampleCustomers_on_Files  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON MultiFileFG ;
GO

CREATE TABLE dbo.SampleOrders_on_Files  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON MultiFileFG ;
GO

INSERT dbo.SampleCustomers_on_Files DEFAULT VALUES;
INSERT dbo.SampleOrders_on_Files DEFAULT VALUES;
GO 20000


CREATE TABLE dbo.SampleCustomers_on_FileGroups  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON SingleFileFG1 ;
GO

CREATE TABLE dbo.SampleOrders_on_FileGroups  ( 
    id int identity( 1, 1 ) NOT NULL PRIMARY KEY CLUSTERED, 
    buncha char(500) default 'A', 
    big char(500) default 'B', 
    vals char(500)  default 'C'
) ON SingleFileFG2 ;
GO

INSERT dbo.SampleCustomers_on_FileGroups DEFAULT VALUES;
INSERT dbo.SampleOrders_on_FileGroups DEFAULT VALUES;
GO 20000

This is a really simple example showing several possible file layouts, and what those do with the physical storage of the data. Starting from the top, note that the database create statement makes three different file setups:

Our old friend Primary – one MDF file to rule them all!
A file group with three physical files: MultiFileFG. These files will be populated with SQL Server’s proportional fill.
Two file groups with one physical file apiece: SingleFileFG1 and SingleFileFG2. This allows the DBA to direct objects into specific files on disk.

Next I make some pairs of sample tables, and locate a pair in Primary, a pair in MultiFileFG, and a pair (one table each) in SingleFileFG1 and 2. The tables are each populated with 20,000 rows of sample data, arbitrarily called Customers and Orders.

It’s important to note that the tables are filled with data in an alternating fashion – one table gets a row, then the other table gets a row – and that means that the allocations for space for those objects will also alternate. One table grows, then the other, then the first again.

If the tables are in the same filegroup, even with separate physical files, the data will be interleaved. There will be a block of pages for one table allocated, then from the other table. Under default settings in SQL Server (not using the –E parameter, for example) the blocks of pages will each be a 64k extent of 8 data pages.

So, let’s have a look at what happens inside the files, and see if it supports this logic.

One File

My goodness it does! But you probably suspected that, as I’m the guy writing this post.

First let’s look at the blue area – the stack-of-bricks looking light blue and purple region is the Primary filegroup / MDF file. What we are seeing is the alternating, interleaved extents from the two tables, one in blue and one in lavender/purple. This isn’t terrible, but there are two things worth noting:

There’s a decent amount of fragmentation. Not harmful, necessarily, but these two tables definitely are not contiguous. If we have an application that wants to do a lot of scans, and we have spinning media, we might have some performance degradation. Read-ahead probably doesn’t work. This might not be ideal for a data warehouse.
If we re-index one of those objects the other one will remain full of “holes.” We could re-index both, but it might take a few passes before this interleaving works itself out.

I want to emphasize here that this isn’t necessarily a terrible situation, but it’s illuminating as far as how the data is physically written out.

Three Files in a Group

The next reasonable question to ask is whether making multiple files, alone, might solve this issue. So the next section of the visualizer, scrolling down a bit, shows the one filegroup/multiple file scenario from the demo. This is filegroup MultiFileFG, shown in alternating pink and purple:

Notice, we got three files, but the two tables we map into them are still interleaved, now across all three files. (Each file in the visualizer has a signature of orange-gray-orage system pages right at the beginning.)

This pattern is because of the proportional fill applied to the physical files inside one filegroup. An extent is allocated for an object from one file, then the next file has the most available space, so the next file used for the next extent, and so on. This means our tables are still cut up into 8-page extents spread across the physical media.

Two Groups with One File Each

The last section of the demo writes each table into a separate filegroup, where each filegroup is backed by a single physical file. The results here are represented by the solid pink area in the graphic:

In this case, the alternating inserts in the script don’t result in alternating allocations inside the files, because one table is specifically isolated to one filegroup and the other table to the other. In this case, the data in the files is practically contiguous.

Conclusion

Does this mean you should go out and make 1,000 file groups, one for every table or index? Please don’t! There’s a balance when planning files and filegroups between manageability, imagined performance impact of fragmentation (or real), complexity, and many other factors. SQL Server actually does a fine job, on smaller databases, working with one or a handful of files. But if you are planning a larger warehouse project, or you find the hardware you have struggles with the volume of data you need to manage, or you are looking at partitioning, then understanding how these storage internals behave, along with many other design considerations, will help.

Filegroups and Non-Clustered Indexes

rob_farley — Tue, 12 Mar 2013 00:20:11 GMT

Let’s start with some basics and then jump in a bit deeper, for this post to go with the 40th T-SQL Tuesday, hosted this month by Jen McCown.

SQL Server holds data, and that data is stored physically in files.

Of course, in the database world we think of the data as living in tables*, so naturally there must be some sort of mapping between the data in a table and the data in a file. This is where filegroups come in.

When SQL objects are created within a database, there is an option to be able to tell the system where to actually store it. But this isn’t a file, it’s a filegroup. (If it were a file and the disk volume that the file was on filled up, we’d have big problems. Instead, we put it in a filegroup, and can add extra files (on different volumes if so desired) to that filegroup.) Objects are stored within filegroups. Filegroups are groups of files, although many database systems do end up with only a single file per filegroup.

Filegroups end up providing a large amount of flexibility for the storage of data. Rarely accessed data can be put in filegroups that have files on cheaper (but probably slower) disk, while data that is heavily written can be put on disk that’s more appropriate for that, and so on. I’m sure you get the picture, and this is nothing overly thought-worthy.

You may even have already considered the idea around partitioning data across filegroups, moving data of a certain age (but potentially even from the same table) onto a different filegroup, so that queries that use different parts of tables can benefit from having some data on faster disk. Lots of potential. :)

Where it becomes interesting though, is when you start to consider the concept of non-clustered indexes.

You see, while a clustered index (or heap) provides the structure to store the data across all the columns of a table (although I’m simplifying it a little in regard to LOB data), we use copies of some of that data (in non-clustered indexes) to optimise access to the data.

So where are these copies stored? Many database people don’t even think about this kind of thing, in the same way that they often don’t think about including indexes in their database design. I’m going to figure you’re not in that boat though, because I probably know you, and you’re probably not one of those people.

Most people like to have a default filegroup that is NOT the primary filegroup. It means that when they create new objects, those new objects get put in the default filegroup, not the primary one. But it’s not actually that simple.

Let’s start by creating a database.

CREATE DATABASE fg_testing;
GO
USE fg_testing;
GO
SELECT *
FROM sys.filegroups;
--Only one filegroup at the moment

Notice the column data_space_id. This is the column which identifies each filegroup. We’ll use it later.

Let’s create a new filegroup and set it to be the default.

ALTER DATABASE fg_testing ADD FILEGROUP FG2;
GO
SELECT *
FROM sys.filegroups;
GO

Cool – data_space_id 2 is created.

ALTER DATABASE fg_testing MODIFY FILEGROUP FG2 DEFAULT;

I think this is a shame – but I guess it’s fair enough. We have to have a file in the filegroup before we can make it the default. That’s easy enough though, and probably what we’d be wanting to do before too long anyway.

ALTER DATABASE fg_testing ADD FILE ( NAME = N'file2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\fg_testing_file2.ndf') TO FILEGROUP FG2;
GO
ALTER DATABASE fg_testing MODIFY FILEGROUP FG2 DEFAULT;

(I’m doing this on my laptop, which only has one physical drive – on a server it’d go somewhere more sensible of course)

Now let’s add another filegroup. This is going to be for some data, but I don’t want it to be my default.

ALTER DATABASE fg_testing ADD FILEGROUP FG3;
GO
ALTER DATABASE fg_testing ADD FILE ( NAME = N'file3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\fg_testing_file3.ndf') TO FILEGROUP FG3;

Now, I want to create an object on FG3, which I’m going to pretend is my slower disk.

Once created, I’m looking in sys.objects to confirm which filegroup the table is in, but there’s no information there. Remember that a table is only metadata, and the things that matter are the indexes/heaps that are on it. Checking in sys.indexes shows me that indeed, it’s in data_space_id 3.

CREATE TABLE dbo.OrderDates (OrderDate date PRIMARY KEY, NumOrders int NOT NULL DEFAULT 0) ON FG3;
GO
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID('dbo.OrderDates');
GO
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.OrderDates');
GO

For completeness’ sake, I’m going to put some data in there, using a query that I blogged about yesterday.

INSERT dbo.OrderDates (OrderDate, NumOrders)
SELECT TOP (DATEDIFF(DAY,'20010101','20130312'))
DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1,'20010101') AS OrderDate,
ABS(CHECKSUM(NEWID())) % 100 as NumOrders
FROM master.dbo.spt_values t1 CROSS JOIN master.dbo.spt_values t2
ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT 1))
GO

But the whole point of this was to see what happens with the non-clustered index, which I want to be on recent data only (ie, filtered), and I want it to be in the DEFAULT filegroup.

As I want it in the default group, I won’t specify a filegroup for the index.

CREATE INDEX ixRecentData ON dbo.OrderDates (OrderDate)
INCLUDE (NumOrders)
WHERE OrderDate >= '20130101';
GO
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.OrderDates');

But look! It’s put the non-clustered index in the same filegroup as the clustered index. This isn’t what I wanted. In fact, it’s almost never what I’d want, because even if the disk performance is identical, it can be nice to have transactions which update both the clustered and non-clustered indexes using different disks.

Instead, we have to specify it explicitly to tell it to use the filegroup we want.

DROP INDEX ixRecentData ON dbo.OrderDates
GO
CREATE INDEX ixRecentData ON dbo.OrderDates (OrderDate)
INCLUDE (NumOrders)
WHERE OrderDate >= '20130101'
ON FG2;
GO
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.OrderDates');

It’s a shame to have to be so explicit with these things, but when you create non-clustered indexes, you really should think about which filegroup they’re going onto, because they may well not go onto the default one as you might expect.

@rob_farley

T-SQL Tuesday #39: Managing your SQL Server Services with PowerShell

AllenMWhite — Tue, 12 Feb 2013 18:09:00 GMT

This T-SQL Tuesday is about using PowerShell to do something with SQL Server. Now, if you've read any of my blog posts you probably know I've been using PowerShell to do things with SQL Server for a while now, but I'm glad Wayne decided on this topic for his T-SQL Tuesday topic, because everyone has different ways to use PowerShell, and you can learn from all of them, as I do.

(When I started to write this post I'd intended to share how I convert a PerfMon binary log file into SQL Server data for baseline analysis, but found I'd already done that here. Then, I thought I'd share how I save SQL Agent jobs and move them to another server, but did that one, too!)

One of the interesting aspects of SMO (Server Management Objects) is the Managed Computer object. It doesn't get a lot of attention because, well, that goes to the SQL Server instance and the various database objects. Administrators, though, need to pay attention to managing the instance itself. SQL Server 2008 introduced the Configuration Manager, a GUI application that allows administrators to view the SQL Server services installed, including their current state, the service account they use, etc. It also allows them to manage the external access to the instances via the network protocols supported, the TCP/IP ports, etc.

Here's a diagram of the Managed Computer object:

Now, if you're familiar with the Configuration Manager you should see some parallels there, and that makes sense, because these objects are the ones Configuration Manager is working with.

Let's say it's time for you to change the service account and password for your SQL Server instance. Using this model, we have our guide. We need to create a new ManagedComputer object, connect to the server, connect to the service, use the SetServiceAccount() method to set the new values, then restart the service. In this example I'll also restart the Agent service since I'm resetting the SQL Server instance service account.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
$sqlinst = $mc.Services['MSSQLSERVER']
$sqlagnt = $mc.Services['SQLSERVERAGENT']
$sqlinst.SetServiceAccount('TESTDOMAIN\AlternateAcct','L44HhRMeF25UDvQeJTj5UqyE')
$sqlinst.Alter()
$sqlinst.Stop()
start-sleep -s 10
$sqlinst.Start()
$sqlagnt.Start()

Let's say I just want to see the services on my local instance, like I do in Configuration Manager. Again, that's pretty easy.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer localhost
$mc.Services | select Name, ServiceState, DisplayName, ServiceAccount | format-table

There are times you'll need to work with the network protocols, and I've actually used the following code to change the IP port for an instance after an install, because it HAD to match a particular value.

$mc = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') localhost
$i=$mc.ServerInstances['MSSQLSERVER']
$p=$i.ServerProtocols['Tcp']
$ip=$p.IPAddresses['IPAll']
$ip.IPAddressProperties['TcpDynamicPorts'].Value = '1099'
$p.Alter()

Now, why is this important, if I can do all this in Configuration Manager? Well, SQL Server 2012 supports installation on Windows Server Core, and there's no GUI on a Core server, so this becomes the best way to access the objects you normally manage with Configuration Manager. More importantly, it allows you to build a set of scripts to "just handle" any number of problems, without trying to remember what submenu gives you access to what property to change to solve your problem.

Good luck, and thanks, Wayne, for hosting this month's T-SQL Tuesday!

Allen

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

Running goals

rob_farley — Tue, 08 Jan 2013 00:03:25 GMT

I’m not big on New Year resolutions. I can’t say I respond particularly well to “everyone’s doing something, I should too” situations. Peer pressure can be useful at times, but I also find that it can make me even more stubborn.

So when Jason Brimhall chose the topic for this month’s T-SQL Tuesday as “standing firm”, considering the ideas of “resolve, resolution, resolute”, I scowled a little, and wondered what I’d write about. I considered writing about my MCM journey (although that’s over – for a few months), or thinking about some business-related thing – but the closest I have to a resolution this year is related to running. I get that it’s not about SQL, but it seems very related to various members of the SQL community. You know who you are – don’t make me list you.

Many of you won’t know that this May (2013) will mark ten years since I had a nasty back injury. I don’t have a dramatic story to tell about it – it happened at work at the water cooler. I simply bent over wrong, and years of having a job where I sit down a lot, cycling (I used to cycle to work most days, every day when I was living in London), and being a little taller than most finally took its toll. Suddenly there was pain, and I ended up in hospital being told I might not get to walk again, and to expect to be in there until some time in July.

Prayer got me out in ten days.

But there were things I lost. I’ve never since been able to pick up my sons (they were five and three at the time). I’ve never been able to ride a bike (although I haven’t forgotten how). Until 2012, I’d been unable to run. I have chronic pain, and I’ve got used to it (which is annoying, but I’ve learned to tolerate it. That’s not always a good thing, like the time I had a badly inflamed appendix and nearly got sent home – although my pain tolerance did mean I could fly to Seattle a few days after the operation).

In 2011, I started to walk more. I walked about eight miles one time just to see if I could. The pain level was higher, as expected, but I managed it. Later that year, I walked a half marathon in Portland, Oregon, just before the PASS Summit. I even beat one of the people in the group who was supposedly running (I felt bad and wanted to wait for him, but I think if I’d stopped I wouldn’t’ve been able to finish).

I kept walking, but in June 2012, I talked to my physio about getting running back. He’d supported me through the walking, and knew that it wasn’t causing me too much bother any more. He got me on the treadmill, then onto grass, then onto the road. It hurt more, but I was surviving. Annoyingly, a cough I had developed into more and I ended up being sick for the best part of June-August, but I was running when I could, and I was surviving.

In November, I didn’t just walk a half marathon, I ran it. Annoyingly I had tendinitis in my right knee, which meant I had acute pain almost the whole way around the San Francisco course. I didn’t stop running though, and although my pace dropped so much I could’ve walked most of it faster, I got to the end. Last month, December 2012, I agreed to do at least 15 minutes of cardio every day, and I managed it. I ran over 75 miles during the month, and I’m sure my fitness level is improving.

So what’s in store for 2013 on the running scene?

Well, I want to get a better time in a half marathon. That shouldn’t be hard – I just need to run one when I’m not injured.

I wouldn’t mind trying a full marathon. I’ve joined the South Australian Road Runners’ Club, and there are some marathons I could aim for during the year. There are also some trips to the US that I’ll be doing, and I could try to do one over there.

I want to keep going with doing some cardio every day. My weight is dropping and I would like to get back to close what I was before my injury. I don’t expect to get under it – I’m not in my 20s any more – but I would like to get close. In the next few weeks I should get under 200lb again (90.7kg), and would like to be able to reach 86kg. The goal isn’t just to lose weight, rather to keep increasing the fitness level and see my back improve. Too many times in the past ten years I’ve had such bad times with my back that I’ve been unable to stand straight, or walk more than a few paces without stopping and holding onto something. On those days, I wouldn’t’ve been able to do any cardio at all, and I praise God massively for the quality of life I have when those times aren’t haunting me.

I don’t know if I can manage to run a particular distance in 2013. If I could average the same as December, that would get me to 900 miles for the year. That should mean that 1000 could be possible, but 750 miles could be a more realistic goal. I’d still be thrilled to be able to achieve that.

So if you follow me on Twitter and see one of these tweets, feel free to hit reply and send me a message. While I’m running, every tweet that starts with @rob_farley will get read out to me by the Runmeter app on my phone. That’s really encouraging, and it helps me know that I have supporters. Thanks in advance, because you’ll be helping me be more resolute.

@rob_farley

PS: If you’ve read all the way down to here, go and donate some money to the Leukaemia & Lymphoma Society, supporting Yanni Robel who will run 39.3 miles this weekend. I can’t see myself trying that particular feat, but two years ago, Yanni wasn’t a runner either. Both she and her husband John are good friends of mine who have encouraged me immensely. So go donate some money now and help her reach her fundraising goal too. http://pages.teamintraining.org/wa/wdw13/yrobel

Joins in single-table queries

rob_farley — Tue, 11 Dec 2012 00:05:17 GMT

Tables are only metadata. They don’t store data.

I’ve written something about this before, but I want to take a viewpoint of this idea around the topic of joins, especially since it’s the topic for T-SQL Tuesday this month. Hosted this time by Sebastian Meine (@sqlity), who has a whole series on joins this month. Good for him – it’s a great topic.

In that last post I discussed the fact that we write queries against tables, but that the engine turns it into a plan against indexes. My point wasn’t simply that a table is actually just a Clustered Index (or heap, which I consider just a special type of index), but that data access always happens against indexes – never tables – and we should be thinking about the indexes (specifically the non-clustered ones) when we write our queries.

I described the scenario of looking up phone numbers, and how it never really occurs to us that there is a master list of phone numbers, because we think in terms of the useful non-clustered indexes that the phone companies provide us, but anyway – that’s not the point of this post.

So a table is metadata. It stores information about the names of columns and their data types. Nullability, default values, constraints, triggers – these are all things that define the table, but the data isn’t stored in the table. The data that a table describes is stored in a heap or clustered index, but it goes further than this.

All the useful data is going to live in non-clustered indexes. Remember this. It’s important. Stop thinking about tables, and start thinking about indexes.

So let’s think about tables as indexes. This applies even in a world created by someone else, who doesn’t have the best indexes in mind for you.

I’m sure you don’t need me to explain Covering Index bit – the fact that if you don’t have sufficient columns “included” in your index, your query plan will either have to do a Lookup, or else it’ll give up using your index and use one that does have everything it needs (even if that means scanning it). If you haven’t seen that before, drop me a line and I’ll run through it with you. Or go and read a post I did a long while ago about the maths involved in that decision.

So – what I’m going to tell you is that a Lookup is a join.

When I run SELECT CustomerID FROM Sales.SalesOrderHeader WHERE SalesPersonID = 285; against the AdventureWorks2012 get the following plan:

I’m sure you can see the join. Don’t look in the query, it’s not there. But you should be able to see the join in the plan. It’s an Inner Join, implemented by a Nested Loop. It’s pulling data in from the Index Seek, and joining that to the results of a Key Lookup.

It clearly is – the QO wouldn’t call it that if it wasn’t really one. It behaves exactly like any other Nested Loop (Inner Join) operator, pulling rows from one side and putting a request in from the other. You wouldn’t have a problem accepting it as a join if the query were slightly different, such as

SELECT sod.OrderQty
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail as sod
on sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesPersonID = 285;

Amazingly similar, of course. This one is an explicit join, the first example was just as much a join, even thought you didn’t actually ask for one.

You need to consider this when you’re thinking about your queries.

But it gets more interesting.

Consider this query:

SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 276
AND CustomerID = 29522;

It doesn’t look like there’s a join here either, but look at the plan.

That’s not some Lookup in action – that’s a proper Merge Join. The Query Optimizer has worked out that it can get the data it needs by looking in two separate indexes and then doing a Merge Join on the data that it gets. Both indexes used are ordered by the column that’s indexed (one on SalesPersonID, one on CustomerID), and then by the CIX key SalesOrderID. Just like when you seek in the phone book to Farley, the Farleys you have are ordered by FirstName, these seek operations return the data ordered by the next field. This order is SalesOrderID, even though you didn’t explicitly put that column in the index definition. The result is two datasets that are ordered by SalesOrderID, making them very mergeable.

Another example is the simple query

SELECT CustomerID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 276;

This one prefers a Hash Match to a standard lookup even! This isn’t just ordinary index intersection, this is something else again! Just like before, we could imagine it better with two whole tables, but we shouldn’t try to distinguish between joining two tables and joining two indexes.

The Query Optimizer can see (using basic maths) that it’s worth doing these particular operations using these two less-than-ideal indexes (because of course, the best indexese would be on both columns – a composite such as (SalesPersonID, CustomerID – and it would have the SalesOrderID column as part of it as the CIX key still).

You need to think like this too.

Not in terms of excusing single-column indexes like the ones in AdventureWorks2012, but in terms of having a picture about how you’d like your queries to run. If you start to think about what data you need, where it’s coming from, and how it’s going to be used, then you will almost certainly write better queries.

…and yes, this would include when you’re dealing with regular joins across multiples, not just against joins within single table queries.

T-SQL Tuesday : Reflections on the PASS Summit and our community

AaronBertrand — Tue, 13 Nov 2012 15:20:00 GMT

Last week I attended the PASS Summit in Seattle. I blogged from both keynotes (Keynote #1 and Keynote #2), as well as the WIT Luncheon - which SQL Sentry sponsored.

I had a fantastic time at the conference, even though these days I attend far fewer sessions that I used to. As a company, we were overwhelmed by the positive energy in the Expo Hall.

I really liked the notebook idea, where board members were assigned notebooks to carry around and take ideas from attendees. I took full advantage when Douglas McDowell stopped by our booth. He had a good explanation for some of my questions/gripes, but definitely walked away with some work items. I think the whole "we are listening" message went over quite well, and PASS continues to demonstrate its commitment to improving the community.

Highlights of the conference, for me, were chatting with Steve Dybing (an MVP lead from the past), attending a Paul White session, and talking briefly with both Conor Cunningham (off-site) and Dr. David DeWitt (on stage).

I had many other great conversations with friends and customers, new and old, far too many to mention. Which brings me to the point of the post: community.

I think we have a fantastic SQL Server community. Is it perfect? No. Do we all want to hang out together all the time? No. Like any family, there are the weird uncles that you just don't see eye-to-eye with on a few or even a lot of issues. And that's okay. Families are supposed to have disagreements, fallouts and other rough times. They make us stronger. And in a roundabout way, remind us that we're all here for each other.

This stage of my career was launched on the backs of the SQL Server community, and I am very thankful for that. And I get the feeling, more and more every year, that folks attend the PASS Summit both for the technical content and, to an even greater degree, the people that are there.