SQL Server Performance Forum – Threads Archive
sp_tables lock up
I had a lock up on one of my databases today. I checked with sp_lock, killed the offensive process, and the lock cleared. In my SQL Profiler trace I then see tons and tons of entries with high durations for the following: exec sp_tables NULL,NULL,NULL,NULL Why would these store procedures be taking so long? The other thing I noticed is the offensive process that I killed had a different T-SQL statement in the Activity Monitor than what was returned in SQL Profiler. It seemed "exec sp_tables NULL,NULL,NULL,NULL" was the cause of the lock-up. Is this something that is done internally in SQL or is it likely the application that we have interfacing with the database?Sp_table is sql store procedure, but there is some user who is running that sp. Or some 3rd party tool. sp_tables
Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause). Syntax
sp_tables [ [ @table_name = ] ‘name’ ]
[ , [ @table_owner = ] ‘owner’ ]
[ , [ @table_qualifier = ] ‘qualifier’ ]
[ , [ @table_type = ] "type" ] Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS†with no warranties for accuracy.
Are you sure sp_tables proc was cuasing the blocking?
It is just resturn only table and view details…
Most of the time, this kind of procedures called in third party apps to get the list of tables/objects…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Note from BOL in this case:
quote:
For maximum interoperability, the gateway client should assume only SQL-92-standard SQL pattern matching (the % and _ wildcard characters). Privilege information about the current user’s read or write access to a specific table is not always checked. Therefore access is not guaranteed. This result set includes not only tables and views, but also synonyms and aliases for gateways to DBMS products that support those types. If the server attribute ACCESSIBLE_TABLES is Y in the result set for sp_server_info, only tables that can be accessed by the current user are returned. sp_tables is equivalent to SQLTables in ODBC. The results returned are ordered by TABLE_TYPE, TABLE_QUALIFIER, TABLE_OWNER, and TABLE_NAME.
For maximum interoperability, the gateway client should assume only SQL-92-standard SQL pattern matching (the % and _ wildcard characters). Privilege information about the current user’s read or write access to a specific table is not always checked. Therefore access is not guaranteed. This result set includes not only tables and views, but also synonyms and aliases for gateways to DBMS products that support those types. If the server attribute ACCESSIBLE_TABLES is Y in the result set for sp_server_info, only tables that can be accessed by the current user are returned. sp_tables is equivalent to SQLTables in ODBC. The results returned are ordered by TABLE_TYPE, TABLE_QUALIFIER, TABLE_OWNER, and TABLE_NAME.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Sorry I haven’t responded back to this for a while. Things have been pretty busy for me lately. Well, the main thing I was concerned about has been answered… this procedure is actually being called by a user or 3rd party application. I honestly don’t know why it takes so long to run. I’ve got another trace up today and perhaps I’ll get some more data that will bring it up again. It is just metadata so I don’t see why it should take so long. At the same time though, I do not see the need for the application to be accessing this metadata. I suspect the developers are doing something incredibly stupid again. All ownership is the same so I can’t see them checking that as a possibility.
MohammedU, I’m not really sure that the stored procedure was causing the lock-up… but when I killed the sp_id that was flagged as blocking in Activity Monitor, everything cleared up and the longest running process by far on SQL Profiler was that stored procedure (note the query that came up in Activity Monitor wasn’t on SQL Profiler at all).
Hm its from third party means then performance degradation is affected as most of them do not follow simple optimization procedures. Fyihttp://sqlserver-qa.net/blogs/tools…rrency-locking-with-asp-net-applications.aspx about information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>