Press "Enter" to skip to content

Category: Administration

Auditing Index Changes

Published 2019-12-27 by Kevin Feasel

Jason Brimhall gives us a solution to track index changes:

In the previous article, I showed a quick and dirty method to try and capture the indexes that may have been just created. We all know that in the hectic life of the DBA, it is easy enough to forget the indexes created more than just a few hours in the past.

In that article, I mentioned that the solution provided was far from a complete solution to the entire problem. A bigger part of the picture involves audits to know when and by whom indexes were created, altered or dropped.

Keeping track of this kind of thing is important, particularly in an environment where performance suddenly changes even though the devs totally didn’t touch anything, promise.

Comments closed

Monitoring Oracle on Azure

Published 2019-12-27 by Kevin Feasel

Kellyn Pot’vin-Gorman covers several tools which are available for working with Oracle databases in Azure:

The Oracle SQL Developer product has come a long way since it’s inception and much of that credit needs to go to the incredible team at Oracle, including those that are prevelant in the Oracle community, Like Jeff Smith, Kris Rice and Ashley Chen.  Their willingness to listen to the Oracle community and turn their needs into features has been one of the critical reasons for the product success.

Although this product is more focused towards the developer, unlike the previous three, I want to point out a few areas that hopefully will convince you there are more similarities than differences.

The shortest version of this is “the same tools as exist on-prem” but if you don’t know that answer, Kellyn’s got you covered.

Comments closed

No-Longer-Necessary Trace Flags

Published 2019-12-20 by Kevin Feasel

Monica Rathbun points out some of the trace flags which are no longer important in SQL Server:

If you have ever attended one of my performance tuning sessions, you know I tend to talk about  trace flags.  Trace Flags can help fix performance issues and some are now defaulted in later SQL Server versions. In my opinion, when a trace flag’s behavior defaulted in a version, then you should potentially put them in place within environments that do not have them implemented. Below, are a few of these particular traces flag along with Microsoft’s definition of what each trace flag does, taken straight from MS documents.  I have also included a brief commentary on each one.  As with any change, you should be sure to thoroughly test before implementing these trace flags into any production environment.

Read the whole thing, especially because at least one of them is still optional and defaulted to off (but able to change at a different scope).

Comments closed

Managing On-Prem Data Gateways

Published 2019-12-19 by Kevin Feasel

Jamie Wick has some advice for managing Azure on-premises data gateways:

When organizations become aware of the On-premises Data Gateway application, there can be an explosion in the number of gateways that are connected to the tenant. Some people will invariably install a ‘temporary’ gateway on their local computer while they investigate/develop a new PowerBI report, PowerApp or Azure Automation process. All of these ‘temporary’ gateway instances can create problems when the systems that use them are moved into production and remain connected to the ‘temporary’ gateways.

In a previous post, I wrote about how to install and use an On-premises Data Gateway to connect on-site data with Azure-based apps (PowerBI, PowerApps, etc). Now we’re going to look at how to curtail the sprawl of gateways that may be occurring and how to remove any existing gateways that are deemed to be unnecessary.

Sprawl happens, but Jamie shows you how to limit it.

Comments closed

Column Alteration with Minimal Downtime

Published 2019-12-12 by Kevin Feasel

Andy Mallon shows how you can turn an integer column into a bigint column without disrupting your users:

Changing a column from int to bigint has gotten a lot easier since I started working on SQL Server back at the turn of the century. SQL Server 2016 introduced the ability to do ALTER TABLE...ALTER COLUMN as an online operation using the WITH (ONLINE=ON) syntax. This wonderful syntax now allows you to alter a column from int to bigint without causing major blocking. The int to bigint conversion is one of the most popular data type changes I see–A developer inevitably creates the table thinking they will never have more than 2 billion rows… then some years or months later 2 billion becomes a reality.

The DBA is left with the task of implementing that data type change, and now that it has almost 2 billion rows, it’s a well-established table and uptime during the change is a major consideration.

This is a great post from Andy. If you want to dig into the concept of near-zero downtime in more detail, I’ve got a series on the topic.

Comments closed

Finding Missing SQL Server MSIs

Published 2019-12-11 by Kevin Feasel

Annette Allen had a service pack installation go south due to missing MSIs:

I was recently doing a service pack, I’d run it on the entire test estate and half of the Production estate, I’d used Pinal Dave’s really useful AG check list and been really overcautious, I’d finished integrity checks on all databases, I’d backed everything up and even had a snapshot of the server completed.

When I clicked on the service pack I got the error  “missing MSI” sorry I don’t have the screen dump or the full error message because at the time of trying to fix it I did’t think to take a copy

Read on to see what the root cause was and how Annette was able to fix this error.

Comments closed

Checklist for an Azure VM Running SSRS

Published 2019-12-10 by Kevin Feasel

Kathi Kellenberger has a troubleshooting guide for setting up an Azure virtual machine to run SQL Server Reporting Services:

Recently I set up an Azure VM running SSRS for my students to host their database and report projects. My goal was to set up a custom domain name and use SSL. I ran into a few issues and a couple of wrong paths so I thought this would be helpful for others trying to accomplish the same. I’m not going to do a step-by-step walkthrough, but instead a checklist to help you troubleshoot

It’s not step-by-step, but it’s pretty close.

Comments closed

Online and Resumable Operations in SQL Server

Published 2019-12-10 by Kevin Feasel

Kendra Little summarizes which operations in SQL Server have the ability to be run online, which are resumable, and which support the WAIT_AT_LOW_PRIORITY flag:

ONLINE operations in SQL Server were simple to understand for years — we got ONLINE index rebuilds in SQL Server 2005. That was it for a while. Then, things got more complicated: we got more types of indexes. We got ONLINE options for schema changes that don’t involve indexes. We got more options for managing things like blocking, because online operations are really only mostly online — generally there’s going to be at least a short period where an exclusive lock is needed to update metadata. We now have some RESUMABLE operations coming in, too, for those big operations that are tough to handle.

Along the way, I fell behind. Because these features have steadily come out over a period of time, my brain simply didn’t register them all, or possibly I missed seeing them amid other announcements.

It’s not a comprehensive list, but it’s a good starting point for understanding the options you have available.

Comments closed

The Difficulty of Tracking CPU Usage

Published 2019-12-04 by Kevin Feasel

Grant Fritchey digs into the difficulties of tracking CPU usage on machines:

There are a bunch of ways to look at processor usage. The simplest, and probably most common, is to use the Performance Monitor counters such as ‘% Processor Time’. Query this, you can get an average of the processor usage at a moment in time.

Ta-da! Fixed it. I thought you said this was hard Grant.

Spoilers: that didn’t fix it.

Comments closed

The Problems with RDPing into SQL Server Hosts

Published 2019-12-02 by Kevin Feasel

Emanuele Meazzo enumerates the issues with using RDP to connect directly to a server hosting SQL Server (or any other database system):

When there are SERIOUS performance problems, i.e. your instance is totally pinned as far as resources go, the only way to log into the instance to do something about it is using DAC; even if you are on the same machine, if you’re trying to log in, as usual, it won’t make any difference.
Enable Remote DAC and save yourself from future troubles, now.

There are several problems. Better is to run client tools from a client machine and something like SSH or Powershell remoting to do things against a server itself.

Comments closed