Wayne Sheffield

My blog about SQL Server

Browsing Posts published by Wayne Sheffield

TSQL2sDay150x150A little over 5 years ago, Adam Machanic (B|T) had this great idea of getting everyone in the SQL Community to all blog about a specific topic on the second Tuesday of the month. This monthly “blogging party” has a host, aka party organizer, that decides the theme. It has evolved: it goes by the name “T-SQL Tuesday”, it has its own Twitter hashtag (#TSQL2sDay), and monthly we are seeing more and more people joining in. For some people, this has become what draws them out of their shell to write something on their blog at all. This month starts the 6th year, and I want to thank Adam for giving this to our community.

This month, I’m the party organizer, and the theme that I had selected was Giving Back. Specifically, I asked:

In “The Spirit of Giving”, I want to know how you plan on Giving Back to the SQL Community during the coming year. Are you going to start speaking at your local user group? Speak at your local SQL Saturday? Perhaps step up and help run your local user group? Do you want to start becoming an active blogger – or increase your blogging? Do you plan on volunteering your time with larger organizations (such as PASS), so that SQL Training can occur at a larger level?

In order to decide how you want to improve, you need to take stock of what you’re currently doing. During 2014, I have (or will have) spoken at 12 SQL Saturdays across the United States (mostly in the East), where I’ve presented 16 sessions and I’ve co-presented 6 workshops (aka precons). Additionally, I’ve presented to three SQL User groups. Locally, I arrange the speakers for my local user group in Richmond, VA and I was a co-organizer for our SQL Saturday in March. For the PASS Summit, I served on the PowerPoint review committee. As far as writing goes, I’ve only blogged 16 posts on my blog, with 5 more on my work blog and 2 articles published at SQLServerCentral. And I just haven’t had the time to help out with online forums.

I’m happy with an average of 1 SQL Saturday per month. However, I really enjoy presenting, and I want to do more. What is missing here is presenting outside of PASS. Furthermore, I’d like to present internationally, though this does need to be at an English speaking location. I have plans for participating in SQL Summer Camp in June in Nova Scotia. And I’d like to present again at the PASS Summit.

Another area that is missing is giving back to user groups – I was shocked to see that I only presented at 3 during this year. I want to do better in this area during the upcoming year.

So, how I plan to Give Back to the SQL community during the coming year are:

Present to at least 1 SQL Saturday per month (average).
Post at least 1 blog post per month (outside of T-SQL Tuesday blog posts or my work blog).
Present at one non-PASS conference.
Submit to present to international conferences (SQL Bits, SQL Rally, etc.).
Present to at least 6 user groups. (Hey user group leaders – contact me!)
Submit four articles for submission at SQLServerCentral.
Participate more in online forums for SQL help.
I hope to be selected to be a volunteer with the PASS Summit again in some capacity.
And I’ll be the heading up our SQL Saturday event on March 21, 2015 (though we all know that this is a team operation).

And now I’ll ask everyone reading this to help hold me to these goals.

The Season of Giving

The annual PASS Summit (otherwise known as the #SQLFamily reunion) is over. Here in the United States, we have just finished celebrating Thanksgiving, where the average person consumes more calories in two hours than what they need for a week. And we are entering the season (that has now been active by merchants for months) where worldwide we try to out give (or, in merchant-speak, out-spend) everyone that we know – or at least to those that we are the closest to.

There are other ways of giving also. At this time of year, we find that food banks across the nation are receiving their largest food donations from the community. Underprivileged children that would otherwise not receive any gifts are receiving some, due to the community giving to organizations such as Toys-For-Tots (http://www.toysfortots.org/default.aspx). Other organizations, such as the Salvation Army (http://www.salvationarmy.org), are seen outside many stores raising money to fund all of the giving activities that they do worldwide throughout the year. Outside of this time of year, we see over and over that whenever there is a disaster that the worldwide community responds by giving donations and resources to help out. Of course the community gives all throughout the year in many other ways.

As the year wraps up, we find ourselves reminiscing about the past year, and in making resolutions for personal change for the coming year.

But hey, this is a SQL Blog. What does all of this have to do with SQL?

Well, I’m glad that you asked. You see, this month I’m the host for the monthly T-SQL blogging party known as T-SQL Tuesday. This party, which was started by Adam Machanic (blog|twitter) and is now starting its 6th year, is hosted by a different person each month. The host selects the theme, and then the blogging begins. Worldwide, on the second Tuesday of the month (all day, based on GMT time), bloggers attend this party by blogging about the theme. So, what theme am I selecting that I want you to blog about?

Giving Back

In “The Spirit of Giving”, I want to know how you plan on Giving Back to the SQL Community during the coming year. Are you going to start speaking at your local user group? Speak at your local SQL Saturday? Perhaps step up and help run your local user group? Do you want to start becoming an active blogger – or increase your blogging? Do you plan on volunteering your time with larger organizations (such as PASS), so that SQL Training can occur at a larger level? However you plan on giving back to the SQL Community during the upcoming year, and whether it is something new that you’ll be doing or continuing what you are doing, I’m looking forward to reading about it in your blog post.

How to participate

TSQL TuesdayAhh, your blog post. Now is a good time to discuss how you can attend this party. First off, your blog post needs to go live between 00:00 GMT Tuesday, December 9th, 2014 and 00:00 GMT Wednesday, December 10th, 2014. Secondly, your blog post needs to contain the T-SQL Tuesday logo image on this post, and it should link back to this post (trackbacks should work, but it’s a good idea to post a comment to this post with a link to your post). And optionally, tweet about your post using the #TSQL2sday hashtag.

Okay, you have a week to think about this, and to write your blog post. After the party, I’ll post a summary of what everyone has posted.

SQLSat349You know, these 1 hour sessions that are at most SQL Saturdays are just too short sometimes – you just get going and bang! The times up. There’s only so much material that can be presented in just one hour. Recognizing this, last year my friend Jason Brimhall and I teamed up and created an all-day workshop for SQL training. We will be chugging into Salt Lake City, UT on the SQL Saturday crazy train to deliver this workshop, titled “Murder They Wrote”, at SQL Saturday #349 on Friday, October 24th.

The Target Audience and what we cover

If you are a DBA, a database developer, or an application developer that connects to a SQL Server back-end database, then this session is for you. If you are experiencing performance issues, then this session is most definitely for you. If you’re looking for a fun way to spend a day and to get some #sqllearning, then this session is for you. This presentation is geared around decisions that have been made in the development life cycle and the effects that those decisions have on SQL Server, based upon the things that we have seen done to the instances that we have managed (and, in my case, some of the things that I did back when I was a developer and didn’t know better). With no topic being off limits, we also cover critical issues that just aren’t that great to do – and that still happen way too often. And demos? Oh yeah! You know that I like to show things instead of just telling them, so in that spirit, this is a demo-heavy presentation. We don’t just tell you that something is bad and something else is good… we’ll show it to you. We might even blow something up!

So, come on out for a day of learning, fun, and SQL camaraderie. We look forward to spending a day chatting with the attendees, and in helping them to overcome some of their issues.

The Official Session Abstract

Join Microsoft Certified Masters Wayne Sheffield and Jason Brimhall as they examine numerous crazy implementations they have seen over the years, and how these implementations can be murder on SQL Server. No topic is off limits as they cover the effects of these crazy implementations from performance to security, and how the “Default Blame Acceptors” (DBAs) can use alternatives to keep the developers, DBAs, bosses and even the end-users happy.

Presented by:

WayneSheffieldWayne Sheffield, a Microsoft Certified Master in SQL Server, started working with xBase databases in the late 80′s. With over 20 years in IT, he has worked with SQL Server (since 6.5 in the late 90′s) in various dev/admin roles, with an emphasis in performance tuning. He is the author of several articles at www.sqlservercentral.com, a co-author of SQL Server 2012 T-SQL Recipes, and enjoys sharing his knowledge by presenting at SQL PASS events and blogging at http://blog.waynesheffield.com/wayne.

 

 

 

JasonBrimhallJason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2012. He has experience in performance tuning, high transaction environments, as well as large environments. Jason also has 18 years experience in IT working with the hardware, OS, network and even the plunger (ask him sometime about that). He is currently a Consultant and a Microsoft Certified Master(MCM). Jason is the VP of the Las Vegas User Group (SSSOLV).

 

 

Course Objectives

  1. Recognize practices that are performance pitfalls
  2. Learn how to Remedy the performance pitfalls
  3. Recognize practices that are security pitfalls
  4. Learn how to Remedy the security pitfalls
  5. Demos, demos and even more demos – (and the scripts to demonstrate both the pitfalls and their remedies will be provided to attendees!)
  6. Have fun and discuss
  7. We might blow up a database

We are trying to have a nice mix of real world examples (and some contrived ones), all of which demonstrate a good and useful point.

Registration – or what to do to join us

If you will be in the area, and you are looking for high quality content with a good mix of enjoyment (and some self-ridiculing), come and join us! You can register for this session at Eventbrite. There is a limit of 75 seats for this murder mystery theater, so sign up now to ensure that you have a spot saved for you. The cost is a mere $150 (plus EventBrite fees).

And then, on Friday October 24th, join us at the SSG Training Facility, located at 2975 West Executive Parkway, Lehi, UT 84043. The train gets underway at 9am.

But wait, there’s more!!!

Come on back Saturday for the main event – Salt Lake City’s SQL Saturday is being held at the Spencer Fox Eccles Business Building, 1655 Campus Center Dr, Salt Lake City, UT 84112. There are 10 tracks going on, so there’s bound to be something there that you just have to see all day long! The complete session schedule is here, and Jason and I are each presenting during the first session of the day, so come on out and join one of us!

Related posts:

The Murder Train chugs into Denver

The Murder Train chugs into Raleigh

SQL Saturday RVA recap

History of the SQL crazy train

Shameless plug:

Jason and I both present regularly at SQL Saturdays. If you are organizing an event and would like to have us deliver our workshop, please contact Jason, myself, or both of us.

SQLSat331You know, these 1 hour sessions that are at most SQL Saturdays are just too short sometimes – you just get going and bang! The times up. There’s only so much material that can be presented in just one hour. Recognizing this, last year my friend Jason Brimhall and I teamed up and created an all-day workshop for SQL training. We will be chugging into Denver, CO on the SQL Saturday crazy train to deliver this workshop, titled “Murder They Wrote”, at SQL Saturday #331 on Friday, September 19.

The Target Audience and what we cover

If you are a DBA, a database developer, or an application developer that connects to a SQL Server back-end database, then this session is for you. If you are experiencing performance issues, then this session is most definitely for you. If you’re looking for a fun way to spend a day and to get some #sqllearning, then this session is for you. This presentation is geared around decisions that have been made in the development life cycle and the effects that those decisions have on SQL Server, based upon the things that we have seen done to the instances that we have managed (and, in my case, some of the things that I did back when I was a developer and didn’t know better). With no topic being off limits, we also cover critical issues that just aren’t that great to do – and that still happen way too often. And demos? Oh yeah! You know that I like to show things instead of just telling them, so in that spirit, this is a demo-heavy presentation. We don’t just tell you that something is bad and something else is good… we’ll show it to you. We might even blow something up!

So, come on out for a day of learning, fun, and SQL camaraderie. We look forward to spending a day chatting with the attendees, and in helping them to overcome some of their issues.

The Official Session Abstract

Join Microsoft Certified Masters Wayne Sheffield and Jason Brimhall as they examine numerous crazy implementations they have seen over the years, and how these implementations can be murder on SQL Server.  No topic is off limits as they cover the effects of these crazy implementations from performance to security, and how the “Default Blame Acceptors” (DBAs) can use alternatives to keep the developers, DBAs, bosses and even the end-users happy.

Presented by:

WayneSheffieldWayne Sheffield, a Microsoft Certified Master in SQL Server, started working with xBase databases in the late 80′s. With over 20 years in IT, he has worked with SQL Server (since 6.5 in the late 90′s) in various dev/admin roles, with an emphasis in performance tuning. He is the author of several articles at www.sqlservercentral.com, a co-author of SQL Server 2012 T-SQL Recipes, and enjoys sharing his knowledge by presenting at SQL PASS events and blogging at http://blog.waynesheffield.com/wayne.

 

 

 

JasonBrimhallJason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2012. He has experience in performance tuning, high transaction environments, as well as large environments.  Jason also has 18 years experience in IT working with the hardware, OS, network and even the plunger (ask him sometime about that). He is currently a Consultant and a Microsoft Certified Master(MCM). Jason is the VP of the Las Vegas User Group (SSSOLV).

 

 

Course Objectives

  1. Recognize practices that are performance pitfalls
  2. Learn how to Remedy the performance pitfalls
  3. Recognize practices that are security pitfalls
  4. Learn how to Remedy the security pitfalls
  5. Demos, demos and even more demos – (and the scripts to demonstrate both the pitfalls and their remedies will be provided to attendees!)
  6. Have fun and discuss
  7. We might blow up a database

We are trying to have a nice mix of real world examples (and some contrived ones), all of which demonstrate a good and useful point.

Registration – or what to do to join us

If you will be in the area, and you are looking for high quality content with a good mix of enjoyment (and some self-ridiculing), come and join us! You can register for this session at Eventbrite.  There is a limit of 30 seats for this murder mystery theater, so sign up now to ensure that you have a spot saved for you. The cost is a mere $125.

And then, on Friday September 19th, join us at the Cherry Creek Presbyterian Church – Community Life Center, located at 10150 E. Belleview Ave., Englewood, CO 80111. The train gets underway at 9am.

But wait, there’s more!!!

Come on back Saturday for the main event – Denver’s SQL Saturday is being held at University of Denver – Driscoll Student Center, 2055 East Evans Avenue, Denver, CO 80208. There are 8 tracks going on, so there’s bound to be something there that you just have to see all day long! The complete session schedule is here, and Jason and I are each presenting during the first session of the day, so come on out and join one of us!

Related posts:

The Murder Train chugs into Raleigh

SQL Saturday RVA recap

History of the SQL crazy train

Shameless plug:

Jason and I both present regularly at SQL Saturdays. If you are organizing an event and would like to have us deliver our workshop, please contact Jason, myself, or both of us.

TSQL TuesdayWell, here it is again. The second Tuesday of the month, which means that it’s T-SQL Tuesday. T-SQL Tuesday… that wonderful monthly blogging party started by Adam Machanic (Blog | @AdamMachanic). The intent is to get the entire SQL Community together on a monthly basis to blog about a common theme – to get each person’s perspective on the chosen subject. This month, it is being hosted by Jeffrey Verheul (Blog | @DevJef), and the topic he has chosen is SQL Family and Community. Jeffrey lays out the topic like this:

This month I would like to give everyone the opportunity to write about SQL Family. The first time I heard of SQL Family, was on Twitter where someone mentioned this. At first I didn’t know what to think about this. I wasn’t really active in the community, and I thought it was a little weird. They were just people you meet on the internet, and might meet in person at a conference some day. But I couldn’t be more wrong about that!Once you start visiting events, forums, or any other involvement with the community, you’ll see I was totally wrong. I want to hear those stories. How do you feel about SQL Family? Did they help you, or did you help someone in the SQL Family? I would love to hear the stories of support, how it helped you grow and evolve, or how you would explain SQL Family to your friends and family (which I find hard). Just write about whatever topic you want, as long as it’s related to SQL Family or community.

I think that this is a wonderful topic. Thank you Jeffrey for selecting this. The #SQLFamily, and the entire SQL community is so fantastic, and this is a wonderful opportunity to shine the light on it. So, what is #SQLFamily? How would you explain it to others? Well, let’s start off with what is family? It’s simply all the people that you are related to. So, #SQLFamily is all the people that you know through SQL Server.

However, #SQLFamily isn’t just about who they are; it’s also about what they are. They are the SQL community. Unlike Other RDBMS platforms, the #SQLFamily openly shares what they know. From folks writing online articles at places like sqlservercentral.com and their own blogs or books. Presenters at user groups, SQLSaturdays, or larger conferences like the PASS Summit. Or perhaps what is most unique about our SQL community is the folks that help out on the #SQLhelp twitter “group”. You see, most of these do it for free (or in the case of SQLSaturday, it comes out of their own pocket). Okay, those that write books get paid for that, and there might be a pittance for some articles, but almost everything else is all volunteering, of both time and money. But they don’t try to hog the spotlight – they are just trying to help and encourage others in all things SQL Server.

Why would they do this? Some may be craving name recognition, maybe even perhaps hoping for a boost in their career. But I’d venture that most are like me, and they like helping others learn. When you get feedback of how you’ve helped someone, or you see the light go on in someone’s eyes while speaking, it is such an incredible rush. When you have taught someone something, you have changed the world. Talk about paying it forward!

My first exposure to #SQLFamily was at my local user group. There, I was encouraged to apply myself – to write and to present. Little did I realize just how much this would make me grow myself, but I suspect that those encouraging me knew.

But this isn’t all that that #SQLFamily is about. It’s also about what else you do away from SQL. The #SQLFamily encourages each other in all kinds of areas. Runners get encouragement where the #SQLFamily tweets messages to them while they are running. We sponsor each other for things that are important to each other (like polar plunges for charity). Or like Brent and Grant are doing for Summit this year and wearing funky leggings for a good cause. We get together for fun nights of SQLKaraoke. When you are down, or if something unfortunate happens, someone is there to lift you up and to offer support. And like all families, we grow. Spouses get added to the mix, and they become part of our #SQLFamily also, sometimes an active part.

So there you have it… my recap of what SQLFamily is all about. Thanks again Jeffrey for the topic, and I look forward to reading what everyone else has posted.

SQLSat320

You know, these 1 hour sessions that are at most SQL Saturdays are just too short sometimes – you just get going and bang! The times up. There’s only so much material that can be presented in just one hour. Recognizing this, last year my friend Jason Brimhall and I teamed up and created an all-day workshop for SQL training. We will be chugging into Raleigh, NC on the SQL Saturday crazy train to deliver this workshop, titled “Murder They Wrote”, at SQL Saturday #320 on Friday, September 5.

The Target Audience and what we cover

If you are a DBA, a database developer, or an application developer that connects to a SQL Server back-end database, then this session is for you. If you are experiencing performance issues, then this session is most definitely for you. If you’re looking for a fun way to spend a day and to get some #sqllearning, then this session is for you. This presentation is geared around decisions that have been made in the development life cycle and the effects that those decisions have on SQL Server, based upon the things that we have seen done to the instances that we have managed (and, in my case, some of the things that I did back when I was a developer and didn’t know better). With no topic being off limits, we also cover critical issues that just aren’t that great to do – and that still happen way too often. And demos? Oh yeah! You know that I like to show things instead of just telling them, so in that spirit, this is a demo-heavy presentation. We don’t just tell you that something is bad and something else is good… we’ll show it to you. We might even blow something up!

So, come on out for a day of learning, fun, and SQL camaraderie. We look forward to spending a day chatting with the attendees, and in helping them to overcome some of their issues.

The Official Session Abstract

Join Microsoft Certified Masters Wayne Sheffield and Jason Brimhall as they examine numerous crazy implementations they have seen over the years, and how these implementations can be murder on SQL Server.  No topic is off limits as they cover the effects of these crazy implementations from performance to security, and how the “Default Blame Acceptors” (DBAs) can use alternatives to keep the developers, DBAs, bosses and even the end-users happy.

Presented by:

WayneSheffieldWayne Sheffield, a Microsoft Certified Master in SQL Server, started working with xBase databases in the late 80′s. With over 20 years in IT, he has worked with SQL Server (since 6.5 in the late 90′s) in various dev/admin roles, with an emphasis in performance tuning. He is the author of several articles at www.sqlservercentral.com, a co-author of SQL Server 2012 T-SQL Recipes, and enjoys sharing his knowledge by presenting at SQL PASS events and blogging at http://blog.waynesheffield.com/wayne.

 

 

 

JasonBrimhallJason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2012. He has experience in performance tuning, high transaction environments, as well as large environments.  Jason also has 18 years experience in IT working with the hardware, OS, network and even the plunger (ask him sometime about that). He is currently a Consultant and a Microsoft Certified Master(MCM). Jason is the VP of the Las Vegas User Group (SSSOLV).

 

 

Course Objectives

  1. Recognize practices that are performance pitfalls
  2. Learn how to Remedy the performance pitfalls
  3. Recognize practices that are security pitfalls
  4. Learn how to Remedy the security pitfalls
  5. Demos, demos and even more demos – (and the scripts to demonstrate both the pitfalls and their remedies will be provided to attendees!)
  6. Have fun and discuss
  7. We might blow up a database

We are trying to have a nice mix of real world examples (and some contrived ones), all of which demonstrate a good and useful point.

Registration – or what to do to join us

If you will be in the area, and you are looking for high quality content with a good mix of enjoyment (and some self-ridiculing), come and join us! You can register for this session at Eventbrite.  There is a limit of 24 seats available for this murder mystery theater, so sign up now to ensure that you have a spot saved for you. The cost is a mere $110.

And then, on Friday September 5th, join us at the Courtyard by Marriott Raleigh North at the Triangle Town Center (3401 Sumner Boulevard, Raleigh, NC 27616). The train gets underway at 9am. Yes, it’s at a hotel, which means that if you’re from out-of-town, you can stay here and just wander downstairs.

But wait, there’s more!!!

Come on back Saturday for the main event – Raleigh’s SQL Saturday is being held at Wake Tech Northern Campus, 6600 Louisburg Road, Raleigh, NC 27616. There are 8 tracks going on, so there’s bound to be something there that you just have to see all day long! The complete session schedule is here. (If you are staying at the Courtyard, it’s only 3 miles away.)

Related posts:

SQL Saturday RVA recap

History of the SQL crazy train

Shameless plug:

Jason and I both present regularly at SQL Saturdays. If you are organizing an event and would like to have us deliver our workshop, please contact Jason, myself, or both of us to make arrangements.

TSQL TuesdayWell, here it is again. The second Tuesday of the month, which means that it’s T-SQL Tuesday. This month, Dev Nambi (blog | twitter) is hosting the party, and he has decided that the topic is to be on Assumptions. Specifically, he wants us to write about:

A big assumption you encounter at work, one that people are uncomfortable talking about. Every team has an elephant in the room.

One assumption that I have seen repeatedly is “if there is a table with a clustered index, I can select all of the rows out of that table and they will be in the order of the clustered index without having to specify an ORDER BY clause”.

So, let’s test this out. Remember, to prove that this assumption is false, all that is needed is to get the results out of order.

First up for this test: create and populate a table.

 

-- let's use a safe database
USE tempdb;
GO
-- create a table to play around with
IF OBJECT_ID('tempdb..temp') IS NOT NULL DROP TABLE temp;
CREATE TABLE temp (
    RowID INTEGER PRIMARY KEY CLUSTERED,
    MyGUID UNIQUEIDENTIFIER DEFAULT (NEWID()));
 
-- populate the table with an ascending number
-- let's try a million rows, generated with a dynamic tally table
WITH Tens    (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                     SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
     Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2),
     Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3),
     Tally   (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions)
INSERT INTO temp (RowID)
SELECT  N
FROM    Tally;
 
 
-- now, checkpoint the database (it's in simple, so all pages will now be "clean")
CHECKPOINT;
-- and let's remove the clean pages from memory
DBCC DROPCLEANBUFFERS;

 

Now let’s just do a few random searches on this data.

-- let's select some data from the table
SELECT  RowID, MyGUID
FROM    temp
WHERE   RowID BETWEEN 100 AND 200;
 
-- and some more data
SELECT  RowID, MyGUID
FROM    temp
WHERE   RowID BETWEEN 1000 AND 1200;
 
-- and some more data
SELECT  RowID, MyGUID
FROM    temp
WHERE   RowID BETWEEN 5000 AND 5100;
 
-- notice that all of these are ordered?

Now let’s run our query against the entire table.

-- now, let's get some more data
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT  RowID, MyGUID
FROM    temp;

On my system, I get the following results:

CI not in order

As you can see, we went from value 1953 to 867133. As can be plainly seen, the assumption is false.

So, what happened? And are you complaining about that READ UNCOMMITTED transaction isolation level? Well, it turns out that this isolation level isn’t necessary to reproduce this – this query will also reproduce the same result set:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT  RowID, MyGUID
FROM    temp
WITH (TABLOCKX);

Are you wondering what is happening here? Well, anytime that you are in the read uncommitted isolation level (you might be familiar with it as the NOLOCK query hint), you are telling SQL that you don’t care about data consistency, so SQL will read the data as fast as it can. Which happens to be an Allocation Order Scan… it will read the pages from the data file in the order that they were allocated for this table. And, for higher isolation levels, anytime that SQL can be sure that the data won’t change underneath it (such as when you have an exclusive lock on the table, or reading from a read-only filegroup), it will also perform an Allocation Order Scan.

Can we see that this is what is happening? Sure we can!

First off, re-run this query with the Actual Execution Plan turned on. You’ll get an execution plan like this:

Non-Ordered Scan

Click image to see full screen

Notice that the scan is not ordered. (If you re-run any of the previous queries, you will see that they are ordered.) With an unordered scan, SQL will get the data as fast as it can since it doesn’t need to follow an index… and in this case, it is an Allocation Order Scan (if you run this query without the TABLOCKX query hint in the READ COMMITTED isolation level, it will still be unordered, but it can’t do an Allocation Order Scan since there isn’t a guarantee that the data can’t be changed). This can include reading pages that are already in memory prior to others (I’ve seen this happen before, and that is why I included those previous queries).

But let’s continue to see if these results are indeed in allocation order. In a separate query window, run this query (undocumented command, but Paul Randal blogged about it here) to see the pages that are allocated for this table:

DBCC IND (tempdb, 'temp', -1)

The results that I get are (unnecessary columns are removed for the sake of brevity):

DBCCIND

And it continues, but this is enough. We are interested in the rows where PageType = 1; these are the pages where the data is stored.

Side note: Do you see a few rows where the next logical page (NextPagePID) is not the next physical page? What we have here is a fragmented index.

So, let’s modify the query to return what file, page and slot that the row is being returned from:

SELECT  RowID, MyGUID, sys.fn_PhysLocFormatter(%%physloc%%)
FROM    temp
WITH (TABLOCKX);

Here we added a function to the result. The %%physloc%% system variable returns the row/page/slot that a row is on, but in a binary format. The sys.fn_PhysLocFormatter function transforms that to something easier to read, in the format “File:Page:Slot”. (Again, this is undocumented, and Paul Randal blogged about this also here). When this query is run, browse through the results, and you will see the pages being returned in the same order as they were allocated in (from the DBCC IND statement earlier). On my system, the results that I get are:

Non-Ordered Scan with physloc

Click image to see full screen

Browse through this result set for the earlier rows and you will see the pages returned in the same order of the physical file allocation of these pages.

In summary: under certain conditions, SQL will choose to perform an Allocation Order Scan of the data. This is not the same as the physical order of the pages in the file, but the order that the pages were actually allocated for this data. The results will be in the order of the pages, and an assumed order is not be guaranteed without using an ORDER BY clause.

You’re utilizing the database mirroring high-availability feature, configured to be in the High Safety mode with automatic failover, which means that the mirroring configuration includes having a witness server to ensure that the system can automatically switch over to the partner in the event of a failure of the principal.

Under this scenario, if the partner server loses its connection to the principal, it will contact the witness server to figure out what to do. In order for automatic failover to occur, both the partner and witness servers have to agree that the principal server is out of commission. There are three possible outcomes:

  1. If the witness is still in contact with the principal, then an automatic failover will not occur. The principal server will remain online, serving the application. However, the transaction log will start to accumulate transactions that cannot be removed until the connection has been re-established and these transactions have been sent to the partner.
  2. If the witness has also lost its connection with the principal, then the partner and witness servers will reach a quorum and the partner database will be brought online, becoming the new principal (specifically, it will undo any un-committed transactions and then bring the database online).
  3. If the partner server is also disconnected from the witness server, then automatic failover will not occur.

The purpose of this last outcome is to prevent what is known as a split brain – a situation where both of the servers are online, serving up results to application requests. If this were to occur, then there would be two different versions of the database, and reconciling these differences would become problematic.

Now let’s consider what would happen if the principal server were to lose its connection to both the partner and witness servers, however the principal server is still online (let’s just say that the partner and witness servers are each in separate remote locations that has had a service interruption – such as a change to a network switch that has isolated these servers from the principal). With the principal server still being online, and potentially able to service at least some users, what does it need to do to ensure that the mirror does not end up in a split brain situation?

Let’s first look at what the possible scenarios are now:

  1. The partner server is in communication with the witness server, and they have reached a quorum where the partner server has been brought online.
  2. The partner server is not in communication with the witness server, and thus it is still in a partner state.

Since the principal server has lost communication with both servers, it can’t know in which situation the mirror is in. Therefore, it needs to protect the mirror from potentially being in a split brain situation. It does this by assuming that the partner server has been elevated to become the mirror’s principal, and so it goes into the partner state. Specifically, this means putting the database into recovery in order to be able to receive transactions from the new principal server.

Is this what actually happens? Well, it’s easy enough to test this out. On three different instances of SQL (they can even be on the same server), establish a mirror in high safety mode with a witness server. After it is established, ensure that SQL Agent is shut down (if running, it will restart SQL), and use task manager to end the processes for sqlservr.exe on the secondary and witness instances. Now look at the status of the database in SSMS, and you will see that its status is “Principal, Disconnected / In Recovery). Alternatively, you can run this following query to see what the status is:

SELECT  sd.name, sd.state_desc,
        sdm.mirroring_role_desc,
        sdm.mirroring_state_desc,
        sdm.mirroring_witness_state_desc
FROM    sys.DATABASES sd
        JOIN sys.database_mirroring sdm ON sd.database_id = sdm.database_id;

To wrap up, mirroring prevents a split-brain situation by:

  1. Both the partner and witness servers have to be in communication with each other, and have lost communication with the principal, in order for the partner server to be elevated to the mirror principal.
  2. If the principal server becomes disconnected from both the partner and witness servers, it goes into partner status and puts its database into recovery.

After installing SSAS, using the tabular model, you receive the error “The service cannot be started: The following system error occurred:  A required privilege is not held by the client.” when trying to start the service.

Examining http://msdn.microsoft.com/en-us/library/ms175371.aspx shows that the only security privilege needed for the SSAS service account is the “Log on as a service” privilege.

Alas, this is not accurate. When running in the tabular model mode, the service account also needs the “Increase a process working set” privilege.

In Windows, under a default installation, the local group “Users” has this privilege (which would allow any user on the system to have this privilege) as seen below:

Increase a process working set

However, many companies will lock down the security policies and remove this privilege. This is apparently a problem because there are a lot of requests on the internet for this issue. The advice generally given is to just run SSAS as an administrator – which is not a good choice, and may not be an option in your company. Now there is a solution.

I’ve filed a connect item to correct the documentation at https://connect.microsoft.com/SQLServer/feedback/details/869677/ssas-privileges-incorrectly-specified – please consider going out there and voting for it.

Wow, what a month. On March 22, 2014, the SQL Saturday train came into Richmond VA (RVA). I was one of the organizers of the event, and what a ride we had.

The rough part of the ride started off earlier in the week. A freak spring snowstorm rolled through the region. Richmond was spared, but points just north of us (Washington D.C. for instance) had 6 inches of snow. This caused a few speakers to need to cancel as things got rescheduled for them. As we got closer to the event, a few other sessions canceled. Now when you are having an event with people coming to speak, and other people coming to see their presentations, this is not what you want to have happening. At one point, we had seven vacancies in our speaking schedule. But the fun wasn’t over yet – on Saturday, two additional speakers failed to show up. They didn’t even call or email anyone that they would not be attending. We juggled things around, coaxed speakers into doing additional sessions, and continued on.

On Friday, we had our first ever RVA precon. About 50 people showed up to attend an all day training in two precon events. One of the precons was conducted by Andy Leonard (SSIS Design Patterns), and the second precon was conducted by Jason Brimhall and myself (Murder They Wrote). Since I was busy with own precon, I couldn’t drop in and see how things were going in Andy’s session. All I can say about ours is “WOW!” What a day of learning. We have heard from several of our attendees since, and they have been busy implementing various things to fix problems. Some comments that have been made about our precon session are:

Feeling like a newbie with #SQLServer while attending @DBAWayne and @sqlrnnr precon at #SQLSatRVA. Great class!

It was an eye and mind opening experience awesome session!

Friday’s session went beyond my expectations for the day.

The information was very useful.  I sat down with my team yesterday to discuss what I learned.  I am certain that we will see implementation of a few items…

Needless to say, I’m extremely pleased to have this feedback. Now I’m eager to do this precon at other events!

Friday night, we had our speaker / volunteer dinner. It was great to reunite with several of my #sqlfamily, and to meet folks that I had never met before and bring them into my #sqlfamily.

After the precon all day Friday, Saturday morning came way too early. Shortly after arriving, the attendees started showing up. Even without coffee yet, there was an air of excitement all around! This was going to be a perfect day! The weather was almost too perfect – a beautiful spring day, and the temperature eventually reached 70 degrees. Just a perfect spring day. The speakers were showing up early; all equipment was functioning, registration / sign in was humming along, and breakfast was being efficiently served. Changes we had made from last year were really working out. The first sessions started, and we could take a breather. We had a few Microsoft Premier Field Engineers (PFE) down from DC, and they manned a “SQL Clinic” room to answer questions. I was really curious about how this would work out for us today.

The second session of the day, I was presenting a fairly new presentation – “Crazy Things Developers Do”. As is typical of my presentations, it has a lot of demos. And as I was setting up, I ran into a technical glitch. Even though this was the same room that I had presented in all day on Friday, my laptop would not sync up with the video equipment. It had worked just fine for the speaker before. I tried rebooting. The facility tech guys checked it out, and couldn’t find anything wrong. So… I ended up doing my presentation with no props. I talked my way through it. At one point, I had everyone gather around to watch one demo that I just had to do, but the rest was all talking, with no slides or anything. In retrospect, I should have used the whiteboard in the room – I guess that I was too stunned and shell-shocked to even recognize that it was there. The attendees said it was still a good session, and that they learned. But oh, how I wish the tech stuff would have worked. Luckily, everything worked out for all of the other speakers for the rest of the day, and this was the only glitch that we had!

Lunch time came around, and again everything went smoothly. Did I mention that it was a beautiful day outside? Most attendees took advantage of it, and ate their lunch outside. You just couldn’t ask for more. A nice spring day, relaxing outside, chatting with friends.

The rest of the day went by too fast. Before long, we were having the closing, and raffling off the prizes from the vendors. I checked in with the Microsoft PFEs, and found that over 20% of the attendees had stopped in and asked questions that they had been able to answer. AWESOME!

People come to a SQL Saturday to learn. Which means that we need people in rooms presenting topics that people want to see. I want to thank all of the speakers that came to SQLSatRVA to present a session or two. I especially want to thank all of the ones that came from outside of the RVA area! A SQL Saturday just can’t be done without these great folks – you are what the attendees are coming to see. Words alone cannot express my appreciation for the time you have spent preparing the presentation(s) that you have delivered, and the time and expense that you have incurred to come to RVA to do your presentation. Thank you so very much!

There is another recurring theme to this post… smooth functioning. This doesn’t just happen – it takes a lot of effort from a lot of special people – people that are passionate about SQL Server, learning, and helping others to learn. People that give of their time to help ensure that the event functions smoothly. Some do work ahead of time, in planning the event, getting sponsors, figuring out the logistics for the location and food, working with the sponsors for advertising and ensuring that their equipment is there ahead of time, and in organizing the volunteers. But this isn’t all… there is a lot of work being done the day before and the day of the event. From room monitors to ensure that speakers have what they need. Spouses that come in to help with registration and ensuring that breakfast, lunch and break times function smoothly. So, I’d like to end this post with a special THANK YOU! to all of these wonderful volunteers, and a picture of a some of them. Of course, with our community, you know it won’t be a serious picture…