PASS Summit 2022 - Day 3 in Pictures

Other PASS Summit 2022 Days - Pre-Cons | Day 1 | Day 2

--

Day 3 of Summit 2022 was "Community Day" with a "community keynote" given by the amazing Kimberly Tripp (@kimberlyltripp) with help from her lovely assistant Paul Randal (@paulrandal).  The keynote was sponsored by Amazon AWS (@aws) but because it was community day the keynote did not include any sponsor content - a nice touch and a big thank you to AWS for supporting the concept.

Before the keynote Steve Jones (@way0utwest) introduced some warm-up entertainment, KillaDBA (@killadba) who rapped his way through #HugADBA getting the crowd ramped up for the final day.

Kimberly and Paul were up next, and Kimberly chose to take us all through "30+ Years of Innovation: How Do We Keep Up with Technology?" - a tour back to the start of her career and all of the changes in SQL Server and the world of data along the way.  She and Paul displayed their inner hoarders with an array of memorabilia, old software, and giant boxes of documentation from versions long past, and also discussed the sheer volume of data being produced in our modern world, including the fact that while we made it to the Moon with 64 kilobytes of memory, today we generate over 333 *billion* emails every day (of course 2/3 of them are probably spam!)

Kimberly wrapped up with comments about the community and how amazing it is, especially compared to many other technologies that don't have anything like the #sqlfamily - I have to admit I teared up a couple of times hearing the comments of some of my friends as to how the family has helped them in the careers and their lives.  She also talked about how sharing and teaching were the best ways to learn yourself - as she told us, "Learn-Share-Grow!"

Paul tweeted the following while I was writing this post:

Exactly.

--

My first session was "Performance Monitoring, Tuning and Scaling Azure SQL Workloads" by Deepthi Goguri (@dbanuggets) and Surbhi Pokharna (@Surbhi_Pokharna).  They were first-time Summit speakers and came through their talk in amazing fashion - even with a network hiccup causing problems in one of their Azure demos, they recovered well and handled questions and comments from the audience like champs.

Next I went to "Built-In Query Intelligence in SQL Server 2022" by Kate Smith and Bob Ward (@bobwardms) from Microsoft.  I had already seen some of the material from Bob in his workshop earlier this week, but during the workshop he told us we should come see this talk to get Kate's insights, and it was worth it - as a Senior PM in the area she added extra info beyond the base presentation.

After lunch I went to see my buddy from Chicago Frank Gill (@skreebyDBA) - I first met Frank at a SQL Saturday in Chicago years ago, and he is a great DBA and member of the community.  He talked about backups in Azure SQL and Managed Instance, and I definitely learned some things I didn't know before.

The last session of Summit 2022 for me was "SQL Server in Azure Virtual Machines - Configuring for Price-Performance" by Pam Lahoud (@sqlgoddess) and Aditya Badramrajo (@aditya_feb22) of the Microsoft Azure team.  I have seen Pam speak several times before but had not seen Aditya, and the two of them presented very well together.  I learned the nuts and bolts of Azure VM sizing and the different models, especially as it relates to capacity constraints (any given model only gets a certain amount of SPU, a certain amount of RAM, of I/O, etc.), along with some recommendations on the best models and configurations to use for SQL VM's inlcuding Pam's favorite VM model for SQL Server, the Ebdsv5, aka the #DoubleSprinklesVMs

Summit 2022 in-person has been awesome - Redgate and the event staff did an outstanding job recreating the magic that was the PASS Summit from before the pandemic, and I can't wait to come back next year!

Querying SQL and Windows Version Info with T-SQL

Just a quick one today - I see questions sometimes about polling Windows information from inside SQL Server itself.  There are a couple of frequently touted options:

--

(1)  SELECT @@VERSION

The most basic option, and it does return most of what we want but not in any kind of a pretty format:


Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 15:36:27                  
Copyright (c) Microsoft Corporation                 
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

To just run a query and see the answer this is fine, but usually I like to be able to programmatically manipulate the data (such as an ORDER BY), and a result set that is one big text field (with embedded line feeds) is not a great way to go.

--

(2) exec master..xp_cmdshell 'systeminfo'

This is sort of cheating as it requires a call to xp_cmdshell to call a Windows command rather than anything truly inside SQL Server, but it does work (assuming you have xp_cmdshell enabled):

Host Name:               Instance01

OS Name:                   Microsoft Windows Server 2008 R2 Standard

OS Version:                6.1.7601 Service Pack 1 Build 7601

OS Manufacturer:           Microsoft Corporation

OS Configuration:          Member Server

OS Build Type:             Multiprocessor Free

Registered Owner:         MyCompany

Registered Organization:   MyCompany

Product ID:                00477-001-0000421-84319

Original Install Date:     3/13/2013, 8:28:33 AM

System Boot Time:          1/28/2017, 8:03:41 AM

System Manufacturer:       VMware, Inc.

System Model:              VMware Virtual Platform

System Type:               x64-based PC

Processor(s):              4 Processor(s) Installed.

[01]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[02]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[03]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

[04]: Intel64 Family 6 Model 45 Stepping 2 GenuineIntel ~2893 Mhz

BIOS Version:              Phoenix Technologies LTD 6.00, 9/21/2015

Windows Directory:         C:\Windows

System Directory:          C:\Windows\system32

Boot Device:               \Device\HarddiskVolume1

System Locale:             en-us;English (United States)

Input Locale:              en-us;English (United States)

Time Zone:                 (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna

Total Physical Memory:     24,576 MB

Available Physical Memory: 5,405 MB

Virtual Memory: Max Size:  25,598 MB

Virtual Memory: Available: 6,182 MB

Virtual Memory: In Use:    19,416 MB

Page File Location(s):     D:\pagefile.sys

Domain:                   mydomain.com

Logon Server:              N/A

Hotfix(s):                 143 Hotfix(s) Installed.

[01]: KB2470949

[02]: KB2509553

[03]: KB2511455

[04]: KB2547244

[05]: KB2560656

[06]: KB2570947

[07]: KB2585542

[08]: KB2604115

[09]: KB2621440

[10]: KB2644615

[11]: KB2654428

[12]: KB2667402

[13]: KB2676562

[14]: KB2690533

[15]: KB2692929

[16]: KB2698365

[17]: KB2705219

[18]: KB2709715

[19]: KB2724197

[20]: KB2736422

[21]: KB2742599

[22]: KB2758857

[23]: KB2765809

[24]: KB2770660

[25]: KB2799494

[26]: KB2807986

[27]: KB2813170

[28]: KB2813347

[29]: KB2813430

[30]: KB2840149

[31]: KB2840631

[32]: KB2861698

[33]: KB2862152

[34]: KB2862330

[35]: KB2862335

[36]: KB2862973

[37]: KB2864202

[38]: KB2868038

[39]: KB2871997

[40]: KB2884256

[41]: KB2892074

[42]: KB2893294

[43]: KB2894844

[44]: KB2898851

[45]: KB2911501

[46]: KB2931356

[47]: KB2937610

[48]: KB2943357

[49]: KB2957189

[50]: KB2968294

[51]: KB2972100

[52]: KB2972211

[53]: KB2973112

[54]: KB2973201

[55]: KB2973351

[56]: KB2977292

[57]: KB2978120

[58]: KB2984972

[59]: KB2991963

[60]: KB2992611

[61]: KB3000483

[62]: KB3003743

[63]: KB3004361

[64]: KB3004375

[65]: KB3010788

[66]: KB3011780

[67]: KB3018238

[68]: KB3019978

[69]: KB3021674

[70]: KB3022777

[71]: KB3023215

[72]: KB3030377

[73]: KB3033889

[74]: KB3035126

[75]: KB3037574

[76]: KB3038314

[77]: KB3042553

[78]: KB3045685

[79]: KB3046017

[80]: KB3046269

[81]: KB3055642

[82]: KB3059317

[83]: KB3060716

[84]: KB3068457

[85]: KB3071756

[86]: KB3072305

[87]: KB3072630

[88]: KB3074543

[89]: KB3075220

[90]: KB3076895

[91]: KB3078601

[92]: KB3080446

[93]: KB3084135

[94]: KB3086255

[95]: KB3092601

[96]: KB3097989

[97]: KB3101722

[98]: KB3108371

[99]: KB3108381

[100]: KB3108664

[101]: KB3108670

[102]: KB3109103

[103]: KB3109560

[104]: KB3110329

[105]: KB3122648

[106]: KB3123479

[107]: KB3124275

[108]: KB3126587

[109]: KB3127220

[110]: KB3133043

[111]: KB3135983

[112]: KB3139398

[113]: KB3139914

[114]: KB3139940

[115]: KB3142024

[116]: KB3142042

[117]: KB3145739

[118]: KB3146706

[119]: KB3146963

[120]: KB3149090

[121]: KB3156016

[122]: KB3156017

[123]: KB3156019

[124]: KB3159398

[125]: KB3161949

[126]: KB3161958

[127]: KB3163245

[128]: KB3164033

[129]: KB3164035

[130]: KB3170455

[131]: KB3177186

[132]: KB3184122

[133]: KB3185911

[134]: KB3188740

[135]: KB3192321

[136]: KB3192391

[137]: KB3205394

[138]: KB3210131

[139]: KB3212642

[140]: KB958488

[141]: KB976902

[142]: KB976932

[143]: KB3212646

Network Card(s):           1 NIC(s) Installed.

[01]: Intel(R) PRO/1000 MT Network Connection

Connection Name: LAN Prod

DHCP Enabled:    No

IP address(es)

[01]: 192.168.22.33

https://i.ytimg.com/vi/eOJ32gNM0qc/hqdefault.jpg 

Well....assuming you have xp_cmdshell enabled *and* you want to know every single KB applied to your server, ever.

Again this isn't cleanly parseable, so it isn't the optimal answer.

--

(3) Glenn Berry

If you have read my blog at all you know I am a huge fan of Glenn Alan Berry's (blog/@GlennAlanBerry) "Diagnostic Information Queries" - commonly referred to as the "DMV Queries." (as seen here, here, here, and here.)  

http://s2.quickmeme.com/img/99/995c9a89f2eb1f869fb6fd7fc72ac143a76d6313f05cb0b8309813514eb0f876.jpg

Glenn has been maintaining this forever, and does an amazing job of both deciphering the DMVs and of cataloging submissions from other prominent SQL Server professionals (such as Jimmy May's great Disk Latency query).

The first relevant query is currently Query #1:

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];

As you can see this is just the combination of @@VERSION described above and @@SERVERNAME to return the instance's name.

The next (and more interesting) query is Query #3:

SELECT SERVERPROPERTY('MachineName') AS [MachineName], 

SERVERPROPERTY('ServerName') AS [ServerName],
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [ProductLevel],                                                       -- What servicing branch (RTM/SP/CU)
SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],         -- Within a servicing branch, what CU# is applied
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
SERVERPROPERTY('ProductBuild') AS [ProductBuild],
SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],                    -- Is this a GDR or OD hotfix (NULL if on a CU build)
SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation],
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version]; 

