Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GeneralSQL
Starting Member
10 Posts |
 Posted - 2006-01-19 : 17:37:11
|
| I have been asked by my management team about the possibility of using a RAM Drive to put a database in to attain faster lookups on SQL2K. The tables are static and are only used for read only lookups for validation. The stored procedures for validation are very complex that will not work in MySQL and definitely cannot be converted to a query in Access. Has anyone here used a RAM drive for a solution? If so, I would love to hear about how it went, good or bad. Any thoughts? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-19 : 17:52:41
|
| Have you actually identified a problem with disk IO, or is this just specualtion?It is much more likely that you can improve performance by adding memory to SQL Server. All queries run against data in the SQL Server data cache, so if you can add emough memory to hold all the relavant data in cache, disk IO and the RAM disk would be unimportant.With the Standard version of SQL Server, you can allocate up to 2 GB to memory. With the Enterprise Edition, you can have much more.Another thing to look into would be to see if you can redesign the tables, indexes, and queries that you are using for validation to improve performance. It is not unusual to improve performance by several orders of magnitude this way.CODO ERGO SUM |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-01-19 : 18:51:55
|
| I agree with what has been said above. The first step of problem solving is identifying the problem.1. How many Read's does this lookup cause? 2. Are you having Disk Queueing issues?3. Is your database larger than the amount of RAM you have in the server?So, first I'd determine if you are Disk Queueing, then see if you need to or can add more RAM. After you've done all the indexing changes that need to be made, and any hardware upgrades that need to be made, then we can look at another option. That option IS DBCC PINTABLE. This is one of those things that I generally do not reccomend, but it may solve your problem without resulting in a dedicated RAM disk or a Solid State drive (REALLLLLYYYYYY Expensive! 6 figures for 70GB's last I looked). I do not reccomend a RAM drive at all. You will run into all sorts of weird problems to solve like when to start teh SQL server serivice after the machine reboots etc. SQL server is very smart about memory management. If anything, I'd do some perfmoning to answer some of the questions above. If you can't figure out how to answer the questions, ask more questions here or just slam more RAM into the server. It can't hurt :)Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-01-20 : 04:05:57
|
| The quality of your code/database design would also have a large bearing on the problem/solution. It would help if you could give us a non-confidential sample of problematic code, matching sample execution plan and a benchmark of the equipment/execution time with target execution times as well.....then maybe we can help you save $$$$ by spending cents. |
|
|
|
GeneralSQL
Starting Member
10 Posts |
Posted - 2006-01-20 : 10:14:45
|
| We are running SQL2K standard and the memory allocation is hitting the 2GB point. Do you think going to Enterprise could help? We have 4GB RAM on the server and there is always about 2GB free. I am not sure which query/SP is causing the problems because none of them are taking too long of a time to run (SQL performance monitor), but the processor is maxed out. Dual Xeon 1.2GHz on a Dell PowerEdge 2500 with 4GB RAM. I will have to check on how many reads there are. We have roughly 20 machines with 1 - 5 processes that hit this DB for lookup. Disk I/O is speculation on the management side, I don't think it is the disk, but the connections into the box. They mainly wanted me to look into that as a solution, so I am humoring them with finding out if anyone has done this. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-20 : 10:30:52
|
| It sounds like you don't really have a handle on exactly what is causing the problem.Since your CPU is maxed out, it sounds unlikely that IO is the performance bottleneck. Usually, IO problems will starve SQL Server for data, and it will be unable to maintain that level of CPU usage as it waits for disk IOs to complete.My first guess would be that the table, indexes (or lack of them), or queries are the problem.You should start reading the articles on this site to develop a more systematic approach to solving performance problems:http://www.sql-server-performance.com/CODO ERGO SUM |
|
|
|
GeneralSQL
Starting Member
10 Posts |
Posted - 2006-01-20 : 10:53:53
|
| Thank you for your help. I will review the articles to see if they may help. I will use this information to persuade the management team against the RAM drive and to figure out what is actually happening instead of putting more and more band-aids onto the problem. Again, thanks. |
|
|
|
|
|
|
|
|
RAM Drive