SQL Server Central Guest Editorial

October 14, 2009 at 6:40 am (Misc) (, , )

My second guest editorial is up at SQL Server Central. These are fun to do (especially the video) even though they’re a shocking amount of work considering their size. My hat’s off to Steve for churning these out day after day. The man does earn his keep. Anyway, go on over and let me know if you have a favorite character from history that might make a good DBA.

Permalink Leave a Comment

Operations Manager Feedback

October 13, 2009 at 2:14 pm (SCOM) (, , )

If you’re involved with maintaining Microsoft Operations Manager, you probably have a few things you’d like to tell the developers, such as, couldn’t you get some sort of override manager/explorer built into the system? Well, they’ve built a new feedback mechanism through the updated Connect interface. Get on over there and make your voice heard.

Yeah, I wasn’t really tracking it either until I saw this post from the OM development team’s blog.

Permalink Leave a Comment

Table Variables Are Only in Memory: Fact or Myth

October 13, 2009 at 8:40 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , )

I keep seeing these concepts that have long been disproven, posted again and again on newsgroups as if they were valid information. The latest? Table variables are better because they’re only in memory where as temporary tables write to the disk through tempdb. This one is abjectly wrong. I’m not even going to hedge with “it depends.” From a macro point of view, there are only a few differences between temporary tables and table variables, statistics being the biggest. Temporary tables have ’em and table variables don’t. Other than that, both will reside completely in memory or will swap out to the disk through tempdb, depending on their size. Some of the minor differences, and why you might want to use table variables over temporary tables, table variables won’t cause a statement recompile while temporary tables will, table variables don’t participate in transactions and log data for a rollback is not maintainted for temp tables. There are a number of other uses and functions around table variables, but they’re not applicable to this discussion. The point here is that table variables do not live only in memory.

Some of you don’t believe me, of course. So let’s prove this one, again. Here’s a very simple query. It creates a temporary table and loads one hundred thousand rows of data into it. The query does all this within an open transaction so that we can investigate where the data is stored, at our leisure:

INTO #Nums
FROM master.dbo.syscolumns AS sc1
,master.dbo.syscolumns AS sc2

This will leave the temp table open and created. The only thing remaining is to determine where the data is stored. To do this, we can query sys.dm_db_session_space_usage. This DMV shows the pages allocated and deallocated to the tempdb. If I run a simple select against the DMV, I’ll see 233 pages in the user_objects_alloc_page_count for the session I’m currently connected as. That represents the data from the temporary table, stored within tempdb. I’ll rollback the transaction and close the connection. Then, I’ll add the following code to the end of the query in a new window with a whole new connection. I do this to avoid any possibility that the temporary table is still within context. Here’s the addition:

FROM #Nums;

Now I’ll run the whole query, creating two identical tables, a temporary and a variable, with identical data. When I look at sys.dm_db_session_space_usage, I now see 466 pages in the user_objects_alloc_page_count for the new connection. That’s because the tempdb is now holding two tables worth of data instead of one.

Or, in other words, table variables are stored in tempdb like temporary tables.

EDIT: Modified the description of the differences between table variables & temp tables.

Permalink 5 Comments

Another Book

October 10, 2009 at 9:05 am (SQL Server 2008, SQLServerPedia Syndication) (, , , )

Well, part of one anyway. I wrote three chapters of Rob Walter’s new book, Beginning SQL Server 2008 Administration. I think I’ve mentioned it before. Well, my copies showed up in the mail. Nothing like holding that book in your hands as proof that you’ve done it. When everything is electronic and we stop printing dead tree books… I don’t think it’s going to be nearly as satisfying to finish a book. Please, look it over. I think it’s going to be a good resource for people just getting started as DBA’s.

Permalink Leave a Comment

3 Weeks to the PASS Summit

October 10, 2009 at 8:10 am (PASS) (, , )

The PASS Summit is only three weeks away and I’m getting awfully excited. It’s not too late to register. Heck, you can register at the door, but you’ll pay full price. If you want to save a little, use this registration code, FRP3D, and tell them you know me. They’ll hook you up & you’ll save $200. 

If you’re involved with designing, building, developing, developing against, supporting or maintaining SQL Server databases or manage any of those who do any of the above, this is where you should be for the first week of November. You’re going to get great information from presentations by top experts within the SQL Server community. Speaking of community, this is one to take part in. You’re going to be able to network, talk to, confer, converse and otherwise hob-nob with your fellow DBA’s, Developers, and Database Developers. Don’t quite feel comfortable networking. There’s an answer for that at PASS too. A pre-conference session is being held on Monday evening just on better networking and communication.

For those who won’t, or can’t, attend the Summit, I’ll post a daily summary here on the blog. If you’re on Twitter, keep an eye out for #sqlpass tweets from me and others. Last year we put quite a load on Twitter. This year, we might just bring it down. It’s worth monitoring even if you’re at the summit. If nothing else you’ll know which parties people are attending.

Three weeks… I can’t wait. If you see me, be sure to say hello. I’m presenting twice on Tuesday (Execution Plan Best Practices, DMV’s as a Shortcut to Procedure Tuning). On Wednesday, I’ll be one of the guys wearing a kilt. On Thursday I’ll be one of the guys curled up against a wall (you’ll see, it’s tiring). This year will be great.

Permalink Leave a Comment

SNESSUG September Meeting

October 8, 2009 at 12:31 pm (SNESSUG) (, , , , )

We had a good night with 13 people attending. I ran unopposed for president of SNESSUG for my second, and final, term. We were graced with the prescence of Jeff Moden, MVP and on of the top posters over at SQL Server Central. He was just visiting. He’s a great guy and I was very happy for the chance to meet him in person. Jeff is going to be at the PASS Summit this year, so you too can have a chance to meet him.

The presentation was on SSAS and SSRS by Sunil Kadimdiwan. He did a full introduction of cubes from Excel to Analysis Services, all with the purpose of showing how to generate good reports using Reporting Services. Attendees were very interested in some of the security settings for Analysis Services. Sunil walked us through the use of BIDS, or Business Intelligence Development Studio, to build & modify cubes.  The presentations were well received. Interestingly enough, he used very few slides and spent all his time working through examples.

Then, the fire alarm went off. We lost about 20 minutes.

Sunil came back in and finished up on Reporting Services. He’s a trooper.

Permalink 2 Comments

PASS Log Reader Awards

October 8, 2009 at 12:30 pm (PASS)

Brent Ozar, Andy Warren and Jeremiah Peschka have put together a blogger award called the PASS Log Reader Award. I took a couple of posts that I did in the last year and entered. No, I’m not going to say which ones and I don’t expect to win because I just picked ’em up, as is, no edits and submitted. I didn’t browse all my posts, I didn’t rank them, rate them, or do anything else. I just picked two that seemed to describe something and describe it reasonably well.

The way I look at it, win or lose, this is supporting PASS, so it’s worth taking part. Plus, I like a little competition. The award will be presented at the Summit (which is only three weeks away!).

Permalink Leave a Comment

Hey, ho, where’d you go…

October 6, 2009 at 7:37 am (PASS, SQLServerPedia Syndication) (, , , , )


I’m presenting on Thursday to the Columbus SQL Server Users Group, home of the newly minted MVP, Jeremiah Peschka (congrats again). It’s to be a virtual presentation through live meeting. The topic is a Understanding Execution Plans. This time I hope that one of us remembers to hit the record button unlike last time when I did this presentation for the PASS AppDev Virtual Chapter. If you’re in the area, come on down and meet me, virtually. Show runs from 6:30-8:30, although all that time is not my presentation, so I suspect Jeremiah has some more fun planned.

Permalink Leave a Comment

Ad Hoc Queries Don’t Reuse Execution Plans: Myth or Fact

October 5, 2009 at 7:00 am (SQL Server 2005, SQL Server 2008, TSQL) (, , , , )

Another frequently heard story is that stored procedures get and reuse execution plans, but ad hoc queries do not. A lot of people believe this and say as much online and in the real world. Unlike my last myth, this time, I’m going to give you the DBA answer to this question. It depends.

There are ad hoc queries and there are ad hoc queries. The classic ad hoc query looks like this:

DECLARE @sql NVARCHAR(MAX), @value int;
SET @value = 43668;
SET @sql = 'SELECT * FROM Sales.SalesOrderHeader AS soh ';
SET @sql = @sql + 'JOIN Sales.SalesOrderDetail AS sod ';
SET @sql = @sql + 'ON soh.SalesOrderID = sod.SalesOrderID ';
SET @sql = @sql + 'WHERE soh.SalesOrderID = ' + CAST(@value AS NVARCHAR);
EXEC (@sql);

And as ad hoc TSQL goes, that one is actually some what clean. They get a lot worse. But, in this case, each and every time the @value variable is changed, you’re going to get a different execution plan. That’s because the full text of the query is used to determine if the existing plan will work for the new query and changing the @value variable will result in a change to that text.

However, there is another way to build and execute ad hoc TSQL. Done correctly, it will work exactly the same way as stored procedures, including plan reuse. This method is known as a parameterized query. There ways to do this through OLEDB & ODBC and there’s a way to do it through TSQL, sp_executesql. Instead of the query above, let’s examine this query:

SET @value = 43668
SET @sql = 'SELECT * FROM Sales.SalesOrderHeader AS soh ';
SET @sql = @sql + 'JOIN Sales.SalesOrderDetail AS sod ';
SET @sql = @sql + 'ON soh.SalesOrderID = sod.SalesOrderID ';
SET @sql = @sql + 'WHERE soh.SalesOrderID = @internalvalue'
SET @parm = '@internalvalue int'
EXEC sp_executesql @sql, @parm, @internalvalue = @value;

Using sp_executesql you can define parameters and put them into the query. Now, when this query is called again, you’ll see that the execution plan gets reused. As part of the comparison, I’ll also create this procedure:

(@value INT)
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = @value;

To test these queries, we’ll need to capture some trace events. Here are the ones I’ll capture:

  • SQL:BatchCompleted
  • SP:CacheMiss
  • SP:CacheHit
  • SP:CacheInsert

When all three queries are run, changing the parameter passed to @value, here is the resulting trace event output:


From the top, I ran each query in order as listed here in the article, changing the value passed once each time I called query. You first see a cache miss for the pure ad hoc query. It inserts to the cache and finishes the batch. Then, the next ad hoc query has a cache miss, a cache insert, and completes it’s batch. Proving that ad hoc queries with only a small change will restult in multiple execution plans.

Then we move on to the sp_executesql queries. They start out the same way, with a cache miss and an insert before the batch is completed. Note, the cache insert is for a different set of code than is immediately visible based on what we’ve provided. The TextData column starts with “(@internalvalue int)…” and procedes to show the rest of the query. This is the use of sp_executesql at work. It’s creating a parameterized query, effectively the same thing as a stored procedure. Then, with the second call to sp_executesql, we see a cache miss followed by a cache hit. This is the classic pattern for a successful call to the plan cache.

If you don’t trust that, the last two batches are the stored procedure. You can see that the first call to the procedure results in the same cache miss followed by a cache insert that every other first call had. The second call to the stored procedure had a cache miss followed by a cache hit. The same as the second, ad hoc sql, call to sp_executesql.

That, my friends, is plan reuse in action from an ad hoc query. Myth 1/2 Busted. Or maybe: Myth “It Depends” Busted.

Permalink 14 Comments

Free Stuff and a SQL Server Standard Update

October 2, 2009 at 5:11 pm (PASS, sql server standard, SQLServerPedia Syndication) (, )

Great news. We’ve managed to get one article all the way through the process. We’ll have our first publication out within a couple of weeks. We might even get a second out at the PASS Summit. We’re working through the final details on contracts, author payments and the layout of the magazine, including cover art. That brings up a question. The intent is to publish high quality articles by great authors (and believe me, we’ve got exactly that coming down the pike). In order to reflect this, instead of just an HTML layout, we’re going with Adobe Acrobat so that we can get that magazine feel, but online. My question… We’re trying to decide what to do with cover art. Our options are:

  1. Some type of stylish picture, say, stamps or coins, or maybe pictures of tables to show off the idea of a database’s tables… you know art
  2. A picture of the author. Personally, I wouldn’t wish my own photograph on someone at the scale we’re looking at and I’m pretty sure others won’t either, but it’s a possibility. It’d look a bit like the Wrox books.
  3. Just plain old, boring, text. I really don’t like this idea. I don’t think it’s in keeping with the idea of a “magazine” even though we’re doing it all online. Still, it’s an option
  4. Something I haven’t thought of

I’m putting the question out. Do you like those Reilly books that have some non-sequitor line drawing on them? How about the Simple-Talk books, all of which (and I didn’t know this until this morning and I have a book published by these guys) have a picture of a gate, for Red-Gate? Simple books like Apress with an abstract graphic & some text?

I’m fishing for ideas here. We’ve got a few days. Mind you, I’ll make a decision with or without your comments, but I’d really like to hear what people think. If we use your idea (assuming it’s not just “I like #1”) I’ll ship you out a copy of my performance tuning book. If we pick one of our own ideas, I’ll randomly draw from one of the commenters. Either way, you’ve got a shot at the book.

Oh, and while I have you reading, I still need more abstracts. We have to feed the beast.

Permalink 15 Comments

« Previous page