09Sep 2009 by Grant Fritchey 8 Comments

Spools in Execution Plans

SQL Server, T-SQL
I got the question the other day, when are you likely to see a spool in an execution plan? Easy, whenever SQL Server needs to walk through the data multiple times, usually in JOIN operations... Yeah, well, once again, my flip answers are not quite the entire story. Spool operations are temporary storage of the data for later reuse in a query plan. There are two types of spool operations, eager spool and lazy spool. A spool is basically a temporary table created within the execution of the query that is used when it's likely that data will be needed again, and again during the execution of the query. This is not an explicit #temp temporary table, but a work table for operations within the processing necessary for a given query's…
Read More
02Sep 2009 by Grant Fritchey 3 Comments

24 Hours of PASS

PASS, SQL Server, T-SQL
This was a pretty cool event. The advertising leading up to it seemed to really cover the community very well. There was even a link to 24 Hours of PASS in my team's local web site. Unfortunately, I'm not an iron man like Tom LaRock, watching all 24 hours. I only hit a few sessions. The first session I hit was Allen White's PowerShell for SQL Server. I have attended a couple of  his presentations at the PASS Summit. I think he's done a great job of gleaning better and better examples out of his presentations. This one was good. I loved the way he put together the backup processes for automating backups. It looked good, took into account common issues, it just worked. I was really happy I got…
Read More
21Aug 2009 by Grant Fritchey 15 Comments

What happens when you use WITH RECOMPILE

SQL Server, T-SQL
I saw this question and my immediate thought was "Well, duh, the execution plan is recreated by a recompile." But, someone a bit smarter than myself suggested that, in fact, no plan was stored in cache, forcing a new plan to be created each time the query was run. So, which is it? Does a plan get added to cache and then removed every time the procedure is called, or do you get nothing in cache and the "recompile" is actually a compile every time? As Mr. Owl says, let's find out. I have a small script that looks like this: CREATE TABLE [dbo].[Test]( [col] [varchar] (10) NULL ); CREATE TABLE [dbo].[Test2] ( [col] VARCHAR(10) NULL ); INSERT INTO dbo.Test (col) VALUES ('Val1'), ('Val2'),   ('Val3') ; INSERT INTO dbo.Test2…
Read More
20Aug 2009 by Grant Fritchey 3 Comments

Two Book Recommendations

PowerShell, SQL Server
I have two new books that I can heartily recommend. This isn't a review of either book since I've only just started reading them. However, I've used each to solve a couple of small problems within a day of having them on the shelf. To me, that proves their worth. First up is Michael Coles new book "Pro SQL Server 2008 XML." I'm still a bit jittery with XQuery... OK, I have a hard time writing XQuery and I have to look up how to do every step and I still get things wrong. Anyway... There are two entire chapters devoted to how to use XQuery and an appendix with an XQuery & XPath reference. Great stuff. Look for an extended review several weeks from now after I've had a chance to…
Read More
12Aug 2009 by Grant Fritchey 3 Comments

Excellent Blog List

SQL Server, T-SQL
For the one or two you that are not reading Buck Woody's blog (and why aren't you), you may not have seen that he's posted a recommended reading list of the blogs that he reads on a regular basis. This list must be reviewed by you, now. Go on, I'll wait here till you get back.
Read More
12Aug 2009 by Grant Fritchey 4 Comments

SQL Quiz 5: SANs Mirroring

SQL Server
My turn to answer Chris Shaw's questions "Do I feel I have a reliable SAN solution?" and "Describe Database Mirroring for the layman?" Thanks Tim. I've a feeling you're going to be disappointed. SAN: Hell, how should I know? Want to talk about a black box? Our SAN, as far as I'm aware, is run by a group of crack-shot ninja assassins and powered by pureed virgin unicorn. This strange mix of esoteric skill, magic and blood is evidently able to provide us with just about perfect up time. Once, one of the ninja's must have gone rogue (speculation) and had to be put down by his brother ninjas because we had a drive disappear on the cluster server. It took us a couple of hours, but it recovered as if it…
Read More
11Aug 2009 by Grant Fritchey 3 Comments

Some Statement Start Times!

SQL Server, T-SQL
I thought I had captured statement start times within the DMV sys.dm_exec_sessions. I was absolutely wrong. However, this has sparked a bit of discussion, as you can see in the link to my correction. John Vanda (I couldn't find a blog by him, but I think this is his LinkedIn profile), suggested possibly joining between the sys.dm_exec_requests and sys.dm_tran_active_transactions through the transaction_id available in the requests DMV. He ran a series of tests that showed it to be promising. I re-ran my original test, a few long-running statements within a batch; BACKUP, CHECKDB, etc.. This time I captured the transaction information along with the other DMV's. The results were interesting. Rather than pull together any particular set of data as I was testing, I just collected all three DMV's once…
Read More
31Jul 2009 by Grant Fritchey 13 Comments

No really. When did this statement start?

SQL Server
I thought I had an interesting answer to the question. Unfortunately Adam Machanic, who has been working in this specific area quite a bit longer than I have, and, let's face it, is a bit smarter, pointed out (in the comments) the possibility that I didn't have this quite right. I ran all his tests and sure enough, it wasn't working the same way that I saw it work. First, I tried modifying his query so that it ran the SELECT statements from the DMV's between the operations, but this didn't change the results, start_time and last_request_start_time were the same. From a scientific stand-point, if no one can replicate your results, the experiment failed. So I went back and tried again. To be sure I got things right and wasn't, somehow,…
Read More
28Jul 2009 by Grant Fritchey 7 Comments

When did this statement start?

SQL Server, T-SQL
UPDATE: This post is incorrect. Adam nailed it in the comments. I explain my mistake here. A question came up over at SQL Server Central where someone was wondering if it was possible to know when a given statement within a batch started. Immediately I thought, oh yeah, that's easy, use the sys.dm_exec_requests dynamic management view (DMV). Done. Wrong. The original poster pointed out that I had assumed that the values present in the DMV represented statement level values, but they show the batch. While the DMV shows a start_time, that time is the start of the current batch, not the statement within the batch. Now the question was, where else might I get this data? I next tried sys.dm_exec_sessions because it has the last_request_start_time value. Sure enough this worked. Don't…
Read More
20Jul 2009 by Grant Fritchey No Comments

MS Field Engineer’s on Performance Troubleshooting

SQL Server, T-SQL
Do you want to get a glimpse into how the Microsoft Field Engineers would go about troubleshooting performance issues on your server? Then go and read this blog entry by Denzil Ribeiro. Not only is this an excellent how-to on troubleshooting performance problems, but Mr. Ribeiro provides multiple links that describe the concepts he's dealing with further, making it a great piece of documentation. The MS Field Engineer blog is not terribly active, but what gets posted there is worth reading. If you don't have it on your feed list, you should.
Read More