Sql-server – Grant DBA permissions on a SQL server

SQL Server Management Studio does a query against the sys.databases catalog view. All databases are listed in that DMV. The stored procedure sp_msforeachdb has logic which calls the function has_dbaccess() to parse its list of databases. It obviously doesn't want to try and run a command against a database to which you don't have access. Do you have access to all of the databases in question with the account used to run the query?

The answer here is easy, buy more RAM and install it in the server. With only 4 Gigs of RAM, SQL can probably only use 2 Gigs with the other 2 Gigs being used by Windows. That only gives SQL about 1 Gig of memory for it to buffer data in. Once that buffer is full any other data that SQL needs has to come from the disks. My guess is that you are pulling data from the disk, leaving it in memory for just a few seconds and then flushing it out to make room for new data to go into memory.

Start by adding in more RAM. Go for 16 Gigs total (12 more) and see how that helps performance.

Then have someone do some performance tuning on the indexes and see if there are any indexes which need to be added or removed. Have you used SQL Profiler to identify the expensive queries and tune them?