Search query very slow - 14 Hive log - Performance

• Saturday, May 12, 2012 10:38 AM

   

   
  

  0

  Hello,

  We have a custom intranet solution at our sharepoint farm. At start page we have a couple of web parts with search query behind.

  We migrate this solution and content from SP 2007 to SP 2010.

  After we made performance test with load testing, we get a response time at our intranet start page for 25 seconds, very bad.

  Here are some entries from 14 Hive log:

  Slow Query Duration: 13042.9722213298

   --------------------------------------------------------

  ConnectionString: 'Data Source=zsqlclusterpre;Initial Catalog=PRE_Farm_WSS_Content_2007;Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15' ConnectionState: Open ConnectionTimeout: 15

  --------------------------------------------------------

  Slow Query StackTrace-Managed:

  at Microsoft.SharePoint.Utilities.SqlSession.OnPostExecuteCommand(SqlCommand command, SqlQueryData monitoringData)

  at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)

  at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)

  at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock)

  at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListsWithCallback(String bstrUrl, Guid foreignWebId, String bstrListInternalName, Int32 dwBaseType, Int32 dwBaseTypeAlt, Int32 dwServerTemplate, UInt32 dwGetListFlags, UInt32 dwListFilterFlags, Boolean bPrefetchMetaData, Boolean bSecurityTrimmed, Boolean bGetSecurityData, Boolean bPrefetchRelatedFields, ISP2DSafeArrayWriter p2DWriter, Int32& plRecycleBinCount)

  at Microsoft.SharePoint.Library.SPRequest.GetListsWithCallback(String bstrUrl, Guid foreignWebId, String bstrListInternalName, Int32 dwBaseType, Int32 dwBaseTypeAlt, Int32 dwServerTemplate, UInt32 dwGetListFlags, UInt32 dwListFilterFlags, Boolean bPrefetchMetaData, Boolean bSecurityTrimmed, Boolean bGetSecurityData, Boolean bPrefetchRelatedFields, ISP2DSafeArrayWriter p2DWriter, Int32& plRecycleBinCount)

  at Microsoft.SharePoint.SPListCollection.EnsureListsData(Guid webId, String strListName)

  at Microsoft.SharePoint.SPListCollection.GetListByName(String strListName, Boolean bThrowException)

  at Company.SolutionName.Publish.NaviControls.FlyoutLeftGlobalNavigation.GetConditionsFromGlobeMegaMenu(SPWeb rootWeb)

  at Company.SolutionName.Publish.NaviControls.FlyoutLeftGlobalNavigation.OnLoad(EventArgs e)

  at System.Web.UI.Control.LoadRecursive()

  at System.Web.UI.Control.LoadRecursive()

  at System.Web.UI.Control.LoadRecursive()

  at System.Web.UI.Control.LoadRecursive()

  at System.Web.UI.Control.LoadRecursive()

  at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

  at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

  at System.Web.UI.Page.ProcessRequest()

  at System.Web.UI.Page.ProcessRequest(HttpContext context)

  at ASP.GLOBEHOMEPAGE_ASPX__350795972.ProcessRequest(HttpContext context)

  at Microsoft.SharePoint.Publishing.TemplateRedirectionPage.ProcessRequest(HttpContext context)

  at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

  at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

  at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)

  at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)

  at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)

  at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

  at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

  at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

  at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr managedHttpContext, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

  SqlCommand: 'proc_EnumLists' CommandType: StoredProcedure CommandTimeout: 0 Parameter: '@RETURN_VALUE' Type: Int Size: 0 Direction: ReturnValue Value: '' Parameter: '@WebId' Type: UniqueIdentifier Size: 0 Direction: Input Value: '54e77d72-f7ff-4e64-999d-1c1a4a82794a' Parameter: '@Collation' Type: NVarChar Size: 4000 Direction: Input Value: 'Latin1_General_CI_AS' Parameter: '@BaseType' Type: Int Size: 0 Direction: Input Value: '' Parameter: '@BaseType2' Type: Int Size: 0 Direction: Input Value: '' Parameter: '@BaseType3' Type: Int Size: 0 Direction: Input Value: '' Parameter: '@BaseType4' Type: Int Size: 0 Direction: Input Value: '' Parameter: '@ServerTemplate' Type: Int Size: 0 Direction: Input Value: '' Parameter: '@FMobileDefaultViewUrl' Type: Bit Size: 0 Direction: Input Value: '' Parameter: '@FRootFolder' Type: Bit Size: 0 Direction: Input Value: '' Parameter: '@ListFlags' Type: Int Size: 0 Direction: Input Value: '-1' Parameter: '@FAclInfo' Type: Int Size: 0 Direction: Input Value: '1' Parameter: '@Scopes' Type: VarBinary Size: 0 Direction: Input Parameter: '@FRecycleBinInfo' Type: Bit Size: 0 Direction: Input Value: '' Parameter: '@UserId' Type: Int Size: 0 Direction: Input Value: '' Parameter: '@FGP' Type: Bit Size: 0 Direction: Input Value: '' Parameter: '@RequestGuid' Type: UniqueIdentifier Size: 0 Direction: Input Value: '26cc13e6-5fc0-430f-a629-04cffdb39478'

  Now I wound to look, where the bottle neck, which query is so slow is.

  How can I monitor these queries, with which tools can i do this?  Is there option to see at sql server – tracing.

  We made another performance test’s, at default teamsite - 500 concurrent user, response time 1,5 seconds, this is great, I think.

  I hope somebody can help me, give me some tip’s to find the bottle neck,

  Horst

  ---

  Thanks Horst MOSS 2007 Farm, MOSS 2010 Farm, TFS 2010

  • Reply
  • Quote