Sometimes good intentions lead to big problems.
Imagine a system where page splits were thought to be causing a big enough performance problem that it made sense to change the fill factor setting of a lot of indexes.
During our Critical Care® sessions with clients, we often see unused indexes (reads=0) with high writes. Sometimes these unused indexes have very high writes. If an index is never being used to help with performance but SQL Server is having to maintain it for INSERT, UPDATE and DELETE operations, then why keep the index around?
When deciding on a backup strategy for a database, there are various things we must consider: Does this database need point-in-time recovery? What are the RPO (data loss) and RTO (downtime) goals? No one likes losing data or encountering unplanned downtime, but the business must decide on these goals so that we can setup an…
Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string.
For frequently executed queries, "EXEC sp_executesql @sql"is a good choice but at the risk of encountering parameter sniffing issues.
I've already blogged about my dislike for ORMs from a production DBA performance tuning standpoint only. I get that they're useful to developers. I get it. But I'm focused on performance.
A quick recap of what I don't like about ORMs from that other blog post:
I knew Brent and Erik wouldn't touch replication, so I figured I'd give it a whirl.
My good, old friend replication
I have a love-hate relationship with replication. Mostly hate due to latency and errors, but it does serve its purpose. Before Availability Groups came out, I used Transactional Replication to copy data from the production OLTP database to another server so that we could offload reports.
In part 1 and part 2 of this series, I showed you the various ways to get the application's SET options so that you can get the same execution plan as the application. If you skip that step and run the query that is timing out in the application, it could be fast in SSMS. Your SET options must match the application's in order to reproduce the slowness users are seeing.
In part 1 of this series, I showed you how to get the compiled parameter values for an execution plan that you are investigating. You've identified it as a bad execution plan. You've already fixed the production emergency by removing the bad plan from the plan cache. You're almost ready to start testing for a workaround or a solution to avoid this issue in the future.
Many of you are troubleshooting Parameter Sniffing issues the wrong way in production. In this three-part series, I'll show you how to troubleshoot it the right way:
Getting the Compiled Parameter Values
Getting the App's SET Options
Avoiding the Density Vector
This isn't about having a sucky job, this is about you sucking at your job. There's a big difference. You could be sucking at your job because you hate your job and just don't care. I get that. In that case though, you know better but choose not to do better.
I've encountered some people that have Senior DBA as their job title but don't really have senior-level experience. I've seen some very questionable things in my career.
Signs you aren't a Senior DBA
I've had a handful of clients that were experiencing significant THREADPOOL waits. This isn't a wait type that I had any knowledge of prior to joining Brent Ozar Unlimited. Looking back, I am pretty sure that a few servers that I've been responsible for over the years were experiencing it based on the symptoms.
WebOps: For a few minutes last night the app servers couldn't connect to SQL. Can you check if there was a SQL problem during that time?
One of Brent's students in the Performance Tuning class, Jason M., told Brent that maintenance plans enable your disabled indexes. What the what?
Steps to reproduce:
When we look at a new server, we check out their database maintenance jobs, making sure their server is in a safe place before we dig into their pain points. We verify if their backups and DBCC CHECKDB jobs can meet their RPO/RTO goals. We also check out their index maintenance jobs, if any.
There's a lot that goes into a Disaster Recovery plan. One of them is offsite backups. There are some businesses that don't have backups going offsite, let alone a Disaster Recovery plan.
This conversation has taken place more than I thought it would.
StackExchange users often ask, "Why is my server slow?" Here's a few tips to get better, faster answers. Server information You may have already included server information in your question, but it may not be sufficient. Run sp_Blitz to output a lot of server information fast. [crayon-6a5cb3dde767b306981406/] Click on the XML mumbo jumbo and post the…
This has been blogged before by several people. One thing I've learned from Brent is to not let the fact that the topic has been blogged before stop you from blogging about it. With that out of the way... When you have sysadmin access, you are bound to make a big mistake in production at…
I was recently asked what I thought about ORMs (Entity Framework, NHibernate, Dapper, etc) while we were looking at implicit conversion warnings in execution plans.
Before I answered the question, I let them know that my answer is based on being a production DBA and the numerous performance problems I have had to troubleshoot that were caused by improper usage of an ORM.
Brent had an idea that we should set aside a day per month where we could build things in the cloud and blog about our experiences. I was tasked with Amazon RDS for SQL Server: create an instance, configure backups and test point-in-time restores. After grabbing some coffee, I logged into the AWS console. We…
You’re a systems administrator or database administrator who wants to protect your production SQL Server. However, you don’t have a separate data center or another colo site.
You’re looking for instructions on:
You use Ola Hallengren's maintenance scripts to do your database backups on SQL Server 2016 or newer, and you want to do things like:
Restore the most recent database backup
Test it for corruption
Initialize restores to get ready for log shipping or replication
