March 31, 2014 at 3:39 pm
I am testing 11.0.3128 on a 12 core machine - this is a default SQL2012 installation on WindowsServer2008R2.
I created 12 T-SQL jobs, all the same except using different partitions of the same data table.
I fired them off.
What was unexpected is that the total CPU usage settles at 41% (5/12 of total)
CPU control effect % 100.0
CPU usage % 41.5
CPU usage target % 41.0
Well, that is rather strange, because select * from sys.resource_governor_configuration returns two zeroes and SSMS shows the Resource Governor as disabled.
select pool_id, name, active_memgrant_count,memgrant_waiter_count from sys.dm_resource_governor_resource_pools
pool_idnameactive_memgrant_countmemgrant_waiter_count
1internal00
2default57
Suggesting I have been limited to 5 active operational sessions (which is exactly what I see from the IO activity)
Anyway, the governor is supposed to be "off", and since I paid for these 12 CPUs, I would rather that I decide whether they get to sit on 100% utilisation!
Am I missing something?
March 31, 2014 at 4:14 pm
What's your MAXDOP and Cost threshold for Parallelism ?
--
SQLBuddy
March 31, 2014 at 4:41 pm
See OP. It is a default installation, so they would be MAXDOP 0 and Threshold 5.
This makes the practical MAXDOP 12 (# of cores)
This is irrelevant because the queries I am running have no parallelism.
March 31, 2014 at 9:28 pm
I don't know. But if I was going to explore options:
1. I'd check there's no Processor Affinity masks set, and no maximum number of concurrent connections. I know you said it's a default install, I'd still check it because it only takes a second.
2a. I'd restart with Trace Flag 8040 which permanently disables Resource Governor, and see what happens.
2b. Or I'd re-enable Resource Governor with a simple classifier function and a Max CPU of 100% to see what happens.
3. Failing that I'd try to run the jobs concurrently myself (i.e. in 6 SSMS sessions) to see if they all run concurrently. If they worked fine I might suspect some kind of connection pooling or connection limit going on elsewhere.
And if all of that washed out and showed nothing... I don't know. But keep us informed ^..^
April 1, 2014 at 3:46 am
Cody K (3/31/2014)
I don't know. But if I was going to explore options:1. I'd check there's no Processor Affinity masks set, and no maximum number of concurrent connections. I know you said it's a default install, I'd still check it because it only takes a second.
2a. I'd restart with Trace Flag 8040 which permanently disables Resource Governor, and see what happens.
2b. Or I'd re-enable Resource Governor with a simple classifier function and a Max CPU of 100% to see what happens.
3. Failing that I'd try to run the jobs concurrently myself (i.e. in 6 SSMS sessions) to see if they all run concurrently. If they worked fine I might suspect some kind of connection pooling or connection limit going on elsewhere.
And if all of that washed out and showed nothing... I don't know. But keep us informed ^..^
1: I wasted that second of my life
2a: The dialogue to change startup parameters is not accessible to any account, privileged or otherwise. This is a Developer edition, so that may be the answer
2b: First I need to learn the semantics and syntax (this is all new)
3: 5 maximum
4:
April 1, 2014 at 4:21 pm
I removed the installation and installed a full Enterprise edition.
No change.
Amongst the irritating things is that I do not have access to the star tup parameter settings in the configuration utility, so I do not have the possibility of forcing the governor to be off by that method. Of course as I posted earlier, SQLServer thinks the governor is turned off already, so I somehow doubt it would make any difference.
April 2, 2014 at 11:47 am
-T8040 seems to have removed the offending functionality completely.
April 4, 2014 at 7:52 am
... but to no apparent effect.
For laughs, I went to SQL2014 because, hey, I can 🙂
The agent is started with the governor turned off via the trace flag.
I started 5 SQLagent jobs, and sure enough, only 4 do anything. I tried running the procedures in SSMS with the same result.
select pool_id, name, active_memgrant_count,memgrant_waiter_count from sys.dm_resource_governor_resource_pools
pool_idnameactive_memgrant_countmemgrant_waiter_count
1internal41
I thought (maybe incorrectly) that dm_resource_governor_resource_pools was specifically related to the resource governor ... silly me.
This is becoming most frustrating.
For some reason, since upgrading from SQL2008R2 something changed and for the life of me, I cannot see what it is.
June 3, 2014 at 3:03 pm
No joy at all of late.
This is a view of the current running situation. 12.0.2000
I previously had 12 executing procedures, 10 active and 2 were pending memory grant.
3 procedures have completed, but still 2 are waiting.
I do not see a memory shortage.
I do not see a processor shortage (5 unused processors)
I do not see a disk IO issue
(There is a backup running from K: at this moment which accounts for all writes to S:)
The machine is very unresponsive, and SSMS hangs regularly, and occasionally crashes as well.
I am open to suggestions.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply