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 |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2009-05-12 : 12:07:27
|
| Running a DSS workload pulling millions of rows from one or two tables which are consolidated, rolled up, and summarized. This is taken place on a 16 CPU server, however only 1 CPU is being used and being maxed. No settings have been changed and Max Degree of Parallelism = 8. What events may cause the SQL query optimiser to use a single cpu and not split the load. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-12 : 13:25:33
|
| Only things I can think of is your max worker threads setting and if you're using fiber mode. Some conditions are listed here that might affect you:http://msdn.microsoft.com/en-us/library/aa175385.aspxhttp://blogs.msdn.com/khen1234/archive/2005/11/07/489778.aspxAre you using any extended procedures or sp_OA procedure calls? Any COM or ActiveX objects? |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-12 : 13:35:28
|
There is no golden rule that SQL Server must use parallellism.It depends on the plan, if the cost of invoking parallellism is worth the effort.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-12 : 13:37:02
|
What about the affinity mask?http://msdn.microsoft.com/en-us/library/ms188603(SQL.90).aspx
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2009-05-12 : 13:57:47
|
| Conditions such as affinity mask, a single CPU or parallelism are not evident with this issue.The workload completion time change was sudden, nothing else has been effected and has remained slow since it begun. Could the execution plan had become inefficient forcing the workload to run on a single CPU? I have purged the procedure cache which has not improved anything. I will try changing the parallelism level, MAXDOP and even a reboot should this kick start parallel processing. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-12 : 14:11:32
|
| How are you running this dataload? SSIS? DTS? Linked servers? Local queries? bcp/BULK INSERT? If you're using staging tables, are they stored on the same filegroup as the source data?Can you change the max degree to a lower number, like 5 or 6, and see if that changes anything? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-05-12 : 16:17:24
|
| i'd guess you have a locking/blocking problem.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
|
|
|
1 CPU out of 16 is being maxed - why?