SQL 2014 Enterprise installed on Server 2012 R2
8 cpu core's & 24 gb mem
SQL instance is dedicated to ONE application database. DB is 210 gb in size.
6 tempdb’s spread across C:\, D:\, E:\, each 12.5 gb for a total of 75 gb combined. Trace flag 1117 applied.
System logs and application log stored on D:\
System DB’s (master, model, msdb) and application DB stored on E:\
We have a third party enterprise web application connected to a SQL database. The objective is to upgrade the application to a newer version – this process executes a series of SQL scripts against the database.
We made a copy of the production DB in our development environment. Dev architecture & resources are identical to Prod. The dev upgrade completed successfully in 6.5 hours.
The application upgrade is underway in production. It appears to be moving much slower than what we encountered in dev/test. Using SQL Activity Monitor we see our commands routinely become SUSPENDED with a wait type PAGEIOLATCH_SH.
Using Resource Monitor we see E:\ has a high amount of disk activity, specifically the .mdf data file of our application.
The prod upgrade is moving much slower than dev/test, at this rate it will take 10-12 hours to complete – double the amount of time in dev/test. We did not observe so many SUSPENDED processes during dev/test.
I’ve been researching PAGEIOLATCH_SH on the www but so far nothing has led us to the culprit. As a test we increased memory in the prod SQL server from 24 gb to 32 gb but did not receive noticeable performance gains.
What’s the best way to track down the ‘wait type’ we’re experiencing and how to best identify a solution?
Changing (or optimizing) the SQL queries executed during upgrade IS NOT an option. We expect the same performance in prod as observed in dev.
Thanks in advance for your expertise!