Arun Sirpal notes a change to the Azure SQL Database Service Level Agreement:
I am sure many missed the updates to Azure SQL Database SLA (Service Level Agreement). It used to be 99.99% across all tiers but split between two different high-availability architectural models. Basic, Standard and General Purpose tiers had its own model and the Premium / Business Critical tiers had a different one.
Read on to see the change.
Max Vernon has a script which shows more than what xp_fixeddrives has to offer:
However, the command output doesn’t include the total size of each drive, making it impossible to determine the percent free space. If you’re in an environment where a separate team monitors disk space, and has alerts set when free space falls below a certain percentage, you may want to ensure you don’t breach those levels. The following script provides “the big picture” for your servers, since it provides total size, free space, available space, and the percent free. It does require the use of the documented and supported
sys.xp_cmdshellsystem extended stored procedure. The code uses the drive letters returned bysys.xp_fixeddrivesinside a cursor. Inside the cursor, we call the dos commandfsutil volume diskfree C:to get total capacity and free space, etc:
Click through for the script.
Jon Shaulis builds a logon trigger to notify when sysadmins log into his systems:
I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.
Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.
Just make sure you get the trigger right and don’t block everybody from logging in. That’s an awkward situation.
Randolph West looks at a new settings tab in the SQL Server 2019 installation:
In 2016 I created the Max Server Memory Matrix as a guide for configuring the maximum amount of memory that should be assigned to SQL Server, using an algorithm developed by Jonathan Kehayias.
SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version.
On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server 2019. Last week we looked at MaxDOP, and this post will specifically look at the Memory tab.
It’s a small change but a nice one.
Jason Brimhall shows how you can use Extended Events to figure out if that database is still in use:
Here we can see there are indeed some databases that are still in use on this server. If we are looking to retire the instance, or migrate relevant databases to a new server, we have better information about how to go about planning that work. Based on this data, I would be able to retire the ProtossZealot database but would be required to do something with the ZergRush and BroodWar databases. In the case of the ProtossZealot database that is not being used, we now have evidence to present back to the team that the database is not used. It is now up to those insisting on keeping it around to justify its existence and document why it must remain in tact.
Click through for the script Jason used to determine this.
David Fowler has a new stored procedure for you:
We love xp_fixeddrives here, it’s a quick and simple way to see how much space you’ve got available on your drives. But there are just a couple of things that I really wish it would do better.
Firstly, I’d love to see the total size of the drive and possibly even a percentage of free space left.
Secondly, mount points. If you’ve got any databases that are on mount points, it’s not going to give you any idea of what you’ve got left on there.
This is the reason that I put together sp_drivespace.
Click through for the script. It would be interesting to see if this works on Linux as well.
Randolph West notes a change with SQL Server 2019:
SQL Server 2019 is still in preview as I write this, but I wanted to point out a new feature that Microsoft has added to SQL Server Setup, on the Windows version.
On the Database Engine Configuration screen are two new tabs, called MaxDOP and Memory. These are both new configuration options for SQL Server 2019. This post will specifically look at the MaxDOP tab, and we’ll look at Memorynext week.
I like that they’re adding these things to initial setup; that makes it easier for people to remember that yeah, MAXDOP is important.
Max Vernon shows us how checkpointing works when your database is in the simple recovery model:
Even though the transaction has been rolled back, the log records will not be cleared until a checkpoint occurs. An automatic checkpoint could be triggered by other ongoing transactions being written to the log, or a manual
CHECKPOINTstatement could be executed. However, for a database that is not seeing frequent transactions, the log may stay nearly full for an extended period of time. This scenario might be seen often during development where there are a very limited number of transactions being generated.
Read the whole thing. Just because you’re in simple recovery mode doesn’t mean the transaction log becomes any less useful.
Mohammad Darab continues a series on Big Data Clusters:
There are many ways to view the health of your Big Data Cluster. As of CTP 3.0, there are kubectl commands, mssqlctl commands as well as dashboards. For the sake of this series, I will focus on the dashboards. I will blog about some of the useful kubectl and mssqlctl commands in later posts.
The first dashboard is the Microsoft Cluster Administration portal (see below snapshot). This is a view into the Big Data Cluster Controller. As you can see from the image below, the Overview pane shows the Controller, Master Instance and all the pools. On the left hand side you can see more details. If you click on the “Service Endpoint” option, you will see a list of endpoints that you can bookmark.
Something I appreciate is that Microsoft thought ahead on what the monitoring story should look like rather than waiting until the end and slapping something together.
Joe Obbish digs into Microsoft’s new guidance for maximum degree of parallelism:
I’ve heard some folks claim that keeping all parallel workers on a single hard NUMA nodes can be important for query performance. I’ve even seen some queries experience reduced performance when thread 0 is on a different hard NUMA node than parallel worker threads. I haven’t heard of anything about the importance of keeping all of a query’s worker threads on a single soft-NUMA node. It doesn’t really make sense to say that query performance will be improved if all worker threads are on the same soft-NUMA node. Soft-NUMA is a configuration setting. Suppose I have a 24 core hard NUMA node and my goal is to get all of a parallel query’s worker threads on a single soft-NUMA node. To accomplish that goal the best strategy is to disable auto soft-NUMA because that will give me a NUMA node size of 24 as opposed to 8. So disabling auto soft-NUMA will increase query performance?
Joe takes a careful look at the documentation and brings up some really good questions.
Kevin Feasel
2019-07-19
Administration, Cloud
No Comment