Monitoraggio e gestione degli indici del database di Planning Server
Aggiornato: 2009-04-30
Nel presente articolo:
Esame degli indici delle tabelle dei gruppi di misura
Verifica della frammentazione dell'indice
Riorganizzazione degli indici
Ricostruzione degli indici
Come la maggior parte dei sistemi di database, anche il sistema Planning Server utilizza gli indici per garantire prestazioni ottimali. INSERT/UPDATE su indici cluster o non cluster causa la divisione delle pagine e una conseguente frammentazione dell'indice che nel tempo diventa sempre più grave e determina un degrado delle prestazioni del sistema. È importante che l'amministratore del database esegua il monitoraggio dello stato di frammentazione dell'indice e sia in grado di adottare le misure adeguate in caso di frammentazione eccessiva.
È consigliabile monitorare tutte le tabelle del database dell'applicazione di PerformancePoint Planning per tenere sotto controllo lo stato di frammentazione dell'indice, tuttavia si consiglia di prestare particolare attenzione alle tabelle dei gruppi di misura, ovvero le tabelle il cui nome inizia con il prefisso "MG_". Nei database dell'applicazione queste tabelle infatti subiscono frequenti operazioni di inserimento, aggiornamento ed eliminazione, ad esempio invii di assegnazioni ed esecuzioni di regole di calcolo. Per alcune tabelle dei gruppi di misura, inoltre, le attività sono maggiori che per altre. La frammentazione degli indici delle tabelle "MG_*" nel tempo diventa grave e, se gli indici non vengono ricostruiti o organizzati regolarmente, si verifica una significativa riduzione delle prestazioni del sistema.
Esame degli indici delle tabelle dei gruppi di misura
Alla creazione di un modello, Planning Server crea un indice cluster sulla nuova tabella "MG_*", che include tutte le chiavi delle dimensioni della tabella. L'ordine delle chiavi delle dimensioni dell'indice cluster è stabilito in modo arbitrario da Planning Server e potrebbe non riflettere in modo ottimale l'utilizzo della tabella del gruppo di misure da parte del cliente, ad esempio writeback, caricamento di dati e calcolo in base a regole. Potrebbe essere necessario che l'amministratore del database modifichi l'ordine delle colonne dell'indice cluster.
L'indice cluster seguente, ad esempio, è stato creato sulla tabella MG_Strategic_Plan_MeasureGroup_default_partition con l'ordine predefinito di colonne seguente. È possibile modificare l'ordine delle colonne dell'indice affinché rispecchi l'utilizzo della tabella dei gruppi di misure, spostando la colonna Entity_MemberID al primo posto nell'indice perché gli utenti di Componente aggiuntivo PerformancePoint per Excel eseguono spesso operazioni di writeback in base a entità.
CREATE CLUSTERED INDEX [ClusteredIndex_default_partition] ON [dbo].[MG_Strategic_Plan_MeasureGroup_default_partition]
(
[Scenario_MemberId] ASC,
[Time_Month] ASC,
[Account_MemberId] ASC,
[BusinessProcess_MemberId] ASC,
[Entity_MemberId] ASC,
[TimeDataView_MemberId] ASC,
[Currency_MemberId] ASC,
[BusinessDriver_MemberId] ASC,
[Product_MemberId] ASC,
[Flow_MemberId] ASC,
[Intercompany_MemberId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Esempio
Nell'esempio seguente è illustrato come ricostruire un indice con l'opzione ONLINE su una tabella del gruppo di misure in un database dell'applicazione di PerformancePoint Planning.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);
È consigliabile che l'amministratore del database crei un processo nel computer in cui viene eseguito Microsoft SQL Server 2005 per deframmentare l'indice di ogni tabella del gruppo di misure. In base alla situazione di frammentazione, sarà possibile pianificare il processo per la riorganizzazione e quindi la ricostruzione dell'indice con frequenze diverse.
Si potrebbe, ad esempio, decidere di riorganizzare l'indice una volta al giorno e ricostruirlo una volta alla settimana. Alcuni indici, ad esempio gli indici cluster della tabella del gruppo di misure, potrebbero necessitare di ricostruzioni più frequenti. Pianificare i processi di ricostruzione degli indici in orari con carichi di lavoro ridotti, ad esempio a mezzanotte. Entrambi i metodi possono essere utilizzati in linea, ma non sulle tabelle, in quanto contengono tipi di dati line-of-business. Per eseguire la ricostruzione dell'indice non in linea, è necessario innanzitutto attivare lo stato non in linea per l'applicazione. Per informazioni su come attivare lo stato non in linea per un'applicazione, vedere la Guida di Planning Administration Console.
Verifica della frammentazione dell'indice
Le modifiche al database nel tempo possono causare la dispersione o frammentazione nel database delle informazioni dell'indice. Indici con un livello di frammentazione elevato possono causare il degrado delle prestazioni e aumentare il tempo di risposta dell'applicazione.
In SQL Server 2005 è possibile risolvere la frammentazione dell'indice riorganizzandolo o ricostruendolo. Per determinare il livello di frammentazione dell'indice e quindi il metodo di deframmentazione da utilizzare, è necessario prima analizzare l'indice.
Esempio
Nell'esempio seguente viene illustrato come monitorare la frammentazione dell'indice.
Quando si esegue l'istruzione di codice seguente:
SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('MG_Strategic_Plan_MeasureGroup_default_partition'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
viene restituito un set di risultati simile al seguente.
| id_indice | nome | frammentazione_media_in_percentuale |
|---|---|---|
1 |
MG_Strategic_Plan_MeasureGroup_default_partition_PK |
23.076923076923077 |
Per ulteriori informazioni sulla frammentazione, vedere SQL Server Books Online. In questo esempio si consiglia di riorganizzare MG_Strategic_Plan_MeasureGroup_default_partition_PK perché il valore frammentazione_media_in_percentuale è inferiore al 30%.
Se la frammentazione fosse stata superiore al 30%, sarebbe stato consigliabile ricostruire l'indice.
Nota
Il valore di 30% deriva dalla raccomandazione in SQL Server Books Online, ma può essere modificato in base alle esigenze dell'organizzazione.
Riorganizzazione degli indici
Se il livello di frammentazione dell'indice non è eccessivo, è consigliabile riorganizzare l'indice. Se invece la frammentazione ha raggiunto un livello molto elevato, la ricostruzione dell'indice garantirà risultati migliori. Per le linee guida sulla frammentazione, vedere Verifica della frammentazione dell'indice.
Esempio
Nell'esempio seguente è illustrato come riorganizzare l'indice di chiave primaria su una tabella del gruppo di misure in Planning Server. Per riorganizzare uno o più indici, utilizzare l'istruzione ALTER INDEX con la clausola REORGANIZE.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REORGANIZE ;
La riorganizzazione dell'indice non è un'operazione completa come la ricostruzione, ma consente solo di eliminare la frammentazione a livello foglia senza spostare l'oggetto, operazione che garantirebbe una migliore densità di analisi dell'extent. Il comando per la riorganizzazione dell'indice viene sempre eseguito in linea. Durante la riorganizzazione dell'indice non vengono mantenuti blocchi che richiedono molto tempo per essere elaborati. Per informazioni sulla riorganizzazione di indici e i vantaggi e gli svantaggi della riorganizzazione e della ricostruzione, vedere SQL Server Books Online.
Ricostruzione degli indici
La ricostruzione di un indice implica l'eliminazione dell'indice e la creazione di un nuovo indice. La frammentazione non sarà più presente nel nuovo indice creato.
Nell'esempio seguente è illustrato come ricostruire un indice con l'opzione ONLINE su una tabella del gruppo di misure in Planning Server.
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
REBUILD WITH (ONLINE = ON);
Pianificare un processo di SQL Server per la deframmentazione dell'indice su ogni tabella del gruppo di misure. Dopo aver determinato il livello di frammentazione, pianificare il processo per la riorganizzazione e la ricostruzione dell'indice con frequenze diverse. Si potrebbe decidere di riorganizzare l'indice una volta al giorno in orari con carichi di lavoro ridotti, ad esempio a mezzanotte, e quindi ricostruire l'indice una volta alla settimana. Entrambi i metodi possono essere utilizzati in linea, ma non sulle tabelle, in quanto contengono tipi di dati line-of-business.
La ricostruzione di un indice garantisce in genere risultati migliori rispetto alla riorganizzazione, in quanto consente di rimuovere tutti i livelli di frammentazione, a partire sia dal livello foglia che dall'albero B, riequilibrando così l'albero. Vengono inoltre aggiornate le statistiche, come avviene per la scansione completa con statistiche accurate.
Per ulteriori informazioni sulla ricostruzione di indici, vedere SQL Server Books Online.