A Fine Slice Of SQL Server
Louis Davidson shows what you can do with persisted, non-nullable computed columns:
Next, let’s add a check constraint our computed column. For this example, we are just going to make sure that the value in the table is a palindrome (because this is something that every data architect has come across at least one in their life, right?). So Value = REVERSE(Value);
Read on for more fun and sometimes-useful things you can do.
Comments closedMax Vernon has a couple scripts to analyze deadlocks in SQL Server:
Deadlock detection and analysis in SQL Server is important for the overall health of affected applications. This post shows how to setup an Extended Events session to capture deadlock details. It also provides a stored procedure to capture details from the Extended Events session. The stored procedure enables simplified permanent storage of those deadlock detection events. Next, we’ll set up a SQL Server Agent Job to run the stored procedure on an ongoing basis. Finally, we’ll see several examples of how to query the captured events. These queries support making the necessary changes to both the application and database design.
Click through for a description of what a deadlock is as well as scripts to help find and fix them.
Comments closedJeff Mlakar shows how you can use Management Studio to generate scripts for database objects:
Sales.SalesOrderDetail looks like a good choice. Let’s generate a script for that table, all associated objects, and its data.
The safest way to create structure including all indexes, keys, defaults, constraints, dependencies, triggers, etc. is to use SSMS Generate Scripts.
I would also recommend becoming familiar with the Powershell command to generate scripts and what dbatools has on store.
Comments closedJosh Darnell collects a few cases where SET STATISTICS IO ON doesn’t behave quite as you might expect:
The first one comes from a post on Database Administrators Stack Exchange: STATISTICS IO for parallel index scan
To summarize the situation, the OP had a query that was scanning a clustered index. They were seeing significantly higher numbers reported in the
logical readsportion of theSTATISTICS IOoutput when the query ran in parallel vs. serially (with aMAXDOP 1query hint). There is a demo of this behavior in the post, so I won’t reproduce it here.
There are several interesting cases in here, so check them out.
Comments closedShane O’Neill wants to know whether the dbcreator built-in role has the ability to back up the databases it creates:
I knew from reading the documentation that
dbcreatorgrants permissions to create, alter, drop, and restore databases. My question was does it give permission to backup databases?It seems to give everything else so is backup databases just missing there? Or is it intentionally left out?
To find out whether it does, click on through.
Comments closedMilos Radivojevic notes some changes in Query Store with SQL Server 2019 CTP 3.0:
In addition to default value, the minimum number of query executions in the AUTOquery_capture_mode for storing the query, its plan(s) and runtime statistics in SQL Server 2019 has been increased from 3 to 30. That means, Query Store does not store anything for first 29 query executions. It reserves query_ids, but it starts storing execution plan and runtime stats from 30thexecution in a single day.
These look like reasonable changes to me.
Comments closedHasan Savran shows how you can set unique key constraints on Cosmos DB containers:
Unique key names are case-sensitive, I have good experience on this. If your unique key is in lowercase letters but your data has field with uppercase, CosmosDB will insert null value into unique key first time, you will get an error second time when it tries to insert null again. CosmosDB does not support sparse unique keys. If your unique key is /SSN, you can have only one null value in this field.
If you like to use unique keys in Azure CosmosDB, you have to them when you create your containers. You cannot add a unique key to an existing container. Only way to add a unique key to an existing container is, to create a new container and move your data from older container to the new one. Also, you cannot update unique keys just like partition keys. Picking a wrong unique key can be an expensive error.
Looks like you’ll need to have a bit of foresight when choosing keys (or choosing not to use keys).
Comments closedLisa Charlotte Rost takes us through sparklines:
Sparklines are curious things. They’re supposed to show a trend, and a trend only. They’re supposed to show when something (like stocks) increase and decrease, where the peaks and the valleys are. But sparklines are not supposed to be comparable with each other.
So when you’re seeing two sparklines with the same height, the ebbs and flows of the first one could play out between 0 and 10 (e.g. US-Dollar), while the other sparkline’s peak is at 10,000.
But that’s odd, no? Doesn’t that invite people to make totally false assumptions?
I like sparklines a lot, but I’m apt to violate this particular rule and make them cross-comparable unless I know people will never care about comparisons between elements. One way to get around the “what if the range is big?” problem is to plot sparkline heights as logs so that 1000 is a bit bigger than 100, which is a bit bigger than 10. The argument I make for doing that is you still see size differences and sparkline comparisons are imprecise to begin with, so magnitudes are more important than exact values.
Comments closedDave Bland explains how you can use DEADLOCK_PRIORITY to control which process gets rolled back:
Before getting into how to set the DEADLOCK_PRIORITY, let’s quickly go over what the options are. There are two ways to set the priority of a process. The first option is to use one of the keywords, LOW, NORMAL or HIGH. The second approach is to use a numeric value between -10 and 10. The lowest value will be chosen as the victim. For example, LOW will be the victim of the other process is HIGH and -1 will be the victim if the other process is greater than -1.
As I recall, index operations (like rebuilds) are automatically set to a low priority.
Comments closedWith these dates on the horizon it’s a good time to look at our estate and make sure we have a good understanding of the versions we currently support. I’m going to show you how to do that easily with a couple of dbatools functions. Then, bonus content, I’ll show you how to present it for your managers with one of my other favourite PowerShell modules ImportExcel.
Jess gets bonus points for avoiding the dreaded pie chart at the end.
Comments closed