Press "Enter" to skip to content

Category: Administration

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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

Azure Kubernetes Service Max Volume Count

Published 2019-09-23 by Kevin Feasel

Chris Taylor explains an error message in Azure Kubernetes Service:

Whilst playing around with my session for Techorama.nl I encountered an error I hadn’t seen previously whilst deploying SQL Server on Linux in Azure Kubernetes Service (AKS)

0/1 nodes are available: 1 node(s) exceed max volume count

The yaml I used was only slightly modified (mainly names) from scripts used on minikube and docker-desktop so I was a little confused as to why I was getting this in AKS.

Read on to understand what’s happening here and how you can fix it.

Comments closed

Read-Only versus Read-Write and SQL Server

Published 2019-09-23 by Kevin Feasel

Jack Vamvas takes us through what it takes to turn a read-write database in SQL Server read-only and vice versa:

There are some considerations for deciding if a Developer should be able to include as part of an ETL process , the capacity to change the READ STATE of a SQL database

1) Requires ALTER permission on the database. This is an elevated privilege – and may break the organisations sql server security policy

2) Is the developer on the hook for maintaining Production data? What is the developer’s role in supporting production data ?

Read on for the commands as well as additional considerations before you grant these permissions.

Comments closed