MSSQL:Ottimizzazioni

Istruzioni e parametri

Stored Procedure

Parametri

La funzione WITH RECOMPILE consente di forzare la generazione del piano di esecuzione ogni volta che verrà usata la StoredProcedure.
Usare WITH RECOMPILE quando si passano parametri che comportano una differente distribuzione dei dati quindi quando non c'è giovamento ad usare un piano d'esecuzione predefinito perché lo scenario cambia spesso.

--Es.:
CREATE  PROCEDURE [dbo].[dt_RemoveCDRs] 
	@Days		int, 
	@Table		varchar(20)=null, 
	@NoCourtesy	tinyint=null
WITH RECOMPILE
 
AS
--...etc

Tabelle

Si possono esplicitamente ottimizzare le statistiche delle tabelle e indici. Normalmente il query optimizer ha già di default questa attività ma la si può anticipare come nel seguente comando:

-- L'esempio seguente aggiorna le statistiche per tutti gli indici della tabella [Mia_Tabella].
UPDATE STATISTICS [Mia_Tabella];

Si possono far ricompilare stored procedure, triggers, funzioni custom che hanno a che fare con un tabella col seguente:

EXEC sp_recompile N'[Mia_Tabella]';

ATENZIONE questo avrà effetto solo la prossima volta che una store o altro lavorerà sulla tabella indicata

Esperienza

Piano di esecuzione

E' un tool disponibile in Management studio che consente di verificare il peso di relazioni di una query in termini di prestazioni.
Il tool è utile anche quando ad es si effettua una INSERT SELECT. Suggerisce cosa fare per migliorare i costi d'esecuzione, in genere costruisce persino la query necessaria a creare un INDICE che corregge il gap individuato automaticamente.

Creare indici suggeriti

Come indicato in Tips per creare Indici: toptal.com
Il tool "Execution Plan" può essere prezioso per creare indici suggeriti per l'ottimizzazione.

• Dopo aver costruito la query da ottimizzare;
• cliccare sull'icona "Include Actual Execution Plan" (CTRL + M) prima di eseguire la query;
• eseguire la query;
• al termine, cliccare sul TAB "Execution plan" visualizzato, tasto dx sulla zona verde con il codice di creazione e scegliere: "Missing Index Details…"

Si aprirà una nuova pagina col testo commentato contenente le istruzioni di creazione degli indici, es:

/*
Missing Index Details from SQLQuery22.sql - DTMIC2-WEBSRV01.SimSrv_CharityGW (sa (59))
The Query Processor estimates that implementing the following index could improve the query cost by 72.256%.
*/
 
--Scommentato:
USE [SimSrv_CharityGW]
GO
CREATE NONCLUSTERED INDEX [<Name OF Missing INDEX, sysname,>] -- <-- inserire il nome dell'indice
ON [dbo].[T_D_rpt_Log_TMP] ([ObjName],[SubObjName])
INCLUDE ([ID],[Description],[MsgType],[OperationLog],[DtInsert])
GO

ATTENZINE potrebbe impiegarci 3 volte il tempo della select originaria

Varie

OPENQUERY vs LinkedSeerver

Da social.msdn.microsoft.com
It looks like OpenQuery runs the query completely on the remote server. The linked server call seems to run each statement on the remote sever and aggregates it locally.
Es OpenQuery

SET QUOTED_IDENTIFIER OFF
INSERT INTO [|DestDBName|].|SchemaTbNamePart|
	|FieldsStatementDest|
SELECT 
	|FiledsStatementSrc|
FROM OPENQUERY ([|LnkSrvName|], 
		"SELECT |FiledsStatementSrc| 
		FROM |SrcDBName|.[|CurrentSchema|].[|TableName|] 
		WHERE 1=1
			|SQLWhereStatement|
		|SQLOrderStatement|");
SELECT @RowsAffected_OUT = @@ROWCOUNT;

Vedere anche blogs.msdn.microsoft.com, che riporto come segue:
Distributed Query : Linked server four part queries are also called distributed queries. Using distributed queries, you can refer tables on different data sources/servers in a single query. Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

OpenQuery : Executes the specified pass-through query on the specified linked server. SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source . That is, SQL won’t apply any kind of logic on the query and won’t try to estimate what that query would do, it would simply pass the specified query as it is to the target linked server. Open queries are useful when you are not referencing multiple servers in one query. It’s generally fast as SQL does not break it into multiple operations and does not perform any local action on the output received.

So which is faster Distributed Query or Open query and why?

The answer is, generally, OPENQUERY would be faster but distributed queries could be as fast too.

For example lets’ say I have linked server between two SQL instances SQL1 and SQL2. And I have to do select count(*) on emp table in test database on remote server SQL2.

Distributed query would be something like

SELECT count(*) FROM [SQL2].[test].[dbo].[emp]<pre>

OPENQUERY would be  
<pre>SELECT * from OPENQUERY([SQL2], 'SELECT count(*) FROM [test].[dbo].[emp]')

If you look at the execution plan by running SET STATISTICS PROFILE ON, you can see that for executing distributed query, SQL1 sends a request to SQL2 to send the statistics information for table emp in the database test. Please note that the user account running this distributed query must have certain permissions in the remote server as documented in http://msdn.microsoft.com/en-us/library/ms175537.aspx to be able to gather data distribution statistics from the remote server else SQL Server might generate less efficient query plan and you will experience poor performance. UPDATE: This is going to be fixed in SQL Server 2012 so that if you have SELECT permission over columns of the index/statistics AND have SELECT permission over all columns of the FILTER (WHERE/HAVING) clause in the target server, you will be able to get the histogram. Important: The target or remote server has to have the SQL Server 2012 version with fix applied.

We have seen issues where too many connections running distributed queries end up in a SOSHOST_MUTEX wait while SQL Server is collecting data distribution statistics from the remote server. Also it has to be noted that a single query makes connection atleast two times to the remote server in case of distributed query, first connection to gather statistics and second connection to collect the actual data in the table.

Another disadvantage in case of distributed query is that though you have a WHERE clause in your query, you might notice that when the query is sent to retrieve the rows of a table in the remote server, SQL Server will just send a SELECT * FROM the remote table and then locally it filters out the necessary data after applying the predicates.

But in OPENQUERY, SQL Server sends the complete query to remote server SQL2 and resources of the SQL2 is spent in processing the query like parsing the SQL statements, generating a plan, filtering the rows as per predicates. Then the final resultset is sent to SQL1 which then just displayes what it received from SQL2.

etc

---

MS SQL