Author Archives: rohitmssqlfun

DBCC CheckDB failed with error “DBCC could not obtain a lock on this object because the lock request timeout period was exceeded”

Error: Today, My DBCC checkdb job failed with below error.

Executed as user: USERNAME. Object ID 451805713 (object ‘dbo.AEETBL’): DBCC could not obtain a lock on this object because the lock request timeout period was exceeded. This object has been skipped and will not be processed. [SQLSTATE 42000] (Error 50000) DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Issue: When I see this error, I ask a question to myself “Does DBCC CHECKDB perform locks?” and the answer is NO. From SQL Server 2005 onwards, DBCC CheckDB works on a hidden database snapshot. A database snapshot is a read-only copy of the database. You can see some blocking on server or slowness for users due to snapshot I/O overhead but for sure no LOCKS.

Cases when DBCC CHECKDB take lock: –

1. If the TABLOCK option is specified when executing DBCC CheckDB
2. If hidden database snapshot is not created due to performance reasons or lack of disk space

So, It’s time to check my code and disk space. I found my DBCC CheckDB job is running with TABLOCK which cause this issue. This is not a common thing to use. If you are using TABLOCK you should have valid reasons like either slow disk system or low disk space.

Some people said they used TABLOCK to avoid I/O overhead of snapshot. For those, I need to mention “it’s higher to possess one thing in situ of nothing”. Microsoft suggests running DBCC Checkdb in odd business hours so that user performance is minimally impacted.

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://social.msdn.microsoft.com/Profile/rohitgarg

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

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

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

Performance is always a most prioritized task for any DBA and every DBA always prefer to go for a proactive approach and keep your system’s performance on top rather than reactive when it comes to addressing user complaints. SQL Server is a complex system and due to the continuous increasing size of data and multiple business logic, getting to the root of performance problems can be difficult. There are a lot of different factors that can impact SQL Server performance, varying from hardware and system configuration settings to poor T-SQL query practices.

Today, we will be discussing BIOS and OS Power Settings to SQL Server Performance boost. BIOS configurations play a very important role in server performance and need coordination with DBA & Server engineer for proper configuration.

BIOS Power Settings (C-State and P-State):

P-states (Performance State)

P-States used by the system for Reducing Power Consumption Without Impacting Performance. P-states are operational states. C-State (C0 – Active Mode) where the code is executed, in this C-state the P-States are relevant.

In C0 State when the code is executing, the operating system and CPU can optimize power consumption through different p-states (performance states). P-States are different frequencies to operate CPU, P0 is the highest frequency (with the highest voltage).

Core C-States – The Details

C-states are idle power saving states. In a P-state, the processor is still executing instructions, whereas, during a C-state (other than C0), the processor is idle, meaning that nothing is executing.

In P-State Cores are executing code, System just changes the CPU cores frequency as per requirements reduce voltage and energy consumption. But In C-State CPU Cores are idle and not executing anything. To simplify, If CPU is idle why to keep the unused circuits powered up and consuming energy. It should be good to Shut them down and save energy.

• C0 – Active Mode: Code is executed, in this state the P-States (see above) are also relevant.

• C1 – Auto Halt

• C1E – Auto halt, low frequency, low voltage

• C2 – Temporary state before C3. Memory path open

• C3 – L1/L2 caches flush, clocks off

• C6 – Save core states before shutdown and PLL off

• C7 – C6 + LLC may be flushed

• C8 – C7 + LLC must be flushed

C-States can be further divided into core C-states (CC-states), package C-states (PC-states) and logical C-states.

A CC-State (Core C-state) is a hardware C-state. One Processor has multiple cores and each of these cores has its own idle state. This is logical as one core might be idle while another is hard at work on a thread. So, the Idle state of one of those cores is C-state.

A PC-State (Processor C-state) is related to a core C-state. At some point, cores share resources, like L2 cache or the clock generators. The processor can only enter a PC-state if all cores of processor enter CC-State (Core C-state).

An LC-State (Logical C-state) is an OS’s view of the processors’ C-states. In Windows OS, a PC-State (Processor C-state is pretty much equivalent to a core C-state. OS manages when the core needs to move to CC-State.

How to Change C-State from BIOS (Hardware Level)?

We can disable C-State from BIOS and this will keep unused circuits powered up and keep performance boost.

How to Change OS Power Settings?

By default, most systems come with a balanced power setting, which means that the system will attempt to conserve power by cutting CPU processing speed. The balanced setting can reduce available processing power to the significant level and impact performance for a production SQL Server system. Balanced is not recommended for the server running SQL Server instance, we should change it to High Performance. To change OS power settings, go to Control Panel > Hardware > Power Option > Choose Power Plan > Select High Performance.

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://social.msdn.microsoft.com/Profile/rohitgarg

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

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

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

CEIP (Customer Experience Improvement Program) is somethings runs from ages and almost each vendor perform it to collect product usage data, issues faced and other information. Vendor uses this information for market expansion, bug fixing and new features.

But all this happen with user confirmation during or after installation. Microsoft is also doing it for it’s multiple products.

As data becomes more important, telemetry for software is potentially a data leakage and part of security and audit concerns. This becomes more concerns when we comes to database systems.

With SQL Server 2016, Microsoft is installing SQL Server Telemetry \ CEIP Services by default (most of the users are unware that they have installed CEIP). You cannot skip the installation of these services.

SQL Server 2016 Telemetry \ CEIP Services – You can see 3 services per instance separately for DB engine, SSAS & SSIS features installed.

SQLTELEMETRY$instancename

SSASTELEMETRY$instancename

SSISTELEMETRY130$instancename

How to disable SQL Server 2016 Telemetry \ CEIP Services?

NOTE : DO NOT REMOVE THESE SERVICES, AS THIS CAN IMPACT SQL SERVER SERVICE PACK INSTALLATION IN FUTURE.

1. Stop and disable all SQL Server Telemetry available on your machine. No. of services can differ on basis of features or no. Of instances installed.

You can use PowerShell script to check SQL Telemetry services status.

Get-Service |? name -Like "SQLTELEMETRY*" | select -property name,starttype,status

Get-Service |? name -Like "SSASTELEMETRY*" | select -property name,starttype,status

Get-Service |? name -Like "SSISTELEMETRY*" | select -property name,starttype,status

2. Disable CEIP registry keys to 0

Two parameters have to be set to 0:

• CustomerFeedback
• EnableErrorReporting

1. The first registry key is HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130\
   
2. The second registry key is HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\
   
3. The other registry keys are per instance and per services(Engine, SSAS and SSRS):
   HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.<instance>\CPE\
   
4. HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.<instance>\CPE\
   
5. HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.<instance>\CPE\
   

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://social.msdn.microsoft.com/Profile/rohitgarg

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

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

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

Creating a System-Versioned Temporal Table – There are three ways to create a system-versioned temporal table with regards to how the history table is specified:

1) Creating a temporal table with an anonymous history table

Creating a temporal table with an anonymous name. This is easiest way to create it when you do not concern about history table name. In the example below, a new table is created with system-versioning enabled and name is automatically decide by SQL Server.

· A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END

· The PERIOD columns are always assumed to be non-nullable, even if nullability is not specified. If the PERIOD columns are explicitly defined as nullable, the CREATE TABLE statement will fail.

· The history table must always be schema-aligned with the current or temporal table, in terms of number of columns, column names, ordering and data types.

· An anonymous history table is automatically created in the same schema as current or temporal table.

· The anonymous history table name has the following format: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]. Suffix is optional and it will be added only if the first part of the table name is not unique.

· The history table is created as a rowstore table. PAGE compression is applied if possible, otherwise the history table will be uncompressed. For example, some table configurations, such as SPARSE columns, do not allow compression.

· A default clustered index is created for the history table with an auto-generated name in format IX_<history_table_name>. The clustered index contains the PERIOD columns (end, start).

2) Creating a temporal table with a default history table

Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on the system to create the history table with the default configuration. In the example below, a new table is created with system-versioning enabled with the name of the history table explicitly defined.

· The schema name is mandatory for the HISTORY_TABLE parameter.

· If the specified schema does not exist, the CREATE TABLE statement will fail.

· If the table specified by the HISTORY_TABLE parameter already exists, it will be validated against the newly created temporal table in terms of schema consistency and temporal data consistency. If you specify an invalid history table, the CREATE TABLE statement will fail.

Msg 2714, Level 16, State 6, Line 1

There is already an object named ‘DEPARTMENTHISTORY_ALREADYTHERE’ in the database.

Msg 13523, Level 16, State 1, Line 3

Setting SYSTEM_VERSIONING to ON failed because table ‘SQL2016_FeatureTest.dbo.Department’ has 6 columns and table ‘SQL2016_FeatureTest.dbo.DepartmentHistory_AlreadyThere’ has 1 columns.

3) Creating a temporal table with a user-defined history table

Creating a temporal table with user-defined history table is a convenient option when the user wants to specify a history table with specific storage options and additional indexes. In the example below, a user-defined history table is created with a schema that is aligned with the temporal table that will be created. To this user-defined history table, a clustered columnstore index and additional non clustered rowstore (B-tree) index is created for point lookups. After this user-defined history table is created, the system-versioned temporal table is created specifying the user-defined history table as the default history table.

Action	Temporal table with an anonymous history table	Temporal table with a default history table	Temporal table with a user-defined history table
Create Table with Temporal Table	CREATE TABLE Department_anonymous ( DeptID int NOT NULL PRIMARY KEY CLUSTERED , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) ) WITH (SYSTEM_VERSIONING = ON)	CREATE TABLE Department_WithHistoryTable ( DeptID int NOT NULL PRIMARY KEY CLUSTERED , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory) ) ;	CREATE TABLE DepartmentHistory_UserDefined ( DeptID int NOT NULL , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , SysStartTime datetime2 NOT NULL , SysEndTime datetime2 NOT NULL ); GO CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory_UserDefined; GO CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS ON DepartmentHistory_UserDefined (SysEndTime, SysStartTime, DeptID); GO CREATE TABLE Department_UserDefined ( DeptID int NOT NULL PRIMARY KEY CLUSTERED , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory_UserDefined)) ;
Temporal Table is created along with main table:
Insert data into temporal table :	INSERT INTO DEPARTMENT_ANONYMOUS(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (111,’FINANCE’,502,101) INSERT INTO DEPARTMENT_ANONYMOUS(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM DEPARTMENT_ANONYMOUS A WHERE A.DEPTID =117	INSERT INTO DEPARTMENT_WITHHISTORYTABLE(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (111,’FINANCE’,502,101) INSERT INTO DEPARTMENT_WITHHISTORYTABLE(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM DEPARTMENT_WITHHISTORYTABLE A WHERE A.DEPTID =117	INSERT INTO Department_UserDefined(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (111,’FINANCE’,502,101) INSERT INTO Department_UserDefined(DEPTID,DEPTNAME,MANAGERID,PARENTDEPTID) VALUES (117,’WALFARE’,522,111) UPDATE A SET A.MANAGERID= 557 FROM Department_UserDefined A WHERE A.DEPTID =117

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://social.msdn.microsoft.com/Profile/rohitgarg

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

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

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