UGISS Community

Ciao a tutti,

sto lavorando con un database SQL Server 2000 in cui le tabelle sono state disegnate in modo che l'indice cluster sia sempre sulla chiave primaria (default di SQL Server quando la chiave primaria viene creata insieme alla tabella). Con il passare del tempo il database è cresciuto e ora ho qualche problema di performance, le query difficilmente riescono ad utilizzare l'indice cluster.

Esempio:

CREATE TABLE TMPT1

(ID INTEGER IDENTITY(1, 1) NOT NULL,

CODICE VARCHAR(20),

DESCR VARCHAR(40)

CREATE TABLE TMPT2

(ID INTEGER IDENTITY(1, 1) NOT NULL,

IDTMPT1 INTEGER NULL

ADD FOREIGN KEY([IDTMPT1]) REFERENCES [dbo].[TMPT1] ([ID])

Vorrei eliminare gli indici cluster creati sulla chiave primaria (tipicamente l'ID come nell'esempio), sulla chiave primaria creare un indice NON cluster e poi analizzare, tabella per tabella, i campi su cui ha più senso mettere l'indice cluster in base alle query e al tipo di tabella.

Se non sbaglio per eliminare l'indice cluster, in questa situazione, devo prima eliminare tutte le chiavi esterne che puntano al campo ID sul quale è presente l'indice cluster.

Vorrei sviluppare una procedura che riceva in input il nome della tabella e svolga questo lavoro... prima di iniziare, volevo sapere se avete suggerimenti per utilizzare eventuali stored procedure o funzioni native di SQL Server.

Grazie, ciao

Sergio

Scusa, ma prima di porti il problema dell'indice clustered... hai pensato se non sia il caso di creare semplicemente degli indici non-clustered sulle tabelle?

Ricorda che usare un indice clustered è un'arma a doppio taglio. Se da una parte ottimizzi qualche query, dall'altra penalizzi le prestazioni su molte altre (la chiave dell'indice clustered è duplicata in tutti gli indici non-clustered). Avere un indice clustered su un campo int identity (come è il tuo caso, mi pare) nel 90% dei casi è il compromesso migliore per supportare altri indici non-clustered sulla stessa tabella.

marco.russo:

Scusa, ma prima di porti il problema dell'indice clustered... hai pensato se non sia il caso di creare semplicemente degli indici non-clustered sulle tabelle?

Ricorda che usare un indice clustered è un'arma a doppio taglio. Se da una parte ottimizzi qualche query, dall'altra penalizzi le prestazioni su molte altre (la chiave dell'indice clustered è duplicata in tutti gli indici non-clustered). Avere un indice clustered su un campo int identity (come è il tuo caso, mi pare) nel 90% dei casi è il compromesso migliore per supportare altri indici non-clustered sulla stessa tabella.

Ciao sgovoni, sono d'accordo con l'ultima analisi di Marco (che saluto ). Nel tuo esempio, purtroppo non ci mostri le query che farai indicativamente su quei dati e sarebbe interessante vedere come ti "butti" su quelle tabelle. In questo modo potremmo darti una mano per capire quali indici (molto probabilmente nonclustered) possono fare al caso tuo.

In effetti mi è capitato qualche volta di spostare un indice clustered, ed è successo nei casi in cui ero sicuro che la queri fosse di tipo range search, oppure che usasse order by e/o group by (immagina le date di una tabella abbastanza grande con una query ad esempio "WHERE Campo BETWEEN DataDa AND DataA"). Però ero altresì sicuro che quella query, o quelle due, molto simili tra loro, fossero l'unico punto di accesso alla tabella stessa.

Effettivamente, in quel caso ho avuto miglioramenti in termini di prestazioni.
Un indice clustered infatti è molto efficace su query di questo tipo.

Grazie per i suggerimenti,

sux_stellino:

sarebbe interessante vedere come ti "butti" su quelle tabelle

la tabella su cui avevo focalizzato l'attenzione è la quella dei documenti di trasporto che viene interrogata in diversi modi: vengono fatti dei posizionamenti (Seek) per l'ID, vengono estratti i DdT per anno, numero, data documento o cliente (e potenzialmente il filtro WHERE può essere applicato a gran parte dei campi del documento). Anche gli ordinamenti sono variabili a seconda dell'operatore che estrae i dati.

Ho provato ad eliminare l'indice cluster sul campo ID ricreandolo sui campi anno, numero e data documento, ma non ho visto un significativo miglioramento... e magari ho peggiorato altre situazioni, come diceva Marco.

Credo che farò il ripristino dell'indice cluster sul campo ID e valuterò eventuali indici non cluster, anche se credo che non ci sia una regola precisa per individuarli, specialmente quando le query non sono di diverso tipo.

Ciao

Sergio

sgovoni:

Grazie per i suggerimenti,

sux_stellino:

sarebbe interessante vedere come ti "butti" su quelle tabelle

la tabella su cui avevo focalizzato l'attenzione è la quella dei documenti di trasporto che viene interrogata in diversi modi: vengono fatti dei posizionamenti (Seek) per l'ID, vengono estratti i DdT per anno, numero, data documento o cliente (e potenzialmente il filtro WHERE può essere applicato a gran parte dei campi del documento). Anche gli ordinamenti sono variabili a seconda dell'operatore che estrae i dati.

Purtroppo a volte succede che poi il cliente chiede filtri su filtri credendo di avere sempre le stesse performance. Capisci bene che con una where che può interessare quasi tutti i campi della tabella, non hai un grosso spazio di manovra.

sgovoni:

Ho provato ad eliminare l'indice cluster sul campo ID ricreandolo sui campi anno, numero e data documento, ma non ho visto un significativo miglioramento... e magari ho peggiorato altre situazioni, come diceva Marco.

Credo che farò il ripristino dell'indice cluster sul campo ID e valuterò eventuali indici non cluster, anche se credo che non ci sia una regola precisa per individuarli, specialmente quando le query non sono di diverso tipo.

Sono d'accordo, lascerei il clustered dove sta. Come ti dicevo sopra, non hai un grosso spazio in cui muoverti, a meno che non poni qualche limite ai filtri.

ciao! 

sux_stellino:

Sono d'accordo, lascerei il clustered dove sta.

Prendo la buona notizia di questa analisi: a posteriori mi accorgo che non ho fatto un grosso sbaglio nel lasciare l'indice cluster sui campi ID (by default per SQL Server).

Per i filtri temo che farò fatica a porre dei limiti, specialmente ora che gli utenti si sono abituati. Ora però ho qualche elemento in più per "spiegare" le performance.

Grazie, ciao !

Sergio