A Fine Slice Of SQL Server
Dennes Torres has a script to check CPU-intense queries:
Fortunately we can find queries with high CPU time using sys.dm_exec_query_stats DMV. This DMV, created in SQL Server 2008, keeps performance statistics for cached query plans, allowing us to find the queries and query plans that are most harming our system.
Glenn Berry’s fantastic set of diagnostic queries also includes a couple for finding CPU consumers.
Comments closedSummary: We will need to drop and re-create any indexes, clustered or not, that reference the IDENTITY column – in the key or the INCLUDE. If the IDENTITY column is part of the clustered index, this means all indexes, since they will all reference the clustering key by definition. And disabling them isn’t enough.
Getting column sizes right at the beginning is your best bet. Stay tuned for other alternatives.
Comments closedClearing out a full transaction log is a common problem. A quick search will find you dozens of forum entries and blog posts. Because of that I’m not going to talk about the correct methods of dealing with a transaction log full error. What I want to discuss is why you shouldn’t use the following method.
And Kenneth also hits the one legitimate use: dire emergency. If this is a normal part of some process (e.g., warehouse loading), bite the bullet and either live in Simple recovery mode (understanding the risks) or get the disk space to keep it in Full mode. Switching back and forth—especially if you aren’t taking full backups immediately after switching back—is a good way to get yourself burned.
Comments closedSQL Wayne cautions you to think about drive failure:
Now is when the MTBF comes in. If all of the drives were from the same batch, then they have approximately the same MTBF. One drive failed. Thus, all of the drives are not far from failure. And what happens when the failed drive is replaced? The RAID controller rebuilds it. How does it rebuild the new drive? It reads the existing drives to recalculate the checksums and rebuild the data on the new drive. So you now have a VERY I/O intensive operation going on with heavy read activity on a bunch of drives that are probably pushing end of life.
This is where it’s important to keep spares and cycle out hardware.
Comments closedJoey D’Antoni argues in favor of automating SQL Server installations:
Anyway, on to my original topic—I know most shops aren’t Comcast where we were deploying multiple servers a week. However, automating your installation process has a lot of other benefits—you don’t have to worry about misconfigurations, unless you put in into your script. It also forces you into adopting a standard drive letter approach to all of your servers. So when something breaks, you know exactly where to go. And more importantly you save 15 minutes of clicking next.
For any shop which deploys SQL Server more than a couple times every few years, automating your installation process is a smart move. Even if you rarely deploy, the consistency benefits make it worthwhile.
Comments closedTara Kizer shows how to shrink a recalcitrant tempdb:
I came across this solution recently when I had to shrink tempdb. I tried shrinking each of the 8 data files plus CHECKPOINTs, repeatedly. It would not budge. I almost threw in the towel and emailed out that the space issue would be fixed during our next patching window, but then I found David Levy’s reply. DBCC FREEPROCCACHE worked like a charm.
Word of warning: understand what FREEPROCCACHE does before running it. In an emergency like the scenario Tara describes, the benefit outweighs the cost, but do be aware that there is a cost.
Comments closedAfter many months of confusing myself with mental arithmetic, I decided to create a straightforward list based on Jonathan’s algorithm, in 4 GB increments, for my own reference. I would like to share this with you today.
You can bask in the glory that is my Max Server Memory Matrix athttps://bornsql.ca/memory/.
It’s a nice lookup table, so thanks to Randolph for putting that together.
Comments closedErik Darling ran into an issue with endpoint security while setting up mirroring:
This is the error text:
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://ORACLEDB.darling.com:5022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
SUPER SLEUTH
Alright, that’s silly. I used the GUI. Instead of going to bed I’ll spend some time checking all my VM network settings. BRB.
I’m back. They were all correct. I could ping and telnet and set up linked servers and RDP. What in the name of Shub-Niggurath is going on with this thing?
These things always happen right before bed, right before the big meeting, right before lunch. They never happen on a slow Tuesday afternoon, it seems…
Comments closedDerik Hammer discusses spanning an Availability Group across multiple sites:
In the architecture above, replica A and B are in the primary data center while replica C and D are in the disaster recovery (DR) site. Like the previous architecture, the disks are displayed as local but the most important part is that they are physically separate. SANs are wonderful systems with a lot of redundancy but they can also be a single point of failure. Keep your Availability Group disks separate.
This is a nice architectural overview. Once the series is done, it looks like it’ll be a good resource to discuss high availability and disaster recovery with management and show the options and trade-offs.
Comments closedKenneth Fisher describes startup stored procedures:
sp_procoption is a system stored procedure that lets us change the options on a stored procedure and in this case set it to run on startup. Note: The account that is running SQL Server needs to have permissions to start the Agent service. (Or do whatever your startup stored procedure does.) You can have as many stored procedures running on startup as you want but remember the more you have the longer it’s going to take for your instance to start.
There are a few uses cases in which startup stored procedures can be useful, but my reservation about them is similar to my reservation about triggers: it’s not apparent to people that a startup stored procedure is in place, so if there is a problem with it, troubleshooting might be harder than normal without good documentation.
Comments closed