13Sep 2013 by Grant Fritchey 2 Comments

Are Foreign Keys Better Than Indexes?

SQL Server, T-SQL
When I first saw this question I thought to myself, "Self. Don't you think that's comparing apples to hammers? Yes, Self, I'm pretty sure it is. Good, I thought so too, self. Yeah, me too." After rebooting because of the runaway iterations on that thought, I had another, "Well... hold on there self. Both types of objects, while pretty different, are taken into account by the query optimizer." I then had to admit to myself that I had a point. So the question remains, are foreign keys better than indexes? As my first self said, these are different objects and they fulfill different purposes within SQL Server. My second self wants to point out that when you're dealing with functional objects within SQL Server, it's a bad habit to start…
Read More
11Sep 2013 by Grant Fritchey 2 Comments

Execution Plan Cost Estimates

SQL Server, T-SQL
It's been emphasized over and over that the costs of operations within an execution plan, and the estimated costs of the plan themselves are, in fact, estimates. But it goes further than that. The estimated values are based on statistics, or the lack thereof. Statistics themselves are also estimates. This means that the costs you're seeing are extrapolations based on extrapolations. So, you should just ignore those values and move on, right? Wrong. In order to understand how the optimizer is choosing to put together an execution plan for your query so that you can use that understanding to then make intelligent choices as to modifying the query or the structure of your database, you must use the values you have at hand. However, you must also understand where and…
Read More
05Sep 2013 by Grant Fritchey 7 Comments

sp_executesql, Parameters and Parameter Sniffing

SQL Server, T-SQL
I'm honestly not crazy about dynamic T-SQL within stored procedures. There are just a few too many opportunities to mess it up with dire circumstances to your server and your data. However, I absolutely recognize that dynamic T-SQL may be needed, and, in some situations, the best way to solve a problem. If you must use dynamic T-SQL, there are ways that are much more efficient than others. The very best thing you can do if you need to build dynamic strings to execute T-SQL in your stored procedures is use sp_executesql. The main reason I advocate for sp_executesql is because you can build out completely dynamic strings of T-SQL, but, you can still take advantage of parameters. Parameters help you avoid a chat with the parents of Bobby Tables…
Read More
28Aug 2013 by Grant Fritchey No Comments

Learn Query Tuning in Dallas

SQL Server, T-SQL
I am excited to be able to tell you about an all day seminar that I'll be putting on prior to the Dallas SQL Saturday #255. The seminar will be on November 1, 2013. It's called Query Performance Tuning in SQL Server. We're going to cover the topic from an understanding of the optimizer to collecting data using extended events to reading execution plans and then on to lots of standard problems and their solutions. If you sign up before September 21st you can get a substantial early-bird discount, so I'd jump on it. Also, seats are limited, so don't wait too long. Let's get together and talk query tuning.
Read More
25Mar 2013 by Grant Fritchey 5 Comments

Execution Plans in Azure SQL Database

Azure
Microsoft has stated pretty clearly that they're putting code on Azure first, ahead of the desktop. Which makes one wonder when we're going to start to see some of this cool stuff within SSMS. What cool stuff you ask? Well, let me explain. Let's start with a query: SELECT m.MovieName, msd.MovieStageName, st.StageTypeDesc FROM dbo.Movie AS m JOIN dbo.MovieStage AS ms ON m.MovieId = ms.MovieID JOIN dbo.MovieStageDefinition AS msd ON ms.MovieStageDefinitionId = msd.MovieStageDefinitionId JOIN dbo.StageType AS st ON msd.StageTypeId = st.StageTypeId WHERE m.MovieId = 42; When I run this on Windows Azure SQL Database (WASD) I get the following execution plan: Kind of weird, kind of useful, right? First thing new that I can do is zoom in using that slider bar you seen in the lower left and then graphical…
Read More
30Jan 2013 by Grant Fritchey 4 Comments

Azure SQL Database Execution Plan Differences

Azure, T-SQL
I've been exploring execution plans in Azure SQL Databases a lot lately. I'm getting a presentation together for  some upcoming SQL Saturday events (first one is SQL Saturday #177, Silicon Valley). If you scroll to the bottom of this previous post, I mentioned that there were clearly differences in the optimizer because queries against empty databases were generating different plans. I've loaded up the data in my database, both SQL Server and SQL Database, so I can compare real behaviors. Doing so, I found a fun difference, even though I was running the query and generating the plan from SQL Server Management Studio. Here's the property sheet from the SELECT operator for the query run against SQL Server: And here's the property sheet from the SELECT operator for the query…
Read More
14Jan 2013 by Grant Fritchey 9 Comments

Execution Plans on Azure SQL Database Portal

Azure
If you've been working with the Azure SQL Database portal, you know that you have a query window. Within the query window you can run queries against your server. You'll get back results, timing, everything you need to observe behavior within the system. What's that? You never heard of this? Let's figure it out real quick. Here's my portal to my Azure SQL Databases: [caption id="attachment_2296" align="alignnone" width="150"] Azure SQL Database Portal showing usage overview[/caption] Yes, all sorts of things you can do from here, but we're focused on the bottom of the screen at the Manage button. Clicking this button will open up the Management portal window, part of which you can see below: [caption id="attachment_2298" align="alignnone" width="150"] Part of the Azure SQL Management Portal showing some of what…
Read More
22Oct 2012 by Grant Fritchey 4 Comments

Clustered Indexes Have Statistics Too

SQL Server, T-SQL
It may seem obvious, but I've heard more than one person suggest to me that statistics on a clustered index just don't matter. That if the clustered index can satisfy a given query, it's going to get selected. That just didn't make any sense to me, but I haven't seen anyone set up a test that shows how it might work one way or the other. Here you go. First, I'm going to create a table and load it up with data. I'm intentionally using strings because I don't want to confuse the ease of management of integers within indexes. I also went for one column that would have a very attractive set of statistics and one that would have a very ugly set. Also, because we're only dealing with…
Read More
15Oct 2012 by Grant Fritchey 6 Comments

Why Tune Queries?

PASS, T-SQL
It's just a query against the database. If things are running slow, buy some more memory, a faster CPU or get a few more disks. Right? Seriously, tuning queries is just a pain and there's no clear evidence that writing them correctly or tuning them has a major impact. Right? Yeah, I'm being facetious. I've spent a considerable portion of my career  trying to make T-SQL code run faster. The fact is, throwing moneyhardware at the performance problem can fix it in many instances. At least temporarily. But let's face it, you're constantly changing the code. There are new queries, changes to old queries, it's always changing. The code is probably the most volatile aspect of a database system because it is the easiest to change. So, you may think…
Read More
22Aug 2012 by Grant Fritchey 1 Comment

24 Hours of PASS, Fall 2012

PASS, SQL Server, T-SQL, Tools
It's time to get your learn on again. The schedule for the Fall 24 Hours of PASS is up and ready for registration. This is the Summit preview session, so many (most, all) of the speakers are showing off some of what you can learn at their sessions at the PASS Summit 2012 itself. It looks like a pretty exciting bunch of topics given by some of the best professionals in the industry. I'll be presenting Three Ways to Identify Slow Running Queries on September 20th, 1400 GMT. This is just a sub-set of the information that I'll be presenting during my all day pre-conference seminar, Query Performance Tuning: Start to Finish. The full seminar I talk about how to measure the performance of your systems, identify which queries are…
Read More