SQL Server Performance

sp_tables lock up

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by KrelianXgs, Apr 17, 2007.

  1. KrelianXgs New Member

    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?
  2. Luis Martin Moderator

    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.



  3. MohammedU New Member

    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.
  4. satya Moderator

    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.

    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.
  5. KrelianXgs New Member

    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).
  6. satya Moderator

    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.

Share This Page