i ahve dedicated SQL server 2005 Database server for supporting one application.
Server Configuration is : O/S:64-bit, Microsoft SQL Server Standard Edition (64-bit), 4 processes with 12 GB RAM. currently we have 150 concurrent users are performing operations.here i am i am facing the issue like "Timeout expired. The timeout period elapsed prior to completion of the operation
or the server is not responding." while installing SQL server Instance i didn't change any configuration setting. The Max worker treads is default is 0 ..
Can any one suggest me if i changed from 0 to some other valve it will improve the perfomance.
Thanks in advance,
I don't think changing the max worker thread setting will solve your problem. And even if it wouuld solve the problem I see it more as a last option if everything else fails.
A better approach is investigating the process which causes the timeout. Timeout errors are generated by the frontend application, since SQL Server doesn't have a query timeout. It's just the client, which won't wait any longer.
You should try to find out what queries are taking too long and why are they taking so long. Is it because the database is huge and the query very complex, do you need to add indexes or is it because different processes are blocking each other.