Partisjonere tabeller i sql server. Opprette en fysisk databasemodell: Performance Engineering
I denne artikkelen skal jeg demonstrere spesifikasjonene til utføringsplaner for spørringer når du får tilgang til partisjonerte tabeller. Merk at det er stor forskjell mellom partisjonerte tabeller (som bare ble tilgjengelig i SQL Server 2005) og partisjonerte visninger (som var tilgjengelige i SQL Server 2000 og fortsatt er tilgjengelige i SQL Server 2005 og senere versjoner). Jeg vil demonstrere detaljene til spørreplaner for partisjonerte visninger i en annen artikkel.
Se tabellen
La oss lage en enkel partisjonert tabell:
lag partisjonsfunksjon pf(int) som område for verdier (0, 10, 100)
lag partisjonsskjema ps som partisjon pf alle til ()
lag tabell t (a int, b int) på ps(a)
Dette skriptet lager en tabell med fire partisjoner. SQL Server tildelte verdier til ID-ene til hver av de fire partisjonene som vist i tabellen:
| PtnId | Verdier |
| 1 | t.a<= 0 |
| 2 | 0 < t.a <= 10 |
| 3 | 10 < t.a <= 100 |
| 4 | 100 < t.a |
La oss nå se på en spørringsplan som ville tvinge optimalisereren til å bruke en tabellskanning:
……|–Konstant skanning(VERDI:(((1)),((2)),((3)),((4))))
…….|–Tabell Scan(OBJECT:([t]))
I planen ovenfor spesifiserer SQL Server eksplisitt alle partisjons-ID-er i "Constant Scan"-setningen, som implementerer tabellskanningen og leverer dataene til den nestede loop-join-operatøren. Det er verdt å huske her at den nestede loop join-operatoren krysser den interne tabellen (i dette tilfellet en full tabellskanning) én gang for hver verdi fra den ytre tabellen (i vårt tilfelle "Konstant skanning"). Så vi skanner tabellen fire ganger; én gang for hver seksjons-ID.
Det skal også bemerkes at tilkoblingen av nestede løkker tydelig viser at den ytre tabellen er verdiene til kolonnen der partisjons-ID-ene er lagret. Selv om det ikke er umiddelbart synlig i tekstvisningen til utførelsesplanen (dessverre merker vi noen ganger ikke denne informasjonen), bruker tabellskanningen en kolonne med ID-ene til seksjonene som er valgt for å utføre skanningen og bestemme hvilken seksjon som skal skanning. Denne informasjonen er alltid tilgjengelig i den grafiske utførelsesplanen (du må se på egenskapene til tabellvisningsoperatøren), så vel som i XML-representasjonen av utførelsesplanen for spørringen:
Statisk seksjonsfiltrering
Vurder følgende spørring:
velg * fra t hvor a< 100
|–Nested Loops(Inner Join, Ytre REFERANSER:() PARTISJONSID:())
…….|–Konstant skanning(VERDIER:(((1)),((2)),((3))))
<(100)) PARTITION ID:())
Predikat "a"<100» явно исключает все строки для секции со значением идентификатора равным 4. В данном случае, нет смысла в просмотре соответствующей секции, поскольку ни одна из строк этой секции не удовлетворяет условию предиката. Оптимизатор учитывает этот факт и исключает эту секцию из плана исполнения запроса. В операторе «Constant Scan» указаны только три секции. У нас принято называть это статической фильтрацией секций (static partition elimination), поскольку мы знаем, что во время компиляции список просматриваемых секций остаётся статичным.
Hvis statisk filtrering eliminerer alle unntatt én partisjon, trenger vi ikke operatørene Constant Scan og Nested Loops Join i det hele tatt:
velg * fra t hvor a< 0
|–Tabell Scan(OBJECT:([t]), HVOR:([t].[a]<(0)) PARTITION ID:((1)))
Merk at "PARTITION ID:((1))"-setningen, som spesifiserer ID-en til partisjonen som skal skannes, nå er en del av Table Scan-setningen.
Dynamisk seksjonsfiltrering
I noen tilfeller kan ikke SQL Server fastslå at sammensetningen av seksjonene som vises, ikke vil endres på kompileringstidspunktet, men den kan se at noen seksjoner kan ekskluderes.
velg * fra t hvor a< @i
|–Nested Loops(Inner Join, Ytre REFERANSER:() PARTISJONSID:())
…….|–Filter(HVOR:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
…….| |–Konstant skanning(VERDIER:(((1)),((2)),((3)),((4))))
…….|–Tabell Scan(OBJECT:([t]), HVOR:([t].[a]<[@i]) PARTITION ID:())
Dette er en parameterisert spørring. Siden vi ikke kjenner verdien til parameteren før utførelse (det faktum at jeg bruker en konstant som parameter i samme batch endrer ikke situasjonen), er det umulig å bestemme verdien av seksjonsidentifikatoren for "Konstant skanning" ”-operatør på kompileringsstadiet. Du må kanskje bare se på seksjon 1, eller det blir seksjon 1 og 2, og så videre. Derfor er alle fire seksjons-ID-ene spesifisert i denne setningen, og vi bruker kjøretidsfiltrering av seksjons-ID-er. Vi kaller dette Dynamic Partition Elimination.
Filteret sammenligner hver seksjonsidentifikator med resultatet av spesialfunksjonen "RangePartitionNew". Denne funksjonen beregner resultatene av å bruke partisjoneringsfunksjonen på parameterverdien. Argumentene til denne funksjonen (fra venstre til høyre) er:
- verdien (i dette tilfellet @i-parameteren) som vi ønsker å vise på seksjons-IDen;
- et boolesk flagg som indikerer om partisjoneringsfunksjonen viser venstre (0) eller høyre (1) grenseverdier;
- seksjonsgrenseverdier (i dette tilfellet er disse 0, 10 og 100).
I dette eksemplet, siden @i er 0, er resultatet av "RangePartitionNew" 1. Dermed skanner vi bare partisjonen med ID 1. Merk at i motsetning til eksemplet med statisk partisjonsfiltrering, selv om vi bare skanner én partisjon, er vi -Vi har fortsatt "Constant Scan" og "Nested Loops Join". Grunnen til at vi trenger disse setningene er fordi vi ikke vet hvilke seksjoner som skal skannes før utførelsesfasen.
I noen tilfeller kan optimalisereren allerede ved kompilering bestemme at vi bare skal skanne én seksjon, selv om den ikke kan bestemme hvilken. For eksempel, hvis en spørring bruker et partisjonsnøkkelekvivalenspredikat, vet vi at bare én partisjon kan tilfredsstille denne betingelsen. Derfor, til tross for at vi måtte ha dynamisk filtrering av seksjoner, trenger vi ikke lenger operatørene "Constant Scan" og "Nested Loops Join". Eksempel:
velg * fra t hvor a = @i
|–Tabell Scan(OBJECT:([t]), WHERE:([t].[a]=[@i]) PARTISJONSID:(RangePartitionNew([@i],(0),(0),(10) ),(100))))
Kombinasjon av statisk og dynamisk seksjonsfiltrering
SQL Server kan kombinere statisk og dynamisk partisjonsfiltrering i en enkelt spørringsplan:
velg * fra t hvor a > 0 og a< @i
|–Nested Loops(Inner Join, Ytre REFERANSER:() PARTISJONSID:())
……|–Filter(HVOR:(<=RangePartitionNew([@i],(0),(0),(10),(100))))
……| |–Konstant skanning(VERDIER:(((2)),((3)),((4))))
……|–Tabell Scan(OBJECT:([t]), HVOR:([t].[a]<[@i] AND [t].[a]>(0)) PARTISJONS-ID:())
Merk at i den siste planen er det statisk filtrering av ID=1-seksjonen ved bruk av "Konstant skanning", og det er også dynamisk filtrering for andre seksjoner definert av predikater.
$partisjon
Du kan eksplisitt kalle RangePartitionNew-funksjonen ved å bruke $partition:
velg *, $partisjon.pf(a) fra t
|–Compute Scalar(DEFINE:(=RangePartitionNew([t].[a],(0),(0),(10),(100))))
……|–Nestede løkker(indre skjøt, YTRE REFERANSER:() PARTISJONSID:())
………..|–Konstant skanning(VERDIER:(((1)),((2)),((3)),(4))))
………..|–Tabellskanning(OBJEKT:([t]))
Et særtrekk ved denne planen for utførelse av spørringer er utseendet til Compute Scalar-operatøren.
Tilleggsinformasjon
Under moderne forhold er det noen ganger veldig rart å høre "vi trenger å kollapse 1C-databasen - volumet overstiger 50 GB." Hvis administratorer av SAP R3 eller Oracle e Business Suite eller til og med MS Dynamics Axe-systemer skulle gjøre dette, ville de sannsynligvis fått sparken. For 1C er dette imidlertid "standard praksis".
For filversjoner går historien tilbake til versjon 1C 7.7 med en grense på 2 GB på databasestørrelsen. Nå er 2GB-grensen bare på tabellstørrelsen; filstørrelsen kan allerede vise seg å være veldig, veldig liten. Riktignok, hvis databasen din har vokst til en slik størrelse, ble data sannsynligvis lagt inn aktivt der - kanskje du trenger å tenke på en klient-server?
Egentlig er hensikten med denne artikkelen å "fraråde" brukere av klient-server-versjonen av 1C fra å utføre databasesammendrag, gjennom bruk av noe mer "avanserte" teknologier.
Det endelige tallet er 30-40 tonn, minimum mot 20-25 hvis du kjøper en harddisk og får 500 GB ekstra plass
Det er derfor produkter som
De er nok gode produkter, og de oppfyller målene sine. Det er bare at strukturen til tabellene endres fra versjon til versjon av plattformen. 1C fortalte oss om dette mer enn én gang. En dataseparator dukket opp i den 14. utgivelsen, og det er det ... mest sannsynlig vil denne behandlingen ikke lenger være egnet for den 14. utgivelsen. Og det er på en eller annen måte skummelt, for ikke å nevne et brudd på lisensavtalen.
Og selv etter dette vil det være brukere som "plutselig trengte" slettede data, som "bare ville korrigere" et tall som "ikke påvirker sekvensene" i et dokument for en lukket periode. Og det er verre hvis det viser seg at noen hele tiden så på disse dokumentene for et eller annet formål bare kjent for ham. Alt dette er selvfølgelig bare feil i driftsmetodikken, men likevel vil det være misnøye hos brukerne.
-
Åpne Management Studio i listen over databaser, velg den du trenger, åpne egenskapene.
- Gå til fanen "Filgrupper" som vist i figuren, og legg til en annen filgruppe (i eksemplet kalles den SEKUNDÆR)
- Gå til "Filer"-fanen og legg til en ny fil, som vi velger den opprettede filgruppen for. Denne filen KAN PLASSERES PÅ EN ANNEN DISK
-
Nå bruker vi prosessering for eksempel: vi bestemmer hvilke tabeller vi trygt kan "donere" til et langsommere (eller omvendt, alt til et langsommere, resten til et raskere) medium. 80/20-regelen gjelder her. 80 % av operasjonene utføres med 20 % av dataene, så tenk på hvilke plater du trenger raskt og hvilke som ikke er så mye. "Lagring av tilleggsinformasjon", dokumenter for å legge inn startsaldo, dokumenter som du ikke lenger bruker, identifiserer dem umiddelbart som de som kan overføres til en "langsom" filgruppe.
Velg tabellen som skal flyttes til en annen filgruppe - velg menyen for å endre tabellen (prosjektet) og endre filgruppen i egenskapene:
tabellindeksene overføres også til denne filgruppen.
En ganske praktisk mekanisme for å distribuere tabeller på tvers av diskarrayer med forskjellige hastigheter. Dette strider ikke mot lisensavtalen, fordi I løsningen bruker vi ikke tilgang til data og informasjonsbase på andre måter enn 1C-plattformen. Vi organiserer kun lagringen av disse dataene på en praktisk måte.
DBCC TRACEON (1807)
Vi skriver denne kommandoen i Management Studio, utfører den og kan opprette databaser over nettverket. Selvfølgelig må SQL Server-instansen startes under en domenekonto, og denne kontoen må ha rettigheter til ønsket nettverksmappe.
Men vær veldig forsiktig når du bruker denne kommandoen hvis nettverket ditt går ned mens du arbeider med databasen, vil ikke hele databasen være tilgjengelig under fraværet. Det er ikke for ingenting at Microsoft stengte denne muligheten for massebruk. Generelt er denne funksjonen ment for å lage databaser på NAS-lagringer, noe jeg anbefaler på det sterkeste. En stabil og pålitelig filserver som har en direkte tilkobling til serveren som kjører MS SQL DBMS er også egnet.
Du kan lese mer om andre sporingsflagg i artikkelen http://msdn.microsoft.com/ru-ru/library/ms188396.aspx
De. En del av filgruppen kan til og med lagres på nettverket, og der kan diskplassen utvides uten problemer.
Selvfølgelig er det greit å dele opp tabellene i forskjellige filgrupper... men du vil si at det er et par tabeller her... som har pågått siden 2005... og allerede opptar et titalls gigabyte... Jeg skulle ønske de hadde alle dataene i seg og satt en egen disk, men la de nåværende.
Du vil ikke tro det, men dette er også mulig, selv om det ikke er veldig enkelt:
Opprett en funksjon for partisjonering etter dato:
opprette partisjonsfunksjon YearSection(datetime)
som områderett for verdier ("20110101");
Alt før 2011 vil falle inn i en seksjon, alt etter vil gå inn i en annen.
Opprette et partisjoneringsskjema
opprette partisjonsskjema YearScheme
som partisjon YearSection til (SEKUNDÆR, PRIMÆR);
Med dette sier vi at alle data frem til år 11 vil ende opp i "Sekundær" filgruppen og etter det - i "Primær"
Nå gjenstår det bare å bygge opp bordet på nytt ved å dele det inn i seksjoner. Den enkleste måten å gjøre dette på er å bruke management studio, fordi prosessen ikke er enkel. Du må gjenoppbygge den klyngede indeksen på bordet (som i hovedsak er selve tabellen), ved å velge det opprettede partisjonsskjemaet for indeksen:
På bildet ser du at valget ikke er tilgjengelig - alt er riktig, Tabellpartisjonering er bare mulig i Enterprise-versjonen av MS SQL Server. Klyngeindeksen er lett å skille - et bilde med parenteser. Den er laget for RN og alle 1C-objekter. For RN er det alltid en klyngeindeks etter periode. For dokumenter og kataloger ville det selvfølgelig være bra å lage en annen som inkluderer detaljene som seksjoneringen vil finne sted... men dette ville allerede være et brudd på lisensavtalen.
Men for å gjøre dette trenger du ikke å skjule databasen, men gjør følgende:
a) Forklar for alle hvordan du bruker utvalg, hvordan de lagres, hvordan du bruker loggintervaller, hvordan de lagres
b) Merk unødvendige data for sletting hvis de ikke har noen betydning (motparter og gjenstander du ikke lenger jobber med) - dette vil gi brukerne mer nytte enn reduksjon. Hvis ressurser er tilgjengelige, sett opp automatisk merking for sletting av ubrukte objekter og foreta et standardvalg i programkoden slik at objekter som ikke trengs av brukere - de som er merket for sletting - ikke vises som standard
c) Sett opp andre nyttige "standardvalg" - for eksempel slik at hver leder kun ser sine egne dokumenter som standard. Og hvis han vil se på dokumentene til en "kamerat", må han slå av valget.
For alle detaljer som deltar i utvalget, ikke glem å merke av i boksen "Indeks med tilleggsbestilling" - da vil ikke slike "bekvemmeligheter" påvirke ytelsen til systemet.
Mens vi jobber på store tabeller, møter vi konstant ytelsesproblemer med vedlikehold og oppdatering av data. En av de mest produktive og praktiske løsningene på nye problemer er partisjonering.
Generelt sett er partisjonering delingen av en tabell eller indeks i blokker. Avhengig av partisjoneringsinnstillingen kan blokker være av forskjellige størrelser og kan lagres i forskjellige filgrupper og filer.
Partisjonering har både fordeler og ulemper.
Fordelene er godt beskrevet på Microsofts nettsted, her er et utdrag:
« Partisjonering av store tabeller eller indekser kan gi følgende administrasjons- og ytelsesfordeler:
- Dette gjør at undersett av data kan flyttes og få tilgang til raskt og effektivt samtidig som integriteten til datasettet opprettholdes. For eksempel fullføres en operasjon som å laste data fra OLTP til et OLAP-system på sekunder, i stedet for i minutter eller timer som med ikke-partisjonerte data.
- Vedlikeholdsoperasjoner kan fullføres raskere med en eller flere seksjoner. Operasjonene er mer effektive fordi de bare utføres på delsett av data i stedet for på hele tabellen. Du kan for eksempel komprimere data til én eller flere partisjoner, eller gjenoppbygge én eller flere indekspartisjoner.
- Du kan forbedre spørringsytelsen basert på spørringer som kjøres ofte i maskinvarekonfigurasjonen. For eksempel kan spørringsoptimereren kjøre equijoin-spørringer raskere på to eller flere partisjonerte tabeller hvis tabellene har de samme partisjonskolonnene fordi partisjonene i seg selv kan slås sammen.
Når du sorterer data for I/O-operasjoner i SQL Server, sorterer den først dataene i partisjoner. SQL Server kan bare få tilgang til én disk om gangen, noe som kan redusere ytelsen. For å øke hastigheten på datasortering, anbefales det å distribuere datafiler i seksjoner på tvers av flere harddisker, og lage en RAID. På denne måten, selv om dataene er sortert etter partisjon, vil SQL Server kunne få tilgang til alle harddiskene i hver partisjon samtidig.
Du kan også forbedre ytelsen ved å bruke låser på partisjonsnivå i stedet for hele tabellen. Dette kan redusere antall låsekonflikter for et bord».
Ulempene inkluderer vanskeligheten med å administrere og støtte driften av partisjonerte tabeller.
Vi vil ikke dvele ved implementeringen av partisjonering, siden dette problemet er veldig godt beskrevet på Microsofts nettsted.
I stedet vil vi prøve å vise en måte å optimalisere driften av partisjonerte tabeller på, eller rettere sagt, vi vil vise den optimale (etter vår mening) måten å oppdatere data for en hvilken som helst tidsperiode.
Den store fordelen med et partisjonert bord er den fysiske separasjonen av disse seksjonene. Denne egenskapen lar oss bytte seksjoner med hverandre eller med et hvilket som helst annet bord.
For en typisk dataoppdatering ved hjelp av et skyvevindu (for eksempel månedlig), må vi gå gjennom følgende trinn:
1. Finn de nødvendige radene i en stor tabell;
2. Slett de funnet radene fra tabellen og indeksen;
3. Sett inn nye rader i tabellen, oppdater indeksen.
Når du lagrer milliarder av rader i en tabell, vil disse operasjonene ta ganske lang tid, men vi kan begrense oss til praktisk talt én handling: bare bytt ut den nødvendige delen med en tabell (eller seksjon) forberedt på forhånd. I dette tilfellet trenger vi ikke å slette eller sette inn rader, og vi må også oppdatere indeksen på hele den store tabellen.
La oss gå fra ord til handling og vise hvordan vi implementerer dette.
1. Sett først opp en partisjonert tabell som beskrevet i artikkelen nevnt ovenfor.
2. Lag de nødvendige tabellene for utvekslingen.
For å oppdatere dataene trenger vi en minikopi av måltabellen. Det er en minikopi fordi den vil lagre data som skal legges til måltabellen, dvs. data for kun 1 måned. Du trenger også en tredje tom tabell for å implementere datautveksling. Hvorfor det er nødvendig - jeg vil forklare senere.
Det stilles strenge betingelser for minikopi og tabell for bytte:
- Begge tabellene må eksistere før SWITCH-setningen kan brukes. Før en bytteoperasjon kan utføres, må både tabellen som partisjonen flyttes fra (kildetabellen) og tabellen som mottar partisjonen (måltabellen) eksistere i databasen.
- Destinasjonsdelen må eksistere og må være tom. Hvis en tabell legges til som en partisjon til en eksisterende partisjonert tabell, eller en partisjon flyttes fra en partisjonert tabell til en annen, må målpartisjonen eksistere og være tom.
- Den ikke-partisjonerte sekundære tabellen må eksistere og må være tom. Hvis partisjonen er ment å danne en enkelt ikke-partisjonert tabell, må tabellen som mottar den nye partisjonen eksistere og være en tom ikke-partisjonert tabell.
- Seksjonene må være fra samme kolonne. Hvis en partisjon byttes fra en partisjonert tabell til en annen, må begge tabellene være partisjonert i samme kolonne.
- Kilde- og måltabellene må være i samme filgruppe. Kilde- og måltabellene i en ALTER TABLE...SWITCH-setning må lagres i samme filgruppe, så vel som kolonnene med stor verdi. Eventuelle tilsvarende indekser, indekspartisjoner eller indekserte partisjonsvisninger må også lagres i samme filgruppe. Den kan imidlertid være forskjellig fra filgruppen for de tilsvarende tabellene eller andre relevante indekser.
La meg forklare begrensningene ved å bruke vårt eksempel:
1. Mini-kopien av tabellen må være partisjonert i samme kolonne som målet. Hvis minikopien er en ikke-partisjonert tabell, må den lagres i samme filgruppe som partisjonen som erstattes.
2. Tabellen for utveksling må være tom og må også være partisjonert av samme kolonne eller må være lagret i samme filgruppe.
3. Vi gjennomfører utvekslingen.
Nå har vi følgende:
Tabell med data for alle tider (heretter tabell_A)
Tabell med data for 1 måned (heretter tabell_B)
Tom tabell (heretter tabell_C)
Først av alt må vi finne ut i hvilken seksjon vi lagrer dataene.
Du kan finne ut av det ved å spørre:
PLUKKE UT
tell(*)som
, $PARTITION.(dt) som
, rank() over (rekkefølge etter $PARTITION.(dt))
FRA dbo. (nolock)
grupper etter $PARTITION.(dt)
I denne forespørselen får vi seksjoner som inneholder rader med informasjon. Det er ikke nødvendig å telle mengden – vi gjorde dette for å sjekke behovet for datautveksling. Vi bruker Rank slik at vi kan gå i loop og oppdatere flere seksjoner i en prosedyre.
Så snart vi finner ut i hvilke seksjoner dataene våre er lagret, kan de byttes. La oss anta at dataene er lagret i seksjon 1.
Deretter må du utføre følgende operasjoner:
Bytt partisjoner fra måltabellen med tabellen som skal byttes.
ENDRE TABELL. BYTT PARTISJON 1 TIL . DELING 1
Nå har vi følgende:
Det er ingen data igjen i måltabellen i den delen vi trenger, dvs. delen er tom
Bytt partisjoner fra måltabell og minikopi
ENDRE TABELL. BYTT PARTISJON 1 TIL . DELING 1
Nå har vi følgende:
Månedsdata dukket opp i måltabellen, men mini-kopien er nå tom
Slett eller slett byttetabellen.
Hvis du har en klynget indeks på bordet ditt, er dette heller ikke et problem. Det må opprettes på alle 3 tabellene som er partisjonert i samme kolonne. Ved endring av seksjoner vil indeksen automatisk oppdateres uten ombygging.
Du kan opprette en partisjonert tabell eller indeks i SQL Server 2016 ved å bruke SQL Server Management Studio eller Transact-SQL. Dataene i en partisjonert tabell og indekser er horisontalt delt inn i blokker, som kan distribueres på tvers av flere filgrupper i databasen. Partisjonering kan forbedre håndterbarheten og skalerbarheten til store tabeller og indekser.
Eller en indeks involverer vanligvis fire trinn:
Opprett en filgruppe eller filgrupper og tilsvarende filer som vil inneholde partisjoner i henhold til partisjoneringsskjemaet.
Lag en partisjoneringsfunksjon som tilordner tabell- eller indeksrader til partisjoner basert på verdiene til elementene i en spesifisert kolonne.
Lag et partisjonsskjema som tilordner partisjoner fra en partisjonert tabell eller indeks til nye filgrupper.
Opprett eller modifiser en tabell eller indeks og spesifiser et partisjonsskjema som lagringssted.
I denne seksjonen
Før du begynner, fullfør følgende trinn.
Begrensninger
Sikkerhet
Lag en partisjonert tabell eller indeks ved å bruke følgende verktøy:
SQL Server Management Studio
Begrensninger
Omfanget av en funksjon og et partisjonsskjema er begrenset til databasen den ble opprettet i. Partisjoneringsfunksjoner er plassert i et eget navneområde fra andre funksjoner i databasen.
Hvis noen rader i partisjoneringsfunksjonen har partisjoneringskolonner med NULL-verdier, plasseres disse radene i partisjonen lengst til venstre. Imidlertid, hvis NULL er spesifisert som grenseverdi og RIGHT er spesifisert, blir delen lengst til venstre tom og NULL-verdier plasseres i den andre delen.
Sikkerhet
Tillatelser
Oppretting av en partisjonert tabell krever CREATE TABLE-tillatelse på databasen og ALTER-tillatelse på skjemaet der tabellen er opprettet. Å lage en partisjonert indeks krever ALTER-tillatelse på tabellen eller visningen som indeksen er opprettet på. Oppretting av en partisjonert tabell eller indeks krever en av følgende tilleggstillatelser:
ENDRE EVENTUELLE DATASPACE-tillatelser. Denne tillatelsen tildeles som standard medlemmer av den faste serverrollen sysadmin og faste databaseroller db_eier Og db_ddladmin.
CONTROL eller ALTER tillatelse på databasen der funksjonen og partisjonsskjemaet er opprettet.
KONTROLLER SERVER eller ENDRE ENHVER DATABASE-tillatelse på databaseserveren der funksjonen og partisjoneringsskjemaet er opprettet.
Følg trinnvise instruksjoner i denne prosedyren for å opprette en eller flere filgrupper, tilknyttede filer og en tabell. Følgende eksempel vil referere til disse objektene når du oppretter en partisjonert tabell.
Opprett nye filgrupper for en partisjonert tabell
Opprette en partisjonert tabell
I dialogboksen Lag en jobbplan i felt Navn Skriv inn et navn for jobbplanen.
På listen Tidsplantype velg tidsplantype:
Start automatisk når SQL Server Agent starter
Kjør når prosessorene er inaktive
Repeterende. Velg dette alternativet hvis den nye partisjonerte tabellen oppdateres regelmessig med nye data.
En gang. Dette alternativet er valgt som standard.
I kapittel Frekvens på listen Utført angi frekvensen av utførelse:
Når du velger Daglig i felt Utført hver Spesifiser hvor ofte jobbplanen skal gjentas i dager.
Når du velger Ukentlig i felt Utført hver Spesifiser hvor ofte jobbplanen skal gjentas i uker. Velg dagen eller ukedagene som jobbplanen kjøres på.
Når du velger Månedlig klikk Dag eller Bestemt.
Når du velger Dag Angi datoen i måneden jobbplanen skal kjøres på, og spesifiser hvor ofte jobbplanen skal kjøres igjen om måneder. Hvis du for eksempel vil at jobben skal kjøre den 15. i annenhver måned, velg Dag og skriv inn "15" i det første feltet og "2" i det andre feltet. Vær oppmerksom på at nummeret i det andre feltet ikke må overstige "99".
Når du velger Bestemt Velg en spesifikk ukedag i måneden jobbplanen skal kjøre, og spesifiser hvor ofte jobbplanen skal kjøres igjen om måneder. Hvis du for eksempel vil at jobben skal kjøre den siste uken i annenhver måned, velger du Dag, plukke ut siste i den første listen og arbeidsdag i den andre listen, og skriv deretter inn "2" i det andre feltet. Du kan også velge først, sekund, tredje eller fjerde, samt bestemte ukedager (for eksempel søndag eller onsdag) i de to første listene. Vær oppmerksom på at nummeret i det siste feltet ikke må overstige "99".
Når du velger Utfør en gang hver angi et bestemt tidspunkt på dagen for å kjøre jobbplanen i feltet Utfør en gang hver. Angi klokkeslett: time, minutt og sekund.
Når du velger Utført hver angi frekvensen av oppgaven på den valgte dagen i feltet Frekvens. Hvis du for eksempel vil at jobbplanen skal kjøre annenhver time den dagen jobbplanen kjøres, velger du Utført hver, skriv inn "2" i det første feltet, og velg deretter fra listen se. Fra denne listen kan du også velge minutter Og sekunder. Vær oppmerksom på at tallet i det første feltet ikke må overstige "100".
I felt Start ved Angi tidspunktet for at jobbplanen skal begynne å kjøre. I felt Slutt kl Angi tidspunktet for å fullføre jobbplanen på nytt. Angi klokkeslett: time, minutt og sekund.
Høyreklikk tabellen for å partisjonere, velg Oppbevaring og klikk Opprett en seksjon...
I Veiviser for oppretting av seksjoner På siden Hilsen fra seksjonsskaperen klikk Lengre.
På siden Velge en partisjonskolonne i rutenettet, velg kolonnen som du vil partisjonere tabellen etter. På rutenettet Tilgjengelige partisjoneringskolonner Bare kolonner med datatyper som du kan partisjonere etter, vises. Hvis du velger en beregnet kolonne som partisjoneringskolonne, må du gjøre den vedvarende.
Valget av partisjoneringskolonne og verdiområde bestemmes først og fremst av i hvilken grad dataene skal grupperes logisk. For eksempel kan du dele data inn i logiske grupper etter måned eller kvartal i året. Planlagte dataspørringer avgjør om en slik logisk gruppering er tilstrekkelig for å administrere tabellpartisjoner. Enhver datatype kan brukes som partisjoneringskolonner unntatt tekst, ntekst, bilde, xml, tidsstempel, varchar(maks), nvarchar(maks), varbinær(maks), datatypealiaser og CLR brukerdefinerte datatyper.
Samlokalisering av denne tabellen med den valgte partisjonerte tabellen
Lar deg velge en partisjonert tabell som inneholder relaterte data for å koble til denne tabellen ved hjelp av partisjonskolonnen. Partisjonerte tabeller sammen med partisjoneringskolonner er generelt mer effektive i spørringer.
Juster lagring av ikke-unike og unike indekser med en indeksert partisjoneringskolonne
Justerer alle indekser på en tabell som er partisjonert med samme skjema. Ved å justere en tabell og dens indekser, kan partisjoner flyttes til og fra partisjonerte tabeller mer effektivt fordi dataene er partisjonert ved hjelp av samme algoritme.
Etter å ha valgt partisjoneringskolonnen og andre kolonner, klikk Lengre.
På siden Velge en partisjoneringsfunksjon I kapittel Velg partisjoneringsfunksjon klikk eller . Når du velger Lag en partisjoneringsfunksjon skriv inn funksjonsnavnet. Hvis alternativet er valgt Eksisterende partisjoneringsfunksjon, og velg deretter navnet på funksjonen som skal brukes til partisjonering fra listen. Merk at hvis det ikke er andre partisjoneringsfunksjoner i databasen, vil parameteren Eksisterende partisjoneringsfunksjon vil være utilgjengelig.
Lengre.
På siden Velge et partisjonsskjema I kapittel Velg partisjonsskjema klikk eller . Når du velger Lag et partisjonsskjema angi et skjemanavn. Hvis alternativet er valgt Eksisterende partisjoneringsskjema, og velg deretter navnet på ordningen som skal brukes fra listen. Hvis det ikke er andre partisjoneringsskjemaer i databasen, er alternativet Eksisterende partisjoneringsskjema vil være utilgjengelig.
Når du er ferdig med denne siden, klikker du på knappen Lengre.
På siden Seksjonskartlegging I kapittel Område plukke ut Venstre kantlinje eller Høyre kant for å velge den høyeste eller laveste cutoff-verdien som skal inkluderes i alle filgrupper som opprettes. I tillegg til antallet filgrupper som er spesifisert som grenser når du oppretter partisjoner, må du alltid angi én ekstra filgruppe.
På rutenettet Velge filgrupper og spesifisere grenseverdier i felt Filgruppe velg filgruppen som dataene skal partisjoneres i. I kapittel Grense angi en grenseverdi for hver filgruppe. Hvis en grenseverdi ikke er spesifisert, tilordner partisjoneringsfunksjonen hele tabellen eller indeksen til en enkelt partisjon ved å bruke navnet på partisjoneringsfunksjonen.
Følgende tilleggsalternativer er tilgjengelige på denne siden:
Sett grenser...
Åpne en dialogboks Sette grenseverdier, hvor du kan velge grenseverdier og datoperioder for seksjoner. Dette alternativet er bare tilgjengelig hvis du har valgt en partisjoneringskolonne som inneholder en av følgende datatyper: Dato, dato tid, smalldatetime, dato klokkeslett 2 eller datotidsforskyvning.
Lagringsvurdering
Anslå antall rader, nødvendig og tilgjengelig lagringsplass for hver filgruppe spesifisert for partisjonene. Disse verdiene er skrivebeskyttet i rutenettet.
I dialogboksen Sette grenseverdier Du kan angi følgende tilleggsparametere:
startdato
Velg en startdato for verdier for partisjonsområde.
utløpsdato
Velg en sluttdato for verdier for partisjonsområde. Når du velger Venstre kantlinje På siden Seksjonskartlegging denne datoen vil være den siste verdien for hver av filgruppene og partisjonene. Når du velger Høyre kant På siden Seksjonskartlegging denne datoen vil være den første verdien i den nest siste filgruppen.
Datointervall
Velg datogranularitet eller områdeverditrinn for hver seksjon.
Når du er ferdig med denne siden, klikker du på knappen Lengre.
På siden Valg av utgangsparameter spesifiser hvordan den partisjonerte tabellen skal fylles ut. Plukke ut Lag script for å lage et SQL-skript basert på dataene på tidligere sider i veiviseren. Plukke ut Start umiddelbart for å lage en ny partisjonert tabell etter å ha fullført alle gjenværende veivisersider. Plukke ut Rute for å opprette en ny partisjonert tabell på et forhåndsdefinert tidspunkt i fremtiden.
Når du velger Lag script V Skriptparametere Følgende alternativer vil være tilgjengelige:
Skriv ut skript til fil
Opprette et skript som en SQL-fil. Skriv inn filnavn og plassering i feltet Filnavn eller klikk Anmeldelse for å åpne dialogboksen Plassering av skriptfil. I kapittel Lagre som plukke ut Unicode-tekst eller ANSI-tekst.
Skriv ut skript til utklippstavlen
Lagrer skriptet på utklippstavlen.
Send ut skriptet til et nytt forespørselsvindu
Skriptet opprettes i et nytt spørringsredigeringsvindu. Dette alternativet er valgt som standard.
Når du velger Rute klikk Endre tidsplan.
Merk av eller fjern merket Inkludert for å slå timeplanen på eller av.
Når du velger Repeterende:
I felt Hvor mange ganger om dagen Spesifiser hvor ofte jobbplanen skal kjøres på nytt den dagen jobbplanen kjøres:
I kapittel Varighet, i området startdato Angi startdatoen for at jobbplanen skal kjøres. Plukke ut utløpsdato eller Ingen sluttdato for å spesifisere fullføringsdatoen for jobbplanen. Når du velger utløpsdato Angi sluttdatoen for at jobbplanen skal kjøres.
Når du velger en verdi En gang V Engangsutførelse i felt Dato Skriv inn startdatoen for jobbplanen. I felt Tid Angi starttidspunktet for jobbplanen. Angi klokkeslett: time, minutt og sekund.
I kapittel Sammendrag V Beskrivelse Kontroller at alle jobbplanparametere er riktige.
Klikk på knappen OK.
Når du er ferdig med denne siden, klikker du på knappen Lengre.
På siden Se sammendraget I kapittel Se valgene dine Utvid alle tilgjengelige alternativer for å sikre at alle seksjonsinnstillinger er riktige. Hvis alle innstillinger er riktige, klikk på knappen Klar.
Side Opptreden Partisjonsveiviseren brukes til å spore statusinformasjon om partisjonsveiviserens handlinger. Avhengig av handlingene du velger i veiviseren, kan fremdriftssiden inneholde én eller flere handlinger. Det øverste feltet viser den generelle statusen til veiviseren og antall statusmeldinger, advarsler og feilmeldinger den har mottatt.
På siden Opptreden Følgende alternativer er tilgjengelige i veiviseren for oppretting av seksjoner:
Intelligens
Informasjon om hendelsen, status og eventuelle meldinger som returneres som et resultat av veiviserens handlinger.
Handling
Angir typen og navnet på hver handling.
Stat
Angir om veiviserhandlingen som helhet returnerte en verdi Vellykket eller Feil.
Beskjed
Eventuelle feilmeldinger eller advarsler fra prosessen.
Rapportere
Opprett en rapport som inneholder resultatene av veiviseren for opprettelse av seksjon. Tilgjengelige alternativer: Se rapport, Lagre rapporten til fil, Og Send rapport på e-post.
Se rapporten
Åpne en dialogboks Se rapporten, som inneholder en tekstrapport om arbeidet med veiviseren for opprettelse av seksjoner.
Kopier rapporten til utklippstavlen
Kopierer resultatene av veiviserrapporten til utklippstavlen.
Send rapport på e-post
Kopier resultatene av veiviserens statusrapport til en e-postmelding.
Når du er ferdig med å velge alternativene, klikker du Lukk.
Partisjonsveiviseren vil opprette partisjoneringsfunksjonen og -skjemaet, og deretter bruke partisjoneringen til den angitte tabellen. For å sjekke tabellpartisjonering, høyreklikk tabellen i Objektutforsker og velg Egenskaper. Gå til side Oppbevaring. Siden viser informasjon inkludert partisjonsfunksjonens navn, layout og antall partisjoner.
Opprette en partisjonert tabell
I Objektutforsker Koble til en forekomst av databasemotoren.
Velg på standardpanelet Opprett en forespørsel.
Kopier følgende eksempel inn i spørringsvinduet og klikk på knappen Henrette. Følgende eksempel viser opprettelsen av filgrupper, funksjoner og partisjoneringsskjemaer. En ny tabell opprettes når du angir et partisjonsskjema som lagringssted.
BRUK AdventureWorks2012; GÅ -- Legger til fire nye filgrupper til AdventureWorks2012-databasen ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test1fg; GÅ ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test2fg; GÅ ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test3fg; GÅ ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test4fg; -- Legger til én fil for hver filgruppe. ALTER DATABASE AdventureWorks2012 LEGG TIL FIL (NAME=test1dat1, FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf", STØRRELSE = 5 MB, MAKSSTØRRELSE = 100 MB, FILVEKST = 5 MB) TIL FILGRUPPE test1fg; ALTER DATABASE AdventureWorks2012 LEGG TIL FIL (NAME=test2dat2,FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf", STØRRELSE = 5 MB, MAKSSTØRRELSE = 100 MB, FILVEKST = 5 MB) TIL FILGRUPPE test2fg; GÅ ALTER DATABASE AdventureWorks2012 LEGG TIL FIL (NAME=test3dat3,FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf", STØRRELSE = 5 MB, MAKSSTØRRELSE = 100 MB, FILVEKST = 5 MB) TIL FILGRUPPE test3fg; GÅ ALTER DATABASE AdventureWorks2012 LEGG TIL FIL (NAME=test4dat4, FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf", STØRRELSE = 5 MB, MAKSSTØRRELSE = 100 MB, FILVEKST = 5 MB) TIL FILGRUPPE test4fg; GÅ -- Oppretter en partisjonsfunksjon kalt myRangePF1 som vil partisjonere en tabell i fire partisjoner LAG PARTISJONSFUNKSJON myRangePF1 (int) SOM OMRÅDE TILBAKE FOR VERDIER (1, 100, 1000); GÅ -- Oppretter et partisjonsskjema kalt myRangePS1 som bruker myRangePF1 til de fire filgruppene opprettet ovenfor LAG PARTISJONSSCHEMA myRangePS1 SOM PARTISJON myRangePF1 TO (test1fg, test2fg, test3fg, test4fg); GÅ -- Oppretter en partisjonert tabell kalt PartitionTable som bruker myRangePS1 til å partisjonere col1 LAG TABELL Partisjonstabell (col1 int PRIMARY KEY , col2 char ( 10 )) PÅ myRangePS1 (col1) ; GÅ
Definere tabellpartisjonering
Følgende spørring returnerer én eller flere rader hvis partisjonstabellen er partisjonert. Hvis tabellen ikke er partisjonert, returneres ingen rader.
Definere grenseverdier for en oppdelt tabell
Følgende spørring returnerer grenseverdiene for hver partisjon i partisjonstabellen.
VELG t .name AS TableName, i .name AS IndexName, p .partition_number, p .partition_id, i .data_space_id, f .function_id, f .type_desc, r.boundary_id, r.value AS BoundaryValue FRA sys .tables AS t JOIN sys .indexes AS i ON t .object_id = i .object_id JOIN sys .partitions AS p ON i .object_id = p .object_id OG i .index_id = p .index_id JOIN sys .partition_schemes AS s ON i .data_space_space_id = s. .partition_functions AS f ON s.function_id = f .function_id LEFT JOIN sys .partition_range_values AS r ON f .function_id = r.function_id og r.boundary_id = p .partition_number HVOR t .name = "PartitionTable" .type<= 1 ORDER BY p .partition_number;
Definere en partisjonskolonne for en partisjonert tabell
Følgende spørring returnerer navnet på tabellens partisjoneringskolonne. Partisjonstabell.
VELG t. AS ObjectID , t .name AS TableName , ic.column_id AS PartitioningColumnID , c .name AS PartitioningColumnName FRA sys .tables AS t JOIN sys .indexes AS i ON t . = i. Og jeg.<= 1 -- clustered index or a heap JOIN sys .partition_schemes AS ps ON ps.data_space_id = i .data_space_id JOIN sys .index_columns AS ic ON ic. = i . AND ic.index_id = i .index_id AND ic.partition_ordinal >= 1 -- fordi 0 = ikke-partisjonerende kolonne JOIN sys .columns AS c PÅ t . =c. OG ic.column_id = c .column_id WHERE t .name = "Partisjonstabell" ; GÅ
Se avsnittet for mer informasjon.