When you send a query to SQL to be executed, the query is compiled and then run. When you have 1 person querying the database, everything runs fine, but when you ramp that up to multiple users, you start to fight for resources. One of those resources is CPU. To manage CPU time, SQL Server […]
SQL Server has a couple of keywords that can be added to a query to give the query optimizer some ideas on how to best compile your SQL statements. Hints like FORCE ORDER, MAXDOP and NOEXPAND can be used as a way to tell the optimizer “Hey, I’ve got this idea, you should try it.” […]
Does SQL Server always cache your parameters? Does it always keep track of what you pass in?
Recently I needed to diagnose why a weekly, multi-step SQL Agent Job was taking so long. I thought, no worries, I’ll go and look at the history to see which step it was. Except…there was no history. Looking at the Job Activity Monitor, I could see that the Last Run was Saturday morning, and the […]
There’s a common belief that you cannot rollback a truncate statement in SQL Server, but is that true? We can find out with a pretty simple test. First, we need a table we can truncate. Obviously you don’t want to go and try this out on your Production tables, so lets create a test one: […]
SQL 2019 RC1 is now generally available and I wanted to try it out. Instead of installing it on my PC, I decided to give it a go in Docker. After a bit of mucking around, I worked out the following: Getting SQL up and running took a matter of minutes. You find the docker […]
In SQL, when you don’t want to change the result set, but you want to provide some messages, for example, when debugging a query in SSMS, it can be really helpful to use the PRINT statement. What you’ll start to notice though is that the print statement doesn’t always print straight away. Usually this is […]
This month for T-SQL Tuesday, Kevin Chant has asked the question “What is your Fantasy SQL Feature?” There are a few that I would love to have, but the one that keeps popping up is having a foreign key that can reference more than one table. I know this seems like an odd request, but […]
SQL Server Reporting Services uses Windows Authentication to authenticate users when they connect, but it also impersonates the user. Depending on how you’re setup, this often requires the creation of an SPN. SPN’s are awesome when they work, they allow integration with Active Directory, but when they don’t work, it’s a nightmare to fix. Reporting […]
So you’ve worked out how often to take your backups, what sort to take, and where to store them. You’ve created your jobs and have them running regularly, now what? Now you need to test that they’re working, and not just now, but forever into the future. There is only 1 way to know that […]
