T-SQL - Kurs 2
Post date: Dec 10, 2010 8:13:28 AM
Detta är endast mina kommentarer från en sqlkurs.
-- om man slår på implicita transactioner så kommer varje ny statement skapa en transaktion, som måste commitas eller rollbackas.
set implicit_transactions on
-- om man kör en transaction över tex en delete sp kommer tabellen att låsas.
-- lås låses "tills vidare" om man inte anger en timeout
set lock_timeout 5000
set lock_timeout -1 -- = default = oändlig väntan
-- normalt så håller ett lås tills clienten timar ut och släpper connection.
-- om en stor process sätter igång som låser en tabell under produktionstid så kommer användarna snart att ringa.
-- När man då dödar processen som låst tabellerna så kan det ta tid innan alla lås är upplåsta.
-- Om processen har varit igång i en kvart så kan det ta en kvart innan alla låst är upplåsta igen.
kill 55 -- process/session id
-- statesments är implicita transactioner i sig själv, så ett statement som avbryts kan aldrig lämna ett halvdant arbete.
-- select skapar shared lock
-- uppdateringar skapar exlusive locks
-- INTENT Exclusive är en markering när det är saker under i kjedjan som är låsta. tex en nyckel vid update. Detta för att förhindra att ingen tar ett lås på en nivå uppåt. tex om man har låst en rad så sätts intent lock på page och table så att ingen annan kan komma o låsa hela sidan eller tabellen.
-- dessa två locks är inte kompatibla
-- sql server låser på row, page eller table. Man kan speca hur man vill ha låset om man vet exakt. Annars kommer sql server välja själv.
-- den försöker låsa rad, men om det är för många rader så kommer den eskalera och låsa hela sidan, och sedan eskalera för att låsa hela tabellen.
-- man kan manuellt ange Update lock om man vet att man senare i sin transaction kommer att uppdatera något. Då kan ingen annan göra update på den resursen innan låset är släppt.
select * from table1 with (updlock)
-- Readpast är att man istället för att lära dirty data så hoppar man över sådana rader. "Hoppa förbi alla låsta saker, läs bara orörda saker"
select * from table2 with (readpast)
-- kontrollera vilka locks som finns just nu
select * from sys.dm_tran_locks
-- key lock innebär att raden är låst.
dbcc inputbuffer(sessionsid) -- visar senaste aktiva query för sessionen
-- finns nyare sätt att få ut detta (se demofilerna)
transaction isolation level xxx -- säter p viss transactin isolation level för sessionen.
-- read commited = vi läser bara saker som inte är exklusivt låst
-- read uncommited = vi kan läsa det uppdaterade värdet även om det inte är commitat
with nolock -- = samma sak som read uncommited
--
select * from table1 with (readuncommitted)
-- när ska man välja read uncommited?
-- När man är optimist och man inte behöver stå till svars för att läsa data som kanske inte kommer gäller.
-- vid OLAP så kan man sätta data i read only så kan man köra read uncommited.
-- när man kör read uncommited så tas inga lås, inte ens shared locks.
-- Man läser rått ur buffer cache, man kan få massa konstiga fel. tex dubbla nycklar
-- repeatable read
-- då tar vi lås när vi läser på ett annat sätt.
-- Inom en transaction så kommer vi kunna läsa samma värde ur en tabell även om vi frågar flera gånger.
-- Det sätts ett lås på det vi läser som hänger kvar. Vid normal(read commited) så tas endast låset tills datat är läst.
-- serializable
-- Då låses med Range Schema locks. Även Värden runt de värden som vi låst låses.
-- --------------------------------------------------------------------------------------------------------------
--sql clr när man konfar och lägger till en assemlby kan man speca lite säkerhetsgrejjer som skall gälla.
create assembly clrBasics .... with permission_Set = trustworthy
--set trustworthy on -- saker får gå utanför databasen, men bara på databasservern.
--set unsafe on
--set safe on -- saker för endast köras i databasen. inte läsa filer eller annat
--set external_access on-- saker får hämtas från vad som helst
-- --------------------------------------------------------------------------------------------------------------
-- MINIMAL LOGGING-- måste ha recovery mode bulk load
-- bulk load
-- insert select -- kan vara minimal logged i sqlserver 2008
-- select into
-- --------------------------------------------------------------------------------------------------------------
-- CHANGE TRACKING
-- kan göras med extended events
-- sql server audit
-- change tracking -- captures data that is changed but does not capture the changed data.. Fångar händelsen att data förändras.
-- chage data capture -- captures changes with the data
-- CHANGE TRACKING
select change_tracking_current_version() -- denna läggs på de tabeoller som är Change tracking.
-- det ligger då kvar ett par versioner, dessa gamla versioner städas bort av ett annat bakgrundsjobb.
-- alla gamla versioner går att ta fram.
-- Det går att se för varje händelse vilken typ av operation det var som påverkade raden (insert update delete).
-- Hur länge versioner skall ligga kvar stälelr man inte med retention tid. Detta görs via konfigurationen.
-- Change tracking är tänkt att användas
-- CHANGE DATA CAPTURE
-- enablas per databasnivå
-- då skapas en user som hanterar change data capture. En monitor kommer att hantera change data catureingen
-- Det skapas triggrar i bakgrunden som hanterar inser update delete drop
-- sedan anger man vilka tabeller som cdc skall användas på
-- exec sys.sp_cdc_enable_table .... -- sql agent måste vara igång, det är den som kommer utföra jobbet.
-- används för att montitorera förändringar på sina tabeller.
--
--alter database demo set auto update statistics on --
--alter database demo set auto update statistics_async on -- optimizern stannar och väntar på att statestiken är tillräckligt bra för att fortsätta
-- för att statistken skall förändras så måste 500 rader + 20% förändras i en tabell.
-- om index är väldigt fragmenterad så kan uppdaterad statistik bli dålig
-- access methods
-- allocation scan = om man inte behöver ett sorterat resutlat så kan IAM användas för att hitta pages som ingår
-- partial scans = om den går in via indexet hittar första värdet ir angen och går sedan framåt tills sista värdet hittas. (range scan)
-- optimering för att inte få massa oanvändbara planer i cachen
exec sp_configure 'optimize for ad hoc workloads', 1;
fast number rows
-- ta fram första svaret fort
select .... option (hash group, fast 10)
-- det kan ligga en plan för en stored procedure som är anpassad för ett visst värde för en parameter som normalt inte kommer att användas.
-- optimizern kommer att köra på den planen som ligger i cachen och det kan vara helt galet.
-- med optimize for så kan man säga till optimizern att
optimize for (@variableName = literan_Constant)
-- RECOMPILE
-- man kan speca att en specifik fråga innuti en stored procedure skall vara forced recompile.
-- ROBUST PLAN
-- säg till obptimizern att anpassa sig för maximala radstorlekar
-- kolla hur databasen upplever statestiken kring en kolumn i en tabell
dbcc show_statistics('dbo.tabellen', 'columnen')
-- När ska man använda indexerade vyer?
-- På data som sällan ändras men där svaret måste gå snabbt att få fram.
-- endast praktiskt om detär massa joins i vyn, annars fungerar ett vanligt index med inlcuded columns.
-- man kan göra en beräknad column i en tabell persisted ifall man behöver göra ett index på columnen
-- inline table valued function expanderas in i den yttre selectfrågan och tas sedan med i optimizern helt och hållet. På så vis så undviks hopp som vanliga funktioner skapar.
-- för att en funktion skall vara inline så få det bara finnas ett statement, och funktionen måste vara markerad med RETURNS TABLE
-- om en funktion är markerad som deterministisk av sql server så kan resultatet cachas och återanvändas om input är samma vid nästkommande anrop
-- förutsättning för att få deterministik funktion är att funktionen är skapas med schema binding