[SQL2005] Performance problemen SQL Server

[Verwijderd]

Momenteel hebben voor een site 1 Microsoft SQL Server 2005 draaien en hierop draait 1 database. We lopen nu tegen performance issues aan dat deze server het niet meer aan kan.

We zijn nu aan het kijken wat de beste oplossing is en ik zou graag daar de kijk van de aanwezige proffesionals hierop willen aanhoren.

We hebben zelf in gedachte om te gaan splittsen naar meerdere databases, dus eigenlijk een database per functie gaan maken en deze verdelen over meerdere servers en dan met linked servers gaan werken.

Wat we ons afvroegen is dit een goede oplossing of zijn er andere mogelijkheden voor?

[P_de_B]

De eerste stap zou het analyseren van de performance issues zijn. Wat zorgt er nu precies voor dat de performance tegenvalt? Zonder een uitgebreide analyse kun je hier weinig zinnigs over zeggen. Kun je iets specifieker zijn?

Hou in ieder geval in je achterhoofd dat het splitsen van een applicatie over meerdere databases een behoorlijk complexe taak is. Dat doe je echt alleen als iedere andere optie uitgeput is.

[ Voor 30% gewijzigd door P_de_B op 18-03-2009 13:51 ]

[Gé Brander]

Je kan zo echt niet een antwoord verwachten als je zo weinig informatie geeft. We kunnen hier niets mee.

Je zal zelf op zoek moeten gaan wat de vertragende factor is, is dat disk, memory, netwerk, missende indexen etc. etc.

[Question Mark]

Draai als eerste de SQL Best Practice Analyzer eens.

[Jrz]

Yup.

Indexen nakijken,
Aantal queries bekijken (heel veel losse vs 1 grotere)
Profiler gebruien

Ik denk zelf niet dat het aan hardware of iets dergelijks ligt. Daar ben ik maar zelfden tegenaangelopen

[Gé Brander]

Jrz schreef op woensdag 18 maart 2009 @ 13:53:
Ik denk zelf niet dat het aan hardware of iets dergelijks ligt. Daar ben ik maar zelfden tegenaangelopen

Te weinig disk i/o is een veel voorkomend probleem. Dat is toch echt wel hardware. Maar je kan het op twee manieren oplossen:
1) zorg dat je code in orde is en je de juiste indexe gebruikt
2) meer disken toekennen aan je omgeving

Als 1 niet meer performance oplevert, dan doe je stap 2...

[Verwijderd]

We lopen al enige tijd tegen deze performance issues aan. We hebben al onze queries al goed bekeken en met de query analyzer en waarnodig hebben we deze ook aangepast, waardoor we de queries niet verder meer kunnen optimalizeren.

De indexen zijn ook al goed onder de loop genomen en ook waarnodig bijgemaakt of weggehaald.

Het probleem is dat we diverse tabellen (ca 50 stuks) hebben waar al 20 tot 30 inserts per seconden opgedaan worden om nog niet over de selects en updates te spreken. Op het moment zit er 32 gb aan intern geheugen in de machine en draaien op 4 intel xeon quad core processors.

We hebben nu bepaalde functies van de site moeten uitschakkelen, omdat als we deze wel draaien de cpu direct naar 80% schiet. Deze functies gebruiken dus erg veel reken kracht. Ik denk dat we niet aan ontkomen om te gaan schalen naar meerdere servers.

[Question Mark]

Verwijderd schreef op woensdag 18 maart 2009 @ 13:57:
Ik denk dat we niet aan ontkomen om te gaan schalen naar meerdere servers.

Meten is weten.....

Leuk dat er veel memory en CPU's in je server zit, als bv. je diskarray en diskindeling waardeloos geconfigureerd is presteert het nog niet. Je zult echt eerst je bottleneck moeten bepalen.

[Verwijderd]

Ik heb zelf nog wat op google gezocht ik heb in het volgende document veel informatie gevonden over scaling:
http://msdn.microsoft.com/en-us/library/aa479364.aspx

Er mag een slotje op deze topic

[P_de_B]

Verwijderd schreef op woensdag 18 maart 2009 @ 15:41:
Er mag een slotje op deze topic

Vaak als je een vraag op GoT stelt wordt er niet direct een antwoord gegeven, er wordt al snel doorgevraagd naar de achterliggende situatie. Dan kan soms vervelend zijn, immers je wilt gewoon snel een antwoord in de oplossingsrichting die je zelf al bedacht had. Soms kan dat toch ook handig zijn omdat zulke reacties je op een andere manier naar je probleem kunnen laten kijken. In dit geval krijg je deze reacties ook, en ook terecht lijkt me. Je zult eerst de bottleneck (geheugen, IO, CPU, slechte queries, outdated statistieken, een te groot transactielog, slechte netwerkperfomance, indexen etc) moeten vaststellen voordat je aan een oplossing gaat werken.

Je hebt nu zonder de echte bottleneck te kennen als een oplossing (meerdere servers) bedacht terwijl je echt niet zeker weet of dat wel is wat je nodig hebt. Dat lijkt me kortgezegd niet handig. Een applicatie over meerdere databases verdelen is echt, echt, echt geen eenvoudige klus. Ik denk dat je nog wat meer tijd moet steken in het exact identificeren van het probleem, en dan moet gaan zoeken naar een oplossing.

[Verwijderd]

P_de_B schreef op woensdag 18 maart 2009 @ 15:51:
[...]

Vaak als je een vraag op GoT stelt wordt er niet direct een antwoord gegeven, er wordt al snel doorgevraagd naar de achterliggende situatie. Dan kan soms vervelend zijn, immers je wilt gewoon snel een antwoord in de oplossingsrichting die je zelf al bedacht had. Soms kan dat toch ook handig zijn omdat zulke reacties je op een andere manier naar je probleem kunnen laten kijken. In dit geval krijg je deze reacties ook, en ook terecht lijkt me. Je zult eerst de bottleneck (geheugen, IO, CPU, slechte queries, outdated statistieken, een te groot transactielog, slechte netwerkperfomance, indexen etc) moeten vaststellen voordat je aan een oplossing gaat werken.

Je hebt nu zonder de echte bottleneck te kennen als een oplossing (meerdere servers) bedacht terwijl je echt niet zeker weet of dat wel is wat je nodig hebt. Dat lijkt me kortgezegd niet handig. Een applicatie over meerdere databases verdelen is echt, echt, echt geen eenvoudige klus. Ik denk dat je nog wat meer tijd moet steken in het exact identificeren van het probleem, en dan moet gaan zoeken naar een oplossing.

Bedankt voor deze reactie... Ik heb het document nog eens aandachtig doorgelezen en de oplossing die wij bedacht hadden is ook niet echt optimaal, omdat als je distributed queries gaat uitvoeren met joins ed. dit gigantisch veel performance gaat kosten.

Verder was er nog de optie om met peer to peer replication te gaan werken en daar load balancing overheen te zetten. Dit is ook geen makkelijke oplossing, omdat je maar op 1 server inserts en updates moet uitvoeren om corrupte data te voorkomen.

Ik denk dat het belangrijker inderdaad is zoals meerdere mensen hebben aangegeven eerst de bottleneck echt op te zoeken en misschien de manier van werken / benaderen eens verder te gaan bekijken.

In ieder geval allemaal bedankt voor snelle reactie.

[P_de_B]

Een goede website hierbij: http://www.sql-server-performance.com

[Verwijderd]

Wou via deze weg even laten weten dat we er achter gekomen zijn waar de problemen zitten. De problemen komen op dit moment door de CPU deze heeft gemiddeld 85 a 90 % ingebruik in de spits uren, ook is het interne geheugen erg kritiek.

We hebben voor het moment nog 2 database server staan waar we op het moment eigenlijk niks anders mee doen dan testen, deze machine zullen we gaan inzetten om de performance problemen op te lossen.

We hebben er voor gekozen om verticaal te gaan partitioneren, zodat de drukke sub-applicaties op een andere database server ondergebracht kunnen worden. We pakken gelijk de volledige database op de schop, dit omdat er nog al veel oude columns en tabbelen inzitten die we niet meer gebruiken.

Alle stored procedure's (ca. 600 stuks) worden herschreven en volledige nieuwe indeling van de tabbelen gemaakt, waardoor we ook weer efficienter met indexen kunnen omgaan.

[Chopper_Rob]

Houd er wel rekening mee dat je CPU omhoog kan schieten als je disk i/o niet snel genoeg is.

[Question Mark]

Chopper_Rob schreef op zondag 22 maart 2009 @ 19:27:
Houd er wel rekening mee dat je CPU omhoog kan schieten als je disk i/o niet snel genoeg is.

Kun je eens uitleggen waarom dat kan gebeuren? Ik kan me er eigenlijk geen voorstelling van maken (maar nu ben ik ook geen SQL-expert). Je zou in mijn beleving dan verwachten dat de cpu-load lager wordt, er moet dan immers op de disken gewacht worden.

[Chopper_Rob]

Zelf ben ik ook geen SQL expert, maar ik heb mij laten vertellen dat dit kan gebeuren .

Ik heb het even opgezocht en ben hier het volgende tegen gekomen.

..Utilization rates consistently above 80-90 per cent may indicate a poorly tuned or designed application. On the other hand, if you have put all the other recommendations of this book into use, they may indicate a need for a more powerful CPU subsystem. In general, I would spend a little bit of time analysing the applications before immediately going out and buying three more processors.

Spending this time experimenting to discover CPU performance problems and correcting them through software improvements will often keep you from just spending money on a more powerful CPU that only covers up poorly written software for little or no time.

If you do see high CPU utilization, you will then want to monitor Processor: Per cent Privileged Time. This is the time spent performing kernel level operations, such as disk I/O. If his counter is consistently above 80-90 per cent and corresponds to high disk performance counters, you may have a disk bottleneck rather than a CPU bottleneck...

[P_de_B]

Dat kan inderdaad, maar ook in deze quote zie je het belangrijkste punt: je moet analyseren waar je bottleneck zit, anders kun je geen zinnige oplossing bedenken.

[axis]

Meten is weten idd.. Maar ik moet toegeven dat ik na 10 jaar (deels) SQL beheren nog steeds elke dag dingen bijleer over performance tuning van SQL, dat is echt een vak apart.. (Ik ben sysadmin, geen dba).

Hier wel een voorbeeldje van de dingen die ik nu grafiek, om de performance van een server over tijd in vogelvlucht te zien. Misschien heb je er wat aan:


Heb vandaag nog wat counters moeten resetten zoals je kunt zien.

Vooral specifieke SQL zaken zoals batch requests/sec en de temp tables kunnen je wijzer maken, en zeker in de aanloop naar een probleem is het handig om te zien wie er als eerste opspeelt (disk queue, cpu, etc). Als je erg veel full scans hebt, kan het zijn dat je nog eens goed naar je indexen moet kijken.. Profilen, profilen en nog eens profilen, en analyseren maar.

Sowieso installeer je als je slim bent de laatste CU, om bugs op te lossen zoals de tokenpermcache problemen (die volgens mij nog in SP2 zaten), en zijn er bepaalde tweaks zoals een aantal tempdb datafiles aanmaken groter of gelijk aan het aantal cpu's, etc, dat kun je als het goed is allemaal uit MS' whitepapers halen. Maarja, je moet maar zin hebben om al die honderden (of duizenden) pagina's door te spitten.

Je kunt natuurlijk ook een consultant inhuren om dit soort problemen eens in kaart te brengen of op te lossen. Kost je een boel centjes, maar dat betaalt zich meestal vrij snel terug.

[ Voor 5% gewijzigd door axis op 25-03-2009 00:17 ]

[_Arthur]

axis schreef op woensdag 25 maart 2009 @ 00:15:
Vooral specifieke SQL zaken zoals batch requests/sec en de temp tables kunnen je wijzer maken, en zeker in de aanloop naar een probleem is het handig om te zien wie er als eerste opspeelt (disk queue, cpu, etc). Als je erg veel full scans hebt, kan het zijn dat je nog eens goed naar je indexen moet kijken.. Profilen, profilen en nog eens profilen, en analyseren maar.

Wat in jouw voorbeeld-gegevens opvalt is het hoge aantal full table scans. Verder ziet het er niet echt spannend uit, op de maintenance/backup zaken die 's nachts plaats vinden.

Om iets met disk queue length (in general) te doen, moet je ook weten hoeveel spindels/disks er in de server zitten. Want een disk queue length van 16 gemiddeld is weinig zeggend als er 64disks voor de database gebruikt worden (als voorbeeld). Maar wel als het er maar 2 of 4 zijn. (dit voor de TS).

Maar meten is weten en met perfmon kan je genoeg meten op een Microsoft server. Het beste is meerdere te creeeren en deze te laten loggen. De ene met een tijdsinterval van 1sec en andere met 5 of 60secs voor het totaal plaatje.

[Gomez12]

Tja, persoonlijk zou ik niet snel kiezen voor een interval van 1 sec. Je logfiles lopen dan al snel de spuigaten uit, het interpreteren is een crime ( je ziet waarschijnlijk heel veel spikes van korte veeleisende query's die op het totaal plaatje niets uitmaken ) en als ergste heb je een kans dat je logging zo veel load gaat veroorzaken dat die het probleem alleen maar erger maakt ( als ik het zo snel tel dan logt axis 28 dingen, dat allemaal per seconde vertoont wel spikes na een uur ja, puur en enkel het verwerken van die loggingsdata gaat dan gewoon al problemen geven )

Zet ze eerst eens gewoon op 1x per 5 minuten ofzo zodat als je een spike ziet je ook weet dat er iets mis is over een langere periode, met zo'n interval kan je historische data opbouwen zodat je na een paar dagen kan gaan kijken waar het probleem echt in zit, dan kan je altijd nog extra performance monitor erbij zetten die specifiek op 1 ding meer logt.

Maar bovenal zou ik je eerst aanraden om gewoon wat historische data op te bouwen over hoe het systeem functioneert, want zonder historische data zit je misschien naar heel verkeerde dingen te kijken.