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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2003-12-09 : 19:25:09
|
| Currently I have a setup as such. 1 webserver, 1 db server. Both are setup as such: intel 2.4 xeon processor, 1gig ram, and SCSI raid 1 arrays.The webserver runs great, the database server slows down at times because of my slow search stored proc. I have about 100,000 rows that are searched against, with up to 20 columns that are used as search criteria. Unfortunately it is a dynamic SQL stored proc, it's the only one I use in my application, but I couldnt find ways around it for this case.We only have 1 processor license, so currently we can't add a second processor due to costs of the license and the server upgrade required. I do have access to another sql server that I could share off however. This SQL server is located across the country, but I could setup a VPN.I am trying to figure out if my idea is completely crazy or not. What I am thinking is, I could replicated just this one table to the other sqlserver nightly. I could divert all searches to be performed just on this second sql box.Obviously it is not as good as getting a stronger box, but is this a feasible solution? Any majar flaws with this idea? I have never attempted something like this.Thanks for any tips / suggestions.Mike123 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-09 : 19:32:14
|
| Is hyperthreading turned on in the BIOS on the database server? That's if this is a newer server that has hyperthreading. I believe it is available in a server with a 2.4 xeon CPU. With hyperthreading, you'll see two times the amount of CPUs in Windows. As I understand it, you'll see 25% gain for each CPU that you have. Windows will show 2 CPUs in your scenario (although you only have to license one because physically you only have one), but you'll be getting performance as if you had 1.25 CPUs.Tara |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2003-12-09 : 19:33:17
|
| 100,000 rows is really pretty small. Why don't you post the query, the ddl, and what you're trying to accomplish, and see if we can come up with an optimization. You probably shouldn't need to throw more hardware at it, especially if just the one query is causing your slowdown. What's your processor, cache utilization, etc. at when this query isn't running?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-12-09 : 21:25:49
|
| Mike, Sounds like an interesting idea, but what happens if your VPN goes down? I think I'm with Derrick on this one. Post some DDL and DML and we'll see what we can figure out. You should be able to nearly Table Scan 100,000 and it would still be fast :)Does your query cause a lot of reads? Use "SET STATISTICS IO ON" in QA to find out. I suspect a bottleneck is either RAM or more probably your RAID 1 (mirrored) Array. Can you tell us the size of the database and the type / speed of the Disk subsytem?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
Is this a decent solution?