SQL Server supports row and page compression on tables, indexes and partitions. This can lead to reduced I/O and better performance. However, it can also result in additional CPU usage in some cases, outweighing the benefits of data compression. SQL Server query optimizer does not cost the overhead of expanding the compressed data, which can lead to plan regression as shown in the video.

The below video demonstrates this with hands-on example. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

Check out http://www.sqlvideo.com for additional hands-on videos.

Subscribe to our newsletter: https://newsletter.sqlworkshops.com

When you use ODBC or SqlClient to access data from SQL Server, by default the query will be cancelled if there is no response from the server within a certain period of time (30 seconds by default). ODBC or SqlClient will start a timer after sending the query to SQL Server and if there are no results from the server within 30 seconds, the query will be cancelled and a timeout error message will be sent to the application. In case a network packet (containing query results) was received within the 30 seconds, and if we are expecting additional network packets (additional rows that couldn’t fit in the first network packet), then the timer is restarted, if the next network packet is not received within this 30 second period, the query will be cancelled.

The common reasons for SQL Command timeout are non-optimal schema or inefficient queries that execute for a long period of time or that miss indexes or lock wait issues. It is important to tune the queries rather than increase the SQL Command timeout settings.

In the video an example is demonstrated where decreasing the network packet size eliminates the timeout, this demonstration is to understand how network packets are involved in resetting the timer and to develop better understanding of timeout. Changing the network packet size is not a solution to avoid timeout, tuning non-optimal schema or inefficient queries or adding indexes or avoiding lock waits are the proper solution.

To identify the command that leads to SQL Command timeout error, you can use Extended Events and monitor event ‘sqlserver.attention’. The extended event script used in the video is available at www.sqlvideo.com/xevents.

In SQL Server Management Studio, the SQL Command timeout can be changed using ‘Options’ in the connection dialog. By default, this is set to ‘0’, which means no timeout. When a query or stored procedure is executing, if you click the ‘Cancel’ button (the red square), the same attention will be sent to the SQL Server (like SQL Command Timeout). And this will in-turn generate ‘sqlserver.attention’ event in Extended Events.

In the below video you can see an example of the SQL Command timeout. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

Check out http://www.sqlvideo.com for additional hands-on videos.

Subscribe to our newsletter: https://newsletter.sqlworkshops.com

SQL Server 2012 brings a new feature called Indirect Checkpoint. You can read more about it here: http://msdn.microsoft.com/en-us/library/ms189573.aspx.

With Indirect Checkpoint, you get smaller and too many I/Os

Checkpoint normally writes large blocks of data to disk optimally in single write operation, up to 256KB, depending on the number of contiguous dirty pages in cache that needs to be flushed to disk. Once you turn on the indirect checkpoint feature by setting target recovery time to a non-zero value, the checkpoint writes will turn into single page writes, 8KB writes.

This will lead to too many I/Os. We all know few large I/Os are more efficient than many small I/Os. This may slow down overall write throughput and performance. If you are hosting your server on the cloud based virtual servers, this means, it will increase the cost as you pay by the number of I/O operations you incur.

Too many outstanding I/Os

Normally check point throttles the number of outstanding I/Os by setting threshold based on write latency in order not to overload the storage subsystem. But once you enable indirect checkpoint, SQL Server can queue up hundreds of thousands of I/Os. This will not only overload the storage subsystem, if users want to read data from the database, their I/O requests will be queued up way behind  the indirect checkpoint writes and they may experience very slow response time and timeouts.

You might also see such errors in the errorlog as large number of I/Os in the queue may take a long time to complete:

A time-out occurred while waiting for buffer latch — type 4, bp 0000000283508040, page 1:1080, stat 0x60d, database id: 16, allocation unit Id: 0/281474976710656, task 0x00000002321BC558 : 0, waittime 300 seconds, flags 0x1a, owning task 0x00000002321BC558. Not continuing to wait.

High CPU usage

When you enable indirect checkpoint, you might encounter high CPU usage. One of the CPUs that is performing the write might spin at 100%. This can be monitored by measuring DP_LIST spins as shown below, more than 4 billion spins were noticed by running the below test just once. Also the writers will use much more CPU.

select * from sys.dm_os_spinlock_stats where name = ‘DP_LIST’

You will notice the updates are much slower when indirect checkpoint is enabled.

In extreme cases when you try to restart the SQL Server service, it will take a long time to stop. Meanwhile you can neither connect nor start the service as the service is already in the process of stopping, flushing the dirty pages before shutdown.

You can use the below scripts to reproduce the scenario

use master

if  exists (select * from sys.databases where name = ‘sqlworkshops’)

drop database sqlworkshops

go

use master

create database sqlworkshops on primary (name = ‘sqlworkshops_1′, filename = ‘P:sqlworkshops_1.mdf’, size = 5GB)

log on (name = ‘sqlworkshops_log_1′, filename = ‘Q:sqlworkshops_log_1.ldf’, size = 64MB)

go

use master

alter database sqlworkshops set recovery simple

go

use sqlworkshops

if  exists (select * from sys.objects where name = ‘tab71′)

drop table tab71

go

use sqlworkshops

create table tab71 (c1 int primary key clustered, c2 int, c3 char(2000))

insert into tab71 with (tablock) select top 500000 row_number() over (order by c1.object_id, c2.o) as c1, 1 as c2, replicate(‘a’, 2000) as c3

from sys.all_columns c1, sys.all_columns c2

update statistics tab71 with fullscan

checkpoint

go

use sqlworkshops

if  exists (select * from sys.objects where name = ‘tab72′)

drop table tab72

go

use sqlworkshops

create table tab72 (c1 int primary key clustered, c2 int, c3 char(2000))

insert into tab72 with (tablock) select top 500000 row_number() over (order by c1.object_id) as c1, 1 as c2, replicate(‘a’, 2000) as c3

from sys.all_columns c1, sys.all_columns c2

update statistics tab72 with fullscan

checkpoint

go

–Session 1:

use sqlworkshops

update tab71 set c2 = 2

go

use sqlworkshops

update tab71 set c2 = 3

go

–Session 2 (optional):

use sqlworkshops

update tab72 set c2 = 2

go

use sqlworkshops

update tab72 set c2 = 3

go

–To monitor spins:

select * from sys.dm_os_wait_stats

where wait_type = ‘DIRTY_PAGE_POLL’

go

When you create a temporary table you expect a new table with no past history (statistics based on past existence), this is not true if you have less than 6 updates to the temporary table. This might lead to poor performance of queries which are sensitive to the content of temporary tables.

I was optimizing SQL Server Performance at one of my customers who provides search functionality on their website. They use stored procedure with temporary table for the search. The performance of the search depended on who searched what in the past, option (recompile) by itself had no effect. Sometimes a simple search led to timeout because of non-optimal plan usage due to this behavior. This is not a plan caching issue rather temporary table statistics caching issue, which was part of the temporary object caching feature that was introduced in SQL Server 2005 and is also present in SQL Server 2008 and SQL Server 2012. In this customer case we implemented a workaround to avoid this issue (see below for example for workarounds).

When temporary tables are cached, the statistics are not newly created rather cached from the past and updated based on automatic update statistics threshold. Caching temporary tables/objects is good for performance, but caching stale statistics from the past is not optimal.

We can work around this issue by disabling temporary table caching by explicitly executing a DDL statement on the temporary table. One possibility is to execute an alter table statement, but this can lead to duplicate constraint name error on concurrent stored procedure execution. The other way to work around this is to create an index.

I think there might be many customers in such a situation without knowing that stale statistics are being cached along with temporary table leading to poor performance.

Ideal solution is to have more aggressive statistics update when the temporary table has less number of rows when temporary table caching is used. I will open a connect item to report this issue.

Meanwhile you can mitigate the issue by creating an index on the temporary table. You can monitor active temporary tables using Windows Server Performance Monitor counter: SQL Server: General Statistics->Active Temp Tables.

 The script to understand the issue and the workaround is listed below:

set nocount on

set statistics time off

set statistics io off

drop table tab7

go

create table tab7 (c1 int primary key clustered, c2 int, c3 char(200))

go

create index test on tab7(c2, c1, c3)

go

begin tran

declare @i int

set @i = 1

while @i <= 50000

begin

insert into tab7 values (@i, 1, ‘a’)

set @i = @i + 1

end

commit tran

go

insert into tab7 values (50001, 1, ‘a’)

go

checkpoint

go

drop proc test_slow

go

create proc test_slow @i int

as

begin

declare @j int

create table #temp1 (c1 int primary key)

insert into #temp1 (c1) select @i

select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)

end

go

dbcc dropcleanbuffers

set statistics time on

set statistics io on

go

–high reads as expected for parameter ’1′

exec test_slow 1

go

dbcc dropcleanbuffers

go

–high reads that are not expected for parameter ’2′

exec test_slow 2

go

drop proc test_with_recompile

go

create proc test_with_recompile @i int

as

begin

declare @j int

create table #temp1 (c1 int primary key)

insert into #temp1 (c1) select @i

select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)

option (recompile)

end

go

dbcc dropcleanbuffers

set statistics time on

set statistics io on

go

–high reads as expected for parameter ’1′

exec test_with_recompile 1

go

dbcc dropcleanbuffers

go

–high reads that are not expected for parameter ’2′

–low reads on 3rd execution as expected for parameter ’2′

exec test_with_recompile 2

go

drop proc test_with_alter_table_recompile

go

create proc test_with_alter_table_recompile @i int

as

begin

declare @j int

create table #temp1 (c1 int primary key)

–to avoid caching of temporary tables one can create a constraint

–but this might lead to duplicate constraint name error on concurrent usage

alter table #temp1 add constraint test123 unique(c1)

insert into #temp1 (c1) select @i

select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)

option (recompile)

end

go

dbcc dropcleanbuffers

set statistics time on

set statistics io on

go

–high reads as expected for parameter ’1′

exec test_with_alter_table_recompile 1

go

dbcc dropcleanbuffers

go

–low reads as expected for parameter ’2′

exec test_with_alter_table_recompile 2

go

drop proc test_with_index_recompile

go

create proc test_with_index_recompile @i int

as

begin

declare @j int

create table #temp1 (c1 int primary key)

–to avoid caching of temporary tables one can create an index

create index test on #temp1(c1)

insert into #temp1 (c1) select @i

select @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)

option (recompile)

end

go

set statistics time on

set statistics io on

dbcc dropcleanbuffers

go

–high reads as expected for parameter ’1′

exec test_with_index_recompile 1

go

dbcc dropcleanbuffers

go

–low reads as expected for parameter ’2′

exec test_with_index_recompile 2

go