|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
CPU-bound or I/O bound?I/O bound? How about in SQL 2005? Do I run a trace and process that? What do I look for? I'm just trying to decide if I should upgrade the motherboard and CPU of my SQL computer, which is a development computer. I would be leaving the disk subsystem alone. (It's a SQL Server 2000 instance that does all the work on that box, which has a single AMD 3000+ CPU, with one internal SATA 300 GB/sec disk for the data files and a SATA 300 GB/sec disk for the log files.) I already have an AMD 64 5200+ dual-core CPU and motherboard that I could put in either that box, or in my "main" day-to-day computer, which also runs on a single-core AMD 3000+ CPU. There's 2 GB of memory everywhere. Thanks. David Walker It is fairly easy to get a good idea if the system is CPU bound. Run
Performance Monitor and graph total CPU. Or you can even use Task Manager, though PM is more flexible. Watch the graph generated when the system is busy with the sort of load that concerns you. Is the CPU up close to 100%? If it is, the system is CPU bound. Likewise, if you have a slow-running load on the system but the CPU doesn't show up near 100% then it is I/O bound. A few warnings here. First, if the processor is hyperthreaded, and hyperthreading is turned on, 50% will be roughly the same as 100%. Second, on a multi-processor system there will be plateaus for each processor. With two processors a single query might hold the total CPU around 50%, which means 100% of one processor. With a quad processor system the plateaus would be around 25%, 50% and 75%. That single task is CPU bound, but probably would require a faster CPU rather than more of them. Third, I find it much more informative to track TOTAL CPU rather than individual CPUs. Tracking each CPU the work constantly shifts from one to another and makes it hard to see the total load. Monitoring total CPU makes it much clearer, and lets you watch for the plateaus. Roy Harvey Beacon Falls, CT On Sat, 15 Sep 2007 13:34:12 -0700, DWalker <n***@none.com> wrote: Show quote >How do I find out if a given workload in SQL Server 2000 is CPU-Bound or >I/O bound? > >How about in SQL 2005? > >Do I run a trace and process that? What do I look for? > >I'm just trying to decide if I should upgrade the motherboard and CPU of my >SQL computer, which is a development computer. I would be leaving the disk >subsystem alone. (It's a SQL Server 2000 instance that does all the work >on that box, which has a single AMD 3000+ CPU, with one internal SATA 300 >GB/sec disk for the data files and a SATA 300 GB/sec disk for the log >files.) > >I already have an AMD 64 5200+ dual-core CPU and motherboard that I could >put in either that box, or in my "main" day-to-day computer, which also >runs on a single-core AMD 3000+ CPU. There's 2 GB of memory everywhere. > >Thanks. > >David Walker Roy Harvey <roy_har***@snet.net> wrote in
Show quote news:1vgoe3l46q3ok27e7jpofcfv1togjor1tt@4ax.com: That's probably good enough for my purposes (either Perfmon or task > It is fairly easy to get a good idea if the system is CPU bound. Run > Performance Monitor and graph total CPU. Or you can even use Task > Manager, though PM is more flexible. Watch the graph generated when > the system is busy with the sort of load that concerns you. Is the > CPU up close to 100%? If it is, the system is CPU bound. > > Likewise, if you have a slow-running load on the system but the CPU > doesn't show up near 100% then it is I/O bound. > > A few warnings here. First, if the processor is hyperthreaded, and > hyperthreading is turned on, 50% will be roughly the same as 100%. > Second, on a multi-processor system there will be plateaus for each > processor. With two processors a single query might hold the total > CPU around 50%, which means 100% of one processor. With a quad > processor system the plateaus would be around 25%, 50% and 75%. That > single task is CPU bound, but probably would require a faster CPU > rather than more of them. Third, I find it much more informative to > track TOTAL CPU rather than individual CPUs. Tracking each CPU the > work constantly shifts from one to another and makes it hard to see > the total load. Monitoring total CPU makes it much clearer, and lets > you watch for the plateaus. > > Roy Harvey > Beacon Falls, CT > manager. My current CPU is not hyperthread-capable, and it's a single CPU. So I ought to be able to tell pretty easily. The new dual-core AMD x64 CPU that I plan to put in has, of course, two cores... Thanks. David Hi David,
Additionaly I would like to add an article here for your reference: Troubleshooting Performance Problems in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx This article is specific to SQL Server 2005, however for CPU and I/O bounds, I think that they are same. You can use System Monitor (Perfmon.exe) to monitor your SQL Server 2000 or SQL Server 2005 instance. If you have any other questions or concerns, please feel free to let us know. Have a good day! Best regards, Charles Wang Microsoft Online Community Support ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== > Likewise, if you have a slow-running load on the system but the CPU Roy, I saw this a few days ago, and shrugged it off, but now came across it > doesn't show up near 100% then it is I/O bound. again, and I'm having trouble agreeing with this "process of elimination"... I don't think it's fair to say "it must be I/O if it's not CPU"... it could also be network, depending on where the query is running. Now, I realize there is a lot more context here, and obviously the best approach has already been identified: determine the culprit proactively, not by weeding out the first usual suspect and assuming there are only two suspects. :-) On Tue, 18 Sep 2007 18:42:07 -0400, "Aaron Bertrand"
<ten.xoc@dnartreb.noraa> wrote: >> Likewise, if you have a slow-running load on the system but the CPU You are right, I chose to over-simply a bit. I confess I haven't yet>> doesn't show up near 100% then it is I/O bound. > >Roy, I saw this a few days ago, and shrugged it off, but now came across it >again, and I'm having trouble agreeing with this "process of elimination"... >I don't think it's fair to say "it must be I/O if it's not CPU"... it could >also be network, depending on where the query is running. Now, I realize >there is a lot more context here, and obviously the best approach has >already been identified: determine the culprit proactively, not by weeding >out the first usual suspect and assuming there are only two suspects. :-) found a network-bound SQL Server yet, but it is certainly possible. When I had day-to-day responsibility for servers I kept Perfmon running on an old box in my office, one instance for each server. I tracked CPU, paging, number of user connections, usual stuff. They were all set to give a 25 minute window into the past. When the phone rang I looked at that monitor to see if anything looked strange before I picked it up. Roy Harvey Beacon Falls, CT  |
|||||||||||||||||||||||
Other interesting topics