Want to start a fight between a bunch of DBAs or database developers? Ask them where the comma should be placed between columns in a SELECT statement. It’s actually a little disturbing just how much emotional value people place in this. Almost as good, tell a database developer you don’t like their naming standard. Hoo boy!

The purpose of a naming standard, I think most of us can agree, is for clarity. We’re defining language so that we all mean the same thing when we say it, right? We want to communicate clearly, so we’re going to implement a naming standard. Fine. Sounds good. And then, you get this:

dbo.tblDdltbl

Umm… Wait. What?

And there are hundreds of these, all with the same first three letters, tbl. Oh, wait, I get it. It’s tbl for Table. Because… um, we need to know what tables in our database are actually tables?

Where’s the clarity?

What about the rest, Ddltbl. If you’ve been to any presentation where I’ve explained Ddltbl (pronounced diddletibble) you must not reveal the secret. I’ll delete your comments. But feel free to guess if you don’t know. No one has ever guessed correctly. Why? Because it’s not even remotely clear. It’s an abbreviation, but not one you’ll find in a dictionary. No, instead, you’ll have to look this up in a special list that matches weird abbreviations, some of them weirder than Ddltbl, with real words. Clarity IS NULL.

OK, what about using spr_ & spu_ for stored procedures? Spr_ means a read procedure and spu_ means update. Clear right? And it’s clearly expandable to insert & delete & merge and procedures that do a mix of things… Wait, we can’t use spm_ for Mix because we’re using that for Merge. I know we’ll use spx_ for procedures that do a mix of things. What’s that, we already used spx_ for XML queries? What idiot did that? Me? Oh, well, let’s see, we’ll use spq_ for procedures that do a mix of things… And clarity just went away again. Not to mention the fact that I want to insert data into the InvoiceDescription table and to get type ahead I have to type ‘spi_I’ before I even get close to being able to select the correct table from code completion. That’s five characters. I’m lazy doggone it! I don’t want to type that much.

Let me now stand up and admit, WAY back at the beginning of my career in data management, I created an entire system with tables that were prefixed ‘tbl’ because I came out of development that used, what was called at the time Hungarian Notation, which was 1-3 characters before a variable to describe that variable. It was a holdover from the days when we needed to account for every letter used because the amount of memory & storage in systems was very tiny. By the gods, I even named every column with a prefix denoting its data type. And, I came up with the spr_, spu_, spd_, spi_, spm_, spq_, spx_ naming convention too (to my utter shame, and I think we had one or two others tossed in there that I don’t remember). Yes, I’ve inflicted this craziness on others. That’s a huge part of why I know it needs to stop (and no, I was NOT responsible for Ddltbl, I fought against it tooth & nail).

We actually have a common language that we can use to communicate clearly what objects are within our databases and what they do. It’s the written and spoken language that we use to communicate to each other when we’re not creating databases. In my case, this is English. Why don’t we just say that the table is Invoice (or Invoices, I’m not about to quibble on that) and that the procedure for adding data to the Invoice table is InvoiceAdd or InvoiceInsert? Why don’t we just use our language skills. Anyone who has had young children has repeated the phrase “Use your words” more than any of us care to think about. Let’s do the same thing with our database objects. Use your words!

Oh, and you should put your comma after the column.

For many years, I loved being a DBA because, unlike being a developer, I could sort of hide from the world. I could go into a dark closet (a well-lit cube, but hang with me here) and hide from everyone (except the people in the cubes next to me, my boss, the teams I supported, the people I passed on the way to the toilet, others) and just be a hermit (except for the daily stand-ups, weekly status meetings, occasional training) and only ever talk to people when things went wrong at 3AM (or at 2PM when the CIO and a bunch of other managers would be standing in my desk). As long as I did my job well, maintaining the backups, running maintenance, and setting appropriate security settings, I didn’t have to interact with people (except when the QA team needed a database refresh, or the dev team wanted an ad hoc deployment, or someone came up and asked me how to make a query run faster, or I had to go to another doggone meeting) at all. DBAs just get to hide in a closet and avoid all human interaction, right?

