You can subscribe to the sql tag micro-feed.
If you have a blocking query, you probably follow a set of manual steps a bit like this… Run sp_who2. Follow the “blocked by” SPIDs until you find a root-SPID (one that is blocking, but not blocked). Run DBCC INPUTBUFFER with the SPID to see what query is being run After doing this twice in […]
Sometimes you need to filter and sort the records you get from sp_who2. You can’t do this directly, but you can use a temporary table to do it. — Create an intermediate table to put all the results CREATE TABLE #spootoo ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME […]
Here is a quick SQL script that will get back information on SQL availability groups, including the role of each server. This can help you find the primary server in an availability group. IF SERVERPROPERTY (‘IsHadrEnabled’) = 1 BEGIN SELECT AvailabilityGroup.name AS AvailabilityGroupName, ReplicaClusterStates.replica_server_name as ServerName, ReplicaStates.role_desc as ReplicaRole, Listeners.dns_name as DnsName FROM sys.availability_groups_cluster AS […]
I have a script I use to find table sizes in SQL server. Sometimes, though, I need to find the rough table size of a massive table without the need for absolute precision. SQL server has a procedure for finding out the number of rows, space, and index size of a table; and it can […]
When you perform a SQL bulk update, you can just press go and wait. Most humans, though, get an increasing feeling of impending doom when the clock ticks up towards several hours with no visible progress. If you are running a transaction and have other things competing for the table, you’ll start hearing about slow […]
I was investigating an issue with am Azure SQL Server database that was much bigger than it ought to be. It was around 50 GB, but should really have been about 15 GB. I ran an old query I keep lying around that finds the size of tables in a SQL database and there was […]
There are lots of ways to make an identity immutable and unconfusable. I’ll show just a couple, but first the principle behind it. Your identity should be immutable, because you will accumulate lots of threads back to your item that need that identity. If the identity changes, you will be breaking all of the relationships […]
If you want non-clustered indexes to be copied to your replication slaves (you probably do) you simply adjust the setting that you’ll find here… SQL -> Replication -> Local Publications -> Publication Name -> Properties -> Articles -> Article Properties -> Copy nonclustered indexes When you set this to “True”, the indexes will be copied. […]
I have found that Activity Monitor can have a habit of bugging out on you when you try to get recent expensive queries. If you also get this, you may want to try the following query instead. You can adjust the query purposefully by changing the ordering, based on what you are looking for. There […]
I was investigating a number of maintenance plans that were taking serveral hours to complete and I noticed some optimizations that could be made to the plans without actually affecting the behaviour very much. The maintenance plan after the fixes is: Check Database Integrity Maintenance Cleanup Task Rebuild Index Clean Up History Back Up Database […]
