May 19, 2017 at 10:10 am
Hello.
I am trying to create a table about some of our more import server instance configurations. Basically I found the information I want presented but they are among tables that currently have no obvious relation. The goal is to get the few queries below to some how be combined in one row so that I can run it on multiple servers. I'm still not very good at writing tsql but as I try and make this work I figured I'd post it in case someone can do it faster then me. Thanks!
SELECT
@@SERVERNAME as [Name],
SERVERPROPERTY('productversion') as [Version],
SERVERPROPERTY('productlevel') as [ProdLevel],
SERVERPROPERTY ('edition') as [Edition]
SELECT cpu_count, hyperthread_ratio FROM sys.dm_os_sys_info
SELECT name, value_in_use
FROM sys.configurations WITH (NOLOCK)
WHERE name in ('max server memory (MB)','optimize for ad hoc workloads','fill factor (%)','max degree of parallelism','cost threshold for parallelism')
May 19, 2017 at 10:35 am
CROSS JOIN them (the three queries), however you'll get three rows back because of your query to sys.configurations.
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
May 19, 2017 at 11:16 am
Something like this might work for you:
SELECT [Name]= @@SERVERNAME,
[Version]= SERVERPROPERTY('productversion'),
[ProdLevel]=SERVERPROPERTY('productlevel'),
[Edition]= SERVERPROPERTY ('edition'),
cpu_count,
hyperthread_ratio,
c.*
FROM sys.dm_os_sys_info
CROSS APPLY
(
SELECT [max server memory (MB)]= MAX(CASE WHEN name='max server memory (MB)' THEN value_in_use END),
[optimize for ad hoc workloads]= MAX(CASE WHEN name='optimize for ad hoc workloads' THEN value_in_use END),
[fill factor (%)]= MAX(CASE WHEN name='fill factor (%)' THEN value_in_use END),
[max degree of parallelism]= MAX(CASE WHEN name='max degree of parallelism' THEN value_in_use END),
[cost threshold for parallelism]=MAX(CASE WHEN name='cost threshold for parallelism' THEN value_in_use END)
FROM sys.configurations
)c
On an unrelated note, apparently the SQL Code blocks don't preserve the formatting the way they used to. Took a lot of manual tinkering to get this one reformatted. C'est la vie.
Cheers!
May 19, 2017 at 11:39 am
Wow! Thank you Jacob! You are awesome. I don't understand why you had to use MAX considering there is only one value for that name but you don't want to explain, you've done enough. Enjoy your day.
May 19, 2017 at 11:58 am
Glad to help!
It's a common technique to satisfy cross tab/pivot requirements.
You can see some explanation of that with links to some articles about it at https://www.sqlservercentral.com/Forums/1522400/Why-we-used-Aggregate-function-in-pivot-and-cross-tab-queries-, especially Jeff Moden's last response. It explains it pretty succinctly, but basically we do it precisely to get values from multiple rows on a single row, which was the main issue you were trying to address.
It's that effect of aggregation, consolidating results from many rows into one, that we're after; we could just as well use MIN in this case.
Hopefully that helps!
May 19, 2017 at 12:55 pm
lmacdonald - Friday, May 19, 2017 11:39 AMI don't understand why you had to use MAX considering there is only one value for that name
Because there's no WHERE clause on the query from sys.configurations, so if he didn't have a MAX, that query would return every row from sys.configurations, with NULL for the value on every row except the 4 listed in the CASE
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply