locked
sql server 2012 migration .net timeout problem RRS feed

  • Question

  • hi, experts, I am doing the migration project for my company, the application is .net4 web form application (not mvc) using ado.net

    the development tool is vs2010 professional ( I think there should be no impact)

    from 2005 to 2012

    during the tests on the 2012. I hit a strange problem, described as below

     1. tests normally on many asp.net web forms, performance is normal

    2. suddenly timeout and prompts timeout error screen (asp.net one) on web page of several asp.net web form pages.

    3. communicated with colleagues, one colleague modify a common database table and added a new column (the new column should have no index associated) 

    4. as that colleague was doing something urgent, I asked him to rebuild all indexes in that database when he quit office

    5. on 2nd day, I continue tests, everything works fine and performance is okay (the status is as same as point 1)

    6. up to now, 20 days later, (no indexes were rebuilt from point 4) , no timeout problem prompts.

    is it normal ? or should be caused from others' issues? please help.


    delaynomore.

    Wednesday, April 22, 2015 7:56 AM

Answers

  • Hello,

    It is recommended to rebuild indexes and update statistics with full scan option after upgrading from one version of SQL Server to a newer version. Timeout errors, high CPU usage, high I/O storage activity, and high use of memory are possible when scans on tables occur for not following this best practice.


    It is also recommended to perform maintenance of index and statistics regularly.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, April 22, 2015 12:32 PM
  • so, update statistics with full scan option is another task. is it ?

    Hi delaynomore,

    Yes.

    Rebuilding an index, for example by using the ALTER INDEX … REBUILD statement, will update only index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update any column statistics.

    However, by default, the UPDATE STATISTICS statement updates both index and column statistics. And the UPDATE STATISTICS statement uses only a sample of records of the table, using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

    For more details about rebuilding indexes and updating statistics, please review the following blog:
    http://www.benjaminnevarez.com/2010/07/rebuilding-indexes-vs-updating-statistics/


    Thanks,
    Lydia Zhang



    Lydia Zhang
    TechNet Community Support




    Thursday, April 23, 2015 6:37 AM

All replies

  • Hello,

    It is recommended to rebuild indexes and update statistics with full scan option after upgrading from one version of SQL Server to a newer version. Timeout errors, high CPU usage, high I/O storage activity, and high use of memory are possible when scans on tables occur for not following this best practice.


    It is also recommended to perform maintenance of index and statistics regularly.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, April 22, 2015 12:32 PM
  • hi, alberto, previously , I had only rebuilt indexes for all tables.

    so, update statistics with full scan option is another task. is it ?

    I will find this function on management studio


    delaynomore.

    Thursday, April 23, 2015 3:25 AM
  • so, update statistics with full scan option is another task. is it ?

    Hi delaynomore,

    Yes.

    Rebuilding an index, for example by using the ALTER INDEX … REBUILD statement, will update only index statistics with the equivalent of using WITH FULLSCAN. Rebuilding indexes does not update any column statistics.

    However, by default, the UPDATE STATISTICS statement updates both index and column statistics. And the UPDATE STATISTICS statement uses only a sample of records of the table, using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

    For more details about rebuilding indexes and updating statistics, please review the following blog:
    http://www.benjaminnevarez.com/2010/07/rebuilding-indexes-vs-updating-statistics/


    Thanks,
    Lydia Zhang



    Lydia Zhang
    TechNet Community Support




    Thursday, April 23, 2015 6:37 AM