A Fine Slice Of SQL Server
Martyn Jones continues a series on g-g-ghosts:
The first blog post introduced the ghost cleanup process, why it’s used and how it works. In this post, we’ll set up a demo to see the process in action.
Click through for a relevant extended event session definition, as well as what can generate ghost records and one way to clean them up.
Comments closedJon Lunn looks at a stored procedure:
Microsoft have rolled out a new ‘sys’ stored procedure called ‘sys.sp_get_table_health_metrics’ for checking the health of your tables in your Lakehouse. And it outputs a lot of metrics looking at the state of your table and anomaly detection in them.
What does it check for?
Click through for that answer, as well as some of the things you should watch for in the procedure results, and what you can do if it does catch something.
Comments closedReitse Eskens checks the logs:
As you’ve probably heard and read before, monitoring your Fabric environment as a whole is quite important. It really does help to know what’s going on.
Now, one thing I’ve learned over all these years is that report users do quite like their data to be as fresh and up to date as possible. And, when the data seems stale, they tend to ask questions.
Read on for some notes covering how to refresh a semantic model, when you might want to, how to automate it, and how to monitor the refresh process.
Comments closedMartyn Jones sees dead records:
When a delete operation is executed, or when an update causes a page split, a marker is set in the page header, and each row is marked to show that the record is to be deleted; this is the process of creating ghost records. The concept is to improve performance as the data is not immediately removed, and, with row-level locking, it helps minimise blocking and supports efficient concurrency under typical isolation levels such as Read Committed and above. If a transaction is later rolled back, the cost of effectively restoring the row is considerably reduced as the data doesn’t need to be reinserted, only the marker removed. Later demos will show this in action.
This is the first part of a longer series on ghost records and ghost cleanup.
Comments closedGilbert Quevauvilliers builds a pipeline:
In this post I will show how I used GitHub Copilot CLI / Agent mode in VS Code to create a Microsoft Fabric pipeline that checks Lakehouse table health and only optimizes tables that require maintenance. I’ll also show the prompts I used, the issues I ran into, and how Copilot helped me resolve them.
Recently Microsoft announced Lakehouse table health. The post showed how you can check can check the health of your Lakehouse table in a very simple way.
Read on to see what this feature can do, as well as how Gilbert was able to gin up (with the help of GitHub Copilot) a pipeline to optimize lakehouse tables. What I appreciate is that Gilbert also included the errors in the process, rather than making it seem like everything with perfectly the first time around.
Comments closedDenny Cherry has a new VS Code extension:
Are you a user of VS Code, but you wish that the MSSQL extension had more of the SQL Server Management Studio features? Good news, there’s now a VS Code Plugin that gives you those capabilities in VS Code by using the “SSMS Tools for SQL Server” extension.
Click through to see what it includes. There’s a pretty decent amount of existing SSMS functionality in this extension, especially on the database administrator side, where Azure Data Studio and VSCode historically have not focused.
Comments closedLaurenz Albe uncovers an oddity:
Recently, I helped a customer investigate database problems. It turned out that these problems could be traced back to too many tables in the database. Since this may come as a surprise to many users, I thought it worth the while to write about it.
Click through to see what was happening, as well as a demonstration of the problem. Granted, when you start talking about tens of thousands of tables, something has gone catastrophically wrong in your database architecture.
Comments closedJeff Iannucci explains an issue:
We were attempting to install a troubleshooting stored procedure in the master database of a SQL Server 2016 instance when we received the following error.
Msg 195, Level 15, State 10, Procedure sp_ShootTheTrouble, Line 227 [Batch Start Line 7]
‘TRY_CONVERT’ is not a recognized built-in function name.
This was unexpected, as TRY_CONVERT has been a command since SQL Server 2012. As a consequence, we were unable to install the stored procedure.
Tracking system database compatibility levels is a minor chore but an important one after an upgrade.
Comments closedJoey D’Antoni describes a problem:
All that being said, customers buy online services and expect them to be available. One of the reasons a company chooses Fabric, Databricks, or Snowflake is the notion that those platforms for Spark and various data warehousing options will be secured, patched, and better maintained than a non-technology company could do by simply deploying Spark into Kubernetes or VMware. With that, the cloud providers have an obligation to deliver services to their customers, and deliver availability and performance congruent with their pricing.
One of the things I expect from a cloud provider is honest post-mortems when they have an outage, and maintaining a history of their outages. These histories help architects better design systems, as we can better identify weaknesses in various cloud services that we might want to design around. Azure and AWS both do an excellent job of providing detailed information around “what happened” in incidents.
But as Joey mentions, Microsoft Fabric doesn’t have the same information. It also doesn’t have a dedicated SLA. But it does have a series of outages over the past month.
Comments closedEitan Blumin gives us a riddle:
Ever ran
DBCC SHRINKFILE, watched it finish without a single error… and then discovered that your file is exactly the same size as before? You run it again. Same thing. And again. Nothing. Meanwhile you know there are gigabytes of empty space in there, taunting you. Welcome to one of the most maddening little corners of SQL Server.
Read on to understand why this happens and what you can do to fix it.
Comments closed