September 2013 - Page 2 of 2

16Sep 2013 by Grant Fritchey 1 Comment

Why the Lazy Spool Is Bad

First thing, there are no bad operators, just bad parents, uh, I mean query writers, or database designers, or ORM tools. Why do I say this? Because all the operators within a query execution plan serve a purpose. They are there to fulfill a task. Depending on where and when you see them, they're doing exactly what you ask of them. The issues come up because you're asking them to do a task that they may not be well suited for. This comes from inappropriate structures and inappropriate code. Lazy spools are not really bad (that was just link bait). In fact, depending on the query, what's being done, how you're retrieving data, what data is being retrieved, the lazy spool is actually awesome. But, it's good to know what…

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…

12Sep 2013 by Grant Fritchey No Comments

Microsoft Expands Data Centers… Again

Azure

Didn't they just do this in the spring? Word is out that they're expanding even more data centers. The cynical take would be that it's really all about the expanded need for data because of XBox One. But I doubt that explains it all. What's happening is that Azure is finally taking off. Note, I didn't say that Azure SQL Databases are taking off. I'm not sure they are. I think it's a combination of the web sites and the virtual machines. I've a feeling that Microsoft's investment is actually going to pay off. Question for you. Have you invested in Azure too? If not, time to get started. I've posted about it multiple times, so I won't insult you with a bunch of links. Just one, connect your existing…

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…

10Sep 2013 by Grant Fritchey No Comments

New Windows Azure Virtual Labs

Azure

Getting started with new technologies can be a pain. That makes all the new labs that Microsoft just posted extremely useful. There are several that are going to be immediately applicable to your average data pro; Introduction to SQL Database, Connecting a PaaS Application to an IaaS Application with a Virtual Network,Â Web Sites and Virtual Machines using ASP.NET and SQL Server. The beauty of these things is that you don't need to do anything to get started. These are virtual machines hosted out on the cloud that you get access to. Further, you'll get a lesson plan to follow so you're not left floundering. You will have to either connect your MSDN account to set up an Azure account or, if you don't have MSDN, sign up for an Azure…

09Sep 2013 by Grant Fritchey No Comments

Developers Rate Azure One of Their Favorite Tools

Azure

Yeah, Azure. How we program, what we program and where we program is changing. All the time. This excellent article lays out a bunch of the trends that are going on within software these days. And one of the single biggest parts of this trend is the fact that more and more things are online. In the cloud, if you insist. Clearly, despite unusual (and I would argue, unreasoning) resistance from my fellow DBAs, Azure is absolutely becoming "a thing." If you're like me, as you sit around carefully weaving your buggy whips, you're also keeping an eye on the road, just in case you start to see more automobiles than horses. Maybe I'm located in a bad spot, but it's starting to look like a sixteen lane mega-highway outside…

06Sep 2013 by Grant Fritchey No Comments

Speaker of the Month: September 2013

Professional Development

This is my second post in what I hope will be an ongoing series. You can see the rules for this, such as they are, and the last winner here. I didn't travel this past month, so I'm pulling my speaker of the month from a session that was recorded at 24 Hours Of PASS. I love the topic of database design. I love the topic performance tuning. So, my speaker of the month is Audrey Hammonds (b|t) and her session Design Matters! The Performance Impact of Database Design. I've known Audrey for a few years now, but I'd never sat through one of her sessions. What's wrong with me? I don't know, but I finally did and I'm really happy that I took care of it. I loved how…

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…

04Sep 2013 by Grant Fritchey No Comments

Azure Capabilities Expand

Azure

But then, the capabilities in Azure are always expanding. Here's the new stuff that was just released in a blog post on Scott Guthrie's blog. I'm interested in seeing how that automatic scaling on VMs works with SQL Server (if it works with SQL Server). Good times. And yeah, the cache is really the big news, but I'm still a relational storage monster. Can't help it. Remember, if you want to learn Azure, you can link your MSDN subscription with an Azure account. Go here for the details.

03Sep 2013 by Grant Fritchey 11 Comments

My Next Phone May Be a Nokia

Misc

I really like my Windows Phone. Yes, there are not as many apps as on a Droid or iPhone. But the apps there are and the OS itself... wow! And the hardware is always charging forward. And evidently, this new acquisition from Microsoft suggests that it might charge forward in a more coordinated fashion. We'll see.