Limity SQL Server Express: strop 10 GB a jak ho obejít

SQL Server Express má tři stropy, kolem kterých se dá navrhovat, a jednu chybějící součást, kterou musíš nahradit. Stropy jsou 10 GB dat na databázi, zhruba 1,4 GB buffer poolu na instanci a menší z hodnot jeden socket nebo čtyři jádra. Chybějící součást je SQL Server Agent, plánovač, který většina návodů bere jako samozřejmost. Na všechny čtyři věci existuje řešení: každou databázi drž štíhlou pomocí komprese, archivace a oddělených databází, velikost serveru přizpůsob cache, kterou skutečně dostaneš, a plánované práce přesuň z databázového stroje do cronu, Plánovače úloh, aplikace nebo vlastních plánů hostingu. Tenhle článek je o té praktické půlce. Co přesně ty limity jsou a kdy je čas z Expressu odejít, si nejdřív přečti v článku SQL Server Express hosting: limits and when it is enough; tady se předpokládá, že ses rozhodl zůstat.

Na který limit narazíš jako první#

U většiny skutečných zátěží je pořadí takové: disk, pak paměť, pak chybějící Agent, pak procesor, a strop 10 GB přijde až poslední nebo vůbec. Limit dat budí strach, protože stojí v nadpisech, ale typická firemní databáze po letech provozu zabírá stovky megabajtů až pár gigabajtů. Dřív kousnou jiné věci: transakční log, který nikdo nezálohuje, tabulka auditu bez doby uchování a noční úloha, která nikdy neběžela, protože ji neměl kdo spustit.

Nejdřív měř. Dva dotazy ukážou, kde stojíš: první ukáže datové soubory databáze, ke které jsi připojený, druhý kolik cache zabírá která databáze.

SELECT name, type_desc,       size * 8 / 1024 AS size_mb,       FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mbFROM sys.database_files;SELECT DB_NAME(database_id) AS db, COUNT(*) * 8 / 1024 AS cached_mbFROM sys.dm_os_buffer_descriptorsGROUP BY database_idORDER BY cached_mb DESC;

Limit se vynucuje podle přidělené velikosti datových souborů, takže s číslem 10 240 porovnávej size_mb, ne used_mb. Soubory logu se do limitu nepočítají. Pokud je size_mb pod polovinou stropu a roste pomalu, máš před sebou roky; energii věnuj zálohám a plánování.

Jak se vejít do 10 GB#

Když datový soubor potřebuje vyrůst přes strop, růst se odmítne a vkládání, které potřebuje nové stránky, selže, zatímco čtení dál funguje. Žádné varování mezi tím není, takže všechna řešení jsou o tom, nepřiblížit se k hranici. Od nejlevnějšího:

Zjisti, co je vlastně velké

Než cokoli rozhodneš, spusť dotaz na velikosti po tabulkách. Horní tři tabulky skoro vždy vysvětlí všechno.

SELECT TOP (10)       s.name + '.' + t.name AS table_name,       SUM(p.reserved_page_count) * 8 / 1024 AS reserved_mb,       SUM(CASE WHEN p.index_id IN (0, 1) THEN p.row_count END) AS row_countFROM sys.dm_db_partition_stats AS pJOIN sys.tables AS t ON t.object_id = p.object_idJOIN sys.schemas AS s ON s.schema_id = t.schema_idGROUP BY s.name, t.nameORDER BY reserved_mb DESC;

Porovnej rezervovanou velikost s počtem řádků. Tabulka s deseti miliony úzkých řádků o velikosti 2 GB je normální. Tabulka se 40 000 řádky o velikosti 3 GB ukládá binární data, a ta do databáze s omezenou velikostí nepatří.

Zkomprimuj tabulky a indexy

