Press "Enter" to skip to content

Category: Administration

Number of Rows Automatically Sampled versus Table Size

Published 2021-02-26 by Kevin Feasel

Matthew McGiffen does the math:

I mentioned in my previous post about manually updating statistics that you can specify whether they’re updated using a full scan, or you can specify an amount of data to sample, either a percentage of the table size, or a fixed number of rows. You can also choose not to specify this, and SQL Server will decide for you whether to do a full scan, or to sample a certain amount of data.

I thought it would be interesting to look at what the sample sizes are that SQL will choose to use, depending on the amount of data in your table. 

Click through for the result of Matthew’s analysis.

Comments closed

A Warning on Using Distributed Network Names

Published 2021-02-24 by Kevin Feasel

Allan Hirt has a warning for us:

DNNs are supported as of SQL Server 2019 CU2 and require Windows Server 2016 or later. I wrote more about them in my blog post Configure a WSFC in Azure with Windows Server 2019 for AGs and FCIs. Go there if you want to see what they look like and learn more.

Right now, I cannot wholeheartedly recommend the use of DNNs for listeners or FCIs if you are using Enterprise Edition. Why?

Read on to learn why.

Comments closed

Memory Grant Feedback in SQL Server

Published 2021-02-24 by Kevin Feasel

Deepthi Goguri hits on one part of Intelligent Query Processing in SQL Server:

In this part, let us focus on the Memory Grant Feedback feature released in SQL Server 2017 for Batch mode execution and in SQL Server 2019 for Row mode execution.

Memory Grants are used by the SQL Server for the Hashes and Sort operations. SQL Server optimizer uses the statistics information and allocate the memory needed by the query before the query executes. When the query is executed, SQL Server uses the allocated memory to process the query for the hashes and sorts. If this memory grant is not enough to process the query, data will use tempdb spilling to disk. When too much memory is allocated based up on the estimates, we can effect the concurrency as all other queries requires memory grants to process the queries as well. Bad estimates can effect the memory grants allocated to the queries. Too much or too little memory grants is bad.

Read on to see how Memory Grant Feedback helps the optimizer out with queries over time.

Comments closed

So You’ve Run Out of Memory

Published 2021-02-18 by Kevin Feasel

Randolph West explains how the buffer pool handles low-memory situations:

One of the bigger clichés in the data professional vocabulary (behind “it depends”) is that you always give SQL Server as much RAM as you can afford, because it’s going to use it. But what happens when SQL Server runs out of memory?

Recently a question appeared on my post about how the buffer pool works, asking the following (paraphrased):

What happens if a data page doesn’t exist in the buffer pool, and the buffer pool doesn’t have enough free space? Does the buffer pool use TempDB, [and] does TempDB put its dirty pages into the buffer pool?

This is an excellent question (thank you for asking!). I spent 30 minutes writing my reply and then figured it would make a good blog post this week if I fleshed it out a little.

Read the whole thing.

Comments closed

Monitoring SSAS with Quest Spotlight

Published 2021-02-16 by Kevin Feasel

Slava Murygin has two questions and two answers:

This post is just answering two simple questions:

1. Can Quest Software’s Spotlight successfully monitor SQL Server Analysis Server?

2. If it can, what SSAS parameters, databases’ and cubes’ details it monitors and provides information about?

First, it’s good to see Slava back in the saddle again. Second, click through for those answers. Slava also promises to check out some other SSAS monitoring tools, so stay tuned.

Comments closed

Installing SQL Server on an Azure VM

Published 2021-02-15 by Kevin Feasel

Niels Berglund takes us through the steps of creating an Azure VM running SQL Server:

A while ago, I wanted to do a quick test on a new SQL installation, and I wanted the SQL installation to be on a “pristine” server. I was not keen on creating a new virtual machine on my local dev-box, as for that I would need to create a VM image etc., and it seemed like too much hassle for a lazy person like me. The obvious choice then is to do it in the cloud. How hard can that be, what could possibly go wrong?!

It turned out to not be as straight-forward as I thought it would be, but eventually, I managed to get it right. Since I probably need to do it again some time, I thought I’d write a post about it, so I have something to go back to. So here we go …

Niels goes through this in meticulous detail, as is the norm.

Comments closed

Using containerd as a Kubernetes Container Runtime

Published 2021-02-15 by Kevin Feasel

Anthony Nocentino does a switcheroo:

In this post, I’m going to show you how to install containerd as the container runtime in a Kubernetes cluster. I will also cover setting the cgroup driver for containerd to systemd which is the preferred cgroup driver for Kubernetes. In Kubernetes version 1.20 Docker was deprecated and will be removed after 1.22. containerd is a CRI compatible container runtime and is one of the supported options you have as a container runtime in Kubernetes in this post Docker Kubernetes world. I do want to call out that you can use containers created with Docker in containerd.

There are a few steps, but it’s not crazy by any stretch, and that’s part of why the post-Docker-in-Kubernetes world won’t be chaos.

Comments closed

Splitting SQL Server Drives on Modern SANs

Published 2021-02-12 by Kevin Feasel

Chris Taylor checks up on some older advice:

Back in the day, “when I was a lad“, the recommendation for SQL Server was to split your data, logs and tempdb files onto separate drives/luns to get the most out of your storage. Jump forward to 2021, is this still relevant and should I be splitting my SQL Server drives on to separate luns on new SAN storage? A question which is often asked not just by customers as well as their 3rd party managed service providers / hosting companies. This question can also be along the lines of, “Why can’t we just put everything on a C:\ because the backend is all on the same lun“. This is slightly different as they’re questioning the drive lettering more than creating separate luns but still relevant to this topic.

Click through to learn what Chris has found.

Comments closed

Visualizing Infrastructure with Terraform Graph

Published 2021-02-10 by Kevin Feasel

Jonathan D’Aloia shows how we can visualize Terraform-based infrastructure with diagrams:

As can be seen from the image above we have every resource that is defined in the Terraform code that is to be deployed. At a first glance, it does appear that not all the information here is of such relevance, for example, the metadata referring to registry or root provider. However, if we look away from this we can begin to see how the Infrastructure model is going to look once it has been deployed.

We can see that we have one resource group called “example” which has an Azure SQL Server and also an Azure Storage account also both called “example” and that all of these resources directly link to the resource group. I would also point out that you can also see that Azure SQL database directly links to the SQL server giving a clear indication of which databases belong to which server.

Click through for an example as well as the process.

Comments closed