Press "Enter" to skip to content

Category: Administration

Managing Azure DevOps via Azure Logic Apps

Published 2021-04-28 by Kevin Feasel

Stuart Ainsworth has a process:

A big part of my job these days is looking for opportunities to improve workflow. Automation of software is great, but identifying areas to speed up human processes can be incredibly beneficial to value delivery to customers. Here’s the situation I recently figured out how to do:

1. My SRE team uses a different Azure DevOps project than our development team. This protects the “separation of duties” concept that auditors love, while still letting us transfer items back and forth.
2. The two projects are in the same organization.
3. The two projects use different templates, with different required fields.
4. Our workflow process requires two phases of triage for bugs in the wild: a technical phase (provided by my team), and a business prioritization (provided by our Business Analyst).
5. Moving a card between projects is simple, but there were several manual changes that had to be made:
– Assigning to a Business Analyst (BA)
– Changing the status to Proposed from Active
– Changing the Iteration and Area
– Moving the card.

To automate this, I decided to use Azure Logic Apps. 

Read on to see how Stuart did this.

Comments closed

Pre-Loading SSAS Databases into Memory Post-Restart

Published 2021-04-28 by Kevin Feasel

Nigel Foulkes-Nock explains why that first query after restarting SSAS can be slow:

When the SQL Server Analysis Services (SSAS) Tabular Service is started, it can take a long time before it is ready to be queried. This can cause delays to Service, not to mention confusion.

This Blog Post will explain what is happening during this time and a method that can be used to improve. It’s worth mentioning that the SSAS Tabular Databases that this has been used on are quite large (> 100Gb).

Click through for the answer, as well as a technique to warm up those servers so an end user doesn’t wind up being the one to pay for this wait.

Comments closed

Read-Ahead Reads

Published 2021-04-26 by Kevin Feasel

Chad Callihan provides some helpful tips around read-ahead:

What are read-ahead reads and how do they impact SQL Server performance? Read-ahead reads allow SQL Server to think ahead to pull pages into the buffer cache before they are actually requested for a query. Up to 64 contiguous pages from a file can be read and the ability to read-ahead can be used for both data pages and index pages. Once data is in the buffer cache, it will not need to be pulled in for future queries unless it has been pushed out by other SQL Server tasks.

Click through to see what they are and how you could disable them if you really need to.

Comments closed

Shrinking an Azure SQL Database

Published 2021-04-23 by Kevin Feasel

Joey D’Antoni wants to take it down a notch:

You will note that I didn’t mention that “your log file grew because of a large index rebuild”. That’s because that is probably roughly (this is a really rough rule of thumb) how big your transaction log needs to be. But, anyway, we’re talking about Azure SQL Database, so you don’t need to worry about your transaction log file. Microsoft takes care of that for you: ‘Unlike data files, Azure SQL Database automatically shrinks log files since that operation does not impact database performance.’

Read on for the twist at the end.

Comments closed

Altering Columns in Temporal Tables

Published 2021-04-22 by Kevin Feasel

Meagan Longoria explains the process around table alteration when it’s a temporal table:

System-versioned temporal tables were introduced in SQL Server 2016. They provide information about data stored in the table at any point in time by storing an effective dated version of each row rather than only the data that is correct at the current time

You can alter a temporal table to add or change columns, but you must first turn off system versioning. Let’s look at an example.

The example here relates to a computed column, so a bit more work has to happen due to the way you define computed columns.

Comments closed

sample_ms in sys.dm_io_virtual_file_stats and Data Types

Published 2021-04-22 by Kevin Feasel

Paul Randal points out an interesting bug:

A prior student emailed me yesterday about some strange behavior of the sample_ms column in sys.dm_io_virtual_file_stats. It’s supposed to be the number of milliseconds since the SQL Server instance has been started. He has a SQL Server 2016 instance that’s been running since August 2019, and it shows the following:

– ms_ticks from sys.dm_os_sys_info: 51915112684 (which works out to be August 28, 2019)

– sample_ms from sys.dm_io_virtual_file_stats: 375504432 (which works out to be about 4.5 days)

Read on to get a determination and an alternative in case you’re using that field.

Comments closed

Unkillable Threads

Published 2021-04-21 by Kevin Feasel

Paul Randal gives us a supervillain origin story:

While I was teaching IEPTO2 last week, I was discussing why sometimes a thread cannot be terminated using the KILL command, and thought it would make a great topic for a post.

Some of you have likely seen a phenomenon called a non-yielding scheduler. This is where a thread is using the processor and doesn’t voluntarily yield after using more than the thread quantum (4 milliseconds, unchangeable). There’s a background task called the scheduler monitor that checks that progress is being made on the various schedulers inside SQL Server and issues a warning if it finds a problem.

Read on to learn more about how this can happen and what it means for you.

Comments closed

Changing SQL Server to Use a Static, Non-Standard Port

Published 2021-04-20 by Kevin Feasel

Jack Vamvas has a cmdlet for us:

Question: I have an  SQL Server Instance – currently configured with a Dynamic Port. I’d like to change the setting from a Dynamic port configuration to a Static port configuration , using Powershell.

I want to change to a static port as we need to set up some Firewall rules , and using a static port will be much easier

How can this be done?

Click through for the answer.

Comments closed

Extended Events in Azure SQL Database

Published 2021-04-20 by Kevin Feasel

Grant Fritchey compares and contrasts extended events on-premises to extended events in Azure SQL Database:

I have long advocated for the use of Extended Events. I’ve been posting all sorts of blog posts on how to implement them, how they present unique opportunities for new and interesting data, and how they do so much more than the old trace events, yet, put less of a load on the system. All of that is true, until we hit Azure SQL Database.

Now, don’t get me wrong, Extended Events are still awesome, amazing and wonderful. It’s just that, Azure SQL Database is going to force us to hop through a few hoops. I want to be up front with these… I’m trying to find the right word here, challenges? Maybe. Frustrations? Yeah, kind of. Limitations? Again, sort of, but not quite. We’ll settle on as neutral a term as possible: differences. For the moment.

Read on for the first part in an ongoing series.

Comments closed

Removing a Node from a Hadoop Cluster

Published 2021-04-19 by Kevin Feasel

The Hadoop in Real World team shows us the proper way to remove a node from a Hadoop cluster:

This post will list out the steps to properly remove a node from a Hadoop cluster. It is not advisable to just shut down the node abruptly.

Node exclusions should be properly recorded in a file that is referred to by the property dfs.hosts.exclude. This property doesn’t have default value so in the absence of a file location and a file, the Hadoop cluster will not exclude any nodes.

Read on for more information, including what happens if you simply turn off the node.

Comments closed