[MS SQL 2005] Server ineens spontaan erg traag

[Verwijderd]

Sinds 2 weken heeft m'n database server, welke een dedicated SQL 2005 server gehost bij een goede provider is, erg grote problemen.

De server:
Intel Xeon 2,8 GHz
1 GB geheugen
Raid 5 40 GB
WIndows Server 2003
SQL Server 2005

Situatie:
Database server met 7 databases, waarvan er 2 aktief gebruikt worden voor redelijk drukke webwinkels. Deze setup heeft ruim een jaar lang zo zonder problemen gedraaid, totdat... Op deze databases draaien verder nog wat backoffice app's geschreven in VB6. De websites zijn trouwens ASP3.0. Beide websites hebben per website zo'n 4000 unieke bezoekers per dag, backoffice gebruik is te verwaarlozen.

Probleem:
Sinds 1,5e week zijn er momenten waarop de server ineens bijzonder traag wordt. Requests vanaf de webserver (een andere dedicated server) duren dan zo lang, dat het zelfs een time-out tot gevolg kan hebben. Op dat moment is het CPU verbruik geen 100%, en ook het I/O gebruik is niet abnormaal hoog. Het CPU verbruik schommelt zo tussen de 5 en 80%, volgens mijn provider die dit na kan kijken. Het CPU verbruik op een "probleem moment" is niet significant hoger dan een "goed moment".

Wat ik uitgezocht / gedaan heb:
Onderstaande zaken heb ik onderzocht/uitgevoerd sinds de problemen. De problemen kwamen uit het niets.... Mogelijke oorzaken kunnen natuurlijk een langzaam steeds groter wordende database en/of meer bezoekers zijn.
- Enkele indexen verwijderd en opnieuw aangemaakt op basis van de situatie zoals deze nu is. Verder de Database Tuning Wizard uitgevoerd, puur voor de info. Hier kwamen verder geen rare dingen naar voren. => Gebruikers merkten af en toe een snellere website, wat natuurlijk te verklaren is door iets betere indexen.
- Transactionlog groeide door de dag heen redelijk hard. Nadat het recoverymodel van full naar bulk-logged gezet was ging dit beter. Alsnog groeit hij redelijk....
- Alle hardware nagekeken, deze is goed.
- Queries die langer dan 2 sec duren geanalyseerd en verbeterd. Het vreemde is dat een query die NU 50ms duurt, NU 30.000 MS kan duren, en vise versa...
- Queries die meer dan 500 ms CPU (time?) vergen verbeterd, zelfde verhaal.
- Zondag was de situatie zo dat ik in m'n query analyser, met bovenstaande instellingen nagenoeg (lees 1 per 10 minuten) queries voorbij zag komen. Alles leek opgelost na een week lang queries verbeteren etc...
- Maar, sinds vanavond 17.00 zijn we weer terug bij af. Nu duren weer erg veel queries weer ineens meer dan 10 seconden, terwijl het andere moment ze weer 0,5 seconden duren.

Morgen avond wordt de server geupgrade naar 2 GB, en een 2e CPU.

Mijn (en dat van de provider) probleem is een beetje dat we niet meer weten wat we nu moeten doen. Is de server niet meer lekker, zijn er gigantisch veel aanvragen (dit kunnen we nergens terug vinden?), of is er toch iets anders aan de hand?

Ik heb op dit moment DBO rechten gekregen, om zelf ook wat onderzoek te kunnen doen. Ik hoop dat jullie me een beetje kunnen helpen om te achterhalen waar de problemen vandaan kunnen komen. Vanuit daar kan ik dan wel weer verder.....

[whoami]

- Hoe groot zijn je tabellen ? Hoeveel gegevens komen er per dag bij ?
- Hoe zit het met de fragmentatie van je tabellen ? Heb je al eens dbcc showcontig gedaan (of iets equivalents; dit commando wordt gebruikt in Sql Server2000, maar ik weet net of dit ook in 2005 ondersteund wordt
- Hoe ziet je maintenance plan eruit ?
- ivm je transaction logs; hoe ziet je backup plan er uit ?

Ben je trouwens 100% zeker dat het aan die queries ligt ? Heb je al gekeken wat de Profiler zegt, hoeveel reads / writes / cpu cycles er zijn bij die trage queries ? Execution plan van die queries al eens bekeken ?

[ Voor 22% gewijzigd door whoami op 19-02-2007 20:11 ]

[gorgi_19]

Doe je nog gekke dingen met locking oid?

[whoami]

Hmmm.... Dat zou ook wel kunnen... Evt excepties die optreden en transacties die niet gecommited / gerollbacked worden, waardoor er locks blijven bestaan op bepaalde tabellen ?
Echter, dan zouden die queries wel een lange tijd blijven wachten (deadlock) ipv zoals het nu is.

[gorgi_19]

whoami schreef op maandag 19 februari 2007 @ 20:14:
Hmmm.... Dat zou ook wel kunnen... Evt excepties die optreden en transacties die niet gecommited / gerollbacked worden, waardoor er locks blijven bestaan op bepaalde tabellen ?
Echter, dan zouden die queries wel een lange tijd blijven wachten (deadlock) ipv zoals het nu is.

Memory zou evt. ook nog kunnen; als ik leuke fratsen uithaal (1 GB, SQL Server 2k5 + VS2005) dan krijg je ook af en toe gigantische 'vertragingen'. Zie ook http://www.microsoft.com/.../2005/tsprfprb.mspx#EYIAC

Physical memory (RAM) running low. This causes the system to trim working sets of currently running processes, which may result in overall slowdown.

Running low on space in the system page file(s). This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. This condition may result in the whole system responding very slowly or even bring it to a halt.

[ Voor 26% gewijzigd door gorgi_19 op 19-02-2007 20:21 ]

[Verwijderd]

whoami schreef op maandag 19 februari 2007 @ 20:07:
- Hoe groot zijn je tabellen ? Hoeveel gegevens komen er per dag bij ?
- Hoe zit het met de fragmentatie van je tabellen ? Heb je al eens dbcc showcontig gedaan (of iets equivalents; dit commando wordt gebruikt in Sql Server2000, maar ik weet net of dit ook in 2005 ondersteund wordt
- Hoe ziet je maintenance plan eruit ?
- ivm je transaction logs; hoe ziet je backup plan er uit ?

Ben je trouwens 100% zeker dat het aan die queries ligt ? Heb je al gekeken wat de Profiler zegt, hoeveel reads / writes / cpu cycles er zijn bij die trage queries ? Execution plan van die queries al eens bekeken ?

- Tabellen zijn niet zo gek groot, zo'n 700 MB per database aan data. Er komen per dag niet veel gegevens bij. Houd het op 300 rows.
- dbcc showcontig gedaan, krijg mooi rapport, maar weet niet waar ik op moet letten en wat de marges hierin zijn. Dus, waar moet ik op letten, en wat te doen als iets de marge overschrijdt?
- Maintenance plan:
1:00am index rebuild / update statistics
1:15am maintenance jobs voor backups data+logfile
3:00am sql log cleaner (verwijderd 4dgn oude backupfiles op schijf)
4:00am rarjob / copyjob naar andere server

Via de profiler gekeken, de waarden bij de trage queries:
CPU: 25 - 100
Reads: 1150
Writes: 0

Wat betekenen deze getallen eigenlijk precies?


Met locking doe ik niets geks, maak er niet eens bewust gebruik van. Gebruik altijd RS = Conn.Execute(....).

[whoami]

DBCC showcontig geeft weer of je tabellen gefragmenteerd zijn, en of je gegevens er 'optimaal' in opgeslagen zijn (als in, zijn ze niet teveel verspreid over een onnodig aantal extents).
De scan density moet zo dicht mogelijk bij de 100% liggen;
dan kan je ook eens kijken naar de logical en de extent scan fragmentation; deze 2 percentages moeten zo laag mogelijk zijn.
Echter, als iedere tabel van jou een clustered index heeft, dan zou dit in een normaal geval redelijk goed moeten zijn.
Op welke velden liggen je clustered indexen (deze bepalen nl. de fysieke opslagvolgorde van je records, best is dus dat je geen clustered index op een GUID legt bv, aangezien dit een random waarde is, waardoor je data bij inserts iedere keer moet gereorganiseerd worden).

Wat zegt de column duration in de profiler ?
(Reads is het aantal logische lees-operaties, cpu is cpu tijd in milliseconden die nodig is om je query uit te voeren).

[Verwijderd]

- Scan Density [Best Count:Actual Count].......: 71.96% [136:189]
- Logical Scan Fragmentation ..................: 21.60%

- Scan Density [Best Count:Actual Count].......: 53.87% [334:620]
- Logical Scan Fragmentation ..................: 30.21%

Dit zijn een beetje de gemiddelden die voorbij komen.

Clusterd indexen liggen in de meeste gevallen op ID's (facnr, facregelnr, productnummer, klantnummer) etc....

Is er een tool die de tabellen weer netjes recht zet?

[Matthijs Hoekstra]

Hoe is de diskqueueing? Dus pages/sec, had ook last van trage server bleek dat de disk het niet meer trok. Een vrij grote tabel die stond te tablescannen. Index loste dat op.

Ook logfiles en data files op fysiek andere disks helpt enorm. Zie dat je 1 raid5 van 40GB hebt en das niet bevordelijk voor de snelheid. (vooral als je databases wat groeien)

[whoami]

Je maintainence plan zou die fragmentatie moeten 'oplossen'. (Reorganize data & index pages aanvinken).

[EfBe]

Al een service pack gedraaid voor SQLserver 2005 ? SP2 is vandaag RTM gegaan, wellicht nuttig die te testen op je installatie, want het lijkt me dat de engine gewoon niets staat te doen en dat terwijl hij moet wachten op requests.