T-SQL Archives - Page 24 of 26

03Nov 2008 by Grant Fritchey 4 Comments

Great Article on DBA-Developer Conflict

nHibernate, Object Relational Mapping, T-SQL

What a great way to phrase the issue. I love the concept of the people-people impedence mismatch. We're going through it pretty regularly where I work. Our developers are convinced that using an ORM tool, in this case nHibernate, they're eliminating all the problems with the database because they're taking complete control of the database through nHibernate. All code will be on their side of the fence, no more messy stored procedures. All data structures will look like their objects, no more having to figure out those silly JOINS. Best of all, by setting all this up, no more messing with those stupid and obnoxious DBA's. Unfortunately, they're still planning on object persistance (don't call it data storage) inside of a SQL Server database... Um, guys, you haven't eliminated a…

30Oct 2008 by Grant Fritchey No Comments

Procedure Cache and Dynamic Management Views

SQL Server, T-SQL

I'm just going through the chapter on the procedure cache in the new book and I'm having a blast playing with the dynamic management views and functions that let you access the procedure cache. It's just too much fun and way too easy to get detailed information about the queries in the system, not like the old days. First, you can access the cache itself with the DMV, sys.dm_exec_cached_plans. This shows some of the data describing the plan in cache, but most importantly it provides the plan_handle. You need this for other joins later. You can also use sys.dm_exec_query_stats to get aggregated performance statistics about the plan. It also has the plan_handle and two things new to SQL Server 2008, the query_hash and the query_plan_hash, also known as query fingerprints.…

24Oct 2008 by Grant Fritchey 4 Comments

Amazingly Stupid Query

T-SQL

I have to share this one. I'm working on a set of queries, tuning them. They're all following a similar pattern. They're passing in XML data sets which are then shredded into a temporary table. Once that's done, the temp table is used in a series of joins to other tables to return the data. Some of the queries were missing indexes, some were structured a bit poorly, but overall, it was pretty standard stuff. Until... I ran a query that looked, at first glance, the same as the rest. When I looked at the execution plan, I saw a warning symbol, one of those little exclamation points on an operator. I figured, based on the other issues with this database, that it was just some out of date or…

29Sep 2008 by Grant Fritchey No Comments

Dissecting SQL Server Execution Plans at PDC

PASS, SQL Server, T-SQL

I just got word that Red Gate has printed more copies of the book that they'll be distributing at the Microsoft Professional Developers Conference that's taking place in LA. I just wish we had a 2008 version of the book now because, while most of it is still applicable, there's more that can be done with execution plans now. Have I mentioned I think the missing index information that's displayed with the statement text in the graphical execution plan in SQL Server 2008 is pretty slick? Well it is. I hope they're going to distribute it at theÂ PASS SummitÂ this year too. I think they are, but I don't know that for a fact.

29Sep 2008 by Grant Fritchey 3 Comments

Loop Joins, More rows on top or bottom?

T-SQL

I've seen this question come by in the web searches multiple times. The nested loop join is also called an iterative join. This is because it takes the rows from the inner part of the join and compares them through an iterative process (one-by-one) to the rows in the outer part of the join. So, if the optimizer has correctly chosen this operation for your query, you should see FEWER rows in the top, or outer, part of the join and MORE rows in the bottom, or inner, part of the join. Take this query as an example (run against AdventureWorks2008): SELECT * FROM [Sales].[SalesOrderHeader] soh JOIN [Sales].[SalesOrderDetail] sod ON soh.[SalesOrderID] = sod.[SalesOrderID] WHERE soh.[SalesOrderID] = 47716 Here we have a single row from the SalesOrderHeader table and 55 rows…

24Sep 2008 by Grant Fritchey 2 Comments

Index Statistics

T-SQL

The other day a developer showed up at my desk. They were getting time-outs in production onÂ a query that didn't normally give them trouble. With the parameters they provided, I ran the query. It ran for over 30 seconds, the application side timeout, before it returned it's data. So I ran it again with an execution plan. It had a bunch of index scans with loop joins across thousands of rows and even created a table spoolÂ with 700 million rows as part of the process. Clearly not good. Next I looked at the query plan. It wasn't too bad, as these things go. It was probably moving too many columns and apparently the business wanted a pivot on the data since they were using an aggregate method to pivot some…

16Sep 2008 by Grant Fritchey 2 Comments

JumpStartTV

SQL Server, T-SQL

All the videos I recorded on execution plans for JumpStartTV are now available. Please, go and check them out. Then hang around JumpStartTV and check out all the videos that Brian Knight did on SSIS. Also, if you're interested, I think Andy and the team would like other people to submit videos as well. So if you're watching my stumbling attempts and are convinced you can do better, do it and submit it.

15Sep 2008 by Grant Fritchey No Comments

Video Lessons

SQL Server, T-SQL

A while back, I wrote a book, Dissecting SQL Server Execution Plans. Because of it, I had some conversations with Steve Jones & Andy Warren. For a SQL Server geek, heady company. Anyway, they asked me what my plans are for the book. Plans? I wrote it. I thought that was the plan. But they meant lessons, licensing and all that kind of stuff. I didn't have a clue, but they did. A few weeks ago I flew down to Florida and recorded a bunch of short video lesson plans derived from the book and from discussions with Andy Warren. It was a blast. They've now been published over at JumpstartTV. I hope you find them useful. I had a blast doing them and learned a lot from Andy and…

09Sep 2008 by Grant Fritchey No Comments

Query Fingerprints and Plan Fingerprints

nHibernate, T-SQL

SQL Server 2008 has been released, but the full capabilities of the product are still be discovered. This isn't a case of discovery so much as Microsoft, in the person of Bart Duncan, has shown us something new. Read the article. This is one of the most exciting things I've seen in a long time. It's especially exciting as we see databases becoming less and less manageable through the implementation of tools like nHibernate. As I noted in aÂ previous post, nHibernate will create a lot of execution plans. With the capabilities here, we'll be able to easily and quickly aggregate some of those plans to identify the costly queries coming out of nHibernate without having to resort to 24 hour Profiler monitoring. Great stuff.

15Aug 2008 by Grant Fritchey No Comments

More on Table Valued Functions

T-SQL

From the hits in the search results, this is a popular topic. That being so, I'd like to redirect you to another blog that has some more detailed tests available. These tests show more of the shortcomings of multi-statement table valued functions. Gail Shaw is an MVP and a regular at SQL Server Central. She's worth tracking and this post shows why. I want to be clear. I'm not suggesting that you never, ever, use multi-statement table valued functions. There may be places where their use is helpful. I'm saying that using them comes with a very heavy cost, so you better be sure that they are in fact needed in the situation, whatever it is. There was a long discussion and debate over at SQL Server Central recently on…

Category: T-SQL