Hi Friends,
This is my 14th blog on SQL Server Trace Flag 1118 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.
Trace Flag 1118 is used to enable the dedicated extent allocation for objects instead of allocating pages from mixed extents. Here we can say trace flag 1118 completely removes single data page allocation from sql server. Some of you who don’t know the internal working on SQL Server may not understand the above statement clearly. So let me explain the default behavior first.
By default in SQL Server, when we create a table and insert data then first eight pages will be allocated from mixed extents i.e. one page at a time. For example, when we create a temp table and insert a row then initially two pages (one IAM + one Page from mixed extent) will be allocated. During this allocation process it will also place entry in SGAM and PFS pages. Let’s consider that we have only single data file here i.e. only Single SGAM Page and we have a workload that is trying to create lots of such tables. That means lots of threads trying to access that single SGAM page which may generates waiting condition and it may generates performance problem.
If you will enable the trace flag 1118, then SQL Server will allocate a dedicated uniform extent (only single access required for GAM and all pages under that extent will be marked as full in PFS entry) rather than allocating first 8 pages one by one (Eight access required for SGAM). That means SGAM and PFS contention is almost solved now.
Some changes were made by Microsoft team in this internal default mechanism for SQL Server 2005 onwards. They are using some kind of caching mechanism to cache temp tables (with one IAM page + one data page) rather than deleting that table completely at the time of deletion. Next time if any request will come to create a new table then sql will go for that cache first and will take that pre created table from there. This will really help to reduce that SGAM access issue. Is that means we don’t need Trace flag 1118. So the answer is: it depends on your workload. If you are still facing SGAM contention then you can use trace flag 1118. For more information you can click here.
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.
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
