question

sqlLearner 1 avatar image
sqlLearner 1 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.
tsqlsql serverperformanceselectinto
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have you looked at the execution plan for both queries to see if there is any differences? Have statistics been updated in one environment and not the other?
1 Like 1 ·
What else is running on these environments? Are they physical or virtual?
0 Likes 0 ·
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.
0 Likes 0 ·
Agree with @JohnM - are the exec plans different?
0 Likes 0 ·
sqlLearner 1 avatar image
sqlLearner 1 answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlLearner 1 avatar image
sqlLearner 1 answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pretty sure you've nailed it on your own.
0 Likes 0 ·
Umesh.DBA avatar image
Umesh.DBA answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

baskar avatar image
baskar answered
what is the sql server version 32-bit or 64-bit? run this select @@version
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.