Link to home
Start Free TrialLog in
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?
Avatar of sunhux
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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
Avatar of 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
Avatar of 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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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