As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Multiple CPUs - SQL2005 - Windows 2003 - VB.net
Posted on 2007-03-30
We are developing a VB.net application against the SQL2005 / Windows 2003 server (4 CPUs), when we run the application which has heavily sql queries, the CPU performance of the SQL server only see one CPU at a time being pegged at 100%, while others are pretty much quiet, but the 100% peak takes turns from one CPU to the other. My question is that:
1) how is it possible to have the CPU usage be evenly spread between the 4 CPUs?
2) why I see 8 CPU instants in Windows Performance task manager other than 4?
3) Is the OS, SQL or App. itself controlling the CPU ultilization?
Thanks,
fshguo.
1) how is it possible to have the CPU usage be evenly spread between the 4 CPUs?
2) why I see 8 CPU instants in Windows Performance task manager other than 4?
3) Is the OS, SQL or App. itself controlling the CPU ultilization?
Thanks,
fshguo.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
6 Comments
Hi,
You can tell SQL Server how many CPU's to use so it could be just set to one.
You have to buy a licence per processor though under certain licence agreements.
If you right click on the SQL Server in Microsoft SQL Server Management Server and choose properties you can then select processors and see how many are being used
Darren
You can tell SQL Server how many CPU's to use so it could be just set to one.
You have to buy a licence per processor though under certain licence agreements.
If you right click on the SQL Server in Microsoft SQL Server Management Server and choose properties you can then select processors and see how many are being used
Darren
After checking what DarrenD describes...
If you are running a single, single threaded app against Sql Server most activity will take place on a single processor. In isolated cases, sql server might optimize a query by spreading it across more than one processor but that would be the exception rather than the general rule. If your app is threaded or multiple instances run, they can be submitting multiple concurrent queries which will then cause more than one cpu to be used.
You'll see 8 if you have 4 processors that either are dual core or support hyperthreading.
If you are running a single, single threaded app against Sql Server most activity will take place on a single processor. In isolated cases, sql server might optimize a query by spreading it across more than one processor but that would be the exception rather than the general rule. If your app is threaded or multiple instances run, they can be submitting multiple concurrent queries which will then cause more than one cpu to be used.
You'll see 8 if you have 4 processors that either are dual core or support hyperthreading.
I checked the SQL server can see 8 processors. and yes, it is dual core.
Now my question to Mastoo is that how can we make the change so that the App is multiple threaded?
Now my question to Mastoo is that how can we make the change so that the App is multiple threaded?
![[Live Webinar] The Cloud Skills Gap](https://filedb.experts-exchange.com/files/public/2017/5/29/3249c5dc-214e-4f8f-afab-ef354630e19f.png)
Multi-threaded programming is complicated and I can't do the subject justice here. Can I suggest a couple other things before you go that route? They all have potential for boosting performance enough that you might not feel the need to thread your app.
#1. If you're going to have several people running your app at the same time, in effect this is the same as threading since it means your database server will be running several queries at the same time. In this case, threading would help a single person but when several people run the performance would be unchanged.
#2. Use a code profiler on your app to be sure it spends most of its time waiting on the database. If it only spends 20% of its time waiting on the db, and you thread it to cut that in half, you've only boosted your performance by 10%.
#3. If your app is waiting on the db for most of the time, use sql profiler to identify the queries that take the longest and then optimize those queries. A simple index change can have a dramatic effect on performance.
#4. If none of those help, then look at the SqlCommand methods like BeginExecuteReader and BeginExecuteNonQuery. Those are the easiest ways to thread things when db performance is the bottleneck. They limit the scope of the threading. They make coding a little more difficult because code needs to start several queries (loading up multiple cpus) and then react when they queries finish. If you're using databound controls, threading isn't an option.
#1. If you're going to have several people running your app at the same time, in effect this is the same as threading since it means your database server will be running several queries at the same time. In this case, threading would help a single person but when several people run the performance would be unchanged.
#2. Use a code profiler on your app to be sure it spends most of its time waiting on the database. If it only spends 20% of its time waiting on the db, and you thread it to cut that in half, you've only boosted your performance by 10%.
#3. If your app is waiting on the db for most of the time, use sql profiler to identify the queries that take the longest and then optimize those queries. A simple index change can have a dramatic effect on performance.
#4. If none of those help, then look at the SqlCommand methods like BeginExecuteReader and BeginExecuteNonQuery. Those are the easiest ways to thread things when db performance is the bottleneck. They limit the scope of the threading. They make coding a little more difficult because code needs to start several queries (loading up multiple cpus) and then react when they queries finish. If you're using databound controls, threading isn't an option.
Thanks Wastoo, how code profiler knows how long it is spending waiting for database? simply a URL link will be helpful.
I use ants:
http://www.red-gate.com/products/ants_profiler/index.htm
Basically you run your program under the profiler. When done, it will show you what percent of the total time was spent in what functions. This lets you quickly identify where your code spends most of its time so you can focus on fixing the right chunk of code. For example I might see that a button click takes 5 seconds, and of that time 25% is in a couple of database calls and the remaining time is spent populating a listview control. In your case, if it leads to a couple stored procedure calls you'd know which procs you should try to optimize.
They let you download an eval copy for a trial period so you can give it a shot.
http://www.red-gate.com/products/ants_profiler/index.htm
Basically you run your program under the profiler. When done, it will show you what percent of the total time was spent in what functions. This lets you quickly identify where your code spends most of its time so you can focus on fixing the right chunk of code. For example I might see that a button click takes 5 seconds, and of that time 25% is in a couple of database calls and the remaining time is spent populating a listview control. In your case, if it leads to a couple stored procedure calls you'd know which procs you should try to optimize.
They let you download an eval copy for a trial period so you can give it a shot.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity)
(CODE)
There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource. In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database.
Open Services Panel:
Create a new connection using New Connection Wizard:
Create a test database called eetutorial:
Create a new test tabel called ee…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 23 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.