Nije horizontalno nego vertikalno

U praksi se ovaj uslov retko desava: "if a current month of data is primarily used for INSERT, UPDATE, DELETE, and MERGE operations while previous months are used primarily for SELECT queries,"

Malo ko ceka mesec dana da bi poceo da radi SELECT. Particije po mesecima deluju malo ekstremno za 'normalne' uslove. Ako radis za Wal-Mart onda je mozda i to normalno, inace je malo mnogo. Sva ta prica o particijama i nije tako jednostavna u praksi, moji DBA nisu bas odusevljeni kad to predlozimo, pa verovatno treba biti oprezan.

Ko sto ja shvatam particioniranje, a i u tekstu stoji 'primarily', sto znaci da ne ogranicava SELECT i na tekuci mesec, jedino sto prepostavlja da na prethodnim mesecima nema (toliko) INSERT, DELETE, UPDATE.
Pitanje do tebe, kao koje strucno lice predlazes, ako DBA nisu odusevljeni, ko je onda. Dali su pokusali i imaju lose iskustvo, il su oprezni jer im je onako kako je dobro?

Nije vertikalno, horizontalno je (samo interno u okviru jedne baze, za razliku od horizontalnog cepanja po federated serverima). Vertikalno particionisanje je teroijska osnova za normalizaciju i secenje kolona, ali po njenom opisu mislim da ne prica o tome vec o horizontalnom particionisanju po osnovu nekog opsega.

Ja imam ponesto da kazem na tu temu, ali ne sada u pola 12 :) Probacu da nadjem malo vrmena sutra da iskucam zasto ni ja nisam uopste bio odusevljen tim konceptom sem u veoma specificnim scenarijima,

OK, najupecatljivije iskustvo sa particionisanjem sam imao pre jedno pet godina na SQL 2000 u reinsurance firmi. Sve transakcione informacije sa berze (nije stock beza ali ipak je bilo oko 50,000 transakcija dnevno, skoncentrisanih tokom radnog vremena) su se slivale u OLTP bazu gde su se vezivale za ostale sirove analiticke podatke i to je posle sluzilo kao osnova za razne analitcke primene, od reportinga do OLAPa. Problem im je naravno bio taj sto su cesti inserti do zla boga usporavali bilo kakvu analizu, ceste analize su eskalirale u table S lockove i blokirale inserte, itd. i hteli su resenja ali nisu hteli nikako da imaju read uncommited transakcije na toj OLTP bazi.
Mi smo se nazalost svi poveli tom pricom o particionisanju na SQLu koja je tad bila u povoju i koja je obecavala taj sveti gral, jeftine S lockove na istoriji. Sa tim sto je u ono doba to bilo jos gore jer SQL 2000 nije imao fabricku podrsku za particionisanje. Radilo se tako sto sam pravis istorijsku i aktivnu tabelu, upis radis direktno nad aktivnom particijom a select radis nad view koji radi union baziran na WHERE koji sece opseg. Ni danas particiona funkcija ne radi nista drugacije iza kulisa sem sto imas bar neku pomoc oko reparticionisanja sto smo mi sve morali da radimo "rucno". Medjutim, uprokos teoriji i operativnim problemima, praksa je pokazala da nije bas sve tako sareno ni u resavanju osnovnog problema.

1. Query optimizer NE MOZE da iskljuci particiju iz execution plana ako WHERE sekcija nije direktan podset partition raspona, pa se svi queryji koji to striktno ne ispune svode na duplo vise aktivnost da se skeniraju indexi i tabele obe particije, cime se vracas na pocetak. Situacija se dalje pogorsava svim mogucim analizama koje nisu bazirane na vremenu vec na ostalim entitetima (npr analiza bazirana na kupcu) i dodatno pogorsava sa svim queryjima koji imaju datumski opseg u OR operaciji. Bukvalno smo dosli do toga da smo morali da teramo analiticare da unose <danas(0:0) za datetime u izdvojenoj AND stavci za svaki report bez obzira dal je baziran na datumima (sto nije bas docekano sa odusevljenjem jer neki reporti nisu bili uopste jednostavni). Ali ne lezi vraze, nisu oni svi kucali SQL nego su koristili analiticke aplikacije pa je dosao sledeci problem:
2. Dok god postoji iole mogucnost da where sekcija pokrije obe particije i sam execution plan ce pokrivati obe particije, cak iako je output iz jedne particije 0 redova. A to je problem sa ustaljenom praksom pisanja parametrizovanih query-a (a gro analitickih aplikacija to radi) i precompiled planova. Dakle cak iako aktuelna vrednost parametra uvek pada samo u jednu particiju obe ce ucestvovati, sto smo otkrili na tezi nacin. Samim tim particionisanje nije uradilo nista jer iako je execution plan radio union praznog seta da bi uopste dobio prazan set morao je da pravi IS/S lockove u aktivnoj tabeli i opet je morao da ceka na U,D,X i njihove derivate. Sad, moram da se ogranicim da nisam proverio ovaj problem na SQL 2005+, moze da se desi da je problem resen, ali jedino resenje koje postoji je da se execution plan ponovo kompajlira svaki put za query-je koji imaju vise od jedne particije sto onda kompletno ubija celu poentu execution plan kesiranja, tako da sumnjam.


E sad najbolji deo price. Problem se na kraju ispostavio da i ne postoji, samo je trebalo vratiti se na dizajn sistema i saslusati use-cases. Kad smo konacno uspeli da popricamo sa stakeholderima utvrdjeno je da apsolutno niko od korisnika podataka NE koristi aktivni set podataka (nisu traderi i ne rade midday analize). Problem je resen bukvalno razvodom istorije i aktivnog seta i primenom "rucnog" federated server principa . Job je tokom noci posle zatvaranja berze pretabao sve dnevne transakcije u dnevni backup i u batch operaciji dodao na ostale podatke u drugu istorijsku bazu koja je bila odskocna daska za sve ostale analize, aktivni set se onda resetuje i ceka sledeci dan. Posto vise nije bilo dnevnih UDX lockova na istorijskoj bazi nisu nam trebali ni S lockovi (dakle ova baza je mogla da ide u read uncommited) i sve je prosljakalo kako treba i optimalnom brzinom.

Samim tim zazirem od internih particija i smatram da postoji veoma mali broj slucajeva kad su one zaiste primenljive i neophodne. Za federated servere bih jos i nasao primenu u nekim scenarijima (za gornji priemer bi mi dobro dosao) ali ovako interno cepanje na filegrupe po samo jednom opsegu (najcesce istorijski po datumu) i u situacijama kad nemas puno istorisjkih selecta mi deluje kao vise problema nego koristi. Ljudi misle da im je interno particionisanje resenje a u stvari imaju na raspolaganju mnogo bolja, efikasnija i kvalitetnija resenja, samo treba trezvenije da pridju problemu. Da ne ulazimo u situacije kad problem uopste i ne postoji tipa transkacionih tabela sa 100 transakcija dnevno u kojima se izvestaj pravi jednom mesecno i slicno.

mmmx, hvala na isrpnom objasnjenju...i molila bih za misljenje za bolje, efikasnija i kvalitetnije resenje.
Insert je jednom mesecno, u jednom trenutku, u osnovne tabele. Podaci za report (analizu) se kreiraju od strane ownera baze producirajuci nove fizicke tabele, uz pomoc temp tabela, mnoge od tih tabela cak bivaju i neindeksirane...join nad istima traje...traje....
Posto je redizajn mislovna imenica, prvo sam mislila da se izbegnu temp tabele, da se izvrsi pravilno indeksiranje..a sad mi zenska intuicija :) kaze da particioniranje osnovnih tabela, izgleda kao nesto za sta bi se vredelo zaangazovati, nema se sta izgubiti (ili ima).
Cenim 'pionorsko' prethodno iskustvo na 2000, i nadam se da ce biti i nekih novih na 2005,2008...

Pa ovim se vracamo na onu pricu iz teme o trigerima. Ti ocekujes previse od foruma, iako nam dajes veoma veoma malo sturih informacija o tvom sistemu ocekujes neke univerzalne odgovore koje ce posluziti u svim situacijama. Tako nesto ne postoji, narocito ako je ceo sistem dizajniran nakaradno od pocetka a tebi su ruke vezane da sistem redizajniras.

Po ovome sto si sad napisala (pod uslovm da nema novih momenata) ti nemas nikakve koristi od particionisanja. Ako ti jednom mesecno imas insert to znaci da je ostatak vremena ta tabela slobodna za select i da to i nije transakciona tabela, samim tim odvajanje dela tabele nad kojim ces raditi UDX lockove nema svrhe jer UDX lockova uglavnom i nema.

Ono sto bi mogla za pocetak je da pogledas sta stari dizajn stavlja u temp tabele i da vidis da li je moguce od njih napraviti views, samo izbegavanje tempdb tabela ce ti u startu povecati brzinu drasticno jer neces imati cross-database joine. To bi ti izmedju ostalog i pomoglo dalje jer bi SQL mogao da napravi optimizovanjie planove secenjem joina, isto bi ti omogucilo da profilises novodobijene query-e i da vidis gde se gubi najvise vremena (sto ce verovatno biti neki table scan+loop) i onda ces videti gde mozes da stavis index ako treba. Stavise sam SQL server ti moze pomoci kroz Database Engine Tuning Advisor (DTA). Proguglaj malo o DTA i nauci kako se koristi i on ce ti biti od velike pomoci.

DOdatna alatka koju imas na raspolaganju je actual execution plan i njegove preporuke. Kad ga dobijes preporuke se ne vide na grafickom prikazu, rclick i i zaberi "Show execution plan XML" i u XMLu potrazi node <MissingIndexes>, on ce sadrzati preporuke SQLservera o tome koji bi mu indexi pomogli za bolje izvrsavanje ovog query-a. On ce sadrzati vise grupa preporucenih indexa zajedno sa skorom (Impact) za svaku grupu, pocni od najskupljeg i za pocetak najvise obrati paznju na EQUALITY i INEQUALITY grupe kolona u njemu (INCLUDE je manje vazan i neki put ume da pretera). Npr ovo je rezultat posle jedne BETWEEN operacije na neindeksiranom polju ExtendedAmount:

Zaboravio sam i na ovo. Svaki put kad je Query optimizer primoran da koristi neoptimalno resenje usled nedostatka index zapamtice to u management tabeli. Ako uradis:

select * from sys.dm_db_missing_index_details

dobices spisak svih tih dogadjaja. Samo imaj u vidu, ako je baza mnogo neoptimizovana dobices veliki spisak koji moze biti malo demoralisuci. Ali, prvo se resi tempdb-a

Ta varijanta da se resimo temp tabela je pozeljno neophodna.
Izvinjavam se na oskudnosti, al zar i skolski primeri nisu takvi, a ipak su dobra polazna tacka

'To expand on this example, suppose you want to move one month of read-only data from this table to a data warehouse table for analysis. With partitioning, subsets of data can be separated quickly into staging areas for offline maintenance and then added as partitions to existing partitioned tables, assuming these tables are all in the same database instance. Operations such as these typically take seconds, instead of the minutes or hours they took in previous releases.

Finally, partitioning a table or index might improve query performance if the partitions are designed correctly, based on the types of queries you frequently run and on your hardware configuration'

Znaci podaci koji se izdvajaju ..se dalje analiziraju i koreguju....i mogu da se vrate u glavnu tabelu (jos uvek ne particioniranu).

Mozda cu promeniti nadavesela u tvrdoglava bubalica.