A Fine Slice Of SQL Server
Chad Callihan covers cost threshold for parallelism:
Let’s talk about cost threshold for parallelism. This is one of the main deciders in a query going parallel. Do you know what it’s set to on your databases?
Read on to learn more and get Chad’s crowd-sourced recommendation for a proper value.
Comments closedAndrea Allred is waiting for an e-mail:
I don’t want emails that tell me everything is ok, only when things are bad and I want them to be helpful emails. Not only did I want an alert, I wanted an email with actual information that I can use to make my decisions. Decisions like, can I just apply a few logs to get caught up or did everything burn down and I need to pull a full backup plus all the logs to be up and running again?
This was a task for some super fancy alerts on my agent job.
Click through to see how.
Comments closedDenny Cherry walks us through gateway logging in Azure:
If you’ve ever set up an Azure Network Gateway for Site to Site or Person to Site VPNing you’ve probably wanted to be able to see logging from the gateway. In the Azure portal, you can see a Logs option, but all it does is tell you to set up log analytics and the link that it gives you is … less than helpful.
Denny, however, has helpful instructions, so check it out.
Comments closedReitse Eskens has the key for the back door:
I got a call this morning from a coworker. One of the database instances was unreachable with the message that the TempDB log file was full. No processes could login and the only way to get things going again might be to restart either server or instance. Restarting the instance should be the last resort, because after a restart SQL Server will plough through all the logfiles to find transactions to either roll forward or roll back. That will take more time that you want. But, we had the famous DAC backdoor installed.
Read on to see how the DAC can save the day.
Comments closedEbru 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 closedRayis 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 closedAndrea Allred riffs on automatic tuning (but, uh, not that AutoTune):
A few weeks ago, we were talking to a new employee about how much time we spend with Query Store and they asked, “Why aren’t you using the Auto Tuning?”
THE WHAT NOW?!!!!
Click through for Andrea’s thoughts on how automatic tuning works in SQL Server Enterprise Edition.
Comments closedErik Darling airs some grievances well ahead of Festivus:
This post is admittedly short, but since we’re short of First Party Solutions™ to gripe to Microsoft with… I need to save this stuff somewhere.
Click through for Erik’s notes and commentary.
Comments closedI 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 closedPaul 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