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 |
|
jparker
Posting Yak Master
118 Posts |
 Posted - 2006-04-21 : 06:04:39
|
| I need some help making a decision on whether to spend money on getting a second processor for my server to speed up our internal web based system.The system was inherited and is fundamentally very poorly written. There is no use of indexing or views and we have some tables that have over 300,000 records and on some reasonably complex transactions pages timeout even though the timeout is set to 120 seconds in IIS.We plan to rewrite the system but to buy us some time we were thinking of putting the extra processor in to speed things up. While a processor will help will it help that much? For example if I'm getting page timeouts then this is just as much a problem in IIS as it is sql server so is it a case of allocating resource more efficiently.May I have your opinion on this please |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-21 : 06:36:53
|
| My batting order would be:1) Stick some indexes on the tables2) Move the database to a standalone machine (i.e. NOT shared with IIS etc.)3) Throw lots or memory, disks, CPUs at the problemFor (1) you can use the SQL monitoring tools to see queries that are taking a long time, and experiment running those specific queries with/without indexes to see what the gain is. Obviously frequently re-occurring queries are worth hitting first.Without doing (1) you run the risk that your system is disk-bound, dragging all 300,000 rows off disk into memory and running out of cache such that more CPUs won't help at all. Checking if you are 100% CPU bound or 100% disk bound might tell you the answer to that one, but whilst that's a good indicator it probably won't make you certain that one or the other is the problem."even though the timeout is set to 120 seconds in IIS"If this is a public facing site [and probably even if it isn't] then the likelihood is that people will press retry. And then IIS will re-request the same data, but will NOT terminate the first query**, so now you have two queries consuming resources** technically it can terminate the first process, but as you say your application is poorly written it seems unlikely that that ability was built in!Kristen |
 |
|
|
jparker
Posting Yak Master
118 Posts |
Posted - 2006-04-21 : 10:59:40
|
| Thanks very much Kristen for your opinion.when you talk about the sql monitoring tools which ones are you talking about specifically? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-04-21 : 11:59:34
|
| Simplest of all...SQL Profiler which comes with the base product.Posting some sample 'bad queries/execution plans' here may get you going down in the right direction....a little education being the key to a big new world, etc.... |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-04-21 : 12:31:33
|
| Here's another good read on SQL Server performance monitoring.It talks about a little of everything.http://www.sql-server-performance.com/sql_server_performance_audit.aspI think you'll want to start with SQL Profiler and identify "slow" queries and put some indexes on tables to make them faster. Once you've got the database performing as well as it can, hopefully this will be all you need. If performance is still unacceptable, then you'll want to fire up Performance Monitor (perfmon in windows) and monitor CPU, RAM, and Disks (mainly disks). This will tell you where your bottleneck is and where you should spend your money. SQL Server generally needs RAM, DISK, CPU in that order. Since this machine is also a WebServer which tend to need RAM, CPU, you can probably see which thing you need to do first (Add RAM!!). Ideally, you need to split your SQL Server and Web Server into two separate machines. Right now you are trying to make one machine have fast EVERYTHING. You really need one machine with a Fast CPU and some RAM for WebServing, and another machine with tons of RAM, lots of fasts disks, and a CPU or two for your database server.HTH!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>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-21 : 14:38:58
|
| "about the sql monitoring tools"That's what I love about SQL Team ... don't bother to answer a questions and the other guys will do it for you!Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-21 : 15:14:16
|
quote:
There is no use of indexing or views
Probably 90% of performance problems are resolved with the proper indexes. If they wrote poor code, they likely designed poor tables and established poor indexes for them. The simplest way to know is just run SQL Profiler and let the tool do an Index Analysis for you (you gotta love that.) If that runs and tells you that it can't improve anything with indexes I'd be drastically shocked. I support a very poorly written 3rd party application (millions of rows of data) and often have sped up stored procedures 10 to 20 times with a few proper indexes, because their out of the box code does nothing but table scans for most things. Hope it helps,Dalton |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-04-22 : 10:44:09
|
| There is an interesting side benefit on the cache by prober indexing, that I have never seen mentioned.Lots of table scans fills the cache with little (if ever) needed data, prober indexes will prohibt that, and there by give your an overall better performance, even on tables that is not involved in the these indexes. |
|
|
|
|
|
|
|
|
Is a dual processor worth it????