locked
SQL error: A time out occurred while waiting to optimize the query. Rerun the query. RRS feed

  • Question

  • Server specs:

    WFE - MOSS 2007, 16GB RAM

    SQL - SQL 2005, 16GB RAM

     

    I'm having an issue with data entry into a very large list.  The list is ~300 fields and I am attempting to edit it in datasheet with about 240 of these fields shown.  I understand that this is on the border of what MS recommends for list views. 

    I am copying/pasting data from an Excel spreadsheet into my datasheet view.  I was adding 5 rows of data at a time.  Each time I would wait for it to sync the changes, it would throw a conflict error which stated that a timeout had occurred.  I clicked Discard All My Changes (retry changes would just error out again) and refreshed my list to find that the data had been successfully entered despite me telling it to discard.  I went on with this routine until I hit 104 items in my list, at that point my datasheet view would hang, only listing 99 items (ignoring my last batch).  Checking a standard view of the list I could see the last batch was there but the datasheet view was hung at 99 (stating Calculating totals...).

    I tried to break my view into smaller chunks but each time I hit OK to save my view it would just sit, trying to load the next screen.  The worst issue is that this is affecting the entire site collection.  Folks began reporting weird issues with pages timing out and similar datasheet errors.  Now that I've stopped messing with that list the site is back to normal.  The only clue I have as to a cause is an error in the SQL event log.

     

    Event Type:    Error
    Event Source:    MSSQLSERVER
    Event Category:    (2)
    Event ID:    8628
    Date:        4/27/2011
    Time:        11:38:11 AM
    User:        ***
    Computer:    ***
    Description:
    A time out occurred while waiting to optimize the query. Rerun the query.

    Any assistance would be appreciated as I have about 300 more records to enter!

    • Moved by Mike Walsh FIN Wednesday, April 27, 2011 4:15 PM admin q not general (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    Wednesday, April 27, 2011 4:02 PM

All replies

  • SharePoint store one piece of record in AllUsersData in content database, for those list with large number of columns, it may take more then one piece of record, which is called row wrapping (see column limit section in http://technet.microsoft.com/en-us/library/cc262787.aspx#Column ).

     

    I created a list with 200 text columns in SharePoint with following script:

    [system.reflection.assembly]::LoadWithPartialName("Microsoft.SharePoint")

    $spsite=New-Object Microsoft.SharePoint.SPSite(“http://moss.litware.com”)

    $spweb=$spsite.rootweb

    $spweb.Lists.Add(“MyList”,”MyListDescription”,”GenericList”)

     $splist=$spweb.lists["MyList"]

     for ($i=1;$i -le 200; $i++) {$splist.Fields.Add($i.ToString(),"Text", $false) }

     

    And paste 542 line of records from Excel into the DataSheet View. The resulting records in AllUsersData table is 2168, that is, 4 records per list item. The copy and paste works fine, I assigned 2G RAM for my MOSS 2007 test Virtual Machine.

     

    Since more than 2000 piece of database table records involved in one page(the datasheet view), there may be SQL lock escalation occurred so that the entire AllUsersData table is locked, which may explain your entire site collection is affected phenomenon.

     

    As to the error message you pasted, please take a look at http://technet.microsoft.com/en-us/library/cc966540.aspx .

     

     

     

     

    Gu Yuming

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com

    Thursday, April 28, 2011 9:43 AM
  • Thanks for the response!

    I think you're on to something with the SQL lock issue.  Late last night, while on the phone with MS, I was able to enter records and refresh my data view and see them.  I still had the timeout error message and needed to click Discard my Changes each time but it was saving the items I was adding and it was refreshing the data view to show these new items.

    This morning I tried the same method and the data view is refusing to refresh the new data.  It's again stuck at 100 items and is not showing me the 4 additional items that I know are present in the list.  It says 'retrieving data' at the bottom of the data view for a minute or two, then throws up a pop up that states: "Cannot connect to the server at this time.  You can continue working with this list, but some data may not be available."  I actually just had to do an iisreset to get the site back to normal, it was throwing up random timeout errors on other sites.

    So during off hours I was able to refresh the data view (I tested all the way up to 240 items in the list) but now during peak usage I am only able to refresh 100 items before erroring out.

     

    EDIT: I just noticed that when refreshing my data view I could watch as the CPU on  the SQL box went from ~1-2% to 100% for one of the cores (quad core), then a 2nd core began to spike.  As soon as the list view was loaded (successfully this time after an iisreset), the CPU usage went back to normal.  Not sure if this represents a bottleneck in the system or not.


    Thursday, April 28, 2011 3:06 PM
  • This time, I opened SQL Server Profiler and tried copying the 400+ line of list items from Excel to SharePoint list Datasheet view, and refreshing the Datasheet view. But I failed to catch any Lock:Escalation Event. And find no 8628 Event in my Windows Event log.

     

    Could you please upload your Excel file and SharePoint content database (or SharePoint site or list template) here: https://sftasia.one.microsoft.com/ChooseTransfer.aspx?key=e84f6928-64fc-4114-b8a0-f240cac6d5a8, so that I can try to reproduce your issue in my test machine. Or please upload some screenshots which may help me to understand the problem you are facing.

     

     

     

    Gu Yuming

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com

    Friday, April 29, 2011 5:42 AM