Being in the cloud does have many benefits, from lower administration to fast scaling but another “side effect” of operating in Azure SQL Database is the cloud first nature of changes. By this I basically mean new features always get pushed to Azure first before the classic on-premises version so some gems come to light.
Tag Archives: TSQL
Azure SQL Database Script for Size
Sometimes you may not want to flip over to the Azure portal to grab the database size, such as the used space below.
Azure SQL Database and Transaction Log
Checking out the transaction log in Azure SQL Database. If you are curious like me, you will want to know about what your transaction log is doing in the cloud. The following queries have been tested and run okay within Azure SQL Database it gives you some great insight. First up, the classic log_reuse_wait_desc. You can’t exactly do much with this output, more so, just to fulfill curiosity.
Automating T-SQL for Azure SQL Database via Logic Apps
Have you ever wanted to capture the T-SQL, waits, sessions IDs (etc) at a specific time for Azure SQL Database? Sure there are a few ways to do this. Extended Events comes to mind but I wanted to do something different.
Azure SQL Database and Transaction Log
Checking out the transaction log in Azure SQL Database. If you are curious like me, you will want to know about what your transaction log is doing in the cloud. The following queries have been tested and run okay within Azure SQL Database. It gives you some great insight.
Azure SQL Database – Reduce the workload before initiating another service objective update
Getting straight to the point, I initiated a very common task recently, another scale up request. However, a new message popped up. “The service objective assignment for database on server could not be completed as the database is too busy. Reduce the workload before initiating another service objective update”.
Azure SQL Database – Firewall Rules
If you decide to use IP addresses to control what services have access to your Azure SQL Database, then understanding firewall rules are important. Within this important area you have the ability to create firewall rules that are scoped to the database or server level as shown below.
Azure SQL Database – Who Changed My Auto Tuning Settings?
One of the features Microsoft wants us to use for Azure SQL Database is Automatic Tuning. Automatic Tuning is a feature where you can think of it as entering the world of self-running and self-tuning databases.
It is said to be safe, reliable and proven using complex algorithms and built-in intelligence where it can do the following (see this link for more details: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning)
- CREATE INDEX – identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.
- DROP INDEX – identifies redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). Please note that at this time the option is not compatible with applications using partition switching and index hints.
- FORCE LAST GOOD PLAN – identifies SQL queries using execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan.
SQL Server 2017 – SELECT INTO [A SPECIFIC FILEGROUP]
Forget about Adaptive Query Processing for a minute, what other feature have I been waiting for? SELECT INTO a specific filegroup, not the default filegroup! I have needed this feature many times in the past. Let’s take a look at it using the WideWorldImportersDW database.
Azure SQL Database – The Default Isolation Is?
So what is the default isolation level for Azure SQL Database? I ran the following code to check it out.