Archive for the ‘MSSQL’ Category

MSSQL: aeglaste päringute tuvastamine

juuni 8, 2016

Vahel on palju tööd, et leida päringud, mis SQL serverit mõttetult koormavad, SQL trace pole ka alati parim abivahend.
Antud päring aitab aeglaselt jooksvad päringud tuvastada.

SELECT TOP 50
[Average CPU used] = total_worker_time / qs.execution_count,
[Total CPU used] = total_worker_time,
[Execution count] = qs.execution_count,
[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END –
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

Advertisements

MSSQL: no-break space ja trim funktsioon

mai 31, 2015

Nii üle pika astusin ämbisse, eeldasin et MS SQL LTRIM / RTRIM suudab ka “no break space” tüüpi tühikud eemaldada (koodiga U+00A0). Tõde see, et ei eemalda…

DECLARE @p NVARCHAR(255)
SET @p = REPLICATE(CHAR(160),16) + 'Tekst' + REPLICATE(CHAR(160),16)
SET @p = RTRIM(LTRIM(@p))
SELECT @p

Antud juhul aitab vaid replace :)

SELECT REPLACE(@p,CHAR(160),'')

MSSQL: krüpteerida kõik andmebaasi protseduurid

oktoober 17, 2013

Lihtsalt ükspäev oli vaja krüpteerida ära ühel andmebaasil kõik SQL serveri protseduurid ja Googlest ei leidnud sellel hetkel mulle sobivat koodilõiku. Tegin ühe lihtsa variandi.

PS enne protseduuride krüpteerimist tehke protseduuride lähtekoodidest varukoopia.

Ka küsige endalt, kas Te teate, mida teete…kui ei tea…ärge tehke !

Parsimist võite täiustada, ma ei hakanud seal palju aega raiskama st
osasid protseduure ei pruugi ta krüpteerida.


SET NOCOUNT ON

DECLARE @cr cursor
DECLARE @cr_merge cursor
DECLARE @procname varchar(255)
DECLARE @pline nvarchar(2096)
DECLARE @tmp TABLE (line nvarchar(2096))
DECLARE @proccont nvarchar(max)
DECLARE @indx1 int
DECLARE @indx2 int

SET @cr = CURSOR LOCAL SCROLL FOR
SELECT name
FROM dbo.sysobjects
WHERE (type = 'P')
AND LEFT(name,3) NOT IN ('dt_','sp_','xp_','ms_')

OPEN @cr

FETCH NEXT FROM @cr INTO @procname

WHILE @@FETCH_STATUS=0
BEGIN

SET @proccont=''
SET @pline=''
DELETE FROM @tmp

INSERT INTO @tmp(line)
EXEC sp_helptext @procname

SET @cr_merge = CURSOR LOCAL SCROLL FOR SELECT line FROM @tmp

OPEN @cr_merge
FETCH NEXT FROM @cr_merge INTO @pline

WHILE @@FETCH_STATUS=0
BEGIN
SET @proccont=@proccont+ @pline
FETCH NEXT FROM @cr_merge INTO @pline
END

IF (CHARINDEX('is encrypted.',@proccont)=0)
BEGIN
-- TODO natuke intelligentsem parsimine; hetkel dummy
-- SET @indx1 = CHARINDEX('AS',@proccont);
SET @indx1 = CHARINDEX('BEGIN',@proccont);
SET @indx2 = CHARINDEX('WITH',@proccont);
IF (@indx2>0) and (@indx1>@indx2)
BEGIN
SET @proccont = STUFF(@proccont,@indx2,4,'WITH encryption, ')
END ELSE
IF (@indx1>0)
BEGIN
SET @proccont = STUFF(@proccont,@indx1-4,0,'WITH encryption ')

END

SET @proccont = REPLACE(@proccont,'CREATE PROCEDURE','ALTER PROCEDURE')
SET @proccont = REPLACE(@proccont,'CREATE PROC','ALTER PROC')

IF (@indx1>0) or (@indx2>0)
BEGIN
EXEC (@proccont)
SELECT 'Krüpteerisin ',@procname

select substring(@proccont,0,300)
END
END

-- select @proccont
-- break

CLOSE @cr_merge
DEALLOCATE @cr_merge

FETCH NEXT FROM @cr INTO @procname
END

CLOSE @cr
DEALLOCATE @cr

MSSQL: Logshipping abiline…

juuli 22, 2013

Eks me kõik teinud tarkvarasid, kus klient “ära kadunud”, aga miks siis seda tarkvara endale hoida. Jagan välja ühe enda logshippingut teostava abimooduli.

Programm asub siin Kui miskit segaseks jääb võite küsida ja mulle paki komme saata 😉

Üldiselt MSSQL puhul on 3 failsafe võimalust hotbackup isegi oleks vale sõna.
* cluster
* mirroring
* logshipping

Ei hakka neid detailsemalt selgitama, aga üldiselt logshippingu puhul on väike andmekadu võimalik, samas need baasid pidevalt stand by reziimid. St suuri päringuid saab jooksutada hoopis backup serveri peal, mitte ei pea põhiserverit koormama. Mirroringi puhul on backup serveri andmebaasid restore modes ja enamasti üle 10 andmebaasi pole enam mirroring stabiilne.

Miks programm parem, esiteks lihtsamalt konfitav, teiseks logshippingut hakkab teostama webservice. Kui midagi väga viltu läheb ning sql server kukub täiesti maha, siis service ikka saadab e-kirja teile.

Kasvõi 50+ andmebaasi…pole probleemi.
— Järgnevalt mõned õpetussõnad

Esmalt kui logshippingu automaatse süsteemi käivitate(!!),
peate mssql backup masinasse (ehk sql server, mis võtab töö üle, kui põhimasin kukkus maha)
tegema kõikidest andmebaasidest full restore (standby reziimis). See on ühekordne protsess, mis tuleb administraatoril endal ära teha

üks näide;
RESTORE DATABASE b
FROM DISK = N’C:\logshipping1\b_backup.bak’
WITH STANDBY = N’C:\secondarycpy\undo_b.DAT’,
MOVE N’b’ TO N’C:\secondarycpy\data\b_data.MDF’,

MOVE N’b_log’ TO N’C:\secondarycpy\data\b_log.LDF’,
NOUNLOAD, REPLACE, STATS = 10

St. põhimasin ja sealne SQL server peab saama logida Windows Authentication abil masinasse, mille peale tehakse logshippingut ja vastupidi.
masin kus koopiat jooksutatakse see masin peab saama logida Windows Authentication abil live masina SQL serverisse.

Seaded:

>Failid luua serveris lokaalsesse kataloogi
St lokaalne kataloog live serveris, ntx c:\logshippingfiles\ sinna tehakse trn failid

>Failid võtta võrgukataloogist

Sealt võtab failid masin, kuhu peale logshipping failid taastatatakse; ehk siis live serveri see lokaalne kataloog teha ka võrgushareks. \\logshippingfiles\

>Failid kopeerida lokaalsesse kataloogi

See on kataloog siis seal serveris, kuhu logshipping failid taastatakse. Ta kopeerib siis võrgusharest sinna failid.

logshipping1

logshipping2

MSSQL: SQL Server 2005/2008 Express ja profileri alternatiiv

detsember 7, 2011

Nagu teada, et express variantidega tuleb kaasa palju piiranguid (üks kohe profileri puudumine): aga tihti on vaja ka expressil töötavate rakenduste sql’i analüüsida.

Tavaliselt kasutaks SQL Server Profilerit, kuid antud masinal see puudus ja
leidsin täiesti suurepärase tasuta analoogi:

http://anjlab.com/en/projects/opensource/sqlprofiler

Tänaseks side lõpp 😉

MSSQL: backup teemalised lingid

november 27, 2009

Viimasel ajal tuleb minu blogisse otsingusse aina rohkem MSSQL backup teemalisi küsimusi; samas jäänud mulje, et tahetakse ka taastada andmebaasi ilma koopiata. Ütleme nii, et pihta saanud ketta puhul see peaaegu võimatu missioon. Varukoopiad on iga süsteemi alustala, ei piisa sellest, et korra kuus teen koopia.

Backing Up and Restoring Databases in SQL Server

Database-Mirroring-Using-T-SQL

http://blog.sqlauthority.com/2009/09/02/sql-server-mirrored-backup-and-restore-and-split-file-backup-2/

http://www.databasejournal.com/features/mssql/article.php/3782401/SQL-Server-2008-Recovery-Models-and-Backups.htm

http://www.databasejournal.com/features/mssql/article.php/3591131/COPYONLY-Backups-in-SQL-Server-2005.htm

MSSQL : Hoiame infot bitmaskide abil…

veebruar 2, 2009

On olukordi, kus vaja hoida kirje kohta staatusi; ala töödeldud, viga (võib olla mitu viga), täiendavat infot.

Variandid:

  • teha tabelile kõvasti bit välju, muudaks tabeli suht koledaks
  • teeks teise tabeli, mis hoiaks antud kirje staatusi. Raiskame ketast ning muudame päringud kohmakaks

DOS ajast külge jäänud komme bitte kasutada. Jaotasin ära bitid loogilisse vahemikku, mis on väga tähtis
Veabitid jätke alati viimaseks !

Kokku 32 staatus, kui 0 ka arvestada :
0 – siis tähendas, et kirjet pole üldse töödeldud

– töödeldud edukalt
2^0=1

– milline programm muutis/töötles kirjet (4 programmi sai muuta)
2^1=2
2^2=4
2^3=8
2^4=16
— reserveeritud
2^5=32
2^6=64
2^7=128
2^8=256
— kirje täiendavad infobitid
2^9=512
2^10=1024
2^11=2048
2^12=4096
2^13=8192
2^14=16384

— töötlemisel tekkinud vead (16 tk), 7 reserveeritud
2^15=32768
2^16=65536
2^17=131072
2^18=262144
2^19=524288
2^20=1048576
2^21=2097152
2^22=4194304
2^23=8388608
2^24=16777216
2^25=33554432
2^26=67108864
2^27=134217728
2^28=268435456
2^29=536870912
2^30=1073741824

Näiditabel siis:

create table bitmaania
(vagavajalikudandmed varchar(255),
staatused int not null default 0)
go
create index bitmaaniatavalineindx on bitmaania(staatused)

Paneme mõned kirjed ka:

insert into bitmaania(vagavajalikudandmed,staatused)
values(‘JAMA 1’,2|32768 )
go
insert into bitmaania(vagavajalikudandmed,staatused)
values(‘JAMA 2’,8|2097152|134217728 )
go
insert into bitmaania(vagavajalikudandmed,staatused)
values(‘ÜKS ÕNNESTUNUD KIRJE’,1|4|8192 )

Tulemus tabelis…

Päringu tulemus:

JAMA 1 32770
JAMA 2 136314888
ÜKS ÕNNESTUNUD KIRJE 8197

Teeme päringu, anna kõik korrektsed kirjed

Teoorias nagu kõik õige, kui bitmaskide loogikat kasutada. Aga tegelikkuses pole lood nii ilusad, sest mõlema näite puhul ei kasutata indeksit (forced index ei anna ka midagi).

SELECT [vagavajalikudandmed]
,[staatused]
FROM [test].[dbo].[bitmaania]
where staatused & 1=1

või mittekorrektsed

SELECT [vagavajalikudandmed]
,[staatused]
FROM [test].[dbo].[bitmaania]
where staatused & 1!=1

Nüüd peame esitama küsimuse, kas me teeme ainult otsingut ainult stiilis anna kõik korrektsed ja mittekorrektsed kirjed või tahame otsida ka veakoodide järgi.

Kui tõesti vaid esimene variant, siis võib bitmaaniatavalineindx kasutada. See olukord eeldab, et kirjel peab alati olema töödeldud staatus. Sest suvaline infobit märgiks, et kirje töödeldud.

SELECT [vagavajalikudandmed]
,[staatused]
FROM [test].[dbo].[bitmaania] with (index (bitmaaniatavalineindx))
where staatused>0 and staatused<32768

Aga saab ka kavalamalt teha,
et kas ikka reaalselt ka töödeldud bit olemas !

alter table [test].[dbo].[bitmaania]
add okrecs as cast(staatused & 1 as bit)
go
create index okbit on [test].[dbo].[bitmaania](okrecs)

— anname indeksi ette,
— et mssql server ennast üle ei mõtleks !
SELECT [vagavajalikudandmed]
,[staatused]
FROM [test].[dbo].[bitmaania] with (index (okbit))
where okrecs =1

Näiteks soovime ainult kirjeid, millel viga:
2^21=2097152

SELECT [vagavajalikudandmed]
,[staatused]
FROM [test].[dbo].[bitmaania] with (index (bitmaaniatavalineindx))
where staatused>=2097152 and staatused<=4194303

Bitmask pole paha stiil andmebaasinduses, aga seda tuleb osata kasutada !

Lõppu hariv sql lause, millega saate 2 astmete väärtustega tutvuda

set nocount on
declare @btmasks table (temp varchar(255))
declare @cnt int
set @cnt=0

while @cnt<31
begin
insert into @btmasks
select ‘ 2^’+CAST(@cnt as varchar)+’=’+cast(power(cast(2 as bigint),@cnt) as varchar)
set @cnt=@cnt+1
end
— 2^31 – 1 viimane bait on negatiivsuse lipp, seda kasutada ei ole hea

select temp as bmaskval from @btmasks

MSSQL : Sinu igapäevased help protseduurid…

november 13, 2008

Nii, ükspäev vaatasin, milliseid süsteemseid protseduure enim kasutan ja tegin nö järjestuse.

1. sp_helptext – nö protseduur, mis võimaldab protseduuride, funktsioonide, trigerite sisu vaadata (sp_helptrigger).
2. sp_monitor – tagastab üldise serveri info ntx palju cpu hõivatud, palju pakette saadetud, mitu ühendust loodud.
3. sp_who2 – näitab, kes serverisse loginud, kus arvutist, millal viimane batch. Enamasti kasutasin seda, et kiiresti näha, kes keda blokeerib.
4. sp_lock – näitab lukustusi, natuke täiustasin väljundit

declare @abimees table (
spid smallint,
dbid smallint,
ObjId int,
IndId smallint,
Type nchar(4),
Resource nchar(16),
Mode nvarchar(8),
Status nvarchar(20))

insert into @abimees
exec sp_lock

select spid,DB_NAME(dbid) as dbname,object_name(ObjId) as object,IndId,
case
when Type=’DB’ then ‘Database’
when Type=’FIL’ then ‘File’
when Type=’IDX’ then ‘Index’
when Type=’PG’ then ‘Page’
when Type=’KEY’ then ‘Key’
when Type=’TAB’ then ‘Table’
when Type=’EXT’ then ‘Extent’
when Type=’RID’ then ‘Row identifier’
end as typestr,
Resource,Mode,Status
from @abimees

5. sp_helpfile – annab ülevaate andmebaasi failidest.
6. sp_depends – protseduur, mis annab infot selle kohta, millised teised andmebaasi objektid sõltuvad etteantud objektist.
7. sp_helpdb – annan infot kõikidest andmebaasidest, nende suurusest, staatustest jne
8. sp_helprotect – annab ülevaate kõikidest objektidele omistatud õigustest; kes tohib exec sooritada, kes select jne Sellele protseduurile soovitan ikka vaadeldava objekti nime ette anda @name=ntxmingitabel. Muidu kuvatakse kõik protseduurid, tabelid jne nende õigustega.
9. sp_helprole – kuvab vaikimisi valitud andmebaasis defineeritud kasutajate rollid.

Seniks … nägelemiseni…

MSSQL : DDL trigger ehk paneme pirukasse muna

november 4, 2008

Üldiselt tavaolukorras ei ole DDL triggereid eriti vaja, ainus hea rakendus neile on andmebaasis toimunud muudatuste logimiseks. Olen seda vaid kasutanud testimisel , üks lihtne näide.

create trigger ainultadminn
on database
for DDL_TABLE_EVENTS
— DDL_DATABASE_LEVEL_EVENTS
as
begin
declare @xmldat xml
declare @event nvarchar(128)
declare @sql nvarchar(max)
set @xmldat=EVENTDATA()
set @event=isnull(@xmldat.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(128)’),”)
set @sql=isnull(@xmldat.value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’),”)

if (CURRENT_USER not in (‘dbo’,’sa’)) and (@event in (‘ALTER_TABLE’,’DROP_TABLE’))
begin
raiserror(‘— Viga: Sa tegeled vale asjaga’,16,1)
rollback
end
end;

Sündmuste tüüpide kirjelduse saab siit :
http://msdn.microsoft.com/en-us/library/bb510452.aspx

Ps. DDL triggerid on toetatud alates MSSQL 2005

Soovitan ka tutvuda Logon triggeritega, ala ntx. kasutaja Karu saab siis sisselogida, kui kasutaja Mesitaru on väljas jne

Logon Triggers

MSSQL: näita andmebaasi avatud transaktsioone

november 1, 2008

Täna olen MSSQL 2008 lainel, seetõttu veel näiteid…

Üldiselt Microsoft on ka palju toredaid näiteid kokku pannud, aga enamasti ei leia neid õigel hetkel.

Üks disainiviga, mis võib ilmneda halbadel juhtudel on avatud transaktioon ehk rollbacki – commitit ei järgne. Kaks SQLi:

— seda kasutaks mina, näitab kõiki avatud transaktsioone
SELECT convert(varchar(5000),s.context_info) as sessioonisonum, s.*
FROM sys.dm_exec_sessions AS s
WHERE EXISTS
(
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)

Microsofti näide oli, seal kuvatakse transaktioone, mis IDLE ehk üldse midagi ei toimu…

SELECT convert(varchar(5000),s.context_info) as sessioonisonum, s.*
FROM sys.dm_exec_sessions AS s
WHERE EXISTS
(
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)

AND NOT EXISTS
(
SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
)

Nii, teeme nüüd näite, mis võite katsetada testserveril !

declare @vb varbinary(128)
set @vb=cast(‘adminn, ma näen sind’ as varbinary)
set context_info @vb
begin tran

Ja, kui adminn esimest päringut sooritab, võite kindel olla, et Teile helistatakse ;))

Üldiselt, kui last_request_start_time näitab, et transaktsioon avatud rohkem, kui 15 min…oleks arukas see lõpetada.

kill on Sinu sõber

Lõpetuseks veel üks hea SQL Microsofti poolt, vägagi efektiivselt leiab päringud, mis serverit koormavad !!!

SELECT TOP 55 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

MSSQL : Fail varbinary/image kujul, tahaks lugeda tema sisu…

november 1, 2008

Redaktorites ja enamus utiliitides on blobide lugemine suht vaevaline, tihti lihtsam see blob salvestada. Kui ntx andmebaasis hoitakse tekstifaile, tahaks ju mõnikord pilgu peale heita.

Üks triviaalne sql:

declare @p varbinary(max)
declare @c varchar(max)

