A Fine Slice Of SQL Server
Every time a user clicks on a row or a column in a visual, every other visual in the page feels an urge to refresh itself and respond to the click.
Visual that are based on direct query may issue multiple queries for each refresh.
Depending on the number of measures used in the visual, the number of generated queries can be as high as 10 or even more for a single visual.
Read on for more information and what you can do to avoid this problem.
Leave a CommentReitse Eskens does a performance comparison:
A few days ago, I heard someone stating that Group By was much quicker than Distinct. Less disk impact, less memory etc.
So, I thought I’d find out if it’s true or not because I found it interesting. I always thought there was no difference. I tested a single small table and found no difference in speed, reads or execution plan. But that’s no real world example. Usually the tables contain a lot of data and are joined to other tables.
Click through for the results of Reitse’s analysis.
Leave a CommentMatthew McGiffen takes a hint:
Another neat little feature in SQL Server 2022 is Query Store Hints. This is the ability to apply a query hint through Query Store rather than having to modify existing code or fiddle around with plan guides.
Query hints are a way to influence optimizer behaviour towards generating desired execution plans for a given query. The word “hint” is a bit of a misnomer as usually they mandate what you wish to happen.
Right. They’re ‘hints’ in the way that my wife ‘hints’ that I should take out the garbage.
Comments closedMatthew McGiffen wants you to put yourr wallet and your best plans in the bag:
I’ve been reading Bob Ward’s book “SQL Server 2022 Revealed” and one of the new features in Intelligent Query Processing (IQP) jumped out at me. Not because it’s going to rock my world but rather it reminded me of an idea I had when I first started playing with Query Store when it came out.
I thought it would be cool if the execution plans cached in Query Store could be used to populate the plan cache after an instance restart, removing the need for all queries to be recompiled. I even raised a suggestion on Microsoft Connect for it to be considered as a new feature.
Click through to see what Microsoft came up with along these lines.
Comments closedJoe Obbish looks at time zones:
Queries that use the AT TIME ZONE operator may perform worse than desired. For example, Jonathan Kehayias blogged about performance struggles with AT TIME ZONE at sqlskills.com. One key difference between Jonathan’s opinion and my own is that I enjoy writing these kinds of posts.
Read on for the details and check out some work Joe has done around time zone friendly functions as an alternative to AT TIME ZONE.
Lee Markum has seven lessons for us:
SQL Server indexing basics are critical to query and server performance. Resources, like CPU and disk, are affected by the indexes that you have, or the ones you’re missing.
In the StackOverflow2013 database we’re going to look at Badges and users. Specifically, I want to start by seeing what badges a user has and when that user received them. Some badges, because of the type of badge it is, can be awarded more than once.
Click through for a demonstration.
Comments closedKate Smith points out a new feature in SQL Server 2022:
In SQL Server 2022, we introduced a new feature called DOP feedback. This feature will look at any parallel query and determine if it might perform better with a lower degree of parallelism than currently being used. For example, perhaps 16 threads will perform better than 20 if there are a lot of waits on other threads. It will test out the new degree of parallelism and, either decide that this was a good change and keep the 16 threads, or it will revert to previous levels of parallelism and go back to 20 threads. If the new degree of parallelism is good, then this optimization is persisted inside the query store and will be applied appropriately to a query for future executions.
Read on for an overview of how it works and what protections are in place to keep it from going completely bonkers. Well, more completely bonkers than what you already have.
Comments closedErik Darling notes a problem with time-based filters on operations:
There are six waitfor commands that each pause for 1 second. In between them are queries that finish in milliseconds.
If I watch the event output, eventually, I’ll see this:
Okay, so the stored procedure took more than 5 seconds, but… no individual query took more than 5 seconds.
This happens a lot and I’m not sure there’s a good answer for it.
Comments closedItzik Ben-Gan shows a good use of a pair of new T-SQL functions:
Comments closedTime-based grouping and aggregation are common in analyzing data using T-SQL—for example, grouping sales orders by year or by week and computing order counts per group. When you apply time-based grouping, you often group the data by expressions that manipulate date and time columns with functions such as YEAR, MONTH, and DATEPART. Such manipulation typically inhibits the optimizer’s ability to rely on index order. Before SQL Server 2022, there was a workaround that enabled relying on index order, but besides being quite ugly, it had its cost, and the tradeoff wasn’t always acceptable.
Far and away, some of the trickiest situations I run into when helping clients is rewriting scalar functions that have WHILE loops in them.
This sort of procedural code is often difficult, but not impossible, to replace with set-based logic.
Erik improves a function in this post, though often, the best way to improve a function is not to play the game at all.
Comments closed