Recently we have migrated our SharePoint 2003 from one AD forest to another forest, apart of this change we have also upgraded our backend DB from SQL 2000 to SQL 2005. Recently i faced a very critical issue, one of the heavily used site is not showing search result and the search is timing out. In order to investigate this issue i have identified the query in the SQL profiler and ran it directly on the SQL management studio. It took around 10 minutes to show results in new DB Server (SQL 2005) but the same query is returning result in 1 sec in the old DB server (SQL 2000) i ran the query again with the Query execution plan against both servers and compared it, i have seen the cost for referencing Clustered index is very high in new DB server as compared to old DB server. After some researching i have planned to update DB statistics by using following command;
update statistics tablename
SharePoint create its own indexes which we cannot alter and it uses the hard coded name for indexes such as ix_databasename. As per my analysis after moving DBs from SQL 2000 to SQL 2005 the databases statistics were not updated and indexes were not using optimal path for searching for data that why it is showing results after long delays.