sql – Page 3 – downshiftdata

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:

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

A-ha Moments

I’m preparing for my session at SQL Saturday #358 in Albuquerque next week, and something finally clicked. I’ll be adding another demo to my section on the differences between EXEC and sp_executesql now. Want to find out what it is? Then come to Albuquerque!

Gotta love those A-ha moments!

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.

Viewing the Plan Cache

I’m working on a script and wanted to know how SQL was caching the query plan. So I wrote this little snippet. The only real value I’m adding here is getting the XQuery correct to pull out the SQL itself.

DBCC FREEPROCCACHE;
-- Run your query here
SELECT
        qp.query_plan,
        qp.query_plan.value(N'(//@StatementText)[1]', N'NVARCHAR(MAX)')
    FROM sys.dm_exec_cached_plans AS cp
        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp;

Of course, I don’t recommend that DBCC in a production environment. But in a dev environment, it makes things easy.

Pages Used

One of the items in my toolbox is this simple query that returns the number of pages used by different tables in the database. The nice thing is that it gives one simple number that includes indexes, lob data – basically the whole table.

SELECT TOP 20
    s.name,
    o.name,
    SUM(ddps.used_page_count)
  FROM sys.dm_db_partition_stats AS ddps
    INNER JOIN sys.objects AS o
      ON ddps.object_id = o.object_id
    INNER JOIN sys.schemas AS s
      ON o.schema_id = s.schema_id
  GROUP BY s.name, o.name
  ORDER BY SUM(ddps.used_page_count) DESC;

I just used this on a database today to find that there are some leftover tables from a migration a while back that are eating up a considerable amount of space. Time to run some DROP TABLE scripts!

/* Why didn’t I think of this years ago? /*

So, I attended the PASS Summit last week in Seattle. If you’ve never been, I highly recommend it. Especially if it’s in Seattle. Yes, the character of the town is basically dark, rainy, and uphill both ways. But the coffee is good, the seafood is better (especially for a midwesterner like me), and the place is just simply one of the nicer downtowns I’ve seen.

But the PASS Summit itself is also worth it, Seattle or not. You pick up a lot of great stuff, much of it at a very deep level. Adam Machanic’s pre-con on Tuesday, for instance, was such a deep dive into parallelism that my eyes were glazed over by the end of it.

And then there are the little things. The stuff that make you want to kick yourself for not thinking of it before.

I write a lot of dynamic SQL. I have tons of stored procedures that include this little gem:

DECLARE @stmt NVARCHAR(MAX) = N'';

And then hundreds of lines of conditionals and string concatenation and parameter substitution, followed by sp_executesql.

But what had never occurred to me was this:

DECLARE @stmt NVARCHAR(MAX) = N'/* dbo.NameOfCallingStoredProcedure */ ';

Duh.

And now when it’s six months later, and I get a call from a DBA who’s trying to troubleshoot a query plan that’s gone sideways, and neither of us know or remember what’s actually generating that query.

It’s the little things, sometimes, that make the big difference.

By the way, this came from Jeremiah Peschka’s AD-302 session, Dynamic SQL: Build Fast, Flexible Queries.