Monitoring Heartbeats in SQL Sentry
SQL Sentry uses a service to monitor targets. But what if this monitoring service is down? Who will alert about the alerter not alerting? Who is watching the watchers? Who is monitoring the monitor?
Filtered Indexes with TRY_CONVERT, ISNUMERIC, and other complex expressions
Who doesn’t like Filtered Indexes, am I right? They’re pretty great, honestly. If… of course… you’re able to actually create them and utilize them in your specific use cases. That… unfortunately, could often become a complicated, if near impossible, task. So, what if our use case is not trivial enough to allow for filtered indexes?
Read More »Filtered Indexes with TRY_CONVERT, ISNUMERIC, and other complex expressions
Detect Weak Passwords in SQL Server
This is a T-SQL script that I’m cross-publishing with the official Madeira Data Solutions blog.
This script generates various permutations and variations of common and weak passwords and uses the PWDCOMPARE function to check whether there are any enabled SQL logins that have these passwords.
Read More »Detect Weak Passwords in SQL Server
Detect Low PAGE Compression Success Rates in all Databases
Thanks to Paul Randal and his recently published blog post: “The Curious Case of… tracking page compression success rates“, I finally discovered what was the purpose of the column page_compression_success_count from the system function sys.dm_db_index_operational_stats, and how to use it.
Read More »Detect Low PAGE Compression Success Rates in all Databases
Performance Tuning Like a Pro – with Hypothetical Indexes
Every once in a while there comes an opportunity to “upgrade” your abilities with newly acquired knowledge that lets you “step up your game” and possibly add some “wow factor” to your work, leaving your peers awe-struck by your amazing new “magic trick”.
As a SQL Server consultant, one such opportunity that I had in my line of work, is when I learned about “Hypothetical Indexes” and how to use them.
Read More »Performance Tuning Like a Pro – with Hypothetical Indexes
It is 10 PM, do you know where your pages are?
Back in April 2020, I created an open-source project called “SQL Server Page Allocation Reports“. It consisted of a set of SQL queries and some Power BI reports that can be used for visualizing the size and locations of your data and transaction log pages.
Well, recently I also added SSMS Custom Reports into the mix. So, it’s time to revisit this project and see what’s new!
Read More »It is 10 PM, do you know where your pages are?
T-SQL Tuesday #143 – Short Powershell code to move DB files in AlwaysOn
This month’s #tsql2sday is hosted by John McCormack who asks us to share some useful snippets of code that can help us in our everyday jobs.
Well, honestly, this is more or less what my blog is mostly about anyway: sharing insights, scripts, and code snippets that I found to be helpful in my day-to-day job as a SQL Server consultant.
Read More »T-SQL Tuesday #143 – Short Powershell code to move DB files in AlwaysOn
The Simplest Alternative to sp_MSforeachdb
Which drawbacks are there to using the built-in sp_MSforeachdb stored procedure? What other alternatives are out there? Is there a simpler solution? Find out all this and more in this short post.
Read More »The Simplest Alternative to sp_MSforeachdb
Finding a use for Extended Properties in SQL Server
“Extended properties allow you to add custom properties to database objects”, so says the official Microsoft documentation. However, very few DBAs make use of them, if at all. This is actually a good thing, because indeed the scenarios in which this feature could be useful are relatively few and rare.
On the other hand, I also see DBAs essentially jumping through hoops to implement something, which could in fact very easily be implemented using Extended Properties.
What are these peculiar use cases? Let’s look at a few examples.
Read More »Finding a use for Extended Properties in SQL Server
The Asynchronous Ledger Trick for Fast SQL Server Insert, Update and Delete Processes
Do you find yourself facing performance problems and long lock chains caused by very frequent INSERT, UPDATE, or DELETE statements being executed on a table? Check out this neat trick that could help you out and make all the difference in the world.
Read More »The Asynchronous Ledger Trick for Fast SQL Server Insert, Update and Delete Processes