Tune-Up Time for SQL Server

Transactional databases tend to increase in size as business operations grow. One of the challenges of maintaining a growing database is managing smooth and efficient database performance. If a database system is not designed properly with correct data type and indexing, and if it uses inefficient queries, the applications will experience significant performance issues.

While it is important to revisit the database design and correct any existing drain issues, it is also important to tune up your databases for better performance. Instead of crediting database performance issues to hardware limitations and trying to upgrade the hardware, database administrators and database developers are using database tune-up techniques to provide better performance for database applications. This article will give you some tips as to how you can provide performance tuning for SQL Server databases.

What is SQL Server performance tuning?

As a leading database server in the industry providing data storage, querying, transaction handling, error handling, reporting, and BI facilities to numerous businesses and organizations, Microsoft SQL Server offers features that cater to different scales of enterprises. Tuning up the database helps in keeping these features functioning properly, regardless of the scale of the business or the size of the database.

There are three basic categories of performance tuning for SQL Server: basic query analysis, advanced query analysis and facilitate tuning. Here are a few tips you can follow if you are given the task of tuning up your SQL Server database instance:

• Table and row counts: Views and table-valued functions are quite different from physical tables in performance handling and rarely contribute to the server performance. Therefore, when you identify a performance issue, make sure you are referring to the actual physical tables in the DB, not views or table-valued functions.
• Be aware of the size of the dataset that is queried. If more columns than necessary are returned in a query, it will degrade the performance. Pay attention to whether a “SELECT *” query is used without justification.
• Review the keys, constraints and indexes of the existing tables and see if they are properly used. sp_helindex is a system-level stored procedure in SQL Server, which would give you information on the existing indexes. Make sure that keys and constraints are used properly where needed.
• Estimated query plans and execution plans help analyze the estimated and actual statistics of a query execution. If the statistics are different, that means there are queries that are poorly written and they need further investigation.
• It also helps to record the statistics before and after performance tuning, to ensure that whatever was done has actually resulted in a performance gain.

(You can find more tips on SQL Server performance tuning here.)

You are not in a production environment with real data. How do you learn how to execute SQL Server performance tuning?

Performance issues are typically seen in the production environment that a developer may not have access to. Chances are that you are able to get a backup of the database, but in most instances the data may be sensitive.

Here’s what you can do:

1. Find a sample database. You can download the Microsoft’s AdventureWorks database here.
2. You will need to grow the sample database in size, probably by using a SQL script to add random data to the tables.
3. Write SQL queries using anti-patterns. The intention is to slow down the execution and create performance issues.
4. Run query plans on the written queries and use your proper SQL knowledge to optimize the queries. Make sure your tune up results in performance gains by running the execution plans again.

You can learn more about how you can get your hands on performance-tuning exercises, even in a demo environment that you create, here. Also, to learn more about SQL Server performance tuning please contact Datavail today. With more than 600 database administrators worldwide, Datavail is the largest database services provider in North America. As a reliable provider of 24×7 managed services for applications, BI/Analytics, and databases, Datavail can support your organization, regardless of the build you’ve selected.