So my last post got me thinking what was also big problem that I spent days trying to resolve. And then I remembered.
We were doing migration of database and application from old server to new one. Everything went smoothly, everything was up and running untill couple of days later. One of procedures was taking forever to finish, at least that’s what client has reported.
And they were right, stored procedure took 18 minutes to finish. So we started testing. On old server, procedure was executing in 2-3 minutes. With same data on new server, it was taking 18-20 minutes.
Just to be clear here are specs for new and old server:
OLD server – 16 GB RAM, disks on 7.2k, RAID 5 (not ideal for database)
NEW server – 64 GB RAM. disks on 10k, RAID 10 (much better for database)
Both servers were running Windows Server 2012 and SQL Server 2012.
And we started testing and comparing everything. Updating statistics, table by table,fullscan, comparing execution plans (those turned up being identical)… We did everything we could think of and it took us days.
So then it hit me, we never checked for IOPS. Input/output operations per second was thing that was slowing us down. Noone paid notice to it as new server was superior in everything to old one exept that one little thing. As procedure was processing millions of rows of data, it processed ‘only’ 5.000 rows in part of second and waited idle for new interval (new second) to start processing new data. Problem was that procedure was writen in a way that processing every row was different process.
After working on procedure and spining things around solution was very simple. Placing one ‘begin’ and one ‘commit’ at beginning and end of procedure was very simple solution (thanks to Enes Hatibovic). This way procedure was considered as one operation and execution was under a minute.
So solution was again very simple but process to find what core of problem was, it took us days to notice.
Hope it helps someone.