January 14, 2009 at 11:24 am
Is there anyway to keep complete SQL Server 2005 user database in Server's Physical memory? suppose i have 2 GB database and on same box I have 8 GB physical RAM, can I keep whole of the user database in memory or its tables for fast access to clients?
Shamshad Ali.
January 14, 2009 at 11:29 am
You could, but there's no real reason for it. SQL Server will cache the data and such for you pretty efficiently.
If you really need to do that, use DBCC PINTABLE (details are in Books Online). I recommend against it, but it is possible.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 1:42 pm
SQL will cache frequently used data. If the database is often used, the data will be in memory.
Pintable is still usable on SQL 2005, but it does nothing.
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
January 14, 2009 at 1:45 pm
There are a lot of things that you can do to speed up the apps before thinking of keeping the entire database in memory.
It should be obvious from the above posts that you don't need such thing setup(SQL Server will do it for you).
* Noel
January 16, 2009 at 11:59 am
although technically with 8gb of ram you might expect a 2gb db to be in buffer cache you might actually be surprised how buffer cache gets used by sql server. I'd probably say your chnaces of always having it in cache are pretty low; however as pointed out the whole way in which sql server works is to cache frequently used pages. There's an rdl you can use as a custom report in ssms which will show how buffer cache is allocated http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 22, 2009 at 1:13 am
Looking closer at the explanation on http://msdn.microsoft.com/en-us/library/ms178015.aspx you'll find:
This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.
So for SQL Server 2005 this won't work 🙁
Any other solutions? I think I do need some kind of pining solution because of the following scenario:
I have an application that loads all data from the DB to memory for really fast processing. It sends quite a lot of updates and inserts on some objects, while most objects are barely updated. So far so good...
When the application needs to restart things go bad. It takes about 30 minutes to load up all the data because the SQL Server has to load up the data from disk; while, restarting the application 2 times in a row, the 2nd time (with sql server having all data in memory) only takes 4 minutes!
All ideas are welcome, but keep in mind that I cannot use global or temp tables!
January 22, 2009 at 3:52 am
serge.kovacs (1/22/2009)
When the application needs to restart things go bad. It takes about 30 minutes to load up all the data because the SQL Server has to load up the data from disk; while, restarting the application 2 times in a row, the 2nd time (with sql server having all data in memory) only takes 4 minutes!
Are you restarting the app, or SQL server? SQL will keep frequently used pages in memory until either the service is restarted or it needs the space for other pages.
Are you sure it's because SQL's loading the data from disk? Have you checked to see if the queries are reading from memory or disk? Have you checked disk stats to see if you have an IO bottleneck? Have you checked that the queries are written optimally and the indexes support the workload? Have you profiled to find where the slowdown comes from?
but keep in mind that I cannot use global or temp tables!
Temp tables have the same rules as to memory or disk as any other table.
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
January 22, 2009 at 5:13 am
I'm restarting the app, not the SQL Server and I found that it is due to disk I/O from SQL Server. Unfortunately the SQL server is under control of a client 🙁 Even worse, it is located on a VM, so I expect I/O perfermance to be pretty bad.
The thing is, there is only 2GB RAM on the machine (64bit, single cpu), while the DB itself is taking almost 2 GB now, and is still growing. In table terms, out of the 300+ tables, there are less then half that are frequently used and at least 100 that won't be used for several days, or even weeks; except of course for the loading that happens when the application is started.
Do you think it could help to increase the amount of RAM allocated to the VM? I'm not so sure that this would help since I suspect SQL server unloading these in-active records anyway...
January 22, 2009 at 5:37 am
serge.kovacs (1/22/2009)
while the DB itself is taking almost 2 GB now, and is still growing.
That's tiny. 🙂
Do you think it could help to increase the amount of RAM allocated to the VM? I'm not so sure that this would help since I suspect SQL server unloading these in-active records anyway...
It probably will. Also, look at optimising the queries that your app is using. If they're inefficient, they may well be needing more IOs than they should. Then look at indexing. Same thing there. Poor indexing means queries doing more IOs than they should.
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
January 22, 2009 at 5:50 am
2 Gb is indeed tiny, but this only the operational data (history now is 3 GB and will continue growing 🙂 )
Indexing shouldn't be a problem. Every table has a primary key and is only accessed via this key (except of course for the initial load...)
I'll see if we can get them to increase the memory.
Thnx!
January 26, 2009 at 5:04 pm
serge.kovacs (1/22/2009)
Indexing shouldn't be a problem. Every table has a primary key and is only accessed via this key (except of course for the initial load...)
It sounds like you've just described your issue...
If the tables only have one index (the PK) and this is not being used when the app starts up, then you are doing table scans across your 2GB of data when the app starts.
You could try designing indexes for startup as well as the PK indexes...
January 26, 2009 at 10:13 pm
Maybe I wasn't clear on the inner workings of the application on start-up. It will load all data from all tables so there should be no need to use indexes. Every table is completely loaded into the memory of the application (simply everything, all columns, all rows) and the application itself will maintain the references between the data, only sending inserts, updates and deletes to the DB, never reading from the DB after startup.
I don't see how adding indexes will help me here.
Also, a small test that according to me shows that it is not due to indexes.
1/ start DBMS & start Application -> loading of data in application takes 25-35 min
2/ after a days work, restart application -> loadind of data takes around 30 min again
3/ just after restarting application, restart it again (data should now be in cache of DBMS) -> loading of data takes about 5 min
I know that there might be other factors that come into play, but so far I haven't been able to identify a single one. If anyone knows a good one to investigate, please let me know because the customer is still not happy with the 'give it more resources'-solution
January 26, 2009 at 10:25 pm
serge.kovacs (1/26/2009)
Maybe I wasn't clear on the inner workings of the application on start-up. It will load all data from all tables so there should be no need to use indexes. Every table is completely loaded into the memory of the application (simply everything, all columns, all rows) and the application itself will maintain the references between the data, only sending inserts, updates and deletes to the DB, never reading from the DB after startup.I don't see how adding indexes will help me here.
...
I know that there might be other factors that come into play, but so far I haven't been able to identify a single one. If anyone knows a good one to investigate, please let me know because the customer is still not happy with the 'give it more resources'-solution
In that case, the core issue is the design of the application.
If that's non-negotiable, then adding better disks will help your SQL Server deal with having an entire 2GB database read when the app restarts.
Adding more memory is unlikely to help, because the intervals between reads are so long that the database will be flushed from the cache.
January 26, 2009 at 10:58 pm
OK, I guess there really isn't anyway around this one (except better HW).
Thnx for all the people helping me to understand this 🙂
January 26, 2009 at 11:57 pm
serge.kovacs (1/26/2009)
Maybe I wasn't clear on the inner workings of the application on start-up. It will load all data from all tables so there should be no need to use indexes. Every table is completely loaded into the memory of the application (simply everything, all columns, all rows)
Why?
If that's what you're doing, you may as well be using flatfiles to store the data, you're not taking any advantage of the power of a relational database
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
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply