performance

When to use IN operator

Using WHERE IN and WHERE NOT IN clauses in T-SQL code can produce an execution plan involving one or more nested loops. This increases the number of comparisons SQL Server must perform exponentially. Use the WHERE IN clause only if you have a short list of values you need to evaluate:

SELECT *
FROM Customers
WHERE CustomerID NOT IN
   (SELECT CustomerID FROM Orders)

Replace the WHERE IN clause with OUTER JOIN if you're using a subquery to generate a potentially large list. Doing so you can improve performance significantly:

SELECT c.*
FROM Customers c
LEFT OUTER JOIN Orders o
ON o.CustomerID = c.CustomerID
WHERE o.CustomerID IS NULL

In this case, the second query uses LEFT OUTER JOIN, producing an execution plan that lets it run about three times faster than the first query.

Solving the N+1 selects problem

This problems occurs when you have a parent object loading its child data by issuing a separate SQL statement for each child objects that needs to be loaded. So n+1 queries are executed against the database.

Records are being loaded individually because they are being lazy loaded.

What we can do instead if to use eager loading.

Adhoc queries vs Parameterized queries vs Stored procedures

Parameterized queries vs adhoc (dynamic) queries:
- Protects against SQL injection attacks.
- Improves application performance and scalability as the same cached execution plan is reused for each query execution, compared with adhoc queries where an execution plan is generated every time.
- Plan Cache memory is kept low.

Parameterized queries vs stored procedures:
- Similar performance.
- Stored procedures offers advantages when we talk about security or restriction to data.

SQL Server Operations in Query Execution Plan

Clustered Index Scan: Reads all the rows stored in a table stored as a clustered index.
Table Scan: Reads all the rows in table that is stored as a heap structure.
Clustered Index Seek: Traverses the tree structure of table stored as a clustered index to find the needed row(s).
Index Scan: Reads all of the key values of an index to find the matching data.
Index Seek: Traverses the tree structure of an index to find the matching index keys.

Nested Loops Join: For each value in the first data set, SQL Server loops through the second data set looking for matches.
Merge Join: Used to join two data sets that are already sorted using the same key. A row from each source is obtained. If the rows match, they are joined. If the rows don't match, the lower value row is discarded and a new row is obtained from that source.
Hash Match: A hashtable of the smaller data set is created, then SQL Server loops through the larger data set probing the hashtable for matching values. Used when two large datasets must be joined.

When statistics were last updated in SQL Server?

T-SQL showing when statistics were last updated using STATS_DATE function in SQL Server:

SELECT
        OBJECT_NAME(S.object_id) as object_name,  S.name as stat_name,
        S.auto_created, S.user_created, S.no_recompute,
        STATS_DATE(S.object_id, stats_id) as last_updated
FROM
        sys.stats S
        INNER JOIN sys.objects O ON O.object_id = S.object_id AND O.is_ms_shipped = 0
ORDER BY object_name

How to (manually) update the statistics?

  • Force stats to update for a specific table (including clause WITH FULLSCAN):
    UPDATE STATISTICS <tablename>;
  • Full sampling:
    UPDATE STATISTICS <tableName> (<columnName>) WITH FULLSCAN;
  • Force stats to update for the entire database:
    EXEC sp_updatestats;

Count of SQL Server connections by IP address

Count of connections by IPs.

SELECT
        EC.client_net_address, ES.host_name, ES.program_name, ES.login_name,
        COUNT (ec.session_id) as NoConnections
FROM
        sys.dm_exec_sessions ES
        INNER JOIN sys.dm_exec_connections EC ON ES.session_id = EC.session_id
GROUP BY EC.client_net_address, ES.program_name, ES.host_name, ES.login_name
ORDER BY NoConnections DESC

Avoiding deadlocks

The following are some of the techniques you can use to avoid a deadlock:

Access resources in the same physical order => the first transaction will successfully acquire locks on the resources without being blocked by the second transaction.

Decrease the locking
• Convert a nonclustered index to a clustered index.
• Use a covering index for a SELECT statement.

Minimize lock contention
• Implement row versioning through the READ_COMMITTED_SNAPSHOT isolation level or through the SNAPSHOT isolation level.
• Decrease the isolation level.
• Use locking hints: NOLOCK or READUNCOMMITTED.

Blocking resolutions

Once you’ve analyzed the cause of a block, the next step is to determine any possible resolutions. Here are a few techniques you can use to do this:

Use a covering index on the contended data If the query of one of the processes can be satisfied using a covering index, then it will prevent the process from requesting locks on the contended resource.
Optimize the queries executed by blocking and blocked SPIDs E.g. break UPDATE/DELETE statements into multiple batches using the appropriate WHERE clauses. If the individual statements of the batch are executed in separate transactions, then fewer locks will be held on the resource within one transaction, and for shorter time periods.
Decrease the isolation level Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED or WITH (NOLOCK) locking hint.
Partition the contended data This way, exclusive locks will be acquired at partition level, so it will increase concurrency.

Using Indexes Effectively in SQL Server

Adding indexes on a specific table doesn't guarantee you the queries will use these indexes effectively. These are some of the query design rules you should follow to improve the use of indexes:

Avoid nonsargable search conditions
A sargable predicate in a query is one in which an index can be used.

Sargable =, >, >=, <, <=
BETWEEN
LIKE 'text%'
Nonsargable <>, !=
NOT EXISTS
NOT IN
OR
LIKE '%text'

Avoid arithmetic operators on the WHERE clause column
For example, assuming an index has been created on NoWeeks column, the use of the multiplication operator column in the first query prevents the optimizer from using (choosing) the index on the column:

-- the index will not be used
WHERE NoWeeks * 7 > @NoDays

-- the index will be used
WHERE NoWeeks > @NoDays / 7

Avoid functions on the WHERE clause column

-- the index will not be taken into account
WHERE SUBSTRING(CustomerName, 1, 1) = 'F' ;

-- the index will be taken into account
WHERE CustomerName LIKE 'F%' ;

Pages

Subscribe to RSS - performance