[MS-Sql] Multi-cpu / snelheids probleem

[Roel Broersma]

Ik heb op een Multi-processor server (SERVER-A), IIS draaien met een Access database.
De Access database groeit bijna 1MB per dag dus heb ik er een multi-processor server (SERVER-B) bij gezet, dit is een Windows 2003 Enterprise met SQL Server 2000 SP3.

Wanneer ik veel queries/data opvraag (ongeveer 2700queries met een loopje), deed de SERVER-A dit met de Access database ongeveer binnen een minuut. Op SERVER-A zag ik dan (bij Task manager) 2 CPU's gedurende 1minuut op 80% staan.

Wanneer ik nu hetzelfde doe met de aparte SQL database (dus IIS op SERVER-A en SQL op SERVER-B), dan duurt het minstens 1,5minuut, ik krijg dan ook de script timeout (90sec.). Ik zie op de SERVER-B (sql server) ook dat deze in feite maar 1 CPU gebruikt, hij wisselt ze als het ware af.
20sec. CPU-1, daarna een tijdje CPU-2, etc...
Ik heb wel in de eigenschappen van SQL-Server staan dat hij 2 CPU's moet gebruiken EN het vinkje BOOST SQL ON WINDOWS SERVERS aan.

Is dit normaal ? TCP/IP overhead ? of wat anders ? (ik gebruik dezelfde connectie, gebruik SQL authorisation i.p.v. Windows authentication)

[ Voor 8% gewijzigd door Roel Broersma op 03-01-2004 17:50 ]

[Annie]

SQL Server maakt, afaik, niet gebruik van meerdere processors door (simpel gezegd) de helft van het werk bij de ene processor neer te leggen en de rest bij de ander. De query optimizer kijkt of een query voordeel kan behalen door deze parallel (over meerdere threads) uit te voeren, zo niet dan zal deze gewoon via 1 thread worden afgehandeld. Wanneer een query geschikt is weet ik ook niet, maar het lijkt mij dat vooral 'complexe' queries waarbij veel tussenresultaten cq. data komt kijken zich lenen hiervoor en ik vermoed dus dat dat in jouw geval niet zo is.

offtopic:
p.s. mag ik vragen waarom je via een scriptje 2700 (!!) queries wil uitvoeren?

[djluc]

Die 2700 query's zijn waarschijnlijk gewoon om deze tests te doen, ik neem aan dat dit niet een normaal scriptje is.

[Roel Broersma]

Maar zo te zien gebruikte Microsoft Access dus wel 2 CPU's voor het uitvoeren van Queries.

Er zijn dus (veel) gevallen waarin Microsoft Access sneller is dan SQL Server ?

[Annie]

Roel Broersma schreef op 05 januari 2004 @ 00:07:
Maar zo te zien gebruikte Microsoft Access dus wel 2 CPU's voor het uitvoeren van Queries.

Ik denk eerder dat 1 processor werd gebruikt door Access en de ander door IIS (of andere processen), maar misschien dat een windows-kenner daar meer over kan vertellen.

[Verwijderd]

Wat moeten de 2700 query's uitvoeren?

[Verwijderd]

je besturingssysteem weet dat er 2 processoren aanwezig zijn en zullen de workload verdelen. Niet het ene proces gaat over 1 proc. en het andere proces gaat over de andere proc. Wat hij gedaan heeft heet SMP Symetric Multi Processing.
Het is zo, zonder query details enzovoorts niet aan te geven waarom sql langzamer is. Dit kan ook liggen aan je database ontwerp. nvarchar ipv char, int ipv tinyInt,en ga zo maar door. Verkeerde keuzes leveren in SQL enorm grote verschillen op in gereserveerd geheugen etc.... of je indexen zijn niet goed...legio mogelijkheden.

[Roel Broersma]

Ik had de database (in MS ACCESS gecomprimeerd ongeveer 30MB) geconverteerd naar MS_SQL_2K.

Toen keek ik naar het snelheidsverschil.

Dit alles zonder er rekening mee te houden dat tijdens het converteren inderdaad AL je indexen verloren gaan, Autonummers worden weggehaald, BOOLEAN/BIT worden veranderd, etc.

Ik ben dus nu het 'datamodel' aan het SQL' len...

Vroeger in Access kon je van je tbl_orders, je order_id bijv. laten indexen. Hoe kan je dit nu simpel/snel in SQL doen ? Toch niet alleen maar via de FULL-TEXT Search ?

