Query Optimizer Wins Again

Last Updated on March 15, 2015 by John Morehouse

In a previous post, I mentioned that I had to use dynamic SQL to handle the switching of the context of the query in order to accomplish what I wanted.  I had also mentioned that I would be putting out another blog post regarding that dynamic SQL and here we are!

As a recap, in that post I was attempting to dynamically calculate the server memory requirements based on the physical memory installed on the machine.  I’ll also mention again that Microsoft changed the structure of the DMV that was used between SQL Server 2012 and SQL Server 2008 R2 and lower.  In order to run the query on all servers, I had to determine a way to dynamically handle the appropriate structure for the various versions of SQL Server we have.

This is how I ended up doing it:

-- Setup the dynamic SQL
-- We need the physical memory values in GB since that's the scale we are working with.
IF @serverVersion > 10
	SET @sql = '(SELECT @memOut = (physical_memory_KB/1024/1024) FROM sys.dm_os_sys_info)'
ELSE
	SET @sql = '(select @memOut = (physical_memory_in_bytes/1024/1024/1024) FROM sys.dm_os_sys_info)'

-- Get the amount of physical memory on the box
EXEC sp_executesql @sql, @paramdefs, @memOut = @mem OUTPUT

This is the way that I actually tried several times to handle it:

-- We need the physical memory values in GB since that's the scale we are working with.
IF @serverVersion > 10
	SELECT @memOut = (physical_memory_KB/1024/1024) FROM sys.dm_os_sys_info
ELSE
	select @memOut = (physical_memory_in_bytes/1024/1024/1024) FROM sys.dm_os_sys_info

Of course, I know now that this won’t work at all.  I’ll be honest and say that it took me more than a few minutes to figure out why my logic wasn’t right and why SQL Server kept yelling at me.

The Truth Shall Set You Free

query_optimizer
Query Optimizer

Doh!  Of course, it was the query optimizer! The first thing that the optimizer does is to validate (or parse) the syntax of the code.

Knowing that this is the first thing the optimizer will do, looking at one of the branches of the ‘IF’ statement in the wrong (bottom) example, the columns were valid.  For the other branch,  it wasn’t because that column didn’t exist for that particular table depending on what version of SQL Server the code was examining.

When it couldn’t find the column, the optimizer had to bow out simply because it couldn’t verify that the column existed.  This forced me to put the syntax of the query into a dynamic statement simply because the dynamic sql isn’t parsed until the query itself is called through the sp_executesql command.

When using dynamic sql, the query optimizer just treats the building of the syntax just as regular text and happily continues along it’s merry way.

Once the dynamic sql syntax was in place, the query optimizer was then able to validate the query and things all fell into place.

Some References

SQL Guru  has a great post on how the optimizer works.  I highly recommend reading it if you’re not familiar with it.  Heck, even if you are familiar with it, read it anyway as a refresher.

Another SQL Server Guru, Paul White, also has a deep dive into the query optimizer and you can find the first post in his series here.

Another Lesson Learned

This little experience, while probably trivial,  was also a great reminder that we don’t always know or remember everything about SQL Server.  SQL Server is such a massive platform that you won’t know or remember everything about it.  You will at times forget even some of the simplest of things and that’s ok when that happens.  We are human after all.  😉

Summary

The next time that you are trying to get some code to compile and it won’t, take a moment see if the query optimizer can do it’s job.  If the query optimizer can’t parse and bind your code, then it won’t even get off the ground.

Enjoy!

© 2014 – 2015, John Morehouse. All rights reserved.

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.