Feeds:
Posts
Comments

Archive for the ‘Cool Script’ Category

Quick one today:

In the past, we’ve covered a MAXDOP query hint with details and nuances (and here too). This time, we’ll get into another interesting query hint MAXRECURSION.

One of my colleagues needed to come up with a numbers table with values like this, where there are multiple batches and each batch has some records.

Batch RecordsInBatch
1 1
1 2
1 3
1 4
1 2500
2 1
2 2
.. ..
2 2500
.. ..
999 2500

This could be achieved with recursive CTE with a table OPTION (MAXRECURSION = n).

--
--  Numbers table with recursive CTE
--
DECLARE @BN INT = 999, @RN INT = 2500

;WITH BatchNumbers (BatchNum)
AS	(
		SELECT 1 AS BatchNum
		UNION ALL
		SELECT BatchNum + 1 AS BatchNum
		FROM BatchNumbers
		WHERE BatchNum < @BN
	),
RecordNumbers (RecNum)
AS	(
		SELECT 1 AS RecNum
		UNION ALL
		SELECT RecNum + 1 AS RecNum
		FROM RecordNumbers
		WHERE RecNum < @RN
	)
SELECT BatchNumbers.BatchNum, RecordNumbers.RecNum
FROM RecordNumbers
CROSS JOIN BatchNumbers
ORDER BY BatchNumbers.BatchNum, RecordNumbers.RecNum
OPTION (MAXRECURSION 2500)
GO

MAXRECURSION – points to keep in mind

  • Server-wide, the limit is set to 100.
    • Use MAXRECURSION query hint to prevent infinite loops
  • Server-wide default is set to 100
  • When MAXRECURSION 0 is specified, it is same as infinite loops (no limit)
  • In query hint, values for MAXRECURSION range from 0 to 32,767
  • In cases where incorrect code enters into production, that results in infinite loops, MAXRECURSION could be used to limit the loops.
Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

On one of our production machines, we recently added a new LUN to a SQL cluster. A task like this is a team effort. Sysadmins perform some steps and DBA carry out the remaining. In this article, the main focus is on covering the steps after the LUN is added to the OS & Sql Cluster by the sysadmins.  For context, we’ll start with high level steps before going into details.

Sysadmins steps

  1. Add new storage to the machine/OS as an available storage
  2. Format the available drive with appropriate settings (cluster size) and add it as a new drive
  3. Make drive available to the Cluster using “Add Disk” screen in FailOver Cluster Management tool.

DBAs steps

  1. Add available storage to Sql Cluster
  2. Configure dependency (and check the report before & after)
  3. Add data file on the new cluster storage drive

Here we’ll cover the DBA steps in detail:

Some of these steps were covered under a different recent article as part of dealing with an error message, but here we’ll cover it as a task by itself (which it is).

Add New Storage

Once sysadmins have made the new storage as an ‘available storage’ to OS Cluster, it needs to be added as a new storage location to the SQL Cluster.

In FailOver cluster manager, go to Sql Server Resource Group for this SQL Cluster and right click for detailed options and choose “Add Storage” (see image below)

sqlcluster_addnewstorage_to_os_cluster

Once successful, go to Storage\Disks under in FailOver Cluster Manager to confirm the availability. See image below:

sqlcluster_addnewdrive

Configure Dependency

Adding the storage is an important step, and equally important step is adding the new drive to Sql Cluster Dependency Chain. Dependency Chain informs Sql Sever “how to act”, when any resource in the Cluster becomes unavailable. Some resources automatically trigger cluster failover to other node; some resources do not. This decision is made based on the configurations in Dependency Chain.

Example:

Critical: Data drive/LUN that has database files is critical for optimal availability of the Sql Cluster. So, if it becomes unavailable, failing over to other available nodes is imperative to keep the cluster available.

Non-Critical: In some scenarios, Sql Server Agent is not considered as Critical. So if it stops for some reason, Cluster will make multiple attempts to start it on the same node, but may not necessarily cause failover.

This is a business decision. All these “response actions” will be configured in Cluster settings.

Now, check the dependency report (before); We can see that new drive exists in Cluster, but is not yet added to the Dependency Chain.

SqlCluster_DependencyReport.PNG

To Configure Dependency Chain, go to the Sql Server Resource Group under Roles in FailOver Cluster Manager. See the image below for clarity:

Then go to the bottom section for this Resource Group, where all the individual resources that are part of this Resource Group are displayed.

Under “Other Resources“, right click on Sql Server Resource and choose properties.

do As show

sqlcluster_addnewstorage_add_to_dependency

In the “Sql Server Properties” window, we can see the existing resources already added to dependency chain logic.

SqlCluster_Dependency_Before.PNG

Now, go to the end of the logic list and choose “AND” for condition and pick the new Cluster Storage to be included. See image below for clarity:

SqlCluster_Dependency_After.PNG

After saving the settings, regenerate the Dependency Chain report. Now, we’ll see the new drive as part of the logic.

sqlcluster_dependencyreport_after

Add Database Data File to New Cluster Storage

Now, that the new drive is ready, we could easily add a new data file to the new location.

--
-- Add data file to new storage location
--
USE [master]
GO
ALTER DATABASE [SampleDB]
ADD FILE
	(
		  NAME 			= 	N'SampleDB_Data3'
		, FILENAME 		= 	N'U:\MSSQL\Data\SampleDB_Data3.NDF'
		, SIZE 			= 	3500 GB
		, FILEGROWTH 	= 	100 GB
		, MAXSIZE 		= 	3900 GB
	)
TO FILEGROUP [PRIMARY]
GO
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Given the usage of UNPIVOT is not that common as PIVOT (which itsel is more uncommon than other techniques in TSQL), the occurance of this error is even more remote. Luckly, we ran into it earlier:

Msg 8167, Level 16, State 1, Line 5
The type of column conflicts with the type of other columns specified in the UNPIVOT list.

Following is the UNPIVOT statement that threw the error. As you can see, on the surface, everything looks correct.

--
--	UNPIVOT syntax that throws error
--
SELECT ID, Metrics, Measures
FROM	(
	SELECT	  Daily_Health_Check_ID
			, Environment
			, DataCenter
	FROM SampleTable
	) AS Source
UNPIVOT
(
	Measures FOR Metrics IN (Environment, DataCenter)
) AS UnPVT
GO

Now let’s change the datatype and size of all the columns to match.

--
--	After converting all colums to the same datatype &amp; size
--
SELECT ID, Metrics, Measures
FROM	(
	SELECT	  Daily_Health_Check_ID
			, CONVERT(VARCHAR(20), Environment) AS [Environment]
			, CONVERT(VARCHAR(20), DataCenter) AS [DataCenter]
	FROM SampleTable
	) AS Source
UNPIVOT
(
	Measures FOR Metrics IN (Environment, DataCenter)
) AS UnPVT
GO

Voilà !! The key is to have uniformed data type & size of all columns in the source data set

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Years ago, there was a popular comment that Sql Server did not wide variety of functions; Once of them is the much needed IsNumeric functionality. Though such a function existed, there was a case where it resulted in incorrect results. So there were several custom functions like IsReallyNumeric or IsInteger, etc (my personal DBA library also had such custom functions)

Now, there is a powerful function that does more than just IsNumeric capability. It is called TRY_PARSE. Let’s take an example:

--
-- Sample code for TRY_PARSE
--
SELECT	  TRY_PARSE('ABCDEF' AS INT)		AS [INT_Test1]
	, TRY_PARSE('123456' AS INT)		AS [INT_Test2]
	, TRY_PARSE('ABCD-01-01' AS DATETIME)	AS [DateTime_Test1]
	, TRY_PARSE('2016-01-01' AS DATETIME)	AS [DateTime_Test2]
GO

TRY_PARSE.PNG

This function takes input in VARCHAR/NVARCHAR format and attempts to convert into the requested format. If the result is successful, the value is returned in requested data type. If not, NULL is returned. This makes it very useful in data processing logic flows; And makes it more useful function than the legacy (IsNumeric) functions.

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

Context:

In replication, as we all know, data/records from Publisher are copied to all Subscribers. This is carried out as Transactions and Commands from Publisher to each Subscriber. Let’s say application INSERTs 10 new rows into the database (Publisher) as part of some application transaction; Now the LogReader reads Transactional Log file and takes a note of these 10 records; Brings their details to Distributor.

Behind the Scenes:

From Distributor, they are transferred to each Subscriber by the Distribution Agent. In actuality, the Distribution Agent executes these INSERT commands at each subscriber; Upon success, it proceeds to execute the subsequent commands until all Subscribers are up to date.

Here is the beauty:

In Replication, the result of INSERT activity by the application, is carried out as “1 transaction with 10 commands” (one INSERT command for each record, all encompassing in one transaction). When we look closely, each INSERT command is carried out by Replication Stored Procedures located in each Subscriber (they are created as part of replication setup). Distributor will have entries with these stored procedures with appropriate parameters. Now Distribution agent will execute them at each subscriber.

Distributor will have entries like:

--
--
--
{CALL [dbo].[mtx_rplins_SampleTable] (758374661,1,N'SomeName1')}
{CALL [dbo].[mtx_rplins_SampleTable] (758374662,4,N'SomeName2')}
{CALL [dbo].[mtx_rplins_SampleTable] (758374663,5,N'SomeName3')}

 

replication_storedprocedures

Question

