Join us via Email

Sequence – Long awaited feature

As per BOL (https://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx) : A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

This feature is really a good to have and this feature is same as Oracle’s sequence objects.  A sequence object generates sequence of unique numeric values as per specifications mentioned.

A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint. A sequence value is not effected by SQL server restart. Many people think sequence is similar to identity. But big difference is sequence object is independent of a table while identity columns are attached to a table.

Create Sequence

CREATE SEQUENCE TEMPSEQ AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5 — MAXIMUM IT CAN GO TO 5

NO CYCLE — DO NOT GO ABOVE 5

CACHE 2 — INCREMENT 2 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

Generate value from Sequence

SELECT NEXT VALUE FOR TEMPSEQ AS seq_no;

Error when Sequence reach to MAX value

Msg 11728, Level 16, State 1, Line 18

The sequence object ‘TEMPSEQ’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Alter Sequence to RESTART it from 1 or desired value

ALTER SEQUENCE TEMPSEQ RESTART WITH 1

 

Create Sequence with CYCLE mode, where value will be reset to MIN value when it reaches to MAX value

CREATE SEQUENCE TEMPSEQ_CYCLE AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5 — MAXIMUM IT CAN GO TO 5

CYCLE — RESET to 0 once reached to MAX VALUE 5

CACHE 2 — INCREMENT 2 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

Output of Sequence with CYCLE

SELECT NEXT VALUE FOR TEMPSEQ_CYCLE AS ID, NAME FROM SYS.OBJECTS

Sequence

Alter existing Sequence to set it to CYCLE mode

ALTER SEQUENCE TEMPSEQ CYCLE — RESET to 0 once reached to MAX VALUE 5

Use Sequence in Table as Default constraint.

Scenario: We need to generate token for each customer coming to meet him with HR & finance personal.

  1. Create Sequence

CREATE SEQUENCE TOKENNO AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5000 — MAXIMUM IT CAN GO TO 5000

NO CYCLE — RESET to 0 once reached to MAX VALUE 5000

CACHE 200 — INCREMENT 200 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

  1. Create Table with Sequence

CREATE TABLE HR_EXECUTIVE_MEETING

(TOKENNO INT DEFAULT (NEXT VALUE FOR TOKENNO), DESKNO INT)

 

CREATE TABLE FINANCE_EXECUTIVE_MEETING

(TOKENNO INT DEFAULT (NEXT VALUE FOR TOKENNO), DESKNO INT)

  1. Insert into table having Sequence

INSERT INTO HR_EXECUTIVE_MEETING(TOKENNO,DESKNO) VALUES(DEFAULT,22)

INSERT INTO FINANCE_EXECUTIVE_MEETING(TOKENNO,DESKNO) VALUES(DEFAULT,7)

  1. Check the table values

SELECT * FROM HR_EXECUTIVE_MEETING

SELECT * FROM FINANCE_EXECUTIVE_MEETING

Sequence

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

 

 

What is “SET STATISTICS TIME”?

When you turn on this option, it will display the number of milliseconds required to parse, compile, and execute each statement. By default, this option is disabled.

SET STATISTICS TIME ON;
GO
SELECT * FROM [HUMANRESOURCES].[EMPLOYEE]

Output: -
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 1 ms.

(290 row(s) affected)

SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 60 ms.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

How to use DTA (Database Tuning Advisor) with Plan Cache option?

SQL Server 2012 comes up with new workload option in DTA (Database Tuning Advisor). In addition, on existing options File or Table, Microsoft gives option to fetch workload from plan cache of SQL Server. In this case, the DTA will select the top 1,000 events from the plan cache based on total elapsed time of the query (that is, based on the total_elapsed_time column of the sys.dm_exec_query_stats DMV). This can be increased using –n option of Database Tuning Advisor command line utility.

dta -E -D DatabaseName -ip –n 2000-s SessionName1

Let’s try it: –

Step 1: Execute query and ensure Cache has plan for required database

–Clear Plan Cache

DBCC FREEPROCCACHE

GO

–Execute fresh statement

SELECT * FROM [HUMANRESOURCES].[EMPLOYEE] WHERE BUSINESSENTITYID > 10

GO

–Check Plan Cache has plan for ADVENTUREWORKS2012 database

SELECT * FROM SYS.DM_EXEC_CACHED_PLANS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)

WHERE DBID=DB_ID(‘ADVENTUREWORKS2012’)

Step 2: Open up DTA and under the “General” tab select

Workload: Plan Cache

Database for work load analysis: AdventureWorks2012

Database and tables to tune: relevant database and table you want to tune. In my case I selected all tables under AdventureWorks2012 database.

Step 3 – Leave other options as default. You can change them as per your requirements. Click on “Start Analysis” and it gives you recommendations as shown below.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

SQL Server 20082008 R22012 || Remove Node from failover cluster

Steps to remove node from failover cluster for SQL Server 2008 onwards :-

Note :-

a) Same process will be used to remove node from failover cluster

b) Same process will be used when you left with only one node, this will clear cluster group as well

c) Old process of uninstallation from Add and Remove program in Control Panel will not work

1) Open SQL Server installation Center > Go to Maintenance Option from Left > Click on “Remove Node from a SQL Server failover cluster” from right

2) Click on Run to start uninstallation process

3) Click “OK”. In case of any failure, you need to fix that before uninstallation

4) Click Next

5) Select Instance Name & click Next

6) Click Remove to start uninstallation

7) Uninstallation in progress

8) Uninstallation successfully completed.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

MSDB log file grown large – SLEEP_MSDBSTARTUP waittype

Issue: MSDB database log file is getting full very rapidly and became very huge. Currently, T-Log file is of 5GB whereas Data file is only 1GB for MSDB on server.

Findings: We found system owned open transaction in MSDB which is causing issue.

Transaction information for database ‘msdb’.

Oldest active transaction:

SPID (server process ID): 19s

UID (user ID) : -1

Name : CTraceEvDataQueue

LSN : (12968:15110:1)

Start time : May 7 2016 12:33:14:507PM

SID : 0x0

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When we try to find SPID 19 details from sys.sysprocesses, we found that session is causing SLEEP_MSDBSTARTUP waittype.

SLEEP_MSDBSTARTUP waittype Occurs when SQL Trace waits for the msdb database to complete startup. This is very rare watitype which we come across in our day to day production support.

Resolution: As we all know, we cannot kill SYSTEM session IDs, We need to restart SQL Services to get rid out of this problematic session.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Cumulative Update – 8 for SQL Server 2008 R2 Service Pack 2 Is Now Available !

The 8th cumulative update release for SQL Server 2008 R2 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 8 contains all the hotfixes released since the initial release of SQL Server 2008 R2 SP2.

Those who are facing severe issues with their environment, they can plan to test CU8 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU5 of SQL Server 2008 R2 SP2

· CU#8 KB Article: http://support.microsoft.com/kb/2871401

Previous Cumulative Update KB Articles:

· CU#7 KB Article: http://support.microsoft.com/kb/2844090

· CU#6 KB Article: http://support.microsoft.com/kb/2830140

· CU#5 KB Article: http://support.microsoft.com/kb/2797460

· CU#4 KB Article: http://support.microsoft.com/kb/2777358

· CU#3 KB Article: http://support.microsoft.com/kb/2754552

· CU#2 KB Article: http://support.microsoft.com/kb/2740411

· CU#1 KB Article: http://support.microsoft.com/kb/2720425

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

We are Back Again..

After lots of crisis in last month, We are back again.

Sorry for inconvenience caused !

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

MDW Data Collector – Collection Set jobs not working

ERROR :

Error Message 1 :

Executed as user: Domain\UserName. SSIS error. Component name: DFT – Upload collection snapshot, Code: -1071636372, Subcomponent: RFS – Read Current Upload Data [1], Description: String too long. The adapter read a string that was 50331648 bytes long, and expected a string no longer than 129 bytes, at offset 12901. This could indicate a damaged input file. The file shows a string length that is too large for the buffer column. . SSIS error. Component name: DFT – Upload collection snapshot, Code: -1073450952, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "RFS – Read Current Upload Data" (1) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5.

Error Message 2 :

Executed as user: : Domain\UserName. SSIS error. Component name: DFT – Collect Query 0, Code: -1071636383, Subcomponent: Raw File Destination [57], Description: The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. . SSIS error. Component name: DFT – Collect Query 0, Code: -1071636383, Subcomponent: Raw File Destination [48], Description: The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. .SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "Raw File Destination" (57) failed the pre-execute phase and returned error code 0xC0202061. . SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "Raw File Destination" (48) failed the pre-execute phase and returned error code 0xC0202061. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.

REASON : Cache files is having issue. When Collection jobs trying to upload cache files, job is getting failed.

