SELECT TOP 1 * FROM PASS.Summit2015;

I’ve been uncharacteristically quiet during this particular Summit. I picked up a cold on the way out there, which made the week a bit different than I was expecting. Still, I always take away quite a lot each time I go, both big and small, and this year was no exception. I’d like to share one of the small things here.

At some point, we’re all running ad hoc queries in a production or otherwise significant environment. We’re doing root cause analysis, looking for bad data, trying to figure out why something is slow, whatever. Our ad hoc queries get rather large sometimes, as we’re pulling in information from all sorts of places. Often, we have several stored away in a toolbox for these occasions, our “go-to” scripts when we get that inevitable call in the middle of the night.

Here’s one small change to those queries that will make one small difference. Admittedly, it’s not much, but it could add up, especially if you’re in a large shop with a lot of people who are doing the same thing. If we all changed our ad hoc to include this, we might actually have an impact. So, what’s the change? Throw in “OPTION (RECOMPILE)” at the end of all your ad hoc statements.

What does this buy us? Well, basically, it keeps those one-off ad hoc queries from bloating the plan cache. Does it really matter? Not much, in the grand scheme of things, except that I know some of the queries in my toolbox are quite lengthy. Why have SQL Server cache the plans for those?

By the way, you can also enable the Optimize for Ad Hoc Workloads server configuration option. This will save you nearly as much, at least until you hit F5 the second time.

Anyway, I might share some of the other stuff I picked up from this year’s Summit. Just not my cold, I hope. But the one thing I always share is my advice that you attend. If you’re a data-centric developer like me, definitely go. It’s excellent content by a thriving community in a beautiful city. I’m already looking forward to next year.

SELECT INTO

I just came across a stored procedure that does something like this:


DECLARE @stmt NVARCHAR(MAX);
CREATE TABLE #foo (col1 INT, col2 INT, col3 INT);
SELECT @stmt = N'SELECT col1, col2, col3 INTO #foo FROM bar;';
EXEC sp_executesql @stmt;

The incorrect assumption that the developer made was that the first #foo (the one created with CREATE TABLE) was the same as the second #foo (the one referenced in the INTO clause). But that’s not the case. To prove it, try this slight variation:


DECLARE @stmt NVARCHAR(MAX);
CREATE TABLE #foo (name NVARCHAR(2));
SELECT @stmt = N'SELECT TOP 5 name INTO #foo FROM sys.objects; SELECT * FROM #foo;';
EXEC (@stmt);

If the @stmt was really using the first #foo, then what would you see? The first two characters from name from 5 rows from sys.objects, right? No, actually you’d see Msg 8152, “String or binary data would be truncated.”. But that’s beside the point. The point is that it isn’t using that first #foo. It’s creating a second one. And it’s getting away with that because it’s running within EXEC. Take out the use of @stmt and SQL will complain to you when you try the “SELECT … INTO #foo” that #foo already exists.

Also note that the original used sp_executesql, but that my second query used only EXEC. That was to prove that it was EXEC alone, not sp_executesql, that was the differentiator.

SQL Saturday #402 – Indianapolis

I’m once again presenting a session at a SQL Saturday, this time in my own backyard. SQL Saturday #402 is here in Indianapolis this Saturday. If you’re in the Indy area and spend any significant amount of time with SQL Server on a regular basis, I really encourage you to go. You can learn a lot in a short amount of time, make some good contacts in the industry, learn about great products that can make your job easier – and, if you’re luckier than me, perhaps even win a prize or two. They also have Friday “pre-cons” with room still available, if you want to take it a step further.

My session, Playing in Traffic, is the same one I presented in Columbus. I’m still including the same topics, but I’ve taken the feedback and retooled it somewhat. If you do attend, I can tell you that all of the presenters appreciate your feedback, especially your criticism. We’re all trying to improve, and what you share now can make a difference later.

Also, while I’m not fortunate enough to be presenting in Seattle, I’m definitely attending the Summit this year. If you’re wondering whether or not to attend, just ask anyone who’s been. It’s definitely a valuable experience for SQL Server professionals.

@@ROWCOUNT Strikes Again

Just when I thought I’d been thwarted the last time by @@ROWCOUNT, along comes this gem:

begin try
select top 10 * from sys.objects;
end try
begin catch
select top 20 * from sys.objects;
end catch;
select @@rowcount;

Yup, it returns 0. It seems that even a try/catch block will stomp on the results. Maybe I’ll learn one of these days.

SQL Saturday #421 – Columbus

They say the only three numbers in computer science are 0, 1, and N. Well, I’ll be at the SQL Saturday in Columbus, Ohio, on July 11th, giving a session entitled Playing in Traffic. It’s really a talk about keeping the wheels turning as a SQL developer when N gets really big. It’s the last session of the day, so expect things to be a bit laid back and mildly entertaining, but hopefully still very educational. See you in Columbus!

I <3 SET STATISTICS IO ON

Once upon a time, a senior DBA at the Salesforce Marketing Cloud had pointed out a very small performance tweak to me. I was curious about whether or not it still held true in SQL Server 2014, so I wrote up the sample below. What’s interesting to me is that a cursory examination of the Execution Plan doesn’t reveal the difference. Of course, if you dig deep enough, you’ll find it there. But it’s not obvious.

On the other hand, by using SET STATISTICS IO ON, I can easily see the difference. Hopefully, this isn’t some new earth-shattering development for you so much as a simple reminder to make use of this statistical information.


IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'Person.Person') AND name = N'IX_Person_PersonType_EmailPromotion')
CREATE NONCLUSTERED INDEX IX_Person_PersonType_EmailPromotion ON Person.Person (PersonType, EmailPromotion);

SET STATISTICS IO ON; SET NOCOUNT ON;
SELECT BusinessEntityID, EmailPromotion
FROM Person.Person
WHERE PersonType = N'SC'
AND EmailPromotion IN (1, 2);
SELECT BusinessEntityID, EmailPromotion
FROM Person.Person
WHERE PersonType = N'SC'
AND EmailPromotion BETWEEN 1 AND 2;
SET STATISTICS IO OFF;

DROP INDEX IX_Person_PersonType_EmailPromotion ON Person.Person;


Table 'Person'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Person'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Wondering what exactly these results mean? Look here: https://msdn.microsoft.com/en-us/library/ms184361.aspx

Wondering what the tweak was? Look closely at the IN versus the BETWEEN, and then at the number of scans and reads.

Here We Go Again

I’ll be presenting my session from SQL Saturday #358 at this month’s IndyPASS meeting next Tuesday, 3/17. I’m looking forward to all the heckling I’ll be getting from the Salesforce Marketing Cloud‘s performance DBA’s that will be in attendance.

Meeting attendees will also get an added bonus. I have a voucher for a free SQL Admin Toolset from Idera that will be part of the prize drawing. This was courtesy of Idera’s support of #358 in Albuquerque.

So, free pizza, the chance to heckle me, and the chance to win free software… what else could you ask for? See you Tuesday!

[n]varchar truncation

I was very happy with the response to my SQL Saturday session in Albuquerque. To those who attended and provided me with feedback, thank you.

One of the items I covered led to this tweet from @coffegrl:

thrilled! I was just able to use something i learned at #sqlsatabq. that's ROI for you :)

Which just made my day yesterday. Anyway, here’s what she was talking about. Have a look at this script:


DECLARE @stmt NVARCHAR(MAX);
SELECT @stmt = N'foo' + REPLICATE(N'A', 4000) + N'bar';
SELECT LEN(@stmt), LEFT(@stmt, 5), RIGHT(@stmt, 5);

What happens when you run it? Ideally, the results should be 4006, ‘fooAA’ and ‘AAbar’. But that’s not the case. Even though @stmt is NVARCHAR(MAX), the four components of the string concatenation (‘foo’, ‘A’, the results of REPLICATE, and ‘bar’) are not. As a result, the value that is constructed is, in this case, an NVARCHAR(4000), which is then written to @stmt.

Here’s one way to fix it:


DECLARE @stmt NVARCHAR(MAX);
SELECT @stmt = N'foo' + REPLICATE(CAST(N'A' AS NVARCHAR(MAX)), 4000) + N'bar';
SELECT LEN(@stmt), LEFT(@stmt, 5), RIGHT(@stmt, 5);

And another:


DECLARE @stmt NVARCHAR(MAX);
SELECT @stmt = N'foo' + CAST(REPLICATE(N'A', 4000) AS NVARCHAR(MAX)) + N'bar';
SELECT LEN(@stmt), LEFT(@stmt, 5), RIGHT(@stmt, 5);

And another:


DECLARE @stmt NVARCHAR(MAX);
SELECT @stmt = CAST(N'foo' AS NVARCHAR(MAX)) + REPLICATE(N'A', 4000) + N'bar';
SELECT LEN(@stmt), LEFT(@stmt, 5), RIGHT(@stmt, 5);

And the same is true for VARCHAR (assuming you replace the 4000’s with 8000’s). So, the lesson is, when concatenating large strings in SQL be careful that it doesn’t trunca

Albuquerque SQL Saturday

I’m excited to announce that I’ll be presenting “Fast and Furious Dynamic SQL” at SQL Saturday #358 in Albuquerque on February 7th, 2015. This will be my first time as a PASS presenter, so please save your best heckling and rotten vegetables for the occasion.

The session will cover various tips and tricks that are generally related to running sp_executesql from within a stored procedure. It will focus on performance, security, and maintainability.