14Jan 2015 by Grant Fritchey 13 Comments

Common Table Expressions Are Not Tables

T-SQL
There's power in naming things. Supposedly some types of magic are even based on knowing the correct names for things. The name for the T-SQL clause Common Table Expression (CTE) is actually pretty accurate. It's an expression that looks like a table and can be used in common across the entire query (at least I think that's what the common part refers to). But note, I didn't say it was a table. It's not. It's an expression. If you look at the T-SQL definition at the link, it refers to a "temporary" result set. Now, to a lot of people, that means table. But it isn't. Let's look at this in more detail. Here's a query that defines a simple CTE and then uses it to query the date in the…
Read More
23Jul 2010 by Grant Fritchey 6 Comments

SQL University: Introduction to Indexes, Part the Third

SQL Server, T-SQL
Nice to see most of you have managed to fight your way through the shoggoths outside to attend another lecture at the Miskatonic branch of SQL University. This will be the third and final part of the introduction to indexes lecture. Please, if you're going mad, step out into the hall. Our previous two lectures introduced the concept of indexes and then talked about two types of indexes, clustered and nonclustered. This lecture will cover the concept of statistics as they relate to indexes. If you followed the previous lecture then you know that indexes are stored in a Balanced Tree or B-Tree structure. You know that this storage mechanism is intended to provide fast retrieval of data. But, how can the query engine inside SQL Server know which index…
Read More
21Oct 2009 by Grant Fritchey 1 Comment

Characters

SQL Server, T-SQL, Tools
No, I'm not talking about a Dickens novel. I'm talking about the number of characters in a string. I had a painful time recently because of the word "characters."  If you take a look at the dynamic management view sys.dm_exec_sql_text you can get the queries that have been run on your system that are still in the cache. It's a great utility. Better still, you can get specific statements from the code that are actively running through sys.dm_exec_requests or ones that have run through sys.dm_exec_query_stats. To do this is very simple. Each of these DMV's has a pair of columns, statement_start_offset and statement_end_offset. These columns, and I'm quoting directly from books online measure the "number of character" offset from the beginning of the SQL string and from the end of…
Read More
13Oct 2009 by Grant Fritchey 5 Comments

Table Variables Are Only in Memory: Fact or Myth

SQL Server, T-SQL
I keep seeing these concepts that have long been disproven, posted again and again on newsgroups as if they were valid information. The latest? Table variables are better because they're only in memory where as temporary tables write to the disk through tempdb. This one is abjectly wrong. I'm not even going to hedge with "it depends." From a macro point of view, there are only a few differences between temporary tables and table variables, statistics being the biggest. Temporary tables have 'em and table variables don't. Other than that, both will reside completely in memory or will swap out to the disk through tempdb, depending on their size. Some of the minor differences, and why you might want to use table variables over temporary tables, table variables won't cause a statement recompile while temporary tables will, table…
Read More
21Sep 2009 by Grant Fritchey 6 Comments

Identifying Frequently Running Queries

SQL Server, T-SQL
It's not enough to look at the longest running query. What if you have two queries, one runs for 5 minutes and the other runs for 5 seconds. Which do you tune? The first one, of course. But, let's add a little information to our thought experiment. The one that runs for 5 minutes is called 2AM, your local time, when there are almost no users on the system and the one that runs for 5 seconds is called 40 times a minute all day long. Now, which one do you tune? That's right, suddenly that 5 second query takes on a HUGE significance based on how often it's called. But how do you know how often a query is called? The easiest way to gather that information is not…
Read More
15Sep 2009 by Grant Fritchey 2 Comments

More Free Training

SQL Server, T-SQL
Quest Connect 2009, taking place in October 21 for 24 hours, looks like it's going to have 64 different sessions, live and recorded, by a variety of the names in the industry. It's another chance to dig in and learn the details on a variety of topics from some of the top names in the business. Can you say Tom LaRock? How about Tim Ford? I know you want to hear from Brent Ozar. Those are just some of the featured speakers. There are a whole slew of others, it's worth pursuing, and did I mention, the price is right. I recorded a session for them last night. It's on the basics of understanding execution plans.
Read More
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
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
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