Yeah, you & I both know that’s not true. Maybe it was once, a very long time ago, but no more. We must interact. If you’re in a very small shop, you’re working on everyone’s systems, with every team, maybe even the entire company. If you’re in a bigger shop, you still interact with the storage team, the network guys, the server guys, the developers, business people, management, etc.. In short, the database professional actually does need at least some people skills (and was that a painful revelation for someone who was already called the Scary DBA). You’re going to need to be able to convince the SAN admin that, yes, in fact, you do care how the disks are laid out and here’s why (and threatening to remove their silly little mustache just gets you a trip to HR, trust me on that). This requires the ability to interact with people, at a personal level. I know, I know. That’s hard. Believe me, I get it. You’re talking to the guy who made a data modeler burst into tears by pointing at their model and laughing. Not good. Not helpful to you, your career, or in getting the model fixed the way you want it to be. You must actually treat people, like people.

What to do? Here’s my suggestion. I’ve taken a couple of classes with Don Gabor. He’s going to be hosting a new one, specifically aimed at IT types, in New York City on March 19th. Check it out. Try it out. Improve how you converse with others and you might just improve your career.

I just did a series of Boogle searches and when that didn’t find anything I tried Ging. Neither listed sys.dm_db_wait_stats. Nothing in a search directly against MSDN either. So, let me introduce you to a new DMO, sys.dm_db_wait_stats.

It’s a dynamic management view since it doesn’t require any input. The output is about what you would expect if you thought about it for a second:

In short, what we have is the sys.dm_os_wait_stats moved internally into your SQL Database so, even though you cannot get at any of the OS counters from with an a SQL Database normally. In short, thanks Microsoft. Now we can see the wait statistics on our Azure SQL Database in order to better understand where things are problematic.

Without documentation I don’t know for sure how far back these stats go. But like the other wait stats, the best way to use them would be to query them twice, say, once an hour ago and once now, or once yesterday and once today. Having two comparison points is how the values prove useful anyway. It’s likely that they reset when your instance moves. I’m working on trying to confirm this.

