SQL Server Management

Optimizing SSMS 22 Installation for Performance-Focused DBA Work When installing SQL Server Management Studio (SSMS 22), it’s important to avoid selecting unnecessary components that can increase disk usage and system overhead. For a performance-driven SQL Server environment focused on: • Availability Group (AG) monitoring • DMV-based performance analysis • Troubleshooting blocking and REDO lag • Production diagnostics A minimal, optimized installation is recommended. Recommended Core Setup For SQL Server Performance & AG Monitoring: ✔ SSMS Core Components ✔ Code Tools Optional (only if required): ✔ SSIS (for ETL workloads) ✔ SSAS (for analytical / tabular models) ✔ SSRS (for reporting environments) Why Minimal Matters Selecting only required workloads: • Reduces background services • Improves SSMS startup time • Minimizes disk footprint • Keeps the DBA environment clean and focused • Reduces patching and maintenance overhead For advanced troubleshooting and AlwaysOn monitoring, the core SSMS engine is sufficient. Most deep diagnostics are driven by DMVs, Extended Events, and system views - not optional workloads. Best Practice A performance-oriented DBA machine should be: Lean. Controlled. Purpose-built. Install what you need. Avoid feature bloat. Keep your environment clean.

Had an interesting session with a client this week who was facing serious SQL Server performance issues. Long-running queries, CPU spikes, and timeouts during peak hours. We started by reviewing their execution plans and found a couple of red flags—missing indexes and suboptimal join patterns. 🔧 What we did: Tuned two critical server-level configurations (one related to MAXDOP, the other to cost threshold for parallelism). Added two well-targeted nonclustered indexes to reduce key lookups and improve seek performance. Made three precise query changes—including replacing scalar UDFs with inline logic and optimizing WHERE clause filters. 🚀 The outcome? The same workload that took minutes now completes in seconds. CPU utilization dropped significantly, and users noticed the difference right away. No hardware upgrade. No magic—just smart tuning. Performance tuning isn’t about throwing everything at the wall. Sometimes, just five well-placed changes can turn a system around. #SQLServer #PerformanceTuning #QueryOptimization #IndexingMatters #DatabaseEngineering #RealWorldSQL

Your SQL Server is lying to you. Not intentionally. But by default, it hides the most critical information you need. Like deadlocks. These suckers happen on every SQL Server. Doesn't matter which version. One transaction locks resources. Another comes in and needs the same things. Neither can complete. SQL Server picks a "victim" and kills one transaction. Your application gets an error. Users get pissed off. Something was left incomplete. But here’s the problem: By default, SQL Server just logs "deadlock occurred" in the error log. That's it. No details about which queries caused it. No information about what objects were involved. No way to prevent it from happening again. But there are hidden configuration options called trace flags. Enable the right ones, and suddenly when a deadlock happens, you get: - The exact SQL statements that conflicted - Which tables and indexes were involved - Enough detail to fix the root cause We enable these trace flags on every server we manage. It's the difference between reactive fire-fighting and proactive optimization. We’ve had clients getting 300+ deadlocks per day. Their previous DBA kept saying "it's normal" and "just retry the transaction." After enabling proper deadlock logging, we identified the pattern quickly. Fixed the underlying issue. Zero deadlocks for the past 6 months. Zero deadlocks is always the target. 𝗪𝗮𝗿𝗻𝗶𝗻𝗴: 𝗦𝗼𝗺𝗲 𝗼𝗳 𝘁𝗵𝗲𝘀𝗲 𝗰𝗮𝗻 𝗯𝗲 𝗱𝗮𝗻𝗴𝗲𝗿𝗼𝘂𝘀. 𝗗𝗼𝗻’𝘁 𝗱𝗼 𝗶𝘁 𝗶𝗻 𝗽𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻. 𝗗𝗼 𝘆𝗼𝘂𝗿 𝗱𝘂𝗲 𝗱𝗶𝗹𝗶𝗴𝗲𝗻𝗰𝗲.

