Domande e risposte su SQLDatabase che crescono, IFilter e connessione in modalità remota

A cura di Matthew Graven

Suggerimento: cancellazione della cache

Chi non si è mai chiesto perché una stored procedure funziona bene nell'ambiente di test e poi evidenzia pessime prestazioni dopo la distribuzione sul SQL Server di produzione? La causa potrebbe essere un problema correlato alla cache. Prima di distribuire le stored procedure nell'ambiente di produzione, sottoporre le procedure a test nell'ambiente di test dopo aver cancellato i piani di query memorizzati nella cache, in modo da poter verificare il funzionamento della stored procedure in un ambiente a cache "fredda". Ecco un paio di suggerimenti davvero utili.

Per cancellare la cache delle procedure in un SQL Server:

DBCC FREEPROCCACHE
Go

Eseguire una query per ottenere un elenco con tutti i piani memorizzati nella cache:

Select * from sys.dm_exec_cached_plans
Go

D Ho un database che produce un traffico intenso durante il giorno e non vorrei utilizzare l'aumento di dimensioni automatico, per evitare timeout quando SQL Server® decide di eseguire l'operazione durante le ore di picco. Vorrei implementare un processo periodico per espandere il file di database di una percentuale dello spazio utilizzato specifica. Come è possibile eseguire questa operazione?

R L'aumento di dimensioni del file è un'operazione a I/O su disco intensivo e, quando SQL Server deve attendere che si completi l'espansione di un file di dati o di registro, si nota un calo nelle prestazioni e un peggioramento dei tempi di risposta. L'incremento di crescita predefinito è di 1 MB per i file di dati e del 10% per i file di registro, che possono rivelarsi incrementi insufficienti per dei sistemi molto sfruttati. Inoltre, quando si utilizza l'aumento automatico delle dimensioni, si può verificare una maggiore frammentazione del disco, perché i file di dati o di registro non sono contigui su disco. Ne risultano tempi di risposta più lunghi del necessario, dal momento che i dati sono fisicamente sparsi sul disco.

Una soluzione per l'ottimizzazione delle prestazioni è l'allocazione proattiva di spazio di archiviazione sufficiente per i file di dati e di registro. A tal fine sono spesso necessarie analisi delle tendenze e previsioni di crescita, ma le prestazioni se ne avvantaggiano perché i file saranno contigui sul disco e si eviteranno i costi in termini di I/O necessario per l'aumento automatico delle dimensioni durante i periodi di punta. È opportuno tenere l'aumento automatico delle dimensioni attivato, perché dei file di dati o di registro completamente pieni impediscono del tutto l'accesso al database. Ma, appunto, l'aumento automatico delle dimensioni deve essere considerato una rete di protezione piuttosto che una funzionalità di gestione del database.

È inoltre preferibile evitare espansioni dei file del database su base regolare, perché ne possono derivare file non contigui su disco con un ovvio peggioramento delle prestazioni. Il controllo proattivo si può ottenere eseguendo uno script per determinare la percentuale di spazio libero per ciascun database (eseguito da un processo di SQL Agent) e avviando quindi un'azione (come l'invio di un avviso di posta elettronica tramite Posta elettronica database). Il codice nella figura costituisce uno script di esempio con cui si illustra come recuperare informazioni sulla percentuale di spazio libero per il database corrente.

Dopo la generazione di un avviso, è possibile creare uno script per l'aumento di dimensioni una tantum dei file con il comando ALTER DATABASE e si può utilizzare un processo di SQL Agent per pianificare l'azione in modo che non venga avviata nelle ore di picco. Provare a far crescere il file di una dimensione che sia sufficiente per il futuro prevedibile, in modo da evitare ulteriori piccole espansioni future. È anche utile verificare che la compattazione automatica non sia attivata su alcun database, poiché potrebbe avviare inutili cicli di aumento delle dimensioni e compattazione del database.

—Justin Langford

Determinazione dello spazio libero in un database

-- Script to gather size, free space and 
-- calculate % free space for current 
-- USER database
DECLARE @size DEC(15,2)
DECLARE @free DEC(15,2)
DECLARE @result DEC(15,2)

SELECT @size = SUM(size)*1.0/128
FROM sys.database_files

SELECT @free = 
(SUM(unallocated_extent_page_count)*1.0/128)
FROM sys.dm_db_file_space_usage

PRINT 'DB Size ' + CONVERT(VARCHAR(15), @size)
PRINT 'Free Space ' + 
CONVERT(VARCHAR(15), @free)

SELECT @result = (@free/@size)*100

PRINT '% Free Space ' + 
CONVERT(VARCHAR(15), @result)

D Nella mia azienda vengono archiviati file in formati diversi all'interno di un database, utilizzando le colonne varbinary e image. Ho sentito dire che in SQL Server è integrata una funzionalità che consente di eseguire ricerche in questi formati di file diversi. Come si configura SQL Server a questo scopo?

R La funzionalità in questione è incorporata nel servizio di indicizzazione full-text. Il servizio garantisce la flessibilità necessaria per utilizzare le interfacce IFilter, rendendo possibile lo sviluppo e il caricamento di filtri che consentono di estrarre informazioni utili dai dati proprietari. Le interfacce IFilter vengono anche utilizzate per altri prodotti, come Microsoft® Office Sharepoint® Server, per raccogliere informazioni sui file sottoposti a ricerca per indicizzazione.

Un IFilter viene fornito dal creatore del formato di file o da terze parti. SQL Server contiene già alcuni IFilter, che vengono caricati quando si installa FulltextService (FTS), ad esempio filtri per i file HTML e DOC. È comunque possibile aggiungere altri IFilter, in base alle proprie esigenze. I filtri per Adobe PDF, ad esempio, sono disponibili sul sito Web di Adobe e alla fine del 2007 è stato pubblicato un nuovo pacchetto di filtri per le estensioni di Office System 2007. È sempre necessario conoscere la versione di IFilter da utilizzare. Per esempio, un IFilter progettato per i sistemi a 32 bit non funziona con le installazioni di SQL Server a 64 bit.

Dopo l'esecuzione del pacchetto di installazione sul client, l'IFilter viene in genere registrato nell'ecosistema del sistema operativo. Con i frammenti registrati nel sistema operativo, è sufficiente eseguire poche operazioni per mettere FTS in condizione di caricare i filtri. Dopo l'avvio dello strumento di esecuzione delle query, passare i comandi seguenti:

• sp_fulltext_service 'load_os_resources',1 (questa consentirà a FTS di caricare i frammenti registrati per l'elaborazione, compresi i componenti come wordbreaker e stemmer).
• sp_fulltext_service 'verify_signature',0 (con questa istruzione si eviterà di controllare con SQL Server se i filtri utilizzati sono firmati, poiché molti fornitori non firmano i propri filtri secondo lo standard).
• Riavviare l'istanza di SQL Server e l'istanza di FTS.
• Creare il proprio indice full-text sulle colonne che hanno la colonna di dati binari come contenuto su cui l'IFilter può eseguire la ricerca per indicizzazione e la colonna di estensioni (vale a dire, la colonna con i tipi di estensione, come DOCX) in cui SQL Server può scegliere il filtro a cui reindirizzare il contenuto.

Ulteriori informazioni in merito sono disponibili all'indirizzo go.microsoft.com/?linkid=7912971.

—Jens Suessmeyer

D Non riesco a connettermi a un SQL Server remoto. È necessario configurare il firewall sul computer client o server?

R Le connessioni remote a SQL Server 2005 possono non riuscire per molte ragioni, ma la configurazione dei firewall è uno dei problemi più comuni. Il blog SQL Protocols (blogs.msdn.com/sql_protocols) è un'eccellente risorsa di informazioni sulle connessioni SQL.

L'installazione predefinita di SQL Server 2005 non consente le connessioni remote. Dal computer su cui si esegue SQL, nel menu Start, scegliere Microsoft SQL Server 2005 | Strumenti di configurazione | Configurazione superficie di attacco di SQL Server. Passare a Configurazione superficie di attacco per servizi e connessioni, scegliere Connessioni remote e scegliere il pulsante di opzione "Usa sia TCP/IP che named pipe". Per applicare le modifiche, riavviare SQL.

Per impostazione predefinita, SQL Server utilizza la porta 1433. Per verificare se la porta è aperta, utilizzare il comando telnet seguente, sostituendo <ipaddress> con l'indirizzo IP reale del computer su cui viene eseguito SQL Server:

telnet <ipaddress> 1433

Se si ottiene una risposta di connessione non riuscita, aprire Windows® Firewall, passare alla scheda Eccezioni, scegliere Aggiungi porta e aggiungere la porta TCP 1433. Il comando telnet ora avrà esito positivo (Telnet non viene installato per impostazione predefinita su Windows Vista®).

—Rick Anderson

Un ringraziamento ai seguenti esperti di SQL Server per il contributo offerto alla redazione dell'articolo:
Justin Langford lavora in Coeo Ltd. ed è uno specialista dell'integrazione di sistemi e un Microsoft Certified Partner che risiede in Inghilterra. Jens Suessmeyer è un consulente per i database in Microsoft e vive in Germania. Rick Anderson lavora presso il Developer User Education in Microsoft. Saleem Hakani è Senior Database Engineer e SQL Server Community Lead in Microsoft.

© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.