none
CREATE PROCEDURE dbo.proc_GetLevel and dbo.proc_FetchDocForHttpGet calling many times

    Soru

  • Hi all,

    In sql server 2008 r2 I saw strange thing about sharepoint stored procedures. the proc_GetLevel and proc_FetchDocForHttpGet procedures are created (not called) 30-40 times per request. is it normal? I think it is enough to create procedure once. Create once call many..

    The script I found is:

    SELECT DISTINCT TOP 20
    t.TEXT QueryName,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    s.total_elapsed_time AS TotalElapsedTime,
    ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn
    --,ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
    FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
    where execution_count > 0
    ORDER BY
    s.max_elapsed_time DESC
    GO


    Comrade

    08 Temmuz 2012 Pazar 18:45

Yanıtlar

  • Hi Again

    I found a link that says my query is wrong. The true query should be like:

    SELECT DISTINCT TOP 200
    t.TEXT QueryName,
    SUBSTRING(t.text, (s.statement_start_offset/2) + 1,
        ((CASE statement_end_offset 
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE s.statement_end_offset END 
                - s.statement_start_offset)/2) + 1) AS statement_text,
    s.statement_start_offset as STRT,
    s.statement_end_offset as ENDD,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    s.total_elapsed_time AS TotalElapsedTime,
    ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn,
    DB_NAME(t.dbid) as DBNM
    --,ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
    FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
    where execution_count > 1 --and dbid is null
    ORDER BY
    s.execution_count desc
    

    I did a mistake in my first query with ignoring start and end offsets in sql text.


    Comrade

    • Yanıt Olarak İşaretleyen caglar ozkul 16 Temmuz 2012 Pazartesi 18:54
    16 Temmuz 2012 Pazartesi 18:53

Tüm Yanıtlar

  • Hi,

    Thank you for your post.

    I'm trying to involve someone familiar with this topic to further look at this issue.


    Xue-mei Chang

    TechNet Community Support

    10 Temmuz 2012 Salı 09:31
  • Hi Chang,

    I get more detailed information. Following is a daily execution counts table of "Create proc/func" queries. And our analytics results are:

    User Count ~ 4000

    PageViews ~ 55.000

    Is it normal to create same procedure more than once for a database in sharepoint? If so, is it normal to create that procedure for 2240665 times? Do you think the results are ok?

    PS: Our pages are Wiki Pages we are using Sharepoint Foundation 2010 SP1.

    Query Execution Count
    CREATE FUNCTION dbo.fn_GetEffectiveSiteFlags( 2240665
    CREATE PROCEDURE dbo.proc_SecGetDomainGroupMapData( 1950342
    CREATE PROCEDURE dbo.proc_GetLevel( 1881126
    CREATE PROCEDURE dbo.proc_FetchDocForHttpGet( 1880281
    CREATE PROCEDURE dbo.proc_FetchDocForHttpGet( 1880281
    CREATE PROCEDURE dbo.proc_GetLevel( 1873467
    CREATE PROCEDURE dbo.proc_FetchDocForHttpGet( 1872622
    CREATE PROCEDURE dbo.proc_FetchDocForHttpGet( 1872622
    CREATE PROCEDURE dbo.proc_FetchDocForHttpGet( 1860967
    CREATE PROCEDURE dbo.proc_FetchDocForHttpGet( 1833623
    CREATE FUNCTION dbo.fn_UnpackCsvString( 757388
    CREATE PROCEDURE dbo.proc_GetEventReceivers( 594975
    CREATE PROCEDURE dbo.proc_GetWebFeatureList( 435820
    CREATE FUNCTION dbo.fn_UnpackCsvString( 303354
    CREATE PROCEDURE dbo.proc_ReturnTpWebMetaData( 277646
    CREATE PROCEDURE dbo.proc_ReturnTpWebMetaData( 277646
    CREATE PROCEDURE dbo.proc_ReturnTpWebMetaData( 277646
    CREATE PROCEDURE dbo.proc_ReturnTpWebMetaData( 277646
    CREATE PROCEDURE dbo.proc_ReturnTpWebMetaData( 277646
    CREATE PROCEDURE dbo.proc_GetWebNavAclsForCachedScope( 215512
    CREATE PROCEDURE dbo.proc_ReturnCachedNavAcls( 215512
    CREATE PROCEDURE dbo.proc_GetTpPageMetaData( 200099
    CREATE PROCEDURE dbo.proc_GetTpPageMetaData( 200099
    CREATE PROCEDURE dbo.proc_GetTpPageMetaData( 200099
    CREATE PROCEDURE dbo.proc_GetTpPageMetaData( 200099
    CREATE PROCEDURE dbo.proc_GetTpPageMetaData( 200099
    CREATE PROCEDURE dbo.proc_GetTpPageMetaData( 200098
    CREATE PROCEDURE dbo.proc_GetDocBuildDependencySet( 200017
    CREATE PROCEDURE dbo.proc_GetDocBuildDependencySet( 200017
    CREATE PROCEDURE dbo.proc_GetListContentTypeOrder( 195310
    CREATE PROCEDURE dbo.proc_GetTpPageFGPInfo( 187398
    CREATE PROCEDURE dbo.proc_GetTpPageFGPInfo( 187398
    CREATE PROCEDURE dbo.proc_GetAllWebPartsInternal( 187106
    CREATE PROCEDURE dbo.proc_SecGetUserPermissionOnGroup( 114589
    CREATE PROCEDURE dbo.proc_SecGetUserPermissionOnGroup( 114589
    CREATE PROCEDURE dbo.proc_GetTpWebMetaDataAndListMetaData( 77347
    CREATE PROCEDURE dbo.proc_GetTpWebMetaDataAndListMetaData( 77347
    CREATE PROCEDURE dbo.proc_UrlToWebUrlOutput( 64775
    CREATE PROCEDURE dbo.proc_UrlToWebUrlOutput( 41129
    CREATE PROCEDURE dbo.proc_GetListMetaData( 39959
    CREATE PROCEDURE dbo.proc_GetListMetaData( 39959
    CREATE PROCEDURE dbo.proc_GetEventReceivers( 39683
    CREATE PROCEDURE dbo.proc_GetUniqueScopesInList( 39670
    CREATE PROCEDURE dbo.proc_GetWebFeatureList( 35624
    CREATE PROCEDURE dbo.proc_FetchDocForHttpGet( 27341
    CREATE PROCEDURE dbo.proc_EnumLists( 24996
    CREATE PROCEDURE dbo.proc_EnumLists( 24957
    CREATE PROCEDURE dbo.proc_UrlToWebUrlOutput( 22155
    CREATE PROCEDURE dbo.proc_GetAllAttachmentsInfo( 19408
    CREATE PROCEDURE dbo.proc_GetAllAttachmentsInfo( 19408
    CREATE PROCEDURE dbo.proc_GetAllAttachmentsInfo( 19408
    CREATE PROCEDURE dbo.proc_GetTpWebMetaDataAndListMetaData( 15414
    CREATE PROCEDURE dbo.proc_TestAttachmentsFlag( 14925
    CREATE PROCEDURE dbo.proc_GetAllWebPartsInternal( 12993
    CREATE PROCEDURE dbo.proc_GetTpPageMetaData( 12701
    CREATE PROCEDURE dbo.proc_GetWelcomeNameForPath( 11655
    CREATE PROCEDURE dbo.proc_getNewObjects ( 10502
    CREATE PROCEDURE dbo.proc_getNewObjects ( 10502
    CREATE PROCEDURE dbo.proc_getNewObjects ( 10502
    CREATE PROCEDURE dbo.proc_getNewObjects ( 10502
    CREATE PROCEDURE dbo.proc_getNewObjects ( 10502
    CREATE PROCEDURE dbo.proc_SecListSiteGroups( 6650
    CREATE PROCEDURE dbo.proc_FetchDocForHttpGet( 6583
    CREATE PROCEDURE dbo.proc_GetWelcomeNameForPath( 6583
    CREATE FUNCTION dbo.fn_IsOverQuotaOrWriteLocked( 5183
    CREATE PROCEDURE dbo.proc_GetFolderRedirectUrl( 5072
    CREATE PROCEDURE dbo.proc_GetFolderRedirectUrl( 5072
    CREATE PROCEDURE dbo.proc_GetFolderRedirectUrl( 5072
    CREATE PROCEDURE dbo.proc_GetFolderContentTypeOrder( 4932
    CREATE PROCEDURE dbo.proc_SecRefreshToken( 4859
    CREATE PROCEDURE dbo.proc_GetFolderInfo( 4823
    CREATE PROCEDURE sp_sqlagent_get_perf_counters( 2559
    CREATE PROCEDURE dbo.proc_FetchChunkFromDocStreams( 1658
    CREATE PROCEDURE dbo.proc_GetListWebParts( 1388

    Comrade

    10 Temmuz 2012 Salı 14:44
  • Hi,

    It’s possible that a timer job has failed and is attempting to retry an upgrade on the DB’s.   

    Check the Timer Job status page to see if any upgrade jobs running.

    You can also run this during off-hours to force an upgrade if one was not completed:

    psconfig –cmd upgrade –inplace b2b -force

    When this command runs it will force an upgrade to occur.   It should reset any timer that wants to try an upgrade.

    If an upgrade really is occurring, the site will be down until it is completed.  Time frames can be as little as a few minutes to as long as a couple of hours.  It all depends on how fast the servers are and how big the databases it has to process.

    If you are unsure how long it will take, try it on a test farm with the same datasets to give an approximate time.

    Make sure you have a backup of all the SharePoint databases before doing this. 


    Regards, Savoeurn Va Microsoft Online Community Support

    11 Temmuz 2012 Çarşamba 21:56
  • Hi Savoeurn,

    these queries are calling even Sharepoint Timer Svc is stopped. When I type "/SiteAssets/print16.png" it creates proc_fetchdocforhttpget 6 times, proc_getlevel 4 times and some others. It is related with http requests I guess. not about timer jobs.


    Comrade

    16 Temmuz 2012 Pazartesi 18:23
  • Hi Again

    I found a link that says my query is wrong. The true query should be like:

    SELECT DISTINCT TOP 200
    t.TEXT QueryName,
    SUBSTRING(t.text, (s.statement_start_offset/2) + 1,
        ((CASE statement_end_offset 
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE s.statement_end_offset END 
                - s.statement_start_offset)/2) + 1) AS statement_text,
    s.statement_start_offset as STRT,
    s.statement_end_offset as ENDD,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    s.total_elapsed_time AS TotalElapsedTime,
    ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn,
    DB_NAME(t.dbid) as DBNM
    --,ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
    FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
    where execution_count > 1 --and dbid is null
    ORDER BY
    s.execution_count desc
    

    I did a mistake in my first query with ignoring start and end offsets in sql text.


    Comrade

    • Yanıt Olarak İşaretleyen caglar ozkul 16 Temmuz 2012 Pazartesi 18:54
    16 Temmuz 2012 Pazartesi 18:53