Optimize T-SQL data types in SQL Server

Optimizing data types means choosing them carefully, as they have a huge impact on I/O, CPU and RAM consumption. A larger data type takes up more room in the cache (RAM) resulting in a smaller hit ratio on the cache, which causes more fetches from disk (more I/O). This results in a heavier CPU hit, longer update times, increased lock times and increased contention on the table, which will show up as blocks. Indexes defined on larger data types take longer to scan/seek because more pages have to be read, which will impact RAM, CPU and I/O. Larger data types also increase maintenance times on the various indexes, yielding longer "maintenance windows."

Primary key considerations

Clustered indexes default to the primary key fields in SQL Server. That said, several factors are important with data types. For this discussion, we will assume that your primary key is the same as the clustered index, since that is the preferred approach.

Any data type you pick for the primary key (usually also the clustered index) is included within every non-clustered index as a reference back to the original row. For example, if you pick the data type datetime as the primary key rather than an int type, any non-clustered indexes will be an ad

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL/Transact SQL (T-SQL) Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility Testing transaction log autogrowth behavior in SQL Server Securing SQL Server with access control, login monitoring and DDL triggers Top 10 SQL Server development tips of 2008 The sqlcmd utility in SQL Server SQL/Transact SQL (T-SQL) Research Microsoft SQL Server Performance Monitoring and Tuning Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server Improving SQL Server full-text search performance New GROUP BY option provides better data control in SQL Server 2008 Microsoft SQL Server 2008 Resource Governor primer Examining data files when SQL Server tempdb is full Testing transaction log autogrowth behavior in SQL Server Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness Database Administration Top load balancing methods for SQL Server Performance implications of transaction log autogrowth in SQL Server The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server Implementing SQL Server 2008 FILESTREAM functionality Improving SQL Server full-text search performance Using the OPENROWSET function in SQL Server New replication features in SQL Server 2008 and what they mean to you

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) commit  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

ditional 4 bytes larger per row because the non-clustered index has to hold the primary key value. In tables with more than two billion rows, this can quickly add more than 2.4 GB per non-clustered index. You'll see the impact on CPU, I/O and RAM quickly accumulate in this scenario.

Smaller data types make it easier -- i.e., faster and less resource intensive -- for SQL Server to ensure that any inserted or updated values are unique against the existing table values.

Individual data type considerations

We will just cover the commonly misused data types here.

The size of the data rows and indexes impacts a wide range of hardware resources. SQL Server performance will increase exponentially as you reduce the size of the rows and indexes. A few fast points to remember: Always pick the smallest data type, use bits and integers (the smaller the int type the better) with lookup tables where possible – trying to avoid the use of (n)varchars.

If you have to use large text/binary values, then consider defining them as (Max) types and setting the options to store the data "out of row." If the text data is fixed length, then always use (n)char(n) data types so 2 bytes is not wasted storing offsets. If your primary concern is performance, avoid using any data types other than ints for primary/foreign keys, as this will significantly reduce your data row and index sizes. SQL Server data types seem like a small issue at design time, but they will have a huge impact on your system as it grows organically over time.

[TABLE]

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.