🥒
Dill's Knowledge Base
  • Hello World
  • 💻SQL
    • ❌Error Handling
    • 🧀Parameter Sniffing
      • Indexes
      • Query Hints
      • RECOMPILE
      • Branching
      • Memory Grants
      • Summary
      • Bonus
    • SQL Server Buffer Pool
  • 🖱️MongoDB
    • Instructor Led Training
      • DF100
      • DF200
      • DF300
      • DF400
    • MongoDB DBA University
      • DBA Admin Tools
      • DBA Basics
      • Metrics & Monitoring
  • 💻Web Design
    • Oxygen Tips
    • Bricks Builder
      • Tips
      • Discovery Call
      • Utility vs Custom Classes
      • Math Functions
      • Static vs Relative Units
  • Azure
    • AZ-900
      • Benefit of Cloud Computing
      • CapEx, OpEx and Consumption-based
      • Differences Between Cloud Service Categories
      • Identify The Right Service Type
      • Differences Between Types of Cloud Computing
      • Reliability and Predictability
      • Regions and Region Pairs
      • Availability Zones
      • Resource Groups
      • Subscriptions
      • Management Groups
      • Azure Resource Manager
      • Azure ARC
      • Resources Required for VM
      • Benefits and Usage of Core Compute Resources
      • Benefits and Usage of Core Network Resources
      • Public/Private Endpoints
      • Benefits and Usage of Storage Accounts
      • Benefits and Usage of Database Resources
      • Data Movement and Migration Options
      • Benefits and Usage of IoT Services
      • Benefits and Usage of Big Data and Analytics Services
      • Benefits and Usage of AI Services
      • Benefits and Usage of Serverless Technologies
      • Benefits and Usage of DevOps Technologies
      • Functionality of Azure Management Solutions
      • Functionality and Usage of Azure Advisor
      • Functionality and Usage of ARM Templates
      • Functionality and Usage of Azure Monitor
      • Functionality and Usage of Azure Service Health
      • Functionality of Microsoft Defender for Cloud
      • Functionality and Usage of Key Vault
      • Functionality and Usage of Microsoft Sentinel
      • Azure Dedicated Host
      • Defense in Depth
      • Describe the Concept of Zero Trust
      • Functionality and Usage of NSGs
      • Functionality and Usage of Azure Firewall
      • Functionality and Usage of Azure DDoS Protection
      • Explain Authentication and Authorization
      • Functionality and Usage of Azure AD
      • Microsoft Entra Overview
      • Functionality of Conditional Access, MFA and SSO
      • Functionality and Usage of RBAC
      • Functionality and Usage of Resource Locks
      • Functionality and Usage of Tags
      • Functionality and Usage of Azure Policy
      • Governance Hierarchy Constructs
      • Azure Blueprints
      • Describe Microsoft Privacy Statement, OST and DPA
      • Purpose of Trust Center and Azure Compliance Documentation
      • Purpose of Azure Sovereign Regions
      • Factors That Affect Costs
      • Factors to Reduce Cost
      • Functionality and Usage of Azure Cost Management
      • Purpose of Service Level Agreements
    • DP-900
      • Study Cram
    • DP-300
      • Deploy IaaS Soluton with Azure SQL
  • 📦Kubernetes
    • Udemy: Kubernetes for Beginners
Powered by GitBook
On this page
  1. SQL

SQL Server Buffer Pool

Source : PASS 2022 - Dive into SQL Server's Buffer Pool and Kill Wasted Space!

PreviousBonusNextInstructor Led Training

Last updated 1 year ago

SELECT * FROM Foo WHERE Bar = 1

  • this is a heap table

  • goes through TDS (tabular data stream) no matter what engine you are using (e.g. ODBC) -> protocol layer -> Lexer / Parser (Lexer is concerned with words and parser looks at sentences), which creates a "parse tree" -> query optimizer -> query executor (could be waiting on CPU or resource_semaphor) -> access methods -> buffer manager (we want to initiate some logical IO and if it is not found in buffer pool, it goes to disk and brings the page into the buffer pool using the BUF array to keep track of the pages we are pulling in) -> data page gets sent to client

    • When a SQL Server starts, it is quite aggressive with what it grabs into the buffer pool

UPDATE Foo SET fname = 'p' WHERE Bar = 1

  • For the update query above, go through all the normal steps and you eventually find the pages in the buffer pool

    • You then need to interact with the transaction manager and start writing to the log to ensure durability

      • The SQL Log Mgr will start writing to a log buffer (60kb in size)

      • At the same time, the transaction manager is making modifications to the page in the buffer pool and will mark it as dirty

      • Once the COMMIT is initiated, the log buffer will then go to the .ldf file

        • Azure PaaS, under the covers it writes to a page server of some sort versus a transaction log

        • Once the COMMIT is initiated and the log buffer has written to the durable storage, you are now complete even if the server goes down before replying to the client

  • Log buffer flush

    • Log buffer is full (60KB)

    • Commit transactions initiated

      • Exception: delayed durability (can cause data loss, but if you have lots of tiny lil writes it can help).. It means that the commit statement does not trigger to log flush operation

  • Writing back to the database .mdf/.ndf (3 scenarios)

    • CHECKPOINT

      • To shorten database recovery

      • Pages remain buffer pool

    • Lazy writer (it is up all the time.. not so lazy!)

      • Uses an LRU-k (least recently used) algorithm to remove pages from buffer pool

      • If the pages are dirty they have to be written to disk otherwise just remove them

      • If we need more space in memory then the warm pages will be removed next

    • Eager writer

      • SELECT INTO, Bulk Insert, WriteText and UpdateText.

  • In a clustered index, the table is physically ordered, but the rows themselves could be anywhere on a page

    • at the bottom of the page is a slot array, which is indeed in physical order

GAM - tracking space on one object on 64k extent

sGAM - tracking space on multiple objects on 64k extent

PFS - Tracks page space frequently in buckets every 8088 pages

  • Empty

  • 0-50% Full

  • 51-80% Full

  • 81-95% Full

  • 96-100% Full

Index Allocation Map (IAM)

  • tracks an allocation unit

    • IN_ROW_DATA

      • Heaps or indexes

    • LOB_DATA

      • XML, VARCHAR

    • ROW_OVERFLOW_DATA

      • Rows/columns that exceed 8060 bytes

Some causes of wasted Bpool space?

  • HEAPs (in operation)

  • duplicats/redundant/unused indexes

  • Page splits

  • Is_deleted BIT NOT NULL AKA soft deletes

Easy wins?

  • Delete unused indexes

  • page compression

    • For OLTP, not warehousing

  • columnstore compression

💻