SQL Server 2016 Public Preview (CTP2) – Live Query Statistics

I have spent a lot of time at customer locations working on resolving SQL Server performance issues on business critical environments. This also involves helping customers understand how to track down the performance bottlenecks and the remediation steps that need to be taken to remove identified performance bottlenecks. This involves two kinds of troubleshooting: post-mortem analysis and troubleshooting the issue as and when it is happening! Yes, I am talking about live troubleshooting which is a scary thing to do on a production server.

So if you share my deep rooted passion for working on SQL Server performance issues, the Live Query Statistics feature in SQL Server 2016 CTP2 is definitely worth knowing more about!

The Live Query Statistics can be enabled in one of the following ways:

1. Using Management Studio

The screenshot below shows the button which enables the Live Query Statistics. This can be a very powerful tool to troubleshoot bad query performance while the query is actually executing. You actually get insights into the plan and which part of the plan is actually taking time while the query executes.

Live Query Stats button on toolbar

You get a host of information from the Live Query Statistics as seen in the screenshot below. You will be able to pin point the part of the plan which is the culprit because you will have the completion percentage statistics for each and every operator in the plan. The completed operators show you the efficient parts of the plan. Additionally, you also get the time spent in each operator which gives you statistics for identifying the most time consuming part of the plan. And what’s more, you even know how much of the query is completed. This is one of the most common questions that I used to receive from customers while troubleshooting long running queries: “How long will the query take to complete“? Well now, there is an answer!

image

2. Use Activity Monitor

A new grid has been added in Activity Monitor named “Active Expensive Queries” which allows you to right-click a query and click on the “Show Live Execution Plan” option. Live Query Stats button in Activity Monitor

However, the “Show Live Execution Plan” option will only be enabled if the application  or user:

  • Executes SET STATISTICS XML ON; or SET STATISTICS PROFILE ON; in the target session.

  • The query_post_execution_showplan extended event has been enabled. This is a server wide setting that enable live query statistics on all sessions

And if you are developer, then you can use this feature in conjunction with the Transact-SQL debugger and pin point slow parts of the execution plan while the query is running. A truly powerful way to write and optimize queries! The debugging experience is now enhanced as the live query plan can be used along with breakpoints! The screenshot below shows what the debugging experience would look like.

image

Do keep in mind that this feature is provided for troubleshooting/debugging slow running query scenarios and should not be kept enabled in production environments. There is a moderate performance impact for collecting and displaying the above mentioned statistics.

Reference:

Live Query Statistics
https://msdn.microsoft.com/en-us/library/dn831878.aspx 

Disclaimer: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Two starts, one recompile and a confusing place

I used to be regular viewer of the TV Series “Two Guys, A Girl and A Pizza Place” co-starring Ryan Reynolds. The blog post title is a play on that name. In the past week, I have heard about this phenomenon of “one ghost Stmt: Starting” event more than thrice which is what triggered this blog post.

When you are analyzing such a trace file using RML Utilities, you will find an entry in the tblStatements table showing a Statement for a particular Batch Sequence (Column name: BatchSeq) which has a Start Time but no End Time. Additionally the AttnSeq column for that row would have a NULL value indicating that the statement didn’t timeout nor was it cancelled due to a Server/Client side error.

So what is this ghost entry all about. If you manually dig through the profiler trace, you will find that that the Statement in question has a Recompile event associated with it. This is by-design. When a Stored Procedure statement encounters a Recompile event, it would have a SP: StmtStarting event associated with it but no corresponding SP: StmtCompleted event. This can lead to confusion during trace file analysis.

Here is an example illustrating the same. I have a Stored Procedure whose definition is as follows:

CREATE PROC usp_recompile
AS
SELECT * FROM tbl1 OPTION (RECOMPILE)

The Recompile hint is added to forcefully induce a Recompile for the Stored Procedure when it is executed. If you look into the profiler events captured below during the Stored Procedure execution, you will see that there is a SP:StmtStarting event followed by a SP:Recompile and a SP:StmtRecompile event. Then there is another SP:StmtStarting event indicating that the recompile operation is complete and the start of the execution of the SP statement.

image

Hope this helps clear up the confusion around this topic.

SQL Server Backup Simulator: Cumulative update

The SQL Server Backup Simulator was launched on Microsoft Code Gallery to aid and assist in troubleshooting backup issues related to SQLVDI. Thank you to everyone who has evangelized the tool and provided feedback on the same.

The current release is marked as v1.2.

We released two minor updates for the tool to incorporate the following changes:

  1. SQL Backup Simulator does not report FileVersion instead it reports ProductVersion while reading the sqlvdi.dll information – There was an issue that we uncovered while validating the sqlvdi DLL version which was corrected in v1.1 and is now part of the v1.2 release.

  2. COPY_ONLY feature while simulating backup – The COPY_ONLY feature was added to ensure that the any full backups done for a database by the tool doesn’t break an existing backup chain for a database.

The latest executable can be downloaded from: https://github.com/Microsoft/tigertoolbox/releases/latest

System.OutOfMemoryException for Management Studio

I have seen multiple posts on the web trying to decipher the OutOfMemoryException thrown by Management Studio (SSMS) while executing a user query. Contrary to popular beliefs, this is not a SQL Database Engine Out of Memory condition. This is a client side OOM condition thrown by the .NET runtime while trying to receive the output of the query from the Database Engine. More often than not, the culprit is a large number of rows returned by the query submitted by the user.

A lot of times the error can be circumvented by using the TEXT mode output of SSMS. The error is a .NET Out of Memory exception pointing to the Management Studio running out of available physical memory. The GRID output requires a .NET GridView control to be created where as the Text mode output uses a TextBox to store the output returned by the database engine. The amount of memory consumed by the GRID is higher than a TextBox.

It is always advisable to store the output of a query returns a large number of rows into a file (CTRL+SHIFT+F) or use SQLCMD to generate the output into a CSV/TXT file. This would help optimize the memory usage on the box that is executing the query and also prevent re-execution of the query due to client box out-of-memory conditions. IMHO I cannot fathom the need to output a million rows in the GRID view because it is not possible to parse the output unless you put that into a flat file! Hope this sheds some light on this common misconception.

Once such issue is mentioned below:

Reference: OOM error when we access Schema changes report from SSMS – Microsoft

Upcoming Zeollar Presentation

I will be presenting a 30 minute demo on how to perform post mortem analysis for SQL Performance issues using RML Utilities and SQL Nexus on 24th June, 2010. The recorded and live session will be available on Zeollar. Zeollar is a Microsoft India DPE initiative that gets you the latest technical content on a daily basis in different channels. Think of it like a personal television that switches on every day at a specified hour allowing you to switch channels and view the channel of your interest.

You can download the calendar invite from here.

How to troubleshoot Windows Internal Database setup issues

Windows Internal Database is bundled along with Windows applications that need to use a backend database solely for their application purpose. Eg: WSUS, Sharepoint.

The application’s installation files will call the MSI package for installing the Windows Installer Database. In case the application setup fails due to a failure in installing the Windows Internal Database, then you would need the MSI log file for the WID MSI package. This can be found normally in the <System Drive:>\Windows folder and would have the following naming convention: *WSSEE*.log. If you are unable to locate the file, then find out all *.log files in the folder which were created recently.

Once you have located the file, you would need to search for the following string “Return Value 3” in the file.

Eg:

MSI (s) (E0:88) [16:02:14:252]: Product: Windows Internal Database — Error 1402. Could not open key: <Reg Key Name>.  System error 5.  Verify that you have sufficient access to that key, or contact your support personnel.

Error 1402. Could not open key: <Reg Key Name> System error 5.  Verify that you have sufficient access to that key, or contact your support personnel.
Action ended 16:02:14: InstallFinalize. Return value 3.

If you check what Operating System error 5 stands for, you will find out that it is a permissions issue.

C:\>net helpmsg 5

Access is denied.

Based on the error message, you will have to take corrective actions and re-run the setup.

Export Import Wizard throws "Class not registered" error

When you launch the Export/Import Wizard from SQL Server Management Studio, you get the following error: 

TITLE: Microsoft SQL Server
——————————
This wizard will close because it encountered the following error:
——————————
ADDITIONAL INFORMATION:
Class not registered
 

When you look into the Technical Details of the error, you will find the following call-stack:
===================================
This wizard will close because it encountered the following error: (Microsoft SQL Server)
===================================
Class not registered
——————————
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.get_DBProviderInfos()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetDtsDbProviderInfos(WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceCombo.PopulateProviders(Boolean fSources, WizardInputs wizardInputs)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnInitializePage(EventArgs e)
at Microsoft.SqlServer.Management.UI.WizardPage.RaiseEnterPage()
at Microsoft.SqlServer.Management.UI.WizardForm.NextPage(WizardPage nextPage)
at Microsoft.SqlServer.Management.UI.WizardForm.Next_Click(Object sender, EventArgs e)
 

If you try to execute the following query, you will get “Class not registered” error or you will get 0 rows as the output: 

EXEC master..xp_enum_oledb_providers  

When you expand Server Objects -> Linked Servers -> Providers, you will find no entries. 

This issue happens because the OLE DB provider DLLs are not registered on the machine. The reason the Export/Import wizard fails is that the landing page of the Wizard executes the code which enumerates all the provider DLLs that are registered with SQL Server. 

You would have to manually register the OLE DB provider DLLs present in the C:\Program Files\Common Files\System\Ole DB\ directory. Or you can run the script below from a Query window to obtain the regsvr32 commands for the all the DLLs present in the OLE DB folder. After executing the regsvr32 commands, check if the providers are listed under Object Explorer in Management Studio. 

/*** Script start ***/ 

set nocount on 

create table #dlltable (rowid int identity(1,1),dllname varchar(4000)) 

insert into #dlltable 

exec xp_cmdshell 'dir "C:\Program Files\Common Files\System\Ole DB\"*.dll /b' 

delete from #dlltable where dllname is null 

update #dlltable set dllname = 'regsvr32 /s "C:\Program Files\Common Files\System\Ole DB\'+dllname+'"' 

select dllname as cmd from #dlltable 

drop table #dlltable 

set nocount off

/*** Script end ***/