I have 4 development SQL Server 2016 instances on a virtual server (128 GB, 12 virtual processors). Each instance is configured the same (16 GB memory, automatic processor and I/O affinity masks). All instance parameters are the same. Each instance has a database called "operations" with a table called "ErrLog" with a clustered PK index; each instance's ErrLog currently has 600-1000 records. Each operations database has one datafile on E: drive, one tx log file on F: drive. Querying the ErrLog table in instances (b), (c), and (d) is sub-second. In instance (a), it consistently takes 2-4 seconds.
I do typical maintenance (Ola Hallengren's index and statistic maintenance once a week), but this is consistently done in each instance.
I have used sp_BlitzFirst and sp_Blitz, which return issues, but consistent issues between the instances. And so far I'm not noticing any issues returned by various checkdb operations.
Can anyone suggest other troubleshooting steps to determine why instance (a) is having different performance than others? (Querying ErrLog is just a proxy for honing in on the differences; other queries are also performing differently.)
Also, I restored a copy of a.Operations into b.Operations_test, and query b.Operations_test.ErrLog also takes 2-4 seconds. I restored a copy of b.Operations into a.Operations_test, and querying a.Operations_test is sub-second.
EDIT: Thanks for your replies...
Here is the structure of the table (same in each of the instances); just the one, clustered primary key index:
CREATE TABLE [dbo].[ErrLog]( [ErrorID] [int] IDENTITY(1,1) NOT NULL, [TimestampCreate] [dbo].[TimestampCreate] NOT NULL, [ErrorNumber] [int] NOT NULL, [ErrType] [int] NOT NULL, [ErrLocation] [varchar](2000) NULL, [ErrContext] [text] NULL, [ErrSeverity] [int] NOT NULL, [MessageLong] [varchar](1024) NOT NULL, [ServerName] [dbo].[DescriptionLong] NULL, CONSTRAINT [PK_ErrLog] PRIMARY KEY CLUSTERED ( [ErrorID] ASC )
This might be a clue to the different performances: I find that in NP02, some of the records have much longer values in the ErrContext text field:
NP02: 668 rows; maximum length of "ErrContext" text field is 3,090,039
NP03: 648 rows; maximum length of "ErrContext" text field is 154,013
NP04: 411 rows; max length of "ErrContext" text field is 28,519
NP05: 46 rows; max length of "ErrContext" text field is 55,445
Top 10 Wait Statistics (pulled using Pinal Dave's query):