May 27, 2005 at 4:12 am
My programmer has asked me to figure out why his stored procedure runs faster (3min) on a 3GHz CPU laptop vs 12 minutes on a multiprocessor (1GHz x 8) server with 4GB of memory. When I run his stored procedure on the server, it only runs on one processor. The total CPU is at 13%. The server generally has low utilization.
Is there a way I can force the sp to run on more than one procesor ? max degree of parallelism and cost threshold of parallelism are set at default. I set affininty mask to 127. I am thinking my programmer will need to change his stored procedure to take advantage of more CPUs. What would he have to change to get it to run faster ?
Thanks, Aubrey
May 27, 2005 at 8:08 am
Have you made sure that the Developers environment is the same as the other environment? i.e:
#records same
Table schema are the same (indexes, etc...)
I don't know of anyway to FORCE the system to use more than 1x CPU. Have you also reviewed the code to ensure TABLE SCANs are not being done???
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 27, 2005 at 8:52 am
If you look at the processors tab of the server property have a look at the Parellism settings.
Does it have "use all available processors" checked?
What is the setting for "minimum query plan threshold for considering queries for parallel execution"?
The chances are that your server simply doesn't think that the developers query justifies using more than one processor and it isn't a processing issue but some other issue such as locking, io or similar.
May 27, 2005 at 9:53 am
Also try to update tables/indexes statistics to see whether SQL Server runs the SP using multiple processors.
I would try to find out which part of SP running slow and compare it to when you run it in your developer's machine.
Check the execution plans on both machines and see the difference.
May 31, 2005 at 3:57 am
Sorry for the delay.
AJ:
* The developer uses his DB on the MP server most of the time, and just copies the DB to his laptop when he has to operate it remotely. So, it should have the same schema.
* He does use TABLE SCANs in his DB. When is a good time to use/not use TABLE SCANs ? How does this affect performance ?
David:
* Use all available processors is checked. Seven CPUs are checked under the processor check box. Maximum worker threads are set to 255.
* the minimum query plan threshold for considering queries for parallel execution is set to 5, the same value as the cost thresthold for parallelism setting.
* if the issue could be locking or i/o, how can I check to see if that is the problem ?
Allen:
* Is there a way to compare execution plans besides doing it visually ? There are about 80 data points in the plan, and it makes it hard to check differences by just looking at the icons and percentages on both computers. Is there a way to output everything to a text format, and maybe run a compare against the two ?
* How do I update tables/indexes statistics on a DB ? Does it matter if the index is clustered or not ?
* There is one step that takes 95% of the time of the stored procedure. How can I tell what is happening during that time ? Is there a debug option that I can use to output to a file to get more granularity ?
The main issue is my developer says his sp should run faster on an MP system with 8 x 1GHz CPUs than one 3GHz CPU. Is this a correct assumption ?
Thanks, Aubrey
May 31, 2005 at 4:19 am
my 2ct
- also keep locking in mind !
- sp_updatestats + dbcc updateusage will rectify your statistics
- you can use sql-perfmon to find out what is consuming how man.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 31, 2005 at 5:23 am
>Is there a way to output everything to a text format, and maybe run a compare against the two ?
Try using SET SHOWPLAN_TEXT ON.
>How do I update tables/indexes statistics on a DB ?
Have a look at UPDATE STATISTICS in Books Online.
>There is one step that takes 95% of the time of the stored procedure
What is that step?
>The main issue is my developer says his sp should run faster on an MP system with 8 x 1GHz CPUs than one 3GHz CPU. Is this a correct assumption ?
No, not necessarily. Not all queries can benefit from multiple processors.
May 31, 2005 at 4:20 pm
Table scans means that your proc has to trawl through the physical table rather than use indices. There are situations where it Table Scans are unavoidable but in general you design your database to avoid them.
You can't really compare a single user installation of SQL with a multiple user installation.
As Chris says, not all queries can make the most of multiple CPUs.
Do a search for MAXDOP on this site and you will see that beyond a certain point additional CPUs will actually slow the query down.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply