SQL Objects (Syntax Code)

Als je (wijzing)scripts maakt op een bestaand datamodel, dan zal je vaak code nodig hebben waarin controles moeten plaatsvinden op bijvoorbeeld de aanwezigheid van objecten. Hieronder een overzichtje van syntax die je vaak nodig hebt:

Tabel Syntax
Tabel
Kolom
Foreign Key
Primary Key
Unique Key
Check Constraint
Default Constraint
Trigger

Routine Syntax
Stored Procedure
Functie


Tabel
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects WHERE Type = 'U' AND Name = '<Tabel>')

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '<Tabel>' AND TABLE_SCHEMA = '<Schema>')

/* --==== DROP ====-- */
DROP TABLE < Tabel >

Kolom
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1
           FROM Sys.Objects sO
                INNER JOIN Sys.Columns sC ON sO.ID = sC.ID
           WHERE sO.XType = 'U' AND sC.Name = '<Kolom>' AND sO.Name = '<Tabel>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = '<Schema>' AND TABLE_NAME = '' AND COLUMN_NAME = '<Kolom>')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP COLUMN < Kolom >


Foreign Key
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE Parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'F' AND Name = '<Foreignkey>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Foreignkey>' AND TABLE_NAME = '<Tabel>' AND CONSTRAINT_TYPE = 'FOREIGN KEY')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP CONSTRAINT < Checkcontraint >


Primary Key
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'PK' AND Name = '<Primarykey>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Primarykey>' AND TABLE_NAME = '<Tabel>' 
           AND CONSTRAINT_TYPE = 'PRIMARY KEY')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP CONSTRAINT < Checkcontraint >


Unique Key
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'UQ' AND Name = '<Uniquekey>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Uniquekey >' AND TABLE_NAME = '<Tabel>'
           AND CONSTRAINT_TYPE = 'UNIQUE')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP CONSTRAINT < Checkcontraint >


Check Constraint
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'C' AND Name = '<Checkconstraint>')
 
IF EXISTS (SELECT 1 INFORMATION_SCHEMA.CHECK_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Checkconstraint>')


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_NAME = '<Checkconstraint>' AND TABLE_NAME = '<Tabel>'
           AND CONSTRAINT_TYPE = 'CHECK')


/* --==== DROP ====-- */
ALTER TABLE < Tabel > DROP CONSTRAINT < Checkcontraint >


Default Constraint
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID(< Tabel >) AND Type = 'D' AND Name = '<DefaultConstraint>')


/* --==== DROP ====-- */
--Default niet aan kolom gebonden
DROP DEFAULT < DefaultConstraint >


--Default aan de kolom gebonden
EXEC sp_unbindefault '< Tabel >'.'< Kolom >'
DROP DEFAULT < DefaultConstraint >


Trigger
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM Sys.Objects
           WHERE parent_object_id = OBJECT_ID('<Tabel>') AND Type = 'TR' AND Name = '<Trigger>')


/* --==== DROP ====-- */
DROP TRIGGER < Trigger >
 Stored Procedure

/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = '' AND SPECIFIC_SCHEMA = '')


Functie
/* --==== EXISTS ====-- */
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_NAME = '' AND SPECIFIC_SCHEMA = '')

Nieuwe TSQL Features (SQL 2005)

Tabel operatoren

In SQL2005 zijn een aantal tabel operatoren toegevoegd, die bewerkingen kunnen doen met tabel expressies. Een tabel expressie kan voor veel verschillende dingen staan zoals een echte tabel, tijdelijke tabel, derived table, een view etc etc.

De reeds bekende operator is de JOIN constructie met de reeds bekende syntax:

Naast de JOIN zijn er drie nieuwe constructies bij gekomen te noemen:
 
• APPLY
• PIVOT
• UNPIVOT

APPLY

De APPLY syntax maakt het mogelijk de rechter tabel expressie uit te laten voeren voor elke rij van de linker tabel. De rechter tabel mag de kolommen van de linker tabel gebruiken om een verhouding van onderliggende afhankelijkheid tot stand te brengen (correlatie). Dit betekend dan ook dat in tegenstelling tot een JOIN, dat de volgorde van verwerking vast staat: de linker tabel wordt eerst geëvalueerd alvorens de rechter tabel wordt benadert. De CROSS syntax maakt de rechter tabel output ten opzichte van de linker verplicht en kan dus vergeleken worden met een INNER statement. De OUTER maakt de rechter tabel output optioneel en zal dus NULL retourneren als er niet aan voldaan wordt.

NB: Doordat APPLY functionaliteit is toegevoegd aan de SQL Server 2005 engine is ook een andere mooie eigenschap beschikbaar geworden, namelijk het feit dat een functie die een tabel retourneert nu ook kolommen accepteert als parameter.

Enkele code voorbeelden:

DECLARE @tPersoon TABLE (Sorteernaam nvarchar(150), PersoonID int, ManagerID int)

INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 1 , 'Jelluh, Snelle', NULL
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 2 , 'Arjan, Zuurtje', 1
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 3 , 'Roosje, Cohen', 1
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 4 , 'Twan, Super', 2
INSERT INTO @tPersoon (PersoonID, Sorteernaam, ManagerID)
SELECT 5 , 'Claudia, Breekmans', 4

