Just to update mine, after leaving it over night from doing the reindex patch, everything has been working reasonably well again since. However I know that in 2 months time this issue will definitely crop up again.
Just to add my own experience to this, we also have been experiencing this decent into slowness even just logging into sims take twice as long as in used to. even though the server is eating up CPU's and Memory.
@jinnantonnixx Did you ever get Bob Carswell document about maintenance plans? Basically it was a pretty word doc covering what your talking about but also some SIMS store procedures that are worth running regularly - like the valid memberships. It also covered other bits like renaming the sa account, testing your SQL backups and having a test system.
edit: forgot to say it also included recommend frequencies as well - ie this every month, that ever day, and this, this you only want to run yearly.. about this time of year.
Afraid not, still trying to find my copy he sent me, the joys of only having a 100mb mailbox :( He's retired now so I've got no hope of getting another copy to make it worse.
I think setting up a separate DW for reporting would be a start. At least then you wouldn't have to fight reporting speed (less joins\logic) vs operational speed (less writes).
Hmm it wont let me run the Maintenance plan for some security reason.
Is this worth going through the hassle of setting this up?
The plan only takes five minutes to set up. Set it to run it over the weekend and see if there's an improvement on Monday. If you do try it, can you let us know the result?
Important point - SQL Express edition doesn't have the SQL Agent and SSIS. Both of these are needed for maintenance plans.
It's possible to script this as TSQL script which can be run on the server or remotely, but this wouldn't be as trivial to set up.
I am wondering if we could get an Ok you can do this from @PhilNeal ?
The script does sound like it would be helpful - but I really don't want to be doing anything with the sims database without capita oking it first.... (yes I'm a chicken... buck buck) :D
We also seem plagued by this , would also be curious to hear if the reindexing management plan as kindly posted by jinnantonnixx helps! Tempted to bite the bullet and give this a go anyway :)