What should the administrator to do improve the SQL Server performance?

After the migration, some SQL queries take longer to execute than prior to migration.

The following SQL best practices were applied while creating the SQL VM on Nutanix:

• 8 vDisks per SQL server VM

• Database and Log files on separate vDisk

• 2 TempDB database drives and 1 TempDB log drive

What should the administrator to do improve the SQL Server performance?
A . Redistribute existing data by creating additional database file and enable autogrowth
B . Create multiple TempDB data files and enable autogrowth
C . Create multiple SQL log files and disable autogrowth
D . Redistribute existing data by creating additional database files and disable autogrowth

View Answer

Answer: B

Explanation:

In the scenario where SQL queries are running slower post-migration, creating multiple TempDB data files is a recognized best practice for optimizing SQL Server performance, especially when dealing with high transaction rates or complex queries. This approach helps in distributing the I/O load across multiple files and drives, reducing the contention and wait times associated with TempDB, which is a common bottleneck in SQL Server environments. Enabling autogrowth ensures that these files can dynamically grow as needed, which helps in handling unexpected increases in database

activity without manual intervention.

Reference: Nutanix Best Practices for SQL Server, SQL Server performance tuning guidelines.