Sql-server – “tempdb metadata memory-optimized” configuration option

First, if you're getting corruption issues in TempDB, I'd start by making sure I've got DBCC CHECKDB reporting clean results across my user databases. I'd also restore those backups on another server and run DBCC CHECKDB there just to make sure the problems are only confined to TempDB.

Then I'd remove any filter drivers on the servers - antivirus, disk defragmenting, storage mirroring, etc - and try to run multiple load-intensive queries against TempDB to see if I can reproduce the issue. Do simple select-intos, dumping Cartesian joins of your user databases into TempDB.

Finally, if I can get an outage window, I'd try SQLIO - not SQLIOSIM - hammering the TempDB drives. SQLIOSIM is interesting if you want to reproduce a vague similarity of a benchmark, but SQLIO is better if you just want to repeatedly punch your storage in the junk. Plus, it's really good about bubbling failures up quickly.

You also asked:

how much more likely is drive corruption than, say, I/O path driver issues (storport, HBA driver or firmware, filter drivers).

In my experience, the most common corruption issues are connectivity problems, followed by storage processor/controller firmware bugs, followed by filter drivers (I see less only because they were so unreliable that most folks have uninstalled them), finally followed by drive corruption. After all, in modern storage, the RAID controllers are pretty good about catching problems with a single failing drive.

You have just downloaded the Management Studio, which is nothing else as an IDE. It seems to me, that you haven't downloaded/installed the SQL Server itself.

You can download it right here. On the website, just hit the download button and check the SQL Server, which is called something like SQLEXPR_x64... or if you running a 32 bit system SQLEXPR_x86....

For those who haven't SSMS downloaded, you can check SSMS on the same download page too and it will be downloaded with the server.