SQL Server EXPRESS 2008 R2 crippling performance issues

Question

My company has recently requested that all our developers gain knowledge and experience using SQL server database’s (as opposed to sitting in VS all day long). We are all using SQL Server EXPRESS 2008 R2 and everyone else seems to be running the setup just fine.

With me however, lies the problem. When running any kind of command it takes a very long time, to give you an example a stored procedure executed on a colleagues PC took 4 seconds, for me 7 minutes!

Of course before posting here I did a fair amount of research into the problem, I’ve tried running a disc defrag, MSDN trouble shooting and even completely reinstalling the program all with no change. When running a query with the resource monitor open I can see that SQL cpu image ssms.exe will run for afew seconds then not respond for a good few minutes, run another few seconds and repeat until the query is complete.

MY question is does anyone have any form of solution to this or perhaps a better idea of the problem?

Thanks

Answer 1

If the query is running over a large dataset (as SQL Server Express 2008 will not use more than 1Gb RAM, and that is shared amongst all data storage a few hundred Mb is pretty large) then the issue may be disk related. Does anything else preform badly on that machine?

If you run the "performance and reliability monitor" (available in Vista, 2008 and 7), what shows for the disk active time? If this is high then the process is bocking waiting for I/O. If your drive is not in a good state it might be encountering recoverable errors but taking its time to respond because of them. Check the drive's SMART parameters to see if it has recorded a problem - if it has replace the drive ASAP.