Mail's memory footprint will depend on how you use it, but on average it should take up between around 50MB and 200MB of memory. For instance, I currently have 3746 messages in my in-box from 5 e-mail accounts (two Gmail accounts and three from other providers), as well as a few RSS feeds. With this setup on two machines Mail uses 104MB of RAM on my Snow Leopard machine, and 62MB on my Leopard machine. Some people, however, seem to run into problems where Mail will spike in RAM usage, resulting in system slowdowns.
If this occurs, you can try the following to help the problem:
1. Use Safe Mode
In an Apple Discussion thread on the issue, a few people have had success by booting their systems into Safe Mode, running Mail, and then rebooting normally. Doing this will clear some caches that may contribute to the problem, and is easy to do (hold Shift at start-up) so give it a try. You can use OnyX or another maintenance utility to run system cleanup scripts, especially those pertaining to the user account.
2. Rebuild Mailboxes
Select one or all of your mailboxes and choose "Rebuild" from the "Mailbox" window. This will ensure the items in the mailbox are properly indexed and available to the program, and clean up any errors with your messages. You can also reindex your in-boxes by closing Mail, moving them from the /username/Mail/ folder (they will be prefixed with "POP-", "IMAP-", or "Exchange-") and then launching Mail again. This will recreate the in-boxes and repopulate them with files from the server. Do keep in mind that this will not save messages you have deleted from the server, so you may lose some messages if you do not save them. You can do this first by right-clicking the mailbox in Mail and choosing the "Archive Mailbox" option.
3. Remove e-mails with attachments
Mail renders attachments in messages for easy viewing; however, this may be what is causing the high memory usage. Try clearing out messages that either have attachments or that are formatted in HTML and load content from the internet when you view them.
4. Remove preferences
A common reason for application problems is faulty preference files, and Mail is no exception. Try removing the preference file for Mail (called "com.apple.Mail.plist" and located in the /username/Library/Preferences/) folder and then relaunch Mail. You will need to enter your mail account information again, so be sure to write this down before removing the file, but your mailbox organization and previously downloaded messages will be preserved.
5. Reapply the latest combo updater
As a blanket solution for odd problems with Apple-supplied software, you can try reapplying the latest "Combo" updater for your system. Search through Apple's support website (http://support.apple.com/downloads) for the most recent one pertaining to your version of OS X (Tiger, Leopard, Snow Leopard--avoid the "Server" versions), and download it. Then reboot into Safe Mode, run a permissions fix with Disk Utility, and run the updater.