Michael J. Swart

March 28, 2012

Sending Query Results to Others

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

I think most of you have all been here before. You want to know the results of a query, but you have to get it from a colleague through email or some other proxy:

Jerry Maguire is asked to "Show Me The Data"

So how do you usually do it? How do you ask someone to send you the results of a query? There’s a million ways to answer that question and I’ve discovered that none of them are perfect. Depending on the situation, different ways are better than others. So let’s pick a situation and explore the best way to ask for those query results.

The Situation

The facts were these…

  • Goal: I want the results of the following query. It retrieves the top 20 cached queries and plans based on total consumed cpu.
  • I have to rely on my friend to run the query for me.
  • My friend is not a database administrator but is comfortable around SQL Server Management Studio
  • I want to minimize or simplify instructions.
  • I don’t want any truncated results.
  • I want to assure my friend that these queries are safe.
  • For this situation, I’m not worried how my friend gets the data to me (email, ftp, etc…). I only care that my friend is able to persist the data to some file.

And to complete the situation, here’s that query I’m curious about…

-- TOP 20 queries (by CPU)
SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC

Notice that the results of this query contains xml, and that xml might contain commas, spaces, tabs, newlines and all the typical delimiters. That makes my job trickier. So now I’m going to list a number of methods and their pros and cons. I can think of a number of methods off the top of my head.

Method 1: Cut and Paste into Excel

This is most people’s usual method and it works in 90% of all cases because Excel is so versatile. However in this particular case, the whitespace in the query plans and the sql text mess up some of the rows and formatting, one field in each row. I get something like this, ugh:

A jumbled Excel spreadsheet

But there’s a better way using Excel. It’s not too hard to use the Data Connection Wizard. So it’s not too much work to guide my friend through it either. If I can, then this is a really useful nice way to pass around data. That wizard is accessed like this:

Using Excel to query the data

Method 2: Store Results As Tables in a Temporary DB and Back It Up

That can be done with a single T-SQL script which my friend should be able to run no problem. The script looks something like this:

create database myresults
go
-- TOP 20 queries (by CPU)
SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
INTO myresults.dbo.myresultset
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC
go
 
backup database myresults to disk = 'C:\Users\Michael Swart\Desktop\Output.bak'
go
 
drop database myresults
go

The best part of this is that the data is backed up perfectly, the data captures all the data with no truncation. Xml fields are stored as xml fields and once restored, it’s easy to access the resultset exactly as it was on the target computer.

The bad news is that although it should be fairly safe, it’s not as easy to assure my friend that the query above changes nothing. It does! It creates a whole database and backs it up. It then drops that database.

Hang on a second, if my friend can use this method, that means my friend (the non-dba) is authorized to drop databases. Yikes! What’s up with that?

Method 3: Using the Export Data Wizard

It seems like SQL Server’s “Export Data” wizard was just built for this job. That can be accessed here:

SSMS's Export Data...

But I don’t recommend it, not one bit. It launches a wizard which takes me through a set of choices, and no matter what I choose, it always seems to choke on the xml column.

It’s clear that this Export Data Wizard is using SQL Server Integration Services (SSIS) under the covers. How do I know it’s SSIS under the covers? Well if the clumsy data type handling weren’t a giveaway (zing!), the wizard can save the job I specified as an SSIS package.

The SSIS shows through too much to recommend to my friend, who has no dreams of becoming a B.I. Maestro.

So it seems like this method is only appropriate for those situations when cutting and pasting to Excel would be just as appropriate. And it’s kind of hard to write instructions simpler than “Cut and paste into Excel”.

Method 4: Use Management Studio’s Results-To-File

Most people use the query window’s default setting, Results-To-Grid. Sometimes there’s a use for Results-To-Text (like when using sp_helptext for example). But what about Results-To-File? I can set that using the Query window’s context menu here:

The context menu used to access "results to file"

I can also enable results to file with Ctrl+Shift+F. I had to look that up though. There’s a reason I haven’t learned that shortcut by heart. It’s because results-to-file doesn’t work very well. As far as I can tell, it’s like specifying results-to-text and then pasting that into a text file.

The biggest problem here is that depending on the results, the data gets truncated easily and the newlines and tabs in the data get mixed up with the rest of the formatting. By the way, I’m also out of luck (and for the same reason) if I use SSMS to save grid results.

Method 5: Use BCP, along with SQLCMD mode

If I can instruct my friend to use a query window in SQLCMD mode, then I can write a script which will get a bcp script to export data. Enable SQLCMD mode by choosing it from the Query menu in SSMS:

Selecting SQLCMD Mode from the Query Menu

And the script I would have my friend run looks like this:

!!bcp "Select 'query omitted' as [BigLongQuery]" queryout "c:\temp\desktop\output5.dat" -SSWARTLAPTOP -T -n

which I could then get back on my side using bcp or using OPENROWSET.

This method is okay. It’s kind of awkward (and any instructions to my friend will be equally as awkward) and it always takes some work to get the syntax just right. But BCP is kind of like Rob Lowe, he hasn’t changed in years and he’s really not that bad.

Method 6: Output the Whole Thing Using FOR XML PATH

Hey! Now I’m getting somewhere! XML was built for this kind of thing. All I do is take the query, and append FOR XML PATH to it and send the query to my friend saying “Run this and paste the results into notepad.” Here’s the whole query:

SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC
FOR XML PATH

And the results that are sent back are immediately accessible without any processing.

I think this method works best for this particular situation. It hits my goals perfectly. Now if you dear reader want to use this method in your situation, there’s just some extra caveats:

  • SSMS restricts XML data results to 2 megabytes, although that’s configurable.
  • If you query binary fields (say query plan handles for example), then you might be surprised if the results come back in base64 encoding rather than hex which is a bit of a pain.

Other methods

Powershell I know you powershell pros are eager to give a one line script that does everything here. So lets hear it the comments! I know that powershell treats sets of objects (rather than unix’s cshell which deals with text). So I’m curious how well it serializes objects to a file.

SQL Server Integration Services I haven’t forgotten about SSIS either, it’s the tool that was built for moving data from here to there in some format (any here, any there, any format). But I just couldn’t get the instructions simple enough for this scenario.

March 19, 2012

I’ll Draw Your Portrait

Filed under: Miscelleaneous SQL — Tags: , — Michael J. Swart @ 6:19 pm

Update April 10, 2012: Results are here!

Hey I’m back after taking a “me” week. And you can look forward to some real technical SQL stuff very soon. But first a contest!

A lot of people have mentioned that they like my cartoon illustrations. And those are great comments. I have a lot of fun drawing them! For example here are a couple of the illustrations that I’ve done for some friends:

Friend 1:
Friend 1

Friend 2:
Friend 2

I’ll Draw You Too

So I’ll draw you for first prize in a contest! Take a look at the faces of my friends above, guess their names and fill in the form below. I’m accepting submissions until Friday, March 23 at 12:00 noon, Eastern time. I’ll select one random winner from correct responses (spelling counts!) and if you’re that winner, I’ll contact you to do a picture for you (or even a loved one).

Thanks to everyone who submitted! I’ll announce the winner shortly

Good luck!

March 7, 2012

SQL Server 2012 Launches Today

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 1:08 am

Question: What just arrived today, has been months in the making and makes me super-excited today?
Answer: It’s my new nephew! I’m an uncle again as of early early this morning!
Question: Okay, what else?
Answer: What? That’s not enough? okay… (Original article follows)

Today is March 7th, 2012 and SQL Server 2012 launches today. Woohoo!

Here’s the scoop:

  • We’ve had the Community Technology Previews (CTPs) to mess around with for a while now.
  • We’ve had a Release Candidate (RC0) for a little bit.
  • Today the product launches.
  • April 1st, (no kidding) the product is released to general availability.
  • But don’t wait until then. Today you can download the RTM evaluation copy.

Also

  • SQL Server Data Tools is now RTW! More on that below.

The Virtual Launch

... it's just an illustration of one

Microsoft is marking the event with a virtual launch here. An online event that lets you watch them demo what they’ve been working on since the last release (And unless you’re a BI professional, that means since 2008)

Personally What I’m Most Excited About: SSDT

The code I work with gets deployed to a large number of systems which run on multiple versions of SQL Server. In order to avoid maintaining multiple code-lines, I can’t make use of the new features until we de-support all the older versions.

For example, I can’t use the IIF function (new in 2012) if I’m worried that the code could be deployed to a 2008 instance somewhere. So there’s going to be some waiting for me and probably some of you …

Not so fast, there are still a few things we can use today. And I want to focus on one of them. It’s called SQL Server Data Tools (SSDT) and it was Released To Web (RTW) today.

What’s SSDT do? It’s a free development environment. It lets you develop SQL for any version of SQL Server (including SQL Azure) whether you’re connected or not. It supports Intellisense (and as far as I can tell, even against 2005). It does schema comparisons and data comparisons.

The clincher for me is that the table designer is the first one I’ve ever preferred over a plain old text editor.  It’s that slick. Check it out.

 

Powered by WordPress