Do Any of Your Stored Procedures Start with sp_?
If you are creating a stored procedure to run in a database other than the Master database, don’t use the prefix “sp_” in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.
The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix “sp_”, is first attempted to be resolved from within the Master database. Since it is not there, time is wasted looking for the stored procedure.
If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is “dbo”. Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don’t name any of your stored procedures with the prefix “sp_”.
Are All Stored Procedures Owned by DBO, and Referred to in the Form of databaseowner.objectname?
For best performance, all objects that are called from within the same stored procedure should all be owned by the same owner, preferably dbo. If they are not, then SQL Server must perform name resolution on the objects if the object names are the same but the owners are different. When this happens, SQL Server cannot use a stored procedure “in-memory plan” over, instead, it must re-compile the stored procedure, which hinders performance.
When calling a stored procedure from your application, it is also important that you call it using its qualified name. Such as:
EXEC dbo.myProcedure
instead of:
EXEC myProcedure
Why? There are a couple of reasons, one of which relates to performance. First, using fully qualified names helps to eliminate any potential confusion about which stored procedure you want to run, helping to prevent bugs and other potential problems. But more importantly, doing so allows SQL Server to access the stored procedures execution plan more directly, and in turn, speeding up the performance of the stored procedure. Yes, the performance boost is very small, but if your server is running tens of thousands or more stored procedures every hour, these little time savings can add up.
Are You Using Constraints or Triggers for Referential Integrity?
Don’t implement redundant integrity features in your database. For example, if you are using primary key and foreign key constraints to enforce referential integrity, don’t add unnecessary overhead by also adding a trigger that performs the same function. The same goes for using both constraints and defaults or constraints and rules that perform redundant work. While this may sound obvious, it is not uncommon to find these issues in SQL Server databases.
Are Transactions Being Kept as Short as Possible?
Keep all Transact-SQL transactions as short as possible. This helps to reduce the number of locks (of all types), helping to speed up the overall performance of SQL Server. If practical, you may want to break down long transactions into groups of smaller transactions. Find out more on how to prevent unnecessary locking.
Application Checklist
Is the Application Using Stored Procedures, Strings of Transact-SQL Code, or Using an Object Model, like ADO, to Communicate With SQL Server?
When an application needs to communicate with SQL Server, essentially it has three choices: it can use stored procedures, strings of Transact-SQL code, or it can use an object model’s properties and methods. From a performance perspective, the most efficient is stored procedures, and the least efficient are an object model’s properties and methods. Ideally, an application should use stored procedures only to access SQL Server.
The advantages of stored procedures have already been described earlier in this article, so they won’t be repeated here. A close second are strings of Transact-SQL code sent to SQL Server. If written correctly, query execution plans are automatically reused, helping to boost performance, although you won’t get the benefits of a stored procedure, such as reducing network traffic. The problem with using a object model’s and methods is that they add an additional layer of code that slows down communications. In addition, often, but not always, the Transact-SQL code created by these properties and methods are not very efficient, further hurting performance.
What Method is the Application Using to Communicate With SQL Server: DB-LIB, DAO, RDO, ADO, .NET?
All applications need to use a data access library (MDAC component) in order to communicate with SQL Server, and there are several to choose from. For best performance, .NET should be your first choice. If you have not yet implemented .NET into your organization, then the next best choice is ADO. Under all conditions, avoid DB-LIB (discontinued but still supported) and DAO, both of which are very slow.
Is the Application Using ODBC or OLE DB to Communication with SQL Server?
If you have a choice between using ODBC or OLE DB to access your SQL Server database, choose OLE DB, as it is generally faster. In addition, using OLE DB allows you to use DSN-less connections, which perform connection maintenance faster than ODBC-based DSN-based connection.
Is the Application Taking Advantage of Connection Pooling?
Try to take advantage of “pooling” to reduce SQL Server connection overhead. Pooling is the term used to refer to the process where a client application can use a preexisting connection from a pool of available connections to connect to SQL Server without having to establish a new connection each time a new connection is required. This reduces SQL Server’s overhead and speeds up connections to SQL Server.
Microsoft offers two types of pooling. Connection pooling is available through ODBC and can be configured by using the ODBC Data Source Administrator, the registry, or the calling application. Resource pooling is available through OLE DB, and can be configured through the application’s connection string, the OLE DB API, or the registry.
Either connection pooling or resource pooling can be run for the same connection. Both pooling connections cannot be used for the same connection. Also, for connection pooling to work effectively, so that connections are reused, how security is implemented is critical. For a connection to be reused, the same security context must be used, or another connection is automatically opened, and connection pooling won’t work. Essentially, what this means is that all users who connect from the application to SQL Server must share the same user account. If they don’t, then each user will automatically open a new connection when they need to access SQL Server through the application.
For maximum performance, will almost always want to take advantage of one or the other pooling methods when connecting to SQL Server.