Last Friday, I migrated a SQL Server instance from 2005 Enterprise Edition to a brand new Windows Server 2012 server with SQL Server 2012 Enterprise Edition installed. Since then, users have started to complain about the application performance.
Here is some information about the server:
- Virtual server on VMWare 5.5
- 4 vCPU and 24 Gb RAM. On the previous configuration, 10 Gb was necessary but the tempdb database was smaller than what I set (almost 6Gb)
- I now have set the maximum memory target to 22 Gb and tempdb is totally in Buffer Pool
- After migration (performed using Database Mirroring), I ran update statistics, index rebuild, and other maintenance commands
I don't know where to find answers. If everything except the SQL Server instance is the same (VM, Disks, etc) then, to me, the problem is in SQL Server configuration, but where? I have tried some changes but none of them seem to have been useful.
Do you have any ideas?
Additional information from comments
- I ran sp_Blitz which gave me some information, but nothing new from SQL 2005 (slow storage for instance)
- The memory consumption (20Gb + ) is mostly due to tempdb (6Gb) and the remainings are mostly taken by an application database
- PLE flows and sometimes goes down to 150. At the beginning of the day, most of the data is out of the cache
- The max memory setting was 10Gb on the previous server
- We use Idera diagnostic manager
- The max server memory is set to twice the value it was on the previous server.
- The page life expectancy drops below 150 at least twice a day
I started an analysis with sp_BlitzIndex and in addition to poorly written code, it showed this:
Aggressive Indexes: Total lock wait time > 5 minutes (row + page)
dbo.TABLE1.PK_TABLE1 (1): Row lock waits: 3,591; total duration: 10 minutes; avg duration: 0 seconds; Page lock waits: 23; total duration: 15 seconds; avg duration: 0 seconds; Lock escalation attempts: 510,489; Actual Escalations: 1. dbo.TABLE1.I_TABLE1_CNTTYPE_CATEGORY_IS_CURRENT (85): Row lock waits: 155; total duration: 48 seconds; avg duration: 0 seconds; Page lock waits: 129; total duration: 8 minutes; avg duration: 4 seconds; Lock escalation attempts: 29,423; Actual Escalations: 4,951.
Would just an additional index creation do some changes in that phenomenon?
I've compared the output of sp_configure as requested. Here are the differences:
Config Old New Blocked process threshold 0 120 Maximum Degree of parallelism 0 4 Maximum Memory (MB) 10240 22000
The power option is already at High Performance. I set the memory back to 10 Gb with this command:
CHECKPOINT ; DBCC DROPCLEANBUFFERS ; EXEC sys.sp_configure N'max server memory (MB)', N'10240' GO RECONFIGURE WITH OVERRIDE GO
After one hour of running with 10Gb of RAM: The last difference is the size of tempdb which is bigger than on the old server and uses now most of the memory, causing Page Life Expectancy to be hardly at 490.
Analysis of the Diagnostic Manager CPU statistics Report
The CPU statistics reports :
- an average SQL compilation of 500
- an average SQL Recompilation of 120
- Up to 10 lock waits per minute, 5 on average
- And mostly, a Table Lock Escalation which is on average of 40.
I've already set the Optimize for adhoc workload server setting and even "forced parametrization" for the most used user database.
So far, nobody told me there have been performance improvements. As it's a database I got back and was not managed by the DBA team, we don't have the background to check if it came back to a normal situation or not...
I will wait for some time and see whether it's ok for now or not. Thank you all for your help !