Vaicājumu izpildes laiks

Vaicājuma izpildes laiks arī ir pieminēšanas vērts (papildina rakstiņu Vaicājuma IO statistika). Šajā rakstā izpildes laiks tiek apskatīts no diviem rakursiem- (1) reālais izpildes laiks un (2) CPU laiks.

Kas notiek? decembris 2010 :)

Neliels atskats uz bijušo un mazliet par nākotnes plāniem.

Tabulas un indeksi (pamati)

Rakstu apvienojums par indeksiem. Aprakstīts, kā dati tiek glabāti SQL Server, datu bāzes tabulas ar indeksu un bez indeksa. Tiek aprakstītas klasterētā un parastā indeksa īpatnības, kā tos izveidot, kā apskatīt to stāvokli un pārliecināties, kā tie tiek izmantoti. Aprakstīts, kā veikt indeksu apkopi.

Indeksu apkope

Indeksu apkope būtu regulāri veicams process un rakstā sniegts pamatojums kāpēc. Īsi aprakstīts varianti indeksu apkopei. Rakstā arī piemērs, kas var tikt izmantots indeksu pārbūvēšanai.
Praksē ir bijuši gadījumu, ka pēc indeksu pārbūves datu bāze sāk strādāt daudz, daudz ātrāk (jā jā, vairumā, protams, tas neko daudz nemaina, tomēr tie pāris gadījumi ir pūļu vērti).

OCV lai iegūtu DB objekta izveides skriptu

Rakstā īsi aprakstīti divi OCV (Object Catalog Views) skati- sys.objects un sys.sql_modules. Šo abu skatu kombināciju var izmantot lai iegūtu procedūru, skatu, ... izveides skriptus.

Piemērs: ko iesākt ar to, ko zinām par indeksiem?

Tāds visai īss rakstiņš, kurā pāris idejas- ko darīt ar visu to, ko nu uz šo brīdi zinām par indeksiem.

Kuri indeksi tiek izmantoti?

Veidojot indeksu, vienmēr to mēģina izveidot pēc iespējas labāku, noderīgāku. Bet ne vienmēr tas izdodas. Dodot ieteikumus indeksu izveidei, parasti līdzi nāk teksts "neaizmirstiet visu testēt, testēt un vēlreiz testēt". Ir jau skaisti, tik ne vienmēr veidojot testus iznāk izdarīt pareizus pieņēmumus par reālo vidi. Kādā no turpmākajiem rakstiem būs plašāk apskatīts kā SQL Server "izdomā" vai indeksus izmantot. Tas ļaus izdarīt labākus pieņēmumus par to indeksu nepieciešamību. Šajā rakstā savukārt- kā uzzināt vai un kuri izveidotie indeksi tiek izmantoti? (tātad- fakta konstatēšana)

Indeksu fragmentācija

Indeksu fragmentācija nav vislielākā problēma strādājot ar indeksiem. Tomēr atsevišķās situācijās tas var būtiski (atkarībā no situācijas) ietekmēt ātrdarbību. Šajā rakstā aprakstīts kas ir iekšējā un ārējā fragmentācija, dots teorētiskais skaidrojums. Doti ieteikumi kā minimizēt ārējo fragmentāciju (iekšējās fragmentācijas novēršana aprakstīta rakstā- Indeksu apkope).

Informācijas par indeksiem apskatīšana

Aprakstīts kā apskatīt informāciju par indeksiem- divos veidos- (1) ar SSMS grafiskā interfeisa palīdzību un (2) izmantojot DMV (Dynamic Management Views and functions) funkciju sys.dm_db_index_physical_stats. Dots skaidrojums iegūtajiem rezultātiem- lapu 'pilnums', lapu skaits, ierakstu skaits, indeksa dziļums un citi.

SQL Server iebūvētās funkcijas

Rakstā vienuviet apvienotas funkcijas, kas var noderēt ikdienas darbā strādājot ar SQL Server. Dotas saites uz MSND rakstiem, kur funkciju skaidrojumi.

Utilītprogramma: bcp. Datu eksports/imports

Kad ir vajadzība datubāzes datus eksportēt uz ārēju failu vai importēt no faila datubāzē, datu eksportam un importam no teksta failiem SQL Server iespējams izmantot bcp utilītu, kura bez problēmām tiek galā arī ar liela apjoma datu apstrādi.

