Questo blog è dedicato a chi lavora con Microsoft SQL Server ed è pensato per condividere Best Practices, Informazioni Utili e soprattutto Esperienze acquisite sul campo (e difficilmente ritrovabili in libri, manuali o... veloci ricerche su internet) da parte del nostro team: l’Italian Premier Center for SQL Server.
Il nostro gruppo, parte di Microsoft Italia, lavora a stretto contatto con i clienti Premier che si occupano (direttamente o non) di SQL Server, erogando workshop, chalk and talks, review architetturali e supportando le attività di performance tuning e troubleshooting delle implementazioni più importanti.
Starring:
Buona lettura!
E’ una domanda, assolutamente non provocatoria, che ho ricevuto di recente da alcuni Clienti preoccupati dall’impossibilità di sfruttare a pieno il non trascurabile investimento in un hardware di ultima generazione, magari server dotati delle recenti CPU “Westmere” di Intel con ben 10 Core per CPU. Ovviamente, nel contesto di questo articolo faremo implicito riferimento alle sole architetture “x64”, considerando anche i processori forniti da AMD, oltre che a quelli di INTEL.
Accanto a questa domanda, eccone un'altra che da un po’ di anni non sentivo, e cioè: “E’ consigliato disabilitare l’Hyper Threading ?” Nonostante esistano moltissimi articoli e altro materiale su Internet che dibattono sull’argomento, un punto fermo non esiste e la prima banale risposta a questa seconda domanda è la più odiosa che spesso mi sento rivolgere da inglesi e americani: “It depends !” J . Prometto che in prossimo “post” cercherò di chiarire questo famoso punto certo.
In questo caso la domanda è più pertinente del solito perché nel caso di un server con 4 CPU, ognuna con 10 CORE e dotate di Hyper Threading, il numero di CPU “logiche” disponibili per SQL Server (e Windows) arrivano ad un totale di (4 x 10 x 2) = 80 !
L’idea, non completamente corretta, del mio Cliente era di disabilitare l’Hyper Threading per abbassare il numero di CPU “logiche” da 80 a 40 in modo da rientrare nel limite delle 64 CPU logiche tanto, come è convinzione diffusa, (1) più di 64 non sono sono gestite e (2) l’Hyper Threading non dà alcun vantaggio in SQL Server.
Per quanto riguarda il primo punto relativo all’ Hyper Threading, mi spiace ma dovrete aspettare un successivo post, qui mi limito a riportare due semplici considerazioni basata su un discreto numero di anni di esperienza sul campo in questo settore:
Ora veniamo al punto principale della questione e quindi al soggetto di questo articolo, e cioè il discorso relativo al numero massimo di CPU/CORE: SQL Server 2008 supporta più di 64 CPU “logiche” ? In che condizioni ? Su quali versioni ?
NOTA per l’autore: ricordare a Microsoft di riportare in queste tabelle anche l’informazione relativa ai CORE e alle CPU “logiche” onde evitare ulteriore confusione da parte dei Clienti;
Quindi, ricapitolando, con Windows Server 2008 R2 e SQL Server 2008 R2 è possibile arrivare a sfruttare a pieno fino ad un totale di 256 CPU “logiche”, inteso come numero ottenuto moltiplicando il numero di “SOCKET” al numero di “CORE” per ogni “SOCKET”, infine moltiplicando il tutto per due in caso di Hyper Threading, ad esempio:
IMPORTANTE: In particolare riferimento all’ultima ipotesi numerica sopra riportata, e considerato l’annuncio sia da parte di INTEL che di AMD di nuove CPU con ancora maggiori numeri di “CORE”, è altamente probabile, ma non sicuro/ufficiale, che il numero massimo di CPU “logiche” attualmente supportato sia innalzato prima dell’uscita della prossima versione “Server” di “Windows”, presumibilmente con il rilascio di una nuova “Service Pack”.
Ci sono alcune considerazioni aggiuntive che, in questi scenari da più di 64 CPU “logiche”, devono essere fatte:
Process name
Use more than 64 CPUs
SQL Server Database Engine
Yes
Reporting Services
No
Analysis Services
Integration Services
Service Broker
Full-Text Search
SQL Server Agent
SQL Server Management Studio
SQL Server Setup
In un prossimo post proverò ad addentrarmi nei meccanismi di SQL Server che riguardano le architetture NUMA, il famigerato parametro MAXDOP, le relative ottimizzazioni ed ai problemi che ne possono scaturire.
Buon lavoro a tutti.
-Igor Pagliai-
Post un po’ anomalo questo, ma visto che ultimamente sia clienti che colleghi mi hanno girato, in varie forme, la seguente domanda, perché non scriverci sopra due righe così la prossima volta girerò direttamente un link ?
La domanda è questa:
Perché l’acronimo SQL non viene pronunciato in stretta osservanza alla pronuncia inglese ?
In effetti è vero, il modo in cui viene pronunciato tale acronimo, che sta per “Structured Query Language” corrisponde al termine “SEQUEL” e non “SQL”.
Dopo un attimo di smarrimento iniziale mi sono ricordato di quanto mi aveva detto il mio vecchio professore di informatica delle scuole superiori (grazie prof.Vasellini ! :-) ) e da buon ex-IBM mi svelò il mistero i cui dettagli potete trovare su Wikipedia e di cui qui riassumo la frase chiave:
http://it.wikipedia.org/wiki/SQL
“….L' SQL nasce nel 1974 ad opera di Donald Chamberlin, nei laboratori dell'IBM. Nasce come strumento per lavorare con database che seguano il modello relazionale. A quel tempo però si chiamava SEQUEL (la corretta pronuncia IPA è [ˈɛsˈkjuˈɛl], quella informale [ˈsiːkwəl]). Nel 1975 viene sviluppato un prototipo chiamato SEQUEL-XRM; con esso si eseguirono sperimentazioni che portarono, nel 1977, a una nuova versione del linguaggio, che inizialmente avrebbe dovuto chiamarsi SEQUEL/2 ma che poi divenne, per motivi legali, SQL.”
Che dire ? Grazie Big Blue…. :-)
--Igor Pagliai—
Migrare reporting services e' una operazione ben documentata. Oggi vedremo come, con poche righe di codice, sia possibile esportare una istanza di reporting services su file system. Non entrero' nel dettaglio di come collegarsi e utilizzare il web service di reporting services (per dettagli vedere qui: http://msdn.microsoft.com/en-us/library/bb522713.aspx). Mi limitero' a utilizzare questi due metodi che fanno al caso nostro:
Il primo metodo e' in grado di restituire tutti gli oggetti di una istanza di RS, il secondo estrae il contenuto come array di bytes. Salvando su file system il contenuto noteremo che si tratta dello stesso XML (o meglio, RDL) che compone il report.
Il codice a questo punto e' molto semplice (qui sotto ne pubblico uno spunto per una eventuale implementazione):
RS2010.ReportingService2010SoapClient svc = new RS2010.ReportingService2010SoapClient(); svc.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation; svc.Endpoint.Address = new System.ServiceModel.EndpointAddress(args[0]); RS2010.TrustedUserHeader tuh = new RS2010.TrustedUserHeader(); RS2010.CatalogItem[] items = null; // Lettura di tutti gli oggetti del server svc.FindItems(tuh, "/", RS2010.BooleanOperatorEnum.And, new RS2010.Property[] { new RS2010.Property() { Name = "Recursive", Value = "true" } }, new RS2010.SearchCondition[] { }, out items); #region Extract all if (fExtractAll) { Console.WriteLine("Extracting all items"); byte[] bBuffer2; for (int i = 0; i < items.Length; i++) { if (items[i] is RS2010.CatalogItem) { RS2010.CatalogItem ci = (RS2010.CatalogItem)items[i]; if (ci.TypeName == "Folder") continue; try { Console.WriteLine("Extracting " + ci.Path); // Estrazione definizione dell'oggetto svc.GetItemDefinition(tuh, ci.Path, out bBuffer2); int idx = ci.Path.LastIndexOf('/'); string strPath = ci.Path; if (idx > 0) { strPath = strPath.Substring(0, idx); strPath = strPath.Replace('/', System.IO.Path.DirectorySeparatorChar).Substring(1); } else { strPath = ""; } System.IO.DirectoryInfo di = new System.IO.DirectoryInfo( strBaseDirectory + System.IO.Path.DirectorySeparatorChar + strPath); if (!di.Exists) di.Create(); string strFileName = di.FullName + System.IO.Path.DirectorySeparatorChar + ci.Name; if (ci.TypeName == "Report") strFileName += ".rdl"; else strFileName += ".xml"; using (System.IO.FileStream fs = new System.IO.FileStream( strFileName, System.IO.FileMode.Create, System.IO.FileAccess.ReadWrite, System.IO.FileShare.Read)) { fs.Write(bBuffer2, 0, bBuffer2.Length); } } catch (Exception e) { Console.WriteLine(e.ToString()); } } } } #endregion
Un paio di note:
Happy coding,
Francesco Cogno
Salve, sicuramente sarete al corrente del Security Bulletin “MS11-049” , uscito circa due settimane fa, e sono altrettanto sicuro che Vi sarete detti: “Anche SQL Server 2008, dopo ben 3 anni di assoluta immunità dai bug di sicurezza, ha avuto la sua prima vulnerabilità”.
Mi dispiace per gli amici di Oracle, IBM DB2 e MySQL, ma dovranno aspettare ancora prima di pronunciare una frase del genere, dato che il Security Bulletin in oggetto non riguarda il “Database Engine” di SQL, bensì una sola DLL che fornisce la funzionalità di editing XML ad alcune delle console della famiglia dei prodotti SQL Server che Vi potreste trovare ad installare.
Ma andiamo con ordine partendo dall’articolo di riferimento per il Security Bulletin “MS11-049”:
MS11-049: Vulnerability in the Microsoft XML Editor could allow information disclosure: June 14, 2011
http://support.microsoft.com/kb/2543893/en-us
Microsoft Security Bulletin MS11-049 - Important
Vulnerability in the Microsoft XML Editor Could Allow Information Disclosure (2543893)
http://www.microsoft.com/technet/security/bulletin/MS11-049.mspx
Il file/dll coinvolta è la "Microsoft.xmlEditor.dll", originariamente la vulnerabilità è stata scoperta nella versione 2008 di Visual Studio: tramite questa vulnerabilità, è in teoria possibile copiare un file locale verso una destinazione remota, nel caso si riesca a far aprire all'utente un vile XML appositamente costruito.
Questo problema di sicurezza, quindi, non riguarda il database engine di SQL Server (SQLSERVR.EXE), quindi la Vostra installazione è ancora “sicura”, a meno che non abbiate installato, in locale al/ai server, una delle console dei prodotti della famiglia “SQL Server” che si basano su Visual Studio: è bene ricordare che, in genere, è sconsigliato installare qualsivoglia tipo di console sui server di produzione SQL, ivi inclusi il Management Studio e/o Il Business Intelligence Development Studio (BIDS per gli amici). Questo Security Bulletin è un ulteriore buon motivo per seguire questa best practice, quindi, ma è anche un avvertimento affinchè la relativa hotfix sia installata non solo sui server di produzione/test/svilluppo, ma anche sui desktop degli Amministratori SQL dove è normale prassi installare le console di gestione di SQL Server.
Un semplice consiglio: se non siete sicuri di avere o meno una delle console di SQL Server affette dal problema di sicurezza specifico, fate una semplice “File Search” per la DLL coinvolta e verificate in maniera inoppugnabile; fate anche attenzione al nome del file, ci sono varie DLL dal nome simile, ma quella che a Voi interessa è solamente “Microsoft.xmlEditor.dll”.
Forse perché è il primo “Security Bulletin” in 3 anni per SQL 2008, ed il terzo per SQL 2005 in 6 anni, e quindi i Clienti SQL non ci sono abituati, ma il rilascio di questo “Bulletin” ha generato alcune domande che riassumo qui di seguito, unitamente alle relative risposte, in modo che tutti possano beneficiarne:
- Q1: Come faccio a sapere quale fix installare per una certa “build number” di SQL Server ?
- Q2: Cosa significano GDR e QFE ?
Description of the standard terminology that is used to describe Microsoft software updates
http://support.microsoft.com/kb/824684/en-us
- Q3: Se l’unico file impattato dal bug di sicurezza è la DLL “Microsoft.xmlEditor.dll” ed è poco più di 1MB, perché sia i pacchetti GDR che QFE sono diverse centinaia (a seconda della versione) di MB ?
Anche per questo post è tutto, per il primo articolo su un security bug di SQL 2008 dovrete attendere ancora ! :-)
--Igor Pagliai--
Salve, questo mese l’oggeto del presente post è passare in rassegna le hotfix contenute nelle ultima “Cumulative Update” (CU8) per SQL 2008 R2 e gli eventuali punti di attenzione, avvertenze ed eventuali problemi. Prima di andare avanti, però, è bene ricordare la policy ufficiale Microsoft menzionata nel post seguente:
http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2011/05/24/devo-installare-l-ultima-cumulative-update-cu-di-sql-server-un-nuovo-servizio-dal-gruppo-italian-premier-center-for-sql-server-solo-per-i-clienti-italiani.aspx
L’articolo ufficiale del presente aggiornamento è il seguente;
Cumulative Update package 8 for SQL Server 2008 R2
http://support.microsoft.com/kb/2507770/en-us
La “build” di questo aggiornamento è la “10.50.1797.0”, come al solito potete trovare la lista completa dei precedenti aggiornamenti al seguente link:
The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released
http://support.microsoft.com/kb/981356
Al momento del download della specifica CU, attenzione che avrete più di un file a disposizione, assicuratevi di scaricarli tutti e provvedere eventualmente all’installazione in base ai componenti installati; la presente CU contiente infatti i seguenti pacchetti (per x64):
Per quanto riguarda questo aggiornamenti, segnaliamo le due seguenti importanti hotfix:
FIX: "A time-out occurred while waiting for buffer latch" error when many transactions concurrently update a database in SQL Server 2008 R2 if the database uses the snapshot isolation level
http://support.microsoft.com/kb/2545989/en-us
FIX: A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database
http://support.microsoft.com/kb/2522893/en-us
Nel dettaglio:
NOTA: Lo stesso problema è presente anche in SQL Server 2008 SP1.
Ricordiamo inoltre che:
Anche per questa CU è tutto, buon lavoro a tutti.
-- Igor Pagliai--
Salve, avevamo promesso che su questo blog non saremmo mai scesi nel banale con post del tipo “E’ uscita la Service Pack tal dei tali, etc….”, non Vi preoccupate, scrivendo questo post non stiamo violando tale impegno, leggete e capirete che quello che sto per dirVi non è immediatamente deducibile dal marasma di link, readme, release notes e materiale vario che sta inondando Internet da questa notte.
Dunque, partiamo con la cosa fondamentale, il link per il download della SP1:
Microsoft® SQL Server® 2008 R2 Service Pack 1
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727
Provate ad aprire questo link e Vi troverete una sorpresa:
E’ quello che sembra ? Posso confermarVi di si, dopo aver espanso i primi due package con il solito flag “-x”: questi due pacchetti, uno per piattaforma x86 ed uno per x64 per Itanium come è noto non c’è console SQL L, contengono gli aggiornamenti necessari per fissare il solo “SQL Server Management Studio e tutte le sue “dipendenze”.
La domanda meno banale è: “Ma se devo aggiornare un server dove sono installati sia il Management Studio sia il motore SQL, devo installare entrambi i pacchetti ?” In questo caso, il pacchetto principale è sufficiente per aggiornare tutto quanto, non c’è bisogno di una doppia installazione.
Altra questione che causa molta confusione è quella riguardante l’inclusione, in generale, delle varie Cumulative Update (CU) in ogni Service Pack (SP): dato che al momento della “chiusura” della lista delle hotfix da includere in una certa SP al momento del rilascio della suddetta SP passa del tempo, il ciclo di sviluppo delle CU va avanti, quindi una certa SP non include tutte le CU rilasciate al momento del rilascio della SP stessa. Frase un po’ contorta, lo ammetto, per dire che in questo caso la SP1 di SQL Server 2008 R2 include solamente le CU dalla (1) alla (6), ma non la (7) e la (8) ! Se avete, quindi, già installato la CU7 o la CU8 per SQL 2008 R2, è caldamente consigliato aspettare il rilascio della CU1 per SQL Server 2008 R2 SP1 , attenzione che questa CU è differente dalla dalla CU1 per SQL Server 2008 R2 in quanto si riferiscono ad una “base” diversa.
IMPORTANTE: La SP1 di SQL 2008 R2 contiene anche la fix necessaria per fissare il security bug MS11-049 di recente rilascio, magari se non lo avete già fatto andate a rileggerVi il mio precedente post sull’argomento per capire se effettivamente avete bisogno di questa security fix:
Il primo Security Bug per il Database Engine di SQL 2008 ?
http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2011/06/27/il-primo-security-bug-per-il-database-engine-di-sql-2008.aspx
Terzo ed ultimo punto di attenzione, ricordateVi che il pacchetto di aggiornamento di ogni Service Pack di SQL (SP), non include alcune componenti “accessorie” che devono essere quindi scaricate separatamente, come ad esempio:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26728
http://www.microsoft.com/download/en/details.aspx?id=9071
NOTA: Il link dei “ BooksOnline” è ancora in fase di aggiornamento, controllare la data e la versione di rilascio prima di scaricare.
Infine, è sempre bene ricordare che:
The SQL Server Service Cannot Start after You Uninstall SP1 for SQL Server 2008 R2 if a UCP Exists in the Instance of SQL Server
http://social.technet.microsoft.com/wiki/contents/articles/microsoft-sql-server-2008-r2-sp1-release-notes.aspx
How to slipstream SQL Server 2008 R2 and a SQL Server 2008 R2 Service Pack 1 (SP1)http://blogs.msdn.com/b/petersad/archive/2011/07/13/how-to-slipstream-sql-server-2008-r2-and-a-sql-server-2008-r2-service-pack-1-sp1.aspx
Credo sia tutto, ho in previsione di aggiornare questo post in caso si presentino “Known Issues” sulla SP1 di SQL 2008 R2, quindi Vi consiglio di controllare periodicamente questo blog :-) ....
Buon lavoro a tutti !
Salve, periodo prolifico questo mese di Luglio, non appena una settimana fa è stata rilasciata la prima “Service Pack” (SP) per SQL Server 2008 R2, e già abbiamo la prima “Cumulative Update” (CU):
Cumulative update package 1 for SQL Server 2008 R2 Service Pack 1
http://support.microsoft.com/kb/2544793/en-us
NOTA: La build number della CU1 per SP1 di SQL 2008 R2 è “10.50.2769.0”.
Se non avete già letto il mio precedente post sulla SP1 di SQL 2008 R2, Vi invito a farlo ora a questo link per meglio comprendere quello che dirò in seguito:
http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2011/07.aspx
Ed eccoci alla domanda cruciale che molti clienti fanno ad ogni rilascio della prima CU per ogni SP: “Ho appena installato la SP1 di SQL 2008 R2, devo anche installare la CU1 ?”
L’informazione fondamentale da tener presente è che la SP1 di SQL 2008 R2 contiene tutte le CU (per SQL 2008 R2 RTM) fino alla CU6 compresa, lasciando fuori quindi la CU7 (build 10.50.1777.0) e la CU8 (build 10.50.1797.0):
A supported cumulative update package is now available from Microsoft. However, it is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems. This cumulative update package may receive additional testing. Therefore, if you are not severely affected by any of these problems, we recommend that you wait for the next SQL Server 2008 R2 service pack that contains the hotfixes in this cumulative update package.
OK, smarcata la CU1 per SQL 2008 R2 con SP1, diamo un’occhiata a quello che contiene la CU5 per SQL 2008:
Cumulative update package 5 for SQL Server 2008 Service Pack 2
http://support.microsoft.com/kb/2555408/en-us
NOTA: La build number della CU5 per SP2 di SQL 2008 è “10.00.4316.0”.
La prima importante informazione è che questa CU5 include la fix per il recente security bulletin MS11-049, se state sobbalzando sulla sedia perché pensate che sia il primo security bug di SQL 2008, allora non avete letto uno dei nostri precedenti post J:
Per quanto riguarda la lista dei problemi fissati, ci sono diversi aggiornamenti molto importanti:
Incorrect results when you run an INSERT SELECT UNION ALL statement in SQL Server 2008
http://support.microsoft.com/kb/2530921/en-us
A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database
Recovery takes longer than expected for a database in a SQL Server 2008 environment
http://support.microsoft.com/kb/2524743/en-us
Infine, per gli amanti della replica di tipo transazionale e “merge”, un prezioso consiglio: installate questa CU perché ci sono due grossi problemi risolti relativi alla convergenza della prima e ad un crash dell’agent di replica per la secondo.
Buon lavoro a tutti….
Come promesso, eccoci al primo appuntamento dedicato ai Workshop e ai Servizi a Valore di cui si occupa il nostro gruppo.
Ne approfittiamo subito per parlare di un corso appena introdotto nel nostro catalogo.:
SQL Server 2008 for Relational Database Developers
Si tratta di un corso (3giorni base + 1 giorno di corso avanzato opzionale) dedicato agli sviluppatori T-SQL, che fornirà ai partecipanti le conoscenze necessarie per programmare con SQL Server 2008.: i toolset, best practices di sviluppo, le nuove features a disposizione. Il corso comprende presentazioni, dimostrazioni e sezioni hands-on-lab che aiuteranno i partecipanti a rinforzare i concetti appresi e a sviluppare le conoscenze acquisite.
Oltre a questo nuovo corso dedicato agli sviluppatori T-SQL, il nostro catalogo include altri due workshop dedicati a chi non copre direttamente il ruolo di Database Administrator, ma che in qualche modo deve interfacciarsi con l'engine per svolgere le proprie attività.
SQL Server 2008 for Non-SQL Administrators
Si tratta di un corso di 2 giorni dedicato alle organizzazioni che non hanno risorse dedicate all'amministrazione di SQL Server, ma che devono supportare istanze di SQL Server critiche, come backend di applicazioni come Microsoft Sharepoint, Microsoft System Center Configuration Manager (SCCM), Microsoft System Center Operations Manager (SCOM), Microsoft BizTalk Server.
Il corso fornisce un introduzione al SQL ServerDatabase Engine (configurazioni base, maintenance plans, performances) ed include hands-on-lab per fornire ai partecipanti la confidenza necessaria per gestire un istanza SQL Server 2008.
SQL Server for SharePoint Administrators
Questo workshop è dedicato agli amministratori di Microsoft Windows Sharepoint Services 3.0, Microsoft Office Sharepoint Server 2007, Sharepoint Foundation 2010 e Sharepoint Server 2010 e fornisce le conoscenze e le competenze necessarie per configurare correttamente e gestire (monitoraggio e piani di manutenzione) le istanze di SQL Server implementate come backend. Il corso include anche una sezione dedicata al performance troubleshooting, e discute le opzioni disponibili per l'implementazione di scenari di High Availability e Disaster Recovery.
Se siete interessati, o desiderate ricevere maggiorni informazioni,vi consigliamo di contattare il vostro riferimento Microsoft Premier.
A presto!
- Beatrice Nicolini -
Sebbene non sia un problema recente, ho deciso di creare questo post sul nostro blog per cercare di fare chiarezza su un problema strisciante, sebbene non grave, che da diverso tempo affligge SQL Server 2008 dalla “Service Pack 1” in poi e che, fino ad oggi, non ha avuto l’onore di avere un articolo di Knowledge Base Microsoft ufficiale. Cercando di visualizzare uno degli “Standard Reports” dal SQL Management Studio, Vi è mai capitato di ricevere il seguente errore ?
A beneficio dei motori di ricerca, includo anche la stringa completa del messaggio di errore sicuro che i motori di indicizzazione renderanno questo post visibile:
The file 'Microsoft.ReportViewer.WinForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' cannot be opened. Do you want to remove the reference from the Recent list?
In base alla mia esperienza diretta, questo problema ha iniziato a manifestarsi a partire dalla CU3 per SQL Server 2008 SP1, è continuato con la CU4, è stato apparentemente risolto con il rilascio della CU5 ma si è ripresentato sia in CU8 per SP1 che con la CU2 per SP2. Al momento, come ho avuto modo di testare direttamente, il problema non si è manifestato con SQL Server 2008 R2 in versione definitiva, dove avrebbe dovuto essere fissato (e così sembra almeno fino ad ora) dato che in alcune versione pre-release l’inconveniente era stato riscontrato:
https://connect.microsoft.com/SQLServer/feedback/details/523972/sql-2008-r2-nov-ctp-management-studio-reports-fail-on-opening
La difficoltà nell’accertare con certezza quali CU e quali SP sono afflitte dal problema sta nel fatto che i Clienti si trascinano dietro il problema da tempo e non subito se ne accorgono, e dal fatto che è un problema che affligge solo la console di SQL ed essendo questa installata in giro per molte macchine, non solo sui server di produzione dove a dire la verità non dovrebbe stare. In ogni modo, la soluzione a questo problema è molto semplice e non richiede alcun reboot, è sufficiente (re)installare la seguente componente su tutte le macchine dove si trova installato il “SQL Server Management Studio (SSMS)”, non necessariamente quelle dove è installato il database engine di SQL Server 2008:
Microsoft Report Viewer 2008 SP1 Redistributable
http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=bb196d5d-76c2-4a0e-9458-267d22b6aac6
-- Igor Pagliai --
Salve a tutti,
L’argomento di oggi ha, per chi lavora con SQL Server da molti anni, un significato particolare perché va ad impattare uno degli aspetti più granitici ed immutabili nel tempo, sai che è così da (n) versioni e (m) anni, con tutti i problemi del caso, ed un bel giorno Ti svegli ed alcune di queste certezze non ci sono più ! :)
L’oggetto della mia meraviglia, e del presente post, è un nuovo meccanismo per la gestione “dinamica” delle statistiche, introdotto con la SP1 di SQL Server 2008 R2 e che sarà anche nella prossima versione di SQL Server (nome in codice “Denali”): quello che ora cambia, se abilitate il trace flag –T2371, è la magica soglia (di modifiche ad una colonna/e di una tabella) oltre la quale una certa statistica viene aggiornata automaticamente da SQL Server.
Fino a ieri, la regoletta che potete trovare in varie documentazioni su Internet era la seguente, per l’aggiornamento dinamico di una statistica (“recompilation threshold”):
Con varie sfaccettature, questa soglia statica (cioè il numero “20%”) non è mai cambiata dalla sua introduzione in SQL 7.0, parliamo quindi di circa 13 anni fa !
Il problema di questa percentuale fissa è che, su tabelle molto grandi come numerosità di righe, tale soglia è troppo alta e si rischia che SQL Server non scateni mai l’aggiornamento automatico delle statistiche, con conseguenti problemi di accuratezza dei “query plan” generati.
Per farVi un esempio concreto, immaginate la seguente situazione:
Riuscite a vedere il problema? Con la soglia fissa al 20%, le statistiche di questa tabella saranno aggiornate non prima di 20gg (1M di record x 20gg = 20M record =circa il 20% delle righe della tabella) !!!
Con tabelle di queste dimensioni, spesso partizionate, la soglia classica di aggiornamento delle statistiche non basta mai, per cui è altrettanto spesso necessario inserire un “job” del “SQL Agent” che periodicamente (non certo ogni 20gg !) forzi l’aggiornamento delle statistiche.
Con questo nuovo meccanismo, la soglia di auto-aggiornamento delle statistiche non è più fissa al 20% ma varia, nella sua percentuale, dinamicamente in base al numero di record di una tabella: purtroppo al momento l’informazione esatta su come varia questa percentuale non è di carattere pubblico :), ma facendo qualche test numerico è facilmente intuibile ;), il concetto importante da considerare è che più record ha una tabella e più si abbassa la percentuale oltre la quale scatta l’aggiornamento delle statistiche, ecco alcuni esempi:
Per chi non avesse ancora indovinato la funzione matematica che governa la soglia dinamica, rendo ancora più facile la cosa con il seguente grafico tratto da un post del collega Juergen Thomas (grazie !), il nostro maggiore esperto di SAP:
NOTA: Non Vi aspettate che questo meccanismo entri in azione su tabelle piccole, come potete vedere dal grafico la soglia è identica fino a (circa) 25000 righe, poi il comportamento cambia.
OK, sembra tutto fantastico, ma qualcuno dovrebbe chiedersi: pechè devo abilitare un “trace flag” per avere questo meccanismo ed invece non è un comportamento di default di SQL Server?
La cosa ha molto senso ed è una consuetudine del gruppo di sviluppo di SQL Server: ogni modifica al codice e/o nuova funzionalità che cambi il comportamento dell’engine di SQL deve essere attivata esplicitamente (e consciamente), ed i “trace flags” adempiono proprio a questo scopo, oltre ovviamente a molti altri. Vorrei poi puntualizzare che non è detto che questo meccanismo porti beneficio a tutti i clienti: è vero che le statistiche saranno aggiornate più frequentemente, ma ciò vuol dire anche un maggiore numero di “ricompilazioni” delle stored procedure e delle query adhoc, quindi (tra le altre cose) un maggiore utilizzo della CPU.
Come si abilita in maniera “permanente” questo meccanismo ? Và inserito il trace flag –T2371 negli “startup parameters” dell’istanza SQL utilizzando il tool “SQL Configuration Manager”, tab “Advanced” nelle proprietà del servizio SQL Server, riga “startup parameters”: attenzione, dovete inserire –T2371 in coda a quello che già c’è, pre-pendendo un punto e virgola (;). Dopo l’inserimento del trace flag di SQL tramite SQL Configuration Manager, e relativo reboot, verificate che nella parte iniziale dell’ERRRORLOG compaia una cosa del genere:
Registry startup parameters:
-d C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\DATA\master.mdf
-e C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\Log\ERRORLOG
-l C:\SQL2008R2\MSSQL10_50.SQL2008R2\MSSQL\DATA\mastlog.ldf
-T 2371
IMPORTANTE: Sebbene dobbiate inserire -T2371 senza spazio tra –T e 2371 negli “startup parameters”, nell’ERRORLOG lo spazio invece compare, non Vi preoccupate è normale e deve essere così altrimenti avete commesso un errore nella configurazione;
Anche per oggi è tutto, mi raccomando: testate, testate ed ancora testate…..
Moltissimi DBA conoscono benissimo la seguente regola:
“You can migrate a database that was created in an earlier version of SQL Server to a later version. This is a typical database upgrade and is supported. However, you cannot migrate a database that was created in a later version of SQL Server to an earlier version.”
Per moltissimo tempo la regola effettivamente non ha avuto eccezioni ed anche Microsoft, in moltissimi documenti e per bocca del suo Supporto, ha fatto di tutto per diffondere questo messaggio.
NOTA: Ho sottolineato il termine versione perché è proprio qui che si nasconde l’ambiguità !
Ebbene, è un altro falso mito o falsa leggenda metropolitana, ne esistono molti/molte in giro come anche il nostro buon amico Paul Randal (http://www.sqlskills.com/BLOGS/PAUL/) ha largamente documentato in questo documento di notevole interesse:
http://www.sqlskills.com/BLOGS/PAUL/post/T-SQL-Tuesday-11-Misconceptions-about-EVERYTHING!!.aspx
Beh, almeno un’eccezione esiste, come Vi illustrerò tra poco e sono sicuro che non è l’unica, magari ne parlerò in un altro blog.
Dunque, il documento che ha attirato la mia attenzione è il seguente e tratta del supporto “esteso” al numero massimo di partizioni per singola tabella, passato da 1000 a 15000 con la SP2 di SQL Server 2008 e la SP1 di SQL Server 2008 R2:
http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/Support_for_15000_Partitions.docx
La parte interessante, per quanto riguarda questo mio post, è la seguente:
SQL Server 2008 SP2 databases with support for 15,000 partitions have version number 662, while SQL erver 2008 R2 RTM supports databases with version numbers up to and including 661.
Quello che in pratica è scritto qui smentisce la regola iniziale che ho citato e che è riportata anche nel sudetto documento: in questo specifico caso, non è possibile migrare un database da una istanza SQL Server 2008 SP2 (build 10.0.x.x) ad una più recente (altro termine ambiguo !) SQL 2008 R2 SP1 (build 10.50.x.x), eppure la build è superiore !
L’ambiguità sta proprio nel termine “version” che ho sottolineato più volte, è necessario rifrasare la regola iniziale nel seguente modo:
”You can migrate a database that was created in an earlier version of SQL Server to a later version to a later version if and only if this later version understand/support the existing database version number”
In questo caso specifico:
Un interessante ed importante corollario è il seguente:
“I can migrate a database that was created in a later version of SQL Server to an earlier version if and only if the database version number supported by the target instance is the same or higher of the one stamped in the database itself by the source instance”
Un esempio ? Se avete un database su SQL Server 2008 SP1 potete migrarlo/spostarlo su una nuova/differente istanza SQL Server 2008 RTM, quindi con un differente livello di Service Pack; lo stesso esempio si può fare con SQL Server 2005, in pratica le Service Pack, in genere, ma non è sempre garantito che sia così, non innalzano la “DB version” di un database e se così è, il relativo database può essere portato su una versione precedente (differente livello di SP ma stessa versione) di SQL Server.
Un’ultima annotazione: come si fa a controllare la “DB version” di un database ?
Il modo più semplice è fornito dal seguente comando non documentato (e quindi non supportato ! :)) ma ben noto a molti DBA:
dbcc dbinfo('AdventureWorks2008')
NOTA: per visualizzare a video il relativo output, dovete prima eseguire il comando DBCC TRACEON(3604) altrimenti il tutto finirà nell’ERRORLOG dell’istanza SQL corrente.
Ecco un estratto di cosa compare a video:
DBINFO STRUCTURE:
DBINFO @0x0000000012F5D780
dbi_dbid = 5 dbi_status = 65544 dbi_nextid = 647673355
dbi_dbname = AdventureWorks2008R2 dbi_maxDbTimestamp = 52000 dbi_version = 661
dbi_createVersion = 661 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2010-06-28 15:31:23.283
La parte evidenziata in rosso fornisce l’informazione che cerchiamo, in questo caso la “DB version” è 661 !
Se invece avete fatto un backup del database che volete esaminare, potete anche esaminare una delle tabella di sistema del database MSDB con la seguente semplice query:
select backup_set_id,database_version from msdb.dbo.backupset where database_name = 'AdventureWorks2008R2'
Come ulteriore possibilità, se non avete le necessarie informazioni in MSDB ma solamente il file BAK, ecco il comando che fa per Voi:
restore headeronly from disk = 'C:\temp\DB_BACKUP.bak'
L’informazione che cerchiamo è immediatamente visibile nell’output del comando:
Anche per questo post è tutto, buon lavoro a tutti !
---Igor Pagliai ---
Vi siete mai chiesti qual’e’ l’overhead del protocollo TDS? Il protocollo e’ ben documentato a questo indirizzo: http://msdn.microsoft.com/en-us/library/dd304523(v=PROT.13).aspx, tuttavia in questo post seguiremo un approccio differente da quello accademico: snifferemo i pacchetti (TCP) fra Management Studio e SQL Server per analizzarli ex-post. In questo modo avremo un pretesto per analizzare il comportamento del protocollo TDS.
Per la nostra analisi utilizzeremo Microsoft Network Monitor (per gli amici, NetMon) disponibile qui: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4865. Tramite NetMon e’ possibile intercettare tutti i pacchetti in ingresso ed in uscita da una interfaccia di rete.
Scriviamo un codice TSQL abbastanza prolisso da essere di dimensioni significative. Nel mio caso mi sono affidato al comment per ingrossare la dimensione del file.
Salviamo il codice dentro un file. Importante: assicuratevi che il formato sia ANSI (cfr 2.2.1.3 SQL Batch) altrimenti avrete dei numeri errati. Guardate la dimensione del file (proprieta’) per avere la dimensione del “payload utile” che invieremo a SQL Server:
La dimensione che ci interessa e’ il campo Size (non Size on disk). Nell’esempio il mio codice occupa 6.145 bytes. Essendo ANSI dobbiamo moltiplicare per 2 il numero di bytes occupati per avere l’occupazione Unicode. Otteniamo 12.290 bytes.
La dimensione di default del pacchetto TDS e’ 4096 (4KB). Con questa impostazione saranno necessari almeno 4 pacchetti (16.384 bytes) per inviare il SQL Batch message da SSMS a SQL Server. Supponiamo di voler minizzare il numero di pacchetti inviati per cui decidiamo di negoziare un packet size di 32.767 bytes (massimo consentito). Per farlo serve modificare l’impostazione del server SQL? La risposta e’ no per cui lasciamo il server al valore di default:
Ma assicuriamoci di modificare lato SSMS il parametro corretto:
Ora attiviamo NetMon ed eseguiamo la query da SSMS. Il risultato (colorato e filtrato) e’ il seguente:
Ho evidenziato i pacchetti da SSMS a SQL. Si nota subito una cosa interessante: benche’ abbiamo impostato il packet size a 32.767 il layer sottostante (TCP) ha comunque deciso di segmentare il messaggio in piu’ pacchetti separati (nel mio caso di 1448 bytes). Cio’ e’ atteso e documentato qui http://msdn.microsoft.com/en-us/library/aa174503(v=SQL.80).aspx:
You can configure the SQL Server packet size, which is the size of the TDS packets. The size of the TDS packets defaults to 4 KB on most clients (DB-Library applications default to 512 bytes), which testing has shown to be the optimal TDS packet size in almost all scenarios. The size of the TDS packets can be larger than the size of the packets in the underlying protocol. If this is the case, the protocol stack on the sending computer disassembles the TDS packets automatically into units that fit into the protocol packets, and the protocol stack on the client computer reassembles the TDS packets on the receiving computer.
Cio’ vuol dire che per calcolare la dimensione totale del nostro messaggio TDS dobbiamo sommare tutti i pacchetti TCP componenti il messaggio TDS. Nell’esempio abbiamo 8 pacchetti TCP da 1448 bytes e 1 pacchetto (l’ultimo) da 736 bytes. In totale, quindi, il messaggio TDS ha dimensione 12.320 bytes.
Ora ripetiamo l’esperimento rinegoziando il network packet TDS a 8.192 bytes. In questo caso saranno necessari 2 pacchetti TDS per inviare il batch di cui sopra:
Sommando i pacchetti (8 da 1448 bytes e 1 da 744 bytes) si ottiene 12.328 bytes.
Nel primo caso abbiamo utilizzato un TDS network packet size di 32.767 bytes e abbiamo effettuato un traffico TCP di 12.320 bytes. Nel secondo caso abbiamo abbassato il network packet size a 8.192 bytes e abbiamo effettuato un traffic TCP di 12.328 bytes. La differenza e’ di 8 bytes.
A cosa sono dovuti? E’ l’header in piu’ dovuto al fatto che abbiamo inviato 2 pacchetti TDS invece che 1. Vi rimando qui per maggiori dettagli http://msdn.microsoft.com/en-us/library/dd340948(v=PROT.13).aspx .
Confrontando con l’occupazione del semplice statement Unicode (12.290 bytes) avanzano 30 bytes. Esclusi gli 8 precedentemente evidenziati ne rimangono ancora 22. Questo valore e’ quello che ci attendiamo per il campo All_Header (cfr http://msdn.microsoft.com/en-us/library/dd304416(v=PROT.13).aspx ). L’esempio cita infatti:
<TotalLength>
<DWORD>16 00 00 00 </DWORD>
</TotalLength>
0x16 in esadecimale equivale esattamente a 22 in decimale.
Aggiungo come riferimento la progressione dell’overhead (dell’header TDS) in percentuale per uno statement di 65.536 bytes in funzione del network packet size. Come si nota anche con un packet size di 512 bytes l’overhead e’ inferiore al 2%.
Happy coding
Sarà un post abbastanza breve, ma l’argomento sono sicuro che interesserà molte persone dato che la cosa non è ufficialmente riportata in nessuna documentazione Microsoft, almeno in quella ufficiale, e riguarda l’utilizzo di un “Mount Point” NTFS come base per la componente MSDTC (Microsoft Distributed Transaction Coordinator) in ambiente Cluster.
Prima di passare all’argomento principale di questo post, è bene ricordare che in Windows Server 2008 R2:
- Mentre con SQL Server 2005, in ambiente Cluster, l’installazione della componente MSDTC era propedeutica all’installazione di SQL, in SQL Server 2008 non è più così, anzi, se la componente MSDTC non è richiesta dallo scenario applicativo/architetturale potete assolutamente fare a meno di installarla;
- In Windows Server 2008 Cluster, potete avere quante istanza della componente MSDTC volete, a differenza di Windows Server 2003 dove era possibile averne solo una per l’intero sistema Cluster;
Dunque, qualora Vi venisse la tentazione di utilizzare un “Mount Point” NTFS come disco di base per la componente MSDTC, sappiate che ciò non è supportato e neanche possibile, avrete sicuramente dei problemi ma non subito: all’atto dell’installazione/configurazione dell’MSDTC, nulla sembra indicare nessun tipo di problema, ma nel momento in cui tenterete di eseguire un “failover” manuale (in questo caso) su un altro nodo, l’operazione fallirà e, a seconda di quanti nodi avete e della configurazione adottata, assisterete al tipico fenomeno del “ping pong” delle risorse da un nodo all’altro, fino a che il tutto non tornerà sul nodo iniziale.
Un rapido esame dell’Event Log “Application” del Sistema del Sistema Operativo, rivelerà il seguente messaggio di errore:
A beneficio dei motori di ricerca che presto indicizzeranno questo post, riporto la stringa completa dell’errore:
Source: MSDTC Client 2
Event ID: 4101
Level: Error
Description: The DTC cluster resource’s log file path was originally configured at: \\?. Attempting to change that to: \\?\GLOBALROOT\Device\Harddisk22\Partition1. This indicate a change in the path of the DTC cluster resource’s dependent disk resource. This is not supported. The error code returned: 0x8000FFFF.
Ebbene, dopo alcune verifiche interne quello che l’errore ci dice, e che ovviamente ho sospettato, si è rivelato corretto: l’utilizzo dei “Mount Point” NTFS, in configurazione Cluster, anche sul più recente Windows Server 2008 R2, non è (ancora) supportata.
Esternamente potete trovare traccia di questa cosa sul sito Connect di Microsoft, al link seguente, Vi invito a votare a favore di questo “bug” per far sapere al nostro gruppo di sviluppo per Windows Server quanto questa cosa sia “desiderabile” avere nella prossima versione:
https://connect.microsoft.com/SQLServer/feedback/details/576545/msdtc-fails-to-restart-in-sql-server-2008-r2-clustered-group
Buon lavoro a tutti, al prossimo post.
Il suo nome per esteso è “Deployment Image Services and Management Tool”, ed e’ la seconda volta che mi imbatto in questo tool di sistema operativo per Windows Server 2008 R2:
Si tratta di uno strumento “builtin” accessibile da command line con privilegi amministrativi “elevati” e nel caso specifico l’ho dovuto utilizzare per rimuovere i file di backup dell’installazione della “Service Pack 1” di Windows Server 2008 R2”, il comando dettagliato è il seguente:
DISM.exe /online /Cleanup-Image /spsuperseded
Et voilà, avete appena liberato 2.1 GB sul disco “C:” che fanno sempre comodo !
NOTA: Ovviamente, poi non sarete più in grado di rimuovere la SP1, ma ad oggi non ho ancora avuto notizia di un Cliente che abbia avuto bisogno di farlo.
Ah, si… Vi ho accennato ad un'altra volta in cui mi sono imbattuto in codesto tool, per l’esattezza durante una procedura di “Security Hardening” di una installazione SQL Server per un grosso cliente in italia; ebbene, utilizzando il seguente comando potete completamente rimuovere “Internet Explorer” dalla Vostra installazione (x64):
Dism /online /disable-feature /featurename:Internet-Explorer-Optional-amd64
Carino vero ? :)
Ho testato la cosa ed è assolutamente supportata, c’è solo il problema, in ambiente Cluster, di non poter più riuscire a vedere in locale il “Validation Report”, ma si può facilmente ovviare da remoto.
Un post veloce per mostrare una funzionalità molto gradita introdotta da SQL Server 2012. Come sicuramente saprete, troppi VLF (virtual log files) possono impattare negativamente le operazioni del transaction log.
Fino SQL Server 2008 R2 bisognava attivamente documentarsi per avere informazioni sul numero di VLF (ad esempio con DBCC LOGINFO). SQL Server 2012, invece, mostra un messaggio di warning nell'error log se, durante la fase di redo, l'engine identifica un database con più di 10000 (si, diecimila) VLF.
Il messaggio è il seguente:
Database testVLF has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
Volete provare voi stessi? Potete usare questo script:
use [master]; GO CREATE DATABASE testVLF; ALTER DATABASE testVLF SET RECOVERY FULL; BACKUP DATABASE testVLF TO DISK='nul'; BACKUP LOG testVLF TO DISK='nul'; USE [master] GO ALTER DATABASE [testVLF] MODIFY FILE ( NAME = N'testVLF_log', FILEGROWTH = 1KB ) GO USE [testVLF] GO CREATE TABLE test(testo CHAR(1200)); GO PRINT 'Inserimento 100 righe...' GO INSERT INTO test SELECT 'somedata'; GO 100 PRINT 'Inserimento massivo righe...'; GO INSERT INTO test SELECT * FROM test; GO 14 DBCC LOGINFO(testVLF); GO USE [master]; GO BACKUP DATABASE [testVLF] TO DISK='I:\Samples\TooManyVLFs\testVLF.bak' WITH COMPRESSION; GO DROP DATABASE testVLF; GO RESTORE DATABASE [testVLF] FROM DISK = N'I:\Samples\TooManyVLFs\testVLF.bak' WITH STATS=1; GO EXEC xp_readErrorLog
Sostanzialmente stiamo creando un database nuovo a cui impostiamo l'autogrowth a 1024 bytes (ma che SQL alzerà al minimo che è 262144 bytes). A questo punto riempiamo il tlog fino a generare almeno 10000 vlf (da cui avremo un tlog di almeno 2.5 GB circa). Ora effettuiamo un backup-drop-restore (potreste dover cambiare il percorso di backup sulla vostra macchina di test).
L'error log riporterà un warning simile a questo:
Nota: non considerate "giusto" il numero di VLF se è minore di 10000. Il valore di 10000 è preso come soglia oltre la quale i VLF sono decisamente troppi. Come sempre, l'unico vero valore giusto è quello che soddisfa le vostre esigenze. In altre parole, testate :). Lo scopo di questo warning è di evidenziare, a beneficio dei DBA, una situazione che sta sfuggendo di controllo.
Oggi continuiamo ad esplorare le novità di SQL 2012 andando a testare una piccola ma importante modifica alla gestione della priorità degli schema lock. Gli schema lock sono quei particolari tipi di lock utilizzati da SQL server per coordinare le operazioni con impatto a livello di schema. Alcuni esempi? Un alter table ovviamente richiede un lock di tipo schema M (modifica). Una select in read uncommitted richiede - sorpresa - un lock di tipo schema S (stabilità). Come potete immaginare (ma come potete confermare leggendo qui: http://msdn.microsoft.com/en-us/library/ms186396(v=sql.105).aspx) i due diversi tipi di lock non sono compatibili fra di loro. In casi in cui non si riesca ad acquisire un lock perché ne esiste uno incompatibile SQL metterà il lock in attesa (o, meglio, il thread che ha richiesto il lock). Appena il lock sarà disponibile SQL risveglierà il thread in attesa e permetterà al lock di acquisire il possesso dell’oggetto.
Questo meccanismo si basa su una coda FIFO che garantisce che tutti i thread avranno la loro occasione di ottenere il possesso dell’oggetto (questo ovviamente a meno di deadlocks ma non ne parleremo ora).
Ad esempio supponendo di avere un lock di tipo S (condiviso) sull’oggetto A avremo una coda di questo tipo:
1: Lock S su A (attivo).
Ora supponiamo che arrivino due richieste X (esclusive) su A. X non è compatibile con S per cui viene messo in attesa.
3: Lock X su A (attesa).
2: Lock X su A (attesa).
Appena il lock X su A verrà rilasciato il primo thread in attesa del lock X su A verrà risvegliato per cui avremo:
1: Lock X su A (attivo).
Se ora arrivasse una richiesta di lock S su A anche lui andrà in coda:
3: Lock S su A (attesa).
NB: Questa è una semplificazione estrema, i lock sono molti di più e gli oggetti su cui si pongono sono vari; tuttavia è sufficiente per lo scopo di questo post.
Questo meccanismo aveva per gli schema locks – fino a SQL 2008 R2 incluso – una deroga: gli Sch-S lock posti negli isolation level read uncommitted e read committed snapshot avevano precedenza sugli altri schema lock (cioè sul Sch-M).
Ciò vuol dire che in una situazione come questa:
3: Lock Sch-M su A (attesa).
2: Lock Sch-M su A (attesa).
1: Lock Sch-S su A (attivo).
All’arrivo di un nuovo Sch-S invece che essere messo in fondo alla coda questo verrà messo in testa:
4: Lock Sch-M su A (attesa).
2: Lock Sch-S su A (attesa).
Come potete ben immaginare in determinate condizioni – ad esempio arrivando continui Sch-S in read uncommitted – i thread in attesa del Sch-M non verrebbero mai risvegliati. Questa situazione è chiamata in letteratura thread starvation.
Ora voi sarete tranquilli perchè non utilizzate l’isolation level read uncommitted. Chi lo usa dopotutto? ;). Però se ci pensate ogni volta che usate il query hint WITH(NOLOCK) lo state facendo! Quindi se voi utilizzate WITH(NOLOCK) siete esposti a questo problema.
Vi do un paio di script per provare il tutto (non in prod, mi raccomando!):
CREATE DATABASE StarvationDemo; GO USE StarvationDemo; GO CREATE TABLE NoLockSample(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Testo NVARCHAR(255)); GO INSERT INTO NoLockSample SELECT name FROM sys.objects; GO 256
Ora aprite tre o quattro finestre di SSMS e lanciate contemporaneamente questo script
USE StarvationDemo; GO DECLARE @cnt FLOAT WHILE 1=1 SELECT @cnt=AVG(CONVERT(FLOAT, ID)) FROM NoLockSample WITH(NOLOCK) WHERE LEFT(Testo, 1) = 'LL';
Notate che usiamo il famigerato NOLOCK.
Se a questo punto, in un’altra finestra, lanciate un banale alter table:
USE [StarvationDemo]; GO ALTER Table [dbo].[NoLockSample] ADD PocaRoba BIT; GO
Attenderete parecchio :). Se controllate i lock presenti:
SELECT l.request_mode,r.transaction_id, r.blocking_session_id, r.wait_type, r.start_time, l.request_lifetime--, r.*, l.* FROM sys.dm_tran_locks l inner join sys.dm_exec_requests r on l.request_session_id = r.session_id where request_mode in ('Sch-S', 'Sch-M') order by start_time desc
Vedrete gli Sch-S locks attivi con il vostro Sch-M lock in attesa (notate come l'id di transazione cambi):
Se avete sottomano un SQL 2012 vedrete che il thread in attesa di Sch-M non verrà “scavalcato”, di fatto risolvendo il thread starvation.
Ecco un altro motivo in più per passare a SQL 2012 :).
Con SQL 2012 è possibile creare user che si autenticano direttamente nei database. In SSMS li vediamo nominati “user with password”. Questa modalità, molto utile per l’indipendenza del database dall’istanza su cui riesiede, ha tuttavia alcuni effetti collaterali. Il più evidente è il cosiddetto “login masquerading” dovuto all’algoritmo di probing di SQL in fase di autenticazione (maggiori informazioni qui http://msdn.microsoft.com/it-it/library/ff929055.aspx). In parole povere è possibile creare uno user with password con lo stesso nome di una login a livello d’istanza. SQL Server sceglierà di autenticare prima lo user with password se:
Capite bene che ciò vuol dire che l’identità presentata a SQL non è più la coppia username-password ma diventa la tupla username-password-initial database.
Non gestendo correttamente questa nuova modalità ci si espone ovviamente a diversi rischi.
Oggi però voglio parlarvi di uno scenario proposto da un cliente particolarmente brillante: cosa succede se io creo due user with password con SID identici su due diversi database? Supponendo che insistano sulla stessa istanza, posso usare lo statement USE [database] per passare da uno all’altro?
Ovviamente se così fosse sarebbe un problema di sicurezza non trascurabile: ad esempio restorando in produzione un database si potrebbe aprire una porta di accesso ad altri database preesistenti. Lo scenario sarebbe:
A questo punto avviene questo:
Ciò che suddede (correttamente) è che sarà possibile collegarsi a Svil in produzione utilizzando SvilLogin.
Ciò che non deve succedere è che sia possibile accedere a Prod autenticandosi su Svil. Di fatto USE Prod deve fallire in questo contesto.
Controlliamo che ciò non succeda. Creiamo per prima cosa due DB con autenticazione autocontenuta:
CREATE DATABASE Cont1; GO CREATE DATABASE Cont2; GO ALTER DATABASE Cont1 SET CONTAINMENT=PARTIAL; GO ALTER DATABASE Cont2 SET CONTAINMENT=PARTIAL; GO
A questo punto creiamo le user with password (SQLCmd mode):
:SETVAR user UserWithSameSID :SETVAR password Passw0rd USE Cont1; GO CREATE USER [$(user)] WITH PASSWORD='$(password)'; GO DECLARE @sid VARBINARY(MAX); SELECT @sid = SID FROM Cont1.sys.database_principals WHERE name = '$(user)'; DECLARE @stmt NVARCHAR(4000); SET @stmt = 'CREATE USER [$(user)] WITH PASSWORD=''$(password)'', SID=' + CONVERT(VARCHAR(255), @sid, 1) + ';' PRINT @stmt; USE Cont2; EXEC sp_executeSQL @stmt; GO SELECT * FROM Cont1.sys.database_principals WHERE name = '$(user)' UNION ALL SELECT * FROM Cont2.sys.database_principals WHERE name = '$(user)'
Come si nota i due user hanno SID identici... anzi hanno anche lo stesso principal_id (incidentalmente):
Ora proviamo a collegarci a Cont1 con l'utenza appena creata. A connessione avvenuta proviamo a eseguire:
SELECT * FROM sys.databases;
Il risultato è l'atteso, infatti vediamo oltre al nostro DB anche master e il TempDB ma non Cont2:
Anche USE [Cont2] non funziona come ci attendiamo:
Ora la domanda lecita è: dove SQL tiene traccia del db iniziale (che completa la tupla username-password-initial db)?
Guardiamo nella sys.dm_exec_sessions e troviamo un campo che fa al caso nostro: authenticating_database_id. Nel caso delle autenticazioni a livello di database questo campo sarà ovviamente diverso da 1:
Happy Coding,
Oggi esploreremo il comportamento delle EntityFramework 5.0 su SQL Server, in particolare il comportamento che si ha utilizzando l’approccio Code First. Per questo esempio creiamo un progetto console con Visual Studio e aggiungiamo (tramite NUGet ad esempio) i riferimenti ad EntityFramework:
Ora aggiungiamo due entità (classi), una chiamata Crewman:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication3 { public class Crewman { public int ID { get; set; } public string Name { get; set; } public Rank Rank { get; set; } public Crewman ReportingOfficier { get; set; } } }
E l’altra chiamata StarShip:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.Entity; namespace ConsoleApplication3 { public class Starship { public Starship() { Crew = new List<Crewman>(); } public int ID { get; set; } public string Name { get; set; } public string Designation { get; set; } public List<Crewman> Crew { get; set; } } }
Il tipo Rank è un enumerato così definito:
public enum Rank { Captain, FirstOfficer, ChiefEngineer, Crewman, Medic }
Notiamo come abbiamo deliberatamente sempre specificato una proprietà pubblica di tipo int con nome ID. Inoltre notiamo come abbiamo creato una relazione fra le entità semplicemente definendo una proprietà come enumerabile di Crewman. Nel modello relazionale potremmo dire che l’entità Starship ha 0..∞ Crewman. Inoltre possiamo dire che ciascun Crewman può avere un Crewman ReportingOfficier.
Per rendere persistenti le entità definiamo una estensione di DbContext in questo modo:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.Entity; namespace ConsoleApplication3 { public class StarFleet : DbContext { public DbSet<Starship> StarShips { get; set; } public DbSet<Crewman> Crew { get; set; } public StarFleet(string nameOrConnectionString) : base(nameOrConnectionString) { } } }
Notiamo l’utilizzo dell’insieme DbSet per le proprietà pubbliche da persistere su SQL Server. A questo punto l’utilizzo è semplice, nel metodo main della nostra classe console creiamo una istanza di StarFleet (con il puntamento ad una nostra istanza di SQL) e popoliamola con dati fittizi:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { try { StarFleet sf = new StarFleet("Server=”ServerHere”;Database=TestEFCF;Trusted_Connection=True"); { Crewman cKirk = new Crewman() { Name = "J.T. Kirk", Rank = ConsoleApplication3.Rank.Captain }; Crewman cSpock = new Crewman() { Name = "Spock", Rank = ConsoleApplication3.Rank.FirstOfficer, ReportingOfficier = cKirk }; Crewman cBones = new Crewman() { Name = "Leonard McCoy", Rank = ConsoleApplication3.Rank.Medic, ReportingOfficier = cKirk }; Starship s = new Starship() { Name = "USS Enterprise A" }; s.Crew.Add(cKirk); s.Crew.Add(cSpock); s.Crew.Add(cBones); sf.StarShips.Add(s); } //----------------------------- { Crewman cPicard = new Crewman() { Name = "Jean Luc Picard", Rank = ConsoleApplication3.Rank.Captain }; Crewman cData = new Crewman() { Name = "Data", Rank = ConsoleApplication3.Rank.FirstOfficer, ReportingOfficier = cPicard }; Crewman cCrusher = new Crewman() { Name = "Wesley Crusher", Rank = ConsoleApplication3.Rank.Captain, ReportingOfficier = cData }; Starship s2 = new Starship() { Name = "USS Enterprise C" }; s2.Crew.Add(cPicard); s2.Crew.Add(cData); s2.Crew.Add(cCrusher); sf.StarShips.Add(s2); } //------------------------------ sf.SaveChanges(); } catch (Exception exce) { Console.WriteLine(exce.ToString()); } } } }
Notiamo subito che il Database non è necessario che esista: in caso negativo il framework cercherà di crearne uno ex-novo (ovviamente la login deve possedere i relativi privilegi perché questa creazione vada a buon fine). Eseguendo il programma otterremo un database con le seguenti tabelle:
Ma cosa succeed dietro le quinte? Attiviamo una sessione di eXtended Events (non più profiler :)) usando il template “Query detail tacking”:
Rieseguiamo il programma (supponendo di aver ripulito il db) e vedremo che i passi effettuati dall’entity framework (tralasciamo la tabella [dbo].[__MigrationHistory] che è privata del framework e non ci interessa in questo contesto).
Vedendo che non esistono le tabelle richieste, il framework le crea per noi:
CREATE TABLE [dbo].[Starships] ( [ID] [int] NOT NULL IDENTITY, [Name] [nvarchar](max), [Designation] [nvarchar](max), CONSTRAINT [PK_dbo.Starships] PRIMARY KEY ([ID]) ) CREATE TABLE [dbo].[Crewmen] ( [ID] [int] NOT NULL IDENTITY, [Name] [nvarchar](max), [Rank] [int] NOT NULL, [ReportingOfficier_ID] [int], [Starship_ID] [int], CONSTRAINT [PK_dbo.Crewmen] PRIMARY KEY ([ID]) )
In seguito vengono creati gli indici:
CREATE INDEX [IX_ReportingOfficier_ID] ON [dbo].[Crewmen]([ReportingOfficier_ID]) CREATE INDEX [IX_Starship_ID] ON [dbo].[Crewmen]([Starship_ID])
E poi le constraint:
ALTER TABLE [dbo].[Crewmen] ADD CONSTRAINT [FK_dbo.Crewmen_dbo.Crewmen_ReportingOfficier_ID] FOREIGN KEY ([ReportingOfficier_ID]) REFERENCES [dbo].[Crewmen] ([ID]) ALTER TABLE [dbo].[Crewmen] ADD CONSTRAINT [FK_dbo.Crewmen_dbo.Starships_Starship_ID] FOREIGN KEY ([Starship_ID]) REFERENCES [dbo].[Starships] ([ID])
Notiamo subito un paio cose interssanti:
Mentre la prima è attesa, la seconda è una ottima notizia: utilizzando SSMS 2012 avremmo avuto solo la constraint e non l’indice:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.Starships SET (LOCK_ESCALATION = TABLE) GO COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.Crewmen ADD CONSTRAINT FK_Crewmen_Starships FOREIGN KEY ( Starship_ID ) REFERENCES dbo.Starships ( ID ) ON UPDATE NO ACTION ON DELETE NO ACTION GO ALTER TABLE dbo.Crewmen SET (LOCK_ESCALATION = TABLE) GO COMMIT
Gli inserimenti avvengono in questa maniera:
exec sp_executesql N'insert [dbo].[Crewmen]([Name], [Rank], [ReportingOfficier_ID], [Starship_ID]) values (@0, @1, @2, @3) select [ID] from [dbo].[Crewmen] where @@ROWCOUNT > 0 and [ID] = scope_identity()', N'@0 nvarchar(max) ,@1 int,@2 int,@3 int', @0=N'Leonard McCoy', @1=4, @2=3, @3=1
Notiamo come ogni inserimento siano in realtà due statement separati:
insert [dbo].[Crewmen]([Name], [Rank], [ReportingOfficier_ID], [Starship_ID]) values (@0, @1, @2, @3) select [ID] from [dbo].[Crewmen] where @@ROWCOUNT > 0 and [ID] = scope_identity()
(NB: potreste vedere comparire anche un SELECT StatMan([SC0])… ma questo non è dovuto ovviamente a EF).
Evidentemente EF usa il secondo statement per sincronizzare l’entità persisita via codice con l’ID generato da SQL. Domanda: come mai l’EF non usa la clausola OUTPUT?
La risposta più probabile è che si aspetti zero record in caso di fallimento (da where @@ROWCOUNT > 0) mentre lo statement OUTPUT restituisce righe anche se l’operazione fallisce. Per maggiori dettagli vedere http://msdn.microsoft.com/en-us/library/ms177564(v=sql.110).aspx.
Nei post successivi continueremo con l’analisi delle interazioni EF5.0-SQL.
Con il Service Pack 1 di SQL Server 2012 è stata aggiunta una funzionalità molto utile a chi utilizza il tipo nativo di SQL Server dedicato ai documenti XML: gli indici XML selettivi. Questi indici selettivi permettono di specificare quali elementi (o, meglio, percorsi) da indicizzare in contrasto col l’indice primario XML che indicizza tutto il documento. Come potete immaginare, è evidente che – a fronte di pattern di ricerca prevedibili – sia più efficiente indicizzare solo quei percorsi specifici.
Per maggiori dettagli vi rimando all’articolo sull’MSDN: http://msdn.microsoft.com/en-us/library/jj670108.aspx.
Vediamo insieme un esempio pratico di questo nuovo indice. Per farlo creiamo un database, una tabella molto semplice e popoliamo con XML di esempio. Sottolineo che fra i prerequisiti ci sia la presenza dell’indice clustered (http://msdn.microsoft.com/en-us/library/jj670108.aspx#prereq). Quindi niente indici XML selettivi su tabelle heap (un motivo in più… :)).
CREATE DATABASE TestSXI; GO USE TestSXI; GO CREATE TABLE testTbl( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, XmlDoc XML); GO INSERT INTO testTbl(XmlDoc) VALUES( '<Meme xmlns="http://schemas.test.com/xyz"> <WorkHours>500</WorkHours> <TaskName>Mission Xyz III</TaskName> </Meme>'); GO 500 INSERT INTO testTbl(XmlDoc) VALUES( '<Meme xmlns="http://schemas.test.com/xyz"> <WorkHours>1</WorkHours> <TaskName>Mission Xyz I</TaskName> </Meme>'); GO INSERT INTO testTbl(XmlDoc) VALUES( '<Meme xmlns="http://schemas.test.com/xyz"> <WorkHours>222</WorkHours> <TaskName>Mission Xyz II</TaskName> <TaskName>Mind your head!</TaskName> <TaskName>No task name</TaskName> </Meme>'); GO 222
INSERT INTO testTbl(XmlDoc) SELECT XmlDoc FROM testTbl;
GO 7
Come potete notare inseriamo dei documenti che potrebbero avere uno schema simile al seguente:
<?xmlversion="1.0"encoding="utf-8"?> <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://schemas.test.com/xyz" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="Meme"> <xs:complexType> <xs:sequence> <xs:element minOccurs="1" maxOccurs="1" nillable="false" name="WorkHours" type="xs:integer" /> <xs:element minOccurs="0" maxOccurs="unbounded" nillable="false" name="TaskName" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>
Fino al Service Pack 1 di SQL Server potevamo solo indicizzare tutto il documento con un indice XML. Ad esempio con:
CREATE PRIMARY XML INDEX [PXML_XmlDoc] ON [dbo].[testTbl](XmlDoc); GO
Creaimo un indice XML completo. Supponiamo che la nostra query ricorrente sia di questo tipo:
WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming) SELECT COUNT(*) FROM testTbl WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1
Ovviamente sarebbe molto più efficiente indicizzare il solo percorso che identifica WorkHours dentro il tipo Meme. Per far questo cerchiamo di creare il nostro indice XML selettivo:
CREATE SELECTIVE XML INDEX sxi_Test ON [dbo].[testTbl](XmlDoc) WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming) FOR ( pathWH2 = '/yming:Meme/yming:WorkHours' AS XQUERY 'xs:integer' SINGLETON ); GO
SQLMS vi dirà che non può farlo a meno che voi abilitiate la funzionalità:
Msg 9539, Level 16, State 1, Line 1
Selective XML Index feature is not supported for the current database version
Per farlo basta eseguire questo comando:
EXEC sp_db_selective_xml_index NULL, 'TRUE'; GO
Perché è necessario abilitare espressamente la funzionalità? Questo perché abilitando il supporto alla funzionalità di indici XML selettivi si aumenta la versione del database. In questo modo SQL garantisce che un database con questa funzionalità (che evidentemente è stata abilitata da un SQL 2012 almeno SP1) non venga restorato su un SQL che non la supporta (ad esempio, ma non solo, SQL 2012 RTM).
Per vederlo utilizziamo il comando non documentato né supportato DBCC DBINFO (quindi non usatelo se non per scopi didattici!):
EXEC sp_db_selective_xml_index NULL, 'TRUE'; GO DBCC DBINFO('TestSXI') WITH TABLERESULTS; EXEC sp_db_selective_xml_index NULL, 'FALSE'; GO DBCC DBINFO('TestSXI') WITH TABLERESULTS;
Per maggiori informazioni vi rimando a questo post di Igor: http://blogs.technet.com/b/italian_premier_center_for_sql_server/archive/2011/12/14/il-falso-mito-della-migrazione-da-una-versione-di-sql-all-altra.aspx.
Abilitato il supporto alla nuova funzionalità riproviamo a creare l’indice XML selettivo. Notiamo come venga specificato l’attributo SINGLETON per migliorare le performance di SQL. Questo attributo equivale a maxOccurs=”1” dell’XSD.
SQL anche in questo caso non ci permette di creare l’indice. Questa volta è perché abbiamo scelto un tipo non supportato per gli indici XML selettivi su XML untyped. Qui è chiaramente dettagliato l’elenco supportato: http://msdn.microsoft.com/en-us/library/jj670107.aspx#untyped . Essi sono:
Per i nostri scopi scegliamo xs:string e riproviamo:
CREATE SELECTIVE XML INDEX sxi_Test ON [dbo].[testTbl](XmlDoc) WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming) FOR ( pathWH2 = '/yming:Meme/yming:WorkHours' AS XQUERY 'xs:string' SINGLETON ); GO
Questa volta abbiamo successo.
Per testare l’efficacia dell’indice effettuiamo nuovamente la nostra query (dopo avere abilitato le statistiche con SET STATISTICS IO ON e aver abilitato la visualizzazione del piano di esecuzione effettivo in SSMS). Per vedere l’incremento di performance eseguiamo la query due volte nello stesso batch. Fra una esecuzione e l’altra droppiamo l’indice XML selettivo (per il momento non possiamo usare i query hints).
WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming) SELECT COUNT(*) FROM testTbl WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1 GO DROP INDEX sxi_Test ON [dbo].[testTbl] GO WITH XMLNAMESPACES ('http://schemas.test.com/xyz' as yming) SELECT COUNT(*) FROM testTbl WHERE XmlDoc.exist(N'(/yming:Meme/yming:WorkHours[.=222])') = 1 GO
Notiamo anche la differenza tangibile di IO:
In questo articolo, esploreremo l’utilizzo dell’hint OPTIMIZE FOR e analizzeremo come questo impatta sulla lettura delle statistiche da parte dell’optimizer.
Quando una stored procedure viene eseguita la prima volta, i parametri con cui viene richiamata vengono salvati nella cache insieme al piano di esecuzione e utilizzati per il calcolo della cardinalità.
Questo comportamento è denominato “parameter sniffing”.
Nel caso in cui la distribuzione dei valori all’interno di una statistica sia poco uniforme, una stima errata della cardinalità, dovuta al parameter sniffing, può impattare negativamente sulle prestazioni in fase di esecuzione.
Facciamo un esempio:
USE tempdbGO
SET NOCOUNT ON
CREATE TABLE t1 (IdRow int identity(1,1) PRIMARY KEY, Valore char(3))
DECLARE @i int
-- Inserisco 1000 righe con il valore AAASET @i = 0WHILE @i < 1000BEGIN INSERT INTO t1 (Valore) VALUES ('AAA') SET @i = @i + 1END
-- Inserisco 500 righe con il valore BBBSET @i = 0WHILE @i < 500BEGIN INSERT INTO t1 (Valore) VALUES ('BBB') SET @i = @i + 1END
-- Inserisco 1 riga con il valore CCCINSERT INTO t1 (Valore) VALUES ('CCC')
CREATE INDEX IX1 ON t1 (Valore)GO
-- Analizziamo le statisticheDBCC SHOW_STATISTICS (t1, IX1)GO
Analizzando i piani di esecuzione possiamo constatare come le righe stimate vengano calcolate sulla base della cardinalità del parametro lanciato per primo dopo la compilazione (o ricompilazione) della stored procedure.
La figura evidenzia, ad esempio, la discrepanza tra righe stimate e quelle reali nel caso in cui la stored procedure venga richiamata con il parametro “AAA” essendo stata richiamata la prima volta con il parametro “CCC”.
Se guardiamo il piano di esecuzione in formato XML troveremo il dettaglio del parametro salvato:
<ParameterList>
<ColumnReference Column="@Valore" ParameterCompiledValue="'CCC'"ParameterRuntimeValue="'CCC'" />
</ParameterList>
In casi come quello appena descritto, possiamo utilizzare l’hint “OPTIMIZE FOR” per controllare la modalità con cui i parametri vengono salvati nel piano di esecuzione, in modo da migliorare la stima della cardinalità.
Ci sono due modi per utilizzare “OPTIMIZE FOR”:
Esempio caso 1:
ALTER PROCEDURE p1 @Valore char(3)AS
SELECT IdRow, ValoreFROM t1WHERE Valore = @ValoreOPTION(OPTIMIZE FOR (@Valore = 'AAA'))
GO
-- Eseguiamola passandogli prima 'CCC' e poi 'BBB'EXEC p1 'CCC' --> 1 riga, stimate 1000EXEC p1 'BBB' --> 500 righe, stimate 1000
Come si vede, in entrambi i casi l’optimizer stima 1000 righe che è appunto la cardinalità del valore “AAA”
Esempio caso 2:
SELECT IdRow, ValoreFROM t1WHERE Valore = @ValoreOPTION(OPTIMIZE FOR UNKNOWN)
-- Eseguiamola passandogli i vari valoriEXEC p1 'CCC' --> 1 riga, stimate 500,333EXEC p1 'BBB' --> 500 righe, stimate 500,333EXEC p1 'AAA' --> 1000 righe, stimate 500,333
Come si vede, l’optimizer stima le righe in base alla densità, ovvero:
righe stimate = densità x numero righe totali = 0,3333333 x 1501 = 500,333
Ora vi faccio notare qualcosa di interessante :-)
Proviamo ad aggiungere un po’ di valori NULL per il campo “Valore”:
DECLARE @i intSET @i = 0WHILE @i < 2000BEGIN INSERT INTO t1 (Valore) VALUES (NULL) SET @i = @i + 1END
-- Analizziamo le statisticheUPDATE STATISTICS t1 WITH FULLSCAN;DBCC SHOW_STATISTICS (t1, IX1)GO
Dalle statistiche possiamo notare la presenza di 2000 valori NULL per il campo “Valore”. La densità è leggermente diminuita, passando da 0,333 a 0,25.
Ricompiliamo la stored e lanciamola con il parametro CCC:
EXEC sp_recompile p1
EXEC p1 'CCC'
Secondo i calcoli fatti in precedenza ci aspetteremmo la seguente stima:
densità x numero righe totali = 0,25 x 3501 = 875,25
Se guardiamo però il piano di esecuzione notiamo che la stima non è cambiata:
Come mai ha ignorato i valori NULL?
La risposta è insita nel predicato della query:
WHERE Valore = @Valore
Nel caso in cui @Valore sia NULL, la query non restituirà alcuna riga nonostante vi siano 2000 righe a NULL, perché il risultato del confronto "Valore = @Valore" è valutato come UNKNOWN.
È interessante il fatto che l’optimizer, conscio di questo fatto, non consideri la cardinalità dei valori NULL, migliorando la stima.
Il calcolo effettuato è
[densità dei valori NOT NULL] x [numero righe totali NOT NULL]
dove densità dei valori NOT NULL si ottiene facendo l’inverso della somma di tutti i valori della colonna DISTINCT_RANGE_ROWS con l’aggiunta del numero di step – 1 (escludo la riga dei NULL).
Nel nostro caso il calcolo è [1/(0+0+0+0+4-1)] x 1501
Alla prossima!
Giuseppe Zagarrio
Un veloce post per sottolineare una funzionalità a mio avviso poco pubblicizzata introdotta dal Service Pack 1 di SQL Server 2012. Con FileTable potete esporre il contenuto di una tabella a schema fisso all’accesso non transazionale tipico dei file systems. Non entro nei dettagli, per un approfondimento vi rimando qui: http://msdn.microsoft.com/en-us/library/ff929144.aspx. Voglio solo segnalarvi che, dal SP1 in poi, è possibile specificare una Access Control List più completa ai files. Nello specifico ora possiamo specificare:
La matrice dei privilegi diventa quindi la seguente:
Privilegio logico
GRANT T-SQL
Privilegio di browse delle cartelle
SELECT
Privilegio di lettura
Privilegio di scrittura di un file
UPDATE
Privilegio di creazione di un file
UPDATE e INSERT
Privilegio di eliminazione
DELETE
Pre service pack era possibile dare solo l'accesso in lettura/scrittura (anche a fronte di un semplice GRANT SELECT).
Da notare un paio di cose interessanti:
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/
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.
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 !):
-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 !