'%.*ls' does not contain an identity column

Why do we get this Err ?

Are you trying to get an IDENTITY property Info of a Table? - But, The table actually doesn't have any IDENTITY on it!

Ok.

Let me create a new table without any IDENTITY on it

CREATE TABLE dbo.Table1

(

Id Int, Name Varchar(10)

)

GO

Now, I am trying to get the IDENTITY Info of the Table

DBCC CHECKIDENT('dbo.Table1')

Msg 7997, Level 16, State 1, Line 4

'Table1' does not contain an identity column.

Yeah! Actually, we don't have an IDENTITY on this table. Right!

The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d. Dropping and re-creating the index may resolve this; otherwise, use another clustering key

Many of you may have encountered such a critical situation like some of the Table was working fine until just last minute without any Err and suddenly you may have faced an Err shown below so called Error 666

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d.
Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

What does mean ?

According to MSDN - "Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following"

• Can be used for frequently used queries.
• Provide a high degree of uniqueness
• Can be used in range queries

If the clustered index is not created with the UNIQUE property forced to it!. So what ?

Here is the internal thing happens - "The Database Engine automatically adds a 4-byte UNIQUIFIER column to the table. When it is required, the Database Engine automatically adds a UNIQUIFIER value to a row to make each key unique. The value starts with 0"

But, This column and it's values are used internally and cannot be seen or accessed by the users by accessing the table just like that.

You may ask now - Why do we need to worry about this situation ?

Let's have some scenario

USE [MyDatabase1]

GO

/*Table created with TWO column*/

CREATE TABLE MyTable1

(

Id INT, Names VARCHAR(20) 

)

GO

/*Clustered Non-Unique key created on Id column*/

CREATE CLUSTERED INDEX CI_Id ON MyTable1(Id)

GO

/*2 Records inserted*/

INSERT MyTable1

SELECT 1,'SQL' UNION ALL

SELECT 2,'SERVER'

GO

/*Checking the Page allocations of the Table*/

SELECT %%Lockres%% [KeyHashValue], sys.fn_physlocformatter(%%Physloc%%) [File:Page:Slot], * FROM MyTable1

/*Checking the specific page (Page ID: 232)*/

DBCC TRACEON(3604)

DBCC PAGE('MyDatabase1',1,232,3) WITH TABLERESULTS

But, It has lot of other Information. Right. let's simplify the data and remove rest of them

/*Creating a Temp table to have the about result. So, That we can filter-it out as needed*/

CREATE TABLE #Info(ParentObject VARCHAR(50), [Object] VARCHAR(100), Field VARCHAR(50), Value VARCHAR(1000))

INSERT #Info

EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

/*We don't need Header and Buffer Info for now*/

DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT * FROM #Info

As you can see above, There is one more Internal column has been added "UNIQUIFIER" to force the Internal Uniqueness for the Clustered Key.

And, Both the "UNIQUIFIER" record has a value as "0". Because, There is No duplicate on Clustered key column "Id". So, There is no need for Nth duplicate indication on "UNIQUIFIER" column. Right!

/*Insert 4 More records with 2 Duplicate Data on "Id" column*/

INSERT MyTable1

SELECT 3,'SQL SERVER' UNION ALL

SELECT 3,'UNIQUIFIER' UNION ALL

SELECT 3,'CLUSTERED' UNION ALL

SELECT 4,'NON-UNIQUE'

GO

TRUNCATE TABLE #Info

INSERT #Info

EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT * FROM #Info WHERE Field IN ('UNIQUIFIER','Id','Names','KeyHashValue')

Can you guess ? What just happening ? Yes! - You are right!

We have inserted 4 New records and 3 of them duplicated (Id as "3"). Right ? So, The system wants to force the Unique value on "UNIQUIFIER" internal column. So, The "UNIQUIFIER" column have Nth duplicate indication!!

/*Deleted the records which Id column has "3"*/

DELETE MyTable1 WHERE Id = 3

GO

/*Inserted 3 records again*/

INSERT MyTable1

SELECT 3,'SQL SERVER' UNION ALL

SELECT 3,'UNIQUIFIER' UNION ALL

SELECT 3,'CLUSTERED'

GO

So, Can you guess - How many physical records will be there ? 

Let's explore here

SELECT * FROM MyTable1

GO

Yeah! We have 6 Records as expected.

What would be the "UNIQUIFIER" internal value the system have ?

TRUNCATE TABLE #Info

INSERT #Info

EXEC('DBCC PAGE(''MyDatabase1'',1,232,3) WITH TABLERESULTS')

DELETE #Info WHERE ParentObject in('BUFFER:','PAGE HEADER:')

SELECT * FROM #Info WHERE Field IN ('UNIQUIFIER','Id','Names','KeyHashValue')

Yes! We just have 6 Records (Slot 0 to Slot 5) only. But, Did you see the "UNIQUIFIER" Internal column value ?

It has been Increased! Because, we already had 2 duplicate detected on "Id" column. Now, It has increased since than. It's not Reset anymore even when records deleted. That's the reason why the Internal column getting Increased even If the actual duplicate records removed/deleted!

So, I hope you would have guessed, What would happen If these kind of duplicate records DELETED and INSERTED multiple time with huge volume (Millions of transactions), The "UNIQUIFIER" internal column still increase along with the DELETED duplicated data and It's not RESET anymore as I said earlier.

Ok. Guess what ?

