Feeds:
Posts
Comments

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

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

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

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

Quick one today:

Once in a while we need a way to quick check how the database files are growing. This below script allows us to query the last few “AutoGrowth” events on database files.

Every time, autogrowth happens, it is logged in the trace file. Since Sql Server has a default trace file running all the time, we query it for autogrowth events.

Note: Data in trace file only stays there for a short period of time (depending on retention), so if this particular event did not occur in the recent past it may not be in the trace file anymore.

According to Event Class  List:

  1. 92 is for Data file auto growth
  2. 93 is for Log file auto growth
--
--	Get the trace file name
--
DECLARE @TraceFile NVARCHAR(1000);

SELECT @TraceFile = path
FROM sys.traces
WHERE is_default = 1;

--
--	Look for 'AutoGrowth' events in TraceFile
--
SELECT    TOP 10
		  DatabaseID				AS [Database_ID]
		, DB_NAME(DatabaseID)		AS [Database_Name]
		, FileName					AS [DatabaseFileName_Logical]
		, (Duration*1.0/1000000)	AS [TimeSpentOn_AutoGrowth]

FROM sys.fn_trace_gettable(@TraceFile, DEFAULT)

WHERE EventClass IN ( 92	--	DataFile Growth
					, 93)	--	LogFile Growth
AND DatabaseID = DB_ID()
GO
Hope this helps,
_Sqltimes

Quick one today:

Sometimes when we are setting up replication on our lab machines, we run into this error:

TITLE: Configure Distribution Wizard
------------------------------

An error occurred configuring SQL Server Agent.

------------------------------
ADDITIONAL INFORMATION:

RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL Server, 
Error: 22002)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=22002&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

 

Resolution:

This is easy to fix.

When you are going through the “Configure Distributor” wizard, do not select the option to “Yes, configure Sql Server Agent to start automatically“. Choose “No, I will start the Sql Server Agent manually” and go through the steps.

Once completed, go to Sql Server Configuration Manager and set the agent to start automatically.

The account under which these wizard runs does not have permissions to make registry changes. So just use the easier workaround.
Hope this helps,
_Sqltimes

Quick one today:

In a clustered environment, sometimes there is a need to query Cluster Name, Node Name, Instance Name individually; The following script uses SERVERPROPERTY to retrieve that information:

SELECT    SERVERPROPERTY('MachineName')					AS [Cluster_Instance_Name]
		, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NodeName]
		, SERVERPROPERTY('IsClustered')					AS [Is_Clustered]
		, SERVERPROPERTY('ServerName')					AS [MachineName_InstanceName]
		, CASE
			WHEN ISNULL(SERVERPROPERTY('InstanceName'), 0) = 0
				THEN 'Default Instance'
			ELSE SERVERPROPERTY('InstanceName')			END AS [InstanceName]
GO

 

Buit-In Function & DMV:

To continue the point further; to retrieve all the nodes in the clustered environment, query the built-in function called  :  fn_virtualservernodes(). Looks like Microsoft recommends using this DMV instead : sys.dm_os_cluster_nodes.

 

Hope this helps,
_Sqltimes
Follow

Get every new post delivered to your Inbox.

Join 27 other followers