Pieteikšanās (logon) trigeri

Rakstā aprakstīts pieteikšanās (Logon) trigeris, apskatītas subjektīvi galvenās nianses, kas būtu jāņem vērā tos veidojot. Raksta beigās atsauces uz citiem avotiem, kur par tiem var palasīt vairāk.

Bitwise OR, jeb kā caur vienu parametru var padot vairākas vērtības?

Rakstā ar piemēru aprakstīts Bitwise OR iekš SQL Server. Droši vien noderētu priekšzināšanas par Būla algebru.

Indeksu izveide

Rakstā aprakstītas indeksu izveides iespējas un īpašības SQL Server 2005, ko var norādīt/kas ir jānorāda. Nav aprakstīti XML indeksi. SQL Server 2008 indeksiem ir iespējams norādīt "Where" daļu, šeit tas netiek aprakstīts.

Piemērs: Indeks ar unikālām vērtībām, IGNORE_DUP_KEY

Piemērā parādīts, kā strādā IGNORE_DUP_KEY unikālam indeksam.

Indeksēti (materializētie) skati

Raksta sākuma pavisam īsi teorija par to, kas ir indeksētais skats. Tālāk dots piemērs indeksētā skata izmantošanai, norādot uz dažām, manuprāt būtiskākajām, niansēm, kas jāņem vērā veidojot indeksētu skatu.

Mazliet par to kas notiek

Jau atkal gribēju pastāstīt par nākotnes plāniem (konteksts: emuārs).

Datumi. Noderīgas funkcijas

Vakar jau neskaitāmo reizi uzdūros tai pašai problēmai- datumi, datumi, datumi. Pašam iekrājušās pāris noderīgu www adrešu. Domāju nebūs nekas slikts, ja padalīšos.

Piemērs: Funkcija, lai iegūtu datumus laika intervālā

Piemērā izveidota funkcija, kurai tiek padoti divi datumi- DatumsNo un DatumsLidz. Funkcija atgriež tabulu ar datumiem šajā periodā (beigu datumu neieskaitot) ar divām kolonnām- Datums un DatumsChar.

Indeksi (non-clustered indexes)

Rakstā aprakstīts un ar ilustrācijām parādīts kas ir indeks (non-clustered). Atšķirības starp indeksu, kas veidots uz tabulas bez klusterētā (clustered) indeksa un tabulas ar klusterēto indeksu.

Piemērs: Indeksi, vaicājumu analīze

Rakstā doti 4 piemēri:
* klusterētā indeksa izmantošana, 
* datu nolasīšana pēc vērtības, kas nav klusterētā indeksa atslēga,  
* datu nolasīšana pēc vērtības, kas ir parasta (non-clustered) indeksa atslēga
* ilustrācija- (non-clustered) indekss satur klusterētā indeksa atslēgu

Piemērs: Lapu dalīšanās (Page splits)

Piemērā tiek ilustrēta tabula ar klusterēto (clustered) indeksu un parādīts gadījums, kad notiek lapu dalīšanās datu mainīšanas rezultātā.

Tabulas ar klusterētu (clustered) indeksu

Rakstā aprakstīts un ar ilustrācijām parādīts, kā dati tiek glabāti un meklēti tabulā, kurai ir klusterēts indekss. Aprakstītas indeksu lapas  īpašības.

Labākais darbs (Amerikā)

Skat tik! :)
Amerikā datu bāžu administrators ir novērtēts kā 7. labākais darbs (pēc apmaksas un izaugsmes iespējām). Nav slikti.

Piemērs: pāredresētie ieraksti (Forwarded records)

Piemērā tiek ilustrēta tabula bez indeksiem un parādīts kas notiek datu mainīšanas gadījumos.

Tabula bez klusterētā (clustered) indeksa

Rakstā aprakstīts un ar ilustrācijām parādīts, kā dati tiek glabāti tabulā, kurai nav indeksu. Vienlaicīgi dziļāk tiek apskatīts kā dati tiek glabāti lapu ietvaros. Apskatīts, kas notiek datu ievietošanas, dzēšanas, mainīšanas gadījumos tabulā, bez klusetētā indeksa (tādu struktūru sauc par "kaudzi"- heap).

Datu drošība divu datu bāzu gadījumā

Piemēri datu izgūšana no citas datu bāzes izmantojot kādu no 4 variantiem- (1) veidojot lietotājus abās datu bāzēs, (2) izmantojot “Ownership Chaining”, (3) izmantojot “Execute As”, (4) izmantojot sertifikātus.

Datu glabāšana SQL Server

Rakstā tiek pārskriets pāri SQL Server datu glabāšanas struktūrām, sniedzot atsauces uz papildus materiāliem padziļinātai informācijas iegūšanai. Šis raksts ir kā pamats vairākiem turpmāk plānotajiem rakstiem.

Mazliet par to, kas notiek

Sākotnēji gan nebiju domājis šeit rakstīt "par dzīvi", bet šodien sanāca piedalīties pasākumā "Kāpēc vērts izmantot Microsoft SQL Server" un tad jau mājupceļā radās doma par nelielu rakstiņu (gan par pasākumu, gan par to kas vispār notiek).

Izolācijas līmeņi transakcijām

Šajā rakstā aprakstīti SQL Server 2005 transakciju izolācijas līmeņi (isolation level).

Kura nedēļas diena?

Lai noskaidrotu kura nedēļas diena ir šodien, ir jāizmanto funkcija datepart(). Viss būtu skaisti, ja vien visā pasaulē pirmā nedēļas diena būtu pirmdiena. Problēmas garāks skaidrojums un risinājums turpinājumā.

Trigera aizliegšana vienas transakcijas ietvaros

Tāda eksotiska situācija gadījās- ir tabula, kurai ir trigeris. Trigeris rūpējas par biznesa loģiku. Vienmēr tas ir jādara, bet ir viena izņēmuma procedūra, kuras izpildes laikā trigerim nav jāstrādā. Zemāk aprakstīts risinājums.

Determinētu funkciju izmantošana

Rakstā īsi aprakstīts kas ir determinētas un kas ir nedeterminētas funkcijas. Mazliet garāk rādīts un skaidrots kā determinētu funkciju izmantošana uzlabo (var uzlabot) ātrdarbību.

CLR agregātfunkcijas piemērs

Sākot ar SQL Server 2005 ir iespēja veidot agregātfunkcijas izmantojot Visual Studio .NET. Tas nozīmē, ka papildus jau iebūvētām agregātfunkcijām (Sum, Average, Min, Max u.c.), ir iespēja pievienot arī paša veidotas agregātfunkcijas.

Šajā rakstā piemērs, kā izveidot funkciji, kas sarindo vairākus teksta apgabalus vienu aiz otra. Piemēram, ja vienā rindā ir ‘Jānis’ otrā rindā ir ‘Linda’, tad agregātfunkcijas izpildes rezultātā šie abi vārdi tiek savienoti vienā virknē izveidojot teksta virkni ‘Jānis;Linda’.

SQL Server programmēšana

.NET integrācija ļauj programmētājiem veidot jaudīgas lietotāja definētas procedūras, trigerus, lietotāja definētas funkcijas, agregātfunkcijas un tipus SQL Server iekšienē, izmantot to .NET valodu, kura ir izstrādātājam vairāk pazīstama (šobrīd gan var izmantot C#, VB un C++).

CLR 'nekonedara' procedūras izveide

Šeit detalizētāk aprakstīts kā var izveidot CLR procedūru, galvenā uzmanība uz to kā realizēt tehniski, nevis kāda jēga no izdarītā :).

Microsoft Visual studio

