How to get the Best Performance from Azure SQL Database Managed Instance

Published On: 2018-11-19By:

IO performance for your Managed Instance may not be what you were expecting. Typically when we create databases, especially when working in the cloud, we create the databases pretty small. However, this can be a problem with Azure SQL Database Managed Instance.  The performance that’s assigned to your databases which are hosed in Managed Instance will depend on the size of the database when you first create the database.  This means that you’ll want to create the database at the largest size possible (1TB currently) to get the best possible performance from your managed instance environment.

Denny

 

The post How to get the Best Performance from Azure SQL Database Managed Instance appeared first on SQL Server with Mr. Denny.

Configure First Domain Controller in Azure

Published On: 2018-11-16By:

Here we’re going to review the steps that you have to take to create your first domain controller in Azure when you already have an on-premises domain. This includes all the times that DNS needs to be switched around in Azure so that the process works.

Bonus, now with sound (I had to upload the video as the sound wasn’t saved last time).

Configure First Domain Controller in Azure

Published On: 2018-11-15By:

Here we’re going to review the steps that you have to take to create your first domain controller in Azure when you already have an on-premises domain. This includes all the times that DNS needs to be switched around in Azure so that the process works.

Columnstore Indexes and Key Lookups–The Worst

Published On: 2018-11-14By:

Key Lookups are one of my least favorite SQL Server execution plan operators. This is where for each record in an index seek, SQL Server goes back to the clustered index and looks up a record. Generally, we either live with this condition (for a very small number of rows) or we fix it by adding columns directly or adding included columns to our nonclustered index.

However, in the plan below, we have a different scenario. We have a clustered columnstore index, that has an additional nonclustered index on the table. This was a feature that was added in SQL Server 2016 to allow point lookups on a column without having to scan many row segments of the index. This works pretty well for some conditions, though it is important to know that it can slow down your inserts significantly.

In the last year or so, with a large customer who makes fairly heavy use of this pattern, I’ve noticed another concern. Sometimes, and I can’t figure out what exactly triggers it, the execution plan generated, will do a seek against the nonclustered index and then do a key lookup against the columnstore as seen below. This is bad for two reasons–first the key lookup is super expensive, and generally columnstores are very large, secondly this key lookup is in row execution  mode rather than batch and drops the rest of the execution plan into row mode, thus slowing the query down even further.

 

Screen Shot 2018-11-14 at 7.38.32 AM

I’ve anonymized the schema, but that key lookup is against my clustered columnstore index. This query never finished. So what’s the resolution? Index hints–I’m not a big fan of using hints, but sometimes you need to kick the query optimizer in the shin. When I changed this query with the index hint, it completed in around 7 seconds. The ultimate fix is for Microsoft to fix this costing, but that’s hard. You can vote on my User Voice item here:

https://feedback.azure.com/forums/908035-sql-server/suggestions/36015868-key-lookup-against-columnstore-index-causes-slow-q

If you see this pattern pop up at all, you will definitely want to hint your queries.

 

1 2 3 4 5 395

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.