SQL Server: Riduzione dell'I/O del disco
L'indicizzazione e l'ottimizzazione delle query costituiscono un metodo efficace per ridurre l'I/O fisico e logico del disco.
Tratto da "SQL Server DMV Starter Pack," pubblicato da Red Gate Books (2010).
Glenn Berry, Louis Davidson e Tim Ford
C'è una persistente necessità di ridurre al minimo i/o logici e fisici. La collezione di oggetti di gestione di database relativi ai / O (DMOs) aiuta a indagare, in particolare, fisico I/O, che si svolgono sul sistema, quando i dati scritti e leggere dal disco.
DMOs in questa categoria forniscono un'immagine esplicita del disco i/o dal punto di vista del sottosistema del disco. Essi ci mostrano, ad esempio, come i/o è distribuito in vari file sul disco, luoghi dove i/o è diventare un collo di bottiglia e con conseguente I/O bancarelle e così via. È possibile utilizzare queste informazioni per ottimizzare l'architettura del sottosistema disco. Si potrebbe anche raccogliere dati e utilizzarlo per sostenere le richieste per i capi unità di affari per maggiore capacità di archiviazione.
Naturalmente, alcuni i/o fisico è inevitabile. SQL Server deve scrivere dei dati dell'applicazione su disco. Ha anche a scrivere nel log delle transazioni per ogni inserimento, aggiornamento ed eliminazione e anche per operazioni di massa. Tuttavia, prima di saltare alla conclusione che semplicemente richiedono più potenza del disco, ricordate che c'è che molto si può fare in termini di query ottimizzazione e indicizzazione per ridurre al minimo i/o logici e fisici inutili.
Si dovrebbe prendere in considerazione le informazioni I/O derivate dal DMOs coperto qui (tutti che iniziano con "sys.dm_io_"), oltre ai dati da altre viste a gestione dinamica (DMV) che fanno riferimento a prestazioni dei / o in qualche modo, tra cui:
- DM exec_query_stats – I/O che una determinata query è costato oltre le volte che è stato eseguito
- exec_connections – I/O che ha avuto luogo in tale connessione
- DM exec_sessions – I/O che ha avuto luogo durante questa sessione
- DM os_workers-i/o in sospeso per un thread di lavoro determinato
Tutte le query in questa sezione lavorare con SQL Server 2005, 2008 e 2008 R2, e tutti richiedono l'autorizzazione View Server State.
Indagare i colli di bottiglia del disco tramite i/o bancarelle
DMV useremo qui è DM io_virtual_file_stats, che SQL Server Books Online descrive come: "Restituisce le statistiche I/O per i file di dati e di log. Questa vista a gestione dinamica sostituisce la funzione fn_virtualfilestats."
Questa DMV accetta due argomenti: database_id e file_id. È possibile specificare NULL per uno. In tal caso, restituirà informazioni su tutti i database o tutti i file.
Si noti che questa DMV è cumulativa. In altre parole, i valori nelle colonne dati incrementano continuamente dal punto quando il server è stato riavviato ultima. Questo significa che avete bisogno di prendere una misura di base, seguita dalla misura reale. Quindi sottrarre i due, che si può vedere dove I/O sta accumulando.
Questo script permette di visualizzare il numero di letture e scrive su ogni file di dati e di log per ogni database in esecuzione su un'istanza di SQL Server. Ordinati di tempo medio di stallo I/O, in millisecondi:
-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
Questa query ti mostrerà i file in attesa più lunga per i/o su disco. Può aiutarvi a decidere dove localizzare i singoli file basati sulle vostre risorse su disco disponibile. È possibile utilizzare anche per aiutare a convincere qualcuno come una SAN ingegnere che SQL Server è vedere disco strettoie per alcuni file.
Indagare tra i colli di bottiglia di disco via in attesa dei / o
Questo richiede un approccio leggermente diverso per indagare i colli di bottiglia dei / o disco. Utilizzare il DMV, che descrive la documentazione Online di SQL Server come sys.dm_io_pending_io_requests: "Restituisce una riga per ogni richiesta dei / o in sospeso in SQL Server".
I dati di DMV forniscono un'istantanea di "punto nel tempo" di richieste dei / o in attesa sul sistema, proprio in questo momento che si esegue lo script:
-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs. num_of_reads , fs. num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
Poiché questi dati rappresentano uno snapshot point-in-time delle attività, si desidera eseguire la query più volte per vedere se gli stessi file (e le stesse lettere di unità) compare costantemente nella parte superiore dell'elenco. Se ciò accade, è prova di colli di bottiglia I/O per quel particolare file o unità lettera. Si potrebbe utilizzare questo per convincere il vostro SAN ingegnere il sistema stava sperimentando problemi di I/O per un particolare LUN.
Le ultime due colonne nella query restituiscano il numero cumulativo di lettura e scrittura per il file di avvio di SQL Server (o da quando è stato creato il file — qualunque era più breve). Questa informazione è utile quando si cerca di decidere quale livello RAID da utilizzare per una determinata lettera. Ad esempio, i file con più attività di scrittura solitamente suonerà meglio su un RAID 10 LUN di essi sarà su un RAID 5 LUN.
Conoscendo il rapporto relativo di lettura e scrittura per ogni file può aiutare a collocare i file di database su un appropriato LUN. Questo, a sua volta, vi aiuterà ottimizzare le query per una maggiore efficienza.
.png)
.png)
.png)
Glenn Berry lavora come un architetto di database NewsGator Technologies in Denver, Colorado Egli è un MVP per SQL Server e ha un'intera collezione di certificazioni Microsoft, tra cui MCITP, MCDBA, MCSE, MCSD, MCAD e MCTS, che dimostra che ama prendere le prove.
Louis Davidson è stato nel settore IT per 16 anni come sviluppatore di database aziendali e architetto. Egli è stato un Microsoft MVP per SQL Server per sei anni e ha scritto quattro libri sulla progettazione di database. Attualmente è l'architetto di dati e a volte DBA per Christian Broadcasting Network, uffici di supporto in Virginia Beach, Virginia e Nashville, Tenn.
Timothy Ford mis MVP per SQL Server e si lavora con SQL Server per più di 10 anni. È il primario DBA ed esperto della materia per la piattaforma SQL Server per la salute di spettro. È stato iscritto sulla tecnologia dal 2007 per una varietà di siti Web e gestisce il proprio blog al thesqlagentman.com, che copre il SQL come soggetti di sviluppo così come telelavoro e professionale.**
Ulteriori informazioni su "SQL Server DMV Starter Pack" a red-gate.com/our-company/about/book-store.