Press "Enter" to skip to content

Category: Administration

When Did That SQL Server Start?

Published 2021-08-20 by Kevin Feasel

Chad Callihan breaks out the stopwatch:

When you are troubleshooting query performance, it’s important to consider when SQL Server last restarted. Each time SQL Server restarts, buffer pool and plan cache get wiped out. This can play a big part in how you approach an issue.

You want to know how much history you have to work with. Not seeing much data in plan cache? Maybe SQL Server restarted recently. Parameter sniffing issues that you were seeing earlier today are now suddenly “fixed?” SQL Server could have restarted between then and now. Let’s take a quick look a few ways to review how long SQL Server has been up and running.

Click through for several techniques. I’ve always checked the tempdb startup time in sys.databases, myself, but that’s not the only way.

Comments closed

Fixing Hadoop Namenodes in Safe Mode

Published 2021-08-16 by Kevin Feasel

The Hadoop in Real World team doesn’t need to play it safe:

When namenode is started or restarted, namenode will be in safemode for a period of time. At this time you will not be able to use your Hadoop cluster fully. Write operations to HDFS will fail and because of that your MapReduce jobs will also fail.

Read on for other reasons why the namenode might be stuck in safe mode and what you can do to fix it.

Comments closed

Using the DAC from SSMS

Published 2021-08-16 by Kevin Feasel

Chad Callihan shows how you can configure the dedicated administrator connection and connect to it via SSMS in a time of need:

Have you heard of SQL Server’s dedicated administrator connection? The dedicated administrator connection (DAC) can come in handy in an emergency scenario so you should have it enabled and know how to use it…just in case. I haven’t needed it too often in my career but it was helpful in instances when SQL Server wasn’t being very responsive. If there are problems connecting to a server, the DAC can be used to connect and troubleshoot issues.

Read on to learn more.

Comments closed

Troubleshooting a Slow Restore

Published 2021-08-09 by Kevin Feasel

Sean Gallardy performs corporate dentistry:

This came with very little to no data available, and to be quite honest, saying “slow restore” doesn’t really mean much. The initial analysis needs to be an actual set of concrete data that describes the issue, what is normal, and what outliers, if any, exist. Since we have none, we can’t even start to analyze anything, so we need to clarify the problem statement and understand a little more about the issue.

This is an interesting dive into the problem and a good example of how to work with “We won’t let you see/do that” as a consultant. Incidentally, if you haven’t heard of WPR, that comes with the Windows Performance Toolkit.

Comments closed

Monitoring SQL Server on Linux with Telegraf, InfluxDB, and Grafana

Published 2021-08-09 by Kevin Feasel

Amit Khandelwal extends a solution for SQL Server on Windows:

In this blog, we will look at how we configure near real-time monitoring of SQL Server on Linux and containers with the Telegraf-InfluxDB and Grafana stack. This is built on similar lines to Azure SQLDB and Managed Instance solutions already published by my colleague Denzil Ribeiro. You can refer to the above blogs to know more about Telegraf, InfluxDB and Grafana. 

Click through for the quick version, and then the step-by-step process.

Comments closed

Updating SQL Server Container Memory Limits

Published 2021-08-09 by Kevin Feasel

Andrew Pruski doesn’t have time to restart containers:

When running multiple SQL Server containers on a Docker host we should always be setting CPU and Memory limits for each container (see the flags for memory and cpus here). This helps prevent the whole noisy neighbour situation, where one container takes all the host’s resources and staves the other containers.

But what if we forget to set those limits? Well, no worries…we can update them on the fly!

Click through to see how you can change the memory limits on a running container.

Comments closed

Finding and Removing Custom Roles, Schemas, and Users from a Database

Published 2021-08-05 by Kevin Feasel

Thomas Williams wants to go back to square one:

I’m a fan of the built-in database roles like db_datareader to standardise & simplify permissions (sorry Dr. Greg Low!) and recently I needed to do just that in a database created using SQL Server 2000, and remove old defaults and a lot of custom roles, schemas and users.

I wrote the set of queries below to generate scripts to remove non-built-in roles, schemas and users, when compared to the model database on a new SQL Server 2019 server.

After running the script generated by the queries, I added back users and gave appropriate roles (like db_datareader).

Read on for the script.

Comments closed

Executing T-SQL with a Proxy Account

Published 2021-08-04 by Kevin Feasel

Tom Collins answers a question:

I have some t-sql code added to a job step on a SQL Server Agent job. The problem is I need to run the code as RUNAS . I though of executing the job with a proxy account – so progressed with the Credential & proxy set up.    But I still can’t view the Proxy\Credential in the RunAs list . Is there a way around this problem?

Read on to learn why and for the answer.

Comments closed

Finding Bad (Worse?) NOLOCK Statements across Instances

Published 2021-08-04 by Kevin Feasel

Aaron Bertrand powers up for about six episodes straight, but the results are amazing:

In Part 1 of this series, I showed how to use a Visitor pattern to walk through one or more T-SQL statements to identify a problematic pattern where NOLOCK hints are applied to the target of an update or delete. The method in my initial examples was very manual, though, and won’t scale if this problem might be widespread. We need to be able to automate collecting a potentially large number of statements across an entire environment, and then try to eliminate false positives without manual intervention.

Read on to see how you can take what Aaron wrote last time and make it scalable.

Comments closed

Estimating Nonclustered Index Sizes with Powershell

Published 2021-08-03 by Kevin Feasel

Alex Stuart performs some calculations:

I recently encountered a requirement to estimate the size of (a lot of) nonclustered indexes on some very large tables due to not having a test box to create them on or the time to build one. I couldn’t find a script to do this, and as any programmer knows, laziness is the real mother of invention, so I wrote one.
This post summarises how we can calculate this and provides a Powershell function to do so.

I used Microsoft’s documentation as a basis on how to do it, but as it’s a wall of text that’s a little tricky to follow, I’ll go over the basics here. I’m only covering the leaf levels and non-MAX columns that would create LOB pages – I’ll explain why later.

Click through for the article and to see how Alex’s calculations play out.

Comments closed