MachineName	ServerName	Instance	IsClustered
INSTANCE01	INSTANCE01	NULL	0
ComputerNamePhysicalNetBIOS	Edition	ProductLevel	ProductUpdateLevel
INSTANCE01	Enterprise Edition (64-bit)	SP3	NULL
ProductVersion	ProductMajorVersion	ProductMinorVersion	ProductBuild
11.0.6020.0	11	0	6020
ProductBuildType	ProductUpdateReference	ProcessID	Collation
NULL	KB3072779	1988	Latin1_General_CI_AI
IsFullTextInstalled	IsIntegratedSecurityOnly	FilestreamConfiguredLevel	IsHadrEnabled
1	0	0	0
HadrManagerStatus	IsXTPSupported	Build CLR Version
2	NULL	v4.0.30319

This query introduces the concept of the SERVERPROPERTY() function and shows some of the many fields that it can retrieve.  On of the nicest things about SERVERPROPERTY() to me is that it cleanly parses out the Major and Minor versions of SQL Server - this is the most direct way to find out that your instance is Version 11 (SQL 2012).

The limitation for our purposes here is that it doesn't return any information about the Operating System version (here we are talking about Windows because these hooks just don't play into Linux)...you get a little external information like the NETBIOS name, but nothing about the actual Windows Version.

We need something more...

--

(4) My Answer

As is often the case, my best answer is a cross between a couple of the previous options to take the best of both... kind of like this:

http://weknowmemes.com/wp-content/uploads/2014/02/hilarious-animal-hybrids.jpg

(Sorry - I found that picture and couldn't resist...)


This query uses string functions to parse @@VERSION and extract some of the information (and then returns pretty text via a pair of CASE statements) and also uses some of the more meaningful SERVERPROPERTY() values.

The string to parse the Windows Version Number out of @@VERSION came from here - much easier than trying to backtrack it myself!

SELECT SERVERPROPERTY('ServerName') AS [SQLServerName]

, SERVERPROPERTY('ProductVersion') AS [SQLProductVersion]

, SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion]
, SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion]
, SERVERPROPERTY('ProductBuild') AS [ProductBuild]
, CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')),4) 

WHEN '8.00' THEN 'SQL Server 2000'

WHEN '9.00' THEN 'SQL Server 2005'

WHEN '10.0' THEN 'SQL Server 2008'

WHEN '10.5' THEN 'SQL Server 2008 R2'

WHEN '11.0' THEN 'SQL Server 2012'

WHEN '12.0' THEN 'SQL Server 2014'

ELSE 'SQL Server 2016+'

END AS [SQLVersionBuild]

, SERVERPROPERTY('ProductLevel') AS [SQLServicePack]

, SERVERPROPERTY('Edition') AS [SQLEdition]

, RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3) as [WindowsVersionNumber]

, CASE RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)

WHEN '5.0' THEN 'Windows 2000'

WHEN '5.1' THEN 'Windows XP'

WHEN '5.2' THEN 'Windows Server 2003/2003 R2'

WHEN '6.0' THEN 'Windows Server 2008/Windows Vista'

WHEN '6.1' THEN 'Windows Server 2008 R2/Windows 7'

WHEN '6.2' THEN 'Windows Server 2012/Windows 8'

ELSE 'Windows 2012 R2+'

END AS [WindowsVersionBuild]

This allows me to return a pretty result set and also to extract number values and simple strings that I can then programmatically act upon (or sort/filter in Excel):

SQLServerName	SQLProductVersion	ProductMajorVersion	ProductMinorVersion	ProductBuild
INSTANCE01	11.0.6020.0	11	0	6020
SQLVersionBuild	SQLServicePack	SQLEdition	WindowsVersionNumber	WindowsVersionBuild
SQL Server 2012	SP3	Enterprise Edition (64-bit)	6.1	Windows Server 2008 R2/Windows 7

--

Hope this helps!