Transact-SQL, Stored Procedure, and Query Writing
Writing fast-performing queries, stored procedures, and Transact-SQL can often be difficult, as there are often many ways to write them to perform a single task. But some ways promote fast code, and others promote slow code. These tips will get you started on the right track, although there are many other tips that are not listed here that can help you.
- Avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task. More
- Carefully evaluate whether your query needs the DISTINCT clause or not. The DISTINCT clause slows down virtually every query it is in. More
- In your queries, don’t return column data or rows you don’t need. More
- Avoid WHERE clauses that are non-sargable. Non-sargable search arguments in the WHERE clause, such as “IS NULL”, “OR”, “<>”, “!=”, “!>”, “!<“, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE %500” can prevent the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, or expressions that have the same column on both sides of the operator, are not sargable. More
- When you have a choice of using a constraint or a trigger to perform the same task, always choose the constraint. More
- When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is more efficient and performs faster. More
- When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient. More
- Avoid using optimizer hints in your queries. More
- Instead of using temporary tables, consider using a derived table instead. A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, we can reduce I/O and boost our application’s performance. More
- Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-SQL. More
- Include in your stored procedures the statement, “SET NOCOUNT ON”. If you don’t turn this command on, then every time a SQL statement is executed, SQL Server will send a response to the client indicating the number of rows affected by the statement. More
- Keep Transact-SQL transactions as short as possible. More
- For best performance, all objects that are called within the same stored procedure should all be owned by the same owner, preferably dbo. In addition, always refers to database objects using this format: object_owner.object_name. More
- For maximum performance when joining two or more tables, the indexes on the columns to be joined should have the same data type. More
Server Hardware/Software Tuning
Of all the performance tips in this article, these tips will most likely produce the least amount of performance gain. This may surprise you, but tuning SQL Server parameters and tuning hardware generally produce the least performance benefits, as compared to the tips listed above. These tips cover the key basics of hardware and server tuning.
- Don’t run any applications on your server other than SQL Server, with the exception of necessary utilities. More
- When selecting your CPU for your server, select one with a large L2 cache. This is especially important if you have multiple-processor servers. More
- In most cases, the more physical RAM your server has, the greater SQL Server’s performance. Ideally, you want all your database’s tables in RAM. More
- For best I/O performance, use the fastest disk drives you can get in your disk arrays. More
- Locate the database files (.mdf) and log files (.ldf) on separate arrays in your server to isolate potentially conflicting reads and writes. More
- Don’t use NT Server’s software-based RAID, instead use hardware-based RAID. More
- From a performance perspective, it is better to have more smaller SCSI disk drives in an array than having fewer larger SCSI disk drives. More
- Limit the number of network protocols installed on the server. More
- Unless you know exactly what you are doing and have already performed impartial experiments that prove that making SQL Server configuration changes helps you in your particular environment, don’t change any of the SQL Server configuration settings that affect server performance. More
- Don’t turn on the “Auto Shrink” database option on your production databases. More
If you can apply all of the above relevant tips to our SQL Server, you will be 80% of the way to maximizing the performance of your SQL Server. Once you have these out of the way, the you can focus your efforts on that last 20% performance boost.
If you disagree with any of the above tips as being critical to the performance of SQL Server, or if you would like to add more suggestions, please e-mail me at: webmaster@sql-server-performance.com
]]>