A Fine Slice Of SQL Server
Rob Sewell explains a new features in dbachecks:
You can see the name, the current value and the description
Ah thats cool he said so
How Do I Know Which Configuration Is For Which Check?
Well, you just…. , you know…… AHHHHHHH
Rob then made this possible. Click through to see how you can determine which configuration works for which checks.
Comments closedValues that you need to change in this request are:
name – name of your Azure SQL Managed Instance (don’t include domain).
properties/administratorLogin – SQL login that will be used to connect to the instance.
properties/subnetId – Azure identifier of the subnet where Azure SQL Managed Instance should be placed. Make sure that you properlyconfigure network for Azure SQL Managed Instance
location – one of the valid location for Azure data centers, for example: “westcentralus”
sku/name: GP_Gen4 or GP_Gen5
properties/vCores: Number of cores that should be assigned to your instance. Values can be 8, 16, or 24 if you select GP_Gen4 sku name, or 8, 16, 24, 32, or 40 if you select GP_Gen5.
properties/storageSizeInGB: Maximum storage space for your instance. It should be multiple of 32GB.
properties/licenceType: Choose BasePrice if you don’t have SQL Server on-premises licence that you want to use, or LicenceIncluded if you can have discount for your on-premises licence.
tags(optional) – optionally put some key:value pairs that you would use to categorize instance.
Click through for the template and a quick Powershell script which shows how to use the template.
Comments closedArun Sirpal has a few T-SQL statements to help manage an Azure SQL Database database:
Creating a database
Very easy to create something like the below – a S2 database.
CREATE DATABASE [MeeTwoDB] ( MAXSIZE = 5GB, EDITION = 'standard', SERVICE_OBJECTIVE = 'S2' ) ; --checking details SELECT Edition = DATABASEPROPERTYEX('MeeTwoDB', 'Edition'), ServiceObjective = DATABASEPROPERTYEX('MeeTwoDB', 'ServiceObjective')
Read on for several more examples.
Comments closedThomas Rushton reproduces an error state in SQL Server:
A question asked on one of the forums today wasn’t easily answerable by Googling. Summary of the question “I have error 18456 State 73 – why?”
Google seemed remarkably quiet on the subject of that particular state code. Even Aaron Bertrand’s list of causes of state codes for SQL Server error 18456 missed this one.
However, some searching did find a link to what appears to be some in-depth VMWare VSAN training documentation that includes that error in some logging information, which made me wonder if it was related to the error 18456 state 38 that followed.
Read on for more details.
Comments closedErin Stellato gives us some good life advice:
There are a variety of methods we use for helping customers upgrade to a new SQL Server version, and one question we often get asked is whether or not statistics need to be updated as part of the upgrade process.
tl;dr
Yes. Update statistics after an upgrade. Further, if you’re upgrading to 2012 or higher from an earlier version, you should rebuild your indexes (which will update index statistics, so then you just need to update column statistics).
Make this one of your “Not too long; did read” posts of the day.
Comments closedAndy Isaacson covers a performance issue Honeycomb experienced with RDS:
In retrospect, the failure chain had just 4 links:
The RDS MySQL database instance backing our production environment experienced a sudden and massive performance degradation;
P95(query_time)went from 11 milliseconds to >1000 milliseconds, while write-operation throughput dropped from 780/second to 5/second, in just 20 seconds.
RDS did not identify a failure, so the Multi-AZ feature did not activate to fail over to the hot spare.
As a result of the delays due to increased
query_time, the Go MySQL client’s connection pool filled up with connections waiting for slow query results to come back and opened more connections to compensate.
This exceeded the
max_connectionssetting on the MySQL server, leading to cron jobs and newly-started daemons being unable to connect to the database and triggering many “Error 1040: Too many connections” log messages.
This was very interesting to read, and I applaud companies making public these kinds of post-mortems, especially because the idea of publicizing the reasons for failures is so scary.
Comments closedDimitri Furman explains Azure SQL Database Managed Instance resource allocations:
In the current Azure SQL Database Managed Instance (MI) preview, when customers create a new instance, they can allocate a certain number of CPU vCores and a certain amount of disk storage space for the instance. However, there is no explicit configuration option for the amount of memory allocated to the instance, because on MI, memory allocation is proportional to the number of vCores used.
How can a customer determine the actual amount of memory their MI instance can use, in GB? The answer is less obvious than it may seem. Using the traditional SQL Server methods will not provide the right answer on MI. In this article, we will go over the technical details of CPU and memory allocation on MI, and describe the correct way to answer this question.
The information and behavior described in this article are as of the time of writing (April 2018). Some aspects of MI behavior, including the visibility of certain compute resource allocations, may be temporary and will likely change as MI progresses from the current preview to general availability and beyond. Nevertheless, customers using MI in preview will find that this article answers some of the common questions about MI resource allocation.
What you see in SQL Server Management Studio is true, but it is also not the whole picture.
Comments closedLori Brown shows how to use a SQL Agent alert to warn you if tempdb grows beyond a certain size:
Lastly, create a SQL Alert to notify you as soon as tempdb grows past the threshold you stipulate. Using the GUI to create the alert, you need to fill out every field on the General page and make sure the Enabled checkbox is marked. Create a Name for the alerts, then specify the Type as SQL Server performance condition alert. The Object should be Databases, the Counter is Data File(s) Size (KB), and the Instance will be tempdb. The alert will trigger if counter rises above the value. The Value will depend upon the cumulative size of your tempdb files. In this case each tempdb file is 12GB (or 12,288,000 KB), so the total size is 98,304,000 KB.
I liked the approach of only firing the SQL Agent job after a trigger was met, rather than running a job which queries and then creates an e-mail afterward.
Comments closedGarry Bargsley gives us two ways to update dbatools on a schedule:
I have been using dbatools heavily since I was introduced to it. I have automated processes and created new processes with it. There are new commands that come out almost daily that fill in certain gaps or enhance current commands. One way to stay current with these updates is to update your dbatools install frequently.
How better to do this than to have an auto update process that will run daily and get the latest dbatools version for you…
I have put together two ways of doing this based on your preferred method. One is via a SQL Agent Job and the other is using a Windows Task Scheduler job.
Read on for examples of both techniques.
Comments closedBrent Ozar shares some findings from his SQL ConstantCare service:
Let’s look at a sample day from last week when 562 servers sent in data (out of the users who agreed to share their data to improve recommendations):
No one was using the -x startup flag
No one had known-dangerous third party modules installed
No one was running a 32-bit SQL Server
No one had high memory usage for in-memory OLTP (Hekaton)
No one’s plan cache had been erased recently (aside from server restarts)
No one needed to restart to fix a TempDB file configuration mismatch in the DMVs
Everyone on 2016 Standard Edition had patched to SP1 to get the free features
Read on to see the rules that customers are struggling with the most, as well as some thoughts on a scenario where a broken rule might not be that important.
Comments closed