January 3, 2011 at 9:38 am
When I run sp_tables with no parameters on about 1700 tables it takes less than a second. When I run on another server it takes almost two minutes for the same amount of tables. Is there some kind of internal indexing of this SQL Server that can be redone to make this run more efficiently?
January 3, 2011 at 10:10 am
Reindexing cannot be done on sp_tables as it is system stored procedure. Does the second server you are querying has exactly the same tables as the first server? Also, it might be the load on the server that is making it take a lot of time. Also what kind of priveleges does the user have?
January 3, 2011 at 10:39 am
Thanks for your reply. It is exactly the same set of tables in that database. We are using the sa user through SQL Server Management Studio so there should be no permissions issues. The reason I asked about the reindexing is not for the sp_tables procedure but wondering if there is an internal SQL Server index that is corrupted for the tables that is causing this issue.
May 14, 2012 at 9:58 am
I had the same problem. One thing I noticed was that SELECT * FROM INFORMATION_SCHEMA.TABLES executed in about 100 mS, while EXEC SP_TABLES was averaging about 3 seconds.. If you look at the execution plan of both queries, you'll see that EXEC SP_TABLES is surprisingly involved. Notice all the system tables that it's hitting.
I got the bright idea to restart SQL Server. That did the trick. After the restart, SP_Tables returned results in about 100 mS. My guess is that there is some sort of in-memory structure, perhaps an index, for the table schema. Over time the database in question had a lot of schema changes. That would have fragmented the structure, slowing retrievals.
AFAIK, SELECT ... FROM INFORMATION_SCHEMA.TABLES is the preferred method of enumerating tables. Post-restart it was averaging around 40 mS execution time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply