Hi All, Performance issue Background. This is a virtual server with below specs SQL Server 2014 Ram : 16 GB Processors: 8 MAX SQL memory 8 GB Maxdop : 0 Cost threshold for Parallelism : 50 We started facing some performance issues on this server. This server is mainly used for logging SSIS packages which run in other servers. Those packages run in parallel. These loggings used to complete in few seconds until a few days ago. But from last 4-5 days its taking more time, due to which SSIS packages are not moving forward. We see a lot of CXPacket waittype and lots of intermittant blockings. I have run some performance counters for about 18 hrs and below are the results. Can someone help me analyse these counter values and suggest how do improve the performance. **Physical Disk:** Avg. Disk Queue length : 376 Avg. Disk sec/Read : 0.15 Avg. Disk sec/Transfer : 0.14 Avg. Disk sec/Write 0.02 **Processor** % Processor time : 24.6 **SQL Server: Buffer manager** BCHR: 98.2 Lazy writes/sec : 14.7 PLE : 18.9 Pages read/sec : 20,630.7 Pages write/sec : 47.2 **SQL Server: Memory Manager** Free Memory (KB) 34,592.9 Memory grants outstanding 12.2 Memory Grants Pending : 0.0 Stolen Server Memory (KB) : 4,812,589.9 Targer Server Memory (KB) : 8,388,608.0 Total Server Memory (KB) : 8,388,615.9 **SQLServer: SQL Statistics** SQL Compliations/sec : 10.1 SQL Re-Compliations/sec 1.18 Any help would be highly appreciated. Thank you.
What would really help would be wait statistics to understand what the server is waiting on. It looks like it's either an I/O issue or CPU (guessing). PLE is a horrible measure, only good for comparisons of load over time. It's not a good indication of an individual problem at any given moment in time. Instead, focus on wait statistics and queueing to understand where things are slowing down. Unfortunately, none of the counters you've captured are wait stats or queues, so it's really hard to pinpoint where the issue is. Also, 8gb of memory for SQL Server is quite low. Assuming there's nothing else running on that machine, I would assume 12-14gb would be a better setting.