Press "Enter" to skip to content

Category: Administration

The Costs Of Rebuilding Indexes Online

Published 2019-01-31 by Kevin Feasel

Tibor Karaszi explains that TANSTAAFL (There A’int No Such Thing As A Free Lunch) applies to online index rebuilds:

The time it takes to rebuild the index can be substantially longer for ONLINE. Many of us has other things to do with the database during night-time and/or weekends. Yes, these are the typical window in time where we try to find things such as index rebuilds. Say that you do it night-time and it currently take 4 hours. Wouldn’t it be nice if you could cut that time down to 1.5 hours? That would leave more time for imports, massaging of data, CHECKDB and other things you want to do. Sure, you can do it ONLINE, but it will slow down access during the rebuild. Also the more data you modify during the rebuild, the more space you need in tempdb.

Betteridge’s Law of Headlines applies too, so that’s two important principles in one post.

As far as the post goes, Tibor makes a fair point: there is a trade-off between availability and efficiency with index rebuilds. But having worked with clustered columnstore indexes in 2014, you’ll pry the online operations in subsequent versions out of my cold, dead hands.

Comments closed

Azure Kubernetes LoadBalancer External IP Woes

Published 2019-01-31 by Kevin Feasel

Andrew Pruski writes up some issues he had with creating a LoadBalancer service in Azure Kubernetes:

I logged a case with MS Support and when they came back to me, they advised that the service principal that is spun up in the background had expired. This service principal is required to allow the cluster to interact with the Azure APIs in order to create other Azure resources.

When a service is created within AKS with a type of LoadBalancer, a Load Balancer is created in the background which provides the external IP I was waiting on to allow me to connect to the cluster.

Because this principal had expired, the cluster was unable to create the Load Balancer and the external IP of the service remained in the pending state.

There were a lot of steps here; click through to see just how many.

Comments closed

QueryMemoryLimit In SSAS 2019

Published 2019-01-30 by Kevin Feasel

Shabnam Watson covers a new setting in Analysis Services 2019:

The purpose of this setting is limit the amount of memory any single query can take. This setting is extremely useful when you want to limit the amount of memory consumption per query for queries across the board. Before this setting, it was possible to have an extremely poorly written query eat up all of a server’s memory and bring all other queries down to a halt. You can see an example of a such a query and SSAS memory settings in my previous post here.

Read on for details about what it does and what happens when a query reaches the memory limit.

Comments closed

Could Not Clear Differential Bitmap

Published 2019-01-30 by Kevin Feasel

Jack Vamvas takes us through a reason why you might get error 3041:

An error message has started appearing in the SQL Server Error Logs during a nightly full backup.

Could not clear ‘DIFFERENTIAL’ bitmap in database ‘Database1’ because of error 9002. As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup. This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary. Typically, the cause of this error is insufficient resources. Investigate the failure and resolve the cause. If the error occurred on a data backup, consider taking a data backup to create a new base for future differential backups.

Click through for the root cause and solution.

Comments closed

Authentication With Azure Managed Instances

Published 2019-01-29 by Kevin Feasel

Hamish Watson explains that Windows authentication is not available with Azure Managed Instances:

Azure SQL Managed Instances do not utilise windows authentication – so your two methods of authenticating applications and users are:
– SQL Authentication:This authentication method uses a username and password.
– Azure Active Directory Authentication:This authentication method uses identities managed by Azure Active Directory and is supported for managed and integrated domains. Use Active Directory authentication (integrated security) whenever possible.

Hamish also elaborates on some of the trickier bits about Azure Active Directory for someone used to on-prem AD solutions.

Comments closed

Optimizing For Ad Hoc Workloads

Published 2019-01-23 by Kevin Feasel

Bert Wagner looks at the optimize for ad hoc workloads option in SQL Server:

Instead of filling the execution plan cache with plans that will never get reused, the optimize for ad hoc workloads option will cache a plan stub instead of the full plan. The plan stub is significantly smaller in size and is only replaced with the full execution plan when SQL Server recognizes that the same query has executed multiple times.

This reduces the amount of size one-time queries take up in t he cache, allowing more reusable plans to remain in the cache for longer periods of time.

I’ve run into several cases where this has helped SQL Server and don’t think I’ve found a scenario where it actively hurts.

Comments closed

Adding ML Services On Windows Server Core

Published 2019-01-23 by Kevin Feasel

Kevin Chant shows us how to add SQL Server ML Services to an already-existing SQL Server installation on Windows Server Core:

It’s important to try and use an install set that is the same level of Service pack as your current install. Otherwise, you could end up installing multiple patches to get the SQL Launchpad service to work. Which is something discussed in a previous post here.

I know some companies have a central installer for SQL Server and then have all the updates in another location. Hence, if you are in such an environment be prepared to run multiple updates from that location after the install.

This is definitely one of the features which is easier to install from the beginning than to install after the fact.

Comments closed

Recreating Dropped Azure SQL Managed Instance DBs

Published 2019-01-22 by Kevin Feasel

Jovan Popovic has a script to re-create an Azure SQL Managed Instance database which you might accidentally have dropped:

Azure SQL Database – Managed Instance is fully-managed PaaS service that provides advanced disaster-recovery capabilities. Even if you accidentally drop the database or someone drops your database as part of security attack, Managed Instance will enable you to easily recover the dropped database.
Azure SQL Managed Instance performs automatic backups of you database every 5-10 minutes. If anything happens with your database and even if someone drops it, your data is not lost. Managed Instance enables you to easily re-create the dropped database from the automatic backups.

Click through for the Powershell script.

Comments closed

Get Windows Failover Cluster Errors

Published 2019-01-21 by Kevin Feasel

John Morehouse walks us through the Get-ClusterLog cmdlet in Powershell:

Sometimes you know that a problem occurred, but the tools are not giving you the right information.  If you ever look at the Cluster Failover Manager for a Windows Cluster, sometimes that can happen.  The user interface won’t show you any errors, but you KNOW there was an issue.  This is when knowing how to use other tools to extract information from the cluster log becomes useful.
You can choose to use either Powershell or a command at the command prompt.  I tend to lean towards Powershell. I find it easier to utilize and gives me the most flexibility.

Click through for an example, including of a method which filters out everything but error messages.

Comments closed

SQL Undercover Inspector V1.3

Published 2019-01-17 by Kevin Feasel

Adrian Buckman announces a new version of the SQL Undercover team’s Inspector:

We know some of you really hate linked servers so we have been working on a powershell collection which will allow you to install the inspector without using linked servers to centrally log the information and instead the powershell function Invoke-SQLUndercoverInspector will do the rest for you (We will be writing a blog post about how you can use this soon) – this is currently a pre-release version so it’s a work in progress – I must say a massive thank you to Shane O’Neill (b | t) without his powershell skills this wouldn’t turned out as well as it has, thanks Shane!

If you’ve already downloaded this version, be aware that there is a hotfix.

Comments closed