Unable to connect to SQL Server on azure VM due to an extra NSG applied to subnet

If you need to open up your SQL Server on an Azure VM to public internet access, you need to look no further than this document Connect to a SQL Server Virtual Machine on Azure (Resource Manager). It has very detailed step-by-step instructions. On a very high level, here are steps In your VM, create…

3

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

Why do I get the infrastructure error for login failures?

In the past few weeks, I saw this error come across quite a bit and thought I will provide an explanation for the reasons why we generate this error. It is very possible that you came across either one of the flavors of the two error messages shown below: 2016-07-08 23:53:59.63 Logon       Error: 18456, Severity:…

1

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

New memory grant query hint MIN_GRANT_PERCENT came to rescue

In SQL Server 2012 SP3, we made supportability improvements in the memory grant space. One of the features (https://support.microsoft.com/en-us/kb/3107401)  is allow you to hint your query (MIN_GRANT_PERCENT and MAX_GRANT_PERCENT), giving you much more granular control. There are additional columns related memory grants in sys.dm_exec_query_stats (https://support.microsoft.com/en-us/kb/3107398) and query_memory_grant_usage extended events to help troubleshoot memory grant issues….

4

Bob is Moving To BOBSQL

Bob Ward and Bob Dorr are among the founding members of PSSSQL as long standing SQL Server support professionals.   We are both excited to tell you about our new roles. Bob Ward has joined the SQL Server development team as a Principle Architect focusing on the customer experience in the Tiger Team.  Bob is expanding…

0

SQL 2016 – It Just Runs Faster: XEvent Linq Reader

SQL Server 2016 improves the XEvent Linq reader scalability and performance.    The XEvent UI in SQL Server Management Studio uses the XEvent Linq reader to process the events for display.   Careful study of the XEvent Linq reader revealed opportunities for scalability and performance improvements.     Note:  The XEvent Linq reader is .NET based and…

2

SQL 2016 – Leverages On Demand MSDTC Startup

SQL Server leverages MSDTC for distributed transactions (begin distributed transaction, remote proc trans, etc.)   Prior to SQL Server 2016 the MSDTC service must be running (started) prior to any SQL Server, DTC based transaction activity.   SQL Server 2016 enhances SQL distributed transaction capabilities leveraging MSDTC, On Demand startup.  The On Demand startup of MSDTC…

2

SQL 2016 – It Just Runs Faster: In-Memory Optimized Database Worker Pool

SQL Server leverages a worker pool for the In-Memory Optimized Database capabilities.  Prior to SQL Server 2016, increasing the size of the pool required you to enable a startup trace flag.   SQL Server 2016 removed the trace flag and updated the design to dynamically adjust the size of the In-Memory Optimized Database worker pool…

1

SQL 2016 – It Just Runs Faster – AlwaysOn AES-NI Encryption

The SQL Server 2016, AlwaysOn, log transport takes advantage of hardware based encryption to significantly improve scale and performance.   SQL Server 2016 development efforts continued focusing on the AlwaysOn log shipping transport.  Testing revealed that software based encryption, while fundamentally sound, could be improved using hardware based capabilities.   The exchange of information between…

0