Before adding hardware resources to your SQL Server, do these 5 things..... Adding hardware may seem like a good idea. It could be a quick fix that will make users happy, at least for a while. But it’s not a panacea. It can come with a hefty price tag. So it shouldn’t be the first tool out of the bag. 1) Define the problem. In the SQL Server world, we’ve seen a wide array of “database problems” that weren’t actually “database” problems. So it’s a good idea to actually make sure the issue doesn’t reside somewhere else. For example, could it be a networking issue? Or perhaps a DNS issue? Maybe it’s a Citrix issue? 2) Assess the SQL Server configuration. Once you’ve determined that the problem may actually be with the SQL Server, it’s worth your time to do a high-level assessment of the configuration. This is especially important if you’ve inherited the SQL Server, didn’t set it up yourself, or it’s been a long time since you’ve looked at it. 3) Confirm maintenance plans. SQL Server's cost-based optimization is predicated on having good, accurate, and up-to-date statistics. If the statistics are stale, bad decisions are made. And bad decisions lead to poor performance. Verify that maintenance plans are in place to keep the statistics up to date. Verify that indexes are being maintained as well. 4) Examine SQL Server wait statistics. As SQL Server goes about its job of responding to queries, it actively manages and keeps track of its key resources. For example, memory, CPU, disk i/o and network throughput are all critical resources for SQL Server. Use the DMVs to sk it what it's waiting on when it's waiting. 5) Identify the most resource intensive queries. The DMVs will be your friend in searching for these. SSMS also has some standard reports built in, including: - Top Queries by Average CPU Time - Top Queries by Total CPU Time - All Blocking Transactions - Service Broker Statistics - Top Queries by Average IO - Top Queries by Total IO - and much more Throwing hardware at a performance can be costly. Determine why your performance is suffering so you'll know how to best resolve it.

Stop spending entire days building SQL Server environments. How many times have you manually configured SQL Server environments? Same steps, same configurations, same 4-hour process repeated over and over. There's a better way. I've built an Ansible role that handles the entire SQL Server deployment pipeline: ✓ Disk initialization and formatting ✓ SQL Server 2022/2025 installation ✓ Automated patching to a specified CU during installation ✓ Standard configurations (MaxDOP, CTFP, trace flags) ✓ Firewall rules ✓ Ola Hallengren maintenance jobs The result? What used to take 4+ hours now runs in under 15 minutes. Unattended. The real power is consistency. No more "oops, forgot to set MaxDOP" or "which trace flags did we use last time?" Want to spin up 10 identical dev environments? One command. Need SQL 2025 Enterprise Developer on some servers and Standard Developer on others? What about SQL Server 2022 Developer Edition? Just change the host variables. Example config for a SQL 2025 instance: rl_mssql_iso_source:  - name: SQLServer2025-x64-ENU.iso   version: SQL2025 rl_mssql_edition: "Enterprise Developer" That's it. The role handles everything else. Bottom line: Your time is worth more than babysitting installs. Automate the mundane stuff so you can focus on the problems that actually need a DBA's brain. I walk through building this exact role step-by-step in my "Ansible for SQL Server DBAs" course - complete with all the gotchas I learned the hard way (it's being refreshed to include SQL Server 2025). Who else is using infrastructure as code for SQL Server? What's been your biggest win? #SQLServer #Automation #DBA #Ansible

Five PowerShell commands that make managing SQL Server easier and that I can’t live without: get-dbadiskspace: I use this command very frequently. It is a quick, easy way to see not only free space and total size of a volume, but it also shows you the block size as well. Copy-dbalogin: I’ve used these frequently with Availability Groups to ensure that SQL Server logins stay in sync between primary and all secondary nodes. This copies logins with their SIDs and permissions. With the -force switch you can drop a login on the target as part of copying from the source. Copy-dbaagentjob: I’ve used these frequently with Availability Groups to ensure that SQL Server Agent jobs stay in sync between primary and all secondary nodes. Not only do job steps and configuration tend to sometimes be different beween AG nodes, but sometimes jobs on the primary don’t exist at all on the secondary because they weren’t deployed to all nodes. With the -force switch you can drop a job on the target as part of copying from the source. test-dbamaxmemory/set-dbamaxmemory: These commands will show you how max server memory is set on your SQL Servers and then set a value based on best practices. I’ve used this with a for-each loop against a set of servers that I’d previously identified as still using the seriously bad default of 2 Petabytes. In this way, I could correct the setting for many servers in a single script. Start-dbamigration: This is an incredibly powerful command that will migrate an entire SQL Server from a source to a target. Migrates ALL databases, logins, database mail profiles/accounts, credentials, SQL Agent objects, linked servers, Central Management Server objects, server configuration settings (sp_configure), user objects in systems databases, system triggers and backup devices from one SQL Server to another. For more flexibility, you can use exclusions to customize the migration or use the individual migration commands that this one command encompasses. If you aren’t familiar with the DBATools module, which is where these commands come from, check it out at dbatools dot io. I also have a PowerShell category on my website at LeeMarkum dot com. What are some of your favorite PowerShell commands? #sqlserver #databases #powershell