Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sandesh.ravi
Posting Yak Master
110 Posts |
 Posted - 2011-11-14 : 10:38:31
|
| I have a dedicated data base for reports and every day morning snapshot replication runs and then the indexes are rebuild.Later there will not be any insert or update on the database.The reports would be fine for couple of hours and then it would be slow. Once the indexes are rebuild again manually, it becomes faster for couple of hours. I am not sure why the queries are running slow. All the queries are simple joins. How often do I need to rebuild indexes? Thanks,Sandesh |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-14 : 10:46:28
|
| are reports using caching? if yes, whats the cache expiration period set?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sandesh.ravi
Posting Yak Master
110 Posts |
Posted - 2011-11-14 : 10:57:24
|
| Could you please tell me where to find that? I am not sure.Thanks,Sandesh |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-14 : 11:01:35
|
| Is it a particular report that runs occasionally and hogs the resources? Often someone will run a report, it will time out so they run it again - then the query is running twice so no hope of completing - this keeps happening and it gets worse and worse until someone restarts the server and it's ok again. Appears that the server is getting progressively slower but actually is getting overloaded. Often a couple of reports cause all the problems - chack what is running on the server at the time as this one is simple to resolve if it is the cause.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sandesh.ravi
Posting Yak Master
110 Posts |
Posted - 2011-11-14 : 11:07:58
|
| That is what exactly happening. But i was not able to figure it out that at first instance y there was time out expired. During that time, I kill the processes that are running and rebuild the index. It would be fast. Later after some 3 hours. The same issue occurs.Thanks,Sandesh |
|
|
|
sandesh.ravi
Posting Yak Master
110 Posts |
Posted - 2011-11-15 : 06:05:22
|
| is there any way to identify the issue? Please let me know.Thanks,Sandesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-15 : 06:49:14
|
quote:
Originally posted by sandesh.ravi
is there any way to identify the issue? Please let me know.Thanks,Sandesh
is it using inline query or procedure? can you analysis the execution plan for them and see what are costly steps?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-15 : 06:57:47
|
| Looks like an issue with parameter sniffing for me.Try putting option(recompile) in the queries.PBUH |
|
|
|
|
|
|
Queries running slow