A Fine Slice Of SQL Server
Kendra Little shows that you can block Query Store cleanup:
This is an isolated test system, so I went to clean out Query Store as a reset. I didn’t need any of the old information in there, so I ran:
- ALTER DATABASE BabbyNames SET QUERY_STORE CLEAR ALL;
- GO
I was surprised when I didn’t see this complete very quickly, as it normally does.
Click through to see how Kendra diagnoses the issue.
Comments closedHugo Kornelius notes a documentation bug with the Segment operator:
The Segment operator, like all operators, is described at the Books Online page mentioned above. Here is the description, quoted verbatim:
Segment is a physical and a logical operator. It divides the input set into segments based on the value of one or more columns. These columns are shown as arguments in the Segment operator. The operator then outputs one segment at a time.
Looking at the properties of the Segment operator, we do indeed see the argument mentioned in this description, in the Group By property (highlighted in the screenshot). So this operator reads the data returned by the Index Scan (sorted by TerritoryID, which is required for it to work; this is why the Index Scan operator is ordered to perform an ordered scan), and divides it into segments based on this column. In other words, this operator is a direct implementation of the PARTITION BY spefication. Every segment returned by the operator is what we would call a partition for the ROW_NUMBER() function in the T-SQL query. And this enables the Sequence Project operator to reset its counters and start at 1 for every new segment / partition.
Read on to understand the issue and see Hugo’s proof.
Comments closedFor a long time, in Windows PowerShell, we had to add the parameter “-NoTypeInformation“, so the “#TYPE …” line on the first row of the *CSV would not be included.
So, in Windows PowerShell executing the command without the “-NoTypeInformation” parameter, will produce the following result:
Now, using the same command in PowerShell Core without the “-NoTypeInformation” parameter, will produce a different result:
This is a better default, but I think it’s going to burn some people who have scripts pre-built expecting to clear out that first line.
Comments closedJana Sattainathan has a procedure which makes it easier to read the error logs in SQL Server:
xp_ReadErrorLog has some limitations
- Reads only the specified error log whose ArchiveNumber is specified
- Shows only the rows with matching string (not adjacent context info rows)
The first bullet is obvious in that we cannot read ALL the logs to look for something more holistic and meaningful with all the information we have. This MSSQLTips post does describe a method to loop through.
Click through for Jana’s stored procedure code.
Comments closedDaniel Janik shows how you can find which queries are causing you pain due to missing indexes:
Missing indexes are an important part of the indexing strategy. I usually start with sys.dm_db_index_usage_stats to find both inefficient and unused indexes and then supplement with missing indexes.
The missing index DMVs are great but they’ve always been missing something.
What are they missing you ask? They currently tell you what table they are for but not what query. How do I know if the queries that sponsored this missing index are business critical or not? Wouldn’t it be nice to know what statements caused this “missing index” to appear?
Read on to learn how to do this.
Comments closedRobert Cain has part one of a two-part series on creating VMs in Azure using Powershell:
Creating a virtual machine is great, but it won’t be of much use unless it can communicate outside of itself. That’s where virtual networking comes in. To setup a virtual network, often abbreviated vnet, you need to accomplish three things. First is the creation of the virtual network itself. After the network is created, you need to define a security group for it. In essence, the security group defines a firewall. In the process of creating it, the PSAzure module automatically creates firewall rules that allow HTTP and RDP (Remote Desktop Protocol) traffic through the firewall. There are functions in PSAzure to create security groups at a lower level, allowing one to create alternate rules. This example will demonstrate the most common options.
The final step is to create a virtual NIC, or Network Interface Card. The NIC will form the bridge between the virtual network and the virtual machine, much like a physical network card allows a physical computer to connect to a real network. First off, a few variables are assigned. These will hold names for the security group, network and subnet names. The network addresses for the main network and subnet are also placed into into variables. Finally, a name is assigned to the NIC.
Check it out, especially if you build a lot of VMs in Azure.
Comments closedLaurence Maroney explains TensorFlow Lite:
TensorFlow Lite is TensorFlow’s lightweight solution for mobile and embedded devices. It enables on-device machine learning inference with low latency and a small binary size. TensorFlow Lite also supports hardware acceleration with the Android Neural Networks API.
It’s designed to be low-latency, with optimized kernels for mobile apps, pre-fused activations and much more. It’s also *really* easy to use, and there’s a great demo app that will get you up and running with image classification from the device camera on both Android and iOS.
It comes in two parts:
A set of tools that you can use to prepare your models for use on mobile. These let you freeze your model to make it smaller, and then optimize and convert it in a process also called flattening the model, so that it will run happily on mobile
A mobile runtime with an easy API that lets you pass data to the model and get classifications back.
You don’t build the neural network on a phone, but the fact that you can run one on your phone is pretty crazy.
Comments closedMsg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 3]
‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.
Msg 11536, Level 16, State 1, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.
Grr! What’s happened here? We had installed R as part of the SQL installation, and we had run the command to enable it, too.
Click through for reasons. One thing which might affect a small percentage of people is that ML Services creates a folder each time you run an R query. Those folders are easy to clean up, and each time the Launchpad service starts up, it deletes the old folders as a step. The problem is that if you have a huge number (tens or hundreds of thousands), it might not get finished deleting in time and the service will fail. Deleting the folders manually does the trick and the service can start up once more.
Comments closedShane O’Neill covers an annoying but necessary thing to remember around window functions:
Now, a new learner of SQL comes along with the requirement to find the last 2 rows per PartitionId.
They are diligent and enthusiastic and have just read about Windows Functions. They think to themselves
Wow! This is great! I can do this with Windows Functions!
They also work for a company that has invested in RedGate’s SQL Prompt so they know that they can rely on SQL Prompt to help iron out any inconsistencies in their script.
So they take the SELECT script above and type in
WHERE… and the auto complete pops up
And that popup isn’t quite accurate… Shane covers this in the guise of a SQL Prompt bug, but it’s a good thing to remember regardless of which tooling you use.
Comments closedLori Brown covers a few string functions added to SQL Server in the past two versions:
STRING_ESCAPE (https://docs.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql)
This function is available starting with SQL 2016 and is currently only able to escape JSON characters. To me it’s not super useful just yet but hopefully they will add more types soon.
|
|
I haven’t had the need to use STRING_ESCAPE yet, but one additional function I’d add is CONCAT_WS.