We just got a new Windows 2008 server running SQL Server 2008 R2.
Our system is written in 'classic' ASP (I want to retire).
After a reboot the server works great and all aspects of the website function quickly, then, after a few hours, all updates start to generate timeouts and all sorts of screen start to fall over. Then my life becomes hell. At the moment I just reboot the server to give the others another few hours of work time.
Does anyone have any idea what would cause these timeouts to start happening after a few hours of operation?
If it's actually memory related, I would expect SQL to be maxing out the memory.
Is there a way I can test for long-running queries?
Depending on which version of SQL you have, fire up Profiler.
How can I check index fragmentation levels
In SSMS, right click on an index, select Properties, and go to Fragmentation. To look at all the indexes on a table, use DBCC SHOWCONTIG (although it's a bit more difficult to decipher): DBCC SHOWCONTIG (TableName) WITH ALL_INDEXES