Adding RAM to SQL 2005 SE, but 2000 Compatibility

Question

Adding RAM to SQL 2005 SE, but 2000 Compatibility

homebrew01

SSC Guru

Points: 55548
More actions
July 29, 2013 at 7:53 am

#280725

We have a Windows Sever 2003 server, running SQL 2005 Standard Edition SP3 with 4 Gig RAM. I would like to increase the RAM. However some of the databases are running in 2000 compatibility mode, due to legacy code not supported in 2005.
Will processes running in those databases benefit from additional RAM ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

All that compatibility level does is change the way that the query processor executes some specific SQL statements, ones that changed behaviour between 2000 and 2005 (and whether or not some constructs will parse or not). That's all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Thanks.

I was prompted to look at RAM as a solution after getting this error message when trying to run a stored procedure:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's a client error. not a server error. Sql server isn't written in .Net and yet that's a .net error.

You'll get that if you're pulling a huge resultset into Management Studio and you run your client machine out of memory (or just SSMS, it's a 32-bit app, so it can use 2GB memory at most)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

homebrew01

SSC Guru

Points: 55548

July 29, 2013 at 8:58 am

#1636680

Thanks. Sounds like I am misplacing the blame.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Oh, and don't run Management Studio on the server and run queries that return huge resultsets....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

GilaMonster (7/29/2013)
Oh, and don't run Management Studio on the server and run queries that return huge resultsets....

I was starting to troubleshoot a timeout error that an application is having. I suppose it must be doing something similar.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

If the app's pulling enough data to make Management Studio run out of memory, I'm not particularly surprised that it's timing out. That'll be a lot of data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass