Avatar of sunhux
sunhux
 asked on

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?
Microsoft SQL Server 2008Microsoft SQL ServerScripting Languages

Avatar of undefined
Last Comment
sunhux

8/22/2022 - Mon
sunhux

ASKER
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?
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sunhux

ASKER
> 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
sunhux

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
sunhux

ASKER
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 . . .
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sunhux

ASKER
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