DaveWentzel.com            All Things Data

Oracle

PerformanceCollector: a performance monitoring tool for SQL Server

About 15 years ago I wrote a suite of performance monitoring scripts for SQL Server 7 that would try to tell you why a given query was running slowly.  Back in those days there was no concept of DMVs to help you out.  Most people I knew relied solely on sqltrace (Profiler).  The problem is, Profiler will tell you how long something will run, but it won't tell you what subsystem of SQL Server is causing those long runtimes.  Is it a memory issue?  Slow IO?  Blocking?  The only information available was sysprocesses.  And it contained excellent information.  

I came from an Oracle background and in those days (Oracle8) there was no equivalent to Profiler.  If you wanted to look at your performance metrics you needed to query the sys and system schemas and the v$ views.  These are functionally equivalent to DMVs.  In fact, the folks at Oracle published a bunch of whitepapers on using these DMV-like tools and called it YAPP (Yet Another Performance Profiling Methodology).  YAPP was quite simply tuning based on the subsystems that caused long response times.  

I never realized how simple and elegant this approach to performance management was until I started working with SQL Server 6.5.  Other than sysprocesses, there was nothing else to tell you where the bottleneck was.  sqltrace was great for baselining but given a big query that had sporadic performance problems, where do you look next?  I actually started to do some independent learning about Performance Management that I began to document.  

But how could I do YAPP in SQL Server which had no publically-exposed wait interface?  Well, it turns out it did.  sysprocesses would show you the waittype, waittime, and waitresource for any spid that is waiting on some subsystem.  

I actually started this website in 1997 partially to document my findings with sysprocesses and ways to do YAPP-like analysis.  Subsystem Information Home, which now has lots of information on DMVs and response time tuning techniques, was initially a series of static pages on interested, undocumented things I found.  Remember, there was no google yet.  

I began to see more performance engineers and DBAs relying on sysprocesses as their tool of choice for performance troubleshooting.  Slowly DBAs started complaining to M$ to expose even more information in sysprocesses and give us something more YAPP-like.  With the release of SQL Server 2005 we were given our first DMVs.  I was thrilled.  But there were so many DMVs and they were still not entirely documented.  I created a new section of my website, DMV Info where I started to document what these DMVs did and how they helped in response time and wait analysis.  

I certainly wasn't the only DBA doing this, nor even the best.  In the last 10 years a complete shift in how SQL Server DBAs do performance engineering has occurred.  Nowadays I don't see anyone using Profiler and everyone uses the DMVs.  How the times have changed.  

Anyway, my first attempt at a performance monitoring and baselining solution was a SQL Executive (now known as SQL Agent) job that merely took a snapshot of sysprocesses and squirrelled it away so I could look at it later when someone complained about poor performance.  It was invaluable.  I rewrote it with the advent of DMVs.  Slowly over time I was monitoring even more "stuff" as every new release of SQL Server gave us new and exciting DMVs.  

I found this so useful I was installing it at every client engagement.  I decided to release a version of it a few years ago and posted it on my blog under the name Performance Engineer-in-a-Box.  I've since installed it at even more client sites and I finally got around to properly releasing this in a manner where I can do controlled releases as needed.  I now call the tool "PerformanceCollector".  The source code is available at my GitLab installation and is open to the public.  Feel free to modify it, fork it, steal it, change it, or whatever you would like.  I really don't care and their is no form of copyright on it.  I would appreciate an attribution if you do use it, but no payment is necessary.  

Over the next few blog posts I'll cover in details what PerformanceCollector does and how it works.  


You have just read "PerformanceCollector: a performance monitoring tool for SQL Server" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  

Bind Variables, Prepared Execution, ad-hoc SQL statements, and Performance...an allegory

And The Developer went to the Oracle and asked, "If programming in Delphi, should I use bind variables? What if I'm programming in Cleveland?  Or Visual Basic?" 

Tom Kyte, aka the Oracle, pondered the question for 3 days while sitting over a crack on a rock, inhaling noxious volcanic fumes.  He then, suddenly, responded.  "You will bind never performance will suffer."

Immediately the Oracle fainted from the fumes. 

The Developer tried to rustle him to consciousness.  He did not understand the Oracle's prophesy.  Should the missing comma come BEFORE or AFTER the word "NEVER" ?

