SQL Server Archives - Page 29 of 33

17Jul 2009 by Grant Fritchey 15 Comments

Execution Plan Compile Termination

Recently I've been seeing a lot of people with bad execution plans, desperately trying to tune them, but they were unable to explain why they had such bad plans. More often than no these were larger queries, with a number of derived tables, CTE's, CROSS APPLY, etc. In most cases the statistics appeared to be fine (this is usually checked by comparing estimated & actual rows within the operations in the execution plan) and the plans themselves didn't look crazy, but the execution plans were flat out, not good. If you're looking at a plan and it doesn't make much sense, one option that most people don't check... SQL Server didn't have enough time to complete optimization. The optimizer is a pretty amazing bit of code. The scary volume of…

26Jun 2009 by Grant Fritchey No Comments

Re-evaluating Execution Plans (again)

SQL Server, T-SQL

I keep thinking I've got a handle on the way execution plans are dealt with in SQL Server. I had a pretty simplistic view of things, there's the estimated plan which comes out of the optimizer and there's the actual plan which comes out of the data engine. The one shows the best guess for what will happen based on the query and the underlying statistics. The other shows what actually happened based on the query and the real data, load on the server, available memory, you get the idea. This model is easy to explain and understand. Too bad it's not quite the way things work. If you query the dynamic management function sys.dm_exec_query_plan, you can see a query plan. Once you drill down on the XML, or browse…

17Jun 2009 by Grant Fritchey No Comments

Object Database Editorial

nHibernate, Object Relational Mapping, SQL Server

I never used to read editorials. Not in emails, magazines, newspapers, whatever. Now, I make it a point of always reading them. You can learn as much from an editorial as you can from the technical articles within, sometimes more. Tony Davis has just posted a guest-editorial over at SQL Server Central. Tony is normally the editor at Simple-Talk, where he also writes interesting editorials. This one is not to be missed. It makes a very clear, and concise case for why object databases have a fundamental flaw for most business needs (not all, not always, but a pretty hefty majority). It's worth a read.

08Jun 2009 by Grant Fritchey No Comments

Microsoft SQL Server Premier Field Engineers

SQL Server, T-SQL

Joe Sack has started a new team blog for the Microsoft SQL Server Premier Field Engineers. If you don't know who they are, you should. The first post is just introductory, but this blog is likely to become a great resource. These are the guys that MS zip lines into tough situations with the expectations that they'll improve them. I'd strongly suspect these are fellows worth listening to.

27May 2009 by Grant Fritchey 16 Comments

WHOOOP!

PASS, SQL Server, T-SQL

I just got the good word, I submitted an abstract for a spotlight session at the PASS Summit and it was accepted. Jazzed doesn't begin to describe it. If you're interested in hearing what I think about Best Practices for working with Execution Plans, please swing by.

20May 2009 by Grant Fritchey 5 Comments

Odd TSQL Behavior

SQL Server, T-SQL

Before I describe this, let me thank Lynn Pettis over at SQL Server Central for coming up with the answer. This morning a developer walked up and asked me what would happen if he ran a query that looked like this: SELECT 1.SomeString I said that he'd get an error. No, he says, try it. So I try it and I get this: Somestring 1 Try it yourself. It works just fine. I'd never seen that before and didn't have a clue what it was. Thinking that Microsoft had supplied some new short hand to define aliases I ran this: SELECT 'dude'.dudette Which resulted in the error: Â Msg 258, Level 15, State 1, Line 1 Cannot call methods on varchar. Â Which is what I would have expected. I…

30Apr 2009 by Grant Fritchey 42 Comments

Database Screening Questions

SQL Server, T-SQL

With all the cool kids posting about beginners and interview questions, I thought I'd toss my favorites out there, from the brief-case gang point of view. TheseÂ are theÂ technical phone-screening questions I use after I look at a resume. There are only 10. They're simple. Stupid simple. Silly even. Yet, I can count on eliminating 4 out 5 people who have a resume that looks like a qualified DBA. I've seen people with 10 years experience fail on these questions. I'm only going to provide the questions. If you can't find the answers on your own, you're already disqualified: What is the difference between a clustered and non-clustered index? No, don't tell me that one is clustered and the other is not. I don't need specific low-level information on this, just…

27Apr 2009 by Grant Fritchey 1 Comment

Tim Ford’s Top 5 Indexing Best Practices

SQL Server, T-SQL

All I can really add to this is, yeah, me too. If you want some absolutely great advice on indexes, read this post. It's a must. And might I add, I've been the bad guy in Tim's example. Once, many, many years ago, I was reading from the SQL Server 7.0 documentation. It suggested that compound indexes were no longer needed since the optimizer could build them on the fly using index intersection. I had a performance problem and a consultant was telling me to use a compound index. I swore up and down it wouldn't work because Microsoft said so. He kept pushing and I kept pushing back. Finally, after a rather heated discussion in which I was convinced I had the upper hand, I got off the phone…

24Apr 2009 by Grant Fritchey 15 Comments

Unpacking the View

SQL Server, T-SQL

A view is simply a query that behaves something like a table. Most people know this. Most people also know that a view is simply a mask on top of what might be a very complex query. It all seems really simple. You call the view inside a simple query, the view runs the underlying complex query. Most people might not know that when a view is called and it gets sent to the optimizer, the optimizer unpacks the view and binds the component parts of the query necessary to create an execution plan that will return the data requested. What I didn't know until recently was that the optimizer is VERY smart. Not only does it unpack the query of the view, but it will change the query that…

13Apr 2009 by Grant Fritchey 4 Comments

Paul Randal’s Database Size Survey

SQL Server, T-SQL

If you have three minutes to spare, swing by Paul Randal's blog and answer his survey questions about the size and distribution of your database. The results are very interesting. I was most interested in the number of respondents to each of the questions.Â As each size category switched, fewer and fewer people responded. However, a lot more people responded than I expected. 94 last I looked had databases under 10gb in size, but 42 had databases over 1tb. Yeah, that's only 1/2, but, holy cow, it's 1/2. I wish I had a database to manage that was over 1tb. Back in the 7.0/2000 days I was at a dot com that was getting close. When I left they had 700gb. I understand they got close to 850 before the…

Category: SQL Server