Press "Enter" to skip to content

Month: November 2020

Tracking Cosmos DB Re-Indexing Progress

Published 2020-11-18 by Kevin Feasel

Hasan Savran wants information:

Indexes let your queries run faster. When you need to adjust your indexing policies, database engines re-indexes your data respecting to your changes. In Cosmos DB, when you change your indexing policies, database engine truncates all your indexes and starts to reindex all your indexes from scratch. You do not want to change your indexing policies when your application is busy. Because your queries can not use the dropped indexes, queries will take longer, and they will cost more Request Units. Also, your queries might not return all the data they supposed to. You can read me my older post about indexes in Cosmos DB.

     You may want to monitor re-indexing progress; you may want to disable your application until indexing is completed or warn your team about the re-indexing progress. You can check the re-indexing progress only from SDK, that means you need to write your own code to accomplish this. I have the following code which checks the progress every second. If progress is at %100 then it quits, otherwise it continues to check progress every second until it receives 100 as result.

Hasan has provided us with a script, so check that out.

Comments closed

Finding Table and Index Compression Levels

Published 2020-11-18 by Kevin Feasel

Kenneth Fisher has a script for us:

It’s been a while since I worked with compression and the other day I needed to check which of my indexes were compressed and which weren’t. Now, I knew the information wasn’t going to be in sys.tables and I couldn’t find it in sys.indexes or INDEXPROPERTY(). I’ll be honest it had me stumped for a little bit. Until I remembered something!

Read on for Kenneth’s remembered insight and a script to get the job done.

Comments closed

Displaying Always Encrypted Data in SSRS with a gMSA

Published 2020-11-18 by Kevin Feasel

Ryan Adams shows how we can view Always Encrypted data in SQL Server Reporting Services when SSRS uses a Group Managed Service Account:

Always Encrypted protects our data both at rest and in transit. To accomplish that, SQL only stores the encrypted data and cannot decrypt it; all the work is done by the client. In our case the client is SSRS and it is the account running the SSRS service that will need the certificate to decrypt data. Note that it is not the account running the report.

Click through for the solution.

Comments closed

The Power BI Field Finder

Published 2020-11-18 by Kevin Feasel

Stephanie Bruno has updated a useful tool:

If you’re like me, building a data model in Power BI is an iterative process.  Sometimes you have to try out different ways of writing measures before you hit on the one that’s right.  You end up with temporary measures that don’t actually end up being used in visuals.  You may also pull in more columns than you might end up needing, just in case.  When you’ve finally finished your masterpiece with measures and visuals, there are probably quite a few that you don’t need.  Two problems with this are that having extraneous columns and measures (1) can slow down your model and (2) can make it more difficult to maintain.  You may also want to know where on your report a change to a measure will have an impact.

Click through for a demonstration of the solution.

Comments closed

Preventing Deadlocks on Key Lookups

Published 2020-11-18 by Kevin Feasel

Erik Darling talks about key lookups:

I go back and forth when it comes to Lookups.

On the one hand, I don’t think the optimizer uses them enough. There are times when hinting a nonclustered index, or re-writing a query to get it to use a nonclustered index can really help performance.

On the other hand, they can really exacerbate parameter sniffing problems, and can even lead to read queries blocking write queries. And quite often, they lead to people creating very wide indexes to make sure particular queries are covered.

Read on for one scenario around deadlocking due to key lookups.

Comments closed

Folders in Azure Synapse Analytics

Published 2020-11-17 by Kevin Feasel


Wolfgang Strasser checks out a small but helpful addition to Azure Synapse Analytics:

Good morning, day, afternoon or night – wherever and whenever you read this blog post! My day started with a nice surprise when I connected to one of our Azure Synapse workspaces …

Sometimes, it’s those little things that make (development) life easier – you can now add folders to structure the list of development artefacts in Synapse:

Read on to see how, including how you can bring order to the chaos of existing Synapse Analytics workspaces.

Comments closed

Waterfall Visuals

Published 2020-11-17 by Kevin Feasel

Mike Cisneros takes us through cases when waterfall charts are useful:

In our workshops, we often put a grid of a dozen charts up on the screen, and say to the participants, “Most of the charts you’ll need to communicate effectively in business are right here on the screen. 99% of the time, one of the visuals you see here will get your message across effectively. And as you can see there aren’t any really unusual charts here. You’ve probably seen all of these before.” 

If, at this point, somebody in the room says, “Actually, I’ve never heard of a ______ chart before,” you can almost always fill in the blank with the word “waterfall.”

Waterfall charts are really useful in a few scenarios, but I see them get misused far too frequently.

Comments closed

PASS Summit Q&A: Intelligent Query Processing

Published 2020-11-17 by Kevin Feasel

Kathi Kellenberger has a follow-up of some questions after a PASS Virtual Summit session:

Last week, I presented a session on Intelligent Query Processing for the first ever Virtual PASS Summit. This summit had a mix of live and pre-recorded session. During the pre-recorded sessions, the speaker could hang out with the attendees in the discussion room and join a virtual chat room at the end.  My session was live, so I answered questions a few questions during the session. There were a couple of questions that I couldn’t answer fully during the session, but all the questions were interesting, so I’ll go through them here.

Click through for the questions and answers.

Comments closed

PASS Summit Q&A: The Curated Data Platform

Published 2020-11-17 by Kevin Feasel

I answer some questions:

On Thursday, I presented a session at PASS Summit entitled The Curated Data Platform. You can grab slides and links to additional information on my website. Thank you to everyone who attended the session.

During and after the session, I had a few questions come in from the audience, and I wanted to cover them here.

Most of the questions were around document databases, so check them out.

Comments closed

Finding the Physical Path of a SQL Server Backup on a Container

Published 2020-11-17 by Kevin Feasel

Jack Vamvas is looking for love files in all the wrong places:

I’m migrating some SQL Server databases to Openshift Containers. The SQL Server is set up with persistent disk , with a dedicated persistent disk partition for the SQL Server defaultbackup directory. I don’t have access to the underlying files via command line and can only use command line. How can I get the physical disk device , which will then allow me to create a RESTORE DATABASE statement pointing to the device?

Read on for the answer, including a T-SQL script to find where these files live.

Comments closed