| Author |
Topic |
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
 Posted - 2012-08-23 : 05:06:44
|
| One of our customers uses SQL Server with our application. We have noticed that the user queries become very slow after the SQL Server and application server have run for 10-12 days. I have also turned on SQL rofiler to log SQL statements in a trace file that take more than 10 seconds to execute. When the application runs normally, such queries are only around 3-4 in number. When the slowness occurs, we have many more queries that take more than 10 seconds to execute. Yesterday when the slowness occurred, I noticed that 3 queries of same type took each around 30 minutes to execute. When this happened, it jammed the entire application server and we were left with no other option but restart both SQL Server and application server. After restart when I executed the time consuming SQL in Management Studio, it still took long time to execute. So, I had to cancel its execution as it again jammed the entire application.As it is production time now, I am yet to run the Database Tuning Advisor on the trace file. Meanwhile, my questions are:1. What other areas can I look for?2. Can one time consuming SQL jam everything else? |
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2012-08-23 : 06:59:04
|
| I want to add something to the above post. The issue occurs on a VM on ESXi Server. The machine has four CPUs and 12 GB RAM. Also, the SQL Server holds only one database. The database size is 4-5GBs. No other resource consuming application runs on this server. The machine is designated to host only application specific database.Has anybody else ever seen that SQL Server becomes slow after few days of operation on virtual machine? |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-08-23 : 16:56:30
|
| 1) How much new data is being added over time? Are you rebuilding indexes on a regular basis? Do things improcve when you rebuild indexes?2) Yes, one bad query can have a cascading effect on other queries.=================================================We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry |
|
|
|
rx2526
Starting Member
4 Posts |
Posted - 2012-08-24 : 00:17:35
|
| Have you tried to update stats with sample rate of 30 percent or more ?EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'orEXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH SAMPLE 30 PERCENT ' |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-24 : 03:33:21
|
| If the update statistics doesn't solve the issue , next , try a reindex, if that doesn't solve your problem - use Profiler (which a;lready seem to be using) identify the slow running queries - and analyse the execuion plans . For example , can you see anything suspicious like Scans ? Is the code scalable? Once you've exhausted those possibilities, start looking at other factors , such as memory allocation though VMJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-24 : 04:03:40
|
| You guys realize Profiler will only exacerbate your problem. You should never run it on a production server. Use a server side trace instead.-Chad |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-24 : 04:06:24
|
| Have you tried to tune that query? Have you looked at the execution plan? Is it indexed properly?-Chad |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-08-24 : 12:49:24
|
| I'd avoid using the Database Tuning advisor . It can give some good hints , but go through the 3 steps : Statistics, reindex , identify slow queries and tune.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
Performance Issue