The Developer could not wait for the Oracle to awaken, the Kanban card was already in the "blocked" state for too long and The ScrumMaster was angry.  What should he do? 

At that moment Bill Gates appeared in a burning bush. 

The Developer:  "Father, are bind variables good?"

"Son, we haven't the concept.  The closest thing is prepared execution.  As with all things my son, imbibe in moderation, for thy performance cup shall never runneth over." 

The burning bush disappeared.  Still The Developer was confused. 

The Developer came across The Great Google and structured his query thusly, "When sending a repeated SQL statement to my RDBMS, should I use a cacheable, parameterized statement?" 

He clicked "I'm Feeling Lucky"...because, well, he was. 

Stack Overflow responded immediately with The Commandments:

  1. "Thou shalt not commit non-parameterized querying."  Regardless of platform…prepared execution/bind variables in ad hoc SQL statements are thy friend.  On modern RDBMSs every single call should be parameterized.  Always.  No exception.  Ever.  
  2. "Remember to bind, to keep Oracle holy."  It is a PITA (pain in thine ass) to return result sets from Oracle stored procedures/packages which is why bind variables are the cornerstone of good performance on that platform.
  3. "Honor thy SQL Server Stored Procedure."  They are generally much better than adhoc parameterized statements for a litany of reasons. 
  • No need to pollute thine query cache with thy garbage singleton statements.
  • Stored procs are easier to change (sql can be recompiled on the fly with any kind of fix…I don't need to monkey with externalized sql embedded in compiled Java.  
  • I can use lots of different kinds of "hinting" that I can't use in ad hoc sql…changing isolation levels, locking paradigms, plan guides, etc. 
  • It's just good design.  Best to keep data logic at the data tier. 
  • Decrease the "network effect"…a stored proc call will always be more compact than an equivalent parameterized ad hoc statement.  Think a couple bytes don’t matter?  Try developing using "the cloud" with SQL Server on EC2 or Azure without using stored procs.  The network effect literally kills performance. 
  • Your friendly neighborhood DBA can't "profile" when you need help.  "All I see is that you ran sp_execute 15, 1417.  Sorry I can't help you. I can't find a stored procedure named "15,1417" in my database.  " 
  • If you use stored procs you can always "bear false witness against thy DBA."  (Seriously.  Just say, "I don't know why that stored proc isn't performing, isn't performance the DBA's job?"  It works flawlessly). 

4.  "Thou Shalt not be Chatty, but should remember to be Chunky, and keep it holy."  Regardless of platform, if you are going to send the same SQL statement a gazillion times a minute (say because you are too lazy to "batch up" your INSERT statements) then prepared execution isn't going to help much.  The performance benefits of a prepared INSERT statement may save a millisecond, but the real killer is the network chattiness, which can be 16, or sometimes even 50, milliseconds per call.  Best to be chunky. 

<TODO insert picture of chunky 'ol Dave Wentzel here.  Until then, here's another "Chunky">

Unit Testing Is Not a Panacea

Perhaps you work at a company that REQUIRES all code to be unit tested.  Perhaps you work at a company where only some code is unit tested.  Perhaps only Java code is unit tested, not SQL code, etc.  I've worked in all of these environments and in each environment some code that was indeed unit tested did not work and some code that was never unit tested always worked like a champ since Day One.  Certainly unit tests help to get you to the Promised Land of bug-free code, but just because you have them doesn't mean your code works.  

UPDATE FROM syntax and LEFT JOINs and ambiguity

If you use SQL Server you've probably seen code like this:

UPDATE dbo.tab SET SomeCol table2.SomeOtherCol

FROM dbo.tab 

JOIN dbo.table2 ON tab.ID table2.ID

To us SQL Server people this code makes perfect sense...update some values in the first table with rows from a second table.  It surprises many SQL Server people when exposed to Oracle for the first time that this is not valid ANSI SQL syntax.  I don't want to make this an Oracle post, but there are solutions to the "update one table with values from a second" pattern that work in Oracle.  They will usually perform just fine, my only complaint would be that the Oracle syntax is not nearly as easy to convert from a SELECT to an UPDATE so I can "test before executing".  What I mean is...in my UPDATE statement above I could change the first line to "SELECT table2.SomeOtherCol, tab.ID" and execute it to see exactly what rows I will be changing.  

That is not a jab against Oracle.  An Oracle purist will counter that the UPDATE...FROM TSQL syntax is frought with danger.  I agree, and that is what this post is about.  Let's look at some code:

CREATE TABLE #Foo (ID intcolA varchar(20))

CREATE TABLE #Bar (ID intFooID intcolB varchar(20))



INSERT INTO #Foo VALUES (1,'FooRow1')

INSERT INTO #Foo VALUES (2,'FooRow2')

INSERT INTO #Foo VALUES (3,'FooRow3')

INSERT INTO #Foo VALUES (4,'FooRow4')

INSERT INTO #Bar VALUES (1,1,'BarRow1')

INSERT INTO #Bar VALUES (2,2,'BarRow2')

INSERT INTO #Bar VALUES (3,2,'BarRow3')

INSERT INTO #Bar VALUES (4,2,'BarRow4')

Requirement:  Set #Foo.colA equal to *any value* from #Bar.colB, if there is a value available for the given key.  A contrived example to be sure.

BEGIN TRAN

UPDATE 
#Foo SET colA Bar.colB

FROM #Foo Foo

LEFT JOIN #Bar Bar ON Foo.ID Bar.FooID



select from #Foo

ROLLBACK TRAN

It should be obvious that #Foo.ID = 1 will be set to 'BarRow1' since there is only one lookup row.  It should also be obvious that #Foo.ID rows 3 and 4 will be set to NULL since there are no corresponding keys in #Bar for those FooIDs.  

What is less clear is what will happen to #Foo.ID = 2 ... will it be set to 'BarRow2', 'BarRow3' or 'BarRow4'?  There is ambiguity here.  In Oracle-land, the equivalent query (written of course without a LEFT JOIN) would throw "ORA-01427: single row subquery returns more than one row."  Exactly.  And really, isn't that what SQL Server should be throwing?  Many people have petitioned Microsoft to deprecate UPDATE...FROM (and DELETE...FROM, which has the same issues) syntax for this reason (as well as some other reasons).  It's become something akin to a religious war. With people on both sides of the debate.    

I personally like the UPDATE...FROM syntax, mostly because it's simple to convert to SELECT and test.  It's the UPDATE...FROM...LEFT JOIN syntax that gets tricky.  There are two reasons we might use LEFT JOIN in an UPDATE statement:

  • I don't want to restrict the rows I wish to act on, rather I want to assume NULL if the LEFT JOIN was not satisfied.  This is safe and unambiguous in my mind.  This is also the requirement listed above for this blog post.  
  • I want to LEFT JOIN but I may have *many* LEFT JOIN table rows for each *one* key row from the FROM table.  In other words, a 1:M relationship.  This is also part of my requirement above.  

In that case, which is my example above, what will be the final value for colA for ID 2?  Here you go:

BEGIN TRAN

UPDATE
#Foo SET colA = Bar.colB

FROM #Foo Foo

LEFT JOIN #Bar Bar ON Foo.ID = Bar.FooID



select * from #Foo

ROLLBACK TRAN

Before you jump to the conclusion that "I'll always get the first matching row back", remember that there is no concept of row ordering in an RDBMS unless there is a specific ORDER BY clause applied.  Never assume order.  Don't believe me?  Let's modify our code to add a CLUSTERED INDEX strategically:

BEGIN TRAN

CREATE CLUSTERED INDEX
tst ON #Bar (FooID,colB DESC)

UPDATE #Foo SET colA = Bar.colB

FROM #Foo Foo

LEFT JOIN #Bar Bar ON Foo.ID = Bar.FooID



select * from #Foo

ROLLBACK TRAN

Oops.  Note that colA's value has changed due to the new ordering inferred by the clustered index!

So far, so good.  I still don't see the big deal with UPDATE...FROM syntax *if* you understand the issues above.  Ah, but that's the rub.  Too often I see code like this:  

BEGIN TRAN

UPDATE 
#Foo SET colA (SELECT TOP 1 colB FROM #Bar Bar WHERE Bar.FooID Foo.ID)

FROM #Foo Foo



select from #Foo

ROLLBACK TRAN

What is this code trying to do?  Very simply, it is bringing back the FIRST row from Bar that has the given FooID, using a correlated subquery.  I'm not a mind reader, but the developer was probably thinking, "I have a situation where I am bringing back possibly more than one row for the given key, I better restrict it since I don't really understand what SQL Server will do.  I can accomplish that by using a TOP clause."  

I have problems with this approach:

  • You still haven't guaranteed *which* key will be returned (although that's not part of our requirement, it's still ambiguous and you shouldn't code ambiguity if possible).
  • You've taken a FROM...LEFT JOIN syntax that the optimizer will be able to handle in a performant manner and replaced it with a TOP clause in a correlated subquery.  Correlated subqueries *often* perform far worse than straight JOINs (especially against larger tables...this is the RBAR effect).  Also, TOP clauses incur the overhead of a SORT operator.  
  • It's not as easy to read or convert from a SELECT statement that I can test to an UPDATE.   

If the requirement were changed to return "the most recent entry from #Bar" then the TOP solution might be acceptable over smaller result sets, assuming of course the addition of an ORDER BY clause.  

Takeaways

  1. If you use UPDATE...FROM syntax be aware of ambiguities.  
  2. If you use UPDATE...FROM...LEFT JOIN be aware that you have ZERO control over which row from the LEFT JOIN'd table will be returned if you have a 1:M situation.  

TSQL

TSQL Autonomous Transactions or Another Reason I Like PLSQL over TSQL

Since I have both an MCDBA and OCP I often get asked which RDBMS I like better, Oracle or SQL Server?  The stock answer is, "it depends" (what a cop-out).  But for sure, PL/SQL is light years ahead of TSQL.  Some of my complaints with TSQL:

  • terrible error handling.  Yes I know all about TRY/CATCH.  I also know that there are still plenty of error conditions that occur before TRY/CATCH is invoked.
  • When assigning potentially multiple values to a variable, no error is thrown.  Consider this example:

      DECLARE @variable varchar(200)

      SELECT @variable = Name
      FROM
            (VALUES
                  (1,'Dave Wentzel'),
                  (1,'Angie Wentzel')
            ) AS tbl (ID,Name)
      WHERE ID = 1     
      SELECT @variable

If you've never seen the goofy VALUES syntax it is Row Constructors syntax.  Don't get caught up in the weeds, basically the variable will always be set to the last value returned.  For this example it will likely be Angie Wentzel every time, but can't be guaranteed and should generate an error.  Oracle does this via the error directive TOO_MANY_ROWS (or OTHERS at a minimum).  We should not be allowed to assign a non-scalar query result to a scalar variable

  • No autonomous transactions.  You can also consider this inconsistent temporary object behavior. 

