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
07Aug 2013 by Grant Fritchey 7 Comments

Be Cautious When Critizing About Guidance

T-SQL
I recently posted some comments about some guidance offered by Microsoft when talking about the CXPACKET wait type. Nothing I said was wrong, no. But, evidently there are a few whip smart and agile people who work for Microsoft. Specifically, Rick Byham, who works for the team tasked with taking care of the SQL Server Books Online. Why am I calling Mr. Byham smart and agile. Evidently he saw the blog post and has completely updated the description for CXPACKET at MSDN: Occurs with parallel query plans when trying to synchronize the query processor exchange iterator. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism. WHOOP! That's awesome work. Thanks Mr. Byham. Of…
Read More
05Aug 2013 by Grant Fritchey 19 Comments

Be Cautious Offering Guidance

Azure, SQL Server, T-SQL
Guidance is hard. Seriously, you'd think it would be easy. You'd think you say things like, don't shrink your database, most tables should have a clustered index, never go against a Sicilian when death is on the line, don't mix sharks and tornados, and that would be it. You'd be done. But it's not that easy. Even worse, it's SHOCKINGLY easy to get stuff wrong. An example. I was looking at information over at Microsoft Developers Network (MSDN) in the SQL Server Books Online. I was reading through information about wait types when I found this little beauty: Occurs when trying to synchronize the query processor exchange iterator. Know what that is? Yeah, OK, a few of you who memorize wait stats do (shut up Tom), but most of us…
Read More
24May 2013 by Grant Fritchey 3 Comments

A Little Azure Fun on Friday

Azure, T-SQL
You know how we've always heard that sp_msforeachtable and other similar undocumented functions may not be supported in future versions of SQL Server? Have you tried running something like this on any of your Windows Azure SQL Databases yet: EXEC sp_msforeachtable 'select ''?'', count(*) from ?' Try it out.
Read More
13Feb 2013 by Grant Fritchey 50 Comments

SQL Server Naming Standards

T-SQL
Want to start a fight between a bunch of DBAs or database developers? Ask them where the comma should be placed between columns in a SELECT statement. It's actually a little disturbing just how much emotional value people place in this. Almost as good, tell a database developer you don't like their naming standard. Hoo boy! The purpose of a naming standard, I think most of us can agree, is for clarity. We're defining language so that we all mean the same thing when we say it, right? We want to communicate clearly, so we're going to implement a naming standard. Fine. Sounds good. And then, you get this: dbo.tblDdltbl Umm... Wait. What? And there are hundreds of these, all with the same first three letters, tbl. Oh, wait, I…
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
07Jan 2013 by Grant Fritchey 1 Comment

Statistics Update Clarification

T-SQL
By default statistics are created automatically within SQL Server. And, by default, these stats are updated automatically based on a set of triggers. The triggers are defined as If 0 rows - Any data added leads to a statistics update If < 500 rows - 500 rows added causes a stats update If > 500 rows - 500 rows + 20% of the number of rows causes a stats update (unless you enable a traceflag in 2012 in which case you get a proportional value instead of 20%). There are some exceptions for temporary tables and some variations for filtered statistics and filtered indexes, but you get the idea. I was writing an article on statistics in preparation for another Oracle/SQL Server discussion (on, you guessed it, statistics) and I…
Read More