Sql Select = 100% cpu

[erwink]

hallo ik heb een rss feed gemaakt.

daarin word de volgende query losgelaten op een mssql server

SELECT TOP 10 capcode, message FROM p2000_2006 ORDER BY datumtijd DESC

de database is 1.8gig groot is ontstaan in een halfjaar tijd dus groeid aardig gestaag door.

als deze query draaid gaat de cpu steeds naar 100% voor een seconde of 4 a 5.hoe meer aanvragen hoe langer op 100%.

opzich logisch, maar volgens mij moet het een stuk sneller kunnen.

de cpu is een amd athlon(Barton) 1.2 Ghz met 2 gig geheugen


Ik heb het script van een forum gebruikt en deze rss gaat zeker niet naar 100% maar naar 20.
allen het forum gebruikt geen top 10 optie bv.

[sig69]

Heb je het execution plan al eens bekeken? Wellicht valt er met een index veel winst te halen.

[Viper®]

Kan te maken hebben met je indexes.

Draai anders een dag lang een sql profiler trace
run je top 10 script een aantal keren.

en laat hem op basis daarvan de beste indexes kiezen m.b.v. de index tuning wizard


Wat anders een optie is als de belasting te hoog wordt is om een aparte tabel top10 te maken die je bijvoorbeeld elk half uur update. Dan hoeft je alleen een select te doen op die tabel wat geen drol kwa tijd kost.

elk uur synct die dan zeg maar.

[ Voor 37% gewijzigd door Viper® op 13-10-2006 15:40 ]

[The Eagle]

Dikke kans dat je niet selecteert op primaire keys. Dan moet ie ws. een full table search door, en ja, dat kost (CPU) tijd

[Yoozer]

Wat je ook kunt doen is die feed cachen; ik kan me niet voorstellen dat er per seconde een nieuw bericht in komt. Zet 'm maar eens op om de 15 minuten of zo, da's heel acceptabel en scheelt een hoop requests.

[whoami]

Leg eens een clustered index op datumtijd.

Dikke kans dat je niet selecteert op primaire keys

Primary keys hebben hier niets mee te maken.
Indien de topicstarter geen index heeft op datumtijd , dan wordt er idd een table-scan gedaan. Een index op dat veld leggen, zal dus al heel wat helpen, aangezien er dan idd sneller kan gezocht worden.
Echter -en aangezien ik TOP zie, ga ik er vanuit dat het hier om MS SQL Server gaat- zal een clustered index op datumtijd nog sneller zijn.
Een clustered index bepaald nl. de fysieke opslagvolgorde van de records. Als er dus een clustered index (en dan nog liefst DESC) op dat veld gelegd wordt, dan weet SQL Server gewoon waar hij z'n eerste record moet ophalen (het begin), en waar hij moet stoppen. (10 records verder). Dit zal dus aanzienlijk schelen in I/O en in CPU.

Let er wel op dat een tabel slechts 1 clustered index kan hebben. Als je dus al een clustered index hebt op je PK (default), dan moet je die eerst eens ff wegdoen. Echter, wees wel indachtig dat dit gevolgen kan hebben voor je performance van je andere queries.

[ Voor 92% gewijzigd door whoami op 13-10-2006 15:45 ]

[Viper®]

Yoozer schreef op vrijdag 13 oktober 2006 @ 15:41:
Wat je ook kunt doen is die feed cachen; ik kan me niet voorstellen dat er per seconde een nieuw bericht in komt. Zet 'm maar eens op om de 15 minuten of zo, da's heel acceptabel en scheelt een hoop requests.

Naast mijn optie is dit ook een goeie mogeiljkheid ja.

Afhankelijk van je website. Met asp.net kun je de query in een dataset opslaan in de applicatie cache, deze wordt voor elke bezoeker gebruikt.

[erwink]

ik heb nu een index aangemaakt.

elke seconde kan er zeker wel wat wijzigen. Is namelijk een p2000 monitor systeem(alarmeringen).

ik als de indexen gemaakt zijn eens kijken wat de winst is. ik vermoed dat inderdaad de gehele database steeds doorlopen word.

[whoami]

Maak er eens een clustered index van; ik vermoed dat die datumtijd kolom een zeer geschikte kandidaat daarvoor is; zeker als je veel sorteert op dat veld, en veel range-selects doet op dat veld.
(zie m'n eerdere reply)

(Tenzij het zo is dat het datumtijd veld regelmatig gewijzigd wordt (update). Dan zal het nadelig zijn om die index als 'clustered' te definieren, aangezien dat als effect zal hebben dat je fysieke opslagvolgorde iedere keer gewijzigd wordt -> overhead / page splits, etc...

[ Voor 39% gewijzigd door whoami op 13-10-2006 15:50 ]

[erwink]

WOW, probleem is opgelost Thanks!

de indexen zorgen er voor dat het nu echt razend snel is.

[RobIII]

whoami schreef op vrijdag 13 oktober 2006 @ 15:42:
Echter, wees wel indachtig dat dit gevolgen kan hebben voor je performance van je andere queries.

Let er ook op dat je inserts trager worden (hoewel dat in de praktijk vaak toch niet zo veel is, de "winst" die je haalt zit meestal in je selects en niet in je inserts in zo'n geval) omdat de records fysiek op de juiste plek worden gezet.

ernieek schreef op vrijdag 13 oktober 2006 @ 15:49:
WOW, probleem is opgelost Thanks!

de indexen zorgen er voor dat het nu echt razend snel is.

Mja, toch wel les 2 databases lijkt me zo

[ Voor 22% gewijzigd door RobIII op 13-10-2006 15:49 ]

[Viper®]

gebruik de sql index tuning wizard

[erwink]

de inserts zijn het probleem niet.

dit zijn er niet zoveel. slechts 20 a 3 per minuut.

[whoami]

Heb je 'm nu als clustered index gemaakt, of niet ?
Bekijk eens de exec plans van die query met een gewone index, en een clustered index ?
En draai ondertussen ook eens profiler.

[erwink]

is nu aangemaakt als een niet clustered

[Yoozer]

Viper® schreef op vrijdag 13 oktober 2006 @ 15:44:
Naast mijn optie is dit ook een goeie mogeiljkheid ja.

Toen ik mijn reply tikte had jij die optie nog niet toegevoegd

[whoami]

RobIII schreef op vrijdag 13 oktober 2006 @ 15:49:
[...]

Let er ook op dat je inserts trager worden (hoewel dat in de praktijk vaak toch niet zo veel is, de "winst" die je haalt zit meestal in je selects en niet in je inserts in zo'n geval) omdat de records fysiek op de juiste plek worden gezet.

Als het een clustered index betreft, en dat veld wordt praktisch nooit tot nooit gewijzigd, dan zullen inserts idd ook behoorlijk snel gaan, aangezien Sql Server direct de locatie weet waar hij het record moet plaatsen en, aangezien die index de fysieke opslagvolgorde bepaald, is de overhead voor die index behoorlijk laag.

Maar goed, TS ziet blijkbaar niet in wat het voordeel van een clustered index in dit geval kan zijn.

[_Thanatos_]

Kijk uit met clustered indexen, dat je die niet zo maakt dat elk nieuw record aan het begint van de tabel moet komen te staan. Dan moet hij af en toe een hele berg data gaan opschuiven, hetgeen nog veel meer tijd kost.

[whoami]

Idd, daar doelde ik op met m'n page splits.
Echter, AFAIK gaat SQL Server niet zomaar met z'n data gaan schuiven; hij zal eerder een nieuwe page gaan maken denk ik. Het echt 'opschuiven' zal hij maar doen bij een DBCC reindex dacht ik.
Echter, dit ben ik niet 100% zeker.

Iig, een clustered index op dat veld is imho best intressant (zeker als het veld nooit aangepast wordt), maar, een DESC clustered index -zoals ik eerder zei- is idd niet zo interessant, en wel om de reden die jij hier aanhaalt.

Daarnaast kan je ook nog spelen met de fill-factor.

[SKiLLa]

Is die 1.8 GB wel echt data ? Staat je logging wel op 'simple' of niet ?
En hebbie de DB al een ge-shrinkt ? Niet dat het direct je select query
sneller maakt, maar als van die 1.8 GB 90% 'leeg' is, scheelt het veel globale performance

PS: Enne "SET NOCOUNT ON" in je query gebruiken; en eventueel ook nog SELECT ... FROM .. WITH (NOLOCK) indien aanvaardbaar ...

[ Voor 21% gewijzigd door SKiLLa op 13-10-2006 16:33 ]

[ACM]

SKiLLa schreef op vrijdag 13 oktober 2006 @ 16:32:
Is die 1.8 GB wel echt data ? Staat je logging wel op 'simple' of niet ?
En hebbie de DB al een ge-shrinkt ? Niet dat het direct je select query
sneller maakt, maar als van die 1.8 GB 90% 'leeg' is, scheelt het veel globale performance

PS: Enne "SET NOCOUNT ON" in je query gebruiken; en eventueel ook nog SELECT ... FROM .. WITH (NOLOCK) indien aanvaardbaar ...

Dat soort maintenance werk en semi-query-tunables zijn soms vast zinvol, maar als je een index mist ga je er weinig mee winnen en blijft het een lapmiddel Dus ik zou dat soort tips toch eerst geven en als het dan nog steeds niet snel genoeg is, beginnen over manieren om de query-executie nog iets sneller te maken.

[erwink]

db word elke dag ge-shrinkt. en dus een echte 1.8gb aan data

ik had twee indexen aangemaakt maar ik had er maar 1 nodig volgens de analyzer.

allen op datum. ik had ook capcode gedaan.

Het werkt nu werkelijk als een tierelier.

[whoami]

SKiLLa schreef op vrijdag 13 oktober 2006 @ 16:32:
Is die 1.8 GB wel echt data ? Staat je logging wel op 'simple' of niet ?
En hebbie de DB al een ge-shrinkt ? Niet dat het direct je select query
sneller maakt, maar als van die 1.8 GB 90% 'leeg' is, scheelt het veel globale performance

Hmm, niet helemaal mee eens...
Stel, je maakt een DB, en je geeft een initial size aan je DB file (mdf) van 1000 mb. Als je die 1000mb dan slechts voor 80% vult, dan heb je nog 20% 'groeimarge'.
Dit zorgt ervoor dat je DB file niet direct hoeft z'n size aan te passen, waardoor je dus minder gefragmenteerde data zult hebben.

db word elke dag ge-shrinkt. en dus een echte 1.8gb aan data

Als die DB iedere dag groeit, is iedere dag shrinken dus niet echt goed voor je performance.

Check ook sqlserverperformance.com

[ Voor 13% gewijzigd door whoami op 13-10-2006 16:46 ]

[SKiLLa]

Dat soort maintenance werk en semi-query-tunables zijn soms vast zinvol, maar als je een index mist ga je er weinig mee winnen en blijft het een lapmiddel Dus ik zou dat soort tips toch eerst geven en als het dan nog steeds niet snel genoeg is, beginnen over manieren om de query-executie nog iets sneller te maken.

Het is ook als aanvulling bedoelt; het is sowieso gewoon good-practise om je queries zo licht mogelijk te schrijven; als je geen locks/rowcounts nodig hebt, moet je ze ook niet gebruiken. De performance-winst is dan wel veel kleiner dan een 'ontbrekende' index toevoegen, maar het is niet of-of, het is en-en

@whoami: ik zei ook eens shrinken; het kan veel schelen. Heb vaak genoeg DBs gezien die maar onnodig bleven groeien terwijl er amper data instond; lees: de .mdf was vele GBs terwijl er amper 20 MB aan data in stond ... (al betekent dergelijke issues fixen vaak wel meer dan alleen shrinken).

Een gezonde DB op een goed geconfig'de server heeft het in principe niet nodig, maarja, de wereld is niet perfect ...

[ Voor 24% gewijzigd door SKiLLa op 13-10-2006 16:56 ]

[RobIII]

whoami schreef op vrijdag 13 oktober 2006 @ 16:25:
Idd, daar doelde ik op met m'n page splits.
Echter, AFAIK gaat SQL Server niet zomaar met z'n data gaan schuiven; hij zal eerder een nieuwe page gaan maken denk ik. Het echt 'opschuiven' zal hij maar doen bij een DBCC reindex dacht ik.
Echter, dit ben ik niet 100% zeker.

SQL Server Books online:
[q]
Clustered indexes are not a good choice for:

• Columns that undergo frequent changes
This results in the entire row moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.


Dus wat je zegt klopt wel; het is alleen bij veel inserts waar je er onder zult lijden.

[whoami]

Maar, veel inserts in een tabel waarbij het veld dat de clustered index bevat, steeds 'sequentieel' groeit, daar zullen er geen data verschuivingen plaatsvinden. (Bv, indien de clustered index op een identity ligt, of op een datum die nooit wijzigt).

[RobIII]

whoami schreef op vrijdag 13 oktober 2006 @ 17:13:
Maar, veel inserts in een tabel waarbij het veld dat de clustered index bevat, steeds 'sequentieel' groeit, daar zullen er geen data verschuivingen plaatsvinden. (Bv, indien de clustered index op een identity ligt, of op een datum die nooit wijzigt).

Dan begrijpen we elkaar
Als (voor de duidelijkheid) je echter een clustered index op (bijv.) een date field zou gooien en je gaat dan "random" datums inserten (dus niet sequentieel) dan wordt het dus wél "duur".

Maar wiebenik en ik begrijpen mekaar alvast