27Sep 2010 by Grant Fritchey 3 Comments

Parameter Name Size And Performance

SQL Server, T-SQL
I saw an odd statement the other day, "The size of the name of the parameter does not affect performance." My first thought was, "Well, duh!" But then, I had one of those, "Ah, but are you sure" thoughts. And you know what, I wasn't sure. If size of the parameter name did affect performance, I figured, the one sure place where that would be evident is in the size of the execution plan. Right? I mean, if there was an impact on memory, and hence on performance, that's probably where you'd see evidence of it. I wrote two queries: DECLARE @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance int SET @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance = 572 SELECT soh.SalesOrderID ,sod.SalesOrderDetailID FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE soh.SalesOrderID = @ThisIsAVeryVeryLongParameterNameThatIsTrulyRidiculousButItIllustratesThePointThatParameterLengthDoesNotAffectPerformance DECLARE @v int SET…
Read More
12Jan 2010 by Grant Fritchey 4 Comments

TSQL Tuesday #2

SQL Server, T-SQL
I've wracked my brain for some bit of puzzle that I could present as part of TSQL Tuesday #2 and I finally came up with a bit of something. When you're looking at an execution plan for a query, you know that this represents SQL Servers best attempt at a good execution plan. But, it may not represent the best possible plan. Or, it could be the only possible plan. The puzzle is, how do you know what you're looking at? Is this a trivial plan, meaning it's the only possible execution method for the query? Is this plan fully optimized, or did the optimizer go through it's prescribed cycles and simply take the best plan it had generated up to that point? These questions can be answered directly from information…
Read More
05Oct 2009 by Grant Fritchey 15 Comments

Ad Hoc Queries Don’t Reuse Execution Plans: Myth or Fact

SQL Server, T-SQL
Another frequently heard story is that stored procedures get and reuse execution plans, but ad hoc queries do not. A lot of people believe this and say as much online and in the real world. Unlike my last myth, this time, I'm going to give you the DBA answer to this question. It depends. There are ad hoc queries and there are ad hoc queries. The classic ad hoc query looks like this: DECLARE @sql NVARCHAR(MAX), @value int; SET @value = 43668; SET @sql = 'SELECT * FROM Sales.SalesOrderHeader AS soh '; SET @sql = @sql + 'JOIN Sales.SalesOrderDetail AS sod '; SET @sql = @sql + 'ON soh.SalesOrderID = sod.SalesOrderID '; SET @sql = @sql + 'WHERE soh.SalesOrderID = ' + CAST(@value AS NVARCHAR); EXEC (@sql); And as ad…
Read More
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…
Read More
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…
Read More
23Jun 2008 by Grant Fritchey No Comments

Breaking Down Complex Execution Plans

SQL Server, T-SQL
Peter Ward, the editor at SQL Server Performance, has published an article of mine on Breaking Down Complex Execution Plans. I go way beyond the blog entry below and show how the estimated costs in execution plans can mess you up, how to use the XML in execution plans to search through them for costly operations or operations that have mismatched estimated rows & actual rows and some other tips and tricks. Hopefully it's worth a read.
Read More
29Apr 2008 by Grant Fritchey 9 Comments

nHibernate Recompiles and Execution Plans

nHibernate
One little potential for performance problems that we've seen comes out of how nHibernate generates it's parameratized queries. It limits the length of any parameter to the length of the column, but if the length of that parameter is less than the column, it uses tha smaller length when declaring the variable. This results in a query that looks like this: exec sp_executesql N'INSERT INTO dbo.users (Name, Password, EmailAddress, LastLogon, LogonId) VALUES (@p0, @p1, @p2, @p3, @p4)',N'@p0 nvarchar(8),@p1 nvarchar(6),@p2 nvarchar(12),@p3 datetime,@p4 nvarchar(8)',@p0=N'Ted Cool',@p1=N'abc123',@p2=N'ted@cool.com',@p3='2008-04-29 14:10:44:000',@p4=N'ted_cool' Note the parameter @p4 which is the parameter mapping to the primary key for the little sample table. In this query it's declared as nvarchar( 8 ) because 'ted_cool' is composed of eight characters. But if we changed it to 'ted_coolish': exec sp_executesql N'INSERT INTO dbo.users…
Read More