Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Sp_releaseschemalock, Sp_getschemalock

    I've been doing some tracing on one of our production systems and I'm seeing some system stored procedures being called I've never seen before. Here are the series of events:

    Code:
    declare @p1 int
    set @p1=1
    declare @p2 bigint
    set @p2=169603968398352
    exec [sys].sp_getschemalock @p1 output,@p2 output,N'"ClinicAdmin"."dbo"."EMR_VIEW_Clinic"'
    select @p1, @p2
    So I'm assuming this chunk of code is obtaining a schema lock on EMR_VIEW_Clinic. Next is:
    Code:
    declare @p1 int
    set @p1=4725
    exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "Tbl1002"."CLINIC_ID" "Col1004","Tbl1002"."CLINIC_HOST_ID" "Col1005","Tbl1002"."CLINIC_HostNumber" "Col1006","Tbl1002"."CLINIC_REG_ID" "Col1007","Tbl1002"."CLINIC_CLINICTYPE_ID" "Col1008","Tbl1002"."CLINIC_PRICEGEO_ID" "Col1009","Tbl1002"."CLINIC_Address_COUNTRY_ID" "Col1010","Tbl1002"."CLINIC_AddressStreet1" "Col1011","Tbl1002"."CLINIC_AddressStreet2" "Col1012","Tbl1002"."CLINIC_AddressCity" "Col1013","Tbl1002"."CLINIC_AddressStateProvince" "Col1014","Tbl1002"."CLINIC_AddressPostalCode" "Col1015","Tbl1002"."CLINIC_Name" "Col1016","Tbl1002"."CLINIC_ShortName" "Col1017","Tbl1002"."CLINIC_FolderName" "Col1018","Tbl1002"."CLINIC_Active" "Col1019","Tbl1002"."CLINIC_PseudoClinic" "Col1020","Tbl1002"."CLINIC_IPAddress" "Col1021","Tbl1002"."CLINIC_GLRegion" "Col1022","Tbl1002"."CLINIC_GLCity" "Col1023","Tbl1002"."CLINIC_GLClinic" "Col1024","Tbl1002"."CLINIC_GLTypeOfClinic" "Col1025","Tbl1002"."CLINIC_CLIA_Number" "Col1026","Tbl1002"."CLINIC_FirstOpenDate" "Col1027","Tbl1002"."CLINIC_LastOpenDate" "Col1028","Tbl1002"."CLINIC_Subsidized" "Col1029","Tbl1002"."CLINIC_NumberOfExamRooms" "Col1030","Tbl1002"."CLINIC_Phone_COUNTRY_ID" "Col1031","Tbl1002"."CLINIC_PhoneNumber" "Col1032","Tbl1002"."CLINIC_PhoneExt" "Col1033","Tbl1002"."CLINIC_CVSCostCenter" "Col1034","Tbl1002"."CLINIC_NoPayer_LABPROC_ID" "Col1035","Tbl1002"."CLINIC_Payer_LABPROC_ID" "Col1036","Tbl1002"."CLINIC_AllowEPrescribing" "Col1037","Tbl1002"."CLINIC_EnablePortalEnrollment" "Col1038","Tbl1002"."CLINIC_EnablePCPFaxing" "Col1039","Tbl1002"."CLINIC_QuickCode" "Col1040" FROM "ClinicAdmin"."dbo"."EMR_VIEW_Clinic" "Tbl1002" WHERE "Tbl1002"."CLINIC_ID"=@P1',759
    select @p1
    I have no idea what sp_prepexec does, but it looks like it's executing that big query. The last two are:
    Code:
    exec sp_unprepare 4725
    
    exec [sys].sp_releaseschemalock 1

    The one thing about that last one - sp_releaseschemalock - the number on the end will vary between 1 through 4. In my readings, I've surmised that when you see this kind of activity, it's not good.

    If anyone could shed some light on this that would be awesome!!

    Thanks!

    P.S. - I just love the way none of these procedures are documented in BOL
    Last edited by ansonee; 02-22-08 at 14:34. Reason: addition
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •