A Fine Slice Of SQL Server
Kristi Cantor notes a Power BI update:
Hello P3 Adaptive Nation! Happy New Year, ring out the old and ring in the new! Speaking of ringing in the new, with all the hustle and bustle associated with the holiday season and the excitement of welcoming the new year, did anybody happen to notice the new feature quietly rolled out in Power Bi back in November to take the edge off creating and maintaining custom pages and bookmarks?
Read on to see what has changed.
Comments closedEd Pollack explains how Vertipaq compression works to make columnstore indexes so efficient:
Columnstore compression is an impressive array of algorithms that can take large analytic tables and significantly reduce their storage footprint. In doing so, IO is also reduced, and query performance dramatically improved.
This article dives into one aspect of columnstore compression that tends to get buried in all of the hoopla surrounding how awesome columnstore indexes are: Vertipaq optimization. This is a critical component of the columnstore compression process, and understanding how it works can significantly improve the performance of analytic workloads while reducing the computing resources required for the underlying data.
Click through for the steps of the process.
Comments closedMichael Swart gets out the ruler:
The configuration setting cost threshold for parallelism has a default value of 5. As a default value, it’s probably too low and should be raised. But what benefit are we hoping for? And how can we measure it?
The database that I work with is a busy OLTP system with lots of very frequent, very inexpensive queries and so I don’t like to see any query that needs to go parallel.
What I’d like to do is raise the configuration cost threshold to something larger and look at the queries that have gone from multi-threaded to single-threaded. I want to see that these queries become cheaper on average. By cheaper I mean consume less cpu. I expect the average duration of these queries to increase.
Read on for Michael’s results, and I appreciate somebody actually testing and measuring rather than pulling a number from a hat.
2 CommentsDeepthi Goguri takes us through hint usage in Query Store:
Query Store hints is another amazing feature added and is available in the preview mode in Azure SQL Database, managed instances, elastic pools and hyperscale databases as well. Query Store hints are just like the hints you use in your queries but the difference is you do not have to change the query code to apply these hints using Query store specifically designed stored procedure- sys.sp_query_store_set_hints. Cool, right?
Yes. Yes it is.
Comments closedAdrian Tam shows how you can test the performance of calls in Python:
Profiling is a technique to figure out how time is spent in a program. With this statistics, we can find the “hot spot” of a program and think about ways of improvement. Sometimes, hot spot in unexpected location may hint a bug in the program as well.
In this tutorial, we will see how we can use the profiling facility in Python. Specifically, you will see
– How we can compare small code fragments using
timeitmodule– How we can profile the entire program using
cProfilemodule– How we can invoke a profiler inside an existing program
– What the profiler cannot do
Read on for those techniques.
Comments closedI was working on an upgrade recently, trying to move from SQL Server 2016 to SQL Server 2019. I wanted to perform an upgrade in-place on an Azure VM, but needed to get the product key. There are a few places where you can find Powershell functions to get this product key, with Ryan @ Ryadel’s post being the most well-known. That method covers versions from SQL Server 2005 through 2014 (if you follow the notes in the blog post), but breaks on 2016.
Click through for a script which works for SQL Server 2016 and later.
Comments closedThe Hadoop in Real World team explains the difference between two partitioning strategies:
The difference between static and dynamic partitioning only exists when the partition is being created based on how the partitions are added to the table. Once the partitions are created, the tables won’t have any difference like static and dynamic partitions. All partitions are treated and one and the same.
Click through for the difference.
Comments closedIn this blog post we shall consider some of the strategies for improving data loading performance in Azure SQL Managed Instance. These strategies apply to the repeatable ETL processes, meaning that if there a problem, data loading process can be repeated without loss of any bit of data or its consistency. Of course, these strategies do require a prepared design for the possibility of the repetition, but this is a basic requirement for any data loading process.
There are many great ways of ensuring high performance data loading and this blog post does not pretend to be exhaustive an ultimate resource, but it will provide a couple of known paths that can drastically improve the performance of the Log Throughput in Azure SQL MI.
Click through for some tips, including some which make sense on-prem and a couple which are specific to platform-as-a-service offerings.
Comments closedGrant Fritchey gets our hopes up:
Everyone knows that you only get a single clustered index, right? Wouldn’t it be great though if you could have two clustered indexes?
Well, you can. Sort of. Let’s talk about it.
Click through to see what Grant means. This is a thing that I’ve done occasionally, though much more often, I’ve ripped it out because Database Tuning Advisor suggested it and a credulous user took DTA at its word.
Comments closedAaron Bertrand explains why it’s probably a good idea to enable indirect checkpoints:
At Stack Overflow, the upgrade to SQL Server 2019 was not as smooth as expected. It introduced long recovery times and high CPU on a secondary, during synchronization activity after failover, patching, and network connectivity testing. This symptom wasn’t present during similar activities and workloads under SQL Server 2017.
Aaron points out that if you have databased created in older versions of SQL Server, enabling indirect checkpoints can provide a performance boost to certain activities around log writing., including backup times.
Comments closed