How to improve MS-SQL Server Performance?

Sanjay Majumder

While working with SQL Server databases, if you often find the database performance sluggish, it could be due to bad coding of procedures or incorrectly set database variables.

Here's a tool that can analyze SQL database instances and help you in removing bottlenecks that cause the slow down. The tool monitors SQL Server's hardware utilization rate and performance through parameters such as CPU, memory and I/O sub systems, and correlates system utilization with user connections and SQL code.

It has an agent that accesses the SQL Server instance once in every 5 minutes, keeping the footprint at a low level. The agent captures the performance counters, which are subsequently saved. Collected data is then sent to Teratrax main console for further analysis in the form of charts and reports. Teratrax exports these at any time using an easy-to-understand user interface.

The tool can be installed on a host machine, where you have SQL server running. You can even install it on a remote machine and connect it to your SQL server instance. Download it from www.teratrax.com/tpm.

Please note that this tool runs only on MS-SQL 2000 and above versions. Install it on a Windows based machine and restart.

Configure agents

To monitor entire SQL instances, you first need to configure the Monitoring Agent on remote machines running SQL. This agent fetches data from the database and forwards it for analysis to the Teratrax console. Run Monitoring Agent 3.0 from Start menu, where under General tab you need to give the names of SQL instances. Once the agent is connected, you can monitor each and every move of your SQL database.

Using Teratrax Performance Viewer

Launch the Teratrax Performance Viewer from the Start menu. This will open a console that's divided in two parts-on the left you will get the Server Explorer, while on the right you will get the performance gauge in the form of graphs.

To check performance, you have to connect the Viewer to the Monitoring Agent. For this select the SQL instance and you can see the performance of the database on the right panel. The tool also lets you create performance reports in the form of a Web page.

Source: PCQuest