A Fine Slice Of SQL Server
Monica Rathbun notes that SSMS lets you change service-level objectives for Azure SQL Databases:
Sometimes as a DBA, I am lazy and want the ability to execute all of my tasks in one place. Lucky for me I discovered the other day that I can change my Azure SQL Database Service Level Object options within SQL Server Management Studio (SSMS) without ever having to go to the Azure Portal.
Read on to learn how, as well as what you can change.
Comments closedAjay Dwivedi has a function to build out time duration as a string:
As DBA, there are various scenarios where I display duration in results. More often, for visual effects, I like to convert the same duration into [DD hh:mm:ss] format.
For example, for representing the duration of total waits, resource waits & signal waits for wait types from [sys].[dm_os_wait_stats].[wait_time_ms] on Grafana dashboard by converting to [DD hh:mm:ss] format:-
Click through for the function.
Comments closedMat Hayward-Hill shows how to use PSSDIAG and SQL Nexus. First, PSSDIAG:
Once you have worked on enough SQL Server performance issues, you start to develop an approach and lean on the same tools, to collect and present your data. I will walk you through how I troubleshoot a SQL Server performance issue, having no prior knowledge of the system. Typically, the description of the problem goes along the lines of “it was fine last week, but today queries are taking forever or timing out.”
If the issue is with one query, I will start with the Query Plan. Otherwise, when the problem is system-wide, and the SQL instance sits on top of a Windows on-prem or IaaS server, I use PSSDIAG to gather the data.
This post is the second part of how I troubleshoot a SQL Server performance issue like a pro. In Part 1, we used PSSDIAG to collect performance data. Now, we will look at how to load and analyse that data using SQL Nexus.
Before we dive straight into SQL Nexus, there are a few prerequisites that you need to install. I will confess I didn’t get this right on my first attempt so to make it easier for you, I have included the requirement along with the link of where to find it.
Check out both posts.
Comments closedJess Pomfret shares two methods of moving dbachecks results into Excel:
I got a message from a friend on Twitter last night asking ‘Is there an easy way to get dbachecks backup info into an Excel spreadsheet?’. I sent them a couple of ideas, but figured this is a great use case that many people might be interested in. Pairing infrastructure testing using dbachecks with creating Excel reports with the ImportExcel module is a great addition to your automation tool belt. I also had ImportExcel on my mind this week after watching some great demos from Mikey Bronowski (b|t) at a user group earlier this week.
Click through for both examples.
Comments closedOne of most frequent questions through our Delta Lake Tech Talks was when would DML operations such as delete, update, and merge be available in Spark SQL? Wait no more, these operations are now available in SQL! Below are example of how you can write delete, update, and merge (insert, update, delete, and deduplication operations using Spark SQL
Read on for the full update.
Comments closedJosh Darnell explains why you might get threadpool waits even when you think you shouldn’t:
I occasionally see (usually brief)
THREADPOOLwaits on systems that are really not all that heavily loaded. This is my investigation into why. Some might say I have too much time on my hands.Before getting into these unusual
THREADPOOLcases, let’s cover the normal ones.
Read the whole thing. It’s example #9068 of how a particular wait is not always a bad thing.
Comments closedNathan Thompson walks us through a mutation testing experiment:
Since our hypothesis was that the implementation differences between Jasmine and Jest could affect the Mutation Score of our legacy and new test suites, we began by cataloging every bit of Jasmine-specific syntax in our legacy suite. We then compiled a list of roughly forty test files that we would target for Mutation Testing in order to cover the full syntax catalog. For each file we generated a Mutation Score for its legacy state, converted it to run in our new Jest setup, and generated a Mutation Score again. Our hope was that the new Jest framework would have a Mutation Score as good as or better than our legacy framework.
By limiting the scope of our test to just a few dozen files, we were able to run all mutations Stryker had to offer within a reasonable timeframe. However, the sheer size of our codebase and the sprawling dependency trees in any given feature presented other challenges to this work. As I mentioned before, Stryker copies the source code to be mutated into separate sandbox directories. By default, it copies the entire project into each sandbox, but that was too much for Node.js to handle in our repository:
In my undergrad days, I loved mutation testing mostly because of the terminology. I’m happy to see a proper implementation of mutation testing and I’m even happier to see that they have a .NET version.
Comments closedMikey Bronowski continues a series on dbatools:
At some point, you have lots of different objects between replicas like logins, credentials, agent jobs, other agent objects. In some scenarios, it is good to have them all in sync. I am going to show you the -WhatIf output just to get an idea.
This is one of the (many) areas where dbatools makes life quite a bit easier.
Comments closedFred Kaffenberger poses a question:
If you can ask, how do we know that we are improving, you should also be able to ask how do we know that the security roles are implemented correctly. Data culture is not just for the business, but for the reporting team as well. I haven’t seen much discussion of auditing security roles in Power BI circles, so I’m genuinely curious about how others tackle this issue. Does everyone simply work hard and hope for the best? Or do you restrict everything at the database level and use different apps for different groups instead? There may even be regulatory reasons which require you to restrict it at the database level. But even if you do restrict everything at the database level, you still need to validate that security as well.
Read on for a verification technique.
Comments closedSoheil Bakhshi shows how we can take an input from Power Query and pass it to a stored porcedure:
This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.
The solution is somewhat easy.
If you’re familiar with SQL Server Reporting Services, the solution instantly makes sense.
Comments closed