Whenever the log fills up it has to grow. See How to shrink SQL Server log for an explanation how it grows. Any transaction could be the one that triggers growth, simply because any transaction could be the one that happened to take the very last unused byte in the existing file. When growth occur all transaction block. And if you have default 10% auto-growth in place and your database log already grew out- of check then the log growth can be very slow as instant initialization is not possible on log files.
What matters is that you have a log growing, so you must take immediate measures to understand why is growing and to mitigate accordingly. Go read Factors That Can Delay Log Truncation right now. The fact that you caught B in the fact of being blocked by A could be just random. B could had just as easily trigger the growth, it would had taken exactly the same time to grow it, and you would have catch A being blocked by B. The fact that A is causing a long running transaction may be one of the factors that prevent truncation, but that has to be confirmed.
The line from MSDN is talking about using EXEC()
, like this:
SET @sql = 'SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';';
EXEC(@sql);
In my testing, modern versions of SQL Server are still able to reuse a plan like this, but there may be other variables (such as version, or for example if you add conditional WHERE
clauses based on the presence of certain parameters - in which case that will generate a different plan).
If you use sp_executesql
then the parameter values can still cause parameter sniffing issues (just like with normal SQL), but this has nothing to do with whether SQL Server can re-use the plan. This plan will get used over and over again, just as if you hadn't used sp_executesql
at all, unless variables that would cause a direct query to get recompiled, in which case this one will get recompiled too (essentially, SQL Server doesn't store anything with the plan that says "this was executed from sp_executesql, but this one wasn't):
SET @sql = N'SELECT foo FROM dbo.bar WHERE x = @x;';
EXEC sys.sp_executesql @sql, N'@x varchar(32)', @x;
As a bonus, this has built-in protection against dynamic SQL, and avoids you having to worry about doubling up single quotes due to string delimiters. I blogged about some of this here and please read up on SQL injection here and here.
If you are having issues with plan re-use and/or parameter sniffing, some things you should look into are OPTION (RECOMPILE)
, OPTIMIZE FOR
, optimize for ad hoc workloads
and simple/forced parameterization
. I addressed a few similar questions in response to a recent webcast here, it may be worth a skim:
The gist is: don't be afraid to use sp_executesql
, but only use it when you need it, and only spend energy over-optimizing it when you have an actual performance issue. The example above is a terrible one because there's no reason to use dynamic SQL here - I've written this answer assuming you have a legitimate use case.
Best Answer
These are query planner warnings. In the actual execution plan (perhaps not the estimated one) I would expect to see a "warnings" section listing what the planner/engine is concerned about.
A common warnings are when an operation spills data to disk or was wanting index statistics that were unavailable. The most common warning is for apparently missing indexes, which is where SSMS gets the information it shows in green text for some statements.
(search for "showplan warning" and "query plan warnings" for a number of articles with more detail)
Other places to pick up on the plan warnings are via extended events (see here and other articles that pop up on searching for "query warning extended events") which allows you to monitor for problems in your running applications and in the output included when you use
SET SHOWPLAN_XML ON
orSET SHOWPLAN_ALL ON
(which give you the information SSMS and other tools such as SQL Sentry's Plan Explorer analyse to show warnings).