Oracle has these things called PRAGMAs which is a fancy term for a "compiler directive".  In Oracle these are often misused and misunderstood.  There's really only one good use for them...logging error messages.  Assume a long running, multi-step batch process with a single transaction where I wish to log status messages as I go along.  Now assume near the end of the process it fails and I need to ROLLBACK.  I can of course catch the error and write out informational messages, but I cannot log anything to a table, especially those intermediate steps, since the transaction that rolls back will also ROLLBACK the logging.  Darn.  In Oracle we can write our logging routine using PRAGMA AUTONOMOUS_TRANSACTION which essentially means that routine does not participate in any existing transaction.  Problem solved.

For the longest time we didn't have this...until the introduction of table variables.  This "feature" is not well documented.  You can in fact do this:

      DECLARE @variable varchar(200)

      SELECT @variable = Name
      FROM
            (VALUES
                  (1,'Dave Wentzel'),
                  (1,'Angie Wentzel')
            ) AS tbl (ID,Name)
      WHERE ID = 1     
      SELECT @variable

Well, that's pretty cool and solves our logging problem, but if you didn't fully understand that behavior and continued to reuse a table variable after a ROLLBACK you will get unexpected results.

it also violates ACID properties of a transaction.  Some think a table variable isn't really a part of the db, it is a temporary object, but then why is the behavior different for temp tables in tempdb?  TSQL needs more consistency here.  

Database Server Consolidation - History and Future

