[MSSQL] Autoclose DB X - invloed op performance DB Y?

[Coltrui]

Hoi,

Op een van onze produktieservers staat MS SQL 2k8R2. Bij de werkzaamheden komen geen ingewikkelde query's aan te pas, maar toch bemerken we af en toe een vertraging die na een aantal minuten weer wegebt.

Nu heeft de onverlaat die de instantie installeerde, ook de reporting service mee geïnstalleerd, terwijl we daar echt geen behoefte aan hebben. Na wat snorren in de windows event viewer, zag ik dat de databases die gebruikt worden voor reporting service om de zoveel tijd geopend worden. Blijkbaar staat de optie 'autoclose' voor deze databanken standaard op 'True'.

Mijn vraag is simpel: heeft de autoclose optie van database X invloed op de performance van database Y die zich op dezelfde server/instantie bevindt, of moet ik een andere oorzaak gaan zoeken?

Edit: met 'vertraging' bedoel ik dat query's die normaal 100ms in beslag nemen, plots een seconde nodig hebben en dit gedurende een paar minuten.

[ Voor 8% gewijzigd door Coltrui op 06-08-2015 15:43 ]

[KoeKk]

Ja dat kan invloed hebben, als er op dat moment 5 databases geopend en deels in het geheugen geladen worden heeft dat invloed op je Disk IO, cpu load en werkgeheugen gebruik,

Maar is het deinstalleren van reporting services dan niet een betere oplossing?

[edit]
Oh en pas Auto-close aan op de 'model' database, dan worden nieuwe databases ook niet meer op auto-close=true gezet

[ Voor 21% gewijzigd door KoeKk op 06-08-2015 16:03 ]

[Coltrui]

Ik heb ondertussen de service gedeactiveerd en autoclose voor beide DB's afgezet. Ik wilde gewoon confirmatie of die stomme setting van zulke invloed kan zijn op andere databases.

Het gaat tenslotte om een klant met een quasi real-time softwaresysteem dat dus in principe geen greintje vertraging kan hebben. En morgenvroeg moet die weer aan de slag, hopelijk zonder deze tijdelijke delay.

[KoeKk]

Heb je ook hardware monitoring op deze machine aanstaan? Kan wel handig zijn om te kijken wat de hardware ervaart als er 'trage queries' zijn.

[Coltrui]

Morgen draaien perfmon en SQL profiler mee. Ik ben softwareman, dus ervaringen met goede HW monitoring tools zijn welkom

[MAX3400]

Vergeet even niet dat perfmon en profiler ook een invloed kunnen hebben op wat er gerapporteerd wordt. Een server die 24/7 al op 85% of meer aan CPU & RAM staat, kan dan met pieken waarden gaan geven in perfmon waarvan alle bellen gaan rinkelen en jij "dus" denkt dat die bak helemaal verkeerd is ingericht.

Profiler is een eleganter tool omdat die puur en alleen kijkt naar hoe de databases eraan toe zijn. Dat hier ook smerige zaken uit gaan komen, dat is altijd mogelijk maar dat in niet expliciet te wijten/herleiden aan de hardware/software die het platform vormt voor de SQL-instance.

Klinkt allemaal een beetje tegenstrijdig maar ga eerst een voldoende met profiler aan de slag, kijk naar deadlocks, non-indexed queries/tables, een overload een non stored procedures etc. Als je daar echt niets geks in gaat vinden, is perfmon (of andere tooling voor het hele OS en de applicaties) pas echt zinvol.

[Coltrui]

Wat RAM-gebruik betreft maak ik me niet snel zorgen. MS SQL heeft de neiging om al het beschikbare geheugen op te eisen, dus dat is geen graadmeter. Het vermoeden lag bij locking inderdaad. Zeer waarschijnlijk gaan tijdens de 'dip' query's aantoonbaar langer duren. Alleen weet ik niet of we een ander proces te zien gaan krijgen in profiler als die autoclose van de reporting service DB's de boosdoener is.

[nescafe]

Als je vermoedt dat het in query's zit die normaal gesproken 100ms nodig hebben en nu een veelvoud ervan, zou ik in Profiler even een filter op Duration >= 250 zetten. Dat scheelt een hele zut aan events die je op voorhand al kunt uitsluiten (het scheelt memory / disk space en je kunt de profile wat langer laten draaien, evt onbeheerd).

[Coltrui]

Thanks. Zoek ik even op. Ik heb zo'n standalone versie die ook voor de express versie werkt

[CMD-Snake]

Coltrui schreef op donderdag 06 augustus 2015 @ 16:45:
Wat RAM-gebruik betreft maak ik me niet snel zorgen.

Je schrijft de Express Edition te gebruiken, die heeft een limiet van 1Gb RAM maximaal per instance, alleen de Express with Advanced Services heeft een limiet van maximaal 4Gb RAM per instance. De data engine kan nooit meer gebruiken dan die limieten. Het totaal aantal SQL Server services/processen zal wel meet verbruiken.

Afhankelijk van de grootte van de database kan je hierdoor ook performance problemen hebben omdat er minder van de database in het geheugen geladen kan worden. Bij meerdere databases in een instance wordt het dan dringen om geheugen.

MS SQL heeft de neiging om al het beschikbare geheugen op te eisen, dus dat is geen graadmeter. Het vermoeden lag bij locking inderdaad.

Eerder genoemde beperkingen gelden al voor je SQL Server. Maar sowieso is het niet verstandig om de geheugen component niet te configureren. Je moet eigenlijk doen Totaal RAM - 2Gb = aantal GB voor SQL Server.

Voor wat betreft locking, in de SQL logging kan je meteen ook al zien of er een deadlock is voorgekomen. Je kan met performance monitor in SQL Server Management Studio ook zien (live) welke queries actief zijn, hoeveel resources ze kosten en of ze staan te wachten op elkaar.

Profiler is ook een goede optie. Let wel dat je dan de juiste zaken logt. Let ook op de grootte van je trace file. Bij veel activiteit vult de log heel snel en kan vrij groot worden, zeker als je veel wilt loggen.

Kijk eventueel ook naar zaken als database tuning, als je dat niet regelmatig doet gaat de performance ook eronder lijden.

[Razwer]

CMD-Snake schreef op vrijdag 07 augustus 2015 @ 15:21:
[...]


Je schrijft de Express Edition te gebruiken,

Dat is niet wat ik expliciet lees.

Coltrui schreef op donderdag 06 augustus 2015 @ 17:37:
Thanks. Zoek ik even op. Ik heb zo'n standalone versie die ook voor de express versie werkt

Maargoed, mocht het idd SQL express zijn, dan ga ik de performance discussie niet eens aan gezien de limitaties van het product.

Overigens hebben bepaalde producten SSRS nodig om uberhaupt te functioneren maar zonder achtergrond informatie valt hier weinig over te zeggen in deze situatie.

De achterliggende hardware danwel virtualisatie (configuratie) kan een grote factor zijn in performance van een SQL server.
Een voorbeeld: Esx host met 8 cores --> virtual sql server met 8 cores + iis server met 2 cores. Dit nekt de performance.
Ook zaken als speedstep (desktop hardware/fout configged server hardware) nekt SQL performance gezien de meeste query performance op SQL pure GHZ snelheid is. Zo kan een oude 4GHZ server veel sneller zijn met SQL queries dan een hagelnieuwe 2 GHZ server.

SQL server settings niet goed hebben staan kan ook funest zijn bijv. Max Degree of Parallelism niet juist hebben staan kan ook een performance dip geven.

Zo zijn er heel veel zaken om een SQL server "basic" te tunen waarbij veel "quick wins" te halen zijn.
zie: http://blogs.msdn.com/b/i...rmance-tuning-basics.aspx

[Coltrui]

Allen dank voor de input.

Na onderzoek, blijkt dat een simpele query die we om de x aantal seconden loslaten om te zien of de connectie met de DB er nog is, de boel soms vertraagt.

Aangezien TCP hardnekkig is, kunnen we niet rekenen op het OnDisconnect event van het DBconnectie component, dus checken we elke 15 seconden de connectie met:

SQL:

SELECT @@version

Deze gaat gewoon in time out na de standaard 30 seconden. Die @@Version value, is een server property. Iemand een idee waar deze waarde wordt opgeslagen? Ik neem aan dat hier geen file access oid voor nodig is?

Ik heb zelf gezocht uiteraard en gevonden dat deze waarde voor MS SQL 2005 in een XML file zit. Hier gaat het echter om 2008R2 en daar vind ik niks over...

[MAX3400]

Coltrui schreef op donderdag 13 augustus 2015 @ 18:14:
Ik heb zelf gezocht uiteraard en gevonden dat deze waarde voor MS SQL 2005 in een XML file zit. Hier gaat het echter om 2008R2 en daar vind ik niks over...

Voor zover ik weet zit die property in de Master database van je instance. Als je daar al een timeout op krijgt, is de vraag inderdaad of dat komt omdat de file-access naar de niet-cached databased heeeeel traag is of omdat er een ander issue is waardoor de versie niet gereproduceerd kan worden.

[gekkie]

Hmm doe zelf een suffe select voor dat doel:

SQL:

SELECT 1 AS ping;

[Coltrui]

MAX3400 schreef op donderdag 13 augustus 2015 @ 19:05:
[...]

Voor zover ik weet zit die property in de Master database van je instance. Als je daar al een timeout op krijgt, is de vraag inderdaad of dat komt omdat de file-access naar de niet-cached databased heeeeel traag is of omdat er een ander issue is waardoor de versie niet gereproduceerd kan worden.

Wel, dat denk ik dus niet. Andere client apps hebben tijdens de hick-up geen problemen met de DB. Registry? XML/andere file? Er is op dat niks mis met de database volgens profiler.

gekkie schreef op donderdag 13 augustus 2015 @ 19:14:
Hmm doe zelf een suffe select voor dat doel:

SQL:

SELECT 1 AS ping;

Dat is inderdaad een oplossing. Maar Ik zoek een oorzaak - waarom is een query die niet in tabellen gaat snuffelen zo'n bottleneck, terwijl de DB gewoon perfect zijn werk doet? Het wijst op een ander probleem...

[gekkie]

Coltrui schreef op donderdag 13 augustus 2015 @ 19:26:
Dat is inderdaad een oplossing. Maar Ik zoek een oorzaak - waarom is een query die niet in tabellen gaat snuffelen zo'n bottleneck, terwijl de DB gewoon perfect zijn werk doet? Het wijst op een ander probleem...

Tsja m'n glazenbol kan momenteel even niet bij de relevante sourcecode van mssql server, wellicht maar een mailtje naar MS er aan wagen wat ze in vredesnaam geimplementeerd hebben ?
(naast wellicht met wat sysinterals tools kijken waar die mee bezig (geopende files) is en met hoeveel load op het moment dat je alleen die @@version query spamt.)

[Coltrui]

Ik waardeer wat je tussen haakjes hebt gezet. Wat daarbuiten staat is uiteraard al een week in progress.

Heb je een tip voor een goede tool die inderdaad file/registry access realtime monitort?

[gekkie]

https://technet.microsoft.com/en-us/sysinternals/bb896645 wellicht ?

(en anders zelf even door de collectie aan tools grasduinen .. er is genoeg
https://technet.microsoft.com/en-us/sysinternals/bb795535 )

[ Voor 51% gewijzigd door gekkie op 13-08-2015 23:20 ]

[Razwer]

Coltrui schreef op donderdag 13 augustus 2015 @ 19:26:
[...]
Dat is inderdaad een oplossing. Maar Ik zoek een oorzaak - waarom is een query die niet in tabellen gaat snuffelen zo'n bottleneck, terwijl de DB gewoon perfect zijn werk doet? Het wijst op een ander probleem...

lees:

Razwer schreef op zaterdag 08 augustus 2015 @ 13:33:
[...]
SQL server settings niet goed hebben staan kan ook funest zijn bijv. Max Degree of Parallelism niet juist hebben staan kan ook een performance dip geven.

Zo zijn er heel veel zaken om een SQL server "basic" te tunen waarbij veel "quick wins" te halen zijn.
zie: http://blogs.msdn.com/b/i...rmance-tuning-basics.aspx

Als ik voor Jan met de korte achternaam post onttrek ik mijzelf wel aan dit topic