Sql Server

Table spool la apelul unui UDF

Daniel Bitin — Wed, 22 Aug 2007 13:31:31 GMT

Table spool la apelul unui UDF

Mai demult, in cadrul unui proiect, a trebuit sa implementam o functionalitate ce cauta in rezultatele aplicarii unui UDF pe toate liniile dintr-o tabela foarte mare.

Ne asteptam la un timp de executie mare - se executa un table scan iar tabela nu putea fi cache-ata cu totul in memorie, mai ales in conditii de concurenta mare deoarece alte parti din baza de date erau folosite mai intens in utilizarea normala. Estimarile ne-au fost insa depasite cu mult. Era timpul studierii planului de executie.

In acesta aparea un operator fizic la care nu ne-am fi asteptat : Table Spool (Eager Spool). Desi acesta prezenta un cost I/O foarte mic, in realitate acesta era mult mai mare deoarece aceste rezultate temporare nu incapeau in memorie si trebuiau scrise pe disc.

Recreand functia cu optiunea SCHEMABINDING (dupa ce am gasit postul http://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx) a disparut acest operator si timpul de executie a scazut cu 30%. Prezint mai jos scenariul de test si explicarea acestui comportament.

Scenariu:

1. Cream tabela de test:

CREATE TABLE [dbo].[test](

[codloc] int primary key,

[codjud] [char](2) NULL,

[tip] [char](1) NULL,

[localit] [varchar](25) NULL

) ON [PRIMARY]

2. Umplem tabela test cu 2^20 linii in 20 pasi, fiecare dubland numarul de linii:

insert into test values(1, 'AB', 'O', 'Alba Iulia')

declare @step int, @maxkey int

set @step = 1

while @step <= 19

begin

select @maxkey = max(codloc) from test

insert into test

select codloc + @maxkey, codjud, tip, localit from test

set @step = @step + 1

end

3. Cream functia user-defined pe care o vom folosi ca exemplu:

create function MyUDF

(

@str varchar(25)

)

returns varchar(50)

begin

return reverse(@str) + @str

end

4. Folosind functia definita pentru a filtra datele :

declare @t table (codloc int)

insert into @t

select codloc from test where dbo.MyUDF(localit) like '%z%'

obtinem planul de executie (ce are costul total estimat de 8.95):

Deoarece am creat UDF-ul fara SCHEMABINDING database engine-ul seteaza intern proprietatea USERDATAACCESS pe 1 chiar daca functia noastra nu acceseaza direct tabele. Pentru ca aceasta proprietate este 1, pentru orice statement de insert/delete/update database engine-ul stocheaza temporar informatii de localizare si rezultatul functiei inainte de a face orice modificare pe date. Daca functia noastra ar fi accesat date din tabela modificata atunci rezultatul sau putea fi afectat de modificarile precedente.

5. Schimbam definitia functiei astfel incat sa includa SCHEMABINDING

alter function MyUDF

(

@str varchar(25)

)

returns varchar(50)

with schemabinding

begin

return reverse(@str) + @str

end

In momentul executiei engine-ul de optimizare stie ca functia noastra nu foloseste direct date din tabele. Astfel planul de executie pentru select-ul nostru este:

Desi planul are un cost estimat de 10.2 el ruleaza intr-un timp cu 30% mai mic.

Isolation level read commited - linii duplicate

Daniel Bitin — Wed, 22 Aug 2007 13:18:27 GMT

Isolation level read commited - linii duplicate

Urmatorul post este inspirat de postul lui Lubor Kollar “Previously committed rows might be missed if NOLOCK hint is used” (http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx). In acesta autorul discuta despre lipsa in rezultatele unui select cu nolock a unor linii commit-ate anterior.

Voi discuta astazi tot despre niste rezultate surprinzatoare, de data aceasta pentru un select ce ruleaza in isolation level read commited. Scenariul pe care il propun consta in folosirea a 2 conexiuni:

1. Una ce foloseste isolation level read commited si executa in cadrul unei tranzactii un select pe o tabela mare (>500k linii) organizata ca un index clusterat.

2. A doua ce foloseste tot isolation level read commited si, in cadrul unei tranzactii executate in timpul selectului, muta liniile in btree din frunzele cele mai din stanga catre frunze aflate la capatul din drepta.

Rezultatul selectului de pe conexiunea 1 este surprinzator prin faptul ca liniile mutate vor fi luate in considerare de 2 ori. Surprinderea este data de faptul ca, in credinta populara, in cadrul isolation level read commited lockurile shared luate pentru citirea tabelei sunt pastrate pana la sfarsitul statement-ului select. Aceasta credinta e generata probabil si de Books Online unde citim:

“If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared locks are released when the statement completes.”

Ce se intampla de fapt, pe timpul rularii statement-ului select, database engine-ul proceseaza paginile de pe ultimul nivel din btree achizitionand lock-uri pe 1 pagina, maxim 2 si renuntand la ele odata ce procesarea paginii s-a incheiat. El previne astfel dirty read-uri insa sufera de nonreapeatable reads in scenariul descris. Un nivel de izolare repeatable read sau mai mare rezolva aceasta problema deoarece pastreaza lockurile (in exemplul nostru luat direct la nivel de tabela si nu de pagini) pana la sfarsitul tranzactiei.

Trebuie sa intelegem insa: comportamentul observat, desi neasteptat, este normal pentru nivelul de izolare ales. Sql Server achizitioneaza lockuri la un nivel cat mai mic si renunta la ele cat mai devreme pentru a mari concurenta, in acelasi timp respectand functionalitatea descrisa in Books Online unde nonreapeatable reads este un side-effect al nivelului de izolare read commited. In cazul in care suntem afectati de asemeneaza efecte in cadrul unei aplicatii trebuie sa ridicam nivelul de izolare pentru ca acest tip de probleme sa dispara.

Scenariu:

1. Se creaza tabela organizata ca si index clusterat (campul codloc asignat ca si primary key duce la crearea unui index clusterat unique pe acesta).

CREATE TABLE [dbo].[test](

[codloc] int primary key,

[codjud] [char](2) NULL,

[tip] [char](1) NULL,

[localit] [varchar](25) NULL

) ON [PRIMARY]

2. Umplem tabela test cu 2^19 linii in 19 pasi, fiecare dubland numarul de linii:

insert into test values(1, 'AB', 'O', 'Alba Iulia')

declare @step int, @maxkey int

set @step = 1

while @step <= 19

begin

select @maxkey = max(codloc) from test

insert into test

select codloc + @maxkey, codjud, tip, localit from test

set @step = @step + 1

end

3. Pe aceeasi conexiune construim selectul care va dura mult. Deoarece numarul de linii folosite in exemplu nu este foarte mare, aceasta va contine multe functii matematice care incearca sa incetineasca procesarea. De asemenea incercam sa nu duplicam functiile deoarece vor fi evaluate o singura data.

set isolation level read committed

begin tran

select count(0) cnt,

max(sqrt(ascii(codjud)+1)),

max(sqrt(ascii(codjud)+2)),

max(sqrt(ascii(codjud)+3)),

max(sqrt(ascii(codjud)+4)),

max(log10(exp(ascii(codjud)+1))),

max(log10(exp(ascii(codjud)+2))),

max(log10(exp(ascii(codjud)+3))),

max(log10(exp(ascii(codjud)+4))),

max(cos(ascii(localit)+1)),

max(cos(ascii(localit)+2)),

max(cos(ascii(localit)+3)),

max(cos(ascii(localit)+4)),

max(sin(exp(ascii(localit)+1))),

max(sin(exp(ascii(localit)+2))),

max(sin(exp(ascii(localit)+3))),

max(sin(exp(ascii(localit)+4)))

from test

commit tran

Din rezultatele selectului ne intereseaza cnt care va fi 524288.

cnt

------

524288

4. Deschidem o conexiune noua in care rulam de mai multe ori sp_lock in timp ce selectul de mai sus ruleaza. Observam faptul ca de fiecare data avem un lock S pe o singura pagina in afara de cel IS pe tabela:

spid dbid ObjId IndId Type Resource Mode Status

------ ------ ----------- ------ ---- -------------------------------- -------- ------

62 7 565577053 0 TAB IS GRANT

62 7 565577053 1 PAG 1:1741 S GRANT

5. Deschidem o noua conexiune pe care vom rula codul:

set isolation level read committed

begin tran

update test set codloc = 1000000 + codloc where codloc < 1000

commit tran

Rezultatul este:

(999 row(s) affected)

6. Rulam selectul de pe conexiunea descrisa la punctul 3. In timp ce ruleaza pornim si update-ul de la punctul 5. In rezultatul selectului vedem:

cnt

------

525287

Numarul de linii din [test] nu s-a schimbat (nu au avut loc insert-uri sau delete-uri). Totusi obtinem o valoare pentru [cnt] mai mare cu 999, ceea ce inseamna ca liniile mutate prin update au fost luat in considerare de doua ori.

Linked server Excel 2007

Daniel Bitin — Wed, 22 Aug 2007 13:17:28 GMT

Linked server Excel 2007

Cand a trebuit sa folosesc un fisier Excel 2007 (.xlsx) ca si sursa de date in Sql Server am avut o mica surpriza. Noul format 2007 nu mai este citibil folosind [Microsoft Jet 4.0 OLE DB Provider] si trebuie folosit providerul [Microsoft Office 12.0 Access Database Engine OLE DB Provider].

Configurarea este identica, trebuind completat [Product name] cu o valoare nevida si [Provider string] = ‘Excel 12.0’ ([Provider string] ajunge in [Extended Properties] pe provider).

xp_cmdshell – spawn cu credentiale la alegere ?

Daniel Bitin — Wed, 22 Aug 2007 13:16:56 GMT

xp_cmdshell – spawn cu credentiale la alegere ?

Zilele trecute am fost sunat de un client ce avea probleme la accesarea din cod TSQL a datelor din niste fisiere Excel aflate intr-un share pe o masina din domeniu.

Persoana in cauza face parte din departamentul de raportare al unei banci si taskul pe care il avea de terminat consta in preluarea datelor dintr-un fisier Excel aflat pe un share, procesarea lor in corelatie cu datele dintr-o baza de date Sql Server si scrierea altui fisier Excel pe alt share. Acest fisier destinatie trebuia generat pe un schedule sau la comanda.

Deoarece procesarea datelor dupa preluarea din fisierul Excel sursa dureaza un timp considerabil, solutia pe care as fi propus-o la inceperea unui asemenea task consta in crearea unui job Sql Server ce ar fi folosit Integration Services in primul pas (preluarea datelor din Excel) si in ultimul (scrierea rezultatelor). Pe ambii pasi puteam seta credentiale Windows de rulare, credentiale ce se foloseau la accesarea share-urilor cu fisierele sursa si detinatie.

Am fost sunat insa catre terminarea taskului, atunci cand implementarea era deja facuta folosind exclusiv TSQL, cod ce accesa sursa si destinatia Excel folosind OPENQUERY. Asa cum am discutat intr-un alt post, folosirea unor linked servers si a maparilor de login-uri remote nu ne ajutau deoarece aceste login-uri remote nu sunt folosite ca si credentiale Windows pentru accesarea share-urilor. Pentru a functiona abordarea cu OPENQUERY, clientul a redus problema la accesarea fisierelor XLS aflate pe masina cu instanta de Sql Server, dupa ce au fost copiate folosind xp_cmdshell de pe share-ul aflat pe alta masina.

In acest punct insa rezultatul operatiei de copiere era constant “Access denied”. O privire in Books Online ne spune ca, daca user-ul ce ruleaza xp_cmdshell este membru al role-ului sysadmin (iar cel ce testa acest lucru se autentifica in Sql Server cu user-ul de domeniu ce era in Administrators pe masina cu instanta Sql Server) procesul cmd.exe spawn-at de Sql Server rula cu credentialele serviciului Sql Server (in cazul nostru Local System Account, adica SYSTEM). Am verificat repede acest lucru ruland in contextul sau de securitate:

exec xp_cmdshell 'copy con'

Copy con va astepta input de pe stdin iar acesta nu va veni niciodata, lucru ce ne permite observarea aparitiei procesului cmd.exe ce ruleaza ca SYSTEM in Task Manager. Terminand fortat acest proces va conduce la terminarea statementului exec xp_cmdshell.

Tot din Books Online aflam faptul ca atunci cand userul ce ruleaza xp_cmdshell nu e membru sysadmin atunci cmd.exe va fi spawn-at folosind credentialele ##xp_cmdshell_proxy_account##. Am creat aceste credentiale folosind sp_xp_cmdshell_proxy_account si am testat acest lucru folosind un login Sql Server pe care l-am creat. Ne-am logat cu noul user si am dat de prima neconcordanta in Books Online unde citim ce pentru a rula xp_cmdshell trebuie sa avem permisiune CONTROL SERVER. Lucru ce nu este adevarat, maparea login-ului pe master si acordarea permisiunii EXECUTE pe master.sys.xp_cmdshell fiind suficiente pentru a rula acest extended stored procedure. Am putut observa apoi procesul cmd.exe spawn-at cu credentialele din ##xp_cmdshell_proxy_account##.

Intrebarea pe care ne-am pus-o a fost, ce este cu aceasta permisiune CONTROL SERVER legata de xp_cmdshell. Raspunsul pe care l-am aflat contrazicea din nou Books Online : acordarea acestei permisiuni user-ului ce ruleaza xp_cmdshell este suficienta (nu trebuie sa fie sysadmin) pentru ca spawn-area cmd.exe sa se faca folosind account-ului sub care ruleaza instanta de Sql Server. Cine nu are aceasta permisiune isi va rula comanda shell folosind credentialele ##xp_cmdshell_proxy_account##.

Workaround-ul gasit de noi pentru a putea rula operatia de copiere folosind credentialele configurabile ( ##xp_cmdshell_proxy_account##) si pentru useri din sysadmin a fost schimbarea contextului de securitate (catre un login cu drept de execute pe xp_cmdshell dar fara CONTROL SERVER permission) inainte de executia comenzii shell si apoi revenirea la vechiul context dupa terminarea acesteia:

execute as login = 'newlogin';

exec xp_cmdshell 'copy con'

revert;

Aceasta mi se pare o limitare fantastica. Pentru userii fara aceasta permisiune exista o modalitate de configurare a credentialelor sub care va rula cmd.exe iar pentru ceilalti nu. Sunt sigur ca aceasta schema de securitate nu a fost creata avand in minte rularea programatica a comenzilor shell folosind credentiale configurabile, insa rezultatul este departe de a fi unul natural, la care te-ai astepta.

Securitate linked servers

Daniel Bitin — Wed, 22 Aug 2007 13:15:17 GMT

Securitate linked servers

Multe neintelegeri am vazut legate de securitatea pe linked servere. Zilele trecute un client incerca sa acceseze datele dintr-un fisier Excel aflat pe un share in domeniu folosind credentialele altui user Windows decat cel cu care era logat.

In tab-ul Security din proprietatile unui linked server se poate configura ca pentru un login ce il acceseaza (fie ca este in lista de mapare de sus, fie in “restul”) sa:

1. Impersoneze acel login inainte de a accesa sursa de date. Aici avem 2 cazuri:

a. Login-ul este Windows -> Inainte de a se conecta la sursa de date contextul de securitate pe firul de executie va fi setat la acest login. Va face apoi conectarea ca si cum noi ne-am conecta la o instanta Sql Server folosind SSMS si alegand Windows Authentication din drop-down list-ul Authentication. Aceasta va functiona deoarece procesul SSMS ruleaza sub contextul nostru de securitate cand il pornim.

b. Login-ul este Sql Server -> Daca impersonam va functiona doar conectarea catre o alta instanta de Sql Server ce are definit un login Sql Server cu acelasi username si parola.

2. Sa se conecteze la sursa de date folosind o pereche username/password. Acestea nu pot fi credentiale Windows (sau mai bine spus nu vor fi tratate ca si credentiale Windows). Sql Server nu va impersona user-ul dat de aceste credentiale ci le va folosi in stringul de conectare pasat provider-ului. Credentialele trebuie sa aibe relevanta pentru sursa de date si nu pentru Windows. Daca sursa de date este Sql Server, credentialele trebuie sa reprezinte un login Sql Server, daca sursa de date este Excel atunci ele trebuie sa poata fi folosite de providerul Jet (singura combinatie de username/password care functioneaza ptr. Jet este admin/<empty>).

3. Sa nu trimita credentiale catre sursa de date. Daca sursa de date este Sql Server aceasta optiune nu va functiona. Daca sursa de date este de exemplu Excel iar login-ul ce incearca conectare este de tip Windows atunci se va accesa fisierul xls tot cu credentialele login-ului dar nu va trimite credentiale catre providerul Jet (in Extended properties : “User ID=admin;Password=;”).

Ce este important de retinut aici este faptul ca oriunde in tab-ul Security putem introduce username/password nu putem introduce credentiale de Windows. Pentru a accesa linked server-ul cu alte credentiale decat cele “curente” va trebui sa modificam credentialele curente inainte de a accesa linked server-ul in cod TSQL. Putem face acest lucru la un nivel superior, de ex:

- Includem codul ce acceseaza linked serverul intr-un job step pe care putem seta credentiale;

- Schimbam contextul de securitate in cod .NET de exemplu, ne conectam la baza de date folosind ADO.NET si Integrated Security=SSPI si accesam linked server-ul;

- Schimbam credentialele pe data source in Reporting Services la niste credentiale Windows si bifam “Use as Windows credentials when connecting to the data source”

Odata ajunsi in codul TSQL nu vom putea face acest lucru, nici chiar folosirea “execute as login=”nu va duce la o authentificare cu succes pe sursa de date.