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.

 

Thoughts on Microsoft Ignite and the PASS Summit being on the same week

Published On: 2018-11-12By:

Having the PASS Summit 2019 and Microsoft Ignite both on the same week will be a very interesting problem, to say the least.

From an attendee perspective, there isn’t a lot of crossover between the two conferences. Some people are able to attend both events, but for the most part, there is going to be minimal crossover between the two events.

From a speaker perspective, there is some overlap between the PASS Summit and Microsoft Ignite, because only a few speakers are selected to present at the Microsoft Ignite conference.

From a sponsor and exhibitor perspective, things are going to get a little more interesting.  There is a decent amount of crossover between the vendors when they have a booth at the conferences.  Vendors will probably end up having to pick an event to sponsor and have their teams attend.

This choice is where things will get interesting. While the PASS Summit is about DBAs and Developers, the Microsoft Ignite conference is about everyone in IT, including senior management. This most significant part of IT that Microsoft Ignite focuses on is the fact that IT management has the budget to buy tools. While DBAs and Developers are the ones that have to use tools, management has to decide if they should buy the tool, hire the consultant, etc.

The fact that senior management attends Microsoft Ignite and typically doesn’t attend the PASS Summit is going to be a challenge for the PASS Summit, when it comes to attracting vendors and sponsors.  One of the things that differentiate members of management from DBAs and developers, is that management can spend money on tools, consulting, etc.  This means that vendors that are deciding between going to the PASS Summit to talk to DBAs and Developers and going to Microsoft Ignite to speak to IT senior management are going to have a decision to make.

Given that DBAs and Developers typically lack authority to spend money (or they can’t spend much money) where members of management have much more money to spend. From a pure business perspective, going to the PASS Summit may end up being a hard decision for companies and people that typically go to both events. Additionally, Ignite is a much bigger conference (9-10x the size of PASS Summit), making it a richer surface area for vendors.

Denny

The post Thoughts on Microsoft Ignite and the PASS Summit being on the same week appeared first on SQL Server with Mr. Denny.

Is Azure SQL Database Managed Instance the Right Solution?

Published On: 2018-11-05By:

Maybe.  Is the software that you’re trying to move to Microsoft’s Azure require the use of a SQL Server instance so that you can create jobs, logins, and do

https://www.flickr.com/photos/theaucitron/5810163712/

things like cross-server queries?  Are you trying to avoid adding more Virtual Machines (VMs) to be managed in your environment (and a SQL Server Failover Cluster or Availability Group)?  If the answer to these questions is “yes” then Managed Instance might not be the right solution for you.

Now granted, you may need to wait until the Performance Tier of Managed Instance is available as the General Purpose tier may not have the power to run the application that you need to set up.  If CPU and memory is the only problem that you’re running across than merely waiting is all that you have to do.

Managed Instance is like everything else in Azure, it may or may not be the correct solution to solve the problem at hand, but it might be.

Denny

The post Is Azure SQL Database Managed Instance the Right Solution? appeared first on SQL Server with Mr. Denny.

1 2 3 393

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.