SOLUTION :

1. Start SQL Server Management Studio, and connect to the instance of SQL Server where the error occurs.

2. Expand the Management folder, right-click Data Collection, and select Properties.

3. Cache directory displayedis the location of the Data Collector cache files. Go to step 5.

4. If a directory is not displayed for Cache directory, the default cache directory is the local temporary directory of the account that executes the collection set. This account may be the SQL Server Agent service account. For example, on Windows Server 2008, if the collection set was executed by an account that is named "SQLSERVICEACCOUNT," this account’s temporary directory is located in a path that resembles the following: C:\Users\ SQLSERVICEACCOUNT\AppData\Local\Temp.

5. Find all files that have a *.CACHE file name extension, and move the files to a different directory.

6. Now wait for next run of collection set jobs.

Reference Article : https://support.microsoft.com/en-us/kb/2019126

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Implementation of Data Compression – SQL Server

Data Compression

Data Compression is feature of Microsoft SQL Server to reduce the size of table on the basis of Duplicates, Null & Zeroes. It’s a process of reducing size of database & its objects by increasing CPU cycle and reducing I/O effort.

Types of Database Compression

· Row Compression

· Page Compression

You can download Data Compression SQL Server white Paper – https://mssqlfun.files.wordpress.com/2015/10/data-compression-sql-server-white-paper1.pdf

Implementation of Data Compression

Row Compression Page Compression
T-SQL Command

ALTER TABLE <TABLE_NAME> WITH (DATA_COMPRESSION=ROW) ALTER TABLE <TABLE_NAME> WITH (DATA_COMPRESSION=PAGE)
SSMS
Step 1 – Open Data Compression wizard. Right Click on Table > Storage > Manage Compression

Step 2 – Select Compression Type & you can click on calculate to check the impact

You can use below system store procedure to estimate the compression results
EXEC sp_estimate_data_compression_savings ‘Schema’,’Table_Name’,Null,Null,’Type_of_Compression’
Step 3 – Select Option if you want to run it immediately or later by generating script

Step 4 – Final Configuration window before you give final go ahead

Step 5 – Compression implemented successfully

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

MDW Data Collector – Collection Set jobs not working

ERROR :

Error Message 1 :

Executed as user: Domain\UserName. SSIS error. Component name: DFT – Upload collection snapshot, Code: -1071636372, Subcomponent: RFS – Read Current Upload Data [1], Description: String too long. The adapter read a string that was 50331648 bytes long, and expected a string no longer than 129 bytes, at offset 12901. This could indicate a damaged input file. The file shows a string length that is too large for the buffer column. . SSIS error. Component name: DFT – Upload collection snapshot, Code: -1073450952, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "RFS – Read Current Upload Data" (1) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5.

Error Message 2 :

Executed as user: : Domain\UserName. SSIS error. Component name: DFT – Collect Query 0, Code: -1071636383, Subcomponent: Raw File Destination [57], Description: The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. . SSIS error. Component name: DFT – Collect Query 0, Code: -1071636383, Subcomponent: Raw File Destination [48], Description: The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. .SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "Raw File Destination" (57) failed the pre-execute phase and returned error code 0xC0202061. . SSIS error. Component name: DFT – Collect Query 0, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "Raw File Destination" (48) failed the pre-execute phase and returned error code 0xC0202061. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. The step failed.

REASON : Cache files is having issue. When Collection jobs trying to upload cache files, job is getting failed.

SOLUTION :

1. Start SQL Server Management Studio, and connect to the instance of SQL Server where the error occurs.

2. Expand the Management folder, right-click Data Collection, and select Properties.

3. Cache directory displayedis the location of the Data Collector cache files. Go to step 5.

4. If a directory is not displayed for Cache directory, the default cache directory is the local temporary directory of the account that executes the collection set. This account may be the SQL Server Agent service account. For example, on Windows Server 2008, if the collection set was executed by an account that is named "SQLSERVICEACCOUNT," this account’s temporary directory is located in a path that resembles the following: C:\Users\ SQLSERVICEACCOUNT\AppData\Local\Temp.

5. Find all files that have a *.CACHE file name extension, and move the files to a different directory.

6. Now wait for next run of collection set jobs.

Reference Article : https://support.microsoft.com/en-us/kb/2019126

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

microsoftcommunitycontributor
LinkedIn Auto Publish Powered By : XYZScripts.com
%d bloggers like this: