A Fine Slice Of SQL Server
It’s important to note that there isn’t a particular finding or insight that needs to be emphasized here. Instead, the goal for this visual is to provide the data in a digestible format, which will be part of a regularly updated report. That way, physicians and researchers can easily monitor any changes in the observations.
I was unsure of the best way to approach this task, so I started sketching.
Click through to get Alex’s thought process while building a chart in Excel.
Comments closedMatthew Roche digs deeper into data governance in Microsoft Fabric:
Comments closedOne of the most underappreciated benefits of Power BI as a managed SaaS data platform has been the “managed” part. When you create a report, dataset, dataflow, or other item in Power BI, the Power BI service knows everything about it. Power BI is the authoritative system for all items it contains, which means that Power BI can answer questions related to lineage (where does the data used by this report come from?) and impact analysis (where is the data in this dataset used?) and compliance (who has permissions to access this report?) and more.
If you’ve ever tried to authoritatively answer questions like these for a system of any non-trivial scope, you know how hard it is. Power BI has made this information increasingly available to administrators, through logs and APIs, and the community has built a wide range of free and paid solutions to help admins turn this information into insights and action. Even more excitingly, Power BI keeps getting better and better even as the newer parts of Fabric seem to be getting all of the attention.
Jura Bratina looks at the numbers:
A client asked us to verify why an INSERT AS SELECT statement, which was part of a scheduled ETL job executing on a single instance V11.2 database running as a data warehouse, suddenly started to take longer than usual. The database doesn’t have the Diagnostic and Tuning license, so Statspack and an external 3rd party application are being used to monitor the performance.
Okay, so I like making fun of Oracle licensing as much as (well, more than) the next guy, but seriously, a Diagnostic and Tuning license? Anyhow, the post isn’t about Oracle’s quest to find a more efficient way to vacuum money out of your bank account, but instead a performance troubleshooting approach when performance is intermittently bad. Spoilers: noisy neighbors. It’s usually noisy neighbors.
Comments closedGlenn Berry breaks out the stopwatch:
SSMS 18.X cold startup performance is a big issue for me, since I regularly have to start SSMS 18.X on various small VMs running on older, slower server-class processors. BTW, “cold startup” is the first time that a program is loaded into a process since the last reboot of the operating system, so nothing from that program is already loaded in memory.
The hero image alone convinced me to install 19.1.
Comments closedQuoth Chad Callihan, “Occasionally more”:
QUOTENAME can be used to make sure database objects are valid in your query. Most of the time, objects like table names only contain valid characters, so there’s nothing to worry about. But nobody’s perfect. Let’s look at an example of what can happen when somebody creates a table with a forward slash in the name and see how QUOTENAME can be used to query against it.
QUOTENAME is also a good way of preventing SQL injection, though I still prefer appropriate use of exec sp_executesql in any case in which it’s possible to use.
Katie Cummiskey provides some tips for us:
We previously discussed how to use Power BI on top of Databricks Lakehouse efficiently. However, the well-designed and efficient Lakehouse itself is the basement for overall performance and good user experience. We will discuss recommendations for physical layout of Delta tables, data modeling, as well as recommendations for Databricks SQL Warehouses.
These tips and techniques proved to be efficient based on our field experience. We hope you will find them relevant for your Lakehouse implementations too.
Read on for these tips.
Comments closedReitse Eskens digs into Microsoft Fabric:
In my previous blog, I wrote about some first impression working with Fabric and mostly following the Lakehouse tutorial provided by Microsoft. Well structured as this one may be, the sizes are not like the sizes I’m seeing in the wild. So I decided to give Fabric a bit more of a challenge by letting it loose on my TPC-H dataset.
Click through for Reitse’s early analysis.
Comments closedItzik Ben-Gan has a new challenge:
The task involves developing a stored procedure called
GetBalancesthat accepts a parameter called@accountidrepresenting an account ID. The stored procedure should return a result set with all existing dates and balances for the input account, but also gap-filled with the dates of the missing workdays between the existing minimum and maximum dates for the account, along with the last known balance up to that point. The result should be ordered by the date.
My first thought was last observation carried forward, which is now available in SQL Server 2022 (Itzik’s solution 2). I kind of thought of solution 3, though did not think through the mechanics of how it’d work and so I get no credit there.
Comments closedQuestion: How can I find tables in a SQL Server database that have a Primary Key ?
Click through for three separate ways to answer this question, though one of them uses the dreaded ANSI-89 syntax, which is loathsome. An upgrade to ANSI-92 syntax, however, would make it a fine query.
Comments closedHugo Kornelis is back and looking at graphs:
SQL Graph is the name for a set of features, introduced in SQL Server 2017 and extended in SQL Server 2019, that bring graph database functionality into SQL Server. See here for the full documentation as provided by Microsoft. In this first post about SQL Graph, I’ll look at what execution plans reveal about the internal structure of graph tables. I’ll then use that knowledge in later parts, where I’ll discuss more advanced queries on graph tables.
Click through for the primer.
Comments closed