Feeds:
Posts
Comments

Archive for the ‘New Features’ Category

Quick one today:

A few days ago, as part of installing Sql Server 2016, we needed to install R Service on a lab machine. During installation, we learnt that the Sql Server DVD/media does not have necessary CAB files to perform installation. We ran into this situation:

Setup was unable to contact the download server. 
Provide the location of the Microsoft R Open and Microsoft R Server 
installation files and click 'Next'. 
The installation files can be downloaded from:

https://go.microsoft.com/fwlink/?LinkId=824879&lcid=1033
 https://go.microsoft.com/fwlink/?LinkId=824881&lcid=1033

Sql2016_R_Install

Resolution:

Since our lab machines do not have internet access, these CAB files could not be downloaded by the install wizard. Using the URL’s, we could download the necessary install CAB files (2 files) to a local laptop and then copy the files over to the lab server.

Then point the install wizard to this location (local to the server) where both the CAB files are present. See image below:

Sql2016_R_Install_with_CAB_Files

 

Once the CAB files path is identified, rest of the installation progresses smoothly. See image below with R Service installed.

Sql2016_R_Install_Successful

Hope this helps,
_Sqltimes

Read Full Post »

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 »

Quick one today:

Earlier today, when installing Sql Server 2016, ran into this interesting error:

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

oraclejre_error

Looks like JRE (Java Runtime Engine) is needed for Polybase application to run. So, lets get it from Oracle website here. Download from the JRE section (see image below).

oraclejre_error2

In the new page, “Accept License Agreement” & download the version pertinent to your environment.

oraclejre_error3

After installing JRE, the checks ran successfully for install.

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:

MSDTC is one of the popular software components that is on all Windows systems. It is one of the Windows  Operating System components that Sql Server relies on it to perform some crucial tasks (when needed).

What does it do?

MSDTC, Microsoft Distributed Transaction Coordinator, is essentially, as name suggests, a coordinator/manager to handle transactions that are distributed over multiple machines. Let’s say we start a transaction, where one of the steps includes querying data from a different Sql Server instance on a different physical machine; MSDTC comes into action with these specific tasks that need transaction coordination across different physical machines. It executes the section of code that is supposed to run on remote machines and brings back the results to local Sql instance. In this process, if any issue were to occur, on the remote machine that results in rollback, MSDTC makes sure the original transaction on this machine also rolls-back safely.

How does it do?

MSDTC comes with necessary Operating System controls and memory structures to carry out these operations independent of the Sql Instances, while keeping integrity of the transaction across the multiple physical Sql machines a.k.a. the complete two-phase distributed commit protocol and the recovery of distributed transactions.

Where does Sql Server use it?

The key point here is that these need to be Sql Instances on different physical machines. Queries that request data across different instances on the same physical box do not go through MSDTC.

MSDTC is used by query activities like

  • Linked Servers
  • OPENROWSET
  • OPENQUERY
  • OPENDATASOURCE
  • RPC (Remote Procedure Calls)
  • Ones with
    • BEGIN DISTRIBUTED TRANSACTION
  • etc…

So, every time we run SQL queries that utilize above techniques, they rely on MSDTC to carry out operation while maintaining transaction integrity.

Who else uses it?

MSDTC is an Operating System resource that is used by applications other than Sql Server, to perform any distributed transaction activities; Like eXtended Architecture applications.

Is MSDTC required?

MSDTC is not required for Sql Server installation or operation. If you are only going to use Database Engine, then it is not required or used. If your Sql uses any of the above mentioned query techniques (Linked Server, OPENQUERY, etc), or SSIS or Workstation Components then MSDTC is required.

If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances.

What about Sql Cluster?

Same rules as above apply to Sql Clusters as well with one additional rule. If you have two instances on the same machine (that are clustered across different physical machines), then you’ll need MSDTC. Since the Cluster could failover to remote machine at anytime.

Let’s take an example:

Let’s say Instance1 is on physical machines A & B, with B as active node. Instance2 is on machines B & C, with B as active node. A query going from Instance1 to Instance2 will need MSDTC (even if both the instances are active on the same physical machine B at that given point in time.).

This is because, there is no guarantee that they will remain on the same physical machine at any given time; They might failover to other machines, resulting in instances being on physically different machines. So MSDTC is required (when distributed operations are performed).

Also the recent Sql Server versions do not required MSDTC during Sql Server installations.

Other points in a Clustered Environment

We could have multiple instances of MSDTC as different clustered resource (along with default MSDTC resource).

In scenario with multiple MSDTC, we could configure each Sql Cluster resource to have a dedicated MSDTC instance. If such mapping does not exist, it automatically falls back to use the default MSDTC resource.

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 »