[off-topic]
Die 2700 queries waren om te kijken of er aan een order, welke op meerdere facturen kan voorkomen (en meerdere orders op 1 factuur), betalingen zijn weggestreept, hetzij door bank-gegevens, credit-facturen, (maar ook storno op betalingen, etc.). Het ging hier dan als test even om 2700 orders.
Er was voor 2700 queries gekozen omdat ACCESS-SQL niet zo makkelijk te schrijven is en minder kan dan MS_SQL. Het begint al met LEFT JOIN [access] <--> ..*=... [ms_sql], TOP/LIMIT, ga zo maar door... Nu kan dat gelukkig in 1 queries strax.
[/off-topic]

[LauPro]

Access zal met dit soort relatief kleine databases idd wel sneller zijn, alleen je loopt tegen de limiet van 2 GB aan bijvoorbeeld. Daarnaast gaat access over z'n nek als je er teveel op los gooit.

[_Thanatos_]

Het is misschien een gek idee, maar heeft SERVER-B wel echt twee CPU's, en niet gewoon een P4 met Hyperthreading? En staat in de configuratie van de SQL server wel aangegeven dat ie alle beschikbare CPU's mag gebruiken?

Leek weetje voor je: je kan MSSQL server ook sneller maken door in de machine een shitload aan geheugen te stoppen. Het lijkt dan alsof ie een geheugenlek heeft, maar het is gewoon de cache in actie als sqlserver.exe 1,5GB geheugen opeet

[ Voor 38% gewijzigd door _Thanatos_ op 06-01-2004 04:14 ]

[P_de_B]

Roel Broersma schreef op 06 januari 2004 @ 02:53:
Ik had de database (in MS ACCESS gecomprimeerd ongeveer 30MB) geconverteerd naar MS_SQL_2K.

[...]

Vroeger in Access kon je van je tbl_orders, je order_id bijv. laten indexen. Hoe kan je dit nu simpel/snel in SQL doen ? Toch niet alleen maar via de FULL-TEXT Search ?

Nee, fulltext indexing is iets heel anders. Je kunt via enterprise manager in het ontwerpscherm van een tabel primary keys en indexen zetten. Beter is om het te leren om het via scripts vanuit Query Analyzer te doen. (zodat je ook weet hoe het moet als er geen EM in de buurt is)

Access zal met dit soort relatief kleine databases idd wel sneller zijn

Ik denk niet dat access bij kleine db's sneller is dan SQL Server.

Annie schreef op 04 januari 2004 @ 13:50:
goed verhaal

Ter aanvulling:

SQL Server kijkt of het aantal records groot genoeg is om parallelism toe te passen, of er voldoende werkgeheugen beschikbaar is, hoeveel concurrent users er op de db zijn en naar het type query.

In Query Analyzer kun je zien of een query in aanmerking komt door het 'Execution plan' te bekijken. Voor meer info kun je zoeken naar 'parallelism' in Books Online.

[EfBe]

Je kunt altijd kijken of de NT Fiber optie wat brengt. Dat zijn een soort subthreads met veel minder overhead. In te stellen middels de server properties in enterprise manager.

Verder is het altijd raadzaam te verifieren of de 2 CPU's wel zijn aangevinkt in de server properties. Dit is default wel het geval, maar een overijverige DBA kan wellicht gedacht hebben dat het schadelijk zou kunnen zijn. Je kunt op het tabje 'Processor' deze zaken nakijken, NT Fibers instellen en bv ook de workload threshold instellen voor parallel uitgevoerde queries.

[ Voor 52% gewijzigd door EfBe op 06-01-2004 09:59 ]

[curry684]

Roel Broersma schreef op 06 januari 2004 @ 02:53:
Ik had de database (in MS ACCESS gecomprimeerd ongeveer 30MB) geconverteerd naar MS_SQL_2K.

Toen keek ik naar het snelheidsverschil.

Dit alles zonder er rekening mee te houden dat tijdens het converteren inderdaad AL je indexen verloren gaan, Autonummers worden weggehaald, BOOLEAN/BIT worden veranderd, etc.

Begrijp ik nu goed dat dit topic nu gaat over dat een compleet geindexeerde en geoptimaliseerde Access-database langzamer is dan eenzelfde SQL-Server database zonder indexes, primary en foreign keys en andere constraints?

Kan ik me iets bij voorstellen

Daarnaast is SQL Server geen ad-hoc-query monster. SQL Server komt het best tot z'n recht als je functionaliteit wegstopt in serverside prepared queries en stored procedures, omdat ie dan wild veel werk kan doen op de momenten dat ie daar de tijd voor heeft ipv op het moment dat ie eigenlijk aan het stampen zou moeten zijn. Access naar MSSQL converteren kan best, maar je moet er wel je database op aanpassen dat je de potentiele winst ook realiseert.

[EfBe]

Daarnaast is SQL Server geen ad-hoc-query monster. SQL Server komt het best tot z'n recht als je functionaliteit wegstopt in serverside prepared queries en stored procedures, omdat ie dan wild veel werk kan doen op de momenten dat ie daar de tijd voor heeft ipv op het moment dat ie eigenlijk aan het stampen zou moeten zijn.

Dat is een mythe. SqlServer prepareert geen stored procedures, alles wordt at runtime gecompileerd en execution plans worden gecached. Ad-hoc queries volgen hetzelfde pad: query wordt gecompileerd en gecached. Dit is vele malen efficienter dan pre-compiled stored procedures, want hij kan at-runtime de beste optimalisatie toepassen. SqlServer is perfect geschikt hierdoor voor ad-hoc queries, mits geparametriseerd natuurlijk.

Zie books online voor details omtrent het cachen van execution plans.

Het zal mij benieuwen of er wel PK's zijn gedefinieerd in sqlserver na de conversie.

[Verwijderd]

access naar SQL server converteren kan prima met de Upsize wizard....Wil nog niet zeggen dat het ook voor 100% goed verloopt maar goed....

Verder inderdaad, werken met Stored Procedures en mijn ervaring is dat SQL weet ik hoeveel sneller is dan Access (mits je ontwerp goed is en je procedures gebruikt!)

[ Voor 10% gewijzigd door Verwijderd op 06-01-2004 10:41 ]

[gorgi_19]

Verwijderd schreef op 06 januari 2004 @ 10:41:
access naar SQL server converteren kan prima met de Upsize wizard....Wil nog niet zeggen dat het ook voor 100% goed verloopt maar goed....

Mijn ervaring is dat SQL Server er een gigantische bende van maakt en alle datatypes niet respecteert (alles werd bij mij varchar)
Handmatig alles langslopen, autonumbers goedzetten (die worden afaik ook gesloopt )

Verder inderdaad, werken met Stored Procedures en mijn ervaring is dat SQL weet ik hoeveel sneller is dan Access (mits je ontwerp goed is en je procedures gebruikt!)

[brutaalmodus]sorry EfBe :P[/brutaalmodus]

Stored Procedures zijn niet per definitie altijd nodig of noodzakelijk voor performance winst.

EfBe schreef op 06 januari 2004 @ 10:40:
Het zal mij benieuwen of er wel PK's zijn gedefinieerd in sqlserver na de conversie.

* gorgi_19 wil in dit geval z'n geld wel zetten op niet..

[ Voor 57% gewijzigd door gorgi_19 op 06-01-2004 10:49 ]

[curry684]

EfBe schreef op 06 januari 2004 @ 10:40:
[...]
Dat is een mythe. SqlServer prepareert geen stored procedures, alles wordt at runtime gecompileerd en execution plans worden gecached. Ad-hoc queries volgen hetzelfde pad: query wordt gecompileerd en gecached. Dit is vele malen efficienter dan pre-compiled stored procedures, want hij kan at-runtime de beste optimalisatie toepassen. SqlServer is perfect geschikt hierdoor voor ad-hoc queries, mits geparametriseerd natuurlijk.

Ik bedoelde met ad-hoc queries de echte ad-hoc queries, oftewel continu verschillend. Je kunt echter met SQL Server ook queries 'preparen' om die vervolgens te 'triggeren' met een handle: ODBC3 feature als ik me niet vergips, voor non-supporting DBMS'en wordt er gewoon client-side gecached en continu ad-hoc gequeried. Maar prepared queries en stored procedures zijn dus met dank aan de caching die je noemt wel degelijk sneller dan wild varierende ad-hoc queries.

Mijn uitleg was wat minder solide dan die van jou, admittedly, maar in essentie zeggen we hetzelfde

Het zal mij benieuwen of er wel PK's zijn gedefinieerd in sqlserver na de conversie.

Mij ook

[curry684]

