We migrated from Oracle to SQL Server, the new server has good resources and should burn through data.
When reading OS MasterMap data it consumes over 50% of cpu and takes several minutes to load.
I have run ANALYZE and INDEX on the three databases.
SELECT latch_class, waiting_requests_count , wait_time_ms, max_wait_time_ms --- *
FROM sys.dm_os_latch_stats
WHERE waiting_requests_count > 100
ORDER BY waiting_requests_count DESC
latch_class waiting_requests_count wait_time_ms max_wait_time_ms
BUFFER 81551206 81704580 2017
ACCESS_METHODS_DATASET_PARENT 1641537 5663549 7506
NESTING_TRANSACTION_READONLY 1084221 494094 213
DBCC_CHECK_AGGREGATE 431924 120721 64
FGCB_ADD_REMOVE 167166 2130810 806
NESTING_TRANSACTION_FULL 91970 23584 194
ACCESS_METHODS_BULK_ALLOC 25362 493780 995
SELECT wait_type, signal_wait_time_ms , waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE signal_wait_time_ms > 176397
ORDER BY waiting_tasks_count DESC
The results:
wait_type signal_wait_time_ms waiting_tasks_count
SLEEP_TASK 2002394 76078204
PAGEIOLATCH_SH 277602 67814336
SOS_SCHEDULER_YIELD 7501348 66782317
LOGMGR_QUEUE 523607 54389813
DIRTY_PAGE_POLL 187977 44182775
CXPACKET 5480362 27547323
ASYNC_NETWORK_IO 720221 20290395
LAZYWRITER_SLEEP 1119484 14749937
WRITELOG 685367 14323568
HADR_FILESTREAM_IOMGR_IOCOMPLETION 176407 9559003
BACKUPBUFFER 564488 9513327
SLEEP_BPOOL_FLUSH 252249 8582959
CLR_MANUAL_EVENT 2000281 7383128
SP_SERVER_DIAGNOSTICS_SLEEP 9665265581 5643767
