Parametrizzazione forzata
È possibile ignorare il comportamento predefinito parameterizzazione semplice di SQL Server specificando la parametrizzazione di tutte le istruzioni SELECT, INSERT, UPDATE e DELETE di un database in base a limiti specifici. La parametrizzazione forzata viene attivata impostando l'opzione PARAMETERIZATION su FORCED nell'istruzione ALTER DATABASE. La parametrizzazione forzata può offrire un miglioramento delle prestazioni di alcuni database riducendo la frequenza delle operazioni di compilazione e ricompilazione delle query. I database che possono essere soggetti a un miglioramento delle prestazione grazie alla parametrizzazione forzata sono in genere quelli che ricevono volumi elevati di query simultanee da origini quali le applicazioni POS.
Quando l'opzione PARAMETERIZATION è impostata su FORCED, qualsiasi valore letterale visualizzato in un'istruzione SELECT, INSERT, UPDATE o DELETE, inviato in qualsiasi forma, viene convertito in un parametro durante la compilazione delle query. Le eccezioni consistono in valori letterali presenti nei costrutti di query seguenti:
Istruzioni INSERT...EXECUTE.
Istruzioni all'interno del corpo di stored procedure, trigger o funzioni definite dall'utente. SQL Server riutilizza già piani di query per tali routine.
Istruzioni preparate già parametrizzate nell'applicazione sul lato client.
Istruzioni contenenti chiamate al metodo XQuery, in cui il metodo appare in un contesto in cui i relativi argomenti verrebbero in genere parametrizzati, ad esempio una clausola WHERE. Se il metodo appare in un contesto in cui i relativi argomenti non verrebbero parametrizzati, il resto dell'istruzione viene parametrizzato.
Istruzioni all'interno di un cursore Transact-SQL. Le istruzioni SELECT all'interno dei cursori API vengono parametrizzate.
Costrutti di query obsoleti.
Qualsiasi istruzione eseguita nel contesto dell'opzione ANSI_PADDING o ANSI_NULLS impostata su OFF.
Istruzioni contenenti oltre 2.097 valori letterali idonei per la parametrizzazione.
Istruzioni che fanno riferimento a variabili, ad esempio WHERE T.col2 >= @bb.
Istruzioni contenenti gli hint per la query RECOMPILE.
Istruzioni contenenti una clausola COMPUTE.
Istruzioni contenenti una clausola WHERE CURRENT OF.
Le clausole di query seguenti sono inoltre senza parametri. Si noti che in questi casi soltanto le clausole sono senza parametri. Altre clausole all'interno della stessa query potrebbero essere idonee per la parametrizzazione forzata.
<select_list> di qualsiasi istruzione SELECT. Include gli elenchi SELECT di subquery e gli elenchi SELECT inclusi nelle istruzioni INSERT.
Istruzioni SELECT delle subquery incluse in un'istruzione IF.
Clausola TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO o FOR XML di una query.
Argomenti, diretti o sottoespressioni, di OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML o di qualsiasi operatore FULLTEXT.
Gli argomenti pattern ed escape_character di una clausola LIKE.
Argomento style di una clausola CONVERT.
Costante integer all'interno di una clausola IDENTITY.
Costanti specificate utilizzando la sintassi delle estensioni ODBC.
Espressioni per le quali è possibile eseguire l'elaborazione delle costanti in fase di compilazione che rappresentano argomenti degli operatori +, -, *, / e %. Quando viene valutata l'idoneità per la parametrizzazione forzata, in SQL Server un'espressione viene considerata come idonea per l'elaborazione delle costanti in fase di compilazione quando si verificano le condizioni seguenti:
Nell'espressione non è inclusa alcuna colonna, variabile o subquery.
L'espressione contiene una clausola CASE.
Per ulteriori informazioni sulle espressioni per le quali è possibile eseguire l'elaborazione delle costanti in fase di compilazione, vedere Risoluzione dei problemi relativi alle prestazioni delle query: elaborazione delle costanti in fase di compilazione durante la stima della cardinalità.
Argomenti delle clausole degli hint per le query. Sono inclusi l'argomento number_of_rows dell'hint per la query FAST, l'argomento number_of_processors dell'hint per la query MAXDOP e l'argomento number dell'hint per la query MAXRECURSION.
La parametrizzazione viene eseguita a livello di singole istruzioni Transact-SQL. In altri termini, vengono parametrizzate le singole istruzioni presenti in un batch. In seguito alla compilazione, una query con parametri viene eseguita nel contesto del batch in cui è stata inviata originariamente. Se un piano di esecuzione per una query viene memorizzato nella cache, è possibile determinare se è stata eseguita la parametrizzazione della query facendo riferimento alla colonna sql della vista a gestione dinamica sys.syscacheobjects. Se è stata eseguita la parametrizzazione di una query, i nomi e i tipi di dati dei parametri precedono il testo del batch inviato nella colonna, ad esempio (@1 tinyint). Per informazioni sulla memorizzazione dei piani di query nella cache, vedere Memorizzazione nella cache e riutilizzo del piano di esecuzione.
Nota
I nomi dei parametri sono arbitrari. Gli utenti o le applicazioni non devono basarsi su un ordine di denominazione specifico. È inoltre possibile che nomi dei parametri, scelta dei valori letterali con parametri e spaziatura nel testo con parametri cambino tra le versioni di SQL Server e gli aggiornamenti dei Service Pack.
Tipi di dati dei parametri
Quando in SQL Server vengono parametrizzati valori letterali, i parametri vengono convertiti nei tipi di dati seguenti:
I valori letterali integer le cui dimensioni altrimenti si adatterebbero al tipo di dati int vengono parametrizzati in int. I valori letterali integer di dimensioni maggiori inclusi in predicati che comportano qualsiasi operatore di confronto, come <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN e IN, vengono parametrizzati in numeric(38,0). I valori letterali di dimensioni maggiori non inclusi in predicati che comportano operatori di confronto vengono parametrizzati in numeric, la cui precisione è tale da supportarne le dimensioni e il cui valore di scala è 0.
I valori letterali numerici a virgola fissa inclusi in predicati che comportano operatori di confronto vengono parametrizzati in numeric con precisione 38 e valore di scala tale da supportarne le dimensioni. I valori letterali numerici a virgola fissa non inclusi in predicati che comportano operatori di confronto vengono parametrizzati in numeric con precisione e valore di scala tali da supportarne le dimensioni.
I valori letterali numerici a virgola mobile vengono parametrizzati in float(53).
I valori letterali stringa vengono parametrizzati in varchar(8000) se il valore letterale non supera gli 8.000 caratteri e in varchar(max) se è maggiore di 8.000 caratteri.
I valori letterali stringa Unicode vengono parametrizzati in nvarchar(4000) se il valore letterale non supera i 4.000 caratteri e in nvarchar(max) se è maggiore di 4.000 caratteri.
I valori letterali binari vengono parametrizzati in varbinary(8000) se il valore letterale non supera gli 8.000 byte. Se il valore letterale è maggiore di 8.000 byte, viene convertito in varbinary(max).
I valori letterali di tipo money vengono parametrizzati in money.
Linee guida per l'utilizzo della parametrizzazione forzata
Quando si desidera impostare l'opzione PARAMETERIZATION su FORCED, considerare gli aspetti seguenti:
Tramite la parametrizzazione forzata, in pratica, le costanti letterali incluse in una query vengono modificate in parametri durante la compilazione di una query. È pertanto possibile che in Query Optimizer vengano scelti piani non ottimali per le query. In particolare, è meno probabile che Query Optimizer associ la query a una vista indicizzata o a un indice in una colonna calcolata. Potrebbero inoltre essere scelti piani non ottimali per le query formulate nelle tabelle partizionate e nelle viste partizionate distribuite. Non utilizzare la parametrizzazione forzata negli ambienti basati in modo significativo su viste indicizzate e indici in colonne calcolate. In generale l'opzione PARAMETERIZATION FORCED deve essere utilizzata solo da amministratori di database esperti dopo avere determinato che le prestazioni non subiranno alcun impatto negativo.
Le query distribuite che fanno riferimento a più di un database sono idonee per la parametrizzazione forzata a condizione che l'opzione PARAMETERIZATION sia impostata su FORCED nel database nel cui contesto viene eseguita la query.
L'impostazione dell'opzione PARAMETERIZATION su FORCED consente di scaricare tutti i piani di query dalla cache dei piani del database, ad eccezione di quelli di cui è in corso la compilazione, la ricompilazione o l'esecuzione. I piani per le query di cui è in corso la compilazione o l'esecuzione durante la modifica dell'impostazione verranno parametrizzati alla successiva esecuzione della query.
L'impostazione dell'opzione PARAMETERIZATION è un'operazione in linea che richiede che non vi sia alcun blocco esclusivo a livello del database.
La parametrizzazione forzata è disattivata, ovvero impostata su SIMPLE, quando la compatibilità di un database di SQL Server è impostata su 80 o quando un database presente in un'istanza precedente viene collegato a un'istanza di SQL Server 2005 o versioni successive.
L'impostazione corrente dell'opzione PARAMETERIZATION viene mantenuta quando un database viene ricollegato o ripristinato.
È possibile ignorare il comportamento della parametrizzazione forzata specificando che su una singola query, e su qualsiasi altra query sintatticamente equivalente ma che differisca solo nei valori dei parametri, venga eseguita la parametrizzazione semplice. Viceversa è possibile specifiare che la parametrizzazione forzata venga tentata solo su un set di query sintatticamente equivalenti, anche se disattivata nel database. A tale scopo, vengono utilizzate le guide di piano. Per ulteriori informazioni, vedere Definizione delle funzionalità di parametrizzazione delle query tramite guide di piano.
Nota
Quando l'opzione PARAMETERIZATION è impostata su FORCED, il report dei messaggi di errore potrebbe presentare differenze rispetto a quello della parametrizzazione semplice: potrebbero essere segnalati più messaggi di errore nei casi in cui nella parametrizzazione semplice sarebbe stato segnalato un numero di messaggi di errore inferiore e i numeri di riga nei quali si sono verificati gli errori potrebbero non essere segnalati correttamente.