0

I have upgraded SQL Server Express 2008 r2 to SQL Standard Edition on amazon EC2 with a Intel Xeon E5-2670 v2 (Ivy Bridge) Processors with 2 vCPU.

I have verified the edition by running this:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') 

and getting

10.50.2500.0    SP1 Standard Edition (64-bit)

Also in the Windows Logs -> Application I'm seeing this MSSQLSERVER log:

Detected 2 CPUs. This is an informational message; no user action is required.

I am running the below SQL Query:

SELECT SUM(CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id) + CONVERT(BIGINT, o4.object_id))
FROM sys.objects o1
CROSS JOIN sys.objects o2
CROSS JOIN sys.objects o3
CROSS JOIN sys.objects o4 

and seeing this on Windows Task Manager -> Performance: enter image description here

and seeing this on windows Task Manager -> Applications: enter image description here

I'm thinking that the SQL Server Express CPU limitation of 1 CPU is not removed.

  • Does anybody knows why? Does it have to be something with the query?

  • And how to fix it?

closed as off-topic by Paul White says GoFundMonica, Mark Storey-Smith, Shawn Melton, swasheck, Kin Shah Jul 18 '14 at 17:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White says GoFundMonica, Mark Storey-Smith, Shawn Melton, swasheck, Kin Shah
If this question can be reworded to fit the rules in the help center, please edit the question.

1

If you want to see pain points of a query capture the actual execution plan via SSMS when you run it. Viewing this within SQLSentry Plan Explorer (free!!) is a bit more easy on the eyes than what SSMS will show. There are some good blog post and articles scattered around the Internet on how to read an execution plan if you need it.

Capture Actual Execution Plan:

enter image description here

  • What are the steps needed for me in SSCM to configure the SQL Server Agent service correctly? – ozba Jul 17 '14 at 22:10
  • Why you posted duplicate question for second part of the question I have replied with link please check. I dont think any issue here if SQL Server is using 50 % CPU and as already pointed use execution plan to see why CPU is high. – Shanky Jul 17 '14 at 22:17
  • And I don't believe the Query has something to do with it, I'm thinking that the SQL Express CPU limitation of 1 CPU is not removed. – ozba Jul 17 '14 at 22:20
  • 1
    Ok what is output of this query select cpu_count from sys.dm_os_sys_info whatever is the number returned will be CPU used by That SQL Server instance – Shanky Jul 17 '14 at 22:45
  • Added link for changing service account, works the same way for almost every service under SSCM. – Shawn Melton Jul 18 '14 at 2:00
1

The problem was in the query, it seems that it doesn't work in parallel, Here is the parallel version by @usr (Source for query: stackoverflow.com/a/24810980/122718) that do max ALL CPUs to 100%:

USE master

SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id)
INTO #temp
FROM sys.objects o1
JOIN sys.objects o2 ON o1.object_id < o2.object_id
JOIN sys.objects o3 ON o1.object_id < o3.object_id

SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt))
FROM #temp o1
JOIN #temp o2 ON o1.MyInt < o2.MyInt
  • 1
    Not all queries benefit from running in parallel. – ypercubeᵀᴹ Jul 18 '14 at 17:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.