gorgi_19 schreef op 06 januari 2004 @ 10:42:
[brutaalmodus]sorry EfBe :P[/brutaalmodus]

"Even better: it will parametrize queries which don't even have parameters to keep the execution plan in the cache!". Interessant, dat wist ik niet

Also uit die link:

Rob also claims that you can better loop through rows in T-SQL (in 'many cases') than in the data-access layer because doing it in T-SQL is faster (according to Rob). Now, looping through rows in T-SQL requires a cursor. A cursor will always create a temp table in tempdb, which can hurt performance pretty bad if tempdb is full and has to be resized. Doing a lot of looping will require that sooner or later.

Inderdaad, cursors zijn true evil: applicatievertragingen met een factor 60 zijn waargenomen

[edit]
en als ik toch bezig ben:

I too find writing code like string s = "SELECT * FROM Foo WHERE Bar = " + barValue; in your code not the right thing to do. However the alternative is not stored procedures, it's a component that generates this SQL on the fly so you don't have the disadvantages of stored procedures and have the advantages of generating the SQL you need. Writing such a component is a one-time effort, you can reuse that component each time you access a database. Stored procedures are not the answer, Dynamic SQL is.

So true, met een fatsoenlijke intermediate layer die het genereert ben je namelijk tevens van het gek*t af met verschillende databases die andere SQL-extensies gebruiken. Anyone ooit gehad dat een 'top 25' of 'limit to 25 rows' onderdeeltje niet werkte?

[ Voor 31% gewijzigd door curry684 op 06-01-2004 10:53 ]

[Maasluip]

Verwijderd schreef op 05 januari 2004 @ 16:07:
je besturingssysteem weet dat er 2 processoren aanwezig zijn en zullen de workload verdelen. Niet het ene proces gaat over 1 proc. en het andere proces gaat over de andere proc. Wat hij gedaan heeft heet SMP Symetric Multi Processing.

offtopic:
Dat is niet wat SMP doet. SMP houdt in dat je twee of meer processoren hebben die gelijk zijn en toegang hebben tot dezelfde hardware. Of een proces dan op een of meerder processoren wordt uigevoerd heeft daar niks mee te maken.
AMP huidt in dat die gelijkwaardigheid er niet is. Door ofwel verschillende processoren (in de pre-Pentium jaren de x86 met x87 co-processor) of door verschillende systemen (tegenwoordig door processor en videokaart met eigen processor: dit is eigenlijk ook een multi processor systeem of zoals in het verleden de Sun Sparcserver 1000 (en anderen): aparte processorkaarten met ieder eigen geheugen)

[EfBe]

curry684 schreef op 06 januari 2004 @ 10:46:
Ik bedoelde met ad-hoc queries de echte ad-hoc queries, oftewel continu verschillend.

Ook dan parametriseert Sqlserver de query. Dus als jij dit doet:

INSERT INTO Foo (Bar, Blah) VALUES (1,0)
INSERT INTO Foo (Bar, Blah) VALUES (2,1)
INSERT INTO Foo (Bar, Blah) VALUES (3,3)
etc.

dan zal SqlServer daarvan maken:
INSERT INTO Foo (Bar, Blah) VALUES (@p1, @p2)
dat cachen en dat execution plan gebruiken voor je reeks. Executeer je telkens andere queries met where predicates e.d. dan heb je sowieso geen reet aan stored procedures want dat had je niet eens in stored procedures kunnen vatten.

Je kunt echter met SQL Server ook queries 'preparen' om die vervolgens te 'triggeren' met een handle: ODBC3 feature als ik me niet vergips, voor non-supporting DBMS'en wordt er gewoon client-side gecached en continu ad-hoc gequeried. Maar prepared queries en stored procedures zijn dus met dank aan de caching die je noemt wel degelijk sneller dan wild varierende ad-hoc queries.

Lees nu eens wat je zegt Prepare op de client is pre-parsing op de client, maar dan alleen voor de slag: ADO -> SqlServer code stream (in dat format van ze). Prepare heeft dus alleen met handel op de client te maken, niet met sqlserver. Wild varierende ad-hoc queries kunnen niet worden vergeleken met stored procs, want het alternatief in stored procs IS er niet: je kunt dan alleen terugvallen op nullable parameters (optional parameters) en de COALESCE/ IS NULL trick en die is echt langzamer dan dyn. queries, of een karrevracht aan stored procedures maken (niet echt je van het) of je stored procedure vol gooien met IF @param=value ellende, en dat is bijna altijd goed voor een complete recompile van de stored proc (m.a.w.: execution plan wordt niet gecached).

Mijn uitleg was wat minder solide dan die van jou, admittedly, maar in essentie zeggen we hetzelfde

Dat denk ik niet.

[curry684]

EfBe schreef op 06 januari 2004 @ 12:00:
[...]
Ook dan parametriseert Sqlserver de query. Dus als jij dit doet:

INSERT INTO Foo (Bar, Blah) VALUES (1,0)
INSERT INTO Foo (Bar, Blah) VALUES (2,1)
INSERT INTO Foo (Bar, Blah) VALUES (3,3)
etc.

dan zal SqlServer daarvan maken:
INSERT INTO Foo (Bar, Blah) VALUES (@p1, @p2)

Ik had 'echte' dus speciaal bold bedoeld omdat ik er "telkens andere queries met where predicates e.d." mee bedoelde Waarvan ik ook helemaal nooit heb gezegd dat je dat met stored procedures op kunt lossen, ik heb alleen gezegd dat SQL Server niet monsterlijk sneller is in de echte ad-hoc queries dan een willekeurige andere database

Lees nu eens wat je zegt Prepare op de client is pre-parsing op de client, maar dan alleen voor de slag: ADO -> SqlServer code stream (in dat format van ze). Prepare heeft dus alleen met handel op de client te maken, niet met sqlserver.

Humz ik was in de overtuiging dat de prepared-query support van ODBC3 juist gestoeld was op de hoop dat de achterliggende server daar serverside support voor is, en dat clientside pre-parsing de fallback is indien de server dat niet ondersteunt. Dat is overigens slechts een miniem verschil, de ODBC layer ligt uitgesmeerd over zowel client als server, en de essentie is iig dat het niet in de echte client zelf ligt. Heb je onderbouwing dat de SQL Server ODBC drivers dit volledig clientside doen?

Dat denk ik niet.

Als je even je preassumptie wegneemt dat je dacht dat ik zei dat stored procedures een volwaardige vervanger zijn voor true ad-hoc queries denk ik dat wel, hoogstens met het puntje hierboven over de preparsing van ODBC prepared queries als misser

Essentieel gezien: de echte potentie van MSSQL en Oracle ligt in hun serverside mogelijkheden. Zonder stored procedures en triggers heb je in principe alleen maar een beter schaalbare, beter uitontwikkelde, beter onderhoudbare en meer-fancy-features-ondersteunende dure versie van MySQL in handen

[EfBe]

curry684 schreef op 06 januari 2004 @ 12:36:
Ik had 'echte' dus speciaal bold bedoeld omdat ik er "telkens andere queries met where predicates e.d." mee bedoelde Waarvan ik ook helemaal nooit heb gezegd dat je dat met stored procedures op kunt lossen, ik heb alleen gezegd dat SQL Server niet monsterlijk sneller is in de echte ad-hoc queries dan een willekeurige andere database

Mja, maar goed, dat is dan dus een open deur want optimaliseren is dan niet mogelijk.

[...]
Humz ik was in de overtuiging dat de prepared-query support van ODBC3 juist gestoeld was op de hoop dat de achterliggende server daar serverside support voor is, en dat clientside pre-parsing de fallback is indien de server dat niet ondersteunt. Dat is overigens slechts een miniem verschil, de ODBC layer ligt uitgesmeerd over zowel client als server, en de essentie is iig dat het niet in de echte client zelf ligt. Heb je onderbouwing dat de SQL Server ODBC drivers dit volledig clientside doen?

ODBC ligt op OleDB al een tijdje. OleDB's prepare doet niet veel, SqlServer compileert de handel pas als de feitelijke query aankomt bij de server. Dan gaat hij eerst kijken of er al een execution plan is, zo ja dan gebruikt hij die, zo nee, dan maakt hij een aan. Dit is allemaal internal functionality, dat is niet voor een deel in een of andere externe dll geplakt zoals OleDb (of .NET).

[...]
Essentieel gezien: de echte potentie van MSSQL en Oracle ligt in hun serverside mogelijkheden. Zonder stored procedures en triggers heb je in principe alleen maar een beter schaalbare, beter uitontwikkelde, beter onderhoudbare en meer-fancy-features-ondersteunende dure versie van MySQL in handen

Tuurlijk, maar face it, elk database systeem is niets anders dan een file-reader/writer met kloten