A Fine Slice Of SQL Server
This is more of a heads up for me / reminder regarding some of the caveats to using ONLINE / RESUMABLE index operations with SQL Server.
Don’t get me wrong, there’s a ton of advantages to using ONLINE and RESUMABLE which I will highlight below but here is the link to the Microsoft Learn page if you want more in depth information
Read on for the pros and cons. I do like the idea, though I personally haven’t used the feature.
Comments closedGarry Bargsley shows a use for extended properties on databases:
Databases listed with 1, 2, 3, 4? What do those databases represent? Where did those databases come from?
Would you believe me if I told you there was a way to identify where the databases were sourced from?
A setting that can be used in SQL Server would allow the person who built these databases or restored them from another system to add a note.
Extended properties are very useful but also very easy to forget and difficult to manage. I’ve seen a few vendor products make great use of them but in-house development tends to ignore them.
Comments closedRobert Blackburn builds a dashboard:
We must periodically evaluate the state of our databases. Luckily for SQL Server, Microsoft provides us with a customizable assessment through their SQL Assessment API Repo and API Documentation. You can change the rules per database and output the results to a database to track history.
However, that will take more than an hour. Let’s create a dashboard with the default rules in under an hour. We will use Azure Data Studio (ADS) and Power BI Desktop (PBI). If you are not familiar with them, both are free. Azure Data Studio is automatically installed with SSMS 18.7 and higher. You can also install them individually.
Read on to see how this works. Granted, it will not auto-update but unless the assessment output format changes between runs, at least you wouldn’t need to modify Power BI and could just refresh the data.
Comments closedBrendan Tierney checks out some recommendations:
In a previous blog post I gave an overview of the DBMS_AUTO_PARTITION package in Oracle Autonomous Database. This looked at how you can get started and to setup Auto Partitioning and to allow it to automatically implement partitioning.
This might not be something the DBAs will want to happen for lots of different reasons. An alternative is to use DBMS_AUTO_PARTITION to make recommendations for tables where partitioning will have a performance improvement. The DBA can inspect these recommendations and decide which of these to implement.
Read on to see how you can run the recommender, as well as what a recommendation looks like.
Comments closedJason Russell takes us through a SQL Server 2019 feature:
Included in SQL Server 2019 (and also the upcoming SQL Server 2022), Accelerated Database Recovery (ADR) improves database availability. Microsoft outlines the main benefits of ADR as:
– Fast and consistent database recovery
– Instantaneous transaction rollback
– Aggressive log truncation
Click through for an overview of how it works.
Comments closedLukas Vileikis begins a series on MySQL backups with the classic option:
mysqldumpis one of the most popular database backup tools in the MySQL world.The tool is prevalent partly because it’s very basic and quite powerful –
mysqldumpdatabase backup tool is command line-based, very simple and very straightforward to use. As far as MySQL or its flavors (MariaDB and Percona Server) are concerned, this command line-based tool is one of the top choices for junior and senior database engineers across the world alike.
Click through to see how it works.
Comments closedAjay Dwivedi continues a series on high CPU utilization:
In the last blog post Live Troubleshooting High CPU On SQL Server – Part 1, we worked on a scenario where we saw a high CPU on SQL Server due to some external OS level task. In this blog, we are going to explore a scenario where a high CPU issue is present because of the workload running on SQL Server.
Just like in the last blog post scenario, when I have to troubleshoot a “slow” SQL Server, if my SQL Server is baselined with the SQLMonitor tool, then I first visit my Monitoring – Live – All Servers dashboard which displays all the metrics of specific SQL Servers that need DBA help.
As normal, we see a scenario with SQLMonitor, as well as other options including sp_BlitzFirst.
Comments closedEtienne Lopes wants to see what the hold-up is:
Blocked processes (and deadlocks) are often one of the main factors responsible for performance issues in the databases, as such, it’s really important to monitor them effectively and if they exist then understand where, what, why, how often, duration, etc. Having that information will greatly help in the following fine tuning process.
In this post I’ll show one way to easily monitor blocked processes in an Azure SQL database.
Click through to learn how.
Comments closed– Enable AAD auth on Azure SQL server
– Conn to Azure SQL database via AAD admin
– Create contained user for the managed identity (using Azure VM name as contained username)
create user <vmname> from external provider;
alter role db_owner add member <vmname>;
Read on for the full set of steps.
Comments closedAjay Dwivedi finds out it wasn’t the database:
Since SQL Server is not a cheap application and would cost a lot of money if we need to scale it horizontally. So it is not a good practice to allow any other resource-consuming processes to run alongside with SQL Server.
In this blog, we are going to troubleshoot a high CPU scenario when the CPU issue is caused by non-SQL Server process. Let’s say, I receive a call from the monitoring team where the application team is complaining about a “slow” SQL Server.
Ajay first uses SQLMonitor to perform this troubleshooting and then shows how to do this without SQLMonitor.
Comments closed