DB servers, especially SQL Servers, seem to sprout up like weeds.  Somebody has some requirement for a little departmental application and right away that requires a new db server (for it's huge database of perhaps 100 MB) and another RDBMS license.  Oracle folks tend not to have this problem...the planning and allocation phase seem to be given more attention for some reason with Oracle.  If this trend continues you find after many years that your data center is full of SQL Servers with low utilization throwing off tremendous heat and using a lot of power.  

History of Server Consolidation

Server consolidation has been hot in IT for quite a few years now.  It's only natural to want to consolidate your db servers.  When consolidation first became a hot topic the idea was to replace those commodity Wintel departmental SQL Servers with blade servers.  They use less space and generate less heat.  

Still, this required purchasing blades, which were not cheap, and potentially tossing out the old Wintels.  Then we discovered the joys of VMWare and virtualization.  Those departmental SQL Servers could be virtualized quickly and easily using a P2V (physical to virtual) tool.  In fact, this worked so well that we began virtualizing every SQL Server we could find, even the clustered solutions (of course ensuring each cluster node resides on a different physical host).  This is pretty much the current state of virtualizing database servers.  

The Future

But there's much more we can be doing.  Virtualizing a server saves on power, rackspace, and climate control, but it saves very little on licensing.  SQL Server does have some interesting new licensing options when you virtualize, but there are still license costs for the ancillary software we all put on our servers (backup software, AV, monitoring, etc).  And of course each of those VMs still has to be patched every Patch Tuesday.  

A much better method is to virtualize up and down the stack where it makes sense.  Again, currently in the industry most people are only virtualizing at the machine-level.  

Consolidate at the Instance level

Some applications/databases can be virtualized by having them co-exist on a given SQL instance/machine.  For very simple departmental databases that are really just a database and a few logins to access the data and require no other features of SQL Server (agent, access to the OS, etc) then we can co-locate those databases on the same instance/machine without any issue.  Usually this involves copying the database and logins to another machine/instance and then updating a connection string on the web server.  

The benefit to this is we've eliminated a SQL license, we've eliminated another machine that will need to be patched, and we've eliminated all of the CPU cycles required to operate the OS and supporting software...we are only using the true cycles that the app/db requires.  Kinda sounds like the old TSO (time sharing option) systems from the 1960's.  The circle of life continues, what is old is new again.  

There are some drawbacks.  Since all of the databases will run under the same SQL service account, with the same global settings, shared memory, and tempdb, we need to be careful.  Departmental apps often simply log in as sa or as an account with sysadmin privileges.  You may need to re-evaluate this if security is a concern among consolidated databases.  Collation is another concern.  The databases must share the same collation.  Ah, but you say that SQL Server collations can be different on each database now.  That's true, but most applications use tempdb to create #temptables.  Those may not function correctly if tempdb's collation is not the same as that of your database.  You only get one collation for tempdb.  

If one of your databases is a resource hog (constantly reading massive amounts of data into and out of the buffer cache) it may contend with the other databases.  On a 32bit OS you have a fixed amount of RAM available for the buffer cache and you really don't want that contention.  

Consolidate Instances

Once you've considered consolidating databases to one instance, it's time to consider consolidating instances to a single machine.  VM hosts tend to be big, beefy boxes.  They need to be in order to handle multiple guests.  However, if you've ever looked at a quiet SQL Server you'll note there is still a lot of CPU activity.  This is due to AV and other data center monitoring tools, SQL Agent heartbeat activity, etc.  This is all wasteful if we just virtualize the SQL box and make it a guest using P2V.  

Instead, let's consider taking all of our instances and consolidating them onto a single server.  Your databases probably reside in the default, unnamed instance of SQL Server (the default instance for SQL Express is called \SQLEXPRESS).  You can create multiple named instances on a single machine.  What are the pros and cons...

Pros Cons
A single 32bit SQL instance can only address so much RAM (depending on Server OS).  If your SQL Server Host (running multiple instances) has lots of memory then each instance can run the maximum RAM without sharing buffer cache or experiencing memory contention.   Ensure your application can connect to a named instance/alternative port
Each instance can have a different service account, hence, different security needs can be addressed.   The CPU/disk can still be a bottleneck since they are still shared across instances.  
Each instance can have a different collation, global settings, etc If the db/app requires OS-level access (for file copying for instance) than inter-database security may still be a concern
$$$ (see next section)  

License Consolidation

Whenever you eliminate SQL Server hosts using instance consolidation you no longer need a license for that host.  A SQL Server license entitles you to multiple instances to a machine at no extra charge.  This is very compelling.  But, you should also consider the free SQL Express edition to save even more money.  SQL Express can run multiple instances on a single machine, the only caveat is the buffer cache is limited to 1GB and db size is limited to 10GB.  Before you scoff at using Express due to the limitations remember that we are only considering consolidating small databases anyway.  Also, remember that there are legal ways to bypass these limitations.  I've written an article about some interesting ways to use SQL Express in non-traditional ways.  License consolidation is just another method.  

Some other notes

Virtualization using Windows 2008 RS Hyper-V and Live Migration allows vms to move between hosts without any perceived service interruption.  

A P2V tool is included with System Center Virtual Machine Manager.  

Summary

When embarking on a database consolidatoin effort, don't just run a P2V on your SQL box and call it a day.  Really think about your architecture.  Can you consolidate databases on a single instance?  Can you consolidate many instances on a single machine?  Can you consider SQL Express to save your company money?  All of these ideas will make you a hero to management and a friend to the environmentalists.  

Data Tier Applications (DTA) vs My DB Porting Process

Data Tier Applications (abbreviated DAC) are the latest method in SQL 2008 R2 to make database deployment and versioning a little bit easier and native to the RDBMS stack.  A few years ago MS created database projects in Visual Studio that was a slightly different take on deployment and versioning.  Database projects are actually a superset and have some useful features, but I never thought the packaging/deployment stuff was all that great.  It's affectionately called Data Dude.  

Back to DAC...my take is that this technology won't take off and implementations will be few, leading to a quick deprecation similar to Notification Services (it died after 3 years).  The reasons are numerous but mainly database deployments are not the same, nor as simple, as your .NET code.  Database upgrades require you to understand the current state of the db and what must happen to the data to get it up to the current release.  DAC actually does a good job of this by running schema compares.  I'm impressed here.  But it still issues warnings and gives an admin choices as to what to deploy and skip.  That's the problem...I would never allow someone else to deploy my database upgrade piecemeal.  Like everything else in a database, the upgrade needs to be ACID.  

Obviously I like My Build Process better than anything else on the market.  It handles these situations cleanly, yet is still flexible.  Let's compare and contrast.  

Feature DAC My DB Porting Process
single unit of deployment that contains all elements used by the application (schema, db objects, instance-level objects) Yes Yes
centrally manage, monitor, deploy, upgrade applications using SQL Server Management Studio Yes Kinda...it can be centrally managed, just not with SSMS
requires additional licensing fees No No
can migrate an existing db to the DAC/DB Porting Process format Yes Yes
SQL Server release compatibility 2008R2 only for deployment. Package can be created on older versions 7,2000,2005,2008,2008R2
can utilize other RDBMS backends No Yes (Oracle only...but can be ported easily)
compares database schemas between 2 dbs (dev and prod for instance) Yes Yes
compares master/system/lookup data between 2 dbs (dev and prod for instance) No Yes
Integrates with any version control system easily Yes Yes
robust error handling (fail/continue on error, warnings, etc Yes Yes

The differences are in the flexibility when upgrading a database from version x to version y.  This is especially cumbersome when version x is not known but the process "figures it out" dynamically and knows just what to do.  DAC does a good job at this where all other tools require a source/target compare, which is difficult for deployment to thousands of customers.  

Feature DAC My DB Porting Process
Method to preserve data prior to upgrade copies db to new db standard sql backup
Method to upgrade data deploys new db/schema, copies data to new db from the copy, renames new db to old db name, maintaining the copy In place.  This will be *much* faster than DAC since most data in most upgrades won't need to be touched, yet DAC is always copying data.  
Upgrade is an online operation No Partially.  New objects (without dependencies) are deployed while the db is online, saving downtime.  
Can upgrade FROM any version TO any version Yes.  Has a "change detection" phase that looks for differences and auto-generates the SQL to make the change Yes.  Scripts are written assuming a change is always needed, hence will run *much* faster since the evaluation is not done during upgrade-time.  
Has a "confirmation page" before applying changes Yes. No.  Not needed, nor desired.  This should never be left to an anyone but the developer.  An admin should not make these decisions.  We either succeed, fail, or log a warning but the outcome of the scripts is always known in advance, we never leave decisions to someone outside of the approved change control process.  

Various DTA links

 

 

Nested Views, Performance, and Predicate Pushdown

I wrote earlier in the week about nested views and performance.  That post was focused around simplifying your views to follow a FROM –> INNER JOIN –> LEFT JOIN query pattern.  As with everything in SQL, these aren’t hard and fast rules.  Sometimes following that pattern may not produce optimal performance if the query optimizer is reducing the nested views too much and we do actually need the views to “materialize” or pipeline.  Usually the underlying cause is still a poorly written query or even bad db design, but it’s important to understand how to get nested (or derived tables, or subqueries, etc) to pipeline without reducing. 

First, What is Predicate Pushdown

I have more on Predicate Pushdown here.  You don't hear this term much in the SQL Server world, it's more prevalent amongst Oracle folks, but it's just as relevant to SQL Server.  When nesting views (or queries in general) in Oracle the predicate against the outermost query is "pushed down" to the nested views as far as possible to generate the most optimal EXPLAIN plan.  Generally this is a good thing except when

  1. you don't use bind variables and the result is much hard parsing and predicate pushdown not working optimally
  2. the predicate being pushed down is not selective enough to result to allow nested loops to be used (where it would normally) and hash joins are done instead.  

SQL Server won't have issues with 1, but 2 is always a possibility.  

Let’s look at an example.  I don’t have a repro script for you to test due to time, but follow along and the concept should be clear.  I have a view that nests other views that has has the execution plan shown on the right.  Note that at this step of the plan the query engine is processing 1.9 million rows.  Rest assured it should only be processing about 42,000 rows at this point. 

Note also that in the icons there is an orange circle with 2 arrows…that means sql thinks the query is so difficult it needs to span CPUs (parallelism) to get it completed.  It should never do this for this query, it’s very simple.  I’ll save you the details…here’s the join that is the problem…

 

LEFT OUTER JOIN MXW.ProjectCostCodeXCostType AS pccXct 
      ON pcc.ProjectCostCodeID = pccXct.ProjectCostCodeID 
LEFT OUTER JOIN MXW.MasterFileAdminCostTypeSetup AS mfc 
      on pccXct.CostTypeID = mfc.CostTypeID

The “mfc” view has no RIGHT JOINs, the problem is simply the nesting and the shear number of JOINs in the views.  Initially I just pulled out the cols and JOINs I needed from mfc (about 7 tables out of 22), embedded them in this view, checked that performance was correct (it was down to under 2 secs now), and moved on.  But that leaves us with duplicated code everywhere which isn’t wise. 

The underlying problem is that other predicates were being pushed down unnecessarily, resulting in a suboptimal execution plan.  

Here’s another solution that eliminates the unwanted predicate pushdown…you need to tell sql server to materialize the problem view first, then join that to the remaining query.  So basically we want pipelining now where normally we would not.  We can do this using temp tables in a stored proc, but you can’t use a temp table in a view.  The solution is to force the JOIN early.  How?  Make sql server resolve it first by using a derived table and CROSS JOIN.  Here’s the new query. 

--LEFT OUTER JOIN MXW.MasterFileAdminCostTypeSetup AS mfc

--    on pccXct.CostTypeID = mfc.CostTypeID

--predicate pushdown for parallelism performance. 

LEFT JOIN 

      (

            select v.* , dummy.nul

            from MXW.MasterFileAdminCostTypeSetup v

            cross join (select '' as nul) as dummy

            where v.CompanyCode = (v.CompanyCode + dummy.nul)

      ) mfc

      on pccXct.CostTypeID = mfc.CostTypeID

The CROSS JOIN makes sql server resolve the entire derived table before it can proceed to join to pccxct, even though the CROSS JOIN functionally does nothing.  That’s what we want.  You may think that you don’t need the CROSS JOIN because the derived table in parenthesis will resolve first because of basic rules of arithmetic order of operations (do what’s in parens first).  You’d be wrong.  Without the CROSS JOIN the optimizer knows the query in parens is reducible to a standard LEFT JOIN.  The CROSS JOIN is enough to fool it into not reducing the query. 

Note the new query plan, the correct actual/estimated rowcount, lack of parallelism (the double arrows), and subsecond response time. 



Summary

Occasionally SQL Server will generate a suboptimal query plan that can be traced back to the optimizer reducing a query that shouldn’t be or not honoring a derived table properly. 

Subscribe to RSS - Oracle