For best performance, don’t mix production databases and development (test or staging) databases on the same physical server. This not only serves to better separate the two functions (production and development), but prevents developers from using up server resources that could be better used by production users.
*****
When we think of performance, we usually think about speeding up our application’s performance. But another way to look at performance is to look at our performance as DBAs or Transact-SQL developers.
For example, one of the easiest ways to speed up our Transact-SQL coding, in addition to making it easier to maintain and troubleshoot our code once it is written, is to format it in an easy to read format.
While there are many different code formatting guidelines available, here are some basic ones you should consider following, if you aren’t doing so already:
Begin each line of your Transact-SQL code with a SQL verb, and capitalize all Transact-SQL statements and clauses, such as:
SELECT customer_number, customer_name
FROM customer
WHERE customer_number > 1000
ORDER BY customer_number
If a line of Transact-SQL code is too long to fit onto one line, indent the following line(s), such as:
SELECT customer_number, customer_name, customer_address,
customer_state, customer_zip, customer_phonenumber
Separate logical groupings of Transact-SQL code by using appropriate comments and documentation explaining what each grouping goes.
These are just a few of the many possible guidelines you can follow when writing your Transact-SQL code to make it more readable by you and others. You just need to decide on some standard, and then always follow it in your coding. If you do this, you will definitely boost your coding performance.
*****
Be wary of allowing users to directly access your databases (especially OLTP databases) with third-party database access tools, such as Microsoft Excel or Access. Many of these tools can wreck havoc with your database’s performance. Here are some reasons why:
• Often these users aren’t experienced using these tools, and create overly complex queries that eat up server resources. At the other extreme, their queries may not be complex enough (such as lacking effective WHERE clauses) and return thousands, if not millions, or unnecessary rows of data.
• This reporting activity can often lock rows, pages or tables, creating user contention for data and reducing the database’s performance.
• These tools are often file-based. This means that even if an effective query is written, the entire table (or multiple tables in the case of joins) has to be returned to the client software where the query is actually performed, not at the server. Not only can this lead to excess server activity, it can also play havoc with your network.
If you have no choice but to allow users access to your data, try to avoid them hitting your production OLTP databases. Instead, point them to a “reporting” server that has been replicated, or is in the form of a datamart or data warehouse.
*****
SQL Server offers support of SSL encryption between clients and the server. While selecting this option prevents the data from being viewed, it also adds additional overhead and reduces performance. Only use SSL encryption if absolutely required. If you need to use SSL encryption, consider purchasing a SSL encryption processor for the server to speed performance.
*****
SQL Server supports named instances of SQL Server. You can run up to 16 concurrent instances of SQL Server on the same server.
As you might imagine, each running instance of SQL Server takes up server resources. Although some resources are shared by multiple running instances, such as MSDTC and the Microsoft Search services, most are not. Because of this, each additional instance of SQL Server running on the same server has to fight for available resources, hurting performance.
For best performance, run only a single instance (usually the default) on a single physical server. The main reasons for using named instances is for upgrading older versions of SQL Server to newer versions of SQL Server, transition periods where you need to test your applications on multiple versions of SQL Server, and for use on development servers.
*****
If you run the ALTER TABLE DROP COLUMN statement to drop a variable length or text column, did you know that SQL Server will not automatically reclaim this space after performing this action? To reclaim this space, which will help to reduce unnecessary I/O due to the wasted space, you can run the following command:
DBCC CLEANTABLE (database_name, table_name)
Before running this command, you will want to read about it in Books Online to learn about some of its options that may be important to you.
*****
Trace flags, which are used to enable and disable some special database functions temporarily, can sometimes chew up CPU utilization and other resources on your SQL Server unnecessarily. If you just use them for a short time to help diagnose a problem, for example, and then turn them off as soon as you are done using them, then the performance hit you experience is small and temporary.
What happens sometimes is that you, or another DBA, turns on a trace flag, but forgets to turn it off. This of course, can negatively affect your SQL Server’s performance. If you want to check to see if there are any trace flags turned on a SQL Server, run this command in Query Analyzer:
DBCC TRACESTATUS(-1)
If there are any trace flags on, you will see them listed on the screen after running this command. DBCC TRACESTATUS only finds traces created at the client (connection) level. If a trace has been turned on for an entire server, this will not show up.
If you find any, you can turn them off using this command:
DBCC TRACEOFF(number of trace)
*****
SQL Server offers a feature called the black box. When enabled, the black box creates a trace file of the last 128K worth of queries and exception errors. This can be a great tool for troubleshooting some SQL Server problems, such as crashes.
Unfortunately, this feature uses up SQL Server resources to maintain the trace file than can negatively affect its performance. Generally, you will only want to turn the black box on when troubleshooting, and turn it off during normal production. This way, your SQL Server will be minimally affected.