Sākot ar SQL Server 2005 kopā ar SQL Server developer edition (SQL Server Express instalācijā šis rīks neietilpst) nāk rīks ar nosaukumu „SQL Server Business Intelligence Development Studio”. Nodaļas virsraksts gan ir Microsoft Visual studio- un tas ir apzināti. Katram, kas ir programmējis ar Visual Studio (c#, Visual Basic, ..) šis rīks būs pazīstams- patiesībā abi šie rīki ir viens un tas pats, bet atšķirība ir tā, ka SQL Server Business Intelligence Development Studio ir Visual Studio ar citiem projektu tipiem un nogrieztām iespējām. Ja lietotājam nav uz datora iepriekš instalēta Visual Studio, tad kopā ar serveri tā tiek uzinstalēta ar ierobežotām iespējām (orientēta strādāšanai ar SQL Server). Savukārt, ja Lietotājs strādā ar VS2005, tad tiek atvērta jau ierastā Visual studio vide.

Rīks: SQL Server Profiler

Ļoti noderīgs rīks SQL Server monitorēšanai. Ar SQL Server profiler var redzēt kādas komandas tiek izpildītas SQL Server, var reģistrēt dažādu notikumu datus (piemēram, resursu bloķēšanas notikumi, strupsaķeres, utt)

SQL Server Management Studio

Sākot ar SQL Server 2005 galvenais rīks, ar ko strādā datu bāzes izstrādātāji, ir SQL Management Studio. Salīdzinot ar iepriekšējām SQL Server versijām šis rīks sevī apvieno iepriekš pieejamos- SQL Server Enterprice Manager un Query Analyzer. Svarīgi ir tas, ka strādājot ar SQL Server Express 2005 un jaunāku (bezmaksas SQL Server versija, kā būtiskākais ierobežojums ir datu bāzes apjoms- šobrīd 4 GB), ir pieejams bezmaksas rīks datu bāzes administrēšanai- SQL Server Management Studio Express. Bezmaksas versijā gan ir ierobežojumi, tomēr, kamēr nav jāveic ļoti sarežģīti uzdevumi, ar to pilnīgi pietiek.

Vaicājuma statistika

Raksta mērķis mudināt paspēlēties ar "Statistics IO" tiem, kas to vēl nav darījuši.

Aprēķinātās kolonnas

Dažkārt ir labi izmantot kolonnas, kuru vērtības tiek aprēķinātas dinamiski, vaicājumam griežoties pie kādas konkrētas kolonnas- tās sauc par aprēķinātajām kolonnām (computed column).

Kāda vērtība tika ielikta Identity laukā?

Daži veidi kā noskaidrot, kāda vērtība tika ievietota Identity laukā (vispār var arī netikt ievietota, ja notiek kļūda vai tiek taisīts Rollback transakcijai):

1) @@Identity- atgriež pēdējo vērtību, kas ģenerēta priekš identity lauka. Ja Tabulā, kurā ievieto datus, ir trigeris, kas raksta datus citā tabulā, tad atgriezīs tās tabulas Identity, kurā datus rakstīja pēdējā. Konekcijas ietvaros!

2) SCOPE_IDENTITY()- atgriež pēdējo ģenerēto Identity vērtību vērtību, bet neskatās, kas notiek trigeros. Konekcijas ietvaros! (šis arī tas, kas tiek izmantots, ja ģenerē .NET SqlDataAdapter- insert komandu)

3) IDENT_CURRENT('TabulasNosaukums')- pēdējo ģenerēto Identity vērtību, kas ģenerēts norādītajā tabulā. Šeit jārēķinās, ka citas konekcijas var iespaidot atgrieztos rezultātus.

4) [2011-05-17 papildināts] Ievietoto datu identifikatoru vērtību iegūšanai var izmantot arī T-SQL Output klauzu.

Labāk šis aprakstīts un ar piemēru ilustrēts šeit: http://msdn.microsoft.com/en-us/library/ms175098.aspx bet arī šeit, jo ik pa laikam šo sanāk meklēt.

Identity kolonnā sev vēlamās vērtības

Šeit aprakstīts, kā panākt, lai Identity kolonnā var ierakstīt sev vēlamās vērtības.

Atkarībā no programmas dažādi vaicājuma rezultāti

Piemēram- ir datu bāze, kurā strādā ar vairākām programmām. Programmas ielasa datus no skatiem. Skatos ir pielikta "where" daļa.
Nepieciešamas izmaiņas- viena no programmām, kas lasīja datus no šiem skatiem ir jāmaina tā, lai tiktu ielasīti pilnīgi visi dati. Tajā pašā laikā saglabājot to, ka pārējās programmās tiek ielasīti dati tieši tāpat, kā to darīja iepriekš.
Ko darīt?
Izveidojam funkciju, kas atgriež vērtību 1, ja jārāda visi dati, 0, ja jārāda tikai pēdējā gada dati- atkarībā no programmas, ar ko lietotājs strādā. Šo funkciju izmantot skatos. Tādā veidā panākot minimālu izmaiņu nepieciešamību programmā.
Funkcija izskatītos apmēram šādi:

Create FUNCTION [dbo].[fn_JaunaProgramma] ()
RETURNS [bit]
AS BEGIN
IF EXISTS (SELECT program_name FROM master.dbo.sysprocesses
    WHERE spid = @@spid AND program_name = 'Nosaukums')
       RETURN 1;
RETURN 0;
END

„Nosaukums” ir programmas nosaukums, ar kuru lietotājam jāslēdzās klāt lai atgrieztu vērtību ‘1’. (Kā uzstādīt programmas nosaukumu)
Savukārt pats vaicājums skatā varētu izskatīties šādi (zvaigznīte konkrētu kolonnu uzskaitījuma vietā netiek rekomendēta):

Select *
From dbo.Tabula
Where dbo.fn_JaunaProgramma() = 1 OR gads > 2008

BET! Kods ir ļoti grūti testējams (kāds saka, ka neatlasa datus, bet programmētājs pieslēdzas ar Management Studio un viss tiek rādīts, jo ir cits programmas nosaukums)! Var rasties daudz mulsinošu problēmu, par kurām neviens nekad neiedomāsies. Šis bija veiksmīgs pagaidu risinājums (daļa programmu strādāja vienā veidā, otra daļa- pavisam citā) pārejas periodam. Pēc tam funkcija vienkārši atgrieza vienu vērtību- „1”. Kā galīgo risinājumu šādu nerekomnedētu.

Programmas nosaukuma norādīšana

Pēc noklusējuma- jebkura izveidota .Net programma Profiler pie „ApplicationName” uzrāda „.NET SQLClient Data Provider”. Ja strādā ar vienu programmu, tad nav nekādu problēmu, bet, ja ar datu bāzi strādā uzreiz vairākas, būtu ērti redzēt skaistu programmas nosaukumu.

Lai to risinātu jāpievieno konekcijai šo: „Application Name=ManaProgramma”, kur ManaProgramma ir programmas nosaukums.
Kopumā tas varētu izskatīties šādi:

„Data Source=SQLServerExpress;Initial Catalog=TestDB;Integrated Security=True; Application Name=ManaProgramma”

Ļoti vienkārša, bet ērta lieta.

Kolonnu saraksta un atbilstošo datu tipu iegūšana

Veidojot aprakstus datu bāzei, kau apskatītu kādi lauki ir tabulā, skatā, funkcijā (kas atgriež tabulu), lai iegūtu kolonnu sarakstu, var veidot diagrammas, otrs variants ir izmantot sp_help procedūru. šādi:

sp_help 'objektaNosaukums'

vai arī- datu bāzes objektu sarakstu iegūst:

select * from sys.all_objects
select * from sys.all_columns

Pēc tam abus var savā starpā sasiet ar object_id palīdzību un izveidot visu ko nu nepieciešams.
Tas gan nestrādā uz vaicājumiem (gribējās dabūt veidu, kā iegūt šādu sarakstu uz jebkuru vaicājumu).

Piezīmes:
Vispār raksta doma bij pilnīgi savādāka nekā beigās sanāca. Sākotnējā doma bija- jebkuram vaicājumam iegūt kolonu sarakstu ar datu tipiem. Bet nekādā elegantā veidā to neizdevās izdarīt. Ja kāds pateiks kā to var izdarīt būšu priecīgs.

Doma kurā virzijos- kad .Net izveido datu adapteri, var nolasīt shēmu no datu bāzes (reāli nenolasot datus). Tas izskatās apmēram tā (dt- jauna datu tabula bez kolonām, sql- vaicājums):

SqlDataAdapter da = new SqlDataAdapter(sql, this.sqlConnection1);
da.FillSchema(dt, SchemaType.Source);

Paskatoties ar profiler tiek izpildīta sekojoša komandu virkne:

SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;
select * from tabula
SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

