Microsoft SQL Server Consultant
Blog posts relating to SQL Performance Tuning.
SQL Server tuning is the process of optimizing the database’s configuration, queries, and infrastructure to ensure the system runs as efficiently as possible. The goal is to reduce resource usage, increase query speed, and improve overall system responsiveness. Performance tuning involves analyzing server performance metrics, identifying bottlenecks, and implementing solutions that range from hardware upgrades to query rewriting. These optimizations are crucial for maintaining a smooth user experience and supporting business-critical applications.
A key aspect of performance tuning is query optimization. Poorly written SQL queries, missing indexes, or incorrect indexing strategies often lead to slow query execution. By using tools such as execution plans and SQL Profiler, DBAs can identify and correct inefficiencies in SQL code. Techniques like index tuning, query rewriting, and partitioning can drastically reduce query execution times. Regularly updating statistics and reorganizing or rebuilding indexes further enhances query performance and ensures accurate query plans.
Beyond queries, performance tuning also focuses on the SQL Server environment itself. Configuring server settings, adjusting memory allocation, and fine-tuning I/O operations play a significant role in optimizing database performance. Monitoring tools like Database Health Monitor (available at DatabaseHealth.com) provide insights into server health and potential issues. Stedman Solutions offers comprehensive SQL Server performance tuning services, combining years of experience with specialized tools to ensure your SQL Server environment performs at its peak. Learn more about our managed services at Stedman Solutions.
String Concatenation SQL Server is one of these things that might be sound until you try to concate longer strings. That is where it gets interesting. String concatenation in SQL Server can be pretty quick but under certain circumstances it can really slow down. Something to be aware of if you are working with larger varchar(max) values that need to …
I was investigating a slow sever recently and discovered almost a million files in the sql server error log directory. This contained job history and backup logs for many years. The error log directory is not a location that I normally check, so I created a script to quickly and easily check how many files are in a SQL Server …
How many files are in your SQL Error log directory? Read more »
Recently when doing a database upgrade for a client, I was informed that we had missed some user procedures, tables and functions in the master database. I needed a way to track all these down so that I could confirm that all the same user objects existed in the new master database, as those on the old server’s master database. …
Biggest MessJune 25, 2020 As a DBA, what is the biggest problem or mess you have had to clean up in a SQL Server. If you have been a DBA for long enough, at some point you … Latest 100 PostsRead More » TempDB Sizing QuestionJune 24, 2020 Question submitted at SteveStedman.com: Is there any formula to calculate size of …
Is there any safe way to ‘update statistics’ without causing major slow downs? As far as a safe way to update statistics. It is best to rebuild statistics in the off hours time as it will have some impact on the system. You can do a couple of things to help: If you need help with your statistics maintenance, or …
Finding Serializable QueriesDecember 3, 2019 Serializable, and some of the other isolation levels can have performance implications. Recently I came across a case where I needed to find queries that were being run against a SQL Server that … Latest 100 PostsRead More » November 2019 Release of Database Health MonitorNovember 30, 2019 Thanks to the successful funding campaign on …
Do you need help with a slow running SQL Server? Take a look at these SQL Server performance tuning options that we offer. Stedman Solutions Performance Tuning
The team at Stedman Solutions, LLC (Steve, Bill, Derrick, and George) offer a variety of services, but one that I particularly enjoy is the performance tuning work. This usually involves initial contact with a new client who is having performance problems. From there it leads to a quick 30 to 45 minute call to discuss the problem, then a proposed …
The question came up as a blog comment of “To delete 100,000 row chunks from a 9,542,067 row table, how about” Thats a great question. I have had to do something very similar lately. The problem that you run into when you are deleting 100,000 rows is lock escalation. When you run a delete statement with just a few rows …
Q & A – Deleting lots of rows from a huge table. Read more »
Here is a question that I received from a friend today and I thought it would be a good post explaining the details: Steve, I have a question on DBCC FREEPROCCACHE. I used sp_blitzcache from Brent and it shows that one particular query is horrendous ( I already knew that as I ran it and cancelled it after 10 minutes …