Database Reference
In-Depth Information
Avoiding the Use of the sp_ Prefix for Stored Procedure Names
As a rule, don't use the
sp_ prefix
for user stored procedures since SQL Server assumes that stored procedures with
the
sp_ prefix
are system stored procedures, and these are supposed to be in the master database. Using
sp
or
usp
as
the prefix for user stored procedures is quite common. This is neither a major performance hit nor a major problem,
but why court trouble? The performance hit of the
sp_ prefix
is explained in detail in the “Be Careful Naming
Stored Procedures” section of Chapter 19. Getting rid of prefixes entirely is a fine way to go. You have plenty of space
for descriptive object names. There is no need for odd abbreviations that don't add to the functional definition of
the queries.
Minimizing the Use of Triggers
Triggers provide an attractive method for automating behavior within the database. Since they fire as data is
manipulated by other processes (regardless of the processes), triggers can be used to ensure certain functions are
run as the data changes. That same functionality makes them dangerous since they are not immediately visible to
the developer or DBA working on a system. They must be taken into account when designing queries and when
troubleshooting performance problems. Because they carry a somewhat hidden cost, triggers should be considered
carefully. Before using a trigger, make sure that the only way to solve the problem presented is with a trigger. If you do
use a trigger, document that fact in as many places as you can to ensure that the existence of the trigger is taken into
account by other developers and DBAs.
Consider Putting Tables into In-Memory Storage
While there are a large number of limitations on the new in-memory storage mechanisms, the performance benefits
are high. If you have a high volume OLTP system and you're seeing lots of contention on I/O, especially around
latches, the in-memory storage is a viable option. You may also want to explore using in-memory storage for table
variables to help enhance their performance. If you have data that doesn't have to persist, you can even create the
table in-memory using the
SCHEMA_ONLY
durability option. All these methods lead to significant performance benefits.
But remember, you must have the memory available to support these options. There's nothing magic here. You're
enhancing performance by throwing significant amounts of memory, and therefore money, at the problem. You also
have to be running the Enterprise version of SQL Server to make this work.
Configuration Settings
Here's a checklist of the server and database configurations settings that have a big impact on database performance:
•
Memory configuration options
•
Cost threshold for parallelism
•
Max degree of parallelism
•
Optimize for ad hoc workloads
•
Blocked process threshold
•
Database file layout
•
Database compression
I cover these settings in more detail in the sections that follow.
