Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #43–Hello, Operator?

    tsqltuesdayIt’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.

    parallel

    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:

    nci

    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.

    parallel2

    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.

  • T-SQL Tuesday #42–The Long and Winding Road

    tsqltuesdayIt’s T-SQL Tuesday time, this month hosted by Wendy Pastrick who asks you to talk about the Long and Winding Road. Wendy asks us to talk about your job, your road map to the future, and what might be in store for you.

    T-SQL Tuesday is a monthly blog party on the second Tuesday of each month, hosted by a different person. You can participate by writing a blog post on the topic for the month and following a few rules that you’ll find in Wendy’s invitation.

    If you want to host yourself one month, contact Adam Machanic (@AdamMachanic).

    The Long and Winding Road

    I’ve been in the same job for about 11 years: the editor of SQLServerCentral as my primary role. About 6 years ago, Red Gate Software bought the site, and I now work for them. The job is mostly the same, though a couple things have changed over the years. Three years ago I made an effort to begin speaking more, both on technical topics as well as Red Gate software. I’ve had a busy speaking schedule across that time, and I’m scheduled for about 15 events in 2013.

    That change in speaking for me, along with the SQL in the City events that Red Gate sponsors keeps me busy with building presentations and delivering them all over the world.

    These events are designed to provide attendees with free training just like other conferences with one exception: we can talk about Red Gate tools. The sessions I do aren’t marketing talks about the tools, but rather presentations about things you should do in your environment, but I do show how the tools Red Gate provides makes these tasks easier. That’s the idea behind our tools: they make you more productive.

    This year that has meant I’m moving a little bit away from the administration areas where I spent most of my career and back into some development work. I’ve been setting up Git and working with source control, learning about Continuous Integration, and more. Mostly this is to support the work Red Gate is doing on Deployment Manager, which I think is a great idea. It solves a lot of problems and hassles that I’ve dealt with when developing software. I’m excited, and looking forward to seeing how people like the tool, as well as watching it evolve based on feedback.

    I have no plans to leave my job or Red Gate in the foreseeable future. The position I’m in is unique, and I don’t expect to move up, or around, in the company because of that, but that’s OK.

    I have the best job in the world.

  • T-SQL Tuesday #41 – I Love Presenting

    tsqltuesday.jpgThis edition of the T-SQL Tuesday blog party isn’t a technical one. Hosted by Bob Pusateri, this one is timely for me. Bob invites us all to write about how we came to love presentating. That’s good timing as I’m actually giving two sessions at the SQL Intersection conference today.

    T-SQL Tuesday is the monthly blog party, on the second Tuesday of the month. If you’d like to host, contact Adam Machanic(@AdamMachanic).

    I’m Scared

    That’s actually how I felt the first time I had to stand up in front of the class in school and give some sort of report. I can’t tell you when the first time I did it, but I do know that all through high school, and even college, I was scared to be in front of people, palms sweaty, bowels rumbling, and cheeks flushed. I couldn’t imagine voluntarily choosing to stand in front of people and talk about anything. Even talking to a group of 10 people at a party in college made me nervous.

    I Need Money

    College is expensive. When I went through college it was a fraction of the cost today, but it was still expensive. To help out my family, I worked almost full-time to cover my living expenses. The most efficient way to do that was to work in the restaurant business. I learned to cook, clean, and wait on tables. I never had to really speak to a group larger than 8 people, but I was constantly meeting new people and talking to them every night.

    One summer, while home working at a local hotel, our bartender called in sick. I volunteered to jump in, did a good job, and spent the rest of my college career mixing drinks. Around this time the movie Cocktail was released, and many of my fellow workers learned to toss and spin bottles, myself included. It was a little like being on stage in front of groups of people, and over the next few years, much of my shyness in front of strangers disappeared.

    Watch and Learn

    I worked hard early in my technology career to learn as much as possible. I read books, magazines, trade articles, and more, trying to learn as much as I could about the various pieces of technology in my companies. I also attended lots of talks at user groups and MSDN local events. I watched many speakers talk about subjects that I had read about, and worked with myself. I learned lots of things, but more and more I realized that I knew much of what speakers were presenting. I started to think that I could present as well as others. I gained confidence from watching others validate my knowledge.

    Eventually I started SQLServerCentral and there was a year when the founders wanted to do some joint presentations. Brian Knight and I had a debate about identities v GUIDs. It was my first time speaking at the PASS Summit and it wasn’t so bad. We had a lively debate, some input form Microsoft in the audience, and I realized presenting wasn’t so bad. I didn’t jump right in, but a couple years later I was asked to do a short presentation at a local user group and accepted. Fortunately for me this was a small group, and I ended up presenting for 15 minutes while seated a a conference table. Much like being in a corporate meeting, I felt the same mix of nervousness and confidence, but sitting down helped me.

    Over the years I’ve had some good and bad presentations, but I’ve tried to learn from what has worked well and from what other presenters have done. I have written about presenting and tried to help others. These days I’m fairly comfortable talking in front of groups. I know I’ll make mistakes, and that someone in the audience might know more than I do. I know I’ll stumble over sentences. However I move on and don’t worry about those issues. I try to learn from them, but I don’t view them as failures.

    Today I love presenting about various topics. I speak on SQL Server and professional development topics 10-15 times a year. I usually end up giving 25-35 talks, often on 8-10 topics a year. It’s fun, and I really enjoy the chance to teach people something or help them improve their careers.

  • T-SQL Tuesday #40– File and Filegroups

    tsqltuesdayIt’s the second Tuesday of the month and time for T-SQL Tuesday again. This is a monthly blog party, where the participants write on a particular theme. This month Jen McCown, of Midnight DBA fame, invites us to talk about files and filegroups in SQL Server.

    If you’re like to participate, write a post and drop a comment (or pingback) on Jen’s blog. Watch the #tsql2sday hashtag on twitter for next month’s invitation.

    Filestream and Filegroups

    I have a couple talks that deal with Filestream related topics, so I decided on a quick introductory lesson on how this works.

    Filestream was built into the AdventureWorks 2008 sample database. Requiring administrators to turn on Filestream caused some confusion and complaints, despite the fact that it’s easy to do.

    What does Filestream have to do with filegroups? In a database that is enabled for Filestream data, you need to add a filegroup specifically for the Filestream data. This is actually a folder on your file system, which you can access through T-SQL, or through the Win32 API. If you are using SQL Server 2012 or later, you can also access this data with a Filetable, which is built on Filestream.

    Let’s create a database, and add a filegroup for Filestream. We start with the “New Database” dialog in SSMS.

    fs_a

    With the normal defaults, we see a data file (FS_Test) and a log file (FS_test_log). For Filestream data, we need a new place to store it. Let’s add a file:

     

    fs_b

    Once I add the file, I mark it as storing Filestream data. The other options are rows (data files) or log files. However this presents a problem. When I scroll right, I see that there is no filegroup for Filestream data. I can’t put this in an existing filegroup.

    fs_c

    Let’s add one of those. Here’s the default filegroup dialog.

    fs_d

    I can click add, and put in a filestream filegroup. The name doesn’t matter, it’s just for administrative purposes. Once I do that, I can go back to the files dialog, and if I select the dropdown, my new filegroup appears.

    fs_e

    Now I need a location. Outside of SQL Server, I created a folder in my data directory. This can be anywhere, but I did it in the default location. It’s called FilestreamDataTest.

    fs_g

    I then select this in the files dialog, using the ellipsis to the right of the Path column.

    fs_f

    I see my folder in the file picker and choose it.

    fs_h

    Once I’ve selected it, I don’t click OK. I click “Script” and get the script below:

    CREATE DATABASE [FS_test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'FS_test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [fs_test_fsdata] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'fs_test_fsdata', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FilestreamDataTest\fs_test_fsdata' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'FS_test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FS_test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

    I can run this, and once I do, if I go into the folder that contains my Filestream file, I see this:

    fs_i

    As I create tables that hold Filestream data, including FileTable data, I’ll see entries in here for each column (or Filetable) that holds this data. There is a folder that holds logging information for this data, which I do not manage.

    Hopefully this is a quick, short piece that helps you understand Filegroups and Filestream.