We will get such an Error, If we perform such a transaction as explained above without resetting the UNIQUIFIER, sometimes we can reach it's upper limit "2147483648"

We will get such an Error If we INSERT 1 more duplicate record...

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d.
Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

The goal of this post is to give you an insight on how the UNIQUIFIER works and allow you to manually check for potential issue in your environment and avoiding such an error 666 and over come the situation

How to reset the value and unblock the situation ?

1.As per the Error suggests DROP and CREATE the Index
DROP INDEX MyTable1.CI_Id
GO
CREATE CLUSTERED INDEX CI_Id ON MyTable1(Id)
GO

Note: Rebuild Index will not help us

Check with the New page ID allocated and see whether the "UNIQUIFIER" value has been reset!

2. If still the Issue not been resolved

• Create a new table with same structure (MyTable1_New)
• Load the Data from an existing table (Using Import wizard Or Bulk Insert)
• Create the Clustered Index
• Archive/Remove the Old Table once make sure everything is resolved 

Thanks for your time!

Tempdb should be some % size of Large Database in SQL Instance ?

It doesn't seem like that!!!

There is NO any arithmetic formula to calculate Tempdb Size. Yes ?

Do we have any ?

Yes. still we can calculate/figure out by performing the following things when storing Intermediate results

1. Memory Spill - Causing by Hash Or Sort operation
2. Rebuilding Index along with SORT_IN_TEMPDB Option
3. DBCC CHECKDB on Larg Database
4. Using Temp (#/##) Table
5. Using multiple aggregations with huge data

Tempdb doesn't behave like User Databases (Say suppose, If the User database grows up to 500 GB and It'll remain same even the SQL Instance restarts. Right ?

But, The Tempdb will be recreated with the Size to whatever the size it was last set to!!!

To avoid Memory Spill:
- Omit the ORDER BY clause if you do not need the result set to be ordered.
- If ORDER BY is required, eliminate the column that participates in the multiple range scans from the ORDER BY clause.
- Using an index hint, force the optimizer to use a different access path on the table in question.
- Rewrite the query to produce a different query execution plan.
- Force serial execution of the query by adding the MAXDOP = 1 option to the end of the query or index operation

optimize for ad hoc workloads - Configuration Option

Normally, SQL Server compile, generate and stores the Plan for reuse. The process continues for all the following object types  

• Proc
• Prepared
• Adhoc
• ReplProc
• Trigger
• View
• Default
• UsrTab
• SysTab
• Check
• Rule

For the every type of queries the process generates and stores the plan and every plan consume some Size.

Here, we going to see about the Transact-SQL statement also referred to as "Adhoc" query.

USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

The following query to get the Stored Plan related Info for the above query
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

The plan consumes 72 KB (73728 Bytes) and the query uses 1st time. But, not sure whether the same plan going to be re-used next time. So, do we need to store the entire plan with consumes 72 KB at first time itself ?

No.

So, We have an option "optimize for ad hoc workloads" is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc queries/batches.

SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE WITH OVERRIDE

Done. 

As long as the above option is ON. All the Adhoc queries' entire plan are not going to be stored in Plan cache. But, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time.

To Remove the specific plan from the cache
DBCC FREEPROCCACHE(0x06000A004230791470B5CC340100000001000000000000000000000000000000000000000000000000000000)
GO

Run the query at first time
USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

The following query to get the Stored Plan related Info for the above query
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

Yes. Now, we got only 352 Bytes (small compiled plan stub) for the plan at first compile/use.

When run the same query again second time

Run the query at second time
USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

Check whether the plan re-generated and stored entirely in Plan Cache
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

Now, New plan generated (See the use count column as 1) and stored the entire plan in plan cache (See the Plan Size(Bytes)).

How can I Truncate/Clear SQL Server Error Log ?

We may have faced an issue like SQL Server error log is getting filled with entries...!

So, We just want to clear the error log entries.. right ?

Oh.. Wait!

First, Tell me that How to read the entries from my SQL Server Error Log ?  (from Active/current file) then we can move into further ...:)

Read SQL Server Error log (Active file)
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc'

Ok. Then how to write a new entry into the SQL server Error log ?

Log an entry into SQL Server Error log
RAISERROR('SQL Server Buddy',16,1) WITH LOG

Now,  Can we go further to clear the log entry ? YES

we can use either...

To clear SQL Server error log
DBCC errorlog
--or
EXEC sp_cycle_errorlog

NULL = NULL

One of my friend had discussion with me some days back that NULL is not equal to another NULL...! 

Is that TRUE ?

I said that "NO, IT IS NOT ALWAYS...!"

What that means.. "NOT ALWAYS" ?

In SQL Server, we have SET option called "ANSI_NULLS"

Is there any way to identify that what are all the options have been set to ON ?

DBCC USEROPTIONS
GO

Its a session specific option. So, It will list out What are all the user options have been set to ON along with some other options!

OK.

NULL is NOT EQUAL to another NULL. How ? 

When ANSI_NULLS is set to ON

SET ANSI_NULLS ON
GO

IF (NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT 'NOT EQUAL'
GO

Result : NOT EQUAL

 
NULL is EQUAL to another NULL. How ? 

When ANSI_NULLS is set to OFF

SET ANSI_NULLS OFF
GO

IF (NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT 'NOT EQUAL'
GO

Result : EQUAL

So, NULL is not equal to another NULL - NOT ALWAYS, It depends on ANSI_NULLS option!