For a database professional, the most dreaded time is when the database server is unresponsive so that it does not serve the application requests. All database professionals need to do some performance tuning. However, often, they are overwhelmed with the situation in hand whereby this only creates confusion. Consequently, database professionals resort to the standard resolution, which is using a search engine. Instead, get back to basics when performance tuning SQL Server.
Performance tuning SQL Server is an art form. Most often, database professionals are pushed to explore new ways to do performance tuning and testing. It helps to look at a possible approach to performance troubleshooting. Start from essential tools to find a high-level problem area and then dig deep into solving the problem. We need to make the crucial initial diagnosis and do proper tests. Finally, we need to discover what would be a suitable solution to performance problems in our SQL Server database.
The 10-page whitepaper “Beginning Performance Tuning” by Pinal Dave discusses how to start performance tuning, the top six SQL Server tools, and five general database tips.
Performance tuning involves systematically identifying bottlenecks in applications and improving performance. There are several places to start performance tuning. In essence, performance tuning is fundamentally about proper planning. This process includes reviewing the system architecture, understanding the workload and environment, gathering data, create baselines, analyzing data and identifying issues, generating alternatives, testing results, and documenting everything.
A lot of times, the essential tools are the ones that help us a lot. For example, the statistics T-SQL commands quickly provide a wealth of information. Other top tools include SQL Server Profiler, SQL Server Management Studio, SQL Server Error Logs, the SQL Server utility SQLDiag, and graphical execution plans.
Generic database tips include maintaining indexes, watching out for blocking and deadlocks, minding dangerous large and long transactions, aging out old data, and fix recompilation.
The author, Pinal Dave, is a developer evangelist and technology enthusiast. He authored eleven SQL Server database books, eleven Pluralsight courses, and over 2900 articles on the database technology on his blog. Along with more than nine years of hands-on experience, he holds a Master’s of Science degree and several certifications including Microsoft Certified Technology Specialist (MCTS), Microsoft Certified Database Administrator (MCDBA) and Microsoft Certified Application Developer (MCAD) for the .NET framework.
Click here to read the whitepaper.
