Hi Friends,
This is my 20th blog on SQL Server Trace Flag 1262 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.
We know that SQL Server uses its own Operating system for scheduling and processing that is known as SQLOS. Some people called it as mini OS. How many schedulers SQL Server will use it depends on the factor ‘How many CPUs have been assigned to SQL Server ’. These assigned schedulers are then divided into three logical categories Hidden, Visible and Visible DAC. Hidden Online schedulers will be used for SQL Server internal processing. Visible Online Schedulers will be used for user request processing. Visible Online DAC is used to connect under Dedicated Administrator Connection which plays a role of life saver for DBAs.
If everything is running fine then we can say that all is well from SQL but there may be a situation when some of the SQL Server schedulers are not working fine or we can say stuck somewhere (may be due to any bug). We know that schedulers are very important part of SQL Server processing. So, SQL Server monitors the health of its schedulers by using an internal monitoring mechanism that is known as SchedulerMonitor. In technical terms we can say that SchedulerMonitor is an internal thread associated with each scheduling node to monitor the health of schedulers. This monitor thread wakes up after every 5 seconds interval to monitor the health of schedulers on its scheduling node.
SchedulerMonitor internally works in two phases:
1- Detection Phase: here it performs only condition checks or we can say below basic check.
- the scheduler is not in an idle state
- and the number of yields has not changed
- and the worker is not performing an action such as taking a mini-dump
If above condition evaluates to true then worker will be considered as nonyielding.
2- Extended Reporting Phase: if SQL Server finds out nonyielding worker in Detection phase then after that this extended reporting phase will invoke. Under this phase SQL Server records data for sys.dm_os_ring_buffers based on below condition.
If the nonyield situation (Elapsed wall clock time) >= nonyield threshold i.e. 10 seconds
If above condition went true then only data for sys.dm_os_ring_buffers will be collected. We know that SchedulerMonitor runs after every 5 seconds and from the above formula threshold value is 10 seconds. So we can say that there will be approx 15 seconds time between the time of the last yield on the scheduler and the time when threshold condition check will be true.
Once the threshold check becomes true, the resource boundary check will be invoked. The worker will be considered nonyielding if either of the following conditions met:
- If the workers (tracked kernel + user mode time) <= 5 percent
- If the workers (tracked kernel + user mode time) >= 40 percent
- and the SQL Server process is able to obtain sufficient processor time
SQL Server generated error messages 17883, if any single scheduler is suffering from nonyield problem or went unresponsive. These errors reported interval is 60 seconds. Once nonyield situation reached to 60 seconds then a mini dump will be generated for 17883. Once a 17883 mini dump captured, no further mini dump will be captured for 17883. However, 17883 error messages will be reported continuously.
All the above information was required to explain the use of today’s sql server trace flag 1262. If you want to generate 17883 mini dumps for each occurrence of 17883 then you should use trace flag 1262. This can provide you the more information for troubleshooting the nonyield issue. But be careful while using this trace flag because for each mini dump generation it will increase the IO.
PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.
Researches have demonstrated that the medication is efficient against pain. Moreover, simultaneous use of naproxen and https://tramadolhealth.com made it possible to decrease the dosage of the latter.
Reference: Click Here.
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
