Hi, I'm Andy. My pronouns are he/him.
Practical ways to be an ally to trans and nonbinary people, including why using someone’s pronouns matters.
Continue readingThe blog of Andy M Mallon
Racism is our problem to solve
This month’s T-SQL Tuesday is hosted by Kenneth Fisher (blog|twitter). Kenneth asked us to talk about any non-SQL tips and tricks we have. I’m going to take the moment to give some tips on how (and why) you can help fight bigotry, racism, and racial injustice.
Continue reading
Pride is a riot
A look at Stonewall’s history and the ongoing work of supporting LGBTQ rights and inclusion.
Continue reading
Find the SQL Server service account with sys.dm_server_services
Use sys.dm_server_services to find SQL Server service accounts, startup times, and instant file initialization status.
Continue reading
Making non-SARGable queries seek, without changing the query
We’ve all been there. You’ve got a query where the JOIN or WHERE predicate is not SARGable. You’ve read about how this can be a problem, and how bad it is for performance. Alas, you cannot change the query. Sometimes this reason is political, sometimes it’s …
Continue reading
Replication latency when an Availability Group replica is offline
Assume you have configured a transactional replication publisher, which is part of an availability group (AG). Now, you have an asynchronous secondary AG (or mirroring) replica that is offline. Perhaps this is a DR node, and you are doing patching or other …
Continue reading
Thirsty Thursday - A five-bottle bar for eleven cocktails (and non-alcoholic alternatives)
A five-bottle home bar that can make eleven cocktails, including nonalcoholic alternatives.
Continue reading
Stop trying to shrink tempdb files
Why shrinking tempdb is usually counterproductive, and how to keep its data files at sensible sizes.
Continue reading
Fixing tempdb: Growing, shrinking, and removing data files
Fix tempdb file sizing problems by bringing data files to consistent sizes and managing growth.
Continue reading
Thirsty Thursday - Essential bar equipment
I’m writing this post during the Coronavirus pandemic. Billions of people across the globe are isolating in their homes. Folks have started exploring new hobbies and interests as a way to pass the time and to keep their mind sharp. Folks are depending heavily …
Continue reading
Should I install SSMS on a server running SQL Server?
There’s a lot of advice out there on the internet that suggests that you should not install SSMS on your database servers running SQL Server. This came up at work recently, where I was asked “Should we install SSMS on our database servers?” and my answer is:
Continue reading
Redgate Streamed
Last week, I had the pleasure of participating in Redgate Streamed, a three-day virtual conference organized by my friends at Redgate. My session was Shortcuts from an impatient DBA–discussing a bunch of Management Studio tips & tricks for data professionals. …
Continue reading
When STRING_SPLIT() falls short
SQL Server’s STRING_SPLIT() has limitations; here are the gaps to consider when splitting strings.
Continue reading
Joining the Friends of Redgate program 🎉
I'm super excited to announce that today I was notified that I will be joining the Friends of Redgate program! 🎉💃🎊
Continue reading
Blocking with OBJECT_NAME()
Use OBJECT_NAME() to resolve object IDs in SQL Server blocking diagnostics and identify the affected objects.
Continue reading
What the heck is the dbcreator role?
Microsoft docs defines the dbcreator role as: Members of the dbcreator fixed server role can create, alter, drop, and restore any database. But what specific permissions are actually included in that role? If we scroll down in the docs just a little, we’ll see …
Continue reading
Testing as another user...without their password
Test SQL Server permissions as another user without knowing or resetting that person’s password.
Continue reading
I hate sp_estimate_data_compression_savings
If you’re working with compressed indexes, SQL Server provides a system stored procedure to help test the space savings of implementing data compression: sp_estimate_data_compression_savings. Starting in SQL Server 2019, it can even be used to estimate savings …
Continue reading
Changing a column from int to bigint, without downtime
Changing a column from int to bigint has gotten a lot easier since I started working on SQL Server back at the turn of the century. SQL Server 2016 introduced the ability to do ALTER TABLE...ALTER COLUMN as an online operation using the WITH (ONLINE=ON) syntax …
Continue reading
How to create date buckets in T-SQL
I was fiddling around with some data, looking at making a quick aging report. I probably should have reached for Power BI Desktop, but sometimes I can be stubborn, and I wanted to get my T-SQL to work–if nothing else to figure out how to make it work.
Continue reading
How does a database RESTORE affect plan cache?
If you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine) Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a …
Continue reading
What the heck is Accelerated Database Recovery?
An overview of Accelerated Database Recovery and how it changes SQL Server crash recovery.
Continue reading
What the heck is the SQL Server Version Store?
Explore how SQL Server’s version store supports row versioning and how to monitor its impact.
Continue reading
Recovery of database is 0% complete (approximately 1000000 seconds remain)
If you’ve ever had a server reboot unexpectedly, you’ve probably seen SQL Server start up, with some databases “stuck” in recovery status, then eventually they come online. Or maybe you thought “this session is stuck in rollback. It’ll be quicker if I just …
Continue reading