15Sep 2009 by Grant Fritchey 30 Comments

No Join Predicate

SQL Server, T-SQL
You could be looking at an execution plan on a query and see this message: Warning, No Join Predicate. With a very few exceptions (very few), that's not a warning, like "Mind the gap" or "Your Mileage May Vary" or "Never fight a land war in Asia." No. It's a screaming shout saying "BY ALL THE GODS IN VALHALA, YOU DON'T HAVE ANY JOIN PREDICATES IN THIS QUERY!" Seriously, that's exactly what it says. But, you might be asking yourself, that's what it says, but what does it mean? Please allow me to explain. The tables in your system, whether it's a classic BI star schema, or a highly normalized OLTP system, or even (shudder) ORM objects, are related to one another. Usually they're related through the use of primary…
Read More
11Sep 2009 by Grant Fritchey No Comments

LaRock on Monitoring

SQL Server, T-SQL
Since I just spent a bit more than half of my 24 Hours of PASS presentation on tuning queries talking about monitoring performance, you could probably tell that I think that the two are inextricably linked. I'm not alone. Tom LaRock has put a post on why it's important to understand the extent of your problem prior to attempting to fix it. It's absolutely spot-on and a must read. Remember, if someone says performance on a system you manage is slow, you have to ask, as compared to what? You need to understand what "normal" performance is on your system in order to arrive at the ability to identify a particular process as performing in an abnormal manner. That's not to say that you couldn't tune a query in complete…
Read More
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