asked
Performance Table Backup
I am having some serious performance issues when executing a simple script of SELECT INTO statements to perform table backups. I am using SQL 2014 in two different environments and each environment has different storage. I restored the same database on both environments. Environment A finishes the entire script of INTO statements in under 1 minute. While Environment B takes nearly 7 minutes. The storage in environment B is not bad by any means. I am getting a high number for Reads and Writes (b/sec) and low disk queue length and response time. I have the same DB settings in each environment also. Also most of the INTO statements are dealing with tables which have large text fields. I am new to performance tuning so I am looking for some guidance on where I can check to see what the cause of the slowness is on Environment B compared to A. Any suggestions that point me in the right direction will be appreciated.
What else is running on these environments? Are they physical or virtual?
Both physical nothing else is running on either box only SQL. CPU is not being taxed at all on either box and memory is also okay.
The storage was definitely part of the issue. What I found really helped and dropped execution time tremendously was setting the DB compatability level to 120 (sql 2014). This allowedus to utilize the parallel select into feature.
http://sqlperformance.com/2013/08/t-sql-queries/parallel-select-into
Exec plans are the same. I am thinking it is storage related. I ran Perfmon on both and the Disk Writes/sec are extremely higher on A compared to B.
May be the problem is due to RPM of your HardDrive , I believe the server A is having a HDD with move RPM and the server B is having HDD with less RPM, Hope it helps Thanks.
what is the sql server version 32-bit or 64-bit? run this select @@version
17 People are following this question.
