Queries and stored procedures are close companions of a DBA. So make your life easy by following the baseline given below, while writing queries and stored procedures by tomorrow.
- Maintain all code in a source control system.
- Keep transactions as short as possible. This reduces locking and increases application concurrency, which helps to boost performance.
- Avoid using query hints unless you know exactly what you are doing, and you have verified that the hint actually boosts performance.
- Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure.
- Use the least restrictive transaction isolation level possible for your user connection, instead of always using the default READ COMMITTED.
- SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.
- Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them.
- For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner. object_name or schema_owner.object_ name.
- One way to help ensure that stored procedures query plans are reused from execution to execution of the same stored procedure is to ensure that any user connections information, SET options, database options, or SQL Server configuration options don’t change from execution to execution of the same stored procedure. If they do change, then SQL Server may consider these same stored procedures to be different, and not be able to reuse the current query plan stored in cache.
- 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. Stored procedures offer many benefits, including:
- Reduced network traffic and latency, boosting application performance.
- Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
- Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.
- Stored procedures help promote code reuse. While this does not directly boost an application’s performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
- Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns). This saves developer time.
- Stored procedures provide better security to your data.
A part chosen from SQL Server DBA best practices By Brad M.mcGehee