main memory database request
-
I am a computer master researcher, and i have been faced with my final research on How can I build an in-memory database system for SQL Server database.
the idea on this is on eliminating the disk I/O by loading the whole database into memory i.e the entire data will be resident in memory so the request will not need to hit the disk for the missed cache data and it will perform its task into memory . the gain from this is enhancing the performance tuning.
i searched the net for a guidance or any light that could lead me to know how can i do such work but without any result. i just want someone to give me how can i arrange my ideas and how can i do like this work. can i find this one is you.please it is urgent for me and i really want to achive this work. i found a lot of in-memory database engine but without any guidance on how they build this application.
would you please reply to me.
Regards....
Hoodi -
Are you asking how to make MS SQL Server keep a DB entirely in memory, or are planning to use any DB engine (eg Oracle, MySQL, etc).
There are many in memory databases for the development environment I use - Delphi. One off the top of my head is TSQLMemTable. There is another from ComponentAce that is highly recommended.
If you are planning to use Microsoft SQL Server, then you'll need to get some RamDisk software. I did a quick google search and found many companies are providing this software.. I only looked at one which was inexpensive (US$49) and seemed to do a nice job. http://www.cenatek.com/product_ramdisk.cfm
-
I understand you want to find how to build an in memory database for SQL server but SQL manages it's memory to keep as much data in memory as possible. Take a look at Cache Hit Ratio & DBCC PINTABLE in Books On Line.
-
For SQL Server, just use DBCC PinTable. I'm assuming that this is a demo project, not a full fledged production process. SQL Server will still write changes to disk, but everything that is read is only read once from disk. You can fill the cache with a full table scan query, then do your testing knowing that all data is coming from memory.
From BOL: DBCC PINTABLE is best used to keep small, frequently referenced tables in memory. The pages for the small table are read into memory one time, then all future references to their data do not require a disk read.
Student of SQL and Golf, Master of Neither -
yes Grasshopper i want to build an in memory database for SQL server as i want to find new way to boost performance as it is my research subject, pintable is not good as i know.
sir addict i also view the TSQLMemTable but this is not the issue that i want i want to know how those imdbs design such issue i want to know the structure of this the methods of doing imdb how can i organize the memory and reserve it and how can i load my tables into memory and then how can i monitor my the news that i made is affected the database.
any help???
thanks
Hoodi -
h!!!,
Keeping tempdb in memory was an option of SQL server 7.0 which was eliminated in SQL server 2000.In production environment if you pin tables in cache also there won't be much improvement in performance ,I feel some new feature should be added in SQL server future editions .
regards,
Vinod (DBA)
HTC Global Services
-
yes Rookie this what i want to add a new feature and it a new challenge to me , but i don't know how
Hoodi -
I may be being simple:
MSSQL will cache data (as mentioned).
This means your database will be run out of memory if all the data can be cached.
If you write to the database, changes will be written to disk - they have to be (write ahead logging)
(I don't know how using a RAMDisk would affect WAL - but RAMDisk is not stable media)
See: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
If you chuck as much memory as you can, use 64 bit, Widnows 2003, SQL 2005 etc you could effectively acheive in memory database. You cannot eliminate writes unless you have 100% read only database and only SELECTs are issued... (and probably not then - I've never considered the idea)...
-
Hoodi,
Mayby you can look at http://www.oracle.com/technology/products/timesten/index.html
You can never make SQL Server a real in-memory-database. When SQL Server makes an execution plan it takes into account that most data will be on a disk. If all data is in memory, the cost of obtaining data is much lower an would lead to a different execution plan.
-
yes Veteran, my idea is making this application for read only databases i.e smallprint dbs.
Rookie, i already download the timesten and my idea if Oracle make an in-memory databse by applying the timesten technology why SQL Server doesn't support such issue and for this i am trying to make something like timesten but i don't know how can i do such work.
Hoodi -
I only wanted to say: if you want to make an in-memory-database of SQL Server you have to rebuild the engine. The optimizer has to be changed. A good optimizer is the key to succes. So, I don't think database-vendors will give much information about how they do it. The competition is listening to.
-
dumb question from someone less experienced....if you have a small database, say 300 meg...your server most likely has multiple gigs of ram...couldn't you pin every table and achieve a performance boost for this? is a pinned table taken into consideration in the execution plan?
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! -
I think in practice this is what SQL does through the Access Methods Manager and the Buffer Manager. Check the Cache Hit Ratio and you should see most (90 % and up) read requests serviced from memory.
-
To my knowlegde (mayby other people have more info) SQL Server does not take into consideration where the data is coming from (disk or memory). The execution plan is always based on data coming from disk. For example. Retrieving a lot of data an sorting it by a field that's also an index (not clustered). Mayby, SQL Server would retrieve it first through the clustered index and sort it in memory. An in-memory database would retrieve it through the index which sorts is wright away. Not worrying about jumping from one page to another.
Taking it in considiration isn't easy because execution plans are cached and can still be in use hours later. Is the data still in memory????
Of course, a lot of GB's boosts performance. There is a gain when the data is in the buffer and doesn't come from disk. But keep in mind, a real in-memory database does not need a buffer manager.
-
Actually all data used in SQL comes from memory, the only sticking point is whether it is already in memory or whether it needs to be brought into memory from the disk in order to fulfill the query.
Some might say i'm being pedantic, and they'd probably be right
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply