Are Temp Tables Being Used When They Don’t Need to Be?
While the use of temp tables has many practical uses, like the elimination of a cursor, they still incur overhead, and if that overhead can be eliminated, SQL Server will perform faster. For example, there are a variety of ways that temp tables use can be eliminated, which reduces overhead and boosts performance. Some of the ways to eliminate temp tables include:
-
Rewrite the code so that the action you need completed can be done using a standard query or stored procedure.
-
Use the SQL Server 2000 “table” datatype. These may, or may not be faster. You must test to be sure.
-
Consider using a correlated sub-query.
-
Use a permanent table instead.
-
Use a UNION statement to mimic a temp table.
Each of these options can often be used to help eliminate temp tables from your Transact-SQL code.
Are Hints Being Properly Used in Queries?
Generally speaking, the SQL Server Query Optimizer does a good job of optimizing queries. But in some rare cases, the Query Optimizer falls down on the job and a query hint is need to override the Query Optimizer in order to get the best performance out of a query.
While hints can be useful in some situations, they can also be dangerous. Because of this, the use of hints should be done with great care.
One of the biggest issues is inheriting some code that makes a big use of hints, especially code that was written for SQL Server 6.5 or SQL Server 7.0 and is now running under SQL Server 2000. In many cases, hints that were needed under previous versions of SQL Server aren’t applicable under newer versions, and their use can actually hurt, not help performance.
In another case, perhaps hints were found to be useful early when an application was first rolled out, but as time passes and the “nature” of the data stored changes over time, once useful hints may no longer apply to the “newer” data, rendering them obsolete and potentially dangerous to performance.
In both of these cases, it is a good idea to periodically revaluate the benefits of query hints being used. You may find that current hints aren’t useful at all, and in fact, hurt performance. And the only way to find this out is to test them in Query Analyzer and see what is actually happening, and then make your decision on whether to continue using them based on what you find out.
Are Views Unnecessarily Being Used?
Views are best used for handling security-related issues, not as a lazy developer’s method to store often-used queries. For example, if you need to allow a user adhoc access to SQL Server data, then you might consider creating a view for that user (or group), then giving that user access to the view, and not the underlying tables. On the other hand, from within your application, there is no good reason to SELECT data from views, instead, use Transact-SQL code to SELECT exactly what you want from the tables directly. A view adds unnecessary overhead, and in many cases, causes more data than necessary to be returned, which uses up unnecessary overhead.
For example, let’s say that you have a view that returns 10 columns from 2 joined tables. And that you want to retrieve 7 columns from the view with a SELECT statement. What in effect happens is that the query underlying the view runs first, returning data, and then your query runs against the data returned by the query. And since you only need 7 columns, not the 10 columns that are returned by the view, more data than necessary is being returned, wasting SQL Server resources. The rule you should follow in your applications is to always access the base tables directly, not through a view.
Are Stored Procedures Being Used Whenever Possible?
Stored procedures offer many benefits to developers. Some of them include:
-
Reduces network traffic and latency, boosting application performance. For example, instead of sending 500 lines of Transact-SQL over the network, all that is need to be sent over the network is a stored procedure call, which is much faster and uses less resources.
-
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. If you use stored procedures exclusively, you can remove direct SELECT, INSERT, UPDATE, and DELETE rights from the tables and force developers to use stored procedures as the method for data access. This saves DBA’s time.
As a general rule of thumb, all Transact-SQL code should be called from stored procedures.
Inside Stored Procedures, is SET NOCOUNT ON Being Used?
By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. Rarely is this information useful to the client. By turning off this default behavior, you can reduce network traffic between the server and the client, helping to boost overall performance of your server and applications.
To turn this feature off on at the stored procedure level, include the statement:
SET NOCOUNT ON
at the beginning of each stored procedure you write. This statement should be included in every stored procedure you write.