Spinlock contention is always a real headache to deal with. I recently saw an issue when spinlock contention on SOS_CACHESTORE was making the server virtually unresponsive. The issue was very intermittent with no obvious pattern but the assumption is that it was caused by a particular process in the application. Finding what that process was... Continue Reading →
External Temp Tables Preventing Plan Reuse in Stored Procedures
Here's an interesting issue that recently came up. We were seeing very high compilations and recompilations on a server to the point that it started causing us some very serious issues (admittedly this wasn't the sole issue but it was certainly a contributing factor, the other factors were also very interesting so I might look... Continue Reading →
sp_RestoreScript 1.9 – a dirty little bug squished!
It look like we had a little bug in sp_RestoreScript that was putting a rogue 0 after the backup file name. That's now been fixed in 1.9. For information and documentation please visit https://sqlundercover.com/2017/06/29/undercover-toolbox-sp_restorescript-a-painless-way-to-generate-sql-server-database-restore-scripts/ Keep an eye out for sp_RestoreScript 2.0 which is currently in development and will bring a while load of new features... Continue Reading →
My Application is Getting SQL Timeout Errors, But What Query is the Problem?
This is something that comes up time and time again. A developer or member of the service desk comes up to me and says "SQL's timing out, you see what the query is?". Well I'll tell you now that SQL's not timing out your query, SQL will happily run a query for a year and... Continue Reading →
Inspector V2.6 now available
As always the code can be found here in our github repoWe updated our sample report hereWe forgot to mention V2.5! We added the ability to add Thresholds per drive for the drivespace module.#270 #279 - Improved the server settings module, if server changes have been made between collections this module will now pick them... Continue Reading →
When Read Committed Snapshot Goes Bad – Version Store Filling TempDB
We all had those calls, it's the middle of the night and we get that phone call that a customer is getting error messages due to their tempDB being full. My first step is usually going to be to have a look and see what sessions have allocations in TempDB, we can do that quickly... Continue Reading →
Encryption in SQL Server #1 – Column Level Encryption
For one reason or another data security and encryption has been coming up quite a bit in my day to day work recently and I've started to realise that it seems to be one of those things that people aren't all that aware of. As I've been meaning to write a little series on encryption... Continue Reading →
Generate a Random Number for Each Row in a Query {a better way}
A couple of years ago I wrote a post on how to generate a random number in SQL Server. https://sqlundercover.com/2017/06/22/generating-random-numbers-for-each-row-over-a-specified-range-and-other-funky-stuff-you-can-do-with-them/ It's ok and it works, it's also a method that I used for quite a while. Why not use RAND() I've heard people say, well the issue with RAND() is that it will generate the... Continue Reading →
T-SQL Tuesday #135 – The Tools That Make My Job Awesome!
Blog posts have been few and far between from SQL Undercover lately but what better to try and get us back in the swing again than with T-SQL Tuesday. This month's invitation comes from Mikey Bronowski https://www.bronowski.it/blog/2021/02/t-sql-tuesday-135-the-outstanding-tools-of-the-trade-that-make-your-job-awesome/ There are so many tools that I use day in and day out to make my life easier... Continue Reading →
Undercover Academy – SQL Server Backup and Recovery – Module 1: Taking and Restoring a Backup
Welcome along to the first module in our Undercover Academy's SQL Server Backup and Recovery course. In this module we're going to look at how to take and restore a full and differential backup. https://youtu.be/rVUuXvJK8XY