July 5, 2005 at 3:54 am
I found the following extract while looking for the info I need:-
"Dependencies are created when objects are created altered. These include
the list of all objects in the current database that the object directly
references.
Note that dependency information can be inaccurate when objects are not
created in proper dependency order. For example, you can create a proc
before the referenced table is created. No record of this dependency is
created in this case. Similarly, if you drop and recreate the table, the
dependency info is deleted but not recreated."
My question is this. Can the absence of the dependency info impact performance? We recently dropped and recreated a table and since then the performance has degraded in that we get more long running transactions and locks. What else could be missing that I don't know about? How can we get this info back - is dropping and recreating all the stored procs the only way?
Any insight and/or comments will be greatly appreciated.
July 5, 2005 at 5:34 am
AFAIK information from sysdepends does not affect performance (at least we haven't noticed it here).
The only way to get the information back is to drop\re-create objects in the proper order unfortunately.
Have you ran profiler or another tracing tool to determine if the indexes that are set are still being used? Were the indexes optimized for the current amount of data? Are there ad-hoc queries running?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 5, 2005 at 8:36 am
Thanks AJ! I have just checked that the indexes are referencing the correct OBJ ID (i.e. the new table ID) by looking in sysindexes and running queries to make sure that they are working. The sysdepends table has 164 references to the old table ID and none to the new table ID. Displaying the dependencies through enterprise manager shows a limited amount - just tables though, but no stored procs.
I have tried profiler, but get nothing helpful. The problem happened the same day so I know it has something to do with the dropping and recreating of the table and not just data volumes. Is there anything else that changes in the system tables when you drop a table? The person who prepared the script did not realise that the new table would take on a new ID - we were just trying to get rid of a duplicate row.
When stored procs run, do they take longer to find the table in the stored proc if the id of the table has changed since they were compiled perhaps?
I do appreciate your input!
Judy
July 5, 2005 at 9:39 am
well you may get some recompilations of the sp until the cache "warms-up" but that should be all you should look for long running queries I suspect Index maintenance problems in your case!
* Noel
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply