Press "Enter" to skip to content

Category: Administration

SQL Assessment for SQL Server on VMs

Published 2021-11-03 by Kevin Feasel

Ebru Ersan announces a new preview:

Wouldn’t it be great if there was a way to learn if your SQL Server on Azure Virtual Machines was configured optimally? Do you have the right options set? Do you have your tempdb on the right disk? Can your queries perform better? All these and more can be answered using the new Azure portal experience on the SQL virtual machine resource page. SQL Assessment feature, once enabled, will evaluate your SQL Server on Azure VM against configuration best practices to determine if your system is healthy and setup for success. This feature is currently in preview. We would love to hear your feedback.

Click through to see it in action.

Comments closed

Data Source Name Not Found with Postgres Driver

Published 2021-11-01 by Kevin Feasel

Rayis Imayev troubleshoots a problem:

A very short blog post, just a reminder to myself, but if you have ever tried to connect to a PostgreSQL database using ODBC interface (I know, it already sounds like a very interesting challenge :- ), then you might have experienced this error message: “ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.”

Read on to see the cause of and solution to this problem.

Comments closed

Connection Leaks with MARS

Published 2021-10-27 by Kevin Feasel

Josh Darnell warns that, if you go to MARS, a doctor will warn you that you have a schizoid embolism and it will be up to you to determine whether the doctor is lying or not:

I recently looked at a SQL Server instance that had a large number of MARS connections under a single “parent” connection. Most of these “child” connections had been idle for quite a while, but they were still hanging around.

Read the whole thing. Because I’ve used MARS so little, I’ll instead add a follow-up point to my Total Recall reference above. In the commentary track for the movie, director Paul Verhoeven notes that Douglas Quaid actually did die and that it really was just a fantasy concocted in his mind and he really did die at the end. You can tell because, instead of a fade to black like normal movies, he fades to white, indicating that this wasn’t a proper ending. But then again, considering the follow-on media which happened (and was slated to happen but didn’t make it to the finish line), I don’t think the studios would have let Verhoeven keep his unhappy ending.

Comments closed

All about Synchronous Stats Updates

Published 2021-10-26 by Kevin Feasel

Paul Randal shares some thoughts about synchronous stats updates:

The SQL Server query optimizer makes use of statistics during query compilation to help determine the optimal query plan. By default, if the optimizer notices a statistic is out-of-date because of too many changes to a table, it will update the statistic immediately before query compilation can continue (only the statistics it needs, not all the statistics for the table).

Note that “too many” is non-specific because it varies by version and whether trace flag 2371 is enabled – see the AUTO_UPDATE_STATISTICS section of this page for details.

Read on to learn more, including the problems that synchronous stats updates can cause, what you can do to avoid them, and ways you can tell that synchronous stats updates are a problem in your environment.

Comments closed

Enabling Statistics Auto-Creation

Published 2021-10-25 by Kevin Feasel

Chad Callihan checks the stats:

When we query for data, we don’t always think about the magic that goes into efficiently returning results. One vital piece to this magic is statistics. Statistics in SQL Server are histograms that are used by the query optimizer to determine an optimal execution plan when executing a query. Let’s take a look at the different ways to check your statistics settings and make sure statistics are being automatically created.

Click through to see how.

Comments closed

Four DBA ToDos in a New Role

Published 2021-10-25 by Kevin Feasel

Lee Markum has a starting point for DBAs in a new role:

You’ve just been hired into a DBA role at a new company, or you’ve been given the DBA keys at your current company. Maybe you’re a SysAdmin and your boss has informed you that you are now supposed to manage the SQL Servers as well as everything else on your plate. In any of these situations, you may have some confidence in your skills, but especially in the case of being a new hire, you have absolutely no true idea of what you’re walking into.

In these scenarios, where do you start? Start with these four areas.

Click through for the four areas. I completely agree with Lee on these for DBAs, including the order.

Comments closed

Scaling an Azure SQL Managed Instance

Published 2021-10-21 by Kevin Feasel

Arun Sirpal wants more power:

No doubt there will be times where you need to scale up the actual instance in terms of vCores but also you may want to move across tiers (for example General Purpose to Business Critical). If you remember a few blog posts ago I said it was really important to plan for these activities during the build phase, more specifically get the subnet range right. If you done that then you will be fine.

Click through for the process, though do note the amount of time it takes. One of the early ideals of cloud processing would be that you could seamlessly scale up and down with no effects on the end user. In some services (especially things like function apps, web apps, and VMs in a Kubernetes pod), you get that experience. When it comes to almost anything data-related, though, immediate scaling is a hard no, to the point where I’d assume you can’t afford the downtime to do it until proven otherwise.

Comments closed