A Fine Slice Of SQL Server
Brent Ozar explains that index seeks aren’t always as selective as they sound:
When you see “index seek” on an execution plan, that doesn’t mean SQL Server is jumping to exactly the row you’re looking for. It only means that SQL Server is seeking on the first column of the index.
This is especially misleading on indexes where the first column isn’t very selective.
Definitely worth the read.
Comments closedLukas Eder shows off natural joins:
At first I though of the
UNION CORRESPONDINGsyntax, which doesn’t really exist in most SQL dialects, even if it’s a standard feature. But then, I remembered that this is again a perfect use case forNATURAL FULL JOIN, this time slightly differently from the above example where two tables are compared for contents. This time, we want to make sure the two joined tables never have matching rows, in order to get theUNIONlike behaviour.
I wasn’t aware of the notion of natural joins because they’re not available in SQL Server. They are available in Oracle, Postgres, and MySQL. Fun as Lukas’s blog post is, I could see natural joins going wrong in so many ways.
Comments closedTaiob Ali takes us through a new DMV:
There is a new DMV currently in preview which returns information about backups of Azure SQL databases except for the Hyperscale tier. Microsoft official documentation is here.
If you run the example query as-is from the above documentation some of the columns do not make sense.
Taiob includes a better query which provides the type of information you’re used to in on-premises SQL Server.
Comments closedErik Darling tries something out:
I think it was sometime in the last century that I mentioned I often recommend folks turn on Forced Parameterization in order to deal with poorly formed application queries that send literal rather than parameterized values to SQL Server.
And then just like a magickal that, I recommended it to someone who also has a lot of problems with Local Variables in their stored procedures.
They were curious about if Forced Parameterization would fix that, and the answer is no.
Shot down by the third paragraph of the intro. That’s rough. Still, click through for the demo.
Comments closedKristyna Hughes shows how to create an info tooltip in Power BI:
The steps below will walk through how to add an information icon to the report, making a tooltip page containing your additional information, and enabling the tooltip to allow users to hover over the icon and see the information.
This can be quite useful, especially as it gets context information out of the way of users after they don’t need it anymore. That’s important for dashboards you expect people to look at regularly.
Comments closedCorrinna Peters makes comparisons:
There are different cases for using both depending on the specific needs and requirements, Synapse and Databricks are similar, but both have their own areas of specialities or rather areas where they are above the other.
Data Lake – they both allow you to query the data from the data lake, Synapse uses either the SQL on demand pool or Spark and Databricks uses the Databricks workspace once you have mounted the data lake. If you are predominately a SQL user and prefer the code and the BI developer feel then Synapse would be the correct choice whereas if you are a Data Scientist and prefer to code in Python or R then Databricks would feel more at home.
Read on for a nuanced take. My less nuanced take is, Databricks beats the pants off of Synapse Spark pools in terms of performance. Synapse has a much better overall ecosystem, expanding beyond Spark and into T-SQL (in two flavors) and log/event analytics with KQL. If you’re spending 100% of your time in Spark and don’t care about the rest, use Databricks; if Spark is a relatively small part of your warehousing work, use Synapse.
1 CommentIn this fourth (and hopefully final) entry in my “Icing the Kicker” series of posts, I’m going to jump back to the first post where I used
tidymodelsto predict whether or not a kick attempt would be iced. However, this time I see if using theh2oAutoML feature and theSuperLearnerpackage can improve the predictive performance of my initial model.
The results are just about what I would have expected: they provide a good floor but a human with knowledge of the data and skill with techniques can still beat out-of-the-box AutoML processes. Still, knowing what that floor is can help a lot: run some AutoML tool for a few minutes/hours/days and you have an easy way of letting the business side know the expected model quality. If AutoML already exceeds expectations, you’re golden. If AutoML is close to expectations (on either end, just above or just below), you as a skilled human should be able to improve things a bit more, especially once you have a chance to analyze what the AutoML processes did. If AutoML is way below business expectations of quality, perhaps this isn’t the best project to spend time on. H/T R-Bloggers.
Comments closedDavid Fowler looks for the biggest, meanest database on a SQL Server instance:
I’m sure we’ve all been there at some point, sometimes it’s easy to tell. We might only have a handful of databases on the server with one known to be the heaviest utilised. But sometimes things might not be so obvious, there could be a large numbers of databases or no obvious resource hog.
In those instances we need some way to figure out what how much time each database is spending on the CPU if that’s what we’re interested in or perhaps the total number of page reads or writes if IO is our problem.
Read on for one way to do this, assuming that the instance has been up long enough to give you reliable results.
Comments closedTom Collins enables Transparent Data Encryption in AWS’s RDS:
Does AWS RDS SQL Server support built-in SQL Server Transparent Data Encryption (TDE)? Yes , is the short answer , but there are some limitations .
Before I dive into the AWS SQL Server RDS TDW limitations , in this post we’re discussing SQL Server TDE as opposed to RDS Encryption covering Aurora, MySQL, MariaDB, PostgreSQL, Oracle, SQL Server
Read on for the limitations Tom mentions.
Comments closedA relationship in Power BI is often for when we want a table to filter another table (based on the selection of items in the visuals). This filtering happens in the direction of the relationship. Meaning that if DimCustomer has a one-to-many relationship with the FactSales, and the relationship direction is single directional from the DimCustomer to FactSales, then DimCustomer can filter the FactSales, but not the other way around.
What we see a lot in the Power BI models is that the developer changes the relationship to both-directional so that each of the tables can filter the other table. This looks like a nice feature, but it will come at a big cost.
Click through for five good tips in blog format as well as written form.
Comments closed