Home » Posts tagged 'SQL 2014'

Tag Archives: SQL 2014

How Row versioning impact tempDB ?

Scenario: DBA found that tempdDBdatabase usage is getting high and most of the size is consumed by row versioning. DBA raised the issue with application team running that query. Once Application team close the session, tempDB usage comes normal.

Question: Application tea raise concern “How can a select statement on table consume huge amount of resources in tempDB?”

Answer:

As per BOL (https://technet.microsoft.com/en-us/library/ms175492%28v=sql.105%29.aspx) : Row versioning-based isolation levels (RCSI – Read Committed Snapshot Isolation) reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. This increases system performance by reducing the resources used to manage locks. Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions. Row versioning-based isolation levels increase the resources needed by data modifications.

While updating the row in a table or index, the new row is marked with a value called the transaction sequence number (XSN), XSN is unique number and keeps updating with upcoming new command \ transactions. When updating a row, New version is generated. If DB is already having previous version of this row, previous version of the row is stored in the version store and the new version of the row contains a pointer to the old version of the row in the version store. New row version stores corresponding XSN value for reflecting the time the row was modified.

All version stores are linked in a chain. SQL Server keep running clean up task to remove old versions which are not in use. As long as a transaction is open, all versions of rows that have been modified by that transaction must be kept in the version store. This is the reason due to which long running open transaction can cause huge tempDB row versioning space.

Here, Row X is getting updated by multiple transactions (Tv,Tx,Ty,Tz) and each time new version store is created. When DB engine tries to clear old unused version stores, it found open transaction using version X. DB engine not able to clear any row version from version store after version X. Due to which TempDB size keeps increasing due to row versioning.

–Check Size of tempDB consumed by Version Store

SELECT SUM(VERSION_STORE_RESERVED_PAGE_COUNT) AS [VERSION STORE PAGES USED],

(SUM(VERSION_STORE_RESERVED_PAGE_COUNT)*1.0/128) AS [VERSION STORE SPACE IN MB],

SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) AS [INTERNAL OBJECT PAGES USED],

(SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [INTERNAL OBJECT SPACE IN MB],

SUM(USER_OBJECT_RESERVED_PAGE_COUNT) AS [USER OBJECT PAGES USED],

(SUM(USER_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [USER OBJECT SPACE IN MB],

SUM(UNALLOCATED_EXTENT_PAGE_COUNT) AS [FREE PAGES],

(SUM(UNALLOCATED_EXTENT_PAGE_COUNT)*1.0/128) AS [FREE SPACE IN MB]

FROM SYS.DM_DB_FILE_SPACE_USAGE;

–Find session using version store

SELECT A.*,B.KPID,B.BLOCKED,B.LASTWAITTYPE,B.WAITRESOURCE,B.DBID,B.CPU,B.PHYSICAL_IO,B.MEMUSAGE,B.LOGIN_TIME,B.LAST_BATCH,

B.OPEN_TRAN,B.STATUS,B.HOSTNAME,B.PROGRAM_NAME,B.CMD,B.LOGINAME,REQUEST_ID

FROM SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS A

INNER JOIN SYS.SYSPROCESSES B

ON A.SESSION_ID = B.SPID

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.asp

Error while loading Excel data into database

Issue: I am trying to load data from Excel 97 – 2003 sheet into a database table. I have written a simple SSIS package to do that, but unfortunately, it fails with error: –

 

Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:02:37 AM  Error: 2016-12-19 02:02:37.91     Code: 0xC0209303     Source: ExcelImpot Connection manager “SourceConnectionExcel”     Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.  An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0x80040154  Description: “Class not registered”.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC001002B     Source: MAPTImpot Connection manager “SourceConnectionExcel”     Description: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC020801C     Source: Data Flow Task 1 Source – Sheet1$ [38]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “SourceConnectionExcel” failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: Source – Sheet1$ failed validation and returned error code 0xC020801C.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:02:37 AM  Finished: 2:02:37 AM  Elapsed:  0.733 seconds.  The package execution failed.  The step failed.

Cause: This error occurs when you try to execute 32-bit environment package in 64-bit system.

Solution 1: If you have project file of SSIS package or If you are running package other than SQL Agent jobs like calling from CMD.

Step 1: Navigate to Project-> [PROJECT_NAME] Properties.

Step 2: Navigate to “Debugging” option from left panel and from Right panel change Run64BitRuntime value to false.

Solution 2: If you need to execute package from SQL agent job only then you have readymade option available. Go to Job step and check below option.

 

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.asp

SQL Server Cluster LooksAlive and IsAlive Check

Windows Cluster Service use the “heartbeat” process to check whether each node is alive at both the OS level and SQL Server level. Heartbeat signals works on UDP.

Microsoft Windows Cluster service perform 2 health checks to perform node & resource availability check.

LooksAlive Health Check

1) LooksAlive is a quick lightweight health check

2) Run at interval of 5 Seconds

3) Does not impact performance but does not perform a thorough check

4) The check will succeed if the service appears to be running even though it might not be operational

5) If Fails calls “IsAlive” health check

6) Polling interval can be changed by adjusting LooksAlivePollInterval property of Cluster service

IsAlive Health Check

1) Run at interval of each 60 Second

2) Perform more detail check then LooksAlive

3) Run @@SERVERNAME to ensure that SQL Server is responding to queries

4) Does not ensure that all user databases are operational

5) Retired 5 times & then try to reconnect SQL Server

6) If all 5 retry fails, the SQL Server resource fails & Windows Cluster service will try to bring it online on other node as per configuration

7) Polling interval can be changed by adjusting IsAlivePollInterval property of Cluster service

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

Creation & Deletion of Database Snapshot by SQL Agent Job

Some days back, I received one query on my facebook page that user want to create Database Snapshot frequently & want to delete the oldest with same frequency. Quite interesting scenario. I did not go in deep why he has such requirement but I am clear that It can be done very easily.

So let’s do it. I have setup 2 scripts one for creation of snapshot & another is for deletion of snapshot. We can schedule a SQL agent jobs with 2 steps using these scripts.

Step 1 :-

DECLARE @CREATE_SS VARCHAR(MAX)

DECLARE @DT VARCHAR(100)

SET @DT = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),’-‘,’_’),’ ‘,’_’),’:’,’_’)

SET @CREATE_SS =

‘CREATE DATABASE TEST_’+@DT+’ ON

( NAME = TEST, FILENAME =

”C:\TEST_’+@DT+’.SS” )

AS SNAPSHOT OF TEST’

EXEC (@CREATE_SS)

Step 2 :-

IF(SELECT COUNT(*) FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’))>1

BEGIN

DECLARE @DROP_SS VARCHAR(MAX)

DECLARE @SS_NAME VARCHAR(100)

SELECT TOP 1 @SS_NAME = NAME FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’)

ORDER BY CREATE_DATE ASC

SET @DROP_SS = ‘DROP DATABASE ‘+ @SS_NAME

EXEC (@DROP_SS)

END

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

Renaming or Changing SQL Server Cluster Instance

Renaming or Changing SQL Server cluster instance process is bit different from renaming a stand-alone instance.

Refer : Rename or Change SQL Server Standalone Default Instance http://mssqlfun.com/2014/06/30/rename-or-change-sql-server-standalone-default-instance/)

Refer : Rename or Change SQL Server Standalone Named Instance (http://mssqlfun.com/2014/07/29/rename-or-change-sql-server-standalone-named-instance/)

The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). You can change the name of the virtual server, you cannot change the instance name like in the case of standalone named instance. You can change a virtual SQL server named SQLServerinstance1 to some other name, such as SQLServerNewinstance1, but the instance part of the name, instance1, will remain unchanged.

Rename a virtual server :-

1. Using Cluster Administrator, change the SQL Network Name or SQL Server DNS Name to the new name.

2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.

3. Bring the SQL Server resource back online.

Verify the Renaming Operation :-

1) Using @@ServerName

2) Using sys.servers

Minimize network propagation delay :-

ipconfig /flushdns

ipconfig /registerdns

nbtstat –RR

You are done ! You should ask your application teams to reconnect with new Name or if they are using IP address then no changes required.

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

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

%d bloggers like this: