Tässä artikkelissa käsitellään SQL Serverin Auto Shrink -tietokantaominaisuutta, jonka avulla SQL Server pienentää tietokantatiedostoja automaattisesti, kun tietokanta-asetuksessa on määritetty arvoksi True. Automaattinen kutistamistoiminto tehdään palvelimen automaattisen kutistamisen tietokantaominaisuudella, joka on tämän artikkelin pääkurssi.
Jotta vältettäisiin data/lokitiedostojen kutistamistoiminto aina, kun tiedoston koko ylittää datatiedoston vapaan tilan, DBA:n (tietokannan ylläpitäjän) on varmuuskopioitava lokitiedostot säännöllisin väliajoin. Koko tietokannan varmuuskopiointi ei ole hyvä idea, vaan myös transaktioloki on rakennettava tai asetettava. Transaktiolokit lisääntyvät, kunnes ne vievät kaiken käytettävissä olevan levytilan, jos niistä ei oteta varmuuskopioita. Jos varmuuskopioit tietokannan, se vapauttaa vapaata tilaa uudelleen käytettäväksi. Tietokannan ylläpitäjän on ajoitettava tapahtumalokin varmuuskopiointi, jotta lokitiedostojen kokoa voidaan pienentää kohtuulliseen kokoon.
SQL Serverin automaattinen kutistustoiminto on oletusarvoisesti pois käytöstä SQL Serverin instanssitietokannoissa. Skenaariossa, jossa on useita suhteellisen pieniä tietokantoja, jotka kasvavat suuremmiksi joko uusien tuplien lisäämisen tai useiden tuplien poistamisen aiheuttamien tyhjien tilojen suuren määrän vuoksi, SQL Server Auto Shrink -ominaisuus on tässä tilanteessa varsin kätevä. Lisäksi sinun ei tarvitse huolehtia tietokantatiedostojen pirstaloitumisesta.
Tietokannan kutistamisen yhteydessä on otettava huomioon sekä kriittisten että suhteellisen suurten tietokantojen kutistamisoperaatiot osana ylläpitoa. Tämän lisäksi pitäisi välttää manuaalisen kutistustoiminnon käyttämistä; sen seuraukset ovat, ettei koskaan tutustu uuteen tai olemassa olevaan pyyntöön liittyviin ongelmiin. Tapahtumatiedostojen kutistaminen on kuitenkin parempi kuin datatiedostojen kutistaminen.
Way to activate and deactivate the SQL Server auto shrink for the database?
Käyttäjät voivat ottaa käyttöön ja poistaa käytöstä tietokannan automaattisen kutistusvaihtoehdon käyttämällä SSMS- ja T-SQL-ohjelmia molemmilla tavoilla.
Tietokannan automaattisen kutistusvaihtoehdon ottaminen käyttöön SSMS-ohjelmalla:
Käyttäjät voivat ottaa tämän vaihtoehdon käyttöön tai poistaa sen käytöstä tietokannan ominaisuuksista tunnisteella Auto Shrink. Tässä pudotusvalikossa True ottaa tämän vaihtoehdon käyttöön tietokannassasi.
Tietokannan automaattisen kutistumisen ottaminen käyttöön T-SQL:n avulla:
Käyttäjät voivat suorittaa alla olevat T-SQL-lausekkeet ottaakseen tietokannan kutistumisen käyttöön tai pois käytöstä. Olemme käyttäneet AdventureWorks-tietokantaa edellä mainittua T-SQL-lauseketta varten.
|
1
2
3
4
5
6
|
–Enable Auto Shrink for the database AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
—Poista tietokannan automaattinen kutistaminen käytöstä AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Tietokannan kutistamisen vaikutukset kyselyn suorituskykyyn
Haitat voivat nousta esiin kyselyn suorituskykypuolella, jos tietokannan automaattinen kutistaminen -vaihtoehto ja tietokannan automaattisen kasvun asetukset otetaan käyttöön tietokantojen automaattinen kutistaminen-asetusten yhteydessä. Voimme säilyttää jonkin verran tilaa datatiedostojen kasvulle ja myös välttää joitakin usein esiintyviä automaattisia kasvutapahtumia asettamalla tietokannan koon optimaaliseen arvoon tai useimmiten jokaisessa tietokannassa on joitakin parametreja, joissa automaattiset kasvutoiminnot on otettu käyttöön.
Tällaisissa tietokannoissa automaattiset kutistustoiminnot on aktivoitava meidän, kun tietokanta on pienempi, yksi ja ei enää CRUD-operaatioita, joten se voi tehdä datatiedostojen kutistamisen mahdolliseksi ja palauttaa vapaana olevan tilan, jonka varasimme tarkoituksenmukaisesti pitääkseen tietokantojamme loitolla automaattisia kasvutapahtumia vastaan. Vapaata tilaa vapautuu automaattisesti datatiedostoissa ja lokitiedostoissa ajoittain round-robin-sekvenssiprioriteetilla Auto shrink -ominaisuuden avulla, jos useat tietokannat ovat ottaneet tämän ominaisuuden käyttöön.
Suurikokoisille tietokannoille suoritetaan automaattinen kasvu ja sen jälkeen automaattinen kutistuminen, joka johtaa suorituskykyongelmiin, jotka johtuvat järjestelmätason pirstoutumisesta. Yhteenvetona kaikista näistä voidaan todeta, että minkään tietokannan osalta automaattista kutistamista ei pitäisi aktivoida seuraavista syistä:
- SQL Serverin automaattisen kutistumisen algoritmien suorittaminen ilman mitään tarkoitusta varmasti tuhlaa resursseja valtavassa määrin
- Suoritatpa sitten SQL Serverin automaattisen kutistumisen tai manuaalisen kutistumisen, se ilmeisesti aiheuttaa indeksin pirstaloitumista, ja tämä lopulta suorittaa myös datatiedostojen kutistumisen
- Jos palvelin on myös pakottamassa IO-alijärjestelmän rajoja, kutistumisen suorittaminen voi työntää sen yli, mikä johtaa pitkiin levyjonojen pituuksiin ja mahdollisesti IO-aikakatkaisuihin, tämä syö palvelimen IO- ja CPU-resursseja valtavasti
- Järjestelmän suorituskykyä hidastaa tiedostojärjestelmän levytason fragmentoituminen, joka on taas suora vaikutus siitä, että datatiedostojen kutistamista ja kasvattamista tehdään usein
Jos haluat ymmärtää lisää SQL Serverin kasvu- ja kutistamistapahtumista, lue tämä artikkeli, Hanki SQL Serverin tietokantojen kasvu- ja kutistamistapahtumiin liittyviä tietoja.
Kriittisessä tietokannassa asiantuntija voisi suorittaa manuaalisen kutistustoiminnon tietokantatiedostotasolla. Manuaalinen kutistustiedostotoiminto voidaan tehdä, kun poisto-operaatio on suoritettu ja sen jälkeen tilaa on saatu takaisin. Pirstaloituneet indeksit on rakennettava uudelleen kutistustoiminnon suorittamisen jälkeen, koska kutistustoiminto voi johtaa indeksin pirstaloitumiseen. Käyttäjä voi tarkistaa indeksin pirstoutumisprosentin DMV:n T-SQL-lausekkeiden avulla. Lokitiedoston kutistaminen olisi kuitenkin tehtävä manuaalisesti tarpeen mukaan, eikä sen pitäisi olla osa säännöllistä ylläpitotoimintaa.
Levynkäytön seuraamiseksi säännöllisesti käyttäjä voisi analysoida levynkäyttöraportin ennen manuaalisen kutistustoiminnon suorittamista SSMS:n avulla, joka antaa kuvan tietokannan data- ja lokitilatiedoista näytössä. Jos käyttäjä haluaa saada kojelaudan tietokantatiedostojen varatun tilan ja vapaan tilan laskemista varten, levynkäyttöraportista on paljon apua. Levyraportti sisältää kuitenkin tiedot SQL Serverin DMV:n avulla. Levyraportti on saatavilla alla olevaan hakemistoon SSMS:n avulla.
Tietokanta >> Raportit >> Vakioraportit >> Levynkäyttö
Tässä meillä on tietokannan levyraportti. Käyttäjä voi nähdä datatiedostojen ja lokitiedostojen reaaliaikaiset tilastot. Tämä raportti sisältää ensisijaiset tiedot varatusta kokonaistilasta, datatiedostojen varatusta tilasta, tapahtumalokin varatusta tilasta ja muistissa olevasta OLTP-tilasta.
Tietokannan kutistaminen automaattisesti SQL-palvelimen työtehtävillä
Tällaiset skriptit voitaisiin suorittaa ajoittamalla käyttäjän tekemä työ tietokannan kutistamistoiminnon suorittamiseksi ajoitustoiminnolla. Skriptin ensimmäisenä vaiheena etsitään tietokantatiedoston vapaa tila ja kutistetaan tiedosto, jos vapaata tilaa koskevat määritellyt kriteerit täyttyvät. Vapaa tila on laskettava SQL Serverin DMV:n avulla.
Periaatteessa tietokantatiedoston seurannan suorittaa tietokannan ylläpitäjä, joka puolestaan valvoo tietokannassa tietokantatiedoston kokoa. Jos jokin tiedosto ylittää ennalta määritellyn rajan, heidän on suoritettava tällaiset toimenpiteet. Tämä toiminta voidaan siis automatisoida SQL Serverin tehtävällä, joka suoritetaan päivittäin, viikoittain tai kuukausittain ennalta määritettynä ajankohtana.
Paras käytäntö on ajoittaa tämä tehtävä vain tietokannan lokitiedostoon ja valvoa datatiedoston vapaata tilaa manuaalisesti. Koska se voi vaikuttaa myös kyselyiden suorituskykyyn. SQL Server DMV palauttaa datatiedoston ja lokitiedoston samassa tulosjoukossa, joten tiedostotyyppi on erotettava SQL Server DMV:n T-SQL-lauseessa. Ennen tiedoston kutistamista käyttäjä voi soveltaa kyselylogiikkaa lokitiedoston kokonaistilaan ja vapaaseen tilaan. Jos laskentakriteerit vastaavat tiedoston ominaisuutta, tiedosto kutistuu tavoitetiedoston koolla.
Tietokantatiedostojen vapaan tilan tarkistaminen:
Jos tällaiset kriteerit vastaavat tietokantatietokantatiedostoja, esimerkiksi jos vapaa tila on suurempi kuin (n) MB/GB, jos n(%) vapaata tilaa verrataan kokonaistilaan, ja monia muita. Kun lokitiedostolle ei ole käytettävissä vapaata tilaa ja tiedoston koko on lähellä max file size -parametrin arvoa, tietokannan ylläpitäjän on etsittävä tapahtumaloki.
Tietokantatiedoston kutistaminen:
|
1
|
DBCC SHRINKFILE(tiedoston_nimi, 5120);
|
Tässä 5120 on tavoitetiedoston koko MB:nä. Tiedostosta tulee siis 5120 MB:n kokoinen. Määritä T-SQL-kysely tarvittavilla kriteereillä SQL Serverin työvaiheessa ja ajoita se on-off-tunteina.
SQL Serverin automaattinen kutistaminen -vaihtoehtoa ei voi ottaa käyttöön kaikissa tietokannoissa. Erityisesti se auttaa pienempiä tietokantoja, jotka suorittavat verrattain vähemmän CRUD-operaatioita.
Johtopäätös
Tässä artikkelissa käsiteltiin SQL Serverin Auto Shrink -tietokantaominaisuutta, jolla voidaan kutistaa data- ja lokitiedostoja ja poistaa käyttämätöntä tilaa. Tietokannan kutistaminen on kallis operaatio, ja sitä tulisi käyttää harkiten.
- Author
- Recent Posts
Katso kaikki käyttäjän Jignesh Raiyani viestit
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Taulukoiden osioinnin automatisointi SQL Serverissä – 7. heinäkuuta 2020
- SQL Serverin Always On -saatavuusryhmien määrittäminen AWS EC2:ssa – 6. heinäkuuta 2020