Pagination in SQL Server

Solution for SQL Server 2008
The ROW_NUMBER function can be used for paging purposes. You assign row numbers to the result rows based on the desired ordering, and then filter the right range of row numbers based on given page-number and page-size arguments.

DECLARE
        @pagenum AS INT = 2,
        @pagesize AS INT = 10;
       
WITH cte AS
(
        SELECT ROW_NUMBER() OVER( ORDER BY Column1) AS rownum, Column1, Column2, Column3
        FROM dbo.MyTable
)
SELECT Column1, Column2, Column3
FROM cte
WHERE rownum BETWEEN (@pagenum - 1) * @pagesize + 1 AND @pagenum * @pagesize
ORDER BY rownum;

For optimal performance, you want to have an index defined on the window ordering elements as the index keys (and eventually include in the index the rest of the attributes that appear in the query for coverage purposes).

Solution for SQL Server 2012 or SQL Server 2014
An alternative solution to paging is to use the new OFFSET/FETCH filtering option:

DECLARE
        @pagenum AS INT = 2,
        @pagesize AS INT = 10;

SELECT Column1, Column2, Column3
FROM dbo.MyTable
ORDER BY Column1
OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

Islands problem - identifying consecutive ranges

I will present a T-SQL query that uses ROW_NUMBER for identifying consecutive ranges of existing values (also known as islands problem).
Given the below script, the expected ranges are: [1,2], [11-14], [25], [32-33].

SET NOCOUNT ON;
IF OBJECT_ID('dbo.MyTable') IS NOT NULL
        DROP TABLE dbo.MyTable;
GO

CREATE TABLE dbo.MyTable
(
col1 INT NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
);

INSERT INTO dbo.MyTable(col1)
VALUES (1),(2),(11),(12),(13),(14),(25),(31),(32),(33);
GO

Step 1: use the ROW_NUMBER function to calculate row numbers based on col1 ordering

SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) as row_num
FROM dbo.MyTable

Step 2: identify islands
The difference between two columns is constant and unique for each island as shown in this query:

SELECT col1, col1 - ROW_NUMBER() OVER (ORDER BY col1) as grp
FROM dbo.MyTable

Step 3: complete solution

;WITH cte
AS
(
        SELECT col1, col1 - ROW_NUMBER() OVER (ORDER BY col1) as grp
        FROM dbo.MyTable
)
SELECT MIN(col1) as start_range, MAX(col1) as end_range
FROM cte
GROUP BY grp

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.

Tips to avoid database blocking

In a multiuser database application, you must minimize blocking among concurrent transactions.

Keep transactions short
• Perform the minimum steps/logic within a transaction.
• Do not perform costly external activity within a transaction, such as sending acknowledgment email or performing activities driven by the end user.

Optimize queries using indexes
• Create indexes as required to ensure optimal performance of the queries within the system.
• Avoid a clustered index on frequently updated columns. Updates to clustered index key columns require locks on the clustered index and all nonclustered indexes (since their row locator contains the clustered index key).
• Consider using a covering index to serve the blocked SELECT statements.

Consider partitioning a contended table

Use query timeouts or a resource governor to control runaway queries

Avoid losing control over the scope of the transactions because of poor error-handling routines or application logic
• Use SET XACTABORT ON to avoid a transaction being left open on an error condition within the transaction.
• Execute the following SQL statement from a client error handler (TRY/CATCH) after executing a SQL batch or stored procedure containing a transaction:

IF @@TRANCOUNT > 0 ROLLBACK

Use the lowest isolation level required
• Use the default isolation level (Read Committed).
• Consider using row versioning to help reduce contention.

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

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%' ;

How to avoid recompilation

- Don’t interleave DDL and DML statements.

- Avoid recompilation caused by statistics changes: use the KEEPFIXED PLAN option or disable the auto update statistics on the relevant table.

- Use table variables instead of temporary tables (as statistics are not created for table variables)

- Avoid changing SET options within the stored procedure.

- Use the OPTIMIZE FOR query hint that uses parameter values
supplied by you to compile the plan, regardless of the values of the parameter passed in by the calling application. It is recommended to use it in case you have a set of parameters that are "representative" from statistics point of view. This way you'll have a plan that works best most of the time.

CREATE PROCEDURE dbo.GetSales
@CustomerId INT
AS
SELECT ...
FROM
       dbo.Sales S
       INNER JOIN ...
WHERE S.CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1)) ;
GO

- Using plan guides. A plan guide allows you to use query hint or other optimization techniques without having to modify the query or procedure text. For example when the query is part of a third-party application and you are not able to modify it by including OPTION (OPTIMIZE FOR).
Example of creating a plan guide:

EXEC sp_create_plan_guide
       @name = N'MyGuide',
       @stmt = N'SELECT ...
                           FROM
                                 dbo.Sales S
                                 INNER JOIN ...
                                 WHERE S.CustomerId = @CustomerId'
,
       @type = N'OBJECT',
       @module_or_batch = N'dbo.CustomerList',
       @params = NULL,
       @hints = N'OPTION (OPTIMIZE FOR (@CustomerId = 1))' ;

Causes of recompilations

- The schema of regular tables, temporary tables, or views referred to in the stored procedure statement have changed. Schema changes include changes to the metadata of the table or the indexes on the table.

- Bindings (such as defaults) to the columns of regular or temporary tables have changed.

- Statistics on the table indexes or columns have changed past a certain threshold.

- An object did not exist when the stored procedure was compiled, but it was created during execution. This is called deferred object resolution.

- SET options have changed. (SET NOCOUNT doesn’t cause stored procedure recompilation)

- The execution plan was aged and deallocated.

- An explicit call of sp_recompile on a table, view, trigger or stored procedure

- There was an explicit use of the RECOMPILE hint:
CREATE PROCEDURE <spName> WITH RECOMPILE => prevents the caching of the stored procedure plan
EXEC <spname> WITH RECOMPILE; => a new plan is generated temporarily, without being cached of affecting any other existing cached plan
OPTION (RECOMPILE)</sql> => causes statement recompilation

DMVs used to analyze the execution plan cache

You can obtain a lot of information about the execution plans in the procedure cache by accessing various dynamic management objects.

sys.dm_exec_cached_plans: obtain information about the execution plans in the plan cache.

sys.dm_exec_query_plan(plan_handle): retrieve the XML representation of the plan.

sys.dm_exec_sql_text(plan_handle): retrieve the original query text.

sys.dm_exec_query_stats: get aggregate performance metrics about the cached plan.

sys.dm_exec_requests: see execution plans for queries that are currently executing.

Index fragmentation resolutions

After you detected fragmentation of an index, you can "resolve" fragmentation in an index by rearranging the index rows and pages so that their physical and logical orders match.

You achieve this through the following techniques:
1. Dropping and re-creating the index
If the index being dropped is a clustered index, then all the nonclustered indexes on the table have to be rebuilt after the cluster is dropped.


2. Creating the index with the DROP_EXISTING clause


3. Executing the ALTER INDEX REBUILD statement on the index
To be performed in case the fragmentation is over 30%.

ALTER INDEX <IX_IndexName> ON <TableName>
REBUILD;

or rebuild all indexes of a specified table:

ALTER INDEX ALL ON <TableName>
REBUILD;


4. Executing the ALTER INDEX REORGANIZE statement on the index
To be performed in case the index fragmentation is between 5% and 30%

ALTER INDEX <IX_IndexName> ON <TableName>
REORGANIZE;


Characteristics of Four Defragmentation Techniques

Drop and Create Index Create Index with DROP_ EXISTING ALTER INDEX REBUILD ALTER INDEX REORGANIZE
Blocking High High Medium Low
Defragment index with constraints Highly complex Moderately Easy Easy
Defragment multiple indexes together No No Yes Yes
Degree of defragmentation High High High Moderate to low
Statistics are updated Yes Yes Yes No
Apply new fill factor Yes Yes Yes No

Pages

Subscribe to SQLhint RSS