Feeds:
Posts
Comments

Archive for August, 2016

Quick one today:

Some times, when SlipStream is performed for Sql Install or for any other reasons, if you want to extract the Service Pack compressed executable into a pre-defined folder structure, there is a technique. Use /extract flag as a parameter to the exe file to extract it into a pre-defined location. Lets see an example:

SQLServer2014SP1-KB3058865-x64-ENU.exe /extract "DestinationLocation"

OR

SQLServer2014SP1-KB3058865-x64-ENU.exe /x "DestinationLocation"

 

Manual SP Extraction

Manual SP Extraction

As you see above, it asks to confirm the destination location; Once confirmed, the SP is extracted to that location.

 

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

Recently, we discussed how to call procedures with OUTPUT parameter. Now, we’ll cover a nuance in the same path. Default parameter values in Stored Procedures.

 

When we call a procedure that has DEFAULT values defined for its parameters, the way we call the procedure could change what values goes go into the execution of the procedure.

 

Variation 1: Purpose of DEFAULT values

DEFAULT values are defined in Store Procedures definition, so when no value is provided in procedure call, default values could be used (during execution).

Sample:

Consider the below sample procedure definition code below:

CREATE PROCEDURE dbo.abc
	  @Param1 INT = 10
...

Parameter @Param1 is defined with 10 as the DEFAULT value. So, when I call the procedure as:

EXEC dbo.abc
GO

The procedure takes the DEFAULT value 10 in its execution.

 

Variation 2 : Values in procedure call

When we actually provide any values in procedure call, the new value provided in the call is used in procedure execution, rather than the default value

DECLARE @P1 INT = 15
EXEC dbo.abc @P1

Now, in procedure execution, the new value (15) is used, and not the default value (10). This makes sense. Every time we need to run the procedure with different values, we just provide them in the parameter and it takes into effect; Where no value is provided, default values kick-in.

Variation 3 : NULL Value in procedure call

When we call the procedure with parameters, but do not provide any value, the default NULL value, assigned during variable declarations, will go in as the value.

DECLARE @P1 INT
EXEC dbo.abc @P1
GO

In this case, when the variable @P1 is declared, by default, NULL value is assigned. Since we do not have a subsequent step to assign value, the NULL value will go into the procedure call. This NULL value will overwrite the default value (10) mentioned in procedure definition.

So, if you want to run with default values defined in procedure definition, just run it like this:

EXEC dbo.abc
GO

 

Complete Code

For more testing, use the below complete code :

--
-- Sample procedure to check the default values
--
CREATE PROCEDURE dbo.abc
	  @Param1 INT = 10
	, @Param2 INT OUTPUT
AS
	SET @Param2 = @Param1

	SELECT @Param1, @Param2
GO

--
-- Variation 1: Purpose of DEFAULT values
--
EXEC dbo.abc
GO

--
-- Variation 2 : Values in procedure call
--
DECLARE @P1 INT = 1
      , @P2 INT
EXEC dbo.abc @Param1 = @P1, @Param2 = @P2 OUTPUT
GO

--
-- Variation 3 : NULL Value in procedure call
--
DECLARE @P1 INT
      , @P2 INT
EXEC dbo.abc @Param1 = @P1, @Param2 = @P2 OUTPUT
GO

Hope this helps,
_Sqltimes

Read Full Post »

In our previous post, we discussed some parameters to improve performance of LogReader Agent. In this one, we’ll continue that topic with Distributor Agent.

Here we’ll look at options available for performance improvement of Distributor Agent

Terminology Context

In Replication, transfer of data happens in transactions. Each transaction that occurs at Publisher is recorded as a Transaction in replication as well, to maintain the data integrity & ACID properties.Each such transaction could have several Commands a.k.a. multiple changes with in each BEGIN TRANSACTION section. All of them are committed together (or rolled back together) at Publisher, Distributor & Subscriber a.k.a. as a single transaction.

Parameters

First, lets see some of them brief details:

Agent Option Default Comments
Distributor SubscriptionStreams 1 Number of parallel threads applying transactions from Distributor to Subscriber
Distributor CommitBatchSize 100 Identified the number of transactions to commit at a time at the Subscriber.

 

Details

SubscriptionStreams

This indicates the number of parallel threads running simultaneously at Subscriber to apply transactions from Distributor. The beauty of this feature is that :

  • It maintains the ACID properties across the threads/SPIDS/processes.
  • If any one threads runs into problems and rolls back, all other threads also rollback.
  • All threads work on the same batch. Batch workload is broken down into parts for each thread to complete, resulting in shorter time to complete each batch.
  • More suitable for workloads with larger bathes or heavy payload. where parallelism is beneficial.

CommitBatchSize

Each transaction committed, at Subscriber, takes certain amount of resources and time.

  • If we have small transactions, the commit is quick; But larger transactions take longer.
  • But longer is not necessarily bad. Time taken to commit 10 large transactions could be shorter than same 10 transactions committed individually. So, we need to play with this parameter to find the right balance.
  • If Distributor and Subscriber are geographically dispersed, then short CommitBatchSize might be more beneficial.
  • Keep in mind, larger batch sizes take longer for rollback, if ever needed.
Hope this helps,
_Sqltimes

Read Full Post »

Lately, we’ve been running several test on Replication and ways to improve the overall performance. Upon reviewing BoL, we found some parameters that could help tweak the performance. They are not applicable for all scenarios, but are good knowledge points to be aware of.

Here we’ll look at options available for performance improvement of LogReader Agent

Terminology Context

In Replication, transfer of data happens in transactions. Each transaction that occurs at Publisher is recorded as a Transaction in replication as well, to maintain the data integrity & ACID properties.Each such transaction could have several Commands a.k.a. multiple changes with in each BEGIN TRANSACTION section. All of them are committed together (or rolled back together) at Publisher, Distributor & Subscriber – as a single transaction.

Parameters

First, lets see some of them brief details:

Agent Option Default Comments
LogReader MaxCmdsInTran 0 Indicates the number of commands per transaction
LogReader ReadBatchSize 500 Number of transactions to read at a time from Publisher and apply to Distributor.
LogReader PollingInterval 5 How frequently the agent checks Publisher’s transactional log for new transactions to replicate

 

Details

MaxCmdsInTran

As we discussed as the top of this article, each Transaction is made up of a number of commands. This option allows us to specify the maximum number of commands allowed in a Transaction.

Please approach this option with caution. All commands that belong to a transaction must go with it (data integrity & ACID properties). So, do not change this option, unless you have a unique situation where each transaction has a larger number of commands that is resulting in EXTREMELY large latencies in committing at Distributor.

ReadBatchSize

Not all entries in the transactional log of Publisher are ‘marked for replication’. So when LogReader, reads the default of 500 transactions, it may sometimes have a large number of transactions identified as not eligible for replication.So, this could result in lesser than 500 transactions to be brought over to Distributor.

This scenario is not common. So do not change this option unless directed by overwhelming evidence or recommendation from Microsoft Support.

The idea is, by changing the read batch size, LogReader could pick up more valid transactions; Hence resulting in higher throughput.

PollingInterval

This determines how frequently, LogReader checks Publishers transactional log. The default value of 5 seconds is optimal for most scenarios. This option goes along with -Continuous option.

To poll more frequently, reduce the number.

Hope this helps,
_Sqltimes

Read Full Post »