By Jennifer Marsh

Jennifer Marsh is a software developer, programmer and technology writer and occasionally blogs for Rackspace Hosting.

A Microsoft SQL Server performs the dynamic queries for a business website and web-based internal applications. The database setup and configurations can make or break the speed of the application, which can indirectly affect productivity for employees. Cloud applications require performance tuning to keep the speed of data flow quick, so users don’t need to wait too long for data transactions. When the business uses Microsoft SQL Server, the cloud database includes tuning tools to speed up slow queries and help reduce wait time for internal and external cloud applications. Before deploying the latest application upgrade, make sure SQL performance is optimized using the following procedures.

Table Indexes

Table indexes create the sorting rules for the table data. But too much can slow down the server and too little can make the applications unbearably slow. In severe cases, a poorly indexed database can cause time-out issues on web-based applications. Indexes allow the data to be found more quickly by ordering records based on column data. All tables should have at least one index. For instance, a primary key is automatically an index on the table, which speeds up queries based on the primary key column. And it speeds up joins set on the primary key column.

Indexes are set in SQL Server Management Studio. Right-click the “Index” icon under the table to edit and select “New Index.” Select columns on which the stored procedures query or join to other tables. These indexes can mean the difference of minutes or even hours when querying large amount of data.

Execution Plans

Microsoft includes a tuning advisor with its database engine. The execution plan displays the path the SQL Server takes to obtain the data and it provides the programmer with resources needed to perform each query, join tables and perform calculations. For each query and stored procedure created, an execution plan should be performed. Review the plan and determine where indexes are needed. The execution plan also helps the developer speed up data queries.

Primary Keys

Every table must have a primary key, because it designates the unique value that identifies each column. For instance, a customer can only have one customer number, which can be the primary key for the table. Microsoft SQL Server also allows the developer to have a composite key, which means the primary key can be made up of two columns. For instance, a social security and account number can make up a unique primary key.

Defrag the Database Indexes

Deleting, adding, updating and moving records leave the database fragmented. Just like a hard drive, defragging indexes can greatly speed up the performance on a database engine that has been in service for a while. SQL Server uses the “DBCC INDEXDEFRAG” command to defrag indexes. Open a query window in SQL Server Management Studio and type the following command:

DBCC INDEXDEFRAG (database_name, “table”, index_name)

Replace “database_name” with the name of the database, replace “table” with the table name and replace “index_name” with the name of the fragmented index. Always run this command during off-hours, because it can greatly reduce the performance of the server while it is running. It can take several minutes to complete. For late night defragmentation processes, set up an SSIS job to defrag the indexes.

If the business site is slow and uses a cloud database, these few tweaks only take a few minutes, and they can greatly improve the speed of the database server. While it might not be the entire solution to a slow-running database server, it can be a factor, and the changes can benefit site performance as the database data grows.