How much memory does an empty table + index cost?

Question

How much memory does an empty table + index cost?

SSC Guru

Points: 323518

May 10, 2004 at 2:17 pm

#161914

After reading a little bit in the Books On Line, it looks to me that if you create a new table, with a clustered index on it's primary key, with no data in it you are going to use 2K of ram (1724 bytes for the index + 256 for the table);

do that same operation for 400 tables, and you are talking 800K of RAM, or just under a meg.

In My case, we are planning on adding 400 tables to an existing database, so that two applications that previously used two different db's can share the same one. The objective is a single database schema that can support either application, or even both applications if necessary.

Clients would be concerned about performance issues, of course, so I'm researching the potential impact. We are just researching the additional load these empty tables might add, because all but two clients will never use both applications at the same time.; the actual load testing for the dual use clients will be next.

I'm very weak on Pages in SQL Server memory... I seem to misremember that a clustered index got it's own page in memory. Doesn't each page in memory take 64K? That makes me think that I might have an additional overhead of 4K x 400 or another 2.56 MEG of ram eaten up.

There may be other factors, but I was looking for a rule of themb that an empty table with an empty index used x bytes of memory;

Could anyone out there give me a heads up or a way to test this out?

From BOL:

Memory Used by SQL Server Objects Specifications

This table lists the amount of memory used by different objects in Microsoft® SQL Server™. The information in this table does not pertain to Microsoft® SQL Server 2000™ Windows® CE Edition.

	Object Size
Object	SQL Server 7.0	SQL Server 2000
Lock	96 bytes	64 bytes plus 32 bytes per owner.
Open database	2,880 bytes	3924 bytes plus 1640 bytes per file and 336 bytes per filegroup.
Open object¹	276 bytes	256 bytes plus 1724 bytes per index opened on the object².
User connection	12 KB + (3 * Network Packet Size)³.	12 KB + (3 * Network Packet Size)³.

¹ Open objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints.

² Indexes can be opened on tables or views.

³ Network Packet Size is the size of the tabular data scheme (TDS) packets used to communicate between applications and the relational database engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Hans Lindgren SSChampion Points: 10496 More actions · Answer 1

Hello!

Just a thought. Didn't you Quote from BOL: 1 Open objects include all tables

Wouldn't that indicate objects that are Open. I.e not just Tables that are existing but Tables that somthing has Opened and not closed? So if most of your tables are unused, they wouldn't take memory?

I have not tested but I believe they mean it like that.

Regards, Hans!

Lowell SSC Guru Points: 323518 More actions · Answer 2

Thanks for the reply Hans;

This has been kinda educational for me.Here's what I had assumed:

a database is open or closed depending on the autoclose option of the database....so on a developer machine this might flop back and forth and use/release memory when someone access the db.

But I had figured that all objects were "open" if the database was open... if there were 3000 total objects, then that would mean ~6Meg is required to support all the SP's, views,indexes,constraints,tables, etc.

I found in the BOL "Pages and Extents" section that a table and it's used a shared extent(8 pages) until it grows beyond that size, and at that point receives it own extent(s) as required to hold it's contents...according to the diagram there you can see that one extent might hold 8 objects in it(4 tables/4indexes as an example), my 64K assumption is a little generous.

I'm still investigating, and will try and report what i find.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 3

Interesting thought. A page is 8k, but memory may operate in extents. If you create a table, at a minimum, 64k is allocated, in that an extent is allocated. Other objects could be in that extent over time, however. The allocation isn't specifically to that table.

As far as memory usage with lots of tables, keep in mind that the amount of memory used per table could be larger as more data is cached. It also could be reduced if the object hasn't been referenced in awhile. The memory usage is balanced over time and items are swapped out. Just like the OS will use a swap file to simulate a 4GB space, the "open" items may be swapped out of memory if other needs arise.