A Fine Slice Of SQL Server
Chris Webb has a performance tip for us:
You may have noticed that a new dataflows connector was announced in the August 2021 release of Power BI Desktop, and that it now supports query folding between a dataset and a dataflow – which you may be surprised to learn was not possible before. In this post I thought I’d take a look at how much of an improvement in performance this can make to dataset refresh performance.
Click through for the demonstration.
Comments closedPaul Thurrott looks at a security issue:
Over 1000 web apps created with Microsoft’s Power Apps inadvertently exposed the data from over 38 million users thanks to a misconfiguration, according to a new report in Wired. The good news? The issue has been fixed and no customers are known to have been compromised.
“We found [a web app created with Power Apps] that was misconfigured to expose data and we thought, we’ve never heard of this, is this a one-off thing or is this a systemic issue?” UpGuard vice president Greg Pollock told Wired. “Because of the way the Power Apps portals product works, it’s very easy to quickly do a survey. And we discovered there are tons of these exposed. It was wild.”
“Known to have been compromised” probably needs a “yet” in there somewhere. Read the whole thing.
Comments closedJared Poche continues a series on QDS Toolbox:
Carrying on from my previous post on the QDS Toolbox, let’s review the Query Waits component.
This component provides details about the wait types associated with a given object, query, or plan. This doesn’t pull data from the reports generated by the Server Top Queries component; this gets the data directly from the Query Store in the database you specify. So you can use this to review the waits on a given procedure, even if you haven’t generated any reports with Server Top Queries .
That being said, reports from Server Top Queries could identify problem queries and give you the PlanID, QueryID, or ObjectName you need to run the procedure. A query that spends a lot of time waiting would tend to have a higher duration and lower active metrics like CPU usage or logical reads. So those are the queries I would want to run the Query Waits report against.
Read on to see how to call this procedure and what you might expect.
Comments closedImke Feldmann has an update to the Power BI Cleaner:
In this post I’m sharing a new version of my Power BI Cleaner tool. Besides from some bug fixes it can now automatically identify and analyze reports that are connected to an Analysis Services Model in tabular mode.
Just a warning that the live connections to Analysis Service or shared Power BI dataset might be slow, as Power Query will retrieve the data from all datasets that are available for your from the service. I might include a feature to pre-select datasets in a future release if there is interest. So please add comments if you want to see this feature.
Read on to see how to set everything up, as well as the things you can do with it.
Comments closedTomaz Kastrun continues a series:
From the previous two blog posts:
– Creating REST API for reading data from Microsoft SQL Server in web browser
– Writing Data to Microsoft SQL Server from web browser using REST API and node.js
We have looked into the installation process of Node.js, setup of Microsoft SQL Server and made couple of examples on reading the data from database through REST API and how to insert data back to database.
In this post, we will be looking the R predictions using API calls against a sample dataset.
Click through to see it in action.
Comments closedJohn McCormack wants to know about those slow queries:
The slow query log will record all queries which are above the threshold level. The default value is 10 (seconds) but you can set it higher or lower depending on your requirements. It is useful for finding slow queries and allows you to pick out candidates for tuning.
If you set the threshold too low, it can increase I/O overhead on your instance and use a lot of valuable disk space. If you set it too high, it might not capture enough useful information.
This is a setting in AWS Relational Database Services and mimics functionality in MySQL
Comments closedConstantine Kokkinos predicts the future:
The first question I wanted to model out was a bigger issue with on-premises databases – when are we going to run out of storage?
Back in the day I’d cheat with msdb backups, comparing compressed sized to actuals, and moving on. However I don’t have a historical reference for Stack Overflow… so what can I do?
Taking a look at the tables we see a commonality in many tables – CreationDate! It looks like the rows faithfully are stamped when they are created.
Constantine does at the end hit on something we tend to forget: most operations in life aren’t quite linear. We often get lucky in that certain stretches are close enough to be linear that we can model them that way, but even in this dataset, you can see the effects of polynomial growth slowly build up. Still, this is a good way of taking us through what an analysis and projection can look like.
Comments closedChad Callihan breaks out the stopwatch:
When you are troubleshooting query performance, it’s important to consider when SQL Server last restarted. Each time SQL Server restarts, buffer pool and plan cache get wiped out. This can play a big part in how you approach an issue.
You want to know how much history you have to work with. Not seeing much data in plan cache? Maybe SQL Server restarted recently. Parameter sniffing issues that you were seeing earlier today are now suddenly “fixed?” SQL Server could have restarted between then and now. Let’s take a quick look a few ways to review how long SQL Server has been up and running.
Click through for several techniques. I’ve always checked the tempdb startup time in sys.databases, myself, but that’s not the only way.
Comments closedMeagan Longoria improves a visual:
There are some easy opportunities to improve the readability of this chart, so I thought I would use it as an example of how small improvements can have a big impact on a fairly simple chart. I recreated the chart (as best I could) in Power BI and then made two revised versions.
Read on to see what Meagan did and get some advice on the subject.
Comments closedHolger von Jouanne-Diedrich has a greatest hits album:
Over the course of the last two and a half years, I have written over one hundred posts for my blog “Learning Machines” on the topics of data science, i.e. statistics, artificial intelligence, machine learning, and deep learning.
I use many of those in my university classes and in this post, I will give you the first part of a learning path for the knowledge that has accumulated on this blog over the years to become a well-rounded data scientist, so read on!
Read on for links to dozens of posts on interesting topics.
Comments closed