Voice of the DBA

Category: Blog

T-SQL Tuesday #43–Hello, Operator?

It’s T-SQL Tuesday time, and this one is a bit of a challenge for me. Rob Farley is hosting, and asking for people to write on Plan Operators, those various elements that go into the execution plans in T-SQL. I don’t dive too deeply into these items, unlike some others, so I’m going to tackle this at a high level with something I learned a few years back, but a few years after I started working with execution plans.

T-SQL Tuesday is the monthly blog party from Adam Machanic. It’s the second Tuesday of each month and if you want to participate, watch for the announcement on Twitter, or Google search it and then publish a post on the specified day, GMT time. If you want to host, you need a blog, need to participate, and then contact Adam.

Parallel Operators

Many of us know that execution plans can be serial or parallel. Which plan we want depends on the situation, but you can determine which plan is being executed from the plan. However I didn’t know the depth to which I could determine the parallel plans from the images.

When I first looked at the Graphical Execution Plan Operator Icon page, I saw a lot of operators, many of which I’ve seen in the past. There’s a section for parallel operators, and if you look at it, you only see three items: the Distribute Streams, Repartition Streams, and Gather Streams icons. I’ve shown that section below.

These operators work to combine to separate out the data into separate parallel operators. If you see these items in your plan, you know there are some parallel operations.

However, do you know which operations are parallel?

If you look at the nonclustered index seek operator, it looks like this:

The description doesn’t say anything about this being serial or parallel. This icon is serial.

A parallel icon looks like the one shown in the image below (from Paul White’s Forcing a Parallel Query Execution Plan). It’s the lower right icon in the image.

Note that the icon has two arrows overlaid on it. In fact, this is a parallel plan that has a parallel clustered index scan, a parallel nonclustered index scan, two repartition streams (parallelism operators), a parallel merge join (top row, second from the left) and a gather streams (parallelism operator). Lots of parallel work being done.

If you look at an execution plan, you can tell which items are parallel with those two arrows overlaid on the icon.

June 11, 2013
CHOOSE in SQL Server 2012
The CHOOSE command is new in the T-SQL as of SQL Server 2012. I hadn’t ever had the chance to work with it, but after seeing someone submit a piece recently, I decided to play with it a bit.

This feature is essentially an indexing value from an array. Here’s a short example.
```
SELECT CHOOSE(1, 'First', 'Second', 'Three');
```
The first parameter to the CHOOSE() function is an integer, which is the position. The second (and subsequent) parameters are the array of values. Here’s a better example. Let’s say I have a lookup table of titles:
```
CREATE TABLE titles
( titleid INT
, title VARCHAR(20))
;
INSERT titles 
 VALUES (1, 'Manager')
      , (2, 'Developer')
      , (3, 'DBA')
      , (4, 'Sysadmin')
      , (5, 'Storage Admin')
      , (6, 'Help Desk')
;
```
I might potentially have a very large table. Imagine that I work at SalesForce.com or I have some type of EAV table here. If I am performing a join with employees, I could easily do this:
```
SELECT 
   e.firstname
 , t.title
 FROM dbo.employees e
  INNER JOIN dbo.titles t
    ON    e.titleid = t.titleid
```
However, suppose I have performance issues, or I’m joining to lots of tables. Perhaps I’d prefer not to actually join to another table for some reason. I could do this instead:
```
SELECT 
   e.firstname
 , CHOOSE( e.titleid, 'Manager', 'Developer', 'DBA', 'Sysadmin', 'Storage Admin', 'Help Desk')
 FROM dbo.employees e
```
Is this useful?

I’m not sure. Most of the examples and places I can think of for using this are rather trivial, or inflexible. This seems like hard coding values into a procedure or function when a table join might be a better option.

I haven’t found any blogs that present practical uses that make sense, but I’m sure some are out there. Let me know if you know of any.

UPDATE: Rob Farley mentioned he uses this in the date dimension of data warehouses. I’m not sure if that’s the best solution, but Rob’s a smart guy, so I’ll take his word that this is a good use of the function.
June 4, 2013
PC Sales Declining

About 3 or 4 years ago I was talking with my wife and a colleague of hers that worked in the mobile industry. This person and my wife thought that the mobile industry would explode, and it’s impact and expansion would dwarf the PC revolution that had most of our companies buying new machines every two to three years.

I think that is part of what is happening with this article on the hemorrhaging of the PC industry. I see quite a few people moving to the mobile space, and using smart phones, texting, and more to handle the needs of their lives. The growth of tablets as well is changing the world. I see more and more non-technical adults carrying tablets to do their work. Kendall’s volleyball coach, our Scoutmaster, and more are using tablets, and ( I am guessing here), sticking with an older computer at home when they need it.

There’s also the factor that we can get so much done on any device that we don’t need that many. These days email, video, audio, browsing, and camera work can happen on almost any device.

The use of older hardware is another issue. We don’t see the fantastic pace of growth in hardware occurring anymore. I know machines get more powerful, but it doesn’t seem to be happening as quick as in the past. Part of this may be psychological with the clock speed ratings not changing as quickly, but it’s also that the technology seems to be focusing on power improvements more than anywhere else. Even the core growth has tailed off. I just bought a laptop, 4 years after my previous Windows laptop. They both had quad core chips, and while the new one seems to be faster, it’s not substantially faster.

Hardware not speeding up, tablets and phones, but there’s something else as well. Microsoft did a fantastic job with Windows 7. In many cases people are finding that the OS is faster and slimmer, without the bloat of previous versions. One can actually put Windows 7 on older hardware and it will run great. I know lots of people are still running Windows XP, which works fine, but even if they upgrade, they don’t need to move to new hardware o do so. I suspect that other OSes are in similar situations. The software is so good, there’s no reason to upgrade.

All of this isn’t good or bad. It just means a changing industry, and PC makers should both learn to adapt to new form factors (tablets), but also expect that we will buy fewer devices in the future.

May 31, 2013
Exploring a Database in Azure

After creating my Azure account, I wasn’t sure where to go next. Fortunately I had an immediate project that occupied my attention: a public Adventureworks database.

As we noted, Red Gate and SQLServerCentral agreed to host the database and cover costs. Once Jamie Thomson transferred the database to my subscription, which didn’t seem too hard, although slightly confusing, it showed up in my list of objects.

I drilled down to the sql databases to ensure this was the only object there, and it was.

I wasn’t sure what I could do, so I clicked on the database. I do tend to be an RTFM guy at the beginning, but I was curious how easy they’ve made things. This is what I saw:

It’s a good list of things, although since I have SSDT installed, I didn’t need the first item. I guess if you have SSDT installed you would know it, and the link is handy. The interesting thing for me was the “Server” string at the bottom of the panel.

I wonder if it works.

Jamie had given me the admin name and password, and I plugged this into Management Studio, along with the connection string.

Sure enough, it connected, and changing to the AdventureWorks2012 database, I could query the objects.

I decided to test Object Explorer as well, and that was interesting. I had a very cut down version of what I normally see for a SQL Server instance.

Most of the “instance” level stuff was gone. I could see the master database, and my own database, but no others.

Since this was a new instance, one of the first things I decided to do was create my own login account. I right clicked “Logins” and selected “New Login”. I go this:

A template for a script. That’s interesting. No GUI version of the login creation dialog. I used CTRL+Shift+M to fill in the template stuff.

That’s not my password, but I did choose a nice, long one. I executed the script to create a login and then went to add a user. Once again, I got a script from the GUI.

I changed this and created my user to map to my login.

This let me log into the system, and set myself as a db_owner. However I wasn’t an administrator. That’s for another post.

May 30, 2013