I am using SQL server 2008 for my web developement with java, i am using Hibernate in java,
because of some heavy loaded query with so many filtering Criteria, Order by, Group by, Joins my sql server goes down.
any suggestion or mechanism by using i am prevent my server goes down.
thanks is advance for any help/suggestion.
Identify which specific queries are causing the heaviest strain on your system (SQL Profiler is your friend, there's a free one if you don't have the appropriate MSFT license).
Focus on the heavy queries first. See what the query plan is (SQL Server is great about suggesting changes to indexing strategy). If necessary, rewrite the worst offenders by hand or configure Hibernate to use a different query strategy.
Also investigate first and second level caching in Hibernate. Proper caching can greatly relieve pressure on the database.
By that point you should have specific questions that lend themselves to a more specific answer.
Why you are using heavy loaded query , you can try with procedures . It will be increase the performance rather than passing heavy quires. And also you can create indexes for frequently used queries.
check this, http://forums.codeguru.com/showthread.php?t=337736
Here's a free tool that may help as well. Once you've captured your SQL Server trace (using SQL Profiler etc) you can analyze the trace in great detail and very intuitively with the free Query Analyzer tool. You can even compare traces (for example, before-and-after an application change or environment modification) to see the effect on the database workload.
The tool may be downloaded here: Qure Profiler - Free SQL Server Trace Analysis Tool
