How to improve SQL Server performance as most of the times it returns a message "Timeout expired"?

Question

The Server is actually structured to entertain corporate level business needs and bulk requests are generated by different type of applications like web, data engines and many other applications that fulfill the business needs. The problem is that during the peak time of business activities the server's CPU performace jumps to 100 therefore data retrieval becomes slow and most of the times it returns a message "Timeout expired". Please do help me if somebody know a solution to it.

Answer 1

Okay I would strongly recommend you check out the following:



(1) Ensure your Production Database is seperate from your Reporting database. An OLTP (ie: Online) Database should be seperate from a reporting (OLAP) Database for load reasons.

(2) Your OLTP Database should typically be indexed less than your OLAP Database as index updates slow down inserts and updates.

(3) Ensure all calls to the database are via Stored Procedures rather than any type of SQL query. ie: You need to protect the database server from someone or something (eg: badly written app) doing SELECT * FROM TABLE and doing complete table reads, or queries with loads of joins etc.

(4) It may make

(5) You mention that the Server ..." is actually structured to entertain corporate level business needs and bulk requests are generated by different type of applications like web, data engines and many other applications that fulfill the business needs. " This implies to me that your Server is doing alot of work. SQL Profiler can be used to determine what the Server is actually doing, and where the cost in terms of performance resides.

(6) I also recommend doing some perfmon analysis. Stats such as CPU, Memory, Network queues and Disk queues are useful.

(7) Run a DBCC CHECKDB every so often and resolve any.

(8) If your data is changing often, updating Statistics is another useful thing to look at.

(9) When the SQL box is going nuts it's useful to check which process is using up most of the CPU. If it's sqlservr.exe (ie: the SQL Server service) then it's probably useful to do an EXEC sp_who from SQL Query Analyser and see what's running and also check whether there is any lock contention.



These are just some simple things to do. But remember you need to work hard to protect the Server from any unusual, unecessary work so I would look at that as an absolute top priority.



Best of luck.

Answer 2

Were not the same faith as individual. but there are a standard faith that Jesus Christ mentioned like "...still other seeds fell on good soil and produced a crop; some produced a hundredfold, others sixty and others thirty." Matthew 13:5. he meant the knowledge and good deeds we received. They might some an unanswered prayers as they may say 'faith must big as a mustard seed.(Luke 4:30) or prayer can move mountain.(Matthew 21:21-22) 'whatever you ask in prayer full of faith, you will receive.' We must heed the message "All Scripture is inspired by God and is useful for teaching, refuting error, for correcting,and training in christian life. Through Scripture the man of God is made expert and thoroughly equipped for every good work" 2 Timothy 3:16-17. then prayers will be answered.

Answer 3

cpu performance is optimum up to about 70%. Over this and the strain on the system grows exponentially.



you'll need to up grade your network to be sufficient at dealing with the number of requests your getting, i.e. more hardware to split the traffic. Alternatively, go through your code and make sure everything you're using is essential. If you can live without a process do, every process is more CPU time and memory taken up.,

Answer 4

you asked and answered your own question.



In performance issues you have to determine the bottleneck. In your case it is clear that the CPU is causing the bottleneck performance issue.



I think you should upgrade your CPU if you're sure that what's keeping it busy isn't a virus or spyware.