RE: What is extended proc sp_releaseschemalock

SSC Guru

Points: 1003865

September 6, 2014 at 10:21 pm

Shawn Wilson (9/5/2006)
I've seen this happen where there was a linked server query running and killed. However the connection persisted and was reporting it was at a 100 percent in the rollback state, but was blocking other queries. I simply stopped / started DTC on the linked server and then the connection cleared. In my several cases, this has always worked for me without having to stop/start sqlserver.
You should check your linked server configurations and make sure they are "collation compatible" checked. Otherwise the entire remote table is returned to the local calling server to execute the WHERE clause.
In regards to sp_releaseschemalock ....below is part of a rootcause analysis I had to give one time before. I've edited it quite a bit to rip out tablenames/queries we use. I used profiler to capture these sprocs.
This is one of many sp's which are called as a result of a linkedserver query.
sp_tables_info_rowset tblXXX
sp_columns_rowset tblXXX
sp_indexes_rowset tblXXX
sp_check_constbytable_rowset tblXXX
sp_provider_types_rowset tblXXX
sp_table_statistics_rowset tblXXX
sp_getschemalock tblXXX
exec sp_prepexec @P1 output, NULL, N'SELECT "Col1601" FROM (SELECT "some dynamic generated query" FROM "dbname"."dbo"."tblName"WHERE somecriteria)
sp_releaseschemalock tblXXX
sp_releaseschemalock tblXXX
sp_releaseschemalock tblXXX

All of this overhead generates a lot of disk IO's and stacks up on the blocking.
All of this occurs because the SQL Server onserverA has no statistical information about thetablesinvolved in the linked query. ServerBwill always inquire that information for each call.


Hope this helps out some.

I know this is an 8 year old post but I wanted to say thanks... this is good stuff and just exactly what I was looking for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)