September 17, 2008 at 7:50 am
Hello Guys,
This has been a very difficult situation for me as I cant find a solution for this.
We are using SQL Server 2005 enterprise edition on windows server 2003, both being 64 bit. This server is used only for the backend changes i.e it only has sql server 2005 installed. When we did batch processing as a part of the preload test, we observed that only one of the 16 processors was lot busy when compared to other 15 processors. Infact it was all the time around 93 % when compared to the other processors which were averaging 25 %.
Any help would be appreciated.
Thanks,
Nikhil parsi
September 17, 2008 at 9:42 am
What kind of 'batch processing'?
Sometimes SQL will parallel processes and sometimes it won't as there is an overhead in running stuff in parallel.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2008 at 10:21 am
Which processor? If it is the first processor that is because the standard configuration for Windows is for all network traffic (and I believe it considers SAN fabric communication in this as well) to be handled by that processor.
See article - http://support.microsoft.com/kb/892100
There was also a post on here - http://www.sqlservercentral.com/Forums/Topic399630-149-1.aspx
Curious if this sounds like your issue?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 17, 2008 at 12:39 pm
hmm that KB relates to a fileserver, not a sql server. batch processes are difficult to debug and your situation may be down to waiting threads - I'd look at waits and sys.dm_os_schedulers ( or ums stats ) I'm not aware of issues with one proc/core with san/networks - I run various up to 16 core / 8 way boxes with multiple network cards ( teamed clusters ) and multiple hbas to san and not seen this.
I would suggest you make sure o/s and sql server are to latest patch levels.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 17, 2008 at 12:40 pm
actually make sure your hba, nic and array controller drivers are bang up to date and configutred correctly
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 17, 2008 at 1:20 pm
It relates to Windows server, not file server specifically. Did you see something in that KB article that made it file server specific? Curious.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 18, 2008 at 9:49 am
if most of that cpu time on cpu1 is kernel time, then I would have to aggree with the ideas of network/san/etc. But if it is mostly user time cpu, then I would think it may be something else.
September 18, 2008 at 12:28 pm
it mentioned file copies and open files, by default windows install is for a file server, you have to configure specifically for an app server. It's a bit of large area, as I say check server setup, patches, drivers, settings, hba queues, etc.. etc.
I could suggest you download Process Explorer from ms/sysinternals, this will allow you to see exactly which process is using each cpu.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 18, 2008 at 1:00 pm
Thanks for all the replies...
Gila Monster
I could not answer as I came in a little late today. We are using MSMQ (microsoft message queuing) for batch processing.
P.S Davidb
David it was the 11th processor which was shooting up and your article was a lot of help as I dont know if that is what causing the problem. I am in the process of changing the registry entry and retry the whole batch processing. I will let you know once I am done with that.
P.S SSCrazy
I have Windows server 2003 X64 enterprise SP2 and Microsoft SQL Server 2005 64 bit SP2. I did not look into the HBA, NIC cards yet. I would install download process explorer and test it. I would do that and let you know.
Thanks for all the help guys,
You guys rock
Thanks,
Nikhil parsi
September 19, 2008 at 1:45 am
toparsi (9/18/2008)
Thanks for all the replies...Gila Monster
I could not answer as I came in a little late today. We are using MSMQ (microsoft message queuing) for batch processing.
Ok, but what's SQL doing? Are you processing a single message at a time, do you have multiple connections to SQL all processing messages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2008 at 2:47 am
Hi,
If the batch processing is within SQL Server it could be an idea to check if you are limiting the CPUs that your SQL Server is using. Either the Server settings themselves are limiting parallel processing, or maybe your t-sql code is using the maxdop setting to limit the processing to one core.
HTH
germanDBA
Regards,
WilliamD
September 19, 2008 at 1:28 pm
1) is affinity masking being used?
2) is the batch job query(s) limited in parallelism, either explicitly or by maxdop setting or cost threshold for parallelism?
3) do a simple test while the batch load is running, set up 4-8 other separate query windows and have them do something in a loop that would obviously cause multiple cpus to show activity.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 24, 2008 at 12:39 pm
Look to see if the batch processing uses a parallel plan.
Could you define exactly what the processing is doing?
September 25, 2008 at 7:19 am
Sounds like this could be parallelism. The MAXDOP option may help with this. This link discusses a little further - http://www.eggheadcafe.com/software/aspnet/29714390/option-maxdop-1.aspx
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply