Feeds:
Posts
Comments

Archive for the ‘Sql Server 2012’ Category

In a recent post, we saw details on one of the advanced setting starting Sql Server 2012, called TARGET_RECOVERY_TIME. When set correctly, this interacts with the server level setting called ‘recovery interval‘.

One is a server-level setting (recovery interval) and the other is a database level setting. So we need to understand the interaction or dynamic between these two settings and how they interact when set on the same database.

By default, recovery interval is set to 0 (1 minute) — which kicks the CHECKPOINT every one minute to flush dirty pages to disk.

Note:1 minute is just a general guideline, the actual interval depends on the amount of traffic on the database system. For higher traffic systems, there will be a lot of transactions each second, so there will be more dirty pages. So, the CHECKPOINT (background writer) kicks off more frequently than once a minute.

By default, both TARGET_RECOVERY_TIME & Recovery Interval is set to 0. so CHECKPOINTs occur approximately every 1 minute.

Recovery Interval could be set to 2 or 3 (minutes) or some other higher number to allow longer recovery times after a crash. So, Sql Server waits a longer period before flushing dirty pages to disk —  which results in longer times for recovery after crash; As it needs to roll-forward & roll-back transactions.

--
-- Set recovery interval on Sql Server 2012
--
SELECT *
FROM sys.configurations
WHERE name = 'recovery interval (min)'
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'recovery interval', 2
GO

EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO

TARGET_RECOVERY_TIME could be set in seconds or minutes. This determines the duration allowed for recovery time after a crash; This setting overwrites the system-level setting (recovery interval).

--
--  Change TARGET_RECOVERY_TIME
--
ALTER DATABASE SampleDB
SET TARGET_RECOVERY_TIME = 30 SECONDS;
GO

Relationship between the two settings:

  • When TARGET_RECOVERY_TIME is set, it overrides recovery interval setting.
  • Similarly, when recovery interval setting is configured and TARGET_RECOVERY_TIME is set to 0, then automatic checkpoint (recovery interval) is used.
TARGET_RECOVERY_TIME recovery interval Checkpoint Used
0 0 Automatic CHECKPOINT is used, where target recovery interval is 1 minute
0 >0 Automatic CHECKPOINT us used. The setting comes from (>0) number.
>0 N/A Indirect checkpoint. Setting is based on TARGET_RECOVERY_TIME
Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

A few months ago, we saw a point on CHECKPOINT and its counterparts. Now, lets dig a little deeper into it.

Starting Sql Server 2012, we have a new advanced option called TARGET_RECOVERY_TIME; It helps with setting Indirect Checkpoints to change the recovery time after a crash.

Automatic CheckPoints are the default (system level setting in sys.configurations) settings that decide how frequently the dirty pages in buffer pool are written to disk. Usually it is once every minute (generalization), but there are some nunaces to it (see NOTE below).   This helps in reducing the amount of time it takes to bring the system back to working after a crash.

Note:1 minute is just a general guideline, the actual interval depends on the amount of traffic on the database system. For higher traffic systems, there will be a lot of transactions each second, so there will be more dirty pages. So, the CHECKPOINT (background writer) kicks off more frequently than once a minute.

With new Indirect Checkpoints, a database level setting, we could configure a custom checkpoint settings to enable faster & predictable recovery times after crash.

TARGET_Recovery_Time_Setting

Context:

When we UPDATE/INSERT data into Sql Server, it is written to buffer pool, not disk. Only when (automatic/default) CHECKPOINT occurs, all the dirty pages in buffer pool are written to disk. This occurs at one minute intervals (varies based on workload, but 1 min in a good general guideline). So, approximately every minute, at the 60th second, you’ll see a HUGE spike in I/O to the MDF/NDF files as all the dirty pages are being written to disk. Then it waits for another ~60 seconds for the next CHECKPOINT, to write all the dirty pages to disk again. So, you see a pattern here.

The entire dirty page workload is being written to disk in one shot —  then wait (sit idle) for the next 60 seconds; And then again write the next workload to disk in one shot. As you can see, the I/O subsystem will be more active during these CHECKPOINT periods than at anytime in between.

If your storage is designed to handled, let’s say, 100 MB/sec and you have 1000 MB worth of dirty pages since the last checkpoint (1 min), it might take storage subsystem more than 10 seconds to fully process the workload. This results in unnecessary spikes in I/O metrics.

See the image below, where it shows the Maximum reading on the amount of dirty pages written to disk.

Default_CheckPoint_1min_interval

This presents an incorrect picture that there is something wrong with your storage. While staying idle the remaining 50 (to 45) seconds of the minute.

--
--	Change TARGET_RECOVERY_TIME
--
ALTER DATABASE SampleDB
SET TARGET_RECOVERY_TIME = 30 SECONDS;
GO

Advantages

  • I/O bottlenecks:
    • Now, if we could write more frequently, then the same workload could be accomplished without triggering off any false positive metrics (and also reducing recovery time after a crash).
    • In our above example, if the same 1000 MB dirty page workload per minute, could be written 2 times within a minute, we’ll have ~500MB workload every 30 seconds.
    • Now, the same storage metrics will show much better picture.
    • Then we could tweak the design of storage to the requirements of Sql Server dirty page workload.

TARGET_Recovery_Time_30seconds

  • Indirect Checkpoints enable you to control recovery time after a crash to fit within your business requirements

Disadvantages:

  • For OLTP workloads, sometimes this setting could result in performance degradation. Looks like the background writer, that writes dirty pages to disk, increases total write workload for server instance.
    • If different databases have different settings, the instance ends up doing more work, which might result in performance degradation.
    • So, this setting needs to be tested in performance environment before enabling it in Production environments.
