What to do when you run out of disk space for In-Memory OLTP checkpoint files

While data for memory optimized tables resides in memory all the time with SQL Server 2014 and 2016’s In-Memory OLTP feature, we still need a means to cut down recovery time in case of crash or restart.  For disk based table, checkpoint flushes the dirty pages into data file(s).  With In-memory OLTP, there are separate set of…

1

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

SQL Server 2016 Temporal Data Assists Machine Learning Models

Microsoft is always seeking out ways to improve the customer experience and satisfaction.  A project that is currently active looks at the SQL Server incidents reported to Microsoft SQL Server Support and applies Machine Learning.   A specific aspect of the project is to predict when a case needs advanced assistance (escalation, onsite, development or upper…

0

MultiSubnet = TRUE Is Now Default Behavior

I get to be the a good new messenger today.    We have made changes to the SQL Server Client Provider.  The provider detects when multiple IP addresses are present for a listener.   The links below detail the behavior making it easier for your multi-subnet AlwaysOn deployments. Improved MultiSubnet Listener Behavior With Newly Released SQL Client…

1

Will SQL Server use ‘incomplete’ or ‘dirty’ statistics during online index rebuild?

We had a customer who opened an issue with us and wanted to know the behavior of statistics during online index rebuild.  Specifically, he suspected that SQL Server might have used ‘incomplete’ statistics because his application uses read uncommitted isolation level. This type of questions comes up frequently.  I thought I’d share my research and…

1

The given network name is unusable because there was a failure trying to determine if the network name is valid for use by the clustered SQL instance

Have you seen this message before? We see our customers encounter this message while performing SQL Server installation. If there is a problem, you will normally get this message in the “Instance Configuration” page of the “new SQL Server Failover Cluster setup” sequence. Here is how the screen appears with the message at the bottom:…

0

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

In-Memory OLTP files –what are they and how can I relocate them?

In SQL 2014 and above, you can create memory optimized tables with In-Memory OLTP feature.   When you use this feature, SQL Server actually generates native code to optimize performance.  As a result, there will be dll, pdb files plus other intermediate files.  In fact, native optimization is one of three pillars of high performance.  The…

1

Using the SSMS ConnectionDialog

I was attempting to add the SSMS connection dialog to my utility and ran into problems with referenced assemblies. The ConnectionDialog is documented here: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.ui.connectiondlg.aspx The following is a snippet using the SSMS ConnectionDialog in a C# application. Microsoft.SqlServer.Management.UI.ConnectionDlg. ConnectionDialog dlg = new Microsoft.SqlServer.Management.UI.ConnectionDlg.ConnectionDialog(); Microsoft.SqlServer.Management.Smo.RegSvrEnum. UIConnectionInfo connInfo = new Microsoft.SqlServer.Management.Smo.RegSvrEnum.UIConnectionInfo { ApplicationName = “My App”…

0