Now here is the question that we’ll discuss today. Why do we have different replication stored procedures calls? Not different procedures, but different procedure calls. We have dedicated replication stored procedures for each table; Each to carry out INSERT, UPDATE and DELETE. Like

  • sp_MSins_ : Handles INSERT operations for this table
  • sp_MSupd_ : Handles UPDATE operations for this table
  • sp_MSdel_ : Handles DELETE  operations for this table

But why do we have different procedure calls?

In the above image, we see entries like {CALL […..]}. There other procedure calls like CALL, SCALL, XCALL, MCALL, VCALL. What are these for?

  • CALL
    • This is the most popular or default call used in Replication.
    • It could be used for INSERT or UPDATE or DELETE
  • SCALL
    • This is used for UPDATEs only.
  • XCALL
    • Used for UPDATEs and DELETEs
  • MCALL
    • Used for UPDATEs only
  • VCALL
    • For internal use only.
    • Also used for updatable subscriptions

CALL:

This is the default option used for all activities a.k.a. UPDATE/DELETE/INSERT. As seen in the picture above, CALL will have a list of values provided for all columns of the table.

SCALL & MCALL:

Looks like some procedures are used exclusively for certain actions; Like MCALL & SCALL are dedicated for UPDATEs. But there is some difference between these too. SCALL only sends the columns that need updating; Where as MCALL sends all columns of the table (including the ones that are not changing).

XCALL:

Sends data for both before and after values for all columns of the table involved in UPDATE or DELETE. Obviously, in UPDATE, there are values for both before and after; But in DELETE, the after values is empty.

For more details, please refer to this excellent article.

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

For the last few months, on and off, there have been opportunities to run some interesting tests in our lab environment. this resulted in some good posts in the last few weeks. Adding to that tradition is another interesting topic Uniquifier.

Context:

Imagine a table with multiple records and a Clustered and bunch of NonClustered indexes. In the non-clustered indexes, the b-tree is structured based on the index keys and at the bottom of the tree, the leaf points back to clustered index using Clustering Key. Now imagine the same scenario with a non-unique clustered index, so there could be multiple records with same clustering key values. The dependent, non-clustered indexes now will need a way to uniquely identify between the identical looking entries. Enter Uniquifier column !!

Solution

An extra 4-byte column called uniquifier is added to all non-clustered indexes to uniquely distinguish between multiple index entries that result in pointing to the same clustering key.

Let’s take an example:

We’ll re-use some of the code from previous posts for this.

--
-- Create a dummy table to test DBCC IND
--
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Uniquifier_Test' AND type = 'U')
 DROP TABLE dbo.Uniquifier_Test
GO

CREATE TABLE dbo.Uniquifier_Test (
    ID INT NOT NULL DEFAULT (1)
  , Name VARCHAR(5) NOT NULL DEFAULT('aa')
)
GO

--
-- Create Clustered and NonClustered indexes
--
CREATE CLUSTERED INDEX CI_Uniquifier_Test_ID
    ON dbo.Uniquifier_Test(ID ASC)
GO

CREATE NONCLUSTERED INDEX nCI_Uniquifier_Test_Name
    ON dbo.Uniquifier_Test(Name ASC)
GO

--
-- Let's insert some dummy records
--
INSERT INTO dbo.Uniquifier_Test (ID, Name)
VALUES (1, 'aa')
     , (2, 'bb')
     , (3, 'cc')
     , (4, 'dd')
GO

SELECT *
FROM dbo.Uniquifier_Test
GO

Now, lets look at the contents of the non-clustered index pages. Fr more details on retrieving PageID and query page contents, please refer to the previous posts.

--
-- Retrieve PageID of nCI
--
DBCC IND (test, Uniquifier_Test, -1)
GO

--
-- Retrieve contents of nCI page
--
DBCC TRACEON (3604)
DBCC PAGE(test, 1, 34535, 3)
GO

As you can see, along with the nCI key column, Name, we also have the clustering key (ID) added to the nCI b-tree structure. Along with that there is a new column called UNIQUIFIER added to the non-clustered index pages. Since we did not add any duplicate values, the UNIQUIFIER column is set to zero.

Uniquifier (before duplicate entries)q

Uniquifier (before duplicate entries)

Now, lets add some duplicate entries.

--
-- Let's insert some duplicate records
--
INSERT INTO dbo.Uniquifier_Test (ID, Name)
VALUES (1, 'aa')
     , (2, 'bb')
GO

As you can see, where there are duplicate entries, the UNIQUIFIER column adds a unique value to distinguish between them. This incremental number is just within the same set of duplicate entries. Two duplicate rows for aa & 1 have the UNIQUIFIER value set to 0 and 1 respectively. For the next set of duplicates the incremental value starts over from 0. So it can accomodate a lot of duplicate entries.

Uniquifier after adding duplicate entries

Uniquifier after adding duplicate entries

So, the uniquifier column helps Sql Server distinguish between two identical entries in the non-clustered index.

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »