ceturtdiena, 2012. gada 23. augusts

Norādi shēmas nosaukumu!

Viena no "labās prakses" rekomendācijām rakstā SQL Server labā prakse ir "Shēmas nosaukumu vienmēr rakstīt pirms objekta nosaukuma". Saskāros ar situāciju, kur šī punkta neievērošana rada ātrdarbības problēmas. Tādēļ vēlos pamatot rekomendācijas ievērošanu, aprakstīt un ar piemēru ilustrēt "slikto scenāriju", kas var rasties neievērojot šo rekomendāciju.

Ko nozīmē "norādīt shēmas nosaukumu"?
SQL Server (sākot no 2005) datu bāzēs visi objekti pieder shēmām. Manā praksē visbiežāk redzēts, ka visi datu bāzes objektu pieder shēmai "dbo". Rakstot vaicājumus nav obligāta prasība rakstīt shēmas + objekta nosaukumu. Var rakstīt tikai objekta (piemēram, tabulas) nosaukumu. Taču labā prakse ir vaicājumā norādīt pilnu nosaukumu:
SELECT col1, col2, col3
FROM dbo.MyTable
;
Rekomendācijas pamatojums
Rekomendācijai ir vismaz divi pamatojumi:
  • Objekta nosaukums ir unikāls shēmas ietvaros, tādēļ shēmas nenorādīšana ir mulsinoša, un var rasties sintaktiski pareizi vaicājumi, kas strādā nepareizi. (piemēram, datu bāzē var būt vairāki objekti ar vienādu nosaukumu, bet dažādām shēmām- tabula "MyTable" var atrasties gan shēmā "sh1", gan arī shēmā "sh2", gan arī shēmā "dbo". Ko šādā gadījumā atgriež vaicājums "Select * From MyTable"? Protams, likumi nosaka, kuras shēmas objekts tiks izmantots katrā no situācijām, bet šāds pieraksts nemaz nav lasāms)
  • Ātrdarbības nolūkos vaicājumiem tiek saglabāti izpildes plāni. Objekta neatrašanās lietotāja noklusētajā shēmā apgrūtina vaicājuma izpildes plāna atkārtotu izmantošanu.
    (raksta turpinājums ir šī dziļāks apskats)
Slikts scenārijs
Šajā sadaļā ilustrēšu ātrdarbības problēmas izmantojot piemēru. Ir visai daudz priekšnosacījumi kam jāizpildās, lai rastos slikts scenārijs.
Situācijas priekšnosacījumi:
  • Datu bāzei slēdzas klāt daudzi dažādi lietotāji (Tas ierobežo problemātisko situāciju skaitu, jo bieži lietojumi izmanto ierobežotu skaitu lietotāju, kas slēdzas pie datu bāzes. Piemēram, izmanto vienu lietotāju visiem WEB servisa pieprasījumiem. Tādā gadījumā lietotāja tiesības kontrolē WEB servisā. Arī gadījumā, ja SQL Server lietotājs ir domēna lietotāja grupa, tiek ierobežota iespēja rasties 'sliktajai' situācijai).
  • Lietotājiem tiek izmantos dažādas noklusētās shēmas. Ja tas ir apzināti, tad parasti tas problēmas nerada. Bet neapzināti šāda situācija var rasties vismaz divu iemeslu dēļ:
    • Datu bāze ir migrējusi no SQL Server 2000 versijas uz jaunāku
    • Lietotāju pievienošanai datu bāzē tiek izmantota procedūra "sp_adduser". Šīs procedūras izmantošanas iemesls visbiežāk ir tāds, ka līdz SQL Server 2005 tas bija veids, kā pievienot lietotājus. Sākot ar SQL Server 2005 ir jauna sintakse "Create User ...". (vēl var interesēt: SQL Server novecojusī funkcionalitāte).
Ātrdarbības problēmu iemesli:
SQL Server izmanto vaicājumu izpildes plānus. Vaicājumu izpildes plānā tiek "izdomāts", kā tiks izpildīts attiecīgais vaicājums- kuri datu bāzes objekti tiks izmantoti. Ja vaicājumā netiek norādīts pilns objekta nosaukums, tad izpildes plāns ir viennozīmīgs tikai tam lietotājam, kas izpilda šo vaicājumu. Ja to pašu vaicājumu izpilda cits lietotājs, tad tam var būt jāizmanto citi objekti (piemēram, citas tabulas) tā paša vaicājuma izpildei. Tātad:
  • Lietotājam, kas izmanto citu noklusēto shēmu, jāveido cits izpildes plāns. Tas nozīmē- papildus resursu patēriņš.
  • Pirms jauna izpildes plāna veidošanas, tiek pārbaudīta atmiņa, vai tajā jau nav plāns vaicājuma izpildei. Jo vairāk atmiņas jāpārmeklē, jo vairāk darba jāveic. Jo vairāk atmiņas aizņem izpildes plāni, jo mazāk atmiņas paliek datiem.
Skripts:
(Skripts ir ilustrācijas nolūkos, gadījumā, ja vēlme pārbaudīt teoriju praksē. Tā kā skriptā tiek izmantots DBCC FREEPROCCACHE, kas iztīra visu saglabāto izpildes plānu atmiņu, tad ar šo nevajag darboties uz produkcijas instances)
/* ================ nepieciešamā sagatavošana ================ */
use master;
go
create database mydbTest;
go
Create Login RG_Test_1 With Password = 'VeryStrongPWD';
Create Login RG_Test_2 With Password = 'VeryStrongPWD';
go
use mydbTest;
-- sp_adduser izveido lietotāju un tam atbilstošo shēmu!
exec sp_adduser 'RG_Test_1';
exec sp_adduser 'RG_Test_2';
exec sp_addrolemember 'db_datareader', 'RG_Test_1';
exec sp_addrolemember 'db_datareader', 'RG_Test_2';
go
create table dbo.table_plan_test
(
    i int
)
go
-- lai īsāks piemērs, procedūra, kas atlasa interesējošos datu no
-- izpildes plānu atmiņas:
create proc dbo.show_mytestdb_proc_cache
as
begin
    select ps.execution_count, ps.creation_time, ps.last_execution_time,
        Cast('<?SQL ' +
         SUBSTRING(st.text, (ps.statement_start_offset/2) + 1,
        ((CASE ps.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE ps.statement_end_offset END
                - ps.statement_start_offset)/2) + 1) + ' ?>' as xml) query_text,
         Cast('<?SQL ' + st.text + ' ?>' as xml) full_text,
        -- izpildes plāns (klikšķinam virsū, lai grafiski parādītos)
        pl.query_plan
    from sys.dm_exec_query_stats ps
      Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
      Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
    where st.text like '%table_plan_test%'
    option (recompile)
end
go

/* ================ SLIKTI: bez shēmas nosaukuma ================ */
-- šo pašu var simulēt izveidojot divas konekcijas pie sql server ar
-- attiecīgajiem lietotājime un izpildot tikai datu atlasi no attiecīgās tabulas.
--
-- jāņem vērā, ka vaicājums tiek uzskatīts par identisku tikai tad,
-- ja tas ir identisks (lielie/mazie burti, tukšumi utt).
exec ('select * from table_plan_test') as login = 'RG_Test_1'
exec ('select * from table_plan_test') as login = 'RG_Test_2'
go
-- 2 izpildes plāni, lai arī vaicājumi identiski!
exec dbo.show_mytestdb_proc_cache
DBCC FREEPROCCACHE -- iztīra plānu atmiņu
go

/* ================ LABI: izmantojot shēmas nosaukumu ================ */
exec ('select * from dbo.table_plan_test') as login = 'RG_Test_1'
exec ('select * from dbo.table_plan_test') as login = 'RG_Test_2'
go
-- tikai 1 izpildes plāns, 2x izmantots:
exec dbo.show_mytestdb_proc_cache
DBCC FREEPROCCACHE

go
/* ================ RISINĀJUMS ================ */
-- obligātā daļa (nomaina noklusēto shēmu):
alter user RG_Test_1 with default_schema = dbo;
alter user RG_Test_2 with default_schema = dbo;
-- ja shēma lieka (un te tā ir lieka):
drop schema RG_Test_1;
drop schema RG_Test_2;
go
exec ('select * from table_plan_test') as login = 'RG_Test_1'
exec ('select * from table_plan_test') as login = 'RG_Test_2'
go
exec dbo.show_mytestdb_proc_cache
go


/* ================ CLEAN UP ================ */
use master
drop database mydbTest;
Drop Login RG_Test_1
Drop Login RG_Test_2
Beigas
Noslēgumam divas piebildes:
* Par izpildes plāniem rakstā izmantotā procedūra "dbo.show_mytestdb_proc_cache" veidota izmantojot Resursu prasīgākie vaicājumi, procedūras, trigeri uzrakstīto vaicājumu.
* Ātrdarbības problēmas summējas (vai reizinās), reti kad tām ir tikai viens cēlonis. Manuprāt, nedz šī, nedz arī jebkura cita rekomendācija neizglābs pasauli. Bet jo vairāk šādas lietas palaiž garām, jo sliktāks ir gala rezultāts.

Nav komentāru:

Komentāra publicēšana