MSSQL Tuning 2005 – Database Engine Tuning Advisor – LoadStorm

One of the biggest issues a database administrator deals with is the impending performance issues that seem to hit an expanding environment. Smaller businesses can usually handle the simple problems that may interrupt daily activities, but large or medium sized businesses need real solutions to quickly fix problems since performance issues equal lower revenue.

In Microsoft SQL 2005, the Database Engine Tuning Advisor can help database administrators determine solutions and analyze issues related to SQL performance issues. The purpose of the tool is to allow administrators a way to monitor indexes and partitions to pinpoint the source of flawed performance design. It also helps you view calling stored procedures to help developers and database administrators work together to provide the best programming interface for each business unit. It works together with the MSSQL Profiler to determine weak points in a database design.

The first step to opening proper analysis for performance monitoring, first you must create a trace file. A trace file will give you an overall view of database calls for you to dig down deeper and analyze. It provides a huge list of calls to the database for both administrative functions and queries from various software applications.

After creating a trace file, you can run the Database Engine Tuning Advisor. After a few minutes, the software will analyze tuning performance and list several recommendations. At this point, you can allow SQL Server to simply apply the recommendations, or you can save it to a file to further analyze what output has been created from the Database Engine Tuning Advisor tool. Obviously, the best route for a professional is to output to a file and analyze later, but if the tool is being run on a development platform, it might be worth trying the recommendations as long as a proper backup is in place.

After creating an output file, database administrators can analyze calls to the database that will allow them to view various stored procedures that may be causing a bottleneck or overworking the database server. Some developers use cursors, which can be a major concern for performance issues. Certain functions may be better used at night or for reporting. Reporting functions can be used through a separate server that has a backup database snapshot from the day prior. Reporting should never be used against the live, production server.

Through a great database administration team and using the tuning tools that Microsoft provides for its database servers, you can find detrimental issues affecting your environment.