Press "Enter" to skip to content

Category: Uncategorized

Power BI and Azure Synapse Analytics

James Serra gives us some insights on the future of Power BI and how it relates with Azure Synapse Analytics today:

As an example of the speed of each layer, during an Ignite session (view here), there was a Power BI query run against 26 billion rows that was returning a sum of store sales by year. The same query was run three times using a different layer:

1. Using a DirectQuery against tables in SQL DW took 8 seconds
2. Using a DirectQuery against a materialized view in SQL DW took 2.4 seconds.  Note you don’t have to specify that you are using a materialized view in the query, as the SQL DW optimizer will know if it can use it or not
3. Using a Aggregation table that is Imported into Power BI took 0 milliseconds

Keep in mind this is all hidden from user – they just create the report.  If they do a query against a table not in memory in Power BI, it will do a DirectQuery against the data source which could take a while.  However, due to SQL DW result-set caching, repeat DirectQuery’s can be very fast (in the Ignite session they demo’d a DirectQuery that took 42 seconds the first time the query was run, and just 154 milliseconds the second time the query was run that used result-set caching).

There’s some interesting information in here, especially around Power BI eventually taking over Azure Analysis Services’ space in the market.

Leave a Comment

Throttling Power BI Data Gateways

Gilbert Quevauvilliers shows how you can use load balancing with Power BI gateways:

I always recommend for On-Premise Data Gateway installations that there be at least 2 Gateways installed.

The initial reason was to ensure that if one gateway went down the other one would be able to still refresh or connect to the DirectQuery or LiveConnection sources.

With the recent update where you can now control the CPU and Memory on each Gateway instance, this means that I am able to define a dedicated server to take more of the refreshing/DirectQuery/LiveConnection load. And I can offload secondary refreshing to the second server which might be installed on another server with multiple roles.

NOTE: In order this to work you must have the Oct 2019 version of the On-Premise Data Gateway installed.

There’s a lot of good information here, including one potential failure scenario.

Comments closed

Cardinality Estimation of Table Varaibles with Nullable Columns

Milos Radivojevic takes us through a quick demonstration of a change in SQL Server 2019:

By using the same formula, the estimated number of rows is:

SELECT 0.001992032*1000000
--1992.032000000

This is exactly what we see in the execution plan. OK, that was CL 140, let’s see how SQL Server 2019 handles this simple case.

When we switch to CL 150, the plan and estimations for the c1 column (non-nullable) are the same. However, the estimation for the nullable column is changed!

Read the whole thing.

Comments closed

Recovering Deleted Indexes

Eric Cobb has an enhancement to the SQL Server Metrics Pack:

I recently had a case at work where a database was restored, and several important indexes were accidentally removed. But because we are using SQL Server Metrics Pack to track the indexes on that server, we were easily able to recover all of the deleted indexes.

In order to spotlight the feature a little, I wanted to answer a few questions and provide some queries to help explain how to use this new feature.

Read on to learn more, and check out the GitHub repo as well.

Comments closed

Generating Unique File Names

Slava Murygin gives us unique file names:

That is pretty common task to generate new files with a timestamp in their names.
It gives you ability to easily identify them, sort them and make them pretty unique.
However, if you have a very busy process it is possible that duplicate name will be produced and you might loose some data.

To avoid that situation I’ve came up with following solution.

It is difficult to envision this solution going wrong.

Comments closed

Locking Azure Resources

Stuart Moore shows us how we can lock Azure resources to prevent accidental mistakes:

A resource deletion may not sound like too much of a big thing if you’re deploying Infrastructure as code, hey we’ll just terraform apply again and it’ll pop backup.

In theory that’s a great idea, just with one big problem. The new resource isn’t the old resource!

For an example, an Azure SQL Database server is a unique resource. If you delete one you lose any backups you’ve taken as they’re hosted on the server. Spinning up a new one isn’t going to get them back!

Stuart shows us how to take these locks. Because I have my doubts that everyone has all of their infrastructure prepped as terraform scripts, Stuart’s point is even more relevant.

Comments closed

Automate VM Shutdown

Meagan Longoria has a script to shut off an Azure VM when a SQL Agent job finishes:

The runbook sets the Azure context to the appropriate subscription (especially important when you are a guest user in someone else’s tenant). Then it checks if the VM is started. If it is, it goes into a do-while loop. This task isn’t super time sensitive (it’s just to save money when the VM isn’t in use), so it’s waiting 60 seconds and then calling the child runbook to find out if my SQL Agent job is running. This makes sure that the child runbook is called at least once. If the result is that the job is not running, it stops the VM. If the job is running, the loop starts over, waiting 60 seconds before checking again. This loop is essentially polling the job status until it sees that the job is completed.

Click through for the script.

Comments closed

Managing Database Changes with Scripts

Ed Elliott continues a series on automated database deployments with a quick change process:

In my blog post here https://the.agilesql.club/blogs/ed-elliott/2019-06-10/steps-to-automated-database-deployments I described the steps you need to go through so you can build up your confidence that you are capable of deploying databases using automation. I mean, afterall, knowing that it is possible to automate your deployments and having confidence that they will succeed are two very different things.

Even with the best tooling in the world, automated database deployments are still a struggle and there is one key thing that you can do, no matter what tools you choose and that is to make the deployments re-runnable. (Insert discussion here on the word idempotent and how it means re-runnable but sounds far cooler and intellectual). If you make your deployments re-runnable then you can, by their very definiton, re-run them.

Click through for two options. I definitely prefer option number 1 as well.

Comments closed

Analyzing Data by the Numbers

I am close to wrapping up my series on forensic accounting techniques:

Round number analysis focuses on the final digits of a number, specifically looking at how many 0s there are at the end after rounding off the decimal. We’re interested in the percentage of records in each rank (or Type). For example, $10,820 is a Type 1 because there is a single 0 at the end. $8,300 is a Type 2 because there are two 0s at the end. $11,000 is a Type 3. And $9009 is a Type 0 because there are no 0s at the end.

With that in mind, how do we do this kind of calculation in SQL Server? With one of my favorite operators: APPLY.

Click through to watch me look at round numbers, last digits, and first digits.

Comments closed