Home » 2019 » January

Monthly Archives: January 2019

Error : DBCC could not obtain a lock on this object because the lock request timeout period was exceeded

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

BIOS and OS Power Settings to enhance SQL Server Performance

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

%d bloggers like this: