Salve, ero indeciso sul fatto di creare un “post” sull’argomento, dato che si tratta di argomento trattato e ritrattato fino alla noia, ma all’ennesima domanda di un cliente che chiedeva chiarimenti sul numero di file da fare (vi dice niente ? :-) ) ho deciso di mettermi a scrivere questa pagina, con la promessa, però, di raccogliere nella maniera più sintetica possibile tutto quello che riguarda eventuali ottimizzazioni di performance e cercando di fugare la ridda di informazioni parziali, non corrette e vecchie che si trovano in giro su Internet, e talvolta anche nella documentazione Microsoft.Ma perché il TEMPDB è così importante ? Principalmente per due ordini di ragioni:
Versioning (utilizzo degli “Snapshot Isolation Levels”);Online index operations (create, alter, rebuild);DBCC;Cursori;Triggers;MARS;Multi-statement table-valued functions (TVFs);
Dunque, ecco la lista dei punti qualificanti che vorrei presentarVi, ho evidenziato in blu le frasi più significative per darVi una visione immediata delle semplici regole da utilizzare:
1) Numero di data file
a. Questo è il punto di maggior confusione, esistono in giro varie interpretazioni basate sul numero di CPU, sull’Hyper-Threading, sui Core, e via discorrendo, fino ad arrivare alla regola che molti conoscono la quale finisce con la frase “…. ma non più di 8…”;
b. Il concetto teorico che sta dietro ad un stima corretta è: quanti thread (i.e. = query) ci possono essere, nel caso peggiore, che accedano contemporaneamente ad oggetti nel TEMPDB ? Questo dovrebbe subito rivelarVi che non importa l’Hyper-Threading o i Core, importa alla fine solo il numero di “CPU Logiche” che alla fine SQL Server si ritrova, al netto ovviamente di una eventuale “Affinity Mask” con cui è possibile limitare i Core/CPU utilizzati da una istanza di SQL Server. Se non volete affaticarVi nel conteggio, è sufficiente eseguire la seguente query per ottenere il numero esatto:
select COUNT(*) from sys.dm_os_schedulers where [status] = 'VISIBLE ONLINE'
c. OK, ora che sappiamo quante “CPU Logiche” ha la nostra istanza di SQL Server, quanti data file creiamo per il TEMPDB ? La regola più completa, ma non necessariamente la più corretta, è: “Tanti data file quante sono le CPU Logiche in uso all’istanza di SQL Server”; perché ho detto che non è necessariamente la più corretta ? Perché la regola vera sarebbe “Tanti data file fin quando non c’è più “contention” sulle pagine di allocazione di sistema contenuti nei data file stessi del TEMPDB”, ma non è molto semplice da appurare per un non esperto DBA, quindi manteniamo per semplicità la regola precedente marcata in blu.
NOTA: La famosa postilla che molti di Voi hanno sicuramente visto/sentito riguarda l’ipotetico massimo di 8 data file: intendiamoci, non è sbagliata a priori, il senso di questa regola è che se non sapete con certezza quanti data file fare per eliminare la “contention”, allora partite pure con la regola marcata in blu ma senza esagerare, un numero superiore di data file dovrebbe essere giustificato da una approfondita analisi delle performance del TEMPDB per capire se ha senso andare oltre;
d. E’ bene tener presente che fare un gran numero di data file per qualsiasi database, a maggior ragione per il TEMPDB, può avere effetti deleteri sul tempo necessario per portare “ONLINE” un database, dato che una delle fasi di “start” che opera in maniera rigorosamente seriale è appunto quella che analizza e poi apre (a livello NTFS) ogni singolo file.
e. IMPORTANTE: Vi prego di notare che ho sempre specificato “data file” e non “log file” o “transaction log file” (le definizioni sono equivalenti) dato che è assolutamente inutile fare “log file” aggiuntivi: a differenza dei “data file” che vengono usati in parallelo, i “log file” sono usati in seriale, solo al riempimento dell’uno SQL Server usa il successivo;
2) Dimensione iniziale
a. Questa è una delle più importanti ottimizzazioni, per qualche scellerato motivo, quando installate SQL Server, la dimensione iniziale dell’unico data file è di soli 8 MB, quella dell’unico transaction log file è di 1 MB, entrambi con “autogrowth“ del 10 %:
b. Se pensate che un TEMPDB può arrivare a svariati GB, se non decine o centinaia di GB, Vi immaginate quanti piccoli “pezzi” (fragment) sul file system dovranno essere allocati ? Migliaia se non milioni, e tutti di dimensioni ridicole, questo è il modo perfetto per distruggere le performance dell’NTFS anche sulle SAN più potenti !
c. La regola d’oro in questo caso è: “Immediatamente dopo l’installazione, rivedere le dimensioni iniziali del TEMPDB portando la dimensione dell’unico data file e del transaction log file ad almeno 1GB”.
NOTA: Non è detto che 1GB sia sufficiente, diciamo che l’NTFS sotto questa soglia ha performance non ottimali per i data file di SQL, su installazioni medie se non grosse consiglio almeno 4-8GB per i data file e 2-4GB per il transaction log file;
3) Autogrowth
a. Ad integrazione della precedente, aggiungo anche questa ulteriore regola: “Eliminare l’Autogrowth in percentuale ed impostare un valore fisso di incremento in MB per tutti i file, usando lo stesso valore”.
i. Che valore scegliere per l’ ”Autogrowth” ? Come raccomandazioni di massima direi almeno 1-2GB per i data file e tra 0.5 e 1.0GB per i transaction log file;
b. Nello scegliere la dimensione per l’autogrowth del transaction log file del TEMPDB, così come per ogni altro database utente considerate questa postilla aggiuntiva: “Evitate di usare il valore esattamente pari a 4GB per l’espansione del transaction log, altrimenti incorrerete in bug noto da 3 versioni di SQL Server”;
i. Il bug è descritto al seguente link:
Bug: log file growth broken for multiples of 4GBhttp://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
NOTA: Ho già verificato che nella CTP3 di Denali questo annoso, nel vero senso della parola, problema è stato finalmente risolto;
c. Lo scopo ultimo dovrebbe essere quello di evitare eventi di “Autogrowth” , dato che sono eventi bloccanti (per il workload di SQL Server) per l’intera durata dell’operazione di espansione, specialmente se:
i. Si tratta di transaction log file con incrementi cospicui, dato che non è possibile beneficiare dell’ “Instant File Initialization” (vedi punto[4] seguente);ii. Si tratta di data file con incrementi cospicui e non si sta utilizando la feature “Instant File Initialization” (vedi punto[4] seguente);
d. Se volete sapere quali, quanti e di che durata sono stati gli eventi di “Autogrowth” sulla Vostra istanza SQL, è possibile utilizzare il report “Disk Usage” dalla console di SQL, tra gli “Standard Report” a livello di singolo database:
i. E’ anche possibile catturare uno specifico evento in una trace SQL con il SQL Profiler:
e. Utilizzando le informazioni dei punti precedenti, ecco una ulteriore regola a corollario della principale: “Dopo aver riconfigurato i valori di size ed autogrowth per i data/log file del TEMPDB, verificare periodicamente la numerosità e la durata degli eventi di autogrowth ed eventualmente ritoccarli ulteriormente per diminuirne frequenza e durata”.
4) Instant File Initialization
a. Si tratta di una importantissima “feature” dell’NTFS di Windows (>= 2003) che permette la creazione o l’espansione istantanea (1-2ms) dei data file di SQL Server, ma non dei transaction log files (quindi prestate attenzione!), anche di centinaia di Gigabyte (GB) o Terabyte (TB).
b. A meno che non abbiate interesse in una attività di “Security Hardening” molto spinta, consiglio caldamente l’abilitazione di questa funzionalità, come abilitarla è presto detto nella seguente frase che riassume questa raccomandazione: “Assicurarsi che il service account di SQL Server abbia lo user-right denominato [Perform Volume Maintenance Tasks] su tutte le macchine dove girerà il servizio”.
5) Posizionamento file
a. Dato che le performance del TEMPDB sono critiche, dovrebbe essere oggetto privilegiato per avere almeno una LUN dedicata, la regola più generale (e costosa !) possibile recita: “Il database TEMPDB dovrebbe avere una LUN dedicata per i data file ed una LUN dedicata per il transaction log file”;b.Dare due LUN al TEMPDB potrebbe non essere sempre possibile per ragioni di costo, oppure potrebbe essere non necessario perché le altre LUN condivise potrebbero reggere il carico necessario;c.In caso ci si debba ridurre ad una sola LUN per il TEMPDB, consiglio di dedicarla ai data file e di piazzare il transaction log file sulla stessa LUN utilizzata dai transaction log dei database utente;
6) RAID
a. Qui la storia è semplice, vale la regola generale per tutti i database, quindi la regola sommaria potrebbe recitare così: “Per la LUN del transaction log file del TEMPDB, scegliere un tipo di RAID che ottimizzi le scritture sequenziali (RAID-1 o RAID-10), mentre per i data file scegliere un tipo di RAID che ottimizzi letture/scritture random, pur garantendo il necessario grado di fault tollerance”.b.La suddetta regola si riferisce ad una situazione ottimale, è anche possibile utilizzare un tipo di RAID meno performante, ma anche meno costoso, a patto che il sottosistema di I/O sia in grado di reggere il carico necessario;c.Per verificare le performance dell’ I/O a livello di singolo file, non è possibile fare uso dei Performance Counters di Windows, è però possibile usare la seguente query in SQL Server (specifica per tutti i file del TEMPDB):
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'),null)
d.E’ anche possibile visualizzare in modalità “live” I tempi di accesso ad ogni file di ogni database, anche per il TEMPDB quindi, utilizzando l’Activity Monitor di SQL 2008 (e successive versioni) come da figura seguente:
b. Sebbene il TEMPDB sia un database con dati “a perdere” dato che tutti i suoi file vengono distrutti e ricreati ad ogni stop/start, “Per il TEMPDB è comunque sconsigliato utilizzare volumi di tipo RAID-0”: in caso di fallimento/corruzione del disco, l’intera istanza si fermerà se il TEMPDB non sarà in grado di funzionare correttamente.
7) Trace flags
a. I principali e più importanti “trace flags” con cui si può intervenire per ottimizzare le performance del TEMPDB sono due:
i. -T1118: Utilizzato moltissimo in SQL 2000 dove non erano presenti alcune importanti ottimizzazioni per il TEMPDB, è tutt’ora valido (e utile) anche in SQL 2005 e successive versioni: dopo aver abilitato tale trace flag, l’allocazione di spazio per gli oggetti temporanei avverrà solo utilizzando “extent” di tipo “uniform”, non più di tipo “mixed”, i particolari sono nel link seguente, questo serve a diminuire possibili colli di bottiglia sulle pagine di sistema di tipo SGAM in ogni data file del database:
Concurrency enhancements for the tempdb databasehttp://support.microsoft.com/kb/328551/en-us
SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usagehttp://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx
Misconceptions around TF 1118http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx
ii. -T1117: Questo è molto meno noto del precedente, ma fornisce un’interessante funzionalità: nel caso il TEMPDB abbia più data file, quando un singolo file deve andare in “Autogrowth”, allora SQL Server espande tutti i data file comtemporaneamente; su alcune grosse installazioni di SQL Server, questo serve a mantenere perfettamente bilanciato lo spazio allocato tra tutti i data file presenti, migliorando la strategia di allocazione per il TEMPDB;
IMPORTANTE: Nel caso si decida di adottare il trace flag “-T1117” e/o si abbiano numerosi data file e/o le dimensioni di “Autogrowth” siano non trascurabili, assicurarsi di aver abilitato la feature “Instant File Initialization” altrimenti le operazioni di espansione si prolungheranno per un tempo potenzialmente lungo.
b. Questi trace flag devono essere aggiunti, eventualmente, agli “startup parameters” dell’istanza SQL Server mediante il tool “SQL Configuration Manager”, separati da “;” e facendo molta attenzione ad aggiungerli alla fine di quanto già presente senza sovrascrivere i parametri esistenti, e senza spazi tra “-T” ed il valore numerico (1117 o 1118):
c. Per finire, ecco la regola sommaria: “Utilizzando il tool SQL Configuration Manager, aggiungere il trace flag –T1118, ed eventualmente anche –T1117, agli startup parameters dell’istanza SQL.
d. I parametri avranno effetto al successivo riavvio dell’istanza SQL, verificate che nell’ERRORLOG compaia una situazione del genere (deve comparire uno spazio tra –T ed il relativo valore numerico, altrimenti avete sbagliato qualcosa !):
Registry startup parameters:
-d C:\SQL2008\MSSQL10.SQL2008\MSSQL\DATA\master.mdf -e C:\SQL2008\MSSQL10.SQL2008\MSSQL\Log\ERRORLOG -l C:\SQL2008\MSSQL10.SQL2008\MSSQL\DATA\mastlog.ldf -T 1118 -T 1117
8) Frammentazione esterna NTFS
a. Altro effetto sgradevole delle “Autogrowth” frequenti e/o di piccole dimensioni, è la frammentazione a livello NTFS dei data/log file non solo del TEMPDB, in questo caso, ma di tutti i database in generale, ovviamente è preferibile che tutti i data/log file di un database siano preallocati in maniera contigua evitando, per quanto possibile, susseguenti espansioni;
b. Se volete analizzare lo stato di frammentazione a livello di file system potete usare il seguente utilissimo tool dalla suite di SysInternals:
Contig v1.6 (by Mark Russinovich)http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx
c. Dato che ho già formulato, nei paragrafi precedenti, le regole relative alle dimensioni raccomandate per la size iniziale e per l’”Autogrowth”, qui non mi rimane che aggiungere la seguente regola accessoria: “E’ consigliato controllare periodicamente la frammentazione a livello di file system dei data/log file che compongono il TEMPDB, utilizzando il tool Contig di SysInternals ”; nel caso che uno o più dei suddetti file sia composto da più di una decina di “fragments” è consigliato rigenerare i file del TEMPDB con i seguenti macro passi:
i. Fermare il servizio SQL Server;ii. Eseguire un backup “offline” di tutti i data/log file che compongono tutti i database (di sistema e utente) dell’istanza, ad eccezione del TEMPDB;iii. Utilizzare un tool di deframmentazione disco ed attivarlo sulla/e LUN in oggetto per ricompattare lo spazio libero su disco.iv. Riavviare l’istanza SQL: al riavvio il database TEMPDB sarà ricreato in ogni suo file, stavolta con dimensioni iniziali congrue in modo da evitare ulteriori frammentazioni dovute a valori iniziali troppo piccoli e/o autogrowth troppo frequenti;
9) Frammentazione interna VLF
a. Il transaction log di ogni database ha una sua struttura interna complessa, cercando di condensare quanto più possibile, esso è diviso in un numero variabile di unità contigue di spazio disco denominate Virtual Log Files (VLFs): quanti VLF, e di che dimensione lo decide SQL Server autonomamente senza possibilità di intervento diretto, l’unico modo di influenzarlo è stabilire la dimensione iniziale del transaction log e la dimensione delle eventuali autogrowth, in base a questi SQL Server farà le sue scelte; I particolari li potete trovare a questo link:
Transaction Log Physical Architecturehttp://technet.microsoft.com/en-us/library/ms179355.aspx
b. Il problema nasce se il numero di VLF diventa elevato, questo infatti può impattare, in generale su ogni database ed in parte anche sul TEMPDB, pesantemente su attività come la recovery di un database (alla partenza di SQL), i tempi di backup/restore, le autogrowth del transaction log ed addirittura le classiche operazioni di manipolazioni dati (DML);
c. In base alle considerazioni dei due punti precedenti, ecco la regola sintetica di questo paragrafo: “Periodicamente, è necessario monitorare il numero di VLF per il database TEMPDB e se ne risultano più di 100 è necessario intraprendere azioni correttive”;
i. Per controllare il numero di VLF per tutti i database, anche per il TEMPDB, è possibile utilizzare lo script che trovate al seguente link, gentilmente fornito dal nostro buon amico Paul Randal (http://www.sqlskills.com):
http://www.sqlskills.com/BLOGS/PAUL/file.axd?file=2010%2f4%2fSQLSkillsLogInfo.zip
d. Le azioni correttive per abbassare il numero dei VLF nel database TEMPDB sono le seguenti:
i. Aumentare la dimensione iniziale del transaction log in maniera congrua, seguendo le raccomandazioni di questo documento;ii. Aumentare la dimensione della eventuale autogrowth in maneria congrua, seguendo le raccomandazioni di questo documento;iii. Riavviare l’istanza SQL Server per rigenerare il database TEMPDB;
10) Formattazione LUN
a. Se avete ancora la sfortuna di dover ancora installare (o gestire) SQL Server su Windows Server 2003 (o precedenti) fate molta attenzione all “Partition Alignment”, dato che tali versioni di Windows, by default, creano le partizioni “disallineate” con offset a 31.5 KB; la perdita di performance, sul sottosistema di I/O, può arrivare anche al 50%, è caldamente consigliato crearle, quindi, con offset a 1MB come da default per Windows Server 2008 e successive:
Disk Partition Alignment Best Practices for SQL Serverhttp://msdn.microsoft.com/en-us/library/dd758814.aspx
NOTA: In genere l’offset a 1MB è ottimale per tutte le “stripe unit” delle SAN attualmente in commercio, in ogni modo è consigliato verificare con il Vendor per eventuali differenti raccomandazioni in tale senso.
b. A meno che il Vendor della Vostra SAN non abbia differenti e specifiche raccomandazioni in materia, formattare la partizione per il TEMPDB con “allocation unit” a 64KB;
NOTA: entrambe queste raccomandazioni si applicano in generale alle LUN utilizzate per tutti i database, non solo per il TEMPDB.
In aggiunta a questa lunga lista, ecco alcune importanti informazioni e peculiarità sul TEMPDB:
- Da SQL 2008 è possibile abilitare l’algoritmo di tipo “CHECKSUM” (default in SQL 2008 e seguenti) anche sul TEMPDB;
- Il “Recovery Model” di default del TEMPDB è SIMPLE (vedi figura precedente) e decisamente non vedo ragioni per cambiarlo;
- Aspetto teorico, ma anche “capacitivo”, molto interessante del TEMPDB: al contrario dei “normali” database utente, dove sia le informazioni di “redo” (roll-forward) che di “undo” (roll-back) delle transazioni vengono inserite nel transaction log, nel TEMPDB solo le informazioni di “undo” vengono considerate in modo da poter supportare eventuali “rollback” delle transazioni utente che contengono tabelle temporanee; non è necessario avere le informazioni di “redo”, necessarie eventualmente solo nella procedura di “crash recovery” (di qualsiasi altro database), quando esso viene messo/portato “online”, perché il TEMPDB viene ricreato da zero e non passa attraverso tale procedura.
- Il processo di “Checkpoint” interno di SQL Server, è attivo anche sul TEMPDB ma si comporta in maniera leggermente differente: generalmente (esiste un’eccezione), le “dirty pages” in memoria relative al TEMPDB non vengono scaricate (flush) su disco proprio perché il TEMPDB non passa attraverso la procedura di “crash recovery” quando và online; l’eccezione è la regola per i database in SIMPLE recovery model, e cioè quando lo spazio effettivamente usato arriva al 70% della dimensione totale.
- In SQL Server 2005 è stato introdotto un meccanismo di “caching” specializzato per il TEMPDB in modo da eliminare gli storici colli di bottiglia sulle mappe di allocazione: quando si distrugge un oggetto temporaneo in TEMPDB, non tutto viene deallocato, una pagina di tipo “IAM” (index allocation map) ed un data page rimangono, in modo da poter essere eventualmente riutilizzate senza dover passar da alcune tabelle di sistema (di allocazione) di SQL Server;
- In aggiunta ai normali performance counters utilizzati per ogni database utente, il TEMPDB ne ha alcuni specializzati che è bene tenere sotto controllo:
• General Statistics -> Active Temp Tables;• General Statistics -> Temp Tables Creation Rate;• General Statistics -> Temp Tables for Destruction;• Transactions -> Free Space in tempdb (KB);• Transactions -> tutti i counters che iniziano con "Version";
- Credenza popolare assolutamente sbagliata: le variabili di tipo tabelle temporanee stanno in memoria e non in tempdb, ovviamente non è vero !
- Anche per il TEMPDB è possibile eseguire il controllo per la presenza di eventuali corruzioni con il comando “DBCC CHECKDB”, cosa per altro consigliata;
- Anche per le tabelle temporanee create in TEMPDB, è possibile creare indici e statistiche, inoltre le funzionalità a livello di database denominate “AutoCreate Statistics” e “AutoUpdate Statistics” sono attive ed è consigliato lasciarle in questo stato.
- Il “database ID” del TEMPDB è sempre (2), ricordavelo, specialmente quando analizzate eventuali blocchi, deadlock, o report di corruzione;
- Attenzione quando abilitate la “Transparent Database Encryption” (TDE) di SQL 2008 su un qualsiasi database utente perché anche il TEMPDB sarà totalmente criptato, sia nella parte relativa ai data file che transaction log files.
- Attenzione ad eventuali operazioni di “shrink” manuale sul TEMPDB, potreste causare una corruzione dello stesso database, leggete bene il seguente articolo, in particolare la sezione "Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use":
How to shrink the tempdb database in SQL Serverhttp://support.microsoft.com/kb/307487/en-us
- La “Collation” con cui il TEMPDB viene creato è quella di sistema che Voi avete specificato all’atto dell’installazione, fate attenzione ad usare database con tale differente impostazione perchè, nel caso le Vostre query facciano un confronto tra stringhe e/o tipi di dato strutturati (money, datetime, etc.) potrebbero esserci dei problemi o degli errori perché il confronto è tra “Collation” differenti e potrebbe essere necessario inserire delle conversioni forzate (“collate” query hint option) per rendere consistente il risultato.
Spero di avere inserito tutto, nel caso mi fossi dimenticato qualcosa non esitate a farmelo sapere.
Al prossimo post !
--Igor Pagliai--
Ben tornati a tutti (o quasi) dalle ferie, per me sono già finite da qualche giorno ma, come molti di Voi, sto ancora spalando le centinaia di mail accumulate durante il mese di Agosto. In genere qualche sorpresa capita sempre, e anche quest’anno l’aspettativa non è andata delusa emi sono ritrovato con una notizia, per gli addetti ai lavori, abbastanza sconvolgente, Ve la riporto con il post diretto dal Blog dei colleghi del team del “SQL Native Client”:
http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx
The next release of Microsoft SQL Server, codename “Denali”, will be the last release to support OLE DB. OLE DB will be supported for 7 years from launch, the life of Denali support, to allow you a large window of opportunity for changing your applications before the deprecation.
OK, dopo un attimo di smarrimento cerchiamo di fare il punto e di “mitigare” lo sgomento:
http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-10-20-20-75/A-Quick-Guide-for-OLE-DB-to-ODBC-Conversion.docx
L’impatto sulle applicazioni è ovvio, ma ci sono altre considerazioni un po’ meno ovvie riguardanti, per esempio, i “Linked Server”, ADO.NET, SSIS, OLEDB Provider per DB2 e altre componenti Microsoft basate su OLEDB. Il seguente post ci dice qualcosa in merito a cosa succederà di qui aqualche anno:
http://social.technet.microsoft.com/Forums/en/sqldataaccess/thread/e696d0ac-f8e2-4b19-8a08-7a357d3d780f
Other Microsoft SQL Server features and products that are built on top of OLE DB or use OLE DB, like Distributed Query (Linked Server), SQL Server Integration Services, SQL Server Analysis Services etc., will continue to be supported as per their respective terms. Microsoft will take care of eplacing the underlying dependency.
P.S. = Qualche sospetto su un annuncio del genere già lo avevo da un paio di anni, quando con la nascita di “SQL Azure” l’OLEDB non è stato (e non è) incluso nei protocolli dati per l’accesso all’infrastruttura, a vantaggio di ODBC, PHP, ADO.NET, JDBC, etc. Un assenza molto sospetta…. :-)
Ah, un’ultima importante cosa, mi raccomando però di fare in fretta visto che scade il 9 Settembre prossimo: se volete dire la vostra o influenzare quello che Microsoft farà nel prossimo futuro per quanto riguarda la connettività a SQL, compilate la survey contenuta al link sottostante.
http://www.zoomerang.com/Survey/WEB22CS45XT9FE/
Buon lavoro a tutti.
Lo confesso, anche io non presto molta attenzione alle “Options” del SQL Management Studio (SSMS), principalmente perché uso sempre gli script TSQL per modificare (e non solo) la struttura di una tabella, ma la cosa può essere potenzialmente pericolosa per cui ho ritenuto interessante creare questo nuovo post per il nostro blog.
Dunque, lo scenario è quello relativo ad una eventuale modifica della struttura di una tabella, magari molto grossa in termini di spazio occupato e numero di righe contenute, per la quale il DBA sceglie di usare l’interfaccia grafica del “SQL Server Management Studio (SSMS)”, nello specifico il “Table Designer”. Quest è una semplice tabella a 2 campi con 1000 record banali inseriti:
Ora proviamo ad aggiungere una colonna altrettanto banale (“valore2” di tipo INT “nullable) alla suddetta tabella:
Ok, fin qui tutto bene, la colonna è stata aggiunta istantaneamente ed i valori per tutte le righe sono state impostati ovviamente a “NULL”:
Proviamo ora una cosa diversa, cioè la rimozione della stessa colonna, ancora tutto bene, nessun problema e prendendo una trace con il Profiler, la console di SQL tra le altre cose esegue il comando che ci si aspetta:
ALTER TABLE dbo.T1 DROP COLUMN valore2
Proviamo ora a rimuovere l’attributo “Allow Nulls” dalla colonna “valore2”, sempre dal “Table Designer”, non appena clicchiamo per salvare la modifica ecco quello che compare sullo schermo:
Perché appare questo messaggio ? Perché il “Table Designer” vuole distruggere questa tabella e ricrearla ?
La risposta alla prima domanda è nelle “Options” del tool, così appaiono di default (almeno su SQL 2008 R2):
La risposta alla seconda domanda è che l’interfaccia grafica talvolta non “traduce” la Vostra modifica in rapporto 1:1 con i comandi TSQL che normalmente un DBA utilizzerebbe; per verificare questo disabilitiamo l’opzione cerchiata in rosso e ripetiamo l’operazione, nel frattempo avviamo una semplice trace con il SQL Profiler per vedere cosa accade in background:
Ovviamente nessun messaggio di errore questa volta, ma andando a vedere cosa c’è nella trace del SQL Profiler abbiamo diverse cose molto interessanti:
A questo punto ricapitoliamo cosa è successo a fronte della modifica alla struttura della tabella:
Se non avete colto la pericolosità di questa cosa permettetemi di aiutarVi, immaginate che la tabella “T1” abbia qualche decina o centinaia di milioni di record o che comunque sia di dimensioni ragguardevoli:
Un’ultima considerazione: cosa sarebbe accaduto nel caso questa tabella fosse stata riferita da una o più “Foreign Key Contraint” ? L’operazione di “drop” della tabella originaria sarebbe andata in errore oppure il Table Designer avrebbe eseguito ulteriori attività in background ? Bè, per questo magari dovrete aspettare un ulteriore post su questo blog ! :-)
A questo punto, per i DBA non avvezzi all’uso estensivo del TSQL per la modifica della struttura delle tabelle, una domanda sorge spontanea: c’è un modo più efficiente per fare questa operazione ? Ovviamente sì, ed è anche molto semplice:
alter table T1 alter column valore char(8000) not null;
Avendo abilitato le statistiche sull’I/O , l’esecuzione (con successo) del suddetto comando, ecco le informazioni:
Table 'T1'. Scan count 1, logical reads 1000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Liscio e lineare, vero ?
La lezione di oggi dal titolo: “In produzione, mai modificare la struttura di una tabella con il Table Designer senza aver prima verificato cosa realmente accade in background, specialmente se la tabella è molto grande” è finita, buon lavoro a tutti ed arrivederci al prossimo post.
Speravo di fare un unico post ed includere anche la successiva CU per SQL 2008, purtroppo sta tardando e quindi procediamo con questo unico aggiornamento:
Cumulative update package 2 for SQL Server 2008R2 Service Pack 1
http://support.microsoft.com/kb/2567714/en-us
Come al solito, la lista delle (CU) rilasciate per SQL Server 2008 R2 dal rilascio della SP1, è disponibile al seguente link:
The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released
http://support.microsoft.com/kb/2567616/en-us
Ecco cosa di interessante contiene:
- Senza scendere nel dettaglio dei singoli problemi, se state usando la “Replica”, allora questa (CU2) è un “must” per Voi, ci sono ben 6 problemi risolti, alcuni di una certa gravità;
- Miglioramento dei tempi di “recovery” di un database, specialmente se avete un numero non banale di “Virtual Log Files”:
Recovery takes longer than expected for a database in a SQL Server 2008 or in a SQL Server 2008 R2 environment
http://support.microsoft.com/kb/2524743/en-us
- Soluzione ad un problema di generazione di risultati non corretta in caso di query che contiene una “INSERT .. SELECT” abbinata ad una “UNION ALL”:
Incorrect results when you run an INSERT SELECT UNION ALL statement in SQL Server 2008 or in SQL Server 2008 R2
http://support.microsoft.com/kb/2530921/en-us
- Il pezzo forte di questa collezione è invece constituito dalla fix per il seguente annoso problema:
Database data files might be incorrectly marked as sparse in SQL Server 2008 R2 even when the physical files are marked as not sparse in the file system
http://support.microsoft.com/kb/2574699/en-us
Ho detto annoso perché non si tratta di un problema di oggi, ma di ben 3 anni fa, come da “alert” uscito a suo tempo e per cui molti Clienti sono stati direttamente avverti dai loro Technical Account Manager (TAM):
SQL Server reports operating system error 1450 or 1452 or 665 (retries)
http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx
I punti di interesse di questo problema erano (e sono) due:
1) Non si tratta di un problema causato da SQL Server bensì dall’NTFS di Windows in una concomitanza di diversi fattori (sparse files, snapshot, backup,etc.):
…This issue occurs because the file system incorrectly marks a whole database data file as sparse after an alternative stream that was originally marked as sparse is removed…
2) La procedura per venire fuori da questo problema non era (a suo tempo) banale, e comunque Vi sareste trovati con un database “offline” senza possibilità di montarlo:
To resolve this issue in SQL Server 2008 R2, apply the following hotfix, and then bring the database online. When database is brought online, SQL Server checks whether the metadata of the physical database file is marked as sparse in the file system. If it is marked as not sparse, the SQL Server metadata is updated.
Con questa hotfix, invece, nel caso Vi imbattiate nel problema, è sufficiente installarla e quindi far ripartire l’istanza SQL Server; alcune considerazioni aggiuntive sono contenute all’interno dell’articolo, da leggere in caso Vi imbattiate effettivamente in questa casistica.
Anche per questa volta è tutto, buon lavoro a tutti.
Volete sapere come Microsoft gestisce, a livello di logistica ed infrastruttura, i sui Datacenter ? Volete sapere come è possibile stipare 2400 server in un container (si, avete capito bene) merci ?
Il seguente video è molto interessante, poche persone hanno avuto la possibilità di accedervi di persona, direi che vale la pena spenderci i 10 minuti della durata.
http://blog.seattlepi.com/microsoft/2011/08/05/what-is-the-cloud-take-a-video-tour-of-microsoft-data-centers/
Buona visione a tutti.
Ecco l’ultima Cumulative Update (CU) per SQL Server 2008 con SP2:
Cumulative update package 6 for SQLServer 2008 Service Pack 2
http://support.microsoft.com/kb/2582285/en-us
Come al solito, la lista delle (CU) rilasciate per SQL Server 2008 dal rilascio della SP2, è disponibile al seguente link:
The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released
http://support.microsoft.com/kb/2402659/en-us
La prima cosa che salta all’occhio è la vicinanza tra il build number di questa CU (4321) e di quella precedente (4316 - CU(5)), davvero impressionante, segno che il team di sviluppo di SQL Server ha raggiunto, e non da ora, un livello qualitativo notevole.
Database data files might be incorrectly marked as sparse in SQL Server 2008 R2 or in SQL Server 2008 even when the physical files are marked as not sparse in the file system
Per maggiori dettagli Vi consiglio di andare a rileggerVi il precedente “post”:
http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2011/09/13/cumulative-update-review-sql-2008-r2-sp1-cu2.aspx
Assertion failure or other issues occur when you run a DML query against a table or a view that has two indexes in SQL Server 2008
http://support.microsoft.com/kb/2542225/en-us
You receive an incorrect result when you run a query that uses the row_number function in SQL Server 2008
http://support.microsoft.com/kb/970198/en-us
Rileggendo la lista che Vi ho fatto, direi che si tratta di una CU di media importanza, se non siete interessati alla parte di “Analysis Services” e/o “Replica”.
IMPORTANTE: Questo post è il primo di questo tipo, lo scopo è allertare i Clienti e tutti gli interessati su problemi non solo di una certa gravità ma anche di ampia diffusione.
Nelle due ultime settimane ho riscontrato il seguente problema da ben quattro clienti su installazioni SQL Server 2008 e SQL Server 2008 R2:
"Non-yielding Scheduler" error and SQL Server 2008 or SQLServer 2008 R2 stops responding intermittently in Windows Server 2008 or inWindows Server 2008 R2
http://support.microsoft.com/kb/2491214/en-us
Impatto: processo SQL Server non responsivo;
Condizioni di applicabilità: Utilizzo del SQL Native Client 10.0 da parte dell’applicazione;
Evidenza del problema: messaggi nell’ERRORLOG;
Workaround: SI, vedi articolo;
Fix:
Per i dettagli, ovviamente leggete con attenzione l’articolo, in caso di dubbi, problemi o complicazioni contattate il Vostro Technical Account Manager (TAM).