DECLARE @tNotities TABLE (NotitieID int IDENTITY(1,1), OwnerID int, Notitie nvarchar(250), Datum smalldatetime)
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 1 , 'Is it Friday Yet?', '2010-02-01'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 1 , 'Talk to the Palm !', '2010-01-01'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 1 , 'More Coffee!', '2010-03-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 2 , 'As long as the answer is right, who cares if the question is wrong?','2010-04-05'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 2 , 'Beauty is only skin deep, ugly goes clear to the bone.', '2010-03-04'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 2 , 'Bureaucracy: a method for transforming energy into solid waste.', '2010-03-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 2 , 'Cocaine is a nature way of telling you you have too much money.', '2010-04-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 3 , 'Complex problems have simple, easy to understand, wrong answers.', '2010-06-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 3 , 'Do someone a favor and it becomes your job.', '2010-05-03'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 4 , 'Each problem solved introduces a new unsolved problem.' , '2010-03-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 4 , 'People tend to make rules for others and exceptions for themselves.', '2010-01-02'
INSERT INTO @tNotities (OwnerID, Notitie, Datum)
SELECT 4 , 'The best way to realise your dreams is to wake up.', '2010-06-02'

---===================================---
-- SQL 2000
-- Meest 2 recente notities van de personen
---===================================---

SELECT tP.Sorteernaam
      ,tN.Notitie
      ,tN.Datum
FROM @tPersoon tP
LEFT OUTER JOIN @tNotities tN ON tN.OwnerID = tP.PersoonID
WHERE tN.NotitieID IS NULL
OR tN.NotitieID IN (SELECT TOP 2 NotitieID
                    FROM @tNotities tN2
                    WHERE tN2.OwnerID = tP.PersoonID
                    ORDER BY Datum DESC)
ORDER BY tP.Sorteernaam

---===================================---
-- SQL 2005
-- Meest 2 recente notities van de personen
---===================================---

SELECT tP.Sorteernaam
      ,tRN.Notitie
      ,tRN.Datum
FROM @tPersoon tP
OUTER APPLY (SELECT TOP 3 tN2.NotitieID
                         ,tN2.Notitie
                         ,tN2.Datum
             FROM @tNotities tN2
             WHERE tN2.OwnerID = tP.PersoonID
             ORDER BY Datum DESC) tRN
ORDER BY tP.Sorteernaam


PIVOT

Met het gebruik maken van de PIVOT techniek is het mogelijk om rijen te roteren naar kolommen van een select statement en tijdens het roteren kan de data ook meteen geaggregeerd worden. Het PIVOT statement is opgebouwd uit drie elementen:

1. De aggegratie die zal plaatsvinden op de waarden in de groep.
2. De FOR statement geeft de kolom aan waar uit de rijen geselecteerd gaan worden die geroteerd worden.
3. Het IN statement geeft de uiteindelijke rijen aan die als kolommen getoond gaan worden.

NB: Omdat er wel een aggegratie plaatsvindt maar geen group by clause is gespecificeerd, wordt er onder water gegroupeerd bij alle kolommen van alle tabellen die niet benoemd zijn in de PIVOT. Dit betekent dat je eigenlijk altijd met derived tabels moet werken om de output zo klein mogelijk te houden en zo onnodige groeppeer acties te verkomen.

Enkele code voorbeelden:
 ---==============================---
-- Output klein houden, om onnodige groepeer acties te verkomen
---==============================---
SELECT Sorteernaam,[6],[5],[4]
FROM (SELECT tN.OwnerID
            ,Month(Datum) AS Maand
             FROM @tNotities tN
            ) tDerived
             INNER JOIN @tPersoon tP ON tP.PersoonID = tDerived.OwnerID
PIVOT (SUM(OwnerID)
FOR Maand IN ([6],[5],[4])
) AS tPivot

Tabel expressies

In SQL 2005 is een nieuw type tabel expressie geintroduceerd, namelijk de Common Table Expressie (CTE) . Een CTE lijkt op vele opzichten op een derived table en alle validaties die van toepassing op een derived table zijn dan ook van toepassing op een CTE, te noemen dat de query een tabel moet retourneren en dus alle kolommen namen moeten hebben en geen order by clause is toegestaan. Omdat CTE’s het WITH statement gebruiken kan dit botsen met andere syntax die ook WITH gebruiken. Het statement die voor de CTE wordt uitgevoerd wordt moet dan ook afgesloten worden met een semicolon (;).

 NB: Het gebruik van Semicolon (oftewel puntkomma) om een statement af te sluiten is in SQL2005 geintroduceerd. Het wordt in deze versie nog niet afgedwongen op een paar cases na zoals CTE’s. Toch wordt verwacht dat in toekomstige versies de semicolon verplicht gaat worden, dus om code compatible te houden en jezelf het gebruik alvast aan te leren is het wel wijs om jezelf dit aan te leren.

Van een Common Table Expressie is de syntax als volgt opgebouwd:

Er zijn echter ook verschillen tussen een derived table en een CTE. Een CTE kan bijvoorbeeld niet direct genestd worden. Daarintegen kan je meerdere CTE’s onder elkaar defineren gebruik makend van het zelfde with statement.

CTE’s hebben nog meer voordelen, zo kan je meerdere keren in je outer query verwijzen naar de CTE tabel zonder de definitie van de CTE te herhalen, dit in tegenstelling tot een derived table. De CTE is niet toegankelijk voor andere query’s in de batch, enkel voor de query rechtstreeks volgend na de CTE definitie. CTE’s maken het ook mogelijk om complexe recursieve vraagstukken op te lossen, zodat hierachieen, bomen etc verwerkt kunnen worden.

 
 
 
 
 
 
 
 
