Andrea Allred has a script for us:
This week, I had a co-worker that was stuck. They no longer use SSMS and needed to rename a database. They asked if I had a script and so I wrote one. Here it is:
Read on for that script.
Comments closedCategory: Administration
Chris Webb continues a series on Power BI monitoring:
In my last post I showed how to use Log Analytics data to analyse Power BI query activity. The problem with looking at a long list of queries, though, is that it can be overwhelming and it can be hard to get a sense of when users were and weren’t actively interacting with a report. In this post I’ll show you how you can write a KQL query that gives you a summary view that solves this problem by grouping queries into sessions.
Click through to see what Chris means by the term “session” and for the KQL to do the job.
Comments closedDavid Alcock reviews the latest SQL Server 2019 cumulative update:
Microsoft recently released Cumulative Update 15 for SQL Server 2019. It contains a bunch of fixes and some improvements, I get a bit geeky with updates like this and love to have a look through the different fixes to see “Physical reads for read-ahead reads are counted incorrectly (two times) when you run queries. Therefore, the information in sys.query_store_runtime_stats and sys.dm_exec_query_stats shows incorrect values.”
Read on to see what this means and a quick test to see if it works as expected.
Comments closedJose Manuel Jurado Diaz hits on an issue with row-level security:
Today, I worked on a service request that our customer reported that running a complex query this is executing in parallel but having more than 2 vCores in Azure SQL Database this query is not using parallelism.
During the troubleshooting process we suggested multiple tips and tricks, but any of them made that Azure SQL Engine uses parallelism:
Being on-premises versus in Azure turned out to be a red herring and the solution was something maybe even more difficult to spot than triggers.
Comments closedSteve Jones is trying to find where he left his keys:
I’ve been working with encryption in SQL Server for a long time, and have delivered quite a few presentations on the topic. Recently I was updating some code and wanted to check if a database had a master key created in it. This post shows how to do that.
Click through to see how you can create a database master key as well as how to drop it if necessary.
Comments closedInstant File Initialization (IFI) enabled on SQL Server, for data files only, leads to faster execution on certain file operations, as it bypasses the zeroing procedure while reclaiming disk space. Instead, disk content is overwritten as new data is written to the files.
But do these benefits continue when Transparent Data Encryption (TDE) is enabled ?
Click through for the answer.
Comments closedI have a post correcting a statement I made before:
The single biggest problem I have with compute instances is that there is no auto-stop functionality to them. This is really frustrating because you’re paying for that virtual machine like you would any other, so if you forget to turn it off when you go home for the weekend, it’ll cost you. I wish there were a built-in option to shut off a compute instance after a certain amount of inactivity. Instead, you’ll need to start and stop them manually.
It turns out that you can and so I wanted to write a post to correct the record.
Click through to see how you can do this. You can bet that I’ve got it enabled now.
Comments closedBob Dorr explains what those negative session IDs actually mean:
SQL Server may report a blocking session id as a negative integer value. SQL Server uses negative sessions ids to indicate special conditions.
Click through for the table. Bob also includes information on -5, the “any task/session can release the latch” scenario. This also covers information on the latches themselves and is worth keeping around in case you run into an issue at some point.
Comments closedChris Webb continues a series on using Log Analytics:
It’s actually very easy to build a simple KQL query to look at query activity on your datasets: you just need to look at the QueryEnd event (or operation, as its called in Log Analytics), which is fired when a query finishes running. This event gives you all the information you need: the type of query (DAX or MDX), the duration, the CPU time, the query text and so on. The main challenge is that while you also get the IDs of the report and visual that generated the query, you don’t get the names of the report or visual. I wrote about how to get a list of visual and report IDs here and here, but how can you use that information?
Read on to see how.
Comments closedWith this post, I’m starting an occasional series on the transaction log and how it works and should be managed, and I’ll touch on all the problems above over its course. In this post, I’ll explain what logging is and why it’s required.
Basic Terminology Around Logging
When I’m talking about any mechanism in SQL Server, I find there’s a chicken-and-egg problem where I need to use a word or phrase before I’ve explained it. To avoid that problem in this series, I’m going to start by explaining some terminology that needs to be used when discussing logging, and I’ll expand on many of these terms as the series progresses.
This post starts off with some of the basics and it’s always good to get the occasional refresher on the basics.
Comments closed