August 13, 2008 at 8:07 am
hi, we support an app running on SQL 2005 std for a customer and certain screens can take up to 20 seconds to refresh. Done some profiling and the following is run approx 8 times:
select x.name, x.indid, x.status, INDEX_COL('appowner.TRAINEE', x.indid, c.colid)
from sysindexes x, syscolumns c where x.id = object_id ('appowner.TRAINEE')
and indid between 1 and 254 and x.id = c.id and c.colid <= x.keycnt
order by x.indid, c.colid ASC
Now this returns 280 rows, about 50 rows for index's, 220+ rows for system statictics. In the execution plan, it estimates a subtree of 0.019. However in reality the query is taking around 1.5 seconds:-
CPU - 1328
Reads - 1757
Writes - 0
Duaration - 1433
Stats are set to auto update and auto create. Index's and stats are updated nightly on all system and live DB's. I cant change the app - any advise on speeding this query up?
TIA Paul
August 13, 2008 at 8:11 am
I'm not sure about speeding the query up, it's based off system tables and system views which you cannot change.
That said, 50 indexes and 230 statistics on a single table? Are all of those indexes necessary?
Perhaps drop all of the statistics. If the optimiser decides it needs them, it will recreate them. It's possible there are stats that aren't necessary any more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2008 at 8:39 am
thats exactly what i was thinking re delete all the statistics. I got a blank app db and had a look and that contained alot of stats too, not quite as many...
I was also thinking that this query isnt optimised for 2005 re sysindexes v sys.indexes and this may have some impact?
edit re 50 rows for indexes - the strange thing about this is there are about 8 indexes on this table. index names are repeated in 3 or 4 rows, but the column it is attached to is in the first row, and the next two are NULL eg
name ...............indid...............status.......(No column name)
TRAINEE_PROGENTRYROUTE60PROGRAMMEENTRYROUTE
TRAINEE_PROGENTRYROUTE60NULL
TRAINEE_PROGENTRYROUTE60NULL
Looking at the index properties, it is on PROGRAMMEENTRYROUTE field but nothing else...
August 13, 2008 at 8:52 am
Looks like the developer was trying to list the index columns, but I don't think that's the right way. There's a much easier (faster) way in 2005, but if you can't change where the query's coming from, that's not much help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2008 at 9:05 am
Of course, then there's the question of why the application wants to know the columns of the indexes in the first place...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2008 at 10:35 am
GilaMonster (8/13/2008)
Of course, then there's the question of why the application wants to know the columns of the indexes in the first place...
Given the age of the app, who knows...
However, i have tested the removal of stats on two tables so far and the time has gone from over 20 seconds to 8!! Result! :w00t:
I will look at a few more key tables, remove the stats and get some users to test before i run in prod. Cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply