February 16, 2016 at 11:04 pm
This query works in sql server 2008 r2 but does not work in 2012 anymore and some of our servers have been upgraded.
Can I get a query(same output columns as below) which works across all sql versions starting at SQL 2005.
I will be collecting the underneath columns info using SSIS running it across a bunch of servers.
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
--physical_memory_in_bytes/1048576 AS [Physical Memory (MB)] -- 2008
physical_memory_kb/1024 AS [Physical Memory (MB)], ---2012 and above
[min server memory (MB)],[max server memory (MB)]
FROM sys.dm_os_sys_info AS si
CROSS APPLY (
SELECT *
FROM (
SELECT name, value
FROM sys.configurations
WHERE name IN ('min server memory (MB)','max server memory (MB)')
) AS sc
PIVOT( MIN(value) FOR name IN ([min server memory (MB)],[max server memory (MB)])) AS p
) AS ca
Thanks
February 17, 2016 at 1:01 am
I run your query on sql server 2014 and it works.
What is the error message you have when you run it on sql 2012 ?
Igor Micev,My blog: www.igormicev.com
February 17, 2016 at 2:25 am
Igor Micev (2/17/2016)
I run your query on sql server 2014 and it works.What is the error message you have when you run it on sql 2012 ?
It works fine on my SQL Server 2012 instance as well.
February 17, 2016 at 3:37 am
I think the OP is looking for a single query that will work with both <= 2008R2 and >= 2012. The problem being that the earlier versions of SQL Server have a different schema for sys.dm_os_sys_info in that they have "physical_memory_in_bytes" whereas the newer versions have "physical_memory_kb"
I've just bumped into this myself, and am mildly cursing the need to support older versions of SQL Server!
Thomas Rushton
blog: https://thelonedba.wordpress.com
February 17, 2016 at 8:21 am
There are a couple ways.
Specific for this problem, the equivalent column in sys.dm_os_sys_memory (total_physical_memory_kb) has not changed between those versions, so for 2008+ you could pull that information from that DMV instead of sys.dm_os_sys_info.
Of course, you mentioned SQL Server 2005 as well, so you will probably have to resort to the more general solution, which is to dynamically construct the query based on the value of SERVERPROPERTY('productversion').
Cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply