We have the following setup for an web application:
Server Intel Xeon E5606 @2.13GHz (2 processors - quad core) 24GB RAM
Software SO: Windows Server 2008 R2 Standard (SP1) x64 Microsoft SQL Server 2008 R2
Database environment description: The SQL server is hosting 1 database that is accessible from our applications: DB1 - production database (around 1.5 million records)
The application has around 1.5 million pagevies per day.
Problems encountered:
- FTS Index rebuilding -
Each time the index is being rebuilt, the CPU rises to 100% - this I think it is normal, but the problem is that, after it is finished rebuilding (which takes 1-2 minutes), the CPU remains to 100% (sqlserver process); The current solution to the problem is
a. Go on application servers, and stop IIS b. Wait for SQL Server to finish serving pending requests c. Start back up the IIS servers, and now CPU on SQL server is back to normal
Can you please tell me if you have an explanation for this behavior? Is there a way of rebuilding the FTS index without affecting server performance?
- Overall CPU on SQL server
The application has a lot of traffic, which generates a large number of requests for SQL server; this is directly affecting the SQL server, and the CPU is now averaging to 80% all the time, which causes slow responses for the application pages
The questions here are: Do you have any suggestions regarding: SQL FTS optimizations for such a high traffic application Index optimization
