MSSQL: Database Diagram V17.9 feature crashed

Trying to map FKs between Dimension and Fact Tables and Database Diagram crashed with error message "Index was outside the bounds of the array". Can't get around it and error sounds like running out of memory. 

Closed everything and reopened get me going again. The issue seems pretty random.

MSSQL: Create a copy of a table and database

Create a copy of a static database.

CREATE DATABASE VCDB_COPY AS COPY OF VCDB

In a way, this is very much creating and restoring from a full backup. 

MSSQL: Instant File Initialization

Enabling Instant File Initialization can potentially improve over performance. It works a similar manner as thick eager provisioning in vCenter than letting it grows as it needs by zeroing the disk.

How To Enable Instant File Initialization
1. Open Local Security Policy and go to Local Policies –> User Rights Assignment.

2. Double click Perform Volume Maintenance Tasks and add your SQL Server database engine service account.
3. Restart the SQL Server service using SQL Server Configuration Manager and this setting should now be enabled.

MSSQL: Using DBCC to gather statistic

Trying to get statistic for performance diagnostic for a bloated vCenter table - VPX_EVENT.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-2017

dbcc show_statistics ("table_name", index_name);

use vcdb_2016

dbcc show_statistics ("vpx_event", pk_vpx_event);

dbcc show_statistics ("vpx_event", pk_vpx_event) with histogram;

MSSQL: Rebuild Index ONLINE

Comparing Rebuild Online on databases that I am familiar with. 

SQL Server
alter index xxxx rebuild ON VPX_VM REBUILD WITH (ONLINE = ON);
alter index xxxx rebuild ON VPX_VM REORGANIZE;

Oracle
alter index xxxx on VPX_VM online;
alter index xxxx rebuild ON VPX_VM REORGANIZE;

Postgresql
alter index xxx on VPX_VM online;
(No Reorganize feature for Postgresql until Postgresql 10)
Alternatively, performing Rebuild Index concurrently.

The awkward way of SQL Server to rebuild index online.