Shared drive's performance vs local drive : to test SQL script's runtime

In a previous maintenance window, I ran an SQL script given by our
principal to update DB schemas & this run took double the amount
of time catered for our downtime.

I'll need to run this script on yet another MS SQL database which is
about four times larger than this last database so I'm required by the
CAB (Change advisory board) to test this on a staging MS SQL VM
(which has SQL Mgmt Studio & MS SQL softwares installed)

However, I have hard time justifying to add a 320GB LUN to this test
server: instead there's another VM (let's call it VM B) n the same subnet
as this test which has 320GB of free space (also FC disk):

I'm exploring the options,

Q1:
If I share out this VM B's drive to my test server (which has MS SQL &
Mgmt studio installed), restore from tape the DB's backup into it , will
running the SQL script to update this 'test Db over the LAN' give us an
estimated run time that's quite close to having the test database sits
in a local drive (FC disk)?  

This VM B & test servers are both FC disk

Q2:
Suppose I install SQL Mgmt Studio on this VM B (which does not have
MS SQL server running it it), is it sufficient to run the MS SQL script
or I'll still need to have MS SQL software installed/run on it?

Q3:
I thought of getting our DBA to export out a dump of the production Db
& build a new DB: is this likely to be faster than restoring the production
Db into a test environment?
sunhuxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sunhuxAuthor Commented:
Btw, the test VM & the test VM (which is short of
storage) are both on Gigabit network on same
subnet so I guess the network speed if fast
enough as to not contribute to delays compared
to having the disk locally or am I mistaken?
Scott PletcherSenior DBACommented:
Wouldn't another step be to review the script and make sure its efficient?  Or will "[y]our principal" not allow you to do that?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sunhuxAuthor Commented:
> Wouldn't another step be to review the script and make sure its efficient?  
Good question, I've been asking our principal last few days & appears that either
they dont know or their backend is not responding.  I'll attach the sanitized sql
script shortly here
10 Holiday Gifts Perfect for Your Favorite Geeks

Still have some holiday shopping to do for the geeks in your life? While toys, clothing, games, and gift cards are still viable options for your friends and family, there’s more reason than ever to consider gadgets and software.

sunhuxAuthor Commented:
attached the SQL script: most appreciate if anyone can enhance it to
improves its performance & provide ways to track its progress (say
by % or by how many more hours to go before completion)
SanitizedUpdateSchemaSqlScript.txt
sunhuxAuthor Commented:
Typo correction:

> Btw, the test VM & the test VM (which is short of storage) are both on Gigabit network on same
> subnet so I guess the network speed if fast enough

    should read

 Btw,  VM B & the test VM (which is short of storage) are both on Gigabit network on same
 subnet so I guess the network speed is fast enough . . .
Scott PletcherSenior DBACommented:
Looks like whoever did the script had an Oracle background.  SQL Server doesn't require cursors for every read, and they are performance killers.  

PRINTs are also relatively extremely slow, and there are gazillions of those as well.

I suspect they won't want to modify the script, which means it will be difficult to speed this up much.
sunhuxAuthor Commented:
I only see a few hundred lines of "PRINTs" being output in SQL Studio's
"Messages" tab.

Does anyone know how to remove the cursors in the script or
enhance it to speed up?  Aiming for 6 hrs or less

We have housekept the database to less than half of its size & despite
that, this script takes about 18 hours to update the schema, something
which our governing body won't approve for the maintenance window.

We don't have SSD yet (will be added around end Q1 next year), & the
18 hrs timing is based on FC disk, the fastest storage disk that we have
currently
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.