November 2008: What's in the breeze Developing India-centric content for a new web portal targeted at NRIs Editing and enhancing a corporate presentation for a German bank Research and content generation for client's website on a pay-per-month basis Repackaging and writing travel content for a client's website Developing content for a new motor vehicles website - delivering 500 articles per month Creating daily reports and summaries in the alternative energy sector... and more
Check out sample articles written by writers from our network.
Writers, use the writers' contact form to get in touch with us.
A Guide to PL/SQL Performance Tuning MS SQL Server 2000
Performance tuning is the approach followed while designing database extensive applications for efficient data availability with the optimal usage of system resources. In order to achieve the desired results from the database application, performance tuning has to be incorporated at the initial stages of the design process. One key aspect of SQL server performance tuning is controlling the I/O performance.
This guide outlines some of the essential tips to assist in designing a good database, which will be able to scale up to the ever increasing needs of the business.
Why Performance Tuning
In today’s world of exceptionally high-speed processors and Moore’s law petering out with the advent of dual core processors from Intel and AMD, one may wonder about the need for performance tuning. The bitter truth is that unless the SQL Server is running on a machine with enough RAM to hold the entire database, which is unfeasible, I/O performance will be determined by how fast the I/O subsystem can read and write data for the SQL Server.
Target Audience
This guide is targeted to help database designers, administrators and developers to design high performance databases for real time transaction systems. It also provides guidelines and best practices for writing queries in the most efficient manner.
I. Key Design Principles for improved performance
Normalization
Database Normalization is the art of storing and organizing the contents of database tables in order to reduce redundant data. Normalizing data is not only a best practice, but also the technique to ensure that the data extracted is accurate. It is essential to follow the three simple rules of normalization while designing.
Typically, data warehouse applications will tend to de-normalize later, in order to meet the wide variety of reporting needs. This is rarely the case with transactional databases. However, the basic principal behind normalization is to reduce the amount of data that needs to be stored, consequently requiring the SQL Server to perform less I/O and hence faster data access.
File Groups
One of the most intelligent outcomes of the evolution in the computing world has been the concept of parallel processing. This feature is very much a part of RDBMS systems in the market today and the SQL Server is no exception. This feature is implemented in the SQL Server through File Groups.
Multiple parallel scans can be executed for a single table if the table is part of a separate file group that contains multiple files. For example, if a table is created on a separate file group and the group contains three files then a full scan will use three threads to read the data from the table. This leads to the obvious design approach of storing more frequently used large tables in a separate file group and further into multiple files. If possible, the file groups should be placed on separate physical drives as well.
On similar grounds, the SQL Server logging mechanism is a continuous phenomenon and hence the LOG file group should be on a separate physical drive in order to minimize I/O time.
It is very likely that at design time it may not be possible to identify tables to be put in a separate file group. However, it is important to remember this alternative route during the development/testing cycle in order to achieve the desired performance bench marks. Word of Caution: Due discretion needs to be exercised while creating File Groups as an indiscriminate creation of threads can lead to system overheads.
SQL Server page size consideration
The SQL Server page size is 8KB, that is 8060 bytes and not 8192 bytes. Most applications will have tables, which contain TEXT data types or large VARCHAR’s to store large volumes of text for each record. A common example is a “Comments” column. In case the row size in a table, the sum of all the bytes of individual column data types, reaches anywhere close to the page size, it is a cause for concern and a positive I/O bottleneck. This will result in the SQL Server traversing each page in order to scan just one row of the table. Such columns should be moved to a separate table linked through a primary-foreign key relationship.
Indexes
One of the most vital performance tuning steps, and also the most commonly discussed and debated amongst the developer fraternity, is the appropriate use of indexes. Indexes help in the sorting of data and consequently enable reduced I/O time. As a normal practice, indexes should be created at development rather than design time.
Typically, in queries, there is more than one column referenced in the where clause. In such scenarios it is advisable to use a “covered index”, which gives better performance than one index on an individual column.
For more information on the creation of Indexes, please take a look at Useful Links below. Word of Caution:
Due care needs to be taken while creating Indexes. Results should be analyzed to determine whether the index created is the right one.
It has to be borne in mind that not only the column, which is indexed but also the number of indexes on the table will govern the I/O performance. It is a myth that the more the indexes the better will be the data retrieval rate. Indexes help fetch the data faster but the underlying fact is that they are an overhead on the SQL Server and the server needs to maintain each index on the table. If there are more than 2-3 indexes on a table then there is a need for re-checking.
Also, based on the type of query it is very likely that the non-clustered index on a column can be more optimal than a clustered index on the same column or vice versa.
In certain scenarios, if the table size is small (referenced table not transactional tables) or if only limited records are to be extracted, it is beneficial to allow the SQL Server to do the entire table scanning instead of creating/using indexes on the table.
Table Fragmentation
This is a very common scenario especially in a transactional database which causes substantial I/O bottlenecks. Table fragmentation occurs when data is repeatedly modified using DML statements. Even if the table has an index, it will become ineffective over a period of time as the DML statements cause gaps in each page. This will cause the SQL Server to read additional pages and hence increase CPU activity.
Transactional databases will typically have an EOD (End of Day) process for database backup and other maintenance activities. At this point it is absolutely critical to de-fragment and re-index the tables.
Appropriate use of Data types
As a rule of thumb, choose the smallest possible data type required to hold the data. If the work can be accomplished with the use of TINYINT instead of an INT, then use TINYINT. Similarly if the content in the column has variable length then use VARCHAR instead of CHAR. All this will enable more data (rows) to be stored in a single indexed page of the SQL Server and thereby boost I/O substantially. Avoid the use of NVARCHAR or NCHAR (Unicode string storage data types – which are allocated twice the space as normal strings) unless it is absolutely essential. Normally multi-lingual applications require Unicode strings.
II. Key Technical Principles for Improved Performance
There are several potential , which should be avoided while writing the PL/SQL queries. The common ones are listed below.
Dead Locks
“Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases. The SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. [SQL 2000 online help].”
Use “NOLOCK” in queries while doing a select on large tables or on tables on which DML operations are extensively used. This will explicitly set the locking level and avoid overheads on the SQL Server.
ORDER BY clause
The ORDER BY clause used in queries will have a marginal performance impact if the column has a clustered index, but can have a high impact on non-clustered or non-indexed columns. For non-clustered or non-indexed columns, sorting should be done as part of a separate logic, example, as part of the thin client logic or in the service which processes the result-set.
IF EXISTS
In order to verify whether a record exists or not, never use the following syntax: <<<< if exists (Select * from….where….) >>>>.
Instead use <<<< if exists (Select 1 from….where….) >>>>.
The drawback in using the former is unnecessary I/O increase. This can be even more expensive if the query has joins in it.
Sub Queries Vs Joins
Nested Queries or sub queries use limited resources and are beneficial with small result sets. Joins are CPU extensive but offer better performance when used for larger result sets. Word of caution: Joins can prove very costly (CPU extensive) when sorting is done. As mentioned earlier, sorting should be done as part of a separate logic.
Temporary tables
When using temporary tables, table variables should be used instead of #Temp Tables.
Syntax: <<<<Declare @Table_name table(…..)>>>>
These are stored in the SQL Server Cache (Temp db of the server). These get cleared automatically, require less recompilations, no logging overheads and less locking. The drawback here is that the SQL Server does not allow any index creation on such tables. Word of Caution: Try to use table variables when there is less data in the table and the table is not used frequently.
Although #Temp tables give better performance with large data, as indexes can be created on them, they force the SQL server to recompile whenever there is a table declaration.
Recompilation
The popularity of SQL Stored procedures is because the Stored Procedures get compiled the first time they are executed and the plan of execution is created and stored in the procedure cache. The next time the procedure is called, if the execution plan still holds good, then the SQL server will not parse it or create the execution plan again, but simply execute it. However there are certain statements (more information in Useful Links below) which cause the procedure to recompile and this adds to the overhead on the server. These statements should be avoided wherever possible.
NULL
One of the most evident downsides of assigning a NULL value to a column of data type CHAR is space wastage. A NULL value on a char column will require 256 characters to be stored for that column in the database.
Useful Links:
The topic of Database performance tuning is a very extensive one. The above article tries to encapsulate some of the important concepts of performance tuning. Below are a list of some excellent links, which can further help database designers and developers in their quest for improved SQL Server performance.
Out of 5 “chilies”, our editorial team gave this article...
—About our writer:
Pooja says, "With a Masters degree in Math from IITD, I got into the IT industry more by chance than by choice - no regrets though! Although a part of the technical world, my heart lies in several other creative pursuits – primarily theatre, writing and music. Writing has been a passion for very long. The first poem I ever wrote was on the Bombay blasts in 1993. Another was a mushy one for my Mom on Mother's Day! I am a die-hard optimist and think the one ingredient to take you through all ups and downs in life is finding humor in everything! My philosophy in life, I love the light for it shows me the way yet I endure the darkness for it shows me the stars."
>> Read more articles written by our chillibreeze staff and writers network:
.gif)
Developing India-centric content for a new web portal targeted at NRIs
