Intrebari generale

Re: sp_spaceused in SQL 2000 index_size

rsocol — Fri, 20 Jun 2008 16:58:14 GMT

M-am mai gândit puţin la informaţiile returnate de query-urile din mesajul anterior şi cred că am înţeles cam la ce se referă fiecare. De exemplu, dacă execut query-urile de mai sus (după un DBCC UPDATEUSAGE) pe o tabelă ceva mai mare, obţin următoarele rezultate:

name                             rows       reserved        data   index_size   unused
------------------------- ----------- -------------- ----------- ------------ --------
Inregistrari contabile         747026      256512 KB   101360 KB    153560 KB 1592 KB

indid name                                         dpages      reserved    used
------ -------------------------------------------- ----------- ----------- -----------
1      PK_Inregistrari contabile                    12670       32064       31865
2      ID_Nota                                      1121        1153        1128
3      CD                                           1784        1833        1795
4      CC                                           1672        1713        1683
5      ID_Detaliu cu TipDetaliu                     5193        5233        5220
9      CD cu ID_Nota cu Valoare                     3065        3105        3088
10     CC cu ID_Nota cu Valoare                     2965        3017        2987
11     TipDetaliu cu ID_Nota cu ID_Detaliu          3245        3289        3270

Din asta, eu înţeleg următorul lucru: index-ul ID_Nota are alocate 1153 de pagini, dintre care doar 1128 sunt folosite efectiv (iar restul de 25 de pagini sunt neutilizate). Dintre cele 1128 de pagini folosite, 1121 de pagini sunt "data pages" adică leaf-level în arborele respectiv, iar celelalte 7 pagini sunt de un nivel superior, conţinând pointer-i spre paginile leaf-level sau spre alte pagini de nivel intermediar (mai precis, probabil că este 1 pagină top-level, care are pointeri spre 6 pagini de nivel intermediar, care conţin pointeri către cele 1121 de pagini leaf-level; ar exista şi mai mult de 3 nivele dacă aş fi avut indecşi pe coloane mai mari, dar în acest caz au fost suficiente doar aceste 3 nivele).

Pentru index-ul clustered, informaţiile sunt afişate în alt mod: la coloana reserved sunt incluse toate paginile alocate acelei tabele (pentru toţi indecşii). Din cele 32064 pagini alocate acestei tabele, 19343 sunt alocate indecşilor non-clustered (dacă facem suma valorilor din coloana reserved pentru ceilalţi indecşi), iar restul de 12721 sunt alocaţi indexului clustered (inclusiv paginilor de date, care sunt stocate în leaf-level-ul indexului clustered. Dpdv al paginilor folosite, din cele 31865 pagini, 19171 sunt folosite pentru ceilalţi indecşi, iar restul de 12694 sunt folosiţi de indexul clustered. Din cele 12694 de pagini folosite de indexul clustered, 12670 sunt leaf-level (conţinând datele efective), iar restul de 24 de pagini sunt pagini de un nivel superior (probabil 1 pagină top-level şi 23 de pagini de nivel intermediar). Spaţiul neutilizat alocat pentru indexul clustered este de 27 de pagini (adică 12721 minus 12694).

În concluzie, spaţiul raportat de sp_spaceindex ca fiind utilizat de indecşi (153560 KB, adică 19195 pagini) este într-adevăr cel corect, fiind compus din 19171 de pagini pentru indecşii non-clustered şi 24 de pagini pentru indexul clustered (excluzând paginile de date, care au fost raportate pe coloana data: 101360 KB, adică 12670 pagini). Iar spaţiul unused (1592 KB, adică 199 pagini) este constituit din diferenţa dintre reserved şi used pentru indecşii non-clustered (în total 172 pagini) plus cel neutilizat la indexul clustered (27 de pagini).

Răzvan

Re: sp_spaceused in SQL 2000 index_size

rsocol — Fri, 20 Jun 2008 16:30:08 GMT

Dacă vrei să vezi cât spaţiu este alocat pentru fiecare index non-clustered al unei tabele, poţi folosi ceva de genul:

SELECT indid, name, dpages*8 as allocated_kb, used*8 as used_kb
FROM sysindexes WHERE id=object_id('Nume tabela')
AND INDEXPROPERTY(id,name,'IsStatistics')=0
AND indid NOT IN (0,1,255)

Pentru mai multe informaţii, vezi topicul referitor la sysindexes din Books Online. Totuşi, din pagina respectivă nu este foarte clar care e diferenţa dintre coloanele dpages, reserved şi used (şi care dintre ele conţine informaţia care contează cu adevărat). De exemplu, dacă execut următorul query în baza de date pubs:

EXEC sp_spaceused 'titleauthor'

SELECT indid, name, dpages, reserved, used
FROM sysindexes WHERE id=object_id('titleauthor')
AND INDEXPROPERTY(id,name,'IsStatistics')=0

obţin următoarele rezultate:

name                      rows        reserved   data    index_size   unused
------------------------- ----------- ---------- ------- ------------ ------
titleauthor               25          56 KB      8 KB    48 KB        0 KB

indid name                      dpages      reserved    used
------ ------------------------- ----------- ----------- -----------
1      UPKCL_taind               1           7           7
2      auidind                   1           2           2
3      titleidind                1           2           2

Dacă încercăm să adunăm valorile din oricare din cele trei coloane şi să înmulţim cu 8 KB (dimensiunea unei pagini), nu obţinem nicicum spaţiul raportat la index_size. Singurul mod de a ajunge la această valoare este să calculăm (used-dpages)*8, pentru indexul cu id-ul 1 (care e oarecum formula pe care o regăsim şi dacă facem un EXEC sp_helptext 'sp_spaceused'). Deci, cel puţin în SQL Server 2000, nu am găsit o metodă de a obţine informaţii foarte clare şi foarte exacte despre spaţiul ocupat de fiecare index, dar oricum, query-ul de mai sus furnizează nişte rezultate orientative destul de corecte.

Răzvan

Re: sp_spaceused in SQL 2000 index_size

crestinul — Fri, 20 Jun 2008 12:19:25 GMT

miralon:
Cu siguranta nu am notat tot ce gandeam in momentul postarii intrebarii de mai sus.
Ceea ce incerc eu sa intuiesc este mecanismul alocarii si dealocarii paginilor de date pentru indecsi si unde anume sunt ele fizic stocate. Avand indecsi nonclusterd, eu cred ca paginile sunt alocate dinamic, nu sunt fizic stocate pe disc, asa cum e cazul indexului clustered.
Asa mi-as explica diferenta dintre valoarea spatiului de pe disc al fisierelor bazei de date si valoarea dimensiunii cumulate a indecsilor.
Daca nu am prins acest mecanism, please, dati-mi o mana de ajutor...

DIn ceea ce stiu eu indexii in sql se stocheaza sub forma de arbori b.Diferenta de stocare intre indexii nonclusterd si clustered consta in modul cum se stocheaza nodurile frunza in cazul indexilor clustered nodurile frunza sunt chiar paginile de date ale tabelei pe care e indexul iar la indexii nonclusterd in paginile frunza sunt pointeri la paginile de date efective.cel putin asa am itneles eu...

Re: sp_spaceused in SQL 2000 index_size

miralon — Fri, 20 Jun 2008 10:48:05 GMT

Pt Razvan:

Perfect de acord cu DBCC UPDATEUSAGE...ai idee cat dureaza? eu, sincer, nu am incercat...in BOL se spune ca e consumatoare de timp si resurse...
Nu este necesar sa fac o noua verificare...daca citesti posturile, am specificat la un moment dat ca am doar nonclustered pe tabela...si sunt vreo 5
partea asta de "interfata " am cercetat-o si eu ...ce nu stiu in acest moment este cum se calculeaza valorile din sysindexes ...
- mai afirmi: "daca apare in continuare o dimensiune considerabila, atunci ...exista niste indecsi..." -aici te apropii de punctul fierbinte...da, stiu ca exista, ca sunt nonclustered, dar de ce aceasta dimensiune considerabila in cazul indecsilor nonclustered...se face o estimare a totalului paginilor necesare indexului, sau acele pagini chiar sunt fizic stocate...si daca da...unde?
Intelegi unde bat?
Multam ca te-ai implicat, poate, cu ajutorul vostru, voi reusi sa patrund intregul mecanism.

Re: sp_spaceused in SQL 2000 index_size

rsocol — Fri, 20 Jun 2008 10:33:01 GMT

Înainte de a executa sp_spaceused, este recomandabilă executarea unui DBCC UPDATEUSAGE. Dacă apare în continuare o dimensiune considerabilă, atunci înseamnă că există nişte indecşi (non-clustered, probabil) şi am putea să verificăm acest lucru cu un SELECT din sysindexes, aşa cum scria şi Diana.

Răzvan

Re: sp_spaceused in SQL 2000 index_size

miralon — Fri, 20 Jun 2008 10:24:04 GMT

Multumesc, Diana.
Ma voi informa si, dca mai sunt intrebari, le voi adresa cu incredere.
Multumesc.

Re: sp_spaceused in SQL 2000 index_size

Diana — Fri, 20 Jun 2008 10:21:16 GMT

Cele mai multe amanunte le gasesti in cartile lui Kalen Delaney. De exemplu in "Inside MS SQL Server 2005" exista un capitol "Index internals and management" - aici sunt exemple bazate pe dmv-uri, nu pe "sysindexes" dar "faptele" raman. Pe cate imi aduc aminte subiectul este detaliat si in "Inside SQL 2000" - nu mai am cartea la indemana acum...

Vezi si conferintele lui Kimberly Tripp: http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=4daca6d9-c52b-4f85-a47c-20f252883e20

Re: sp_spaceused in SQL 2000 index_size

miralon — Fri, 20 Jun 2008 10:09:10 GMT

Multumesc, Diana, pentru link.
Am citit si in BOL ce era legat de sp_spaceused, am analizat si corpul procedurii...
Intr-aadevar, valorile sunt culese din sysindexes... eu as vrea sa aflu cum se updateaza coloana used din sysindexes...cum si de unde sunt calculate acele valori...as vrea sa trec dincolo de interfata si nu stiu cum....

Re: sp_spaceused in SQL 2000 index_size

miralon — Fri, 20 Jun 2008 10:02:24 GMT

Cu siguranta nu am notat tot ce gandeam in momentul postarii intrebarii de mai sus.
Ceea ce incerc eu sa intuiesc este mecanismul alocarii si dealocarii paginilor de date pentru indecsi si unde anume sunt ele fizic stocate. Avand indecsi nonclusterd, eu cred ca paginile sunt alocate dinamic, nu sunt fizic stocate pe disc, asa cum e cazul indexului clustered.
Asa mi-as explica diferenta dintre valoarea spatiului de pe disc al fisierelor bazei de date si valoarea dimensiunii cumulate a indecsilor.
Daca nu am prins acest mecanism, please, dati-mi o mana de ajutor...

Re: sp_spaceused in SQL 2000 index_size

miralon — Fri, 20 Jun 2008 09:55:55 GMT

Da, pe tabela mea am cativa indecsi nonclustered.

Re: sp_spaceused in SQL 2000 index_size

Diana — Fri, 20 Jun 2008 09:52:27 GMT

Vezi http://support.microsoft.com/kb/90758 - incearca sa rulezi:

SELECT *
FROM sysindexes
WHERE id = object_id('table_name')

Exista totusi indecsi (nonclustered) pe tabelul tau?

Eu am rulat query-ul de mai sus pe SQL 2005 pentru un tabel fara indecsi. In coloana index_size figureaza in acest caz chiar

dimensiunea tabelului (indexid=0, "heap").

Re: sp_spaceused in SQL 2000 index_size

miralon — Fri, 20 Jun 2008 09:47:26 GMT

Nu am mentionat un amanunt: Fisierele bazei de date ocupa cca 700MB pe disk. So....

sp_spaceused in SQL 2000 index_size

miralon — Fri, 20 Jun 2008 09:27:50 GMT

Buna tuturor

Am folosit procedura stocata sp_spaceused in SQL 2000 pentru a afla dimensiunea unei tabele in MB...totul Ok
Am remarcat, in cazul uneia dintre ele , cu o valoare a dimensiunii datelor de cca 100MB, o valoare de cca 11GB pentru indecsi.
Nu am niciun index clustered pe tabela.
Ce reprezinta in acest caz valoarea evidentiata in index_size? O valoare estimata?
Poate si-a mai pus aceasta intrebare unul dintre voi si, sper, a gasit si raspuns...
Care credeti ca e raspunsul real ?

Multam frumos.