I’ve been exploring execution plans in Azure SQL Databases a lot lately. I’m getting a presentation together for  some upcoming SQL Saturday events (first one is SQL Saturday #177, Silicon Valley). If you scroll to the bottom of this previous post, I mentioned that there were clearly differences in the optimizer because queries against empty databases were generating different plans. I’ve loaded up the data in my database, both SQL Server and SQL Database, so I can compare real behaviors. Doing so, I found a fun difference, even though I was running the query and generating the plan from SQL Server Management Studio.

Here’s the property sheet from the SELECT operator for the query run against SQL Server:

And here’s the property sheet from the SELECT operator for the query run against SQL Database:

Two differences jump out. The smaller of the two is that for the same data set, there are differences in estimated values. I noted that before, but it’s fun to see it coming up. By the way, if a good estimate could be defined as “closest without going over” then the optimizer in SQL Database is closer with 1.9 to the SQL Server value of 2.1. Just sayin’. Anyway, the big thing that you may have noticed was the NonParallelPlanReason with a value of CouldNotGenerateValidParallelPlan. Oooh, cool! Not that I would have expected a parallel plan for an estimated cost of .0149347. Assuming SQL Database has a default value of 5 for parallel plans, that’s pretty far off (and I hope they don’t still use that ancient value, nor should you). Some enterprising individual could try to suss out how much cost is needed to get a parallel plan (I don’t have time for that right now). More to come on execution plans and query tuning in Azure SQL Databases.

In my previous post showing how to get to execution plans in the Database Management Portal, I showed that it’s pretty easy to put a query in a query window and get the execution plan. This allows you to understand query behavior in order to tune your T-SQL or your data structures, all through the Azure interface. But, what happens if you want to share an execution plan with a friend, post it to an online forum, save it for later comparisons as part of troubleshooting bad parameter sniffing, track behaviors over time as statistics change, other purposes that I can’t think of at the moment?

To first answer this question, let me tell you how you would do these things in SQL Server Management Studio (SSMS). First, and most obviously, when you look at the plan in SSMS you can just right click on that plan and select “Save Execution Plan As…” from the context menu. This will allow you to create a .sqlplan file on your OS. From there, do what you want. The file saved is XML, but the .sqlplan format is read by SSMS as a graphical plan. You can look at the underlying XML any time you want by right clicking and selecting the appropriate context menu.

And Azure SQL Database?

Well, things are a little different. Let’s start with the query. After you execute the query to get an actual plan or call up the estimated plan you’ll see the plan in the Portal.

You can see the query, the plan, and options with the plan (to be explored in a later post). If you right click on the plan as you would within SSMS, the context menu is singularly unhelpful since it will only have one option ‘Silverlight.’ (yes, we’re still running Silverlight in this part of Azure, don’t shoot the messenger). So I guess there’s no way to save that plan, right? Wrong. Look up at the top of the image above. You’ll see a button that looks like a 3.25″ floppy disk (kids, get granddad to explain what that is) and the phrase “Save As” underneath it. Click on that and you can save your query. Ah, but there’s a drop down under file types and there you’ll see “SQL Plan File.”

Provide a file name and you can save the execution plan down to your machine. Easy.

Ah, but there is a catch. Let’s say we query the Dynamic Management Objects to pull plans out of the cache. This query will run on both SQL Server and Azure SQL Database:

SELECT	deqp.query_plan
FROM	sys.dm_exec_query_stats AS deqs
		CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
		CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE	dest.text LIKE 'SELECT	m.MovieName,
		msd.MovieStageName,
		st.StageTypeDesc%';

The results, when run from SSMS, will look something like this:

That’s an embedded URL that you can click which will then open the execution plan from the cache. But, when the same query is run from SQL Database Management Portal, the results look like this:

No options for a clickable interface here to open the plans. You can try copying the output and then pasting it into Notepad then saving as a .sqlplan file. Of course, that’s only going to work for small plans since the result set here is not unlimited. You could build an app to query the cache and output to file. But, in general, querying directly to the plan cache from the SQL Database Management Portal gives you very few options for displaying your query plan.

Since this is such an important part of query tuning, here’s hoping that we get further enhancements to the Management Portal in the future.

I was very privileged to get the opportunity to write a chapter in a book with some of my friends and SQL Family, Pro SQL Server 2012 Practices
. Just as each of us took a chapter to write, each of us going to take a chapter to review. But, being the greedy sort, I’m going to review two. First up, Chapter 12, “Windows Azure SQL Database for DBAs”, by Herve Roggero (b|t)

Personally, I love Azure. And I love Azure SQL Databases. But, I get the fear and trepidation they might cause. I also get the urge to write about them, but I never really felt like I should approach them from a book. Everything changes so much, so quickly in Azure and books just take a while to get out the door. It all seemed like an exercise in terror. But, Herve Roggero has taken a very smart approach here. Instead of a straight how-to, which is what I would have stupidly done, Herve has done a “why” approach. Why would you use this? What does it offer? What business need are you fulfilling? And I really love that approach to technology, not worrying about the technical aspects of it, but the business needs that it answers.

Herve starts with the basic architecture of the SQL Database. His emphasis is on the fact that we’re talking availability and up-time instead of performance. He does a good overview of how things are put together on the server so you can understand what’s happening to your data and why. He then goes on to show differences between what you are presumably familiar with, SQL Server, and SQL Database. He also covers the various management tools available online as part of Azure and security settings. He’s very careful to qualify things so that you know that everything is subject to change, but he still packs a lot of useful information in as he discusses the types of databases available and how to backup your data (you don’t, but you can get around that to a degree).

Herve spends a lot of time talking about how Federations are currently working within SQL Database. This is because Federations are one of the key business needs that the Azure SQL Database answers. He gives a great overview of Federations works then walks through a simple example so that you can understand what he meant. He finishes up that section with a review on the limitations so you will know how it affects your business needs.

Since performance is a secondary consideration everyone can breathe a sigh of relief, right? No! Of course not. And Herve doesn’t let us down. He covers some of the most important performance Dynamic Management Objects (although he calls them DMVs, dude, get with the program. Kidding, kidding.) you’ll need for understanding which of your queries are performing badly. He also provides a nice overview of how execution plans are presented within the SQL Database Management Portal (SDMP, a new abbreviation, I’ll use this). He also shows how the Query Performance dashboard will help you identify problem queries within your SQL Database.

Herve also covers some of the other utilities included with SQL Database such as Reporting Services and SQL Data sync. It’s important to remember, we’re not just talking T-SQL here. This is a full infrastructure to answer complete business needs. Herve doesn’t forget. He concludes with a short discussion on price, something that must be taken into account when talking about Azure SQL Database.

In summary, this is an excellent introduction to the topic of the Azure SQL Database. Herve has supplied you with the information you need to begin to make decisions on whether or not this could be something to satisfy business needs within your organization. Nice work Herve.

I heard about this new law that was passed to prevent employers from asking for your social media passwords. After the laughter stopped, I realized that, maybe, this would be needed. Not because I need the government to help me manage my interactions with my employers and potential employers. And not because I think the government needs to be involved in other peoples interactions, not at this level. But because I don’t think people realize they have a word that they can use with employers. That word is ‘No.’

“We want you to sign this non-compete agreement that says you’ll never be a DBA for any other company after leaving ours.” Ha! No. And yes, I really had one of these. And yes, there actually are laws against it (I looked it up), but I didn’t need them. I figured out all on my own that I might not stay at that company forever and that, after leaving the company, I might actually want to ply my trade. By the way, this particular dot com is long dead.

“We require you to take a psychological test as a requirement for employment.” OK, I did this, because I was very interested in the output of the test, but I asked them, what if I said no. They said they’d hire me anyway. Why? Because they can’t make you do stuff you don’t want to. Oh, and the output said was I a psycho killer, but they hired me anyway for some reason.

And I’ve heard that some employers want your private email address password. Again, no.

Here’s the deal, what I do in public, out loud, at large, that is my employer’s concern. If I’m posting compromising photo’s of myself and I’m a company spokesman, of course my employer can, and probably will, get upset. But if I’m communicating with my significant other or children, friends, family, etc., in a private space, even one that is digital, that’s what we used to call nunya, as in Nun Ya Business. You just don’t get to go there. Sorry.

Look, I get it. It’s a cold, awful, horrible world out there, especially if you are not employed. I’ll never forget the 3 months (and thank the gods it was only 3 months) that I spent looking for work in the fall of 2001. And when I finally got a job, I took a massive pay cut. Why? Because making X was more than making ZERO, which is what I was making at the time. So yes, you may compromise yourself at times. But, understand, it’s you making the compromise. You get to make the choices. No one is holding a gun to your head. If someone asks you to do something ridiculous or insane, say ‘No.’

If you’ve been working with the Azure SQL Database portal, you know that you have a query window. Within the query window you can run queries against your server. You’ll get back results, timing, everything you need to observe behavior within the system. What’s that? You never heard of this? Let’s figure it out real quick. Here’s my portal to my Azure SQL Databases:

Azure SQL Database Portal showing usage overview

Yes, all sorts of things you can do from here, but we’re focused on the bottom of the screen at the Manage button. Clicking this button will open up the Management portal window, part of which you can see below:

Part of the Azure SQL Management Portal showing some of what can be done.

Pretty prominent in the upper left corner, you can see the New Query button. I’m sure you’ll be shocked to find that clicking on that gives you another window in the portal as shown below. Worth noting though is the listing under My Work on the left side of the screen. These are more or less windows that you can switch between, without losing your work. It’s almost like tabs in SSMS. You can open multiple queries as you see. I have two unnamed queries I’m playing with and a file, TraceCaptureDef.1.sql, which will generate all sorts of fun errors if I try to run it on Azure. But the main point is, don’t feel like you have to open multiple tabs in your browser, you have a lot of control right there in the Management Portal.

Azure Portal with a Query Window open

I have a query there ready to go. A few more points about that query. Note that you do have some color coding, showing key words highlighted in blue. I typed this query and formatted in Management Studio before pasting it into the query window here, but that formatting went away to a degree. Something to keep in mind. Also note, that you don’t have properties about this query that you can set. If you want to see statitistc io or time, which you can, you’ll have to set them manually using T-SQL code. But once set, you can get messages showing how long the query ran and what sort of resources were used as you see here:

Messages shown after running a query in Azure SQL Database

You can also choose to look at an Estimated execution plan by clicking the appropriate button, or toggle the inclusion of an Actual Execution plan by again, clicking the button at the top of the window. Here’s what you get for an Actual plan within Azure SQL Database:

Execution plan for the query outlined above as displayed in Azure Management Portal

I’ve posted in the past about some of the details that you can access when looking at execution plans within Azure SQL Databases. I’ll be posting some more on the Management Portal, execution plans, and query tuning in Azure.  Worth mentioning, if you look at my posts from several month ago, how they’re displaying icons in the plans has already changed. This is a seriously moving target.

Before I go, compare that execution plan with the same one from SSMS:

SSMS Version of the same query against an identical structure

A couple of things to point out. Obviously, it’s different. But the real point here is that these are identical, empty, databases. The structures are 100% the same. The queries are 100% the same. But the Azure SQL Database plan includes a scan where there’s a seek in SSMS. More fun little indications that the optimizer that exists in Azure is not the same as in the base product (and yes, I’ve got the latest SP and CU installed). Something to keep in mind when you start writing queries against your Azure SQL Database instance.

I have lists. Lots of lists. I even have them in different locations sometimes. Some of them are carefully written down in my notebook, others are typed into OneNote and I’ve been experimenting with Remember the Milk and Trello (Trello is winning). These lists include ideas for presentations, blogs, articles. Notes from sessions I’ve attended or meetings. Lots and lots of plans and ideas and all that sort of stuff. I try to keep it organized, but sometimes it runs away from me. However, I find writing things down helps me to keep things organized. Between very carefully scheduling out my calendar and all these notes, I only occasionally completely drop the ball.

One ball I dropped was coming up with some goals, some plans, for 2012. I just plowed through 2011 and then 2012 and here I am, two years an employee with Red Gate (and I’m sorry to say, terribly happy there, my apologies) and I’m not sure where I should be going. I don’t mean within the company. We’ve got lots going on there (more than I can keep up with). No, I mean career goals, work outside work, all the extra stuff.

I’m going to break these down into pure technical issues that I want to spend more time on and professional development. Let’s start with the easy stuff, technical.

First, HDInsight. I absolutely believe there’s something big possible with this technology. I could be utterly wrong (I sure thought SCOM had legs too, oops). But I’m going to keep slogging on this stuff til I get something of a more thorough understanding of it. I doubt I’ll be presenting on it except maybe for introductory sessions, and not even those for the next 3-6 months. But I’m going to get my mind wrapped around it and understand how it works. Maybe then I can better determine if it’s just a highly specialized impact wrench or a true paradigm shift.

Next up, more Azure. Go ahead, giggle. Microsoft keeps doing more and more stuff with it. You know you can store spatial data in Azure now? I didn’t either until earlier this week. I’m going to be presenting on how to do query tuning using Azure SQL Database tools. Actually, I’m thinking I’ll only use the online tools. Everything you need is there. You can access most of the Dynamic Management Objects (DMO) and you can read execution plans from queries there. I’ve got presentations already scheduled for this. I’m pretty excited about others that might be scheduled (LOTS of noise if those come to pass). I’m going to continue expanding my learning here.

As to professional/personal development, I’ve got something somewhat trivial I want to work on, and something that, frankly, scares me. I would also like to find a coach or coaches.

The trivial task is that I want to work on pumping up my slide decks. I’ve long simply used them as markers for my talks. Bullet points as reminders of the topics I want to make sure I cover. They somewhat act as a point for taking notes for the attendees, but they are excessively dull. I’m going to work with Mrs. Scary (a graphic artist, good at her job, freelance if you need some help and are willing to pay for it, get in touch) and punch up my presentations. I had about 12 or so different presentations last year. This year, I’m dropping that number down a little.

Scary. Really scary. No, not me. Me. I mean I’m scared, not scary. I’m scared because I’ve taken on the task of acting as a mentor to an actual human being. I don’t know about the rest of you, but I find that extremely scary. I’ve got a level of responsibility that I’ve never had with putting up a blog post or even making a presentation. It’s as bad as writing a book (and if you think putting your name on a book isn’t scary, wait til you see your first bad review). But, I’m excited by it. This is something new and different. I’m really looking forward to it, and I think the person who approached me has a lot of potential. They’ll be great without my ever getting involved, so if I can take some of the credit, I win. Sorry, joke. I’m going to try to help someone who doesn’t need it, but will benefit from the help. Assuming I get it right… Scared again.

Coaches. Am I doing the marketing bits of my job right? I don’t know. I need some help in that area. Am I approaching learning HDInsight correctly? I could use a coach to talk with and get some guidance from. Maybe some suggestions on my slides (although my wife is going to supply a lot of that). Pro athletes do it. Good surgeon’s do it. They must know something. It’s an approach I want to try.

That’s it. I’ll be continuing things like working on books (yes, I’ve committed to writing one and editing one, so far), presentations, light consulting, and working for a fantastic organization. But these are the current plans for 2013.

By default statistics are created automatically within SQL Server. And, by default, these stats are updated automatically based on a set of triggers. The triggers are defined as

• If 0 rows – Any data added leads to a statistics update
• If < 500 rows – 500 rows added causes a stats update
• If > 500 rows – 500 rows + 20% of the number of rows causes a stats update (unless you enable a traceflag in 2012 in which case you get a proportional value instead of 20%).

There are some exceptions for temporary tables and some variations for filtered statistics and filtered indexes, but you get the idea. I was writing an article on statistics in preparation for another Oracle/SQL Server discussion (on, you guessed it, statistics) and I came to these rules and wrote something along the lines of “So even if you have 499 rows, in order to see the stats update you would need to have to add more than 500 additional rows, meaning you would get to 999 before you saw a statistics update.” Which made sense, but I was suddenly concerned, is that valid. I decided to clarify it for my own piece of mind.

Quick set up of a test table and loading 499 rows into followed. I also ran a simple query to generate some statistics on the column:

CREATE TABLE dbo.StatsTest (StatsColumn INT);

WITH Nums
  AS (SELECT TOP (499)
ROW_NUMBER() OVER (ORDER BY (SELECT 1
)) AS n
  FROM master.sys.all_columns AS ac
CROSS JOIN master.sys.all_columns AS ac2
)
INSERT INTO dbo.StatsTest
(StatsColumn)
SELECT n
FROM Nums;

--auto create stats
SELECT *
FROM dbo.StatsTest AS st
WHERE StatsColumn = 42;

This results in the follow set of statistics:

If you click on it to look at the details you can see that it shows 499 rows sampled.

If I add two more rows to the statistics and then run a different query, guaranteed to require a statistics update:

SELECT	*
FROM	dbo.StatsTest AS st
WHERE	StatsColumn < 42;

If I rerun DBCC SHOW_STATISTICS I still see the same output. In short, it appears that my original assumption was correct.

I ran several more tests, and I could get to 999 without updating the statistics, but adding just one more row and breaking 500 additional rows and the statistics update fired. I needed to validate this behavior for myself and it worked.