none
Strange query takes 18 seconds.

    Question

  • Hello,

    we have migrated our database from mssql 2005 to mssql 2008 r2 x64 SP1. We have a problem with this query which takes 18 seconds.  It causes that our application hangs  for 18 seconds.

    This is the query:

    select v.name, schema_name(v.schema_id), v.type, cast(case when v.is_ms_shipped = 1 then 1when (select major_id from sys.extended_properties where major_id = v.object_id and minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support')is not null then 1 else 0 end as bit),ObjectProperty(v.object_id, N'IsSchemaBound') from sys.all_objects AS v where v.type = N'V' and permissions(v.object_id) & 4096 <> 0

    I think, that this has somethig to do to with security. If users are dboweners so this query is not being called and performance is good. For authetication we use domain groups. We didn't have these problems in mssql 2005. Our database compatibility level is set to SQL Server 2000, because we started on mssql 2000. According to developers it shouldn't be a problem.

    Thanks.

    Monday, February 13, 2012 2:38 PM

Answers

  • You mentioned migrating to SQL Server 2008 R2.   What is your frontend program coded in?  Is it a Microsoft Access frontend? 

    The PERMISSIONS function is being deprecated and other faster functions are being provide now.  See:

    http://msdn.microsoft.com/en-us/library/ms186915.aspx  " Use fn_my_permissions and Has_Perms_By_Name instead"

    I asked about Microsoft Access since even Access 2010 still uses the PERMISSIONS function to determine to which objects the login has access.

    RLF

    Tuesday, February 14, 2012 2:56 PM
  • I can assure you that they are using the deprecated function and that they know that they are still using it.  (It is doubtless embedded in the Microsoft Access code somewhere and would take time to replace and redistrubute the code.)

    RLF

    Thursday, February 16, 2012 1:30 PM

All replies

  • Now, that is a puzzle.

    Where does this query come from? Your application? In such case, why?

    How many views do you have in the database?

    What does "SELECT COUNT(*) FROM sys.extended_properties" return?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 13, 2012 10:39 PM
  • You mentioned migrating to SQL Server 2008 R2.   What is your frontend program coded in?  Is it a Microsoft Access frontend? 

    The PERMISSIONS function is being deprecated and other faster functions are being provide now.  See:

    http://msdn.microsoft.com/en-us/library/ms186915.aspx  " Use fn_my_permissions and Has_Perms_By_Name instead"

    I asked about Microsoft Access since even Access 2010 still uses the PERMISSIONS function to determine to which objects the login has access.

    RLF

    Tuesday, February 14, 2012 2:56 PM
  • You are right. Our aplication is written in MS ACCESS. We started in Access 2003, and now we have Access 2010.

    I will try to investigate if they use deprecated function.

    Thanks.

    Wednesday, February 15, 2012 9:11 AM
  • I can assure you that they are using the deprecated function and that they know that they are still using it.  (It is doubtless embedded in the Microsoft Access code somewhere and would take time to replace and redistrubute the code.)

    RLF

    Thursday, February 16, 2012 1:30 PM