A Fine Slice Of SQL Server
Jon Fletcher shows how to disable query processing in Power Query:
In Power BI Power Query there is an option to enable or disable whether a table is loaded into the report. The option ‘Enable load’ can be found by right clicking on the table. Typically, by default, the load is already enabled.
Click through to see how you can disable query processing or remove a particular query from report refreshes.
Comments closedJack Vamvas is hunting deadlocks:
An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users.
I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team.
Click through for the script.
Comments closedOne of the most common issues I look at from day to day is some variation of the question. “Why did it take a long time for my AG/Database to failover?”. There are many different meanings for this innocuously simple looking statement, for example was it that the failover time was long or was it a long time bringing the database online, or was it that it took a long time because a failover wasn’t possible, and what *exactly* is a long time? Are we talking a long time means 10 seconds, 1 minute, 5 minutes, 30 minutes? To each different business and their needs, “long” dramatically fluctuates. I’d like to go through at a high level, some of the most common reasons that I troubleshoot and if they might apply to your environment. FYI, if you tell me 1 second is a long time then I’m going to point you toward different architectures with multiple layers of caches and front-end servers/services which isn’t going to be cheap, but that’s what you want so you’re _willing_ to pay for it, right? Yeah, I thought not.
Click through for several factors which may affect how long it takes for a failover to occur.
Comments closedMike Scalise has an alternative to using the GREATEST() and LEAST() functions in SQL Server:
As of 4/14/21, Microsoft has officially announced that the GREATEST and LEAST functions are in Azure SQL Database and SQL Managed Instance. Unofficially, it seems they had silently included these functions in at least (no pun intended) SQL Managed Instance several months prior. In any case, here we are today with official Microsoft documentation on GREATEST and LEAST. This is all great news. What’s also great is that, in their statement, Microsoft stated they would be including these two functions in the next version of SQL Server.
But what about all of us on SQL Server 2019 and prior? Fortunately, there’s a way to mimic these two functions in your queries using a correlated subquery in the SELECT clause.
Click through for examples. This is a bit different from getting the largest or smallest value in a window, which you can do with MIN(val) OVER () or MAX(val) OVER (). But I’m looking forward to seeing GREATEST() and LEAST() in the box product.
Over the years I have written a lot about Power BI/Power Query performance but it has always been in the context of loading data direct into datasets, not dataflows. A lot of cool things have been happening in dataflows recently, though, and now that Premium Per User has made Premium features to a much wider audience I thought it would be interesting to look at an example of how PPU can help dataflow performance and specifically how and when the Enhanced Compute Engine can make dataflow refresh faster.
Click through for some interesting findings.
Comments closedAntoine Rebecq shares a warning:
I was recently working on a cool dataset that looked unusually friendly. It was tidy, neat, interesting… the kind of things that you rarely encounter in the wild! My goal was to build a super simple predictor for one of the features. However, I kept getting poor results and at first couldn’t figure out what was happening.
There’s some good, practical advice in there, so check it out. H/T R-Bloggers
Comments closedWe can specify conditional expressions like OR, AND using the Query expression during search in Elasticsearch.
We have an index named account and in the index we have details of account owners including their name, address, age, sex, employer etc.
Let’s search the documents with AGE=25 and STATE IN (‘ca’, ‘ny’) in the index.
As a spoiler, it’s not as easy as using OR, AND, and NOT, though there are synonyms.
Comments closedElie Bou Issa does some no-code automation:
Azure Logic Apps is a cloud service to help you schedule, automate, and orchestrate tasks and workflows between apps and across enterprises and organizations. A Logic App can be built using the Azure portal, or infrastructure as code.
By the end of this article, you will have a good understanding of leveraging a Logic App for Azure DevOps to automate the create of work items, in addition to creating an automated approval-based workflow using Office 365.
Click through for the demo. This is useful on its own, especially with non-technical product managers, but you can extend the use of Logic Apps quite a bit and automate more work without writing much code.
Comments closedDaniel Hutmacher shares some hard-earned wisdom:
I’m a complete beginner at Linux, so I should preface this post with the fact that these are my humble notes after hours of pulling my hair. It’s not really a fully-fledged how-to article, and there are lot of things I’m not covering. But I figured it may help someone out there at some point.
Also, different Linux distros and versions will behave differently, so your mileage will most likely vary.
For the purposes of this post, I’m on Red Hat Enterprise 8.3.
Note that this is using a Linux-based client, rather than talking about SQL Server on Linux.
Comments closedHugo Kornelis has a two-parter for us. First up is the inserted scan operator:
The Inserted Scan operator is only found in execution plans for code in triggers. It is used for queries that read data from the inserted pseudo-table. Its counterpart, Deleted Scan, reads from the deleted pseudo-table.
This pseudo-table contains a copy of all the rows that were inserted in AFTER INSERT triggers, or the new content of the data in all affected rows in AFTER UPDATE triggers. In INSTEAD OF INSERT or INSTEAD OF UPDATE triggers, the data in the inserted pseudo-table is the data that would have been inserted, or the data as it would have been after the update. In AFTER DELETE and INSTEAD OF DELETE triggers, using the inserted pseudo-table is allowed but returns no data.
This pseudo-table contains a copy of all rows that has just been deleted in AFTER DELETE triggers, or the original data of all affected rows in AFTER UPDATE triggers. In INSTEAD OF DELETE or INSTEAD OF UPDATE triggers, the data in the deleted pseudo-table is the current data in the rows that would have been deleted or updated. In AFTER INSERT and INSTEAD OF INSERT triggers, using the deleted pseudo-table is allowed but returns no data.
Click through to see how they work.
Comments closed