select @p=failikeha
from failid
where faili_id=1787449
/*
muutuja on ise varchar max tüüpi, aga ma ei soovi kogu faili “dumpi”
ainult esimesed 8000 märki, kui kõike vaja kirjutage 8000 asemel max
*/

set @c=convert(varchar(8000),@p)

select @c

varchar(max) on kasutusel alates MSSQL 2005 !

MSSQL : ja limit ?

oktoober 17, 2008

Mysql sõbrad teavad, et limit käsk aitab neid alati. Limit 0,5 … esimene offset teine mitu kirjet peale seda. Kuigi Mssqli pean ühte lihtsamaks ning võimsamaks andmebaasiks on seal ka natuke puudusi, just see sama limit. Tahad teha lehekülgi, kus kirjed kuvatud vahemike kaupa, tüüpiline veebinduse teema.

select top 45 * from kliendid top annaks lihtsalt esimesed 45 klienti vastavalt sorteeringule.

Kuidas ikka saada kirjete vahemikud ilma temptabeleid ja muud keemiat kasutamata.

select b.recnr,b.kliendi_id,b.eesnimi,b.perekonnanimi
from
(select top 1000 row_number() over (order by perekonnanimi asc) recnr,kliendi_id,eesnimi,perekonnanimi
from meiekliendid) as b
where b.recnr between 1 and 50

üldiselt over klauslis peate määrama mille järgi on sorteeritud subselect !

That’s It!

MSSQL : kui üldse kursoreid kasutad, siis kasuta kiireid

mai 23, 2008

Ütleme nii, et kui vähegi võimalik, võiks kursoritest hoiduda. Reaalsuses pole see võimalik. Siin üks näide kursorist, mis serveris suht kiire.

set nocount on

create table #test1(a varchar(25))
insert into #test1(a)
values(‘esimene’)
insert into #test1(a)
values(‘viimane’)

declare @pintcurs cursor,
@amuutuja varchar(25)

set @pintcurs=cursor fast_forward for
select a
from #test1

open @pintcurs
fetch from @pintcurs
into @amuutuja

while (@@fetch_status=0)
begin
select @amuutuja

fetch from @pintcurs
into @amuutuja
end

close @pintcurs
deallocate @pintcurs

MSSQL : Kuidas queryt analüüsida, kui profilerit pole?

aprill 24, 2008

Kui pole korralikke graafilisi vahendeid MSSQL jaoks serveril, siis konsooli kaudu saab query execution plani järgnevalt kätte (mysqlis siis vastavalt explain käsk).

SET SHOWPLAN_TEXT ON
GO
SELECT *
FROM articles
WHERE title like ‘89%’
GO
SET SHOWPLAN_TEXT OFF
GO

MYSQL imelikud päringud

märts 26, 2008

Feedreader Connect serverit arendades avastasin (MYSQL) omapärad

Mitu kirjet väljastab esimene SQL ja mitu teine 😉

select title
from articles
where deleted=0
limit 0,5
union all
select title
from articles
where deleted=0
limit 0,2;

select s.title
from (select title
from articles
where deleted=0
limit 0,5) as s
union all
select s.title
from (select title
from articles
where deleted=0
limit 0,2) s;

____________________

s.id unknown column ? aga miks või olen harjunud MSSQL mugavustega. Jah lause rumal, aga süntaks vägagi õige.

select s.title,s.id
from articles s
where (select count(*)
from (select s1.title
from articles s1
where s1.id=s.id) as ptt)>0
limit 0,5;

____________________

Ühel päringul kasutati fulltable scani, sunnime MYSQL indeksit kasutama

select *
FROM articles FORCE INDEX (IDX_Articles_modified)
where modified<‘2005-01-01’;

____________________

Ajutiste tabelitega on MYSQL tiimil veel tööd teha;
jah dokumentatsioonis ka öeldud, et see päring hetkel ei tööta…aga miks …

create temporary table if not exists test(a int) engine = memory

insert into test(a)
select a
from test