A Fine Slice Of SQL Server
Gilbert Quevauvilliers checks out the Power BI audit logs:
When I review the Power BI Audit logs, I am always finding either new operations or new additions into the audit log.
In my example below I found a way to quickly and easily identify Power BI REST API calls that are being used.
Click through to figure out how.
Comments closedErik Darling puts his pants on one leg at a time and once his pants are on, he makes gold records:
One persistent idea is that tempdb is something to be avoided. Either because it was “slow” or to avoid contention.
Granted, if a query has been around long enough, these may have been valid concerns at some point. In general though, temp tables (the # kind, not the @ kind) can be quite useful when query tuning.
Erik is absolutely right in this post. Ceteris paribus I’d rather not directly use tempdb because I’d prefer one query over multiple queries. But once performance comes into question, working on smaller subsets of data one step at a time will typically give you at least an acceptable solution.
Comments closedElizabeth Noble continues a series of videos on database projects in Azure Data Studio:
This week, we’ll talk about one of the easier ways to deploy your database changes. One of the benefits of database projects is that they can generate data-tier applications (DAC). The data-tier applications can be bundled into what is called a DACPAC. This is a collection of files that can be used to deploy your database.
Click through for the video.
Comments closedTomaz Kastrun builds a function:
Once we have a data.frame we want to generate Python dictionary that will hold schema and data for direct creation (or import) into your favourite Python environment. Function RtoPy does the needed transformation:
Click through for the code.
Comments closedDinesh Asanka takes us through the cross-validation component in Azure ML Studio:
Let us look at implementing Cross-Validation in Azure Machine Learning. Let us use the sample Adventure Works database that we used for all the articles.
Then Cross Validate Model is dragged and dropped to the experiment. The Cross Validate model has two inputs and two outputs. Two inputs are data input and the relation to the Machine Learning technique. Let us use the Two-Class Decision Jungle as the Machine Learning Technique. Then the first output is connected to the Evaluate Model as shown in the following figure:
Click through for the process.
Comments closedBrent Ozar has another year of salary data:
We asked what you make, and 1,747 of you in 69 countries answered. Altogether, you made $171,879,034 this year. Hubba hubba, there’s some earning power in this audience.
Download the Data Professional Salary Survey results (XLSX).
Click through for more details on the process.
Comments closedEric Cobb takes a look at some nice functionality in dbatools:
There may be times that you want to ensure certain settings are applied to a database, or multiple databases. For example, if you restore a Production database to a QA environment, you may need to change the Recovery Model. Or if you’re migrating databases to a new SQL Server version you want to make sure to update the Compatibility Level. With dbatools this is really, really easy. Here are some examples:
Read on for examples around setting the database owner, changing the compatibility level, and setting the recovery model.
Comments closedJack Vamvas contrasts Kerberos and NTLM:
There is a message found in SQL Server Error Logs similar to
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:60000 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
The line I’m interested in reviewing is Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. It’s good to first understand the differences between Kerberos & NTLM – both supported by SQL Server during AD authentication
Read the whole thing. It’s easy to fall into the trap of “Windows authentication = Kerberos”—I do that myself far too often.
Comments closedMelissa Coates has an update for us:
An updated version of the Power BI End-to-End diagram is now available as of January 4, 2021.
This update is as of January 4th and includes things like Azure Purview. Melissa also shows how it has evolved over time, which is particularly interesting for Power BI given its rate of change.
Comments closedMatthew McGiffen has a query for us:
Some time ago I wrote a query store version of the “Top 20 queries” query that will produce a ranked list of your most expensive queries – and I’ve ended up using this a lot.
The only downside of using the DMVs for Query Store is that they are per database whereas dm_exec_query_stats is a view across the whole instance. So I had to use a cursor and a temp table, populating the temp table for each database in turn.
Click through for the query.
Comments closed