Press "Enter" to skip to content

Category: Administration

Deploying CUs to Multiple Instances with Powershell

Published 2019-10-07 by Kevin Feasel

Jeff Iannucci embraces the power of the shell:

This all started because we had some 14 new SQL Server 2017 instances that we were setting up, but we hadn’t yet applied the most recent cumulative update that we are using in our environment.  I started using the Update-DbaInstance cmdlet in the script below to apply to one server, but then I looked at the list of outstanding requests and thought about something Buck Woody once told me.

“You don’t have time for that. You’re going to be dead soon.”

He’s a fantastic fellow, but we should all be grateful he didn’t become a physician.

Click through for the five-line script and an explanation of what each line does.

Leave a Comment

SQL Assessment API Public Preview 2

Published 2019-10-01 by Kevin Feasel

Ebru Ersan announces public preview 2 of the SQL Assessment API:

SQL Assessment API is a new mechanism to evaluate configuration of your SQL Server for best practices. The API methods are used by means of a SQL Server Management Object (SMO) extension and new cmdlets in SqlServer PowerShell module. API is delivered with a ruleset that is highly customizable and extensible. It can be used to assess SQL Server versions 2012 and higher, both on Windows and Linux.

Read on for instructions on how to install and what has changed since public preview 1.

Leave a Comment

SQL Server Monitoring with Grafana and Telegraf

Published 2019-10-01 by Kevin Feasel

Denzil Ribeiro shows how you can use Telegraf and Grafana to monitor Azure SQL Database databases:

SQL DB Storage
This is the dashboard for monitoring file size and space used, IO latency, and IO throughput, for each file in the database. When using Standard or General Purpose databases, which use data files in Azure blob storage, storage performance depends on several blob properties, exposed via the FILEPROPERTYEX() function.

Aside from a couple Azure SQL DB-specific steps, it’s basically the same process that Tracy Boggiano has for monitoring on-prem and IaaS SQL Server instances.

Leave a Comment

Hammering Azure SQL DB

Published 2019-09-30 by Kevin Feasel

John Morehouse shows how we can configure HammerDB to run against Azure SQL Database:

Bench marking your environment is an important step when introducing new hardware, which is accomplished by running a test workload against the hardware.    There are multiple ways to accomplish this to get  SQL Server performance data  One of these methods is using HammerDB, which is a free tool that provides TPC standard bench marking metrics for multiple database systems, including Microsoft SQL Server.  These metrics are an industry standard and are defined by the Transaction Processing Performance Council (TPC).  The results from bench marking will help you to ensure that the new infrastructure will be able to support the expected workload.

Azure introduces ways to quickly implement new hardware.  However, if the Azure environment isn’t setup correctly, you can introduce issues that could potentially degrade performance.  Thankfully, HammerDB is Azure aware which allows you to easily benchmark your cloud environment.

HammerDB isn’t as good as having your own perfectly-created set of scripts which exactly replicate your environment, but I’ve never seen an environment with one of those.

Leave a Comment

Configuring Memory Limits for SQL Server in Kubernetes

Published 2019-09-30 by Kevin Feasel

Anthony Nocentino doesn’t have all the RAM in the world:

With that Pod deployed, I loaded up a HammerDB TPC-C test with about 10GB of data and drove a workload against our SQL Server. Then while monitoring the workload…boom HammerDB throws connection errors and crashes. Let’s look at why.

First thing’s first, let’s check the Pods status with kubectl get pods. We’ll that’s interesting I have 13 Pods. 1 has a Status of Running and the remainder have are Evicted. 

Anthony does a great job of explaining the problem and showing you the solution.

Leave a Comment

Get Lock Details Against a Database

Published 2019-09-27 by Kevin Feasel

David Fowler has a new procedure:

Have you ever wanted a quick and easy way to see who was holding (and waiting on) locks on a particular database? Perhaps you’ve got some blocking issues going on and you want to see exactly which rows the row level locks were taken out on?

sp_LockDetails will return some handy information about the locks held on a specific database, including SPID, login, database, lock type and resource.

David includes the script in the post as well.

Leave a Comment

Troubles with Dropping Logins

Published 2019-09-27 by Kevin Feasel

Pamela Mooney takes us through a scenario involving dropping a user and login, and some of the difficulties which might arise:

I had to obscure a lot, but the bottom query results correlate to the top results.  The first line of the bottom query results show the grantor of the permissions, and the bottom line is the grantee.  In this case, a login was explicitly denied impersonation on a server role.  I’m using this example because it is really quirky to fix.  Most often, you’ll just reverse the permissions, using pretty standard syntax. Even easier, right click on the login, go to the “Securables” tab, and remove the permissions.  However, if you are a fan of the TSQL approach, this one is not so straightforward, so it’s a good one to show.  

Click through for a demonstration.

Leave a Comment

Instant Transaction Rollback in SQL Server 2019

Published 2019-09-26 by Kevin Feasel

Matthew McGiffen explains that Accelerated Database Recovery in SQL Server 2019 works for more than just startup times:

If you’ve read about the Accelerated Database Recovery feature in SQL Server 2019 you could be forgiven for thinking it’s just about speeding up database recovery time in case of a server failure.

In fact, enabling it also means that where you have a long running transaction that fails or is cancelled the rollback is almost instantaneous. This is great news for DBAs who have to sometimes kill a long-running blocking transaction but worry that it may take a long time to rollback – continuing to block all that time.

Read on for an example. I hadn’t thought about this, but it’s pretty cool.

Leave a Comment

Power BI Admin API Updates

Published 2019-09-25 by Kevin Feasel

Matthew Roche takes us through a few updates to the Power BI Admin API:

Even with these new experiences, there will still be times when you want or need to use the Power BI API to get insight into all the workspaces in the Power BI tenant for which you are an administrator. This ability isn’t new, but some recent updates to the Power BI admin API have made it easier.

I don’t do a lot of development these days, so when a post titled Avoiding workspace loops by expanding navigation properties in the GetGroupsAsAdmin API showed up on the Power BI blog, I didn’t pay much attention. I should have.

Read on for the full set of updates.

Leave a Comment

Ring Buffer CPU Over 100%

Published 2019-09-24 by Kevin Feasel

Thomas Rushton ran into an oddity with sys.dm_os_ring_buffers:

Wait! Process Utilization + Idle adds up to waaaaay over 100…What’s going on?

My first thought was that the CPU was being throttled, so I checked the windows power options – for some really irritating reason, Windows defaults to a “balanced” power setting, which is rubbish for SQL Server.

That looks OK, but… if you check the CPU page of the Task Manager, things are a bit more interesting:

Unfortunately, the story doesn’t have a conclusion, but Thomas’s conjecture does make sense.

Comments closed