Performance bottlenecks - ways to detect

Source: Praveen (AsthraSoft)

CPU

Symptoms: unexpectedly high CPU usage and low throughput

How to detect:

·         If % Processor Time counter > 80%

·         sys.dm_os_schedulers DMV - runnable_tasks_count is high

·         sys.dm_exec_query_stats DMV, statistics of currently cached batches/stored procedures, and total_worker_time, execution_count

Potential causes

·         Excessive compilation/recompilation

o    Goal: identify excessive recompilation and reduce it

·         Inefficient query plan

o    Goal: take steps to write queries with efficient plans

·         Intra-query parallelism

o    Goal: identify parallel queries and make sure they are efficient

Memory

Symptoms

·         Explicit memory-related errors (e.g. out of memory, timeout while waiting for memory resource).

·         I/O utilization is higher than usual

·         Overall system slow behavior

How to detect:

·         PerfMon

o    Process object: Working set, Private bytes

o    Memory object: Available KBytes, System Cache Resident Bytes, Committed bytes, Commit Limit

o    SQL Server: Buffer Manager object - Buffer cache hit ratio, Page life expectancy, Checkpoint pages/sec, Lazy writes/sec

IO

Symptoms

·         Slow response time, timeout error messages, I/O subsystem operates at its max capacity

Causes

·         Moving database pages between memory and disk

·         Log file operations

·         TempDB operations

How to detect

·         PerfMon: Physical Disk object

o    % Disk Time > 50%

o    Avg. Disk Queue Length > 2

o    Avg. Disc sec/Read or Avg. Disc sec/Write > 10-20 ms

o    Avg. Disk Reads/sec or Avg. Disk Writes/sec > 85% of disk capacity

• DMVs
• sys.dm_os_wait_stats for wait_type like ‘PAGEIOLATCH%’
• sys.dm_io_pendion_io_requests with sys.dm_io_virtual_file_stats
• sys.dm_exec_query_stats: *_reads, *_writes columns

TempDB

Symptoms

·         Running out of TempDB space

·         Bottleneck in system tables due to excessive DDL operations

·         Allocation contention

How to detect

·         DMVs:

o    sys.dm_db_file_space_usage (user, internal objects and version store sizes)

o    sys.dm_tran_active_snapshot_database_transactions (longest running transaction  most row version space)

o    sys.dm_db_session_space_usage (accounted at the end of a task)

o    sys.dm_db_task_space_usage

·         PerfMon:

o    SQL Server: Transactions object

o    Version Generation/Cleanup rates

Locks

Sql server uses different locks to manage database concurrency.

Shared locks allow a resource to be read by multiple concurrent transactions, but do not allow data to be modified while the shared lock exists on the resource.

Update locks are used to eliminate a dead lock scenario. With this, only one transaction at a time can obtain update lock on a resource. When the actual update occurs update lock will be converted to exclusive lock.

Exclusive locks are used to prevent concurrent access to a resource by multiple transactions. Only the transaction with the exclusive lock can read or modify the data. This type of lock is used when you are modifying data during an update operation. You can read data held by exclusive lock only by specifying a NOLOCK hint or using a read uncommitted isolation level.

Intent locks are used by sql server to indicate that a lock is being acquired for some resources further down in the hierarchy. A shared intent lock on a table indicates that a transaction plans to acquire a shared lock on rows or pages within the table. This prevents another transaction from placing an exclusive lock on the table containing the page or rowlocks. The types of intent locks are 1) intent shared 2) intent exclusive 3) shared with intent

Schema locks are used to control concurrency for a table and database alterations. Sch-m locks are established for DDL commands. Sch-s locks are used for operations such as compiling a query. They prevent schema changes from being made.

Bulk update locks are used to allow multiple processes to copy data in bulk into a table concurrently while preventing access to the table by any other process that is not copying bulk data into it. This lock is used when you employ the TABLOCK hint for bulk copy operations or when you set the ‘Table lock on bulk load’ option with the sp_tableoption stored procedure. 

Here's a little explanation of the three columns from sys.dm_tran_locks used in the examples:

resource_type	This tells us what resource in the database the locks are being taken on. It can be one of these values: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT.
request_mode	This tells us the mode of our lock.
resource_description	This shows a brief description of the resource. Usually holds the id of the page, object, file, row, etc. It isn't populated for every type of lock

The filter on resource_type <> 'DATABASE' just means that we don't want to see general shared locks taken on databases. These are always present.

Key - Range locks

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

• RangeX-X - exclusive lock on the interval between the keys and exclusive lock on the last key in the range
• RangeS-U – shared lock on the interval between the keys and update lock on the last key in the range

Observations on locks

UPDATE emp SET empname=UPPER(empname) WHERE empid=1

KEY                   X

OBJECT             IX

PAGE                IX

-----------------------------------------------------------------------

SELECT * FROM emp (REPEATABLEREAD)

KEY                   S

OBJECT             IS

PAGE                IS

-----------------------------------------------------------------------------

SELECT * FROM emp (READCOMMITTED) --& (READUNCOMMITTED) & (SNAPSHOT)

DATABASE         S

-------------------------------------------------------------------------------

SELECT * FROM emp (SERIALIZABLE)

KEY                   RangeS-S

OBJECT             IS

