Press "Enter" to skip to content

Day: November 18, 2020

An Intro to Time Series Databases

Kyle Buzzell looks at time series databases:

As the name implies, a time series database (TSDB) makes it possible to efficiently and continuously add, process, and track massive quantities of real-time data with lightning speed and precision. While other database models have been used for these kinds of workloads in the past, TSDBs utilize specific algorithms and architecture to deal with their unique needs.

In this piece, we’ll take a deeper look at time series databases, including the unique needs of the workloads they’re built for, their benefits, common use cases, and the TSDBs out there.

Click through for an overview. Time series databases are definitely a niche product, but they are really good inside that niche.

Comments closed

Tracking Cosmos DB Re-Indexing Progress

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

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

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

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

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