SQL Server Perfomance Bible

Tips and tricks in SQL Server perfomance…. (read more)

1. Dont run Performance monitor or profiler in the same machine, even if connected remotelly.This is too much overhead and will cause your SQL Server to suffer some performance degradation. Instead, run it on a different server or workstation and remotely monitor the SQL Server.

2. A list of  key counters you would like to watch on a regular basis (explained):

• Memory — Pages/Sec: To see how much paging my server is doing. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal.
• Network Interface — Bytes Total/sec: To see how much network activity is going on.
• PhysicalDisk — % Disk Time — _Total: To see how busy all the disk drives are.
• PhysicalDisk — Current Disk Queue Length — _Total: Also to see how busy the drives are.
• System — % Total Processor Time: To see how busy all the CPUs are as a whole.
• System — Processor Queue Length: Also see how busy the CPUs are.
• SQLServer: General Statistics — User Connections: To see how many connections (and users) are using the server. Keep in mind that one connection does not equal one user. A single user can have more than one connection, and a single connection can have more than one user.
• SQLServer: Access Methods — Page Splits/sec: Lets me know if page splits are an issue or not. If so, then that means I need either to increase the fill factor of my indexes, or to rebuild the indexes more often.
• SQLServer: Buffer Manager — Buffer Cache Hit Ratio: To find out if I have enough memory in the server. Keep in mind that this ratio is based on the average of the buffer hit cache ratio since the SQL Server service was last restarted, and is not a reflection of the current buffer cache hit ratio.
• SQLServer: Memory Manager — Target Server Memory (KB): To see how much memory SQL Server wants. If this is the same as the SQLServer: Memory Manager — Total Server Memory (KB) counter, then I know that SQL Server has all the memory that it wants.
• SQLServer: Memory Manager — Total Server Memory (KB): To see how much memory SQL Server actual is using. If this is the same as SQLServer: Memory Manager — Target Server Memory (KB), then I know that SQL Server has all the memory that it wants. But if this is smaller, then SQL Server needs more available memory in order to run at its optimum performance.

3. Take advantage of SQL Server’s ability to create SQL Server Performance Condition Alerts. You can create alerts that are fired when performance monitor conditions, that you set, are reached. For example, if you want to know if the number of SQL Server user connections exceeds 100, you can create the alert, and when it is fired, SQL Server can e-mail you with the alert message. Create these alerts using SQL Server Enterprise Manger.

4. Optimizing Index Views. Remember:

• These options are set to OFF:
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIERS
  • NUMERIC_ROUNDABORT

5. If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.

Here are some alternatives to using a cursor:

• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries

If you have to use a temp table, do not create it from within a transaction.

If you do, then it will lock some system tables (syscolumns, sysindexes, and syscomments) and prevent others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.

To avoid this problem, create the temporary table before the transaction. This way, the system tables are not locked and multiple users will have the ability to run this same query at the same time, helping concurrency and performance.

6. In a WHERE clause, the various operators used directly affect how fast a query is run. This is because some operators lend themselves to speed over other operators. Of course, you may not have any choice of which operator you use in your WHERE clauses, but sometimes you do.

Here are the key operators used in the WHERE clause, ordered by their performance. Those operators at the top will produce results faster than those listed at the bottom.

• =
• >, >=, <, <=
• LIKE
• <>

7. If you have a choice between using a trigger or a CHECK constraint to enforce rules or defaults within your SQL Server databases, you will generally want to choose a CHECK constraint, as they are faster than using triggers when performing the same task.

8. Following standard database normalization recommendations when designing OLTP databases can greatly maximize a database’s performance. Here’s why:

• Helps to reduce the total amount of redundant data in the database. The less data there is, the less work SQL Server has to perform, speeding its performance.
• Helps to reduce the use of NULLS in the database. The use of NULLs in a database can greatly reduce database performance, especially in WHERE clauses.
• Helps to reduce the number of columns in tables, which means that more rows can fit on a single data page, which helps to boost SQL Server read performance.
• Helps to reduce the amount of Transact-SQL code that needs to be written to deal with non-normalized data. The less code there is, the less that has to run, speeding your application’s performance.
• Helps to maximize the use of clustered indexes, the most powerful and useful type of index available to SQL Server. The more data that is separated into multiple tables because of normalization, the more clustered indexes that become available to help speed up data access.
• Helps to reduce the total number of indexes in your database. The less columns tables have, the less need there is for multiple indexes to retrieve it. And the fewer indexes there are, the less negative is the performance effect of INSERTs, UPDATES, and DELETES.

9.  Don’t run a screensaver on your production SQL Servers, it can unnecessarily use CPU cycles that should be going to your application. The only exception to this is the “blank screen” screensaver, which is OK to us.

10. In general, temp tables should be avoided, if possible. Because they are created in the tempdb database, they create additional overhead for SQL Server, slowing overall performance. As an alternative to temp tables, consider the following alternatives:

• Rewrite your code so that the action you need completed can be done using a standard query or stored procedure, without using a temp table.
• Use a derived table.
• Consider using a correlated sub-query.
• Use a permanent table instead.
• Use a UNION statement to mimic a temp table.

Sample code for Derived Table

[sourcecode lang=”sql”]SELECT * FROM (SELECT * FROM customers) dt_customers[/sourcecode]

…
…….
and the tips goes on and on keep tuned…..