A Fine Slice Of SQL Server
Anthony Nocentino has some practices for us, the best practices:
The intent of this post is a quick reference guide based on the recommendation made on Pure Storage Support page in the Microsoft Platform Guide . The target audience for this blog post is for SQL Server DBAs introducing them to the most impactful configurations and settings for running SQL Server on physical machines on Pure Storage.
Click through for a checklist of recommendations.
Comments closedChad Callihan has a few close-to-one-liners:
The most common, short script I could think of may very well be one of the shortest T-SQL scripts that can be executed. When you want to know what version of SQL Server you are connected to, you can run this script:
Read on for that one-liner as well as a few other short scripts.
Comments closedAndy Mallon wants a database all of his own:
I’ve written about my DBA Database quite a few times. Some of the code in that database is written recently, and some of it was originally written a decade ago. Regardless of how old it is, or how good of a DBA I was when I wrote it, these little scripts are life-savers, and they are always at my fingertips.
And Andy has made them available to your fingertips as well. And the rest of your hands, I suppose.
Comments closedSo in the last blog we confirmed that we could move to SQL MI via some analysis, this is now time to actually do a backup and restore via URLs to move data.
Quite simply you need to BACKUP to URL (Azure Storage container) and the setup requirement is that you need to create a SQL credential that holds the SAS token – this is what allows authentication to the container to take place.
Click through for the process.
Comments closedJordan Braiuka compares two models for scaling:
We often get questions from customers about the best way to add capacity to their cluster. Is it better to add nodes, or simply to increase the capacity in their nodes? Unfortunately, the truth is there is no best way—like all complex issues in distributed systems, there are benefits and drawbacks to each scaling approach.
While each of our highly distributed systems (Apache Cassandra, Apache Kafka, etc.) have slightly different implementations of scaling, the concepts remain consistent across most distributed systems.
Click through for a comparison between the two approaches. As the article indicates, both are meaningful strategies and your choice might come down to a combination of the technology stack and the problem at hand.
Comments closedAaron Bertrand takes us to the Island of Misfit Databases:
The only database that requires extra handling is the one that contains a tab, because SQL Server doesn’t know how to generate file names when that character is present. I am sure there are a bunch of other less common but equally exotic characters that may cause the same problem.
This is how I actually tested sp_ineachdb, to make sure it was ready for just about any bad idea anyone used to name a database, and could handle various possible database states (for a lot more background on this procedure, and why it is better than the undocumented, unsupported, and buggy
sp_msforeachdb, see this and this). Here you can see that the procedure works against all these poorly-named databases, and skips databases that are inaccessible (rather than raise an exception).
Click through to see the list of databases Aaron uses. Technically, I think Aaron’s blog post also counts as a Halloween post because some of those databases are spooky.
Comments closedMicrosoft’s recommended SQL Server upgrade workflow is to upgrade to the latest SQL Server but keep the source DB compatibility level, assess the workload impact via establishing a baseline and based on testing move the compatibility level to the latest.
Upon creation of a new user database – the create database sets the compatibility level at the default compatibility level of the SQL Server. Keep in mind – if the model database level is set lower than the create database will set the compatibility level based on the model db
You can also change the database compatibility level at any time
Read on for more information about compatibility mode and some inbuilt guard rails around upgrades. Those guard rails aren’t perfect by any means, but over the past couple of editions, we’ve seen a fair amount of movement toward this ideal of compatibility mode being a guarantee of behavior between versions.
Comments closedAnthony Nocentino has a script and some tips for us:
This post is a reference post for retrieving IO statistics for data and log files in SQL Server. We’ll look at where we can find IO statistics in SQL Server, query it to produce meaningful metrics, and discuss some key points when interpreting this data.
Click through for the script, and then a bulleted list of things to keep in mind as you’re reviewing the data.
Comments closedLooking at the memory limits and other limits on the SQL Server versions over time, we have seen things increase, but one limit that is still very low is the memory limit for SQL Express. Specifically the maximum memory for buffer pool per instance of SQL Server Database Engine for SQL 2019. The limit there is 1410 MB.
At first glance you may think that this limit is the total amount of memory that SQL Server will use, but let me show you a couple of screen shots for Database Health Monitor showing the memory utilization on two different SQL 2019 Express servers.
Read on to see what, exactly, the memory limitation is. Also, there are separate limits for things like In-Memory OLTP table sizes.
Comments closedEd Pollack continues a series on extended events:
While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.
This article dives into one method for collecting and retaining all event data for a specific set of events. A system like this is highly customizable and provides a solid starting point for Extended Events development. This should be viewed as a collection of puzzle pieces; individual pieces can be adjusted as needed to produce a monitoring solution that fits the needs of a given situation, even if it is vastly different from what is demonstrated here.
Read on for the process. Shredding XML isn’t pretty, but the good news is that with a setup like this, you only need to do it once…unless you need to change it later, so get it right the first time and bam, problem solved.
Comments closed