<rp id="wnpn7"><ruby id="wnpn7"></ruby></rp>
<progress id="wnpn7"><track id="wnpn7"><rt id="wnpn7"></rt></track></progress>
<ruby id="wnpn7"></ruby>
<ruby id="wnpn7"><blockquote id="wnpn7"><div id="wnpn7"></div></blockquote></ruby>

    1. <em id="wnpn7"><ruby id="wnpn7"><input id="wnpn7"></input></ruby></em>
      1. <button id="wnpn7"><acronym id="wnpn7"></acronym></button><button id="wnpn7"><acronym id="wnpn7"></acronym></button>

        <rp id="wnpn7"><acronym id="wnpn7"></acronym></rp>

        <li id="wnpn7"><object id="wnpn7"><u id="wnpn7"></u></object></li>
        VB.net 2010 視頻教程 VB.net 2010 視頻教程 python基礎視頻教程
        SQL Server 2008 視頻教程 c#入門經典教程 Visual Basic從門到精通視頻教程
        當前位置:
        首頁 > 數據庫 > SQL教程 >
        • sql教程之SQL Server等待事件—PAGEIOLATCH_EX

        本站最新發布    SQL Server 2016數據庫視頻教程

        試聽地址   http://www.squ68.com/eschool/SQLxin3721/
        什么是PAGEIOLATCH_EX等待事件? 下面我們將對PAGEIOLATCH_EX等待事件的相關資料做一個簡單的歸納、整理。關于PAGEIOLATCH_EX,官方文檔的簡單介紹如下:
         
        PAGEIOLATCH_EX
         
        Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
         
          在任務等待 I/O 請求中緩沖區的閂鎖時發生。 閂鎖請求處于“獨占”模式。 長時間的等待可能指示磁盤子系統出現問題。
         
        In SQL Server, a latch is a short-term lightweight synchronization object.
        Buffer latches including the PAGEIOLATCH_EX wait type are used to synchronize access to BUF structures and associated pages in the SQL Server database. The most frequently occurring buffer latching situation is when serialization is required on a buffer page. These buffer latches are not held for the complete period of the transaction. The PAGEIO latches are a subset of BUF latches used when the buffer and associated data page or the index page is in the middle of an IO operation. PAGEIOLATCH wait types are used for disk-to-memory transfers.
         
         
        在SQL Server中,閂鎖是短暫的輕量級同步對象。
        緩沖區閂鎖(包括PAGEIOLATCH_EX等待類型用于同步訪問SQL Server 數據庫中的 BUF 結構和關聯頁。最常見的緩沖區閂鎖情況是在緩沖區頁面上需要序列化操作時。這些緩沖區閂鎖不會在事務的整個期間內保持不變。PAGEIO閂鎖是緩沖區和相關數據頁或索引頁處于 IO 操作中間時使用的 BUF閂鎖的子集。PAGEIOLATCH 等待類型用于磁盤到內存的傳輸。
         
         
        PAGEIOLATCH_EX (exclusive mode page IO latch request)
         
        When a SQL Server user needs a page that is not in buffer cache, the database must first allocate a buffer page, and then puts an exclusive PAGEIOLATCH_EX latch on the buffer while the page is transferred from disk to cache. During this operation SQL Server puts a PAGEIOLATCH_EX request on the buffer on behalf of the user. After the write to cache is complete, the PAGEIOLATCH_EX latch is released.
         
         
        當SQL Server用戶需要不在緩沖區高速緩存中的相關頁面時,數據庫必須首先分配一個緩沖區頁面,然后在頁面從磁盤傳輸到高速緩存時,在緩沖區上放置一個獨占PAGEIOLATCH_EX閂鎖。 在此操作過程中,SQL Server 代表用戶在緩沖區上發出PAGEIOLATCH_EX請求。寫入高速緩存完成后,將釋放PAGEIOLATCH_EX閂鎖。
         
         
        Problem indication
         
        Excessive PAGEIOLATCH_EX waits occur when data is transfered from disk to memory for update operations and these transfers take time to complete.
        This may be an indication of disk contention or other disk subsystem bottlenecks.
         
        當數據從磁盤傳輸到內存中以進行更新操作時,將出現大量的PAGEIOLATCH_EX等待,并且這些傳輸(磁盤IO操作)需要時間才能完成。這可能表明存在磁盤爭用或其他磁盤子系統瓶頸。
         
        PS:其實當一個大表發生大量數據刪除操作時,也會出現大量的PAGEIOLATCH_EX等待事件。
         
         
         
        Description:
         
        This wait type is when a thread is waiting for the read of a data file page from disk to complete, and the thread is going to modify the page structure once it is in memory (EX = EXclusive mode). The Latches Whitepaper in the sidebar on the right has a description of all latch modes and their compatibility with other latch modes.
        (Books Online description: “Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.”)
         
        這種等待類型是線程正在等待從磁盤讀取數據文件的相關頁面到內存的完成,并且一旦線程將修改內存中的頁面結構(EX = EXclusive模式)。 右側邊欄中的“閂鎖白皮書”介紹了所有閂鎖模式及其與其他閂鎖模式的兼容性。
        (在線叢書描述:“當任務正在等待I / O請求中的緩沖區的閂鎖上時發生。閂鎖請求處于互斥模式。長時間等待可能表明磁盤子系統有問題。”)
         
         
         
         
        PAGEIOLATCH_EX
         
        A query is waiting for exclusive write access to a page in order to add data to the page, but the page is not currently in memory and has to be loaded from disk.
         
        Page latches are lightweight, non-configurable locks used by internal processes within SQL Server to manage access to the page buffer in memory.  When SQL Server has to read pages from the disk into the memory buffer or from the buffer out to disk, it must place latches on the buffer pages while the processes take place.
        It’s normal to see some PAGEIOLATCH_* waits, but if you’re frequently seeing them with wait times consistently above 10 milliseconds and you’re experiencing some type of latency, it suggests that the I/O subsystem is under pressure. Conversely, if you see many brief waits, you may be affected by increased I/O activity.
         
         
        查詢正在等待對頁面的獨占寫入訪問,以便將數據添加到頁面,但是該頁面當前不在內存中,必須從磁盤加載。
         
        閂鎖是SQL Server 內部進程用于管理對內存中頁面緩沖區的訪問的輕量級、不可配置的鎖。當SQL Server必須將磁盤中的頁面讀取到內存緩沖區中或從緩沖區中讀取到磁盤時,它必須在進程進行時將閂鎖放在緩沖區頁面上。
        看到一些PAGEIOLATCH_ *等待是很正常的,但是如果您經常看到它們的等待時間始終超過10毫秒,并且遇到某種類型的延遲,則表明I/O子系統面臨壓力。 相反,如果您看到許多短暫的等待,則可能會受到I/O活動增加的影響。
         
         
         
         
        參考資料:
         
        https://logicalread.com/2012/11/12/sql-server-pageiolatch_ex-wait-type/#.XkH2KCIzaHs
        https://www.sqlskills.com/help/waits/pageiolatch_ex/
        https://documentation.red-gate.com/sm4/working-with-overviews/using-performance-diagnostics/list-of-common-wait-types/pageiolatch_ex
        https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15
         
        作者:瀟湘隱者

        相關教程
                
        免费看成年人视频大全_免费看成年人视频在线观看