This is a great question!

My long-term approach has been to schedule a re-organize of indexes Monday-Saturday nightly, and a re-build of indexes on Sunday nights.  As you would know be careful not to schedule the rebuild to take place in a time when users might be logged in and using the system.

If the client complains about specific performance problems in specific areas, I find its best to use the "Recommend missing Indexes process" on tables relating to the performance problems, then create those Indexes.  If you Google "Create missing indexes in Microsoft SQL" you should find lots of tutorials to do this.

Cheers...Tim

Tim, Thank you very much for this helpful feedback!

No worries, I was a bit vague on the Missing Indexes part.  Basically what I do is ask the users to perform the activity that is running slow, then open the Activity Monitor -> Expensive queries -> Show Execution Plan.  Clicking on the Select component if there are any missing indexes in green above, I right-click on the Select component -> Show Missing Indexes.  You can then review them, copy the Create Index queries -> paste and execute.  Hope this helps!

No worries, I was a bit vague on the Missing Indexes part.  Basically what I do is ask the users to perform the activity that is running slow, then open the Activity Monitor -> Expensive queries -> Show Execution Plan.  Clicking on the Select component if there are any missing indexes in green above, I right-click on the Select component -> Show Missing Indexes.  You can then review them, copy the Create Index queries -> paste and execute.  Hope this helps!

Tim, Thank you again for the helpful guidance. I'll explore the Execution Plan and potential Missing Indexes as well.