Performance: Werkelijke data in plaats van cached plans

Posted on by

Samenvatting:
SQL Query’s worden als tekst in de cache van de server opgeslagen om later eventueel te worden hergebruikt. De kleinste afwijking in een query, levert een nieuw record van de query in de cache op; dus ook bij een andere formatting, het gebruik van hoofdletters of het gebruik van comments. Query plannen zijn erg belangrijk en het opslaan van query plannen komt de performance ten goede. Echter, query plannen dubbel in de cache opslaan omdat de tekst nét wat anders eruit ziet, gaat ten koste van het geheugen, waardoor er minder ruimte voor werkelijke data overblijft, die regelmatig wordt benaderd.

Uitwerking en voorbeeld
Om query’s zo efficiënt mogelijk te draaien, slaat SQL Server query plannen op. Deze worden opgeslagen in de tabellen sys.dm_exec_cached_plans (de plannen) en sys.dm_exe_sql_text (de tekst van de SQL queries). Het opslaan van deze query plannen gebeurt in het geheugen. In principe is hier niets mis mee. SQL Server gebruikt deze query plannen opnieuw wanneer dezelfde query wordt gedraaid en hoeft niet opnieuw een query plan op te zetten, wat performance kost.

Wat regelmatig vergeten wordt, is dat query plannen als text worden opgeslagen. Hierdoor is het mogelijk dat een query plan van dezelfde query twee keer in de cache voorkomt. Voorbeeld van een query, uitgevoerd op de StackOverflow database:

/* Test query 1 */
SELECT
*
FROM dbo.Users
WHERE Reputation = 2499
GO

/* Test query 2 */
SELECT
*
FROM dbo.Users
WHERE Reputation = 2499
GO

Wordt vervolgens de cache uitgelezen met de volgende query:

SELECT
	cacheobjtype,
	text,
	plan_handle
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%Test query%' AND text NOT LIKE '%sys.dm_%'

Dan is het resultaat twee records. Wordt er een ‘Test query 3’ toegevoegd, dan zitten er daarna drie records in de cache. Onbedoeld kan het geheugen hiermee onnodig worden belast met query plannen van dezelfde query. Dit terwijl het geheugen juist zo vol mogelijk moet worden gezet met daadwerkelijke data uit de database die regelmatig wordt benaderd.

Conclusie: voorzichtig, dus! SQL Server houdt zelfs rekening met hoofdletters. Is er één teken in de query tekst anders (hoofdletter, punt, komma, enter, wat dan ook!), dan verschijnen er al twee query plannen in de cache.

ErrorCode en ErrorColumn in SSIS gereserveerd woord (keyword)!

Posted on by

Vandaag reviewden mijn collega en ik een handleiding voor het samenstellen van een eenvoudig SSIS package in SQL Server Data Tools. Onderdeel van de handleiding was een voorbeeld SSIS package. Binnen het voorbeeld gebruikten we een dimensietabel waarin foutcodes en de omschrijving van die foutmeldingen waren vastgelegd. De tabelstructuur is in het Engels, dus de veldnamen waren ErrorCode en ErrorDescription.

Microsoft heeft de woorden ErrorCode en ErrorColumn als ‘keywords’ (gereserveerde woorden) gemarkeerd. Deze woorden mogen niet in veldnamen van een tabel voorkomen. Doen ze dat wel, dan dient in de SSIS package het veldnaam een andere alias te krijgen.

Een bug? Microsoft vindt van niet… en niet iedereen lijkt zich daarin te kunnen vinden. Zie dit Engelse artikel op Microsoft Connect.

SQL Server 2017: installatie en foutmeldingen

Posted on by

Vandaag is SQL Server 2017 door mij van de plank gehaald en heb ik de versie ter evaluatie geïnstalleerd. Dat installeren had de nodige voeten in de aarde en ik heb mijn machine volledig overhoop gehaald om de installatie rond te krijgen… achter af onterecht overigens. In deze korte bijdrage mijn ervaringen, zodat U wellicht niet dezelfde lessen hoeft te leren, die ik vandaag wél heb geleerd. De lessen die ik heb geleerd:

  1. Versies van verschillende Microsoft onderdelen kunnen van elkaar afwijken en met elkaar conflicteren, doordat Microsoft zelf slecht aan versiebeheer doet. Voorbeeldje maar: SQL Server komt met een bepaalde versie van Microsoft Visual C++ 2015 Redistributable. De versie op mijn machine had een hoger versienummer, waardoor de installatie de foutmelding VS Shell installation has failed with exit code 1638 gaf. Als reactie hierop is Visual C++ 2015 Redistributable door mij gedeïnstalleerd.
  2. Hoewel de foutmelding erg vaag is, zoals Microsoft het betaamt, wordt wel heel veel in log bestanden vastgelegd in de folder C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log. Lees bij foutmeldingen altijd de logs door… dat scheelt een hoop gezoek. Had mij veel tijd gescheeld.
Al met al is de installatie gelukt en kon ik dus weer door.

SQL Server auto statistics update

Posted on by

Query performance leunt zwaar op SQL Server statistieken. Goede statistieken dragen bij aan een betere performance van query’s. Het belang van SQL Server statistieken is dermate groot, dat ik er een bijdrage in het Nederlands aan wijd.

Wat zijn statistics?
Statistics voor query performance tuning zijn objecten die statistische informatie bevat over de verdeling van waarden in één of meer kolommen van een tabel of indexed view. De query optimizer (de interne tool die SQL Server gebruikt om query plannen te maken) gebruikt deze statistieken om de kardinaliteit, of het aantal rijen, in het query resultaat te schatten. Deze schatting van de kardinaliteit biedt de query optimizer de mogelijk om een optimaal query plan te maken, zodat de performance van de query zo optimaal mogelijk wordt (vertaald uit dit document).

Aanzetten van automatisch bijwerken van statistics op de database
De meeste SQL Server gurus raden aan om statistics te gebruiken. SQL Server geeft de mogelijkheid om de statistics standaard aan te maken bij het aanmaken van tabellen en/of indexed views en geeft de mogelijkheid om deze automatisch bij te laten werken. Via de eigenschappen (properties) op database niveau kunnen in het tabblad ‘Options’ de opties ‘Auto create statistics’ en ‘Auto update statistics’ aan worden gezet.

Automatisch bijwerken van statistics in SQL Server 2014 en daarvoor
Dit is waar deze bijdrage daadwerkelijk om gaat. Statistics worden bijgewerkt wanneer bepaalde drempelwaarden worden bereikt. Deze drempelwaarden zijn:

  1. Een lege tabel wordt met minimaal één record gevuld
  2. Een tabel bereikt de 500 records
  3. In tabellen die al meer dan 500 records hebben, worden statistics bijgewerkt wanneer 500 records + 20% zijn gewijzigd, verwijderd of zijn toegevoegd.
Bovenstaande tabel wordt hier bij Microsoft beschreven.

Potentieel probleem
Vooral in datawarehouse databases die op de Kimball techniek steunen kunnen bovenstaande drempelwaarden tot problemen leiden. Eén van de hoekstenen van de Kimball techniek, is dat onbekende dimensiewaarden in feitentabellen (bijvoorbeeld een omzetregel in de tabel FactOmzet met een regiocode die niet in de DimRegiocode tabel voorkomt) naar een zogenaamd ‘onbekend’ record in de dimensietabel verwijst. Elke dimensietabel in deze techniek heeft een ‘onbekend’ record waarnaar verwezen wordt, indien het dimensierecord niet in de dimensietabel voorkomt.

Hoe kan dat fout gaan? Tijdens het aanmaken van de dimensietabel wordt eerst een ‘onbekend’ record toegevoegd. Daarna wordt de bestaande dimensiewaarden in de tabel ingelezen. Na het inlezen van het eerste (‘onbekend’) record worden de table statistics bijgewerkt. De statistics geven nu aan dat de tabel uit één record bestaat. Wanneer er niet meer dan 498 dimensiewaarden worden toegevoegd, worden de statistics volgens de tabel ‘Automatisch bijwerken van statistics’ niet opnieuw bijgewerkt, omdat de drempelwaarden niet gehaald worden. Vooral wanneer de dimensietabel wat groter is (maar niet meer dan 500 records heeft), kan dit bij het opzetten van een query plan door de query optimizer tot potentiële problemen leiden.

De oplossing
Er zijn een aantal oplossingen voor dit potentiële probleem:

  1. Werk de statistics handmatig bij via de functie UPDATE STATISTICS (staat hier beschreven). Deze optie optie heeft mijn voorkeur.
  2. Zet Trace Flag 2371 aan (staat hier beschreven). Ik adviseer zelf om in een productie omgeving nooit met trace flags te werken, omdat het aan- of uitstaan van trace flags niet goed zichtbaar is.
Automatisch bijwerken van statistics in SQL Server 2016 en verder
In SQL Server 2016 wordt Trace Flag 2371 standaard aangeboden. Deze trace flag heeft geen echter geen gefixeerde drempelwaarde, zoals SQL Server 2014 en daaronder kent. Microsoft geeft aan dat hoe groter de tabel is, hoe minder records relatief hoeven te worden bijgewerkt om de statistics bijgewerkt te krijgen. Dat kan een nadeel zijn en het is daarom zaak om query performance goed in de gaten te houden en eventueel tijdig de statistics handmatig bij te werken.

Tenslotte nog dit ter herinnering…
Statistics op tabellen en indexed views worden pas bijgewerkt wanneer SQL Server een (nieuw) query plan op de tabel maakt. Bij het samenstellen van een nieuw query plan stelt SQL Server pas vast dat de statistieken niet meer up-to-date zijn en worden (wanneer de optie ‘Auto Update Stats’ aanstaat) de statistieken bijgewerkt. Om zelf te kunnen zien of statistics goed zijn bijgewerkt kan van de functie DBCC SHOW_STATISTICS gebruik worden gemaakt. Deze functie wordt hier beschreven.

Meer informatie
Meer informatie is via de volgende Microsoft links beschikbaar in het Engels.

SQL Server Statistics (concepts)
Controlling autostat auto update statistics behavior in SQL Server
Statistical maintenance functionality: autostats in SQL Server
List of trace flags in SQL Server

… en speciaal voor SQL Server 2016

Default auto statistics update treshold change for SQL Server 2016

Kill stupid users!

Posted on by

Tijdens mijn dagelijkse rondgang langs diverse SQL Server websites, werd mijn aandacht door deze pagina gepakt. Kenneth Fisher vertelt op deze pagina hoe kan worden voorkomen dat iemand onbedoeld wijzigingen op een tabel maakt. Het is een wat grove, maar bijzonder grappige manier om iemand met zijn vingers van tabellen af te laten blijven.

Kenneth waarschuwt er wel voor dit script NOOIT in productie te gebruiken. Dat lijkt mij een prima plan!

Bugje in grafische weergave van execution plan in SQL Server 2016, SP1

Posted on by

Vertaald uit: https://www.brentozar.com/archive/2016/11/sql-server-2016-sp1-bug-graphical-execution-plans

Brent Ozar en zijn kornuiten hebben een bugje gevonden in de grafische weergave van execution plans in SQL Server 2016, SP1. Blijkbaar heeft Microsoft besloten een aantal nieuwe attributen aan de execution plans toe te voegen, waardoor dit bugje is ontstaan.

Voor de ‘Die Hards’ onder ons: er zijn wat Microsoft Connect items voor bugjes in de laatste versie aangemaakt:

in de originele link (van waaruit dit artikel is vertaald), legt Brent Ozar de bug uit.

SQL Server 2016, service pack 1 is beschikbaar!

Posted on by

Microsoft heeft SQL Server 2016, service pack 1 beschikbaar gesteld. Op deze pagina vindt u al het nieuws over de nieuwe service pack 1!

Het grootste nieuws in deze service pack is dat Microsoft alle features uit de Enterprise Edition van SQL Server 2016 in de Standard Edition beschikbaar heeft gemaakt. Is er dan geen verschil meer tussen de twee edities? Zeker wel! Ter herinnering: de Standard Edition ondersteunt nog steeds SQL Servers tot 4 sockets, 24 cores en 128 Gigabye of geheugen! Heeft u meer van dit nodig, dan heeft u de Enterprise Edition van SQL Server 2016 nodig!

Met de features uit de Enterprise Edition nu beschikbaar op in de Standard Edition, wilt u mogelijk uw onderhoudsplannen nog eens nalopen en kijken of daar nog verbeteringen in zijn aan te brengen!

Eindelijk… alle TRACE flags bij elkaar

Posted on by

Link: https://msdn.microsoft.com/en-us/library/ms188396.aspx

Trace flags worden gebruikt om tijdelijk specifieke SQL server eigenschappen aan te zetten of bepaald gedrag van de SQL Server juist uit te zetten. Vaak worden trace flags gebruikt bij het analyseren van performance problemen bij een SQL server of om een analyse van een stored procedure te maken om te kijken waar een fout in een stored procedure gezet. Tot nu was de documentatie over trace flags voor SQL Server over de MSDN website verspreid. Sinds kort heeft Microsoft een pagina aan alle trace flags gewijd. De link naar deze pagina vindt u bovenin deze pagina.

Wees u ervan bewust dat de functionaliteiten van trace flags kunnen wijzigen en niet alle trace flag opties binnen elke SQL Server versie beschikbaar is. Daarnaast waarschuwt Microsoft ervoor dat de trace flag functie niet per definitie in toekomstige versies van SQL Server wordt ondersteund.