DaveWentzel.com            All Things Data


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 #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.


#Foo SET colA Bar.colB

FROM #Foo Foo

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

select from #Foo


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:


#Foo SET colA = Bar.colB

FROM #Foo Foo

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

select * from #Foo


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:


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


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:  


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

FROM #Foo Foo

select from #Foo


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.  


  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 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
                  (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
                  (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.  

Subscribe to RSS - Oracle