Unable to restore a backup – Msg 3241

I worked on an interesting issue today where a user couldn’t restore a backup.   Here is what this customer did: backed up a database from an on-premises server (2008 R2) copied the file to an Azure VM tried to restore the backup on the Azure VM (2008 R2 with exact same build#) But he got…

2

Why am I getting so many checkpoint files when I have In-Memory OLTP enabled?

Recently, I looked an In-Memory OLTP issue with Principal Software Engineer Bob Dorr who is still my office neighbor.  After restoring a database that had just one memory optimized table, we dropped the table. Even without any memory optimized tables,number of checkpoint files keep going up every time we issue a checkpoint.  For a while,…

2

A few new things in XML plan to help you troubleshoot query performance

  In this blog Added per-operator level performance stats for Query Processing, Senior PM in QP talks about extending operator level performance stats.  They include stats related to reads, CPU and elapse time.  These are very helpful to track down query performance issues.  We worked on recent case where we put ActualElapsedms in a good…

0

Be aware of 701 error if you use memory optimized table variable in a loop

In blog “Importance of choosing correct bucket count of hash indexes on a memory optimized table”,  I talked about encountering performance issues with incorrect sized bucket count.  I was actually investigating an out of memory issues with the following error. Msg 701, Level 17, State 103, Line 11There is insufficient system memory in resource pool…

4

Solving SQL Connectivity issues: A new guided walk through just got published

We recently published a new document that provides a one stop shop for solving majority of connectivity issues that you may run into when working with SQL Server. This can be accessed here: Solving Connectivity errors to SQL Server In addition to providing a quick checklist of items that you can go through, the doc…

7

Pssdiag & Sqldiag Manager update

We are releasing a new version of Pssdiag & Sqldiag Manager which supports SQL Server 2016.   It was originally published on https://diagmanager.codeplex.com. But we just moved it to github under Microsoft organization repo. New location is at https://github.com/Microsoft/DiagManager.  In the “Releases” tab, you can download latest binaries.   Go to getting started wiki to learn how…

1

Why can’t I get a parallel plan when rebuilding my index?

In support, we frequently have customers who call us and want to find out why a particular query doesn’t use parallelism.  Recently, we had a user who wanted to know why his index rebuild didn’t use parallelism.  In some cases, NonParallelPlanReason in query plan tells you the reason.  But in this case, the query plan…

2

Unable to connect to SQL Server AlwaysOn Listener on Azure due to misconfigured subnet mask

When it comes to troubleshooting, the more you know the better troubleshooter you are (in my option). Sometimes knowing more actually gets in the way.   We repeatedly get calls where customers couldn’t connect to AlwaysOn Listener on Azure because of the misconfiguration of the subnet mask for the listener IP. They receive this error “A…

0

SQL Nexus 5.5.0.1 was released

We just released SQL Nexus 5.5.0.1 on codeplex.   In addition to numerous ‘rules’ that catch issues that can potentially impact your SQL Server performance, we added quite a few reports.  Some of the reports are displayed below. The following three were born out of troubleshooting experience. “Top 20 Active CPU Intensive Queries”:  When troubleshooting CPU…

0