In the next post, we’ll see the interaction between ‘recovery interval‘ & TARGET_RECOVERY_TIME setting.
Hope this helps,
_Sqltimes

Read Full Post »

Problem

While gathering replication backlog details, ran into this interesting error. The goal was to run the sp_replmonitorsubscriptionpendingcmds stored procedure and store the output in a table. As we’ve seen in a recent post, redirecting output of a stored procedure execution into a table is possible; But in this case, it throws an error saying that is not allowed.

INSERT INTO #DC1_Repl_Backlog
EXEC  sp_replmonitorsubscriptionpendingcmds
		  @publisher	= 'InstanceName'
		, @publisher_db	= 'DBName'
		, @publication	= 'Publication'
		, @subscriber	= 'Subscriber'
		, @subscriber_db= 'DBName2'
		, @subscription_type = '0'
GO
Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 233
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)

With the available information, right now, a clear & coherent explanation fo this behavior is not available from my end. But my guess is this; The code inside this stored procedure must be using a similar INSERT INTO #table EXEC sp_xyz, hence the error “INSERT EXEC statement cannot be nested

Resolution

OPENROWSET helps in getting around this. See the sample code below:

--
--
--
IF OBJECT_ID('tempdb..#DC1_Repl_Backlog') IS NOT NULL
	DROP TABLE #DC1_Repl_Backlog

CREATE TABLE #DC1_Repl_Backlog (
	  pendingcmdcount	BIGINT
	, estimatedprocesstime	BIGINT
)

INSERT #DC1_Repl_Backlog (pendingcmdcount, estimatedprocesstime)
SELECT *
FROM OPENROWSET('SQLOLEDB',
		'Server=InstanceName;Trusted_Connection=yes;',
		'EXEC Ditribution.dbo.sp_replmonitorsubscriptionpendingcmds
					  @publisher = ''PublisherInstance''
					, @publisher_db	= ''DBName''
					, @publication = ''Publication''
					, @subscriber = ''Subscriber''
					, @subscriber_db = ''DBName2''
					, @subscription_type = ''0'''
		) 

SELECT *
FROM #DC1_Repl_Backlog
GO
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

In replication, the transactions that come into the system are stored in Distribution database first; From there they are replicated to each Subscriber. The data stays there for several hours. This is determined by a ‘Transaction Retention‘ setting on the Distributor.

Similarly, as the data is replicated, log entries are made in the Distributor database. These entries also have a retention policy that could be set using ‘History Retention‘ setting.

Go to Distribution instance, Replication >> Right click >> Distributor Properties

Distributor Retention Properties

Distributor Retention Properties

Under general tab, we see these settings. Change them as needed to achieve longer/shorter retention of both data & log.

Retention Policy

Retention Policy

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Every few days, we run into a situation, where just adding an ORDER BY clause does not solve the problem of retrieving records in a particular fashion.

ORDER BY is beneficial, if you want to order the records and retrieve them in a defined pattern. Adding TOP 10 (TOP 100, etc) to it makes it easy to query the ones you want. But our situation we needed to order the records in ascending order, but we only want the bottom 10 records.

Drum roll !! Enter the all purpose CTE to rescue!!

--
-- Create a temp table and store records in some random order
--
CREATE TABLE #OrderTest (
	  ID	INT			NOT NULL
	, Name	VARCHAR(10)	NOT NULL
	, Flag	TINYINT
)
GO

INSERT INTO #OrderTest (ID, Name, Flag)
VALUES (2, 'Test Name1', 1)
	, (4, 'TestName2', 0)
	, (1, 'TestName3', 7)
	, (3, 'TestName4', 2)
	, (9, 'TestName5', 5)
	, (8, 'TestName6', 1)
	, (5, 'TestName7', 3)
	, (6, 'TestName8', 0)
GO

SELECT * FROM #OrderTest
GO
Initial order of records (Insert order)

Initial order of records (Insert order)

Now, when we query TOP 10 using ORDER BY ID ASC, we’ll get records in the expected ascending order of the ID column. But the requirement is to retrieve BOTTOM 5 records, with the same ascending order on ID column. Like TOP 10 clause, there is no BOTTOM 10 clause. See the image below for clearer understanding:

Records we need to query

Records we need to query

So we use CTE to circumvent that.

Usually ORDER BY clause is not allowed in CTE’s; Except when TOP clause is used.

--
-- Use CTE to pick the records we want
--
; WITH Bottom5 (ID, Name)
AS
	(
		SELECT TOP 5 ID, Name
		FROM #OrderTest
		ORDER BY ID DESC
	)
SELECT *
FROM Bottom5
ORDER BY ID ASC
GO

Desired Result

Desired Result

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Recently, in one of our lab machines, we ran into this errors during replication reconfiguration (rebuilding) efforts.

Msg 18752, Level 16, State 1, Procedure sp_replcmds, Line 1
Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, 
and sp_replshowcmds) can connect to a database at a time. If you executed a 
log-related procedure, drop the connection over which the procedure was executed 
or execute sp_replflush over that connection before starting the Log Reader Agent 
or executing another log-related procedure.

The error message, though verbose, it was a bit confusing to understand the underlying cause for this error. Once, we reiterated the steps that resulted in this error, it became clear as to what could have contributed to this error. We had replication set upon a lab machine, and before this error occurred, we were attempting to drop transactional replication using SSMS GUI.

Turns out Log Reader agent was not properly stopped before attempting this; To resolve this, just stop the Log Reader Agent. To be sure, go to SSMS > Replication > Right click on Publication and go to ‘View Log Reader Agent Status‘.

Once Log Reader Agent is stopped, we were able proceed with dropping Transactional replication configuration.

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »