[MSSQL 2000] sp_indexes_rowset performance probleem

[Verwijderd]

Vandaag een vreemd probleem van een klant proberen te tackelen: een niet al te spannende query neemt bij hun al gauw 5 seconden in beslag, terwijl ze vrij fatsoenlijke hardware hebben staan (dual processor DB-server met 4GB aan boord) en niet eens zo gek veel concurrent users hebben, zo'n stuk of 40 schat ik.
Op m'n eigen testserver (single processor, 1GB) duurt die query iets meer dan een halve seconde, maar ik ben dan ook single user.
De query ziet er ongeveer zo uit:

SQL:

SELECT <3 group by velden>, <17 geaggregeerde velden, waarvan 12 uit tabel2>
FROM tabel1 JOIN tabel2 ON <2 foreign key velden>
WHERE <conditie op 2 niet-group-by velden uit tabel1>
GROUP BY <3 group by velden>

Indexen staan goed, FK's staan goed, query is geparametriseerd, en de aggregates kosten vrijwel geen tijd: meestal MIN() op de velden uit tabel2, om te voldoen aan de eis dat niet-group-by velden altijd geaggregeerd moeten zijn.

Toen ik de profiler op mijn testsetup losliet, zag ik dat van de 563 ms totaal er bijna 500 ms naar 't volgende commando gingen:

SQL:

exec sp_indexes_rowset N'tabel2', NULL, NULL

Op die sp werden steeds zo'n 50.000 reads losgelaten (OK, tabel2 is groot, dus daar kan ik me iets bij voorstellen). Niet handig, en volgens mij ook niet nodig.

Ik heb 't nu opgelost door exact diezelfde query in een stored procedure te zetten, met als gevolg dat sp_indexes_rowset niet meer wordt aangeroepen, en dat de execution time is gedaald van 563 naar 16 ms...
Probleem opgelost, maar ik weet nog steeds niet waarom MSSQL besloot dat 'ie bij die query sp_indexes_rowset nodig had, en bij diezelfde query in een stored proc niet.

Iemand enig idee?

_{Getest met MSSQL 2000 SP4 en MDAC 2.8}

[Plopeye]

Vanuit wat voor applicatie word de inline sql opdracht gegeven ???

Access / .Net / Anders...

[Verwijderd]

Win32 Delphi applicatie, gebruik makend van ADO, dus geen ODBC of ADO.NET.

[_js_]

ADO doet dat omdat het extra metadata nodig heeft. Het zou op te lossen moeten zijn door alle tabellen volledig te kwalificeren: ...FROM dbo.tabel1 ... dbo.tabel2

[P_de_B]

Volledig kwalificeren is sowieso een goed idee om volledig gebruik te kunnen maken van het cachen van executieplans.

[Crazy D]

P_de_B schreef op vrijdag 28 juli 2006 @ 11:28:
Volledig kwalificeren is sowieso een goed idee om volledig gebruik te kunnen maken van het cachen van executieplans.

Die worden toch altijd gecached en hergebruikt indien de query identiek is?

[P_de_B]

Crazy D schreef op vrijdag 28 juli 2006 @ 11:51:
[...]

Die worden toch altijd gecached en hergebruikt indien de query identiek is?

Quote van books online:

The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Employees

SELECT * FROM Northwind.dbo.Employees

[Crazy D]

Begrijp ik het goed dat SQL dus eigenlijk kijkt naar het object (Northwind.dbo.Employees) en niet (nou ja, natuurlijk uiteindelijk wel..) naar de fieldlist? (oftewel
SELECT * FROM Northwind.dbo.Employees
en
SELECT ID, Name FROM Northwind.dbo.Employees
gebruiken hetzelfde plan? Ik heb Northwind hier niet staan, dus niet klagen dat ID en Name niet bestaan )

[P_de_B]

Volgens mij heb je gelijk, en zijn de gebruikte objecten en de eventuele parameters van belang om een object in de query cache te hergebruiken.

[Verwijderd]

_js_ schreef op vrijdag 28 juli 2006 @ 10:21:
ADO doet dat omdat het extra metadata nodig heeft. Het zou op te lossen moeten zijn door alle tabellen volledig te kwalificeren: ...FROM dbo.tabel1 ... dbo.tabel2

Had ik misschien moeten melden, maar voordat ik de startpost plaatste was ik dit ook al op internet tegengekomen. Echter haalde 't volledig kwalificeren (dbo.tabel1 of zelfs database.dbo.tabel1) helemaal niets uit.

Overigens heb ik 't probleem alleen maar bij MSSQL 2000. Bij 2005 en 7 treedt het probleem niet op.
Helaas gebruikt het grootste gros van onze klanten 2000...

Edit:
Je zegt dat ADO dit doet, maar met een IP-sniffer ben ik geen enkel TCP/IP packet tegengekomen tussen client en server waar 'sp_indexes_rowset' in voorkomt. Het kan zijn dat 't via een andere call wordt aangeroepen, maar ik hou 't er voorlopig op dat MSSQL zelf besloot om die sp aan te roepen (onder de ClientID en SPID van die ADO connectie).
Het te vaak aanroepen van sp_indexes_rowset was wel een probleem van MDAC 2.6, maar is daarna opgelost. En gezien het feit dat 't bij mij alleen maar bij MSSQL 2000 optreedt denk ik niet dat 't aan de client-kant (ADO/MDAC) ligt.

[ Voor 37% gewijzigd door Verwijderd op 28-07-2006 21:13 ]

[BCC]

Verwijderd schreef op vrijdag 28 juli 2006 @ 19:40:
[...]
Had ik misschien moeten melden, maar voordat ik de startpost plaatste was ik dit ook al op internet tegengekomen. Echter haalde 't volledig kwalificeren (dbo.tabel1 of zelfs database.dbo.tabel1) helemaal niets uit.

Overigens heb ik 't probleem alleen maar bij MSSQL 2000. Bij 2005 en 7 treedt het probleem niet op.
Helaas gebruikt het grootste gros van onze klanten 2000...

Dan lijkt 't me eerder een cache-bugje in 2000.

[whoami]

Hoe zit het met de statistics van die DB ? Heb je deze al eens ge-updated ?
Heb je ook een maintenance-plan voor die DB ?
Hoe zit het met de fragmentatie van de DB ? Wat zie je voor gegevens mbt de betreffende tabellen als je DBCC showcontig uitvoert ?

Wat P_de_B aanhaalt, zou ik ook eens proberen.

Verder hoort dit eerder thuis in DT&E
-> DT&E

[ Voor 17% gewijzigd door whoami op 29-07-2006 10:37 ]