Why am I getting NULL values for query_plan from sys.dm_exec_query_plan?

Recently we got a customer who called in and wanted to know why he received NULL for query_plan when querying sys.dm_exec_query_plan.   This customer referenced a blog from https://dzone.com/articles/dmexecqueryplan-returning-null.  In that scenario, you will get NULL for query_plan if there are any statements involving temp tables that have not been executed for the first time.   For…

3

Tips & Tricks on ‘cloning’ Azure SQL virtual machines from captured images

While we have documentation on how to create a VM from captured image under “How to capture a Windows virtual machine in the Resource Manager deployment model”, it doesn’t address some specifics on SQL Server.  We have seen more and more users wanting to configure SQL Server and a set of databases so that they…

0

SQL Server Parallel Query Placement Decision Logic

Recently I had a conversion with (Jonathan Kehayias jonathan@sqlskills.com) about the placement of workers belonging to a parallel query.   As I asked around and reviewed the code I quickly found that the assumption is still ‘Least Loaded Node’ but this changed in SQL Server 2012 and broad awareness for both our support engineers and customers…

2

Not every extended event is suited for all situations

SQL Server Extended Events (xevent) are great to troubleshoot many issues including performance issues or other targeted scenarios.  But we keep seeing users misusing them to negatively impact their systems. Here is a latest example.  We had a customer who called our support for an issue where the same query ran fast in test but…

0

Spool operator and trace flag 8690

If you have seen enough query plans, you for sure ran into spool operators (index spool or table spool). It is documented in https://technet.microsoft.com/en-us/library/ms181032(v=sql.105).aspx The spool operator helps improve a query performance because it stores intermediate results so that SQL doesn’t have to rescan or re-compute for repeated uses.  Spool operator has many usage. For…

1

Are My Statistics Correct?

The question is often “Are my statistics up-to-date?” which can be a bit misleading.   I can make sure I have up-to-date statistics but the statistics may not be accurate.  I recently engaged in an issue where the statistics were rebuilt nightly.   A maintenance job change had been made moving from FULLSCAN to WITH SAMPLE statistics…

1

Operating System Error (665 – File System Limitation) Not just for DBCC Anymore

The operating system error 665, indicating a file system limitation has been reached continues to gain momentum beyond DBCC snapshot files.    Over the last ~18 months I have reproduced issues with standard database files, BCP output, backups and others. We have posted previous blogs talking about the NTFS attribute design and associated limitations (665) as…

5

Server’s “Max Degree of Parallelism” setting, Resource Governor’s MAX_DOP and query hint MAXDOP–which one should SQL Server use?

SQL Server allows a user to control max degree of parallelism of a query in three different ways.   Just for references, here is a list of documentation: SQL Server wide “max degree of parallelism” configuration is documented in max degree of parallelism Option.   Microsoft Support has recommended guidelines on setting max degree of parallelism per…

4