Weekly Summary June 26, 2020
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… Read More »Weekly Summary June 26, 2020
Performance
Blog posts relating to SQL Performance Tuning.
SQL Server Performance Tuning
Enhancing Database Efficiency
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.
Update Statistics
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… Read More »Update Statistics
Steve Stedman Blog Posts from 2019
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… Read More »Steve Stedman Blog Posts from 2019
SQL Performance Tuning
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… Read More »SQL Performance Tuning
Do you need SQL Server Performance Tuning Help?
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… Read More »Do you need SQL Server Performance Tuning Help?
Q & A – Deleting lots of rows from a huge table.
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.… Read More »Q & A – Deleting lots of rows from a huge table.
DBCC FREEPROCCACHE – What is the impact.
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… Read More »DBCC FREEPROCCACHE – What is the impact.
Presenting this Friday at the CHUG Conference
This Friday, October 14th I will be presenting at the Centricity Healthcare Users Group (CHUG) conference in Austin TX. The session is on “Tune, Monitor… Read More »Presenting this Friday at the CHUG Conference
Don’t Reboot SQL Server So Often
How often should I reboot SQL Server? It is a common belief with computers that rebooting cleans things up in memory and that they run… Read More »Don’t Reboot SQL Server So Often
Instant File Initialization (IFI)
One way to improve performance on SQL Server is with IFI or Instant File Initialization.
Normally files are zeroed out on a database growth during an AUTOGROW, RESTORE, CREATE DATABASE or ALTER DATABASE. This is done by SQL Server when the file grows, it runs through that file and writes zeroes to the entire new allocation in the file. The zeroing process can take a great deal of time, the Instant file initialization process skips this zeroing, and just allocates the file. This works since SQL Server will just write each 8k page to disk as they are used, thus overwriting the uninitialized file.
Running some tests on a local virtual machine running SQL Server 2012 (similar results tested on SQL Server 2014 and SQL Server 2016), here is what I found.
- Creating a new database (10GB file size) took 2 minutes and 6 seconds, with IFI enabled, it took just 4 seconds.
- Autogrow of a data file witha 1GB growth size took 13 seconds without IFI, with IFI it took less than a second.
For IFI to work, the user account that SQL Server is running as needs the “Perform volume maintenance tasks” policy to be enabled.Read More »Instant File Initialization (IFI)