Een recursieve CTE is opgebouwd uit tenminste 2 queries. Eerst wordt een zogenaamde anchor query gedefineerd. Dit is de tabel die eigenlijk gewoon een resultset terug retourneert en is de basis tabel waarover recursie gaat plaatsvinden De tweede tabel is de recursieve tabel waar een relatie wordt gelegd met de CTE naam, dit is niet te verwarren met dezelfde relatie die de outer query legt met de CTE naam. De outer query krijgt namelijk het uiteindelijk resultaat terug geretourneerd. De referentie die de recursieve query legt naar de CTE naam (door verwijzing naar de CTE wordt de query een recursieve query), verwijst naar een resultaat wat nog in verwerking is.
 
NB: Hoe diep een recursie kan gaan, mag worden ingesteld met het statement OPTION (MAXRECURSION ); Default staat de recursie ingesteld op 100 levels, dit om te verkomen dat een server vast loopt als er een bug in de programmatuur zit die een oneindige recursie tot het gevolg heeft.
 
NB: Kolommen die met betrekking hebben op elkaar door de UNION moet het zelfde datatype hebben.
 
Enkele code voorbeelden: 
---===================================---
-- Meerdere Common Table Expressie's (CTE)
-- Geef Maanden waarvan aantal actieve
-- notitiegebruikers grotergelijk is aan 2
---===================================---
;WITH C1 AS
(
    SELECT Month(tN.Datum) AS Maand
          ,tN.OwnerID      AS EigenaarID
    FROM @tNotities tN
),
C2 AS
(
   SELECT Maand
         ,COUNT(DISTINCT EigenaarID) AS AantalNotities
   FROM C1
   GROUP BY Maand
)
SELECT Maand, AantalNotities
FROM C2
WHERE AantalNotities >= 2 

---===================================---
-- Common Table Expressie (CTE) meerdere keren naar verwijzen
-- Bepaal de toename van het aantal actieve gebruikers
-- die de notitie functionaliteit gebruiken per maand.
---===================================---
;WITH cteNotitiesPerMaand AS
(
  SELECT Month(tN.Datum)         AS Maand
        ,COUNT(DISTINCT OwnerID) AS AantalNotities
  FROM @tNotities tN
  GROUP BY Month(tN.Datum)
)
SELECT tNPM.Maand          AS Maand
      ,tNPM.AantalNotities AS AantalNotities
      ,(tNPM.AantalNotities - tNPM2.AantalNotities) AS ToenameAantalNotities
FROM cteNotitiesPerMaand tNPM
     LEFT OUTER JOIN cteNotitiesPerMaand tNPM2 ON tNPM.Maand = (tNPM2.Maand + 1)
ORDER BY Maand 


---===================================---
-- Recursieve Common Table Expressie (CTE)
---===================================---
;WITH ctePersonenMetManager AS
(
  --Anchor
  SELECT PersoonID
        ,Sorteernaam
        ,ManagerID
        ,0 AS TreeLevel
  FROM @tPersoon
  WHERE ManagerID IS NULL -- Root
UNION ALL
  --Recursive Member
  SELECT tP.PersoonID
        ,tP.Sorteernaam
        ,tP.ManagerID
        ,tRecursief.TreeLevel + 1 AS TreeLevel
  FROM ctePersonenMetManager      AS tRecursief
       INNER JOIN @tPersoon tP ON tP.ManagerID = tRecursief.PersoonID
)
SELECT * FROM ctePersonenMetManager 

Set operatoren

Joins kunnen gezien worden als horizontale operaties tussen tabellen. Een join genereert dan ook een viruele tabel, die uit kolommen bestaat van meerdere tabellen. Een Set operator kan gezien worden als een verticale operatie tussen tabellen. Het verbreedt de data niet in de zin van meerdere kolommen, maar wel in de zin van meer of minder data te tonen dan 1 tabel. Een reeds bekende set operator is de UNION (ALL). In SQL 2005 zijn er twee nieuwe bij gekomen namelijk de EXCEPT en INTERSECT.

EXCEPT

Met het statement EXCEPT kan je rijen indentificeren welke in het eerste SQL statement voorkomen en die niet aanwezig zijn in het tweede SQL statement.

NB: In SQL 2000 wordt vaak NOT EXISTS gebruikt om vershillen tussen tabellen te herleiden. Een groot nadeel van NOT EXISTS statement is dat NULL=NULL als verschil wordt gezien.

INTERSECT

Met het statement INTERSECT kan je rijen indentificeren die in beide SQL statement aanwezig zijn.

NB: Voor alle SET operatoren geldt dat het aantal kolommen moet overeenkomen in de verschillende SQL statements. Ook moet het datatype per corresponderende kolom overeenkomen.

Enkele code voorbeelden

---===================================---
-- SQL 2000 versus SQL 2005
-- NOT EXISTS versus EXCEPT
---===================================---

CREATE TABLE #tDummyTabel1 (Straat VARCHAR(50), Postcode VARCHAR(10), Plaats VARCHAR(50))
INSERT INTO #tDummyTabel1 (Straat,Postcode,Plaats)VALUES ('Kipstraat',NULL,'Rotterdam')
INSERT INTO #tDummyTabel1 (Straat,Postcode,Plaats)VALUES ('Koestraat','2014GH','Rotterdam')
INSERT INTO #tDummyTabel1 (Straat,Postcode,Plaats)VALUES ('Varkenstraat','2045JG','Rotterdam')

CREATE TABLE #tDummyTabel2 (Straat VARCHAR(50), Postcode VARCHAR(10), Plaats VARCHAR(50))
INSERT INTO #tDummyTabel2 (Straat,Postcode,Plaats)VALUES ('Kipstraat',NULL,'Rotterdam')
INSERT INTO #tDummyTabel2 (Straat,Postcode,Plaats)VALUES ('Koestraat','2014GH','Rotterdam')
INSERT INTO #tDummyTabel2 (Straat,Postcode,Plaats)VALUES ('Visstraat','2045JG','Rotterdam')

--SQL 2000 , ziet NULL als verschil
SELECT t1.Straat,t1.Postcode,t1.Plaats
FROM #tDummyTabel1 t1
WHERE NOT EXISTS(SELECT 1
                 FROM #tDummyTabel2 t2
                 WHERE t1.Straat = t2.Straat
                       AND t1.Postcode = t2.Postcode
                       AND t1.Plaats = t2.Plaats)

--SQL 2005 , except geeft werkelijk verschil
SELECT Straat,Postcode,Plaats FROM #tDummyTabel1
EXCEPT
SELECT Straat,Postcode,Plaats FROM #tDummyTabel2

---===================================---
-- SQL 2005
-- INTERSECT
---===================================---
SELECT Straat,Postcode,Plaats FROM #tDummyTabel1
INTERSECT
SELECT Straat,Postcode,Plaats FROM #tDummyTabel2
 
Aggregatie

In SQL 2005 zijn vele nieuwe opties om data te aggregeren, eerder is al besproken PIVOT en UNPIVOT methodiek. Er zijn echter nog andere mooie nieuwe features in 2005 zoals het OVER statement en de CUBE optie.

OVER

 
 
 
 
De OVER clause is een zeer eenvoudige en krachtig statement, dat het mogelijk maakt om data te aggregeren zonder een group by clause. Hierdoor wordt het dus mogelijk om geaggreerde data te combineren met data die niet geaggreerd is. Tevens is het ook toegestaan om meerdere aggregaties op verschillende kolommen te laten plaatsvinden. De OVER clause is ook nog eens zeer efficient omdat de data maar 1 keer gescand wordt en alle aggregaties hierop worden uitgerekend.
 
CUBE

Met behulp van het CUBE statement maak je vraagstukken met betrekking tot geaggreerde data flexibel. Een cube statement verkomt dat je voor elke gewenste vraagstuk een nieuwe groepeer actie moet laten uitvoeren. Om gegroepeerde data toegangelijk te maken voor meerdere vraagstukken kan je het WITH CUBE Statement toevoegen onder de Group by clause.

SQL Server Query Tuning en Optimalisatie - Resources

Ik heb regelmatig kennisoverdracht sessies naar collega's en vind het zonde om de kennis niet verder te delen. Hieronder een beschrijving van een kennissessie m.b.t. de SQL performance tuning. Ik heb bij het opstellen van het document/blog met name geput uit de verschillende SQL inside boeken die microsoft op de markt heeft gebracht. Deze boeken zijn een aanrader voor mensen die dieper in het SQL Server wereldje willen duiken.

De performance van een applicatie kan afhankelijk zijn van vele factoren. Zoals de gekozen applicatie architectuur, het applicatie ontwerp, het gekozen isolation level met betrekking tot transacties, de geschreven sql-code, gebruikte hardware en de configuratie van SQL Server zelf.

Resources

Om SQL server efficiënt query’s te laten uitvoeren vraagt deze om resources. Als een bepaalde resource niet optimaal benaderbaar is zal dit zeker tot performance problemen lijden. De belangrijkste resources die SQL Server gebruikt zijn de volgende:

 • CPU
 • Geheugen
 • I/O
 • Tempdb
 • Blocking

Het is belangrijk om te weten hoe te achterhalen is dat 1 van deze resources de performance bottleneck is.

CPU probleem
SQL Server heeft een set van ‘workers’ tot zijn beschikking om queries en interne acties uit te voeren. Een worker krijgt een taak uitgedeeld door SQL Server die 1 op 1 wordt gekoppeld aan een thread of fiber van windows. Elke batch of query die SQL Server moet uitvoeren wordt gekoppeld aan 1 worker. De worker blijft aan zijn of haar taak gekoppeld totdat deze in z’n geheel is uitgevoerd, daarna zal SQL Server dezelfde worker gaan gebruiken voor het uitvoeren van een andere batch of query. Workers kunnen vele statussen hebben, de onderstaande zijn het interessants:

 • Running - De worker gebruikt momenteel actief de CPU
 • Runnable - De worker wacht tot het zijn beurt is om de CPU te mogen gebruiken
 • Suspended - De worker is aan het wachten op een resource bv I/O of een lock.

Bovenstaande statussen laten zijn dat als er veel workers een runnable status hebben dat dit kan duiden op te weinig CPU kracht. Daarintegen als er veel workers de status suspended hebben kan dit weer aangeven dat er een I/O of een uitgebreid lock probleem aanwezig is.

NB: Als er meer dan 1 processor aanwezig is op een Server kan SQL Server besluiten op 1 query of batch te verdelen over meerdere workers en dus meerdere threads. Dit is het zogenaamde parallel uitvoeren van een query wat dan ook terug te zien is in bijvoorbeeld executieplannen.

Wanneer het duidelijk is dat er een CPU probleem is dan zal moeten worden uitgezocht worden waar dit vandaan komt. Twee veel voorkomende oorzaken zijn:

 • Inefficiënt query plan
 • Veel recompiles van executie plannen

Inefficiente query plannen kunnen een bottleneck zijn en leiden tot een cpu performance probleem. Veel oorzaken kunnen leiden tot een minder inefficiënt query plan, zo kan er per ongeluk een nuttige index zijn verwijderd of er zijn geen goede statistieken aanwezig voor SQL server om een goed plan uit te rekenen.

Ook kan het voorkomen dat er veel recompile plaats vinden van executieplannen. Het uitrekenen van een executieplan is een erg CPU intensief proces, het vaak opnieuw uitrekenen van een plan kan dus ook een zware druk leggen op de CPU. Een recompile kan veroorzaakt worden door nieuwe statistieken, verandering van indexen etc.

Met behulp van Dynamic Management Views (DMV’s) en Dynamic Management Functions (DMF’s) kunnen performance problemen worden gevonden.

NB: DMV’s en DMF’s geven alleen waarden terug van queries die gecached zijn. Er kunnen dus dure queries buiten schot blijven omdat er bijvoorbeeld geheugen te kort was er queries uit de cache zijn verstoten. Om een goed totaal beeld te krijgen moeten de DMV’s regelmatig geraadpleegd worden.


Hieronder handige code om CPU problemen te achterhalen
---==== CPU Performance ====---

--Reset wacht counters
--LET OP 1 malig uitvoeren alvorens meeting te beginnen
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

--===================================================================----
--===================================================================----
--Toon workers en hun status mbt de CPU.
SELECT dmT.session_id            AS sessie_wacht_voor_cpu_time
      ,dmW.last_wait_type
      ,dmER.command              AS type_sql_command
      ,db_name(dmER.database_id) AS databasenaam
      ,CASE
         WHEN dmW.state = 'RUNNABLE' THEN 'wacht op cpu'
         WHEN dmW.state = 'RUNNING' THEN 'gebruikt cpu'
         WHEN dmW.state = 'SUSPENDED' THEN 'wacht op resource (bv I/O of lock)'
       END
FROM sys.dm_os_workers dmW
     INNER JOIN sys.dm_os_schedulers dmS  ON dmW.scheduler_address = dmS.scheduler_address
     INNER JOIN sys.dm_os_tasks dmT       ON dmW.task_address = dmT.task_address
     INNER JOIN sys.dm_exec_requests dmER ON dmER.session_id = dmT.session_id
                                             AND dmER.request_id = dmT.request_id
WHERE dmS.scheduler_id < 255 --kleiner dan 255 zijn queries
      AND dmW.state IN ('RUNNABLE','RUNNING','SUSPENDED')
      AND dmT.session_id > 50 -- kleiner dan 50 is SQL Server zelf
ORDER BY dmW.state

--Toon totaal aan gewachte tijd voor CPU
SELECT SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats

--===================================================================----
--===================================================================----
--Queries met meeste CPU Time
SELECT TOP 10
      dmQS.total_worker_time/dmQS.execution_count  AS gemiddelde_cpu_tijd
     ,dmQS.execution_count                         AS aantal_keer_uitgevoerd
     ,(SELECT db_name(dbid)
       FROM sys.dm_exec_sql_text(dmQS.sql_handle)) AS databasenaam
     ,(SELECT SUBSTRING( text
                        ,statement_start_offset/2+1
                        ,(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
                               ELSE statement_end_offset
                          END - statement_start_offset) / 2)
       FROM sys.dm_exec_sql_text(dmQS.sql_handle)) AS QueryText
FROM sys.dm_exec_query_stats dmQS
ORDER BY gemiddelde_cpu_tijd DESC

--Queries meest uitgevoerd
SELECT TOP 10
       dmQS.total_worker_time
      ,dmQS.execution_count                         AS aantal_keer_uitgevoerd
      ,(SELECT db_name(dbid)
        FROM sys.dm_exec_sql_text(dmQS.sql_handle)) AS databasenaam
      ,(SELECT SUBSTRING( text
                         ,statement_start_offset/2+1
                         ,(CASE
                             WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
                             ELSE statement_end_offset
                           END - statement_start_offset) / 2)
        FROM sys.dm_exec_sql_text(dmQS.sql_handle)) AS QueryText
FROM sys.dm_exec_query_stats dmQS
ORDER BY aantal_keer_uitgevoerd DESC
--===================================================================----
--===================================================================----

Geheugen probleem
Meeste productie databases zijn groter dan het geheugen wat aanwezig is op de server. De data van SQL Server staat opgeslagen op zo genoemde ‘data-pages’. Als SQL Server een data-page niet kan vinden in het geheugen dan zal hij deze laden van schijf en laden in het geheugen (buffer pool) om de volgende keer sneller te raadplegen. SQL Server bepaald via een algoritme (Least Recently Used – caching algorithme) welke data-pages in het geheugen blijven staan. Ook gebruikt SQL Server het geheugen voor interne doeleinden om bijvoorbeeld locks, connecties, workers en geoptimaliseerde plannen te managen. Te weinig geheugen kan leiden tot een belasting van andere resources en de gehele performance omlaag halen. Zo kan SQL Server bijvoorbeeld minder data-pages in het geheugen houden wat zal leiden tot meer I/O acties, of minder query plannen hergebruiken wat weer kan leiden tot meer CPU acties.

Er kan onderscheid worden gemaakt tussen 2 verschillende type geheugen problemen:

• Fysiek geheugen
• Virtueel geheugen

Fysiek geheugen

Het fysieke geheugen, ook wel RAM genoemd, kan onder druk staan doordat er kort weg te weinig RAM beschikbaar is, voor de applicaties die draaien op de Server. SQL Server monitort de aanwezige geheugen druk en zal zijn buffer-pool verkleinen als middel om deze te verlagen.

Ook kunnen interne processen van SQL Server besluiten om geheugen te ‘stelen’ van de buffer pool, dit levert zo geheten ‘stolen pages’ op.

Het makkelijkst om te controleren of een server een te kort heeft aan fysiek geheugen is door te controleren hoeveel RAM er nog beschikbaar is door op de performance tab van de task manager te kijken. Als er minder dan 50 a 100 mb beschikbaar is dan zal te kunnen leiden tot performance problemen. Ook kunnen performance counters worden ingesteld:

 • Memory: Available Bytes
     Dit geeft het totaal geheugen aan dat beschikbaar is voor programma’s om te gebruiken.
 • SQL Server: Buffer Manager: Buffer Cache Hit Ratio
    Dit geeft aan hoe vaak een page gevonden is in het geheugen inplaats van op schijf, hoort hoog in de waarde 90 te zitten.
 • SQL Server: Buffer Manager: Page Life Expectancy
    Dit geeft het aantal seconden weer dat een page in het geheugen (buffer pool) aanwezig blijft, een te lage waarde kan duiden op een geheugen probleem.

Er is ook een DBCC commando MEMORYSTATUS aanwezig waarmee geheugen problemen geanalyseerd kunnen worden.

Virtueel geheugen

Een te kort virtueel geheugen kan ook leiden tot een langzaam systeem doordat dit zal leiden tot meer I/O acties. SQL server detecteert ook onder water het te kort of te gefragmenteerd virtueel geheugen en zal dan zijn gereserveerde stukken vrij geven en zijn caches verkleinen.

Het makkelijkst om te controleren of een server een te kort heeft aan virtueel geheugen is ook hier om op te performance tab van de taskmanager te kijken naar ‘Commit Charge’ mbt het total en het limit aan beschikbaar geheugen. Ook kunnen performance counters worden ingesteld:

 • Paging file: % Usage
   Geeft het percentage weer dat de page file in gebruik is
 • Memory: Commit Limit
    Geeft het aantal bytes aan dat nog beschikbaar is voor gebruik alvorens de page file vergroot moet gaan worden.

Hieronder handige code om Memory problemen te achterhalen
---==== MEMORY Performance ====---
 --===================================================================---
--===================================================================----
--Controleer kolom buffer counts committed versus target
--Controleer kolom buffer counts committed versus buffer distribution kolom stolen (niet hoger dan 75%)

DBCC MEMORYSTATUS

--===================================================================----
--===================================================================----

I/O probleem
SQL Server zijn performance is sterk afhankelijk van een snel I/O verkeer. Zolang de database niet totaal in het geheugen past zullen er pages van disk naar het geheugen geschreven moeten worden en omgekeerd. Ook worden aanpassingen aan de database gelogd en voordat een transactie gecommit kan worden moet de data naar schijf geschreven zijn. Ook wordt de tempdb gebruikt om tijdelijk data naar weg te schrijven om bijvoorbeeld data te orderen (order by clause) of indexen bij te werken.

Een van de mogelijkheden om een I/O bottleneck te detecteren is gebruik te maken van performance counters:

 • PhysicalDisk Object: Avg. Disk Queue Length
    Geeft het gemiddeld aantal fysieke lees en schrijf acties weer die staan te wachten in queue. Als de queue lengte meer dan 2 per schijf is dan kan dit duiden op een I/O probleem.
 • PhysicalDisk Object: Avg. Disk Sec/Read en Avg. Disk Sec/Write
    Geeft de gemiddelde waarde weer in seconden van de duur om data van en naar schijf te schrijven

 
 
 
 
 
 
 • PhysicalDisk Object: Disk Reads/Sec en Disk Writes/Sec
   Geeft het aantal lees en schrijf acties weer op de schijf, zorg ervoor dat het onder de 85 procent blijft anders wordt de schijft toegangs snelheid sterk vertraagd.

Bovenstaande counters geven weer of er een probleem op schijf niveau is, echter kan er niet mee achterhaald worden voor welke file het probleem bestaat, is het de tempdb of log file die problemen veroorzaakt en naar een aparte schijf geplaats moet worden. Dit soort informatie kan in SQL server met behulp van een DMV worden uitgevraagd.

Als er geconcludeerd is dat er lange wachttijden zijn op I/Q requests dan is het verleidelijk om snel te wijzen naar de schijven, echter kan een te kort aan geheugen, indexen of slechte queries leiden tot meer I/O verkeer. Met DMV’s kan dit worden achterhaald.

Hieronder handige code om I/O problemen te achterhalen

---==== I/O Performance ====---
--===================================================================----
--===================================================================----
---Wachten tijden per SQL file sinds start van SQL Server
SELECT db_name(dmVFS.database_id) AS databasenaam
      ,sMF.name AS bestandnaam
      ,sMF.physical_name          AS bestandslocatie
      ,dmVFS.io_stall_write_ms    AS schrijf_vertraging_ms
      ,dmVFS.io_stall_read_ms     AS lees_vertraging_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) dmVFS
     INNER JOIN sys.master_files sMF ON sMF.database_id = dmVFS.database_id
                                        AND sMF.file_id = dmVFS.file_id

--===================================================================----
--===================================================================----

---Wachten van een worker totdat de data-page van schijf in pool-buffer is geschreven

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

SELECT wait_type
      ,waiting_tasks_count                 AS aantal_keren_gewacht_op_io
      ,wait_time_ms AS ms_workers_in_suspended_en_runnable_mode
      -- signal_wait_time_ms: mag io gaan gebruiken van systeem
      --, tijd die het duurt alvorens de worker de io ook echt gebruikt.
     ,signal_wait_time_ms                  AS ms_workers_in_runnable_mode
     ,(wait_time_ms - signal_wait_time_ms) AS ms_workers_gewacht_op_io
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('PAGEIOLATCH_EX', 'PAGEIOLATCH_SH')
ORDER BY wait_type

--===================================================================----
--===================================================================----

-- Toon 10 meest dure queries
SELECT TOP 10
            (total_logical_reads/execution_count)    AS gemiddeld_logical_reads
           ,(total_logical_writes/execution_count)   AS gemiddeld_logical_writes
           ,(total_physical_reads/execution_count)   AS gemiddeld_phys_reads
           ,execution_count                          AS aantal_keren_uitgevoerd
           ,(SELECT SUBSTRING( text
                             ,statement_start_offset/2+1
                             ,(CASE
                                  WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
                                  ELSE statement_end_offset
                               END - statement_start_offset) / 2)
            FROM sys.dm_exec_sql_text(sql_handle))    AS QueryText
          ,(SELECT query_plan
            FROM sys.dm_exec_query_plan(plan_handle)) AS QueryPlanXML
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC

TempDB probleem
De tempdb is een resource voor de gehele SQL Server instance. SQL Server gebruikt de tempdb onder andere voor het wegschrijven van tijdelijke tabellen of tabel variabelen, sorteringen, rebuilds van indexen, joinen van hash tabellen, uitvoeren van cursoren etc etc.

Dit doet SQL Server naar de tempdb en niet naar de gebruikte database zelf, omdat:

 • Er een rechten conflict kan ontstaan niet elke user heeft rechten om een CREATE TABLE commando uit te voeren.
 • Er geen logging overhead aanwezig is in de tempdb, elke tabel gecreëerd in de database zelf zou log informatie onthouden om gegevens ongedaan te maken.
 • Anders de zelfde I/O pad gedeeld moet worden, nu kunnen de tempdb en de gebruikers database op afzonderlijke schijven geïnstalleerd worden.
 • Er kan vervuiling in de database ontstaan als er tijdelijke tabellen achter zouden blijven.

Omdat de tempdb niet onder deel is van het transactie log systeem en bij een crash van de server geen data herstelt hoeft te worden, is de tempdb uit termate geschikt om op andere type schijven te worden gezet voor snellere I/O toegang zoals een RAM schijf of een schijf met veel cache.

Een van de mogelijkheden om een tempdb bottleneck te detecteren is gebruik te maken van performance counters:
• SQLServer:Access Methods: Worktable Created/Sec
   Het aantal werk tabellen die gecreerd worden door SQL Server per seconde in de tempdb. Normaal hoort dit onder de 200 tabellen per seconden te zijn. (werk tabellen zijn bedoeld voor cursoren, ordeningen etc)
• SQLServer: General Statistics: Temp Tables Creation Rate
   Het aantal tijdelijke tabellen of tabel variabelen gecreerd per seconde.
• SQLServer: General Statistics: Temp Tables for Destruction
   Het aantal tijdelijke tabellen of tabel variabelen die wachten om opgeruimd te worden.

Je kan tempdb problemen verkleinen door te achterhalen met DMV’s welke queries veel I/O verkeer veroorzaken in de tempdb.

Hieronder handige code om TempDB problemen te achterhalen

---==== Tempdb Performance ====---
--===================================================================----
--===================================================================----
-- Geef de queries terug die het meest I/O verkeer veroorzaken in tempdb
SELECT TOP 10
               dmDTSU.session_id           AS sessie
              ,db_name(dmDTSU.database_id) AS databasenaam
              ,dmDTSU.pages_allocation     AS pages_gereserveerd_voor_sessie
              ,dmDTSU.pages_deallocation   AS pages_vrij_gegeven_voor_sessie
              ,(SELECT SUBSTRING( text
                                 ,statement_start_offset/2+1
                                 ,(CASE
                                     WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
                                     ELSE statement_end_offset
                                   END - statement_start_offset) / 2)
                FROM sys.dm_exec_sql_text(dmER.sql_handle))    AS QueryText
              ,(SELECT query_plan
                FROM sys.dm_exec_query_plan(dmER.plan_handle)) AS QueryPlanXML
FROM (SELECT session_id
            ,request_id
            ,database_id
            ,SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count)   AS pages_allocation
            ,SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS pages_deallocation
      FROM sys.dm_db_task_space_usage
      GROUP BY session_id, request_id,database_id) dmDTSU
      INNER JOIN sys.dm_exec_requests dmER ON dmER.session_id = dmDTSU.session_id
                                              AND dmER.request_id = dmDTSU.request_id
WHERE dmDTSU.session_id > 50
ORDER BY pages_gereserveerd_voor_sessie DESC

Blocking probleem
Vaak is er een misverstand over het feit dat locking iets verkeerds zou zijn. Echter geen enkele database zou in een productie omgeving kunnen werken zonder lockingmechanisme. Een record kan immers niet door twee personen tegelijkertijd worden aangepast. Echter significant veel locking kan blocking problemen opleveren en veroorzaakt vertraagde response tijden. Vaak komt dit door langlopende transacties of het isolation level waar de transacties in uitgevoerd worden. Als een transactie een rij aanpast wordt een zogeheten ‘X-lock’ vastgehouden voor de duur van de transactie. Als het transactie level hoger is (repeatable read, serializable) worden zelfs de S-locks niet vrijgegeven voor de duur van de transacties.

Er zijn een paar standaard regels om blocking problemen te verkleinen:

 • Verkort de duur van de transactie en laat transacties in een lager isolation level draaien. Zorg er voor dat gebruikers nooit invloed kunnen hebben op de duur van de transactie.
 • Gebruik zo min mogelijk data binnen een transactie, soms ontbreekt er een index en wordt er veel te veel data gelezen die niet relevant zijn voor de te aan te passen waarden.
 • Als er mutaties binnen 1 transactie plaatsvinden of veel verschillende rijen, breek dit op in meerdere transacties.

Een van de mogelijkheden om een blocking bottleneck te detecteren is gebruik te maken van performance counters:

 • SQLServer: Locks: Average Wait Time (ms)
     Gemiddelde wachttijd for elke lock request dat resulteerde in een block situatie
 • SQLServer: Locks: Lock Requests/Sec
   Geeft het aantal nieuwe locks weer die de lockmanager van SQL Server aanvraagt per seconde
 • SQLServer: Locks: Lock Wait Time (ms)
   Geeft de totale tijd weer van het aantal ms dat locks staan te wachten per seconde.
 • SQLServer: Locks: Lock Waits/Sec
   Geeft het aantal lock requests aan dat niet meteen door SQL Server kon worden toegekend en waar de gebruiker voor moest wachten.
 • SQLServer: Locks: Number of Deadlocks/Sec
   Geeft het aantal deadlocks weer per seconde
 • SQLServer: General Statistics: Processes Blocked
   Geeft het aantal processes weer dat momenteel geblocked worden
 • SQLServer: Access Methods: Table Lock Escalations/Sec
   Geeft het aantal keer weer dat een lock geescaleerd is tot een tabel lock.

Om blocking problemen te verhelpen, zal je moeten weten welke transacties locks vasthouden en welke transacties geblocked worden. Dit kan doormiddel van DMV’s worden geanalyseerd.


Hieronder handige code om Blocking problemen te achterhalen

---==== Blocking Performance ====---
 --===================================================================----
--===================================================================----

---Laat zien welke en hoe transacties elkaar beinvloeden door locking

SELECT dmOWT.session_id           AS request_sessieid
      ,dmTL.request_mode          AS request_type_lock
      ,dmOWT.blocking_session_id  AS blocking_sessieid
      ,dmOWT.wait_duration_ms     AS wachttijd_ms
      ,db_name(dmTL.resource_database_id) AS databasenaam
      ,dmOWT.resource_description AS beschrijving_blocking_resource
      ,(SELECT SUBSTRING( text
                         ,statement_start_offset/2+1
                         ,(CASE
                              WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),text)) * 2
                              ELSE statement_end_offset
                           END - statement_start_offset) / 2)
        FROM sys.dm_exec_sql_text(sql_handle)) AS QueryText
FROM sys.dm_tran_locks dmTL
     INNER JOIN sys.dm_os_waiting_tasks dmOWT ON dmTL.lock_owner_address = dmOWT.resource_address
     INNER JOIN sys.dm_exec_requests dmER ON dmTL.request_request_id = dmER.request_id
                                              AND dmOWT.session_id = dmER.session_id

--===================================================================----
--===================================================================----

--Dedecteer een table lock
 GO

SELECT dmTL.request_session_id                         AS sessieid
      ,dmTL.resource_type
      ,db_name(dmTL.resource_database_id)              AS databasenaam
      ,object_name(dmTL.resource_associated_entity_id) AS tabelnaam
      ,dmTL.request_mode
      ,dmTL.request_type
      ,dmTL.request_status
FROM sys.dm_tran_locks dmTL
     INNER JOIN sys.all_objects sAO ON dmTL.resource_associated_entity_id = sAO.object_id
WHERE dmTL.request_type = 'LOCK'
      AND dmTL.request_status = 'GRANT'
      AND sAO.type = 'U'
      AND dmTL.resource_type = 'OBJECT'
      AND dmTL.resource_database_id = DB_ID()
      AND dmTL.request_mode IN ('X','S')

--===================================================================----
--===================================================================----