A Fine Slice Of SQL Server
Michael Mayer wants to suss out the effects of inputs into a causal forest model:
We use a causal forest [1] to model the treatment effect in a randomized controlled clinical trial. Then, we explain this black-box model with usual explainability tools. These will reveal segments where the treatment works better or worse, just like a forest plot, but multivariately.
Read on for the example, as well as several mechanisms you can use to gauge feature relevance.
Comments closedDharmbir Kashyap makes a comparison:
In the realm of event streaming and real-time data processing, choosing the right platform is critical to the success of your project. Two of the most popular options available today are Azure Event Hub and Apache Kafka. Both platforms offer robust solutions for handling large volumes of streaming data, but they are designed with different architectures, features, and use cases in mind. This blog post will delve into the key differences between Azure Event Hub and Kafka, helping you determine which platform is best suited for your specific needs.
Read on for an overview of each product and where each product fits.
Comments closedKendra Little has a script for us:
tldr; I’ve published a script to loop through all databases on an instance, identify if there are any query plans in a problematic “failed” forced state (which can hurt query performance), and un-force them if found. Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub.
This script is designed to work on SQL Server on-prem, in a VM, or in Azure SQL Managed Instance or SQL Server RDS. Since the script is instance-level and loops through all databases, this isn’t really designed for Azure SQL Database – and you don’t get a SQL Agent there anyway, so you probably want to change this around for that use case. The script is shared under the MIT license, feel free to contribute code and/or adapt away for your own uses.
Read on to learn more about what might cause these failure to occur and what you can do about them.
Comments closedKristina Mishra troubleshoots a problem:
Recently my boss reached out to me with an interesting question: How can she create a workspace in Fabric’s Data Engineering section? When she clicked on create a workspace, and then the Advance tab, her License mode options were restricted to Pro or Premium per-user. She didn’t have any of the Fabric options.
Read on for the solution.
Comments closedGreg Dodd enables another hop:
The main issue with the security is when you map a login on the source server to a destination server. When you do this, you provide anyone who can access the source server access to the linked server. Sure, you can lock it down with different logins in the top section there, but whenever I’ve seen this, people take the lazy option and choose “Be made using this security context:” and fill in an account that has quite privileged access on the linked server. Hopefully you can see the security issue here…
Read on for Greg’s recommendations around this. I mean, it’s just so much easier to give everybody the sa account (and make the password something they can remember like “sa”). That solves all of these silly security issues no problem.
Hang on, someone just dropped an important table on accident again. Be right back.
Comments closedEdwin Sarmiento shows a practical application of Query Store:
In a previous tip on SQL Server 2016 Query Store Introduction, we’ve seen how to enable Query Store and find out the top resource consumers. We’ve experienced high CPU utilization recently and wanted to know the root cause and how to fix it. How can we use Query Store to achieve this?
Read on for the scenario and how Edwin diagnosed and fixed the issue.
Comments closedBen Johnston shares some advice:
An entire post on choosing the correct data types either seems like overkill or much needed and overdue. The perspective might vary based on the databases you’ve worked with recently. I decided to write this after seeing some code with data type decisions that I would classify as questionable. There are many decisions in technology that can be ambiguous, but the correct date type should be based on business rules and a set of technical guidelines. I am going to share my thought process for deciphering the correct type here.
Selecting a data type is an important part of database and table design. The column represents an actual business attribute, is used to support data integrity, or is used for performance considerations. Care should be used when selecting the definition for each column. Choosing the wrong type can impact each of these areas, makes the system difficult to work with, and makes integrations harder than necessary.
Read on for Ben’s selection criteria and further thoughts. Most of it I find quite sensible.
I do, however, strongly disagree on part of Ben’s Unicode character strings recommendation, as I am an NVARCHAR Everywhere kind of guy. My counter-recommendation is always to use NVARCHAR over VARCHAR (I tend to be a bit more flexible about NCHAR vs CHAR, as those are typically for flags versus user-relevant data), and use row-level or page-level compression on indexes wherever it makes sense. If you don’t have any characters outside of your codepage in any row of that data, the size will be the same as with VARCHAR. If you do have the need for special characters, you don’t need to rebuild everything from scratch as part of a half-year (or longer) internationalization program. And if you’re consistent about always using NVARCHAR, you also eliminate implicit conversion risk.
Comments closedHugo Kornelis continues a deep dive into cursors:
One of the things that has always bothered me about the fast forward cursor type is the shocking lack of documentation of what it does exactly. Okay, the name suggests that it is fast. But is it really? When I first looked at cursor performance (granted, a long time ago), I found that a static cursor was actually faster than a fast forward cursor for the same query. So… fastish forward?
The name also suggests that this cursor is forward only. That is indeed the case. At least they got that right in the naming.
Read on to learn more about this cursor property, as well as how it compares to static and dynamic cursors. Hugo ends on a spicy take you won’t want to miss.
Comments closed