PAGE                IS

Query Design hints

Here’s a list of the performance-related best practices you should follow when designing the database queries:

·         Use the command SET NOCOUNT ON.

·         Explicitly define the owner of an object.

·         Avoid nonsargable search conditions.

·         Avoid arithmetic operators and functions on WHERE clause columns.

·         Avoid optimizer hints.

·         Stay away from nesting views.

·         Ensure there are no implicit data type conversions.

·         Minimize logging overhead.

·         Adopt best practices for reusing execution plans.

·         Adopt best practices for database transactions.

·         Eliminate or reduce the overhead of database cursors.

·         BETWEEN is better than IN

·         always use the same data type for both expressions in where clause to avoid implicit conversions

·         Use EXISTS over COUNT(*) to Verify Data Existence

·         Use UNION ALL Instead of UNION

·         Reduce Logging Overhead by using table variables in place of temp tables

·         Do not use SELECT *; use proper column names to decrease network traffic and fewer locks on table.

·         Avoid Cursors as it results in performance degradation. Sub Query, derived tables, CTE can perform same operation.

·         NULL columns consume an extra byte on each column used as well as add overhead in queries. Also NULL is not good for logic development for programmers.

·         Normalized database always increases scalability and stability of the system. Do not go over 3rd normal form as it will adversely affect performance.

·         Use WHERE clauses to compare assertive logic. Use IN rather than NOT IN even though IN will require more value to specify in clause.

·         Always perform referential integrity checks and data validations using constraints such as the foreign key and check constraints.

·         Stored Procedure should return same numbers of resultset and same columns in any input parameters. Result Set of Stored Procedure should be deterministic.

·         Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.

·         Use Column name in ORDER BY clause instead of numbers.

·         Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR (MAX) or NVARCHAR (MAX).

·         Do not use temp tables; use CTE or Derived tables instead.

·         Try to use constraints instead of triggers, whenever possible. Constraints are much more efficient than triggers and can boost performance.

·         Use table variables instead of temporary tables. Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible.

·         When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause.

·         Try to avoid using the DISTINCT clause, whenever possible.

Single-user mode

(Source: msdn.microsoft.com)

Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

Limitations and Restrictions

If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

Prerequisites

Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode.

ALTER DATABASE AdventureWorks2012

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

Query Hints

(Source: msdn.microsoft.com)

Query hints specify that the indicated hints should be used throughout the query. They affect all operators in the statement. If UNION is involved in the main query, only the last query involving a UNION operation can have the OPTION clause. Query hints are specified as part of the OPTION clause. If one or more query hints cause the query optimizer not to generate a valid plan, error 8622 is raised. Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend only using hints as a last resort for experienced developers and database administrators.

Query hints cannot be specified in an INSERT statement except when a SELECT clause is used inside the statement. Query hints can be specified only in the top-level query, not in subqueries. When a table hint is specified as a query hint, the hint can be specified in the top-level query or in a subquery;

Specifying Table Hints as Query Hints

When specified as a query hint, the INDEX, FORCESCAN, and FORCESEEK table hints are valid for the following objects:

• Tables
• Views
• Indexed views
• Common table expressions (the hint must be specified in the SELECT statement whose result set populates the common table expression)
• Dynamic management views
• Named subqueries

The INDEX, FORCESCAN, and FORCESEEK table hints can be specified as query hints for a query that does not have any existing table hints, or they can be used to replace existing INDEX, FORCESCAN or FORCESEEK hints in the query, respectively. Table hints other than INDEX, FORCESCAN, and FORCESEEK are disallowed as query hints unless the query already has a WITH clause specifying the table hint. In this case, a matching hint must also be specified as a query hint by using TABLE HINT in the OPTION clause to preserve the semantics of the query. When a table hint other than INDEX, FORCESCAN, or FORCESEEK is specified by using TABLE HINT in the OPTION clause without a matching query hint, or vice versa; error 8702 is raised and the query fails.

Examples:

OPTION (MERGE JOIN)

OPTION (MAXRECURSION 2)

OPTION (MERGE UNION)

OPTION (MAXDOP 2)

OPTION (TABLE HINT(e, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))

OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))

Table Hints - Points to remember

·         The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead.

·         Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.

·         If a table contains computed columns that are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables and are not propagated.

·         SQL Server does not allow for more than one table hint from each of the following groups for each table in the FROM clause:

• Granularity hints: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK, or TABLOCKX.
• Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.


Nolock
·         A compatibility level of 80 does not allow nolock without a WITH.
·         Error 601 - When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft® SQL Server™ is not able to continue the scan.
·         Table hints are not sent through the client stack you can use a query with OPENQUERY to address the relevant SQL Server and submit any table hints you want the external server to process. The query will be just passed through and interpreted by the linked server.
·         Create a view on the remote server that has the nolock specified in it, or a stored procedure and call through one of those mechanisms to get the data without causing locking.
·         NOLOCK generally helpful in blocking scenario not in Dead lock.
·         Enabling any of the snapshot options has some repercussions. It causes SQL Server to add 14 bytes to every updated row. To create the version store from which SQL Server constructs the snapshot, SQL Server uses tempdb
·         NOLOCK will read the data irrespective of the (transaction) lock on it, and NOWAIT will return error if the original table has (transaction) locked on it.