Press "Enter" to skip to content

Day: January 22, 2024

The Importance of Data Retention Policies

Published 2024-01-22 by Kevin Feasel

Ed Pollack shares some great advice:

It is always an afterthought. New objects are created that start off small and current. New feature development takes over and the recently architected data structures become old news. Over time, data grows and suddenly a previously small table contains millions or billions of rows.

Is all that data necessary? How long should it be retained for? If there is no answer to this question, then the actuality may be “Forever”, or more honestly “No one knows for sure.”

Retention takes on many forms and this article dives into ways in which data can be managed over time to ensure that it is fast, accurate, and readily available.

We don’t tend to think about data retention in the development phase, but it’s an important consideration and thinking about it up-front might save you disk space headaches later.

Comments closed

Explaining FIRST_VALUE() and LAST_VALUE() Execution Plans

Published 2024-01-22 by Kevin Feasel

Hugo Kornelis wraps up a mini-series on window functions:

In part twenty-eight of the plansplaining series, I’ll wrap up the six-part mini-series on window functions. After covering the basics, fast-track optimization, window frames ending at UNBOUNDED FOLLOWING, window frames specified with RANGE instead of ROWS, and LAG and LEAD, we will look at the LAST_VALUE and FIRST_VALUE analytical functions, and find that a function we would have expected to be available as an internal aggregate function does not exist at all! We’ll also find out how SQL Server works around that.

Click through to unravel that mystery.

Comments closed

Tips for Saving Money in the Cloud

Published 2024-01-22 by Kevin Feasel

David Klee offers up some advice:

You might be able to shave off thousands – or more – in your monthly cloud bills for your critical SQL Servers, all while maintaining or even improving performance.

Public cloud providers charge organizations for everything they deploy, and while a few items in the cloud are based on a pay-by-consumption model, most of the services that really stack up, namely SQL Server licensing, are charged on a pay-by-allocation model. If you provision a SQL Server VM with eight cores and promptly forget about it, you will be shocked at the end of the month when you receive your cloud subscription bill. If you provision a 32-core VM, and your workload only uses four cores, you are still paying for all 32 cores, regardless of the low utilization rates. The same goes for memory and storage. A large memory footprint and lots of high-speed attached managed disks all add up.

Read on for some advice around figuring out the right server size for your workload.

Comments closed

Fixing Non-SARGable Predicates

Published 2024-01-22 by Kevin Feasel

Erik Darling makes the donuts:

Okay, deep breath. Deep, deep breath.

Would you believe I still have to fix queries with a bunch of functions in joins and where clauses? Like every day? All day?

Where things get tough is when there’s some third party vendor app where code can’t be changed, but customers have become so fed up with performance that they’re willing to make other changes to help things along.

Read on for an example of one scenario in which reversing an index can help improve performance without touching the code.

Comments closed

New Limits for Maximum Connections per Data Source in Power BI

Published 2024-01-22 by Kevin Feasel

Chris Webb notes a change:

One of the most important properties you can set in a Power BI DirectQuery semantic model is the “Maximum connections per data source” property, which controls the number of connections that can be used to run queries against a data source. The good news is that the maximum value that you can set this property to has just been increased in Premium.

Read on to learn why this setting is important.

Comments closed