Komprese řádků a stránek v Expressu funguje (je dostupná ve všech edicích od SQL Server 2016 SP1). Komprese stránek často zmenší velkou opakující se tabulku, třeba auditní log, na polovinu, za cenu trochu vyšší zátěže procesoru při zápisu a čtení. Před přebudováním nech databázový stroj odhadnout úsporu:

EXEC sp_estimate_data_compression_savings     @schema_name = 'dbo', @object_name = 'AuditLog',     @index_id = NULL, @partition_number = NULL,     @data_compression = 'PAGE';ALTER TABLE dbo.AuditLog REBUILD WITH (DATA_COMPRESSION = PAGE);

Přebudování potřebuje pracovní místo zhruba o velikosti tabulky a zvětšuje log, takže ho dělej, když je rezerva v obou, a předtím udělej zálohu. U širokých tabulek, které se hlavně čtou, komprimuje clusterovaný columnstore index mnohem víc než komprese stránek, ale Express omezuje cache columnstore na 352 MB a columnstore se hodí na analytické skeny, ne na bodové dotazy, takže to vyzkoušej na vlastních dotazech.

Pro jednotlivé velké textové nebo JSON sloupce aplikují funkce COMPRESS() a DECOMPRESS() GZIP přímo v T-SQL a výsledek uloží do sloupce varbinary(max). U velkých dat to šetří místo; cenou je, že takový sloupec už nemůžeš přímo indexovat ani v něm hledat.

Přesuň binární data pryč

Obrázky, PDF a nahrané dokumenty v varbinary(max) zaplní databázi o 10 GB rychleji než cokoli jiného a každý z nich navíc prochází tvým buffer poolem o velikosti 1,4 GB a vytlačuje stránky, na kterých záleží. Ukládej soubor do objektového úložiště nebo na disk a v tabulce nech jen klíč, velikost a typ obsahu. Pokud chceš úložiště vedle databáze, RE:NODE prodává samostatnou řadu objektového úložiště kompatibilního s S3; funguje jakýkoli S3 endpoint, princip je stejný.

Archivuj po dávkách

Staré řádky, které je nutné uchovat, ale čtou se zřídka, můžeš přesunout do druhé databáze na téže instanci, která má vlastních 10 GB. T-SQL neumí přepínat oddíly mezi databázemi, takže přesouvej po malých dávkách, které mažou ze zdroje a vkládají do archivu jedním příkazem:

WHILE 1 = 1BEGIN    DELETE TOP (5000) FROM app.dbo.AuditLog    OUTPUT deleted.* INTO archive.dbo.AuditLog    WHERE CreatedAt < DATEADD(MONTH, -18, SYSUTCDATETIME());    IF @@ROWCOUNT = 0 BREAK;END

OUTPUT ... INTO vyžaduje, aby cílová tabulka měla stejné sloupce a neměla zapnuté triggery ani cizí klíče. Malé dávky udržují každou transakci krátkou, takže log nenabobtná a zámky se rychle uvolní. Spouštěj to z plánované úlohy (viz níže), ne ručně.

Smaž to, co nemá hodnotu

Prošlé relace, tokeny pro obnovu hesla, soft-delete záznamy starší než tvoje doba uchování a podrobné logy požadavků nemají důvod žít v databázi navěky. Urči dobu uchování pro každou tabulku, napiš pro každou jeden příkaz na čištění a naplánuj je. Je to nejlevnější řešení a většina databází ho nikdy nepoužije.

Odstraň nepoužívané indexy

Indexy se počítají do velikosti dat. Pohled sys.dm_db_index_usage_stats ukazuje čtení a zápisy po jednotlivých indexech od posledního restartu. Index s mnoha zápisy a žádným čtením jsou čisté náklady. Věř mu až poté, co server prošel celým obchodním cyklem (měsíční report může být jediným uživatelem indexu), protože počítadla se při restartu nulují.

Dělej databáze podle účelu

