trešdiena, 2011. gada 16. februāris

Faili un failu grupas SQL Server

Rakstā plašāk aprakstīts kas ir fails un kas ir failu grupa (file and filegroup) SQL Server.

Iepriekš rakstot par optimizāciju un aprakstot kā dati tiek glabāti SQL Server (Datu glabāšana SQL Server) pieminēju, ka datu bāze sastāv no vismaz diviem failiem- datu bāzes failu un log faila. Vizuāli tas varētu izskatīties apmēram tā:

Minētajā rakstā netika aprakstīts, ka datu bāzes datu faili (tātad visi faili, kas nav log faili) atrodas failu grupā (filegroup). Katrai datu bāzei ir vismaz viena primārā failu grupa ar tādu pašu nosaukumu- "Primary". Katram datu failam jāatrodas vienā un tikai vienā failu grupā.
Tātad, precīzāks iepriekšējās bildes attēls būtu šāds:

Jā, neizskatās nekas būtisks.. Kāpēc tad failu grupa būtu svarīga? Tam nozīme parādās brīdī, kad vienā failu grupā ir vairāk kā viens fails.

Failu grupa ar vairākiem failiem

Vizuāli datu bāze, kurā ir viena failu grupa ar vairākiem failiem izskatās šādi:
Idejas te apakšā ir vairākas:
  1. Datu bāzes fails ir fiziska vienība. Strādājot ar datu bāzi, kurā ir vairāki faili vienā failu grupā, mēs nevaram norādīt, kurā datu bāzes failā nonāk dati (mēs varam norādīt failu grupu, bet par to nākamajā apakšsadaļā).
  2. Datu bāzes fails atrisina problēmu, ja vairāk diskā nav vietas, bet ir nepieciešamība pēc lielākas datu bāzes. Tātad- pievienojam jaunu failu uz cita diska un pievienojam failu grupai. Pievienojot datus tie tiks ievietoti failā, kurā ir vieta. Tā kā pievienotais fails atrodas uz cita diska un tajā ir vieta, tad dati tiks pievienoti šajā jaunajā failā.
    Starp citu, 1. punktā, kurā apgalvots, ka mēs nekontrolējam kur nonāk dati. Mēs tiešām nekontrolējam. Par to sīkāk 3. punktā.
  3. Veidojot objektu, mēs norādām kurā failu grupā tas atrodas. Ja šajā failu grupā ir vairāki faili, mēs nezinām, kuri objekta dati atrodas kurā failā.
    Ideja tāda:
    Piemēram, failu grupā ir trīs datu bāzes faili- nr1, nr2, nr3, kuros attiecīgi ir vieta nr1- 200mb, nr2- 200mb, nr3-600mb. Ja šajā failu grupā izveidosim vienu tabulu, kurā ievietosim 100 mb informācijas, tad rezultātā informācija būs izmētāta pa failiem- apmēram 20 mb failā nr1, 20 mb failā nr2 un 60 mb failā nr3. Tas ir tādēļ, ka vieta priekš datiem tiek iedalīta failos failu grupā, proporcionāli vietas daudzumam, kas ir attiecīgajā failu grupā.
  4. No iepriekšējā punkta arī izriet, ka gadījumā, ja mēs izveidojam datu bāzes failu grupu ar vairākiem failiem, kurus mēs izvietojam uz dažādiem fiziskiem diskiem, mēs iegūstam ātrdarbības uzlabojumus brīžos, kad dati jānolasa no cietā diska (kā zināms, SQL Server ļoti cenšas paturēt visu mums vajadzīgo operatīvajā atmiņā). Informācija tiek lasīta nevis no viena diska, bet no diviem diskiem uzreiz.
Tomēr šis veids nav tas veiksmīgākais, kā dalīt datus. No tā izriet problēmas, piemēram, mēs nezinām kurā datu failā atradīsies dati. Dati var būt izmētāti pa datu failiem neveiksmīgā veidā. Lielāku kontroli var panākt, izmantojot failu grupas.

Vairākas failu grupas
Vizuāli datu bāze ar vairākām failu grupām varētu izskatīties šādi:
Šajā gadījumā ir izveidota situācija, ka vienā datu bāzē ir 3 failu grupas. Viena failu grupa ir tā, kas pēc noklusējuma ir katrai datu bāzei un saucās Primary, tajā viens fails. Otra failu grupa- Nr1 sastāv no viena datu bāzes faila. Trešā failu grupa- Nr2 sastāv no diviem failiem. Vairāk šādas struktūras datu bāzi aprakstīšu ar piemēru tālāk rakstā. Bet runājot par failu grupām galvenais par ko der padomāt:
  1. Katrs fails pieder failu grupai. Ja datu bāzei veido vairākus datu failus un tai nav veidotas failu grupas, tad visi faili pieder Primary failu grupai.Viens fails nevar piederēt vairākām failu grupām. Failu grupas tiek veidotas tikai un vienīgi vienas datu bāzes ietvaros.
  2. Veidojot objektu tiek norādīts, kurā failu grupā tas atradīsies. Ja veido dalītus objektus (Tabulu horizontālā dalīšana (Partitioned Tables)), dati var atrasties dažādās failu grupās. Šādā gadījumā dati nonāk konkrētā failu grupā pēc principa, kādu nosaka datu dalīšanas funkcija un shēma.
  3. Ņemot vērā to, ka var kontrolēt kurā failu grupā nonāk dati, var izmantot šo iespēju datu bāzes optimizācijai, piemēram
    1. Tabulas datus novietot vienā failu grupā, savukārt indeksus šai tabulai novietot citā
    2. Tabulas, kas vienā vaicājumā savstarpēji saistītas novietot dažādās failu grupās
  4. Datu bāzei var veidot rezerves kopijas pa konkrētām failu grupām, tāpat arī atjaunot iespējams konkrētu failu grupu.
Piemērs
Iepriekš aprakstītajai (Vairākas failu grupas) datu bāzes struktūrai piemērs ar skaidrojumiem komentāros. Iespējams, labs veids paskatīties kā tas viss strādā, ir šo skriptu pa solīšiem iziet cauri.
-- Tiek izveidota datu bāze pēc iepriekš aprakstītā principa:
-- failu grupa "Primary", 1 fails
-- failu grupa "FailuGrupa1", 1 fails
-- failu grupa "FailuGrupa2", 2 faili
-- log fails- "TestDB_log"- nepieder nevienai failu grupai
-- UZMANĪBU- jābūt attiecīgai mapei (c:\Temp\)
CREATE DATABASE [TestDB] ON PRIMARY
( NAME = N'TestDB', FILENAME = N'C:\Temp\TestDB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FailuGrupa1]
( NAME = N'Fails2', FILENAME = N'C:\Temp\Fails2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FailuGrupa2]
( NAME = N'Fails3', FILENAME = N'C:\Temp\Fails3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
( NAME = N'Fails4', FILENAME = N'C:\Temp\Fails4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TestDB_log', FILENAME = N'C:\Temp\TestDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)


Go
use TestDB
-- Apskatam visas datu bāzes failgrupas. Redzam, ka "Primary" failgrupa ir arī noklusētā.
Select * From sys.filegroups
-- Apskatam visus datu bāzes failus divos veidos
Select * From sys.master_files Where Database_id = db_id()
select * from sys.sysfiles


Go
Create Table dbo.TTable1
(
    x char(8000)
)
Go
-- Noņemam lai neatgriež info par to, ka ievieto rindņu (mini-optimizācija)
Set NoCount ON
-- Ievietoju 1000 rindiņas.
--    tas kopumā veido aptuveni  8 Mb
--  Jāpievērš uzmanība "Go" komandai (http://www.sqlblog.lv/2011/01/par-go-komandu.html).
Insert Into TTable1 (x) values('tratata')
Go 1000


Go
-- Nomainu noklusēto failu grupu (uz FailuGrupa1) un apskatu rezultātu
ALTER DATABASE [TestDB] MODIFY FILEGROUP [FailuGrupa1] DEFAULT
Select * From sys.filegroups


Go
-- šoreiz tabulai jābūt izveidotai uz citas failu grupas, jo tika nomainīta noklusētā!!
Create Table dbo.TTable2
(
    x char(8000)
)
Go
-- Tāpat kā iepriekš
Insert Into TTable2 (x) values('tratata')
Go 1000


Go
-- Specifiski norādu, kurā failu grupā veidot tabulu!!
Create Table dbo.TTable3
(
    x char(8000)
) On [FailuGrupa2]
Go
-- Tāpat kā iepriekš
Insert Into TTable3 (x) values('tratata')
Go 1000


Go
-- Kad visas trīs tabulas ir izveidotas, pārliecinamies, ka tās tiešām atrodas tur,
--    kur tām būtu jāatrodas:
Select o.name objekta_nosaukums, f.name failu_grupas_nosaukums
From sys.indexes i
    Inner Join sys.filegroups f On i.data_space_id = f.data_space_id
    Inner Join sys.all_objects o On i.[object_id] = o.[object_id]
Where o.name like 'TTable%'
-- Un otra lieta par ko pārliecināties- ka Failu grupā Nr 2, kurā bija divi faili,
--    tie ir aizpildījušies līdzīgi (ņemot vērā īpašību, ka dati tiek "izmētāti"
--    pa failu grupas failiem!).
SELECT physical_name, type_desc, size
FROM sys.master_files
WHERE [database_id] = db_id()
Use Master
Drop DataBase TestDB
Par piemēru vēl derētu piebilst:
* Rindiņas testa tabulās speciāli ir veidotas tā, lai katra rindiņa aizņemtu vienu datu lapu. Datu bāzes tabulām vieta tiek piešķirta uzreiz 8 lapu ietvaros, jeb viena extent (=8 lapas) apmērā. Šajā gadījumā visas tabulas ir "kaudzes" jeb heaps (teorija par to visu- Indeksi (pamati)).
* Dalīto tabulu/indeksu gadījumā (Tabulu horizontālā dalīšana (Partitioned Tables)) vaicājums, kas parāda kurā failu grupā atrodas tabula, neparādīs rezultātus.
* Visas minētās darbības var izdarīt arī ar SSMS interfeisa palīdzību.
* Reālā situācijā visdrīzāk katra failu grupa (faili tajā) atrastos uz cita fiziska diska.
* Piemēru var pamainīt, lai otrās failu grupas faili būtu dažāda izmēri. Tad var redzēt, ka dati tiek aizpildīti proporcionāli brīvajai vietai failā.

Papildus literatūra:
MSDN par failiem un failgrupām šeit
MSDN cits raksts šeit

4 komentāri:

  1. OK tā kā man slinkums lasīt dokos :) , tad es pareizi saprotu, ka arī vienai konkrētai tabulai datubāzē, nevar noteikt kurā failā (un attiecīgi diskā) tā nonāks? Tas ir, ja man ir, piemēram, kāda ļoti mainīga tabula, kuru gribās uzlikt uz jaunā modes SSD diska, tad man jāveido jauna datubāze, kurai es varu pateikt, ka tai visi faili būs uz SSD un tad tabula jāveido tajā?

    AtbildētDzēst
  2. nē, ne gluži. Tu vari darīt tā:
    1. izveidot datu bāzei jaunu failu grupu X, ar vienu failu- failam pasaki, ka tas atradīsies uz tā SSD diska.
    2. izveidot tabulu, to veidojot pasaki, ka tai jāatrodas failu grupā X
    Tādā veidā Tu skaidri zināsi, ka tabula atradīsies tieši tur.

    Savukārt- ja Tu izveidotu failu grupu X ar diviem failiem- abi vienāda izmēra, katrs atradīsies savā cietajā diskā. Izveidosi tabulu, kas atrodas šajā failu grupā. Un ievietojot datus- tie nonāks proporcionāli- viens vienā failā (uz viena diska) otra daļa datu otrā failā (otra diska).

    AtbildētDzēst
  3. Tabulai (datiem tabulā) nevar noteikt kurā failā jāatrodas, bet var noteikt kurā failu grupā!

    Ja failu grupai viens fails, viss ir brīnišķīgi un sanāk, ka visi dati atrodas šajā vienīgajā failā.

    AtbildētDzēst