Atslēga tajā visā ir „SET FMTONLY ON”- ja šis parametrs ir „ON”, tad vaicājums neatgriež datus, bet tikai shēmu.
Respektīvi- paliku pie secinājuma, ka pa tiešo nav ērta veida, kā iegūt visus rezultātus, bet ar .net programmas palīdzību to tā kā varētu sataisīt..

SnapShot izmantošana testēšanā

Strādājot pie izmaiņām ierastais proces ir apmēram tāds- viens vai vairāki cilvēki viec izmaiņas testēšanas vides serverī, tiek laisti daudzi skripti, datu mainīšanas vaicājumi. Vēlāk datu struktūras maiņas skripti tiek apvienoti vienā lielākā, vēlreiz pārbaudīti uz citas testēšanas vides un galu galā tiek laisti produkcijas vidē.

Problēma:
Pēc neveiksmīgas vaicājumu izpildes nepieciešams atjaunot vidi tādu, kāda tā bija- to var darīt ar vaicājumu palīdzību, kas atsauc veiktās izmaiņas (piemēram- nepareizi pievienojam kolonnu, tad nu to pēc tam dzēšam). Tāpat to var darīt ar rezerves kopiju (BackUp) palīdzību. Ja pirmais veids ir ātrs (bet ne vienmēr izpildāms, jo piemēram, netīši izmetot kolonnu atjaunot to ar iepriekšējiem datiem ar vaicājumu palīdzību parasti nav iespējams), tad otrs jau ir lēns un samērā piņķerīgs process.

Risinājums (sākot ar SQL Server 2005):

Izmantot datu bāzes SnapShot (SnapShot pēc idejas ir datu bāzes ‘momentuzņēmums’ tā izveides brīdī- SnapShot’ā datus mainīt nav atļauts).

Tātad izveide:

CREATE DATABASE TDatuBaze_SnapShot ON
(
NAME = TDatuBaze,
FILENAME = 'C:\SQL\TDatuBaze.ss'
)
AS SNAPSHOT OF TDatuBaze;

Kur TDatuBaze_SnapShot- veidojamā ‘momentuzņēmuma’ nosaukums,
„TDatuBaze”- fiziskais faila nosaukums, no kā veido ‘momentuzņēmumu’:

FileName-jaunā SnapShot faila fiziskā atrašanās vieta (kā nu paši izdomājam).
„As SnapShot OF TDatuBaze” – kurai datu bāzei tiek veidots ‘momentuzņēmums’.
Pēc skripta izpildes pie Databases Snapshots varēs atrast jaunizveidoto snapšotu. To var pavisam droši izmantot select vaicājumu izsaukšanai vai datu bāzes iepriekšējās struktūras apskatīšanai.

Atjaunošana:

Use Master
ALTER DATABASE TDatuBaze SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE TDatuBaze from DATABASE_SNAPSHOT = 'TDatuBaze_SnapShot'
ALTER DATABASE TDatuBaze SET MULTI_USER WITH ROLLBACK IMMEDIATE

Šajā skriptā:
· No sākuma pārslēdzam konekciju uz Master datu bāzi (datu bāzei nedrīkst lietot brīdī, kad to atjauno no SnapShot).
· Nežēlīgi atsitam visus citus lietotājus, kas šobrīd varētu lietot datu bāzi.
· Atjaunojam.
· Atļaujam citiem lietotājiem slēgties klāt datu bāzei.

Pēc skripta izpildes datu bāzi ir tieši tāda, kāda tā bija SnapShot izveides brīdī. Galvenā atšķirība no BackUp:
· Strādā stipri ātri (pie izveides dati netiek kopēti- tikai tad, kad pamata datu bāzē tiek veiktas izmaiņas) un ērti
· Var salīdzināt snapshot ar aktuālo datu bāzi

Ierobežojumi:
· SnapShot nevarēs izveidot uz SQL Server Express (vajag Enterprise Edition)
· Vienai datu bāzei šādā scenārijā nevar būt vairāk kā viens SnapShot (nu- principā būt var, tikai atjaunot datu bāzi varēs tikai tad, kad paliks tikai viens snapshot).
· Ja datu bāze, kurai ir izveidots SnapShot atjauno no rezerves kopijas- tad SnapShot vairs nav derīgs.
· Būtu jārēķinās ar papildus slodzi serverim, jo mainot datus jārūpējas arī par SnapShot.