Jedna databáze na aplikaci a zvláštní databáze pro reporty nebo archiv pro data, která rostou bez omezení, je dobrý návrh v každé edici a v Expressu navíc násobí tvou rezervu. Limit platí na databázi, ne na instanci. Dotazy napříč databázemi na jedné instanci fungují s třídílnými názvy (archive.dbo.AuditLog), takže rozdělení málokdy znamená přepisovat dotazy.

Zmenšení souboru, jednou

Po velkém úklidu zůstane soubor velký. DBCC SHRINKFILE místo vrátí, ale silně fragmentuje indexy, takže ho spusť jednou po velkém úklidu, přebuduj důležité indexy a nikdy ho nedávej do plánu. Protože se limit řídí přidělenou velikostí, napůl prázdný soubor o velikosti 9,8 GB je u stropu, dokud ho nezmenšíš.

Disk je také limit a na malém tarifu přijde první#

Strop 10 GB je licenční pravidlo; disk je fyzické omezení a na hostingu bývá menší z nich. Databáze o 10 GB potřebuje místo pro log, pro tempdb, pro zálohy, které leží na stejném svazku, než je odvezeš, a pro vlastní soubory operačního systému. U RE:NODE jde řada SQL Server od Express Starter (2 GB paměti, disk 10 GB, 1 vCPU) po Express Max (6 GB paměti, disk 80 GB, 4 vCPU), od 6 USD měsíčně. Aritmetika je jednoduchá: na disku 10 GB se k databázi o 10 GB nikdy nedostaneš, na 80 GB můžeš provozovat několik databází těsně u stropu.

Tarif proto vybírej nejdřív podle disku. Hrubý rozpočet pro jednu databázi, která bude na maximu velká N GB: N na data, N/4 až N/2 na log, jeden soubor plné zálohy (zhruba N, protože Express zálohy nekomprimuje), dokud leží na serveru, a několik GB na tempdb a rezervu. Druhá kopie leží mimo stroj, právě o to jde; viz část o plánování.

Jak žít s 1,4 GB buffer poolu#

Limit paměti se týká buffer poolu, tedy cache datových stránek. Proces jako celek využije víc, protože plány, spojení a řazení se do limitu nepočítají. V praxi z toho plynou dvě věci.

Zaprvé, server s víc než zhruba 4 GB RAM databázovému stroji nepomůže, protože cache se zastaví na 1410 MB. Paměť kupuj jen do bodu, kdy se buffer pool může naplnit a operační systém i stroj mají rezervu; dál kupuj disk. Tarify RE:NODE to odrážejí: přechod z Express Pro na Express Max (ze 4 na 6 GB paměti) je hlavně o disku a všech čtyřech jádrech.

Zadruhé, pracovní sada větší než 1,4 GB se čte z disku častěji. NVMe, které mají všechny tarify RE:NODE, to dělá mnohem méně bolestivé než na rotačních discích, ale čtení můžeš i omezit:

• Užší řádky a indexy. Krycí index jen se sloupci, které horký dotaz potřebuje, vejde do cachované stránky víc užitečných řádků. Komprese nabalí do stránky víc řádků a protáhne tak cache i disk.
• Méně indexů, ale lepších. Každý udržovaný index soupeří o stejných 1,4 GB. Indexes and execution plans vysvětluje, jak číst plán a zahodit, co nepomáhá.
• **Žádné SELECT *.** Načítání širokých řádků, které nepotřebuješ, táhne cache binární data a zřídka potřebné sloupce.
• Najdi drahé dotazy. Většinu čtení stránek obvykle způsobí hrstka dotazů. Query Store and slow queries ukazuje, jak je najít v Expressu, kde je Query Store dostupný.
• Cachuj v aplikaci. Referenční data, která se nemění, patří do paměti aplikace nebo do Valkey, ne do dotazu na SQL Server při každém požadavku.

Omez instanci, ať zbyde paměť pro ostatní

SQL Server se v základní instalaci snaží zabrat veškerou přidělenou paměť až do limitu edice a v Expressu se buffer pool zastaví na 1410 MB. Pokud má kontejner nebo stroj nad to paměti málo, nastav výslovný strop, aby si operační systém nechal svůj díl:

EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'max server memory (MB)', 1536;RECONFIGURE;

Jestli to můžeš změnit, závisí na tom, jak hosting instanci spouští a jaký máš login, takže si to ověř, než na tom něco postavíš. U RE:NODE limit paměti tarifu vynucuje kontejner: na hranici paměti jádro kontejner zastaví a ten se čistě restartuje, místo aby swapoval. Takové chování je přínosem pro stabilitu a důvodem nechávat si rezervu. Vyber tarif, jehož paměť je pohodlně nad tím, co proces SQL Serveru potřebuje, a sleduj graf paměti na záložce Console, místo abys hádal.

Limit čtyř jader#

Express používá menší z hodnot jeden socket nebo čtyři jádra, takže hostitel s osmi jádry ho nezrychlí. Řešením je jádra dobře využít, ne chtít jich víc:

• Nejdřív oprav nejhorší dotazy. Chybějící index, který z seek udělá scan při každém požadavku, stojí víc procesoru než jakékoli ladění jinde.
• Drž paralelismus rozumný. Paralelní plány na čtyřech jádrech mohou vytlačovat ostatní práci. Serverové nastavení max degree of parallelism (EXEC sp_configure 'max degree of parallelism', 2; po zapnutí pokročilých voleb) je na malých serverech častá volba, ale otestuj ji na své zátěži a nekopíruj číslo.
• Přesuň reporty mimo špičku. Těžké agregace patří do klidné části dne, což je další důvod mít plánovač.
• Odlehči čtení, která nejsou dotazy. Fulltextové hledání, cachování a fronty je lepší svěřit jinému nástroji, než nutit SQL Server dělat všechno.

Na RE:NODE je procesor tarifu pevné omezení na zakoupený podíl, takže na Express Starter máš celkem jeden vCPU, ne čtyři. Server na 100 % procesoru je pomalý, ne rozbitý, a nikdy se za to nepozastavuje. Pokud graf procesoru ve špičce naráží na strop, je to znamení ladit dotazy nebo přejít na vyšší tarif.

Bez SQL Server Agent: čím ho nahradit#

Krok úlohy Agentu je příkaz, plán a stav dokončení. Každý plánovač, který umí spustit sqlcmd, dá totéž. Přicházíš o vestavěnou historii, upozornění a poštu a nahrazuješ je vlastním logováním. Možnosti od nejjednodušší:

Vlastní plán hostingu, pro zálohy

Na RE:NODE má každý server záložku Schedules: cron výraz, pak seřazené úlohy se zpožděními, kde úloha je příkaz konzole, záloha nebo akce napájení. Úloha zálohy vytvoří noční kopii serveru bez skriptu, který bys musel udržovat. Tarify SQL Server mají 1 až 4 sloty záloh; zálohy jsou uložené mimo stroj, který chrání, dají se zamknout proti rotaci, stáhnout a obnovit tlačítkem. To pokrývá nejdůležitější práci, kterou Agent obvykle dělá. Nechápej to jako spouštění libovolného T-SQL z panelu; na to použij další možnosti. Syntaxi popisuje Cron expressions explained a co do plánu dát, napovídá scheduled tasks worth having.

cron plus sqlcmd

Na libovolném linuxovém stroji, který ovládáš (malý VDS, CI runner, aplikační server), spustí řádek v crontabu soubor se skriptem proti databázi. Heslo drž mimo příkazový řádek v proměnné prostředí SQLCMDPASSWORD:

$ export SQLCMDPASSWORD='the-generated-password'$ sqlcmd -S db.example.net,14330 -U sa -d app -C -b -i /opt/jobs/purge.sql

-S bere host,port s čárkou, -C důvěřuje certifikátu serveru (potřeba u self-signed a přijatelné jen tehdy, když hostitele znáš), -b způsobí, že sqlcmd při selhání skriptu vrátí nenulový kód, a -i pojmenuje skript. Právě návratový kód umožní cronu nebo obalu poznat, že se něco pokazilo. Přepínače a dva různé programy se stejným názvem popisuje sqlcmd and bcp.

Záznam v crontabu pro noční čištění ve 03:15, s připojováním výstupu do souboru s logem:

15 3 * * * /opt/jobs/run-sql.sh purge.sql >> /var/log/sqljobs.log 2>&1

Volání zabal do malého shell skriptu, který při selhání skončí nenulovým kódem a pošle ti zprávu (e-mail, webhook do chatu), aby se tiché selhání stalo viditelným. Je to náhrada za upozornění Agentu a Database Mail, které Express nemá.

Plánovač úloh Windows

Pokud tvoje aplikace běží na Windows, udělá totéž naplánovaná úloha spouštějící sqlcmd -S ... -i script.sql. Nastav ji, aby běžela bez ohledu na přihlášeného uživatele, přihlašovací údaje ukládej do úlohy, ne do skriptu, a nastav rozumný limit „zastavit úlohu, pokud běží déle než“, aby zaseknuté spojení nezablokovalo zítřejší běh.

Uvnitř aplikace

Hosted service v .NET, úloha Hangfire nebo Quartz.NET, cron knihovna pro Node nebo pracovní proces v jakémkoli jazyce umí podle plánu spouštět údržbové T-SQL a mají jednu výhodu: connection string i logger už mají. Stranu připojení pokrývá .NET with PostgreSQL, MySQL or SQL Server. Slabina je, že když aplikace neběží, neběží ani úloha, a úloha, která běží v každé instanci škálované aplikace, proběhne vícekrát. Použij ji na čištění a statistiky, ale nedělej z ní jediného, kdo zálohuje.

Co nedělat

Nespouštěj spojení, které visí ve smyčce WAITFOR DELAY v okně dotazu nebo v dlouhé proceduře, aby napodobilo plánovač. Drží spojení a relaci navěky, tiše zemře při restartu serveru a o selhání nic neřekne. Totéž platí pro trigger, který při každém vložení dělá těžkou práci, aby ses obešel bez úlohy: přesouvá náklady do požadavků tvých uživatelů.

Čím nahradit úlohy, které obvykle dělal Agent#

Většina instalací Agentu provozuje čtyři druhy úloh. Každá má přímý ekvivalent.

Na nativní zálohy stačí soubor skriptu o takové velikosti a dalším krokem je zkopírovat výsledek jinam:

BACKUP DATABASE [app] TO DISK = N'/var/opt/mssql/backup/app.bak'    WITH INIT, CHECKSUM, STATS = 10;RESTORE VERIFYONLY FROM DISK = N'/var/opt/mssql/backup/app.bak' WITH CHECKSUM;

Cesta je výchozí adresář záloh při instalaci na Linuxu; na hostingu se může lišit. Soubor .bak na stejném disku jako databáze chrání před překlepem, ne před ztrátou disku. O obnově, WITH MOVE a osiřelých uživatelích je SQL Server backup and restore. Protože Express zálohy sám nekomprimuje, zkomprimuj soubor po zápisu (gzip nebo zstd ve stejném skriptu), než ho odvezeš ze stroje.

Zálohy logu mají smysl jen tehdy, když je databáze v plném modelu obnovy. Pokud obnovu k okamžiku v čase nepotřebuješ, ALTER DATABASE [app] SET RECOVERY SIMPLE; tuto úlohu úplně odstraní a zabrání nekontrolovanému růstu logu popsanému v recovery models and log growth. Pokud ji potřebuješ, naplánuj BACKUP LOG a vyzkoušej obnovu k okamžiku v čase, protože řetězec logu, který jsi nikdy neobnovil, je hypotéza.

U údržby indexů a statistik dělej méně, než radí staré návody. Automatická aktualizace statistik je ve výchozím stavu zapnutá a malým databázím stačí. Fragmentace na NVMe skoro nevadí; index přebuduj nebo reorganizuj, až když umíš ukázat, že zpomaluje konkrétní dotaz. Pokud chceš hotový postup, bezplatné a široce používané řešení údržby od Oly Hallengrena je obyčejné T-SQL, které na Expressu funguje, dokud ho něco zvenčí volá, a o to v téhle části jde.

Plánování v praxi: noční úloha od začátku do konce#

Dohromady vypadá rozumný noční postup pro databázi Express takto:

1. 02:00 - plán hostingu spustí zálohu serveru (nebo tvůj skript provede BACKUP DATABASE a soubor zkopíruje pryč).
2. 03:15 - cron na stroji, který ovládáš, spustí skript čištění a archivace přes sqlcmd -b.
3. 03:45 - tentýž stroj spustí sp_updatestats, pokud jsi viděl zastaralé statistiky, a dotaz na velikost, který zapíše velikost databáze do logu nebo metriky.
4. Týdně - obnova poslední zálohy do zkušební databáze, pak DBCC CHECKDB nad obnovenou kopií, ne nad živou databází.
5. Měsíčně - přečti trend velikosti a rozhodni, zda komprimovat, archivovat, nebo přejít na vyšší tarif.

Úlohy časově rozlož, aby se dlouhé čištění nikdy nepřekrývalo se zálohou. Na čtyřjádrové instanci s cache 1,4 GB budou dvě těžké úlohy najednou zpomalovat aplikaci po celou dobu překryvu.

Přechod na větší tarif bez ztráty dat#

Když řešení přestanou stačit, je přechod na vyšší edici nebo jiný hosting záloha a obnova: .bak z Expressu se obnoví do Standard nebo Enterprise stejné nebo novější verze bez převodu. Přesun plánuj dřív, než ho vynutí strop. Migrating a database to SQL Server hosting popisuje pravidla verzí opačným směrem a which database should I use porovnává databázové stroje, pokud je skutečnou odpovědí jiný. Změna tarifu na RE:NODE server nepřebuduje, takže přechod z menšího tarifu Express na větší kvůli disku není migrace.

FAQ#

Jak se nejrychleji dostat pod limit 10 GB?

Najdi největší tabulky, smaž nebo archivuj zbytečné řádky po dávkách o několika tisících, přesuň ukládané soubory do objektového úložiště a zbytek zkomprimuj pomocí DATA_COMPRESSION = PAGE. Většina databází, které dosáhnou stropu, je plná logů a binárních dat.

Dají se v SQL Server Express spouštět plánované úlohy?

Ano, zvenčí databázového stroje. Použij sqlcmd z cronu nebo Plánovače úloh, spouštěč úloh v aplikaci, nebo vlastní plány hostingu pro zálohy. Chybí jen samotný SQL Server Agent.

Počítá se transakční log do 10 GB?

Ne, počítají se jen datové soubory. Log omezuje místo na disku, proto je neošetřovaný log v plném modelu obnovy skutečné riziko a proto na disku záleží při výběru tarifu.

Zvýší víc RAM limit buffer poolu 1,4 GB?

Ne. Buffer pool se zastaví na 1410 MB na instanci, ať má server paměti kolik chce. Paměť navíc pomůže jen zbytku procesu a operačnímu systému, takže peníze radši dej do disku nebo lepších indexů.

Jak dostat upozornění bez Database Mail?

Nech skript úlohy při selhání vracet nenulový kód přes sqlcmd -b a obal ať v takovém případě pošle zprávu na e-mail nebo do webhooku chatu. Upozornění pak žije ve tvém plánovači, ne v databázovém stroji.