June 20, 2008 at 10:12 am
Hallo,
first i have to say sorry fpr my bad english.
I have a big problem and hope someone can help me. We habe SQL Server 2005 Standard running on a dual soket machine with two quad core cpu's.
The problem is that if we use our application or the reporting services i can see on the permon the system ist working only in one cpu core. The storage is not the problem
i can see my harddisk and the ram ist not working strong but only the one cpu core ist working on 100% the other 7 cores do nothing.
The database habe 108 tables and the bigest table habe 20.000 rows. The report we generate need 15 minuts to be finisht.
Its posible that my sql preferences are wrong or its only the aplikation who make this problem? We thing to redisign the reports with linq and hope we can
cause the other 7 cores to work.
Thenk you for you help and best regard
Chris
June 20, 2008 at 10:53 am
There are a few things to check.
First, not sure which version of Windows you have, but in some versions there is a different kernel for 1 CPU or multiples. I'd check and be sure that you have the right Windows kernel and a CPU wasn't added without changing that.
The other thing is to check SQL Server 2005 affinity in SSMS , server properties, advanced configuration). you can set SQL to run on only certain CPUs.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 20, 2008 at 11:07 am
Also make sure you are in fact running SQL Server Standard and not SQL Server Express (which would limit you to a single processor).
If your application uses a single connection and MAXDOP is set to 1 - you would see this behavior also. But, that is not likely 😉
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 11:18 am
Let as look at the micrososft reporting services problem.
I have a windows server 2003 Standard R2 with Sql server 2005 standard on it. I see in the task manager all 8 cores.
I set in the sql server preferences that multicore is posiblek to use.
In the Microsoft reporting services i only can configure one connection for one report so the report is running on this database connection.
Can i change somewhre the seting that one user can only use one core? if i connect with a ather user an run somthing else i see theat the sql server user 2 cores but only one core for one user. i like to powerup my report and i have the right hardware but the report use only one core 🙁
What can i check - what perferences can be wrong? or you thing the stored procedure is not able to run at more cores an we have to redisign the stored procedure?
Thank you very mutch for you help
June 20, 2008 at 11:26 am
A single procedure will only use multiple processors when SQL Server believes a parallel plan will produce better results.
What did you set/change to tell SQL Server that multiple cores are available? There should be no reason to modify any setting for this.
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2008 at 11:35 am
On the SQL propetis i all cpu are selected. I have set the sql server high priority checkbox. I have set teh value 0 to limitation of work threds so there no limitation.
Max paralell work i have set to 8 and then i have set the cost for paralell work to 5.
That all i have change. In the standard configuration i have the same problem. I have test it on difrent machine and difrent sql server version, but i can't do it that the sql server run the report on more then one core. The other application querys have the sam problem.. one user can only use one core
thank you for you help
June 20, 2008 at 12:02 pm
info (6/20/2008)
On the SQL propetis i all cpu are selected. I have set the sql server high priority checkbox. I have set teh value 0 to limitation of work threds so there no limitation.Max paralell work i have set to 8 and then i have set the cost for paralell work to 5.
That all i have change. In the standard configuration i have the same problem. I have test it on difrent machine and difrent sql server version, but i can't do it that the sql server run the report on more then one core. The other application querys have the sam problem.. one user can only use one core
thank you for you help
You don't need to set the SQL Server high priority checkbox (I am assuming you meant Boost SQL Server Priority). Again, if the query does not require a parallel plan - SQL Server will not use more than a single CPU for that query.
I'm sorry - but I am not sure what the problem is. Is this a problem with a single report only using a single processor, or the fact that you are never seeing any activity on the other CPU's?
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 23, 2008 at 10:13 am
The problem ist that the report need now with only 20.000 records (testdate) 15 minuts to be finished. I think i have 500.000 new recordsin 12 months and so in the live system the report will by slower. I wont to optimise all i can to get the best performance for this report becouce its i need this report in the live system a few times per day.
So i look on my hardware to speed up the report an i have see that my drives and memory are not the problem. Then i see that one of the processor cores is working 15 minuts on 100% so i think i can get better performance if the report can use more cores then just one. But im not a developer so i dont now if the problem is the report and i have to redisign the report to use more cores or the problem are the sql server settings.
i can't belive that this is the real and end peformance of my report with so small data. I search also for a sql specialist how can look an the report and the stored procedure.
If you know a good company pleas tell my the contakt data.
Thank you very mutch
Regards
Chris
June 23, 2008 at 10:29 am
posting what your proc does and some sample tables (and indexes on those tables) could help us help you 😉
* Noel
June 23, 2008 at 11:08 am
My guess is that you really need to look at tuning the query. Without seeing the query, sample data, etc... I could not begin to guess at how to optimize this.
With that said, if you post your query using the information at: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Somebody here can probably help.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply