Is the Application Properly Opening, Reusing, and Closing Connections?
Generally speaking, a connection to SQL Server should only be opened when it is needed, used, then immediately closed by the application. Assuming that you are using connection pooling and are using the proper security model, what will happen is that if a connection is not currently available, it will be created. And once the connection is closed by the application, it will remain open (although the application thinks that it is closed), available to be reused as needed.
Reducing how often actual connections are opened and closed reduces SQL Server’s overhead. Also, by opening and the closing a connection quickly from an application, these allows pooled connections to be more efficiently reused, which also helps to reduce overhead, boosting performance.
Is the Transact-SQL Code Being Sent to SQL Server Optimized for SQL Server, or is it Generic SQL?
Some applications are designed to work with multiple databases, and because of this, use ANSI SQL instead of Transact-SQL to access SQL Server data. While this does make it easier to connect to a wide variety of different flavors of database, it also hurts performance. Transact-SQL offers some specific code that is not available in ANSI SQL which offers performance benefits. Ideally, for best performance, Transact-SQL should be used to access SQL Server, not generic ANSI SQL.
Does the Application Return More Data from SQL Server Than it Needs?
This is similar to one of the audit suggestions for Transact-SQL. Some applications, especially those that allow a user to browse data, return way too much data to the user, often allowing the application to further “limit” the data for the user’s benefit. For example, I have seen applications that essentially return all the rows in a table, or a view, to the application, where the application then sorts the data and allows the user to browse through it. If the number of rows is not large, this is OK. But if the number of rows is huge, let’s say 100,000 or more, then SQL Server has to produce a huge amount of work (generally a table scan) to return all this data, and the network is also flooded. No user will use all of this data. The application should be designed to only return that data the user really needs at that moment, and not one byte more.
Another example of returning too much data includes applications that allow the user to specify the query criteria. If you must allow users to select their own criteria, it is important to prevent them from returning too many rows by accident. For example, you can use the TOP clause in your SELECT statement, or you can include default parameters for the WHERE clause to prevent users from returning every row in a table.
Returning unneeded data is a terrible waste of resources and a problem that is easily avoided with a little planning.
Does the Application Keep Transactions Open When the User is Modifying Data?
This audit suggestion is also similar to one described above for Transact-SQL. One comment activity found in most applications is that a user is allowed to lookup a record, then update it. The key to doing this successfully is to ensure that when you allow a user to do this, that you don’t keep the connection open–and the record locked–as it is being updated. If you do, you can create unnecessarily long blocking locks that can hurt SQL Server’s performance.
Ideally, from the application’s point of view, once the user specifies what record to update, the application should open the connection, select the record, and close the connection. Now the record is on the application’s screen. Once the user is done updating it, then the application needs to reopen the connection, update the modified record (assuming it was modified), and then close the connection. It is critical that transactions be kept as short as possible.
Where Do I Go From Here
If you made it all the way to here, I have to admire your sincere interest in boosting the performance of your SQL Server. As I have already mentioned, this part of the performance audit will be the most difficult. If fact, you may even decide to leave it to the last part of your audit, or if your applications are third-party, ignore it all together. But if your think your application is the root of your performance problems, and you have access to the code, then you may want to follow these audit suggestions, step-by-step.
Read the Next Article in this Series
Return to Previous Article in the Series
]]>