I have a thin C# application with SQL server 2005 standard Edition . Database size is 15GB . The server has 16 GB RAM and 2.6 Ghz Xeon processor, with 4 cores. Application has lot of stored procedures performs some calculations and transfer the
data from one database named a to second database named b. Users can also get the reports from report database .
1: The application is much slow when it perform analysis (some calculations )
2: CPU usage goes to 100% and sometimes application crashes with this message.
timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I have to optimize the application . I have found some duplicate non cluster indexes.
for example if i have primary key PK index (clustered) on the fields 1, 2 ,3
there is another index which is non clustered on the same fields (1,2,3)
there are around 10 to 15 tables with the same duplicate non clustered indexes.
I have few Questions :
How can i configure the sql server 2005 to perform parallel executions of queries to utilize the all cores of CPU.
2: How can i know which query or store procedure is taking too much execution time and getting system resources.
3: How to know which index is fragmented&n
View Complete Post