Really slow schema information queries on SQL Server 2005

Question

I have a database with a rather large number of tables, about 3500, and an application that needs to access a table list.

On a particular server this takes over 2.5 min to return.

EXEC sp_tables @table_type="'TABLE'"

I know there are faster ways to do that but sadly I'm not in a position to modify the application and need to find a way to push it below 30 seconds so the application doesn't throw timeout errors.

So. What, if anything, can I do to improve the performance of this sp within sql server?

Answer 1

I have seen these stored procedures run slow if you do not have the GRANT VIEW DEFINITION permission set on your user account. From what I read, this will cause a security check to occur slowing down the query.

Maybe a SQL guru can comment on why, if this does help your problem.

Answer 2

Well, sp_tables is system code and can't be changed (could workaround in SQL Server 2000, not SQL Server 2005+)

Your options are

1. Change the SQL
2. Change command timeout
3. Bigger server

You've already said "no" to the obvious solutions...

Answer 3

You need to approach this just like any other performance problem. Why is it slow? Namely, where does it block? Disk IO? CPU? Network? Lock contention? The scientific method is to use a methodology like Waits and Queues, or its newer SQL 2008 equivalent Troubleshooting Performance Problems in SQL Server 2008. The lazy way is to simply check the wait_type, wait_time and wait_resource columns in sys.dm_exec_requests for the session executing the sp_tables call. Once you find out what is blocking the execution, you can proceed accordingly.

If I'd venture a guess, you'll discover contention as the cause: other sessions are locking table's metadata exclusively and thus block the execution of sp_tables, which has to wait until all operations in front of it finish.