How can we improve Azure Log Analytics ?

SQL Assessment performance tempdb

Priority Performance recommendation to "Configure the tempdb database to reduce page allocation contention" reporting incorrectly for SQL 2017 instance. Trace flag -T1118 is obsolete and has no effect in SQL 2017 per MS white paper.

2 votes
Vote
Sign in
(thinking…)
Password icon
Signed in as (Sign out)
You have left! (?) (thinking…)
Stephen Hall shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    1118 Removes most single page allocations on the server, reducing contention on the SGAM page. When a new object is created, by default, the first eight pages are allocated from different extents (mixed extents). Afterwards, when more pages are needed, those are allocated from that same extent (uniform extent). The SGAM page is used to track these mixed extents, so can quickly become a bottleneck when numerous mixed page allocations are occurring. This trace flag allocates all eight pages from the same extent when creating new objects, minimizing the need to scan the SGAM page. For more information, see this Microsoft Support article.

    Note: Starting with SQL Server 2016 (13.x) this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no effect. For more information, see ALTER DATABASE SET Options (Transact-SQL).

    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

Feedback and Knowledge Base