I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
--
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
--
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> > Hello-
> >
> > I am trying to restore a large sized database (100+ GB) on a machine that
> > didn't have database in the beginning, so its a create DB + restore
> > operation.
> >
> > Below is the machine configuration:
> >
> > Machine Configuration: quad processor (Pentium 3 Xeon)
> > Memory: 2 GB
> > HDD: Fiber Array Channel with separate controllers.
> > Version: SQL Server 2000/SP3 (Standard Version)
> > OS: Windows 2003/SP1 (sp presence is a guess).
> >
> > I understand the process will take some time to create database first
> > before
> > starting restoration. So far, its been running for over 2 1/2 hours
> > without
> > any sign of restoration (I have kept STATS=1 for getting any indication of
> > restoration start).
> >
> > Now, I have three(3) questions:
> >
> > 1. What should be the approximate DB creation time in such type of
> > environment? 3-4 hours'
> >
> > 2. How come I know if there is a problem? Currently the restore process
> > has
> > wait type of ASYNC_IO_COMPLETION with very large wait time.
> >
> > 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> > db creation/restoration) across all processers? OR this is only true in
> > some
> > instances say index rebuild?
> >
> > Thanks in advance!!!
> >
> > --
> > Regards,
> > MZeeshan
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>> Once this is done, the restore can really happen.
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>> Regards
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>|||This is a multi-part message in MIME format.
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
>
> --
> Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>>
>> Regards
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@.epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any
indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore
process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true
in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL Server =will first run a BACKUP VERIFICATION and you will first have to wait for SQL =Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.
If you need fast recovery, you should =consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore phases; =however, the raw database creation will still be solely dependent on the =disk subsystem throughput.
If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.
An alternative to these methods would =be to use one of several 3rd-party backup tools. Lightspeed by Imceda =would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)"
--=_NextPart_000_0595_01C56968.94534E90--|||This is a multi-part message in MIME format.
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
ANthonys suggestion of using striped backups to improve restore speed is =a great idea...
And in response to your third question, you will not see an difference =in backup/restore speeds if you upgrade to Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message =news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction =log files to be created and zeroed out, SQL Server will first run a =BACKUP VERIFICATION and you will first have to wait for SQL Server to =read through the backup file first. The file read itself can take =minutes to hours depending on size, disk throughput, and backup file =multiplexing.
If you need fast recovery, you should consider backing up to multiple =files per backup. This will allow you to run parallel read operations =for the verification and actual restore phases; however, the raw =database creation will still be solely dependent on the disk subsystem =throughput.
If you also need fast backup times, consider multiple data files per =filegroup. This will allow SQL Server to run parallel operations for =the backup process.
An alternative to these methods would be to use one of several =3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
-- "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message =news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to =their IO loads.
Getting the fastest disk subsystem, and configuring it correctly, =does more to help than STD/EE editions upgrade.
Regards-- --
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and =currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible =improvement in > any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some =of the
> common activities happening on this box.
>
> -- > Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages =for the >> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to =create,
>> depending on the IO performance of the disk subsystem.
>>
>> Regards
>> -- >> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@.epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a =machine >> > that
>> > didn't have database in the beginning, so its a create DB + =restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database =first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 =hours
>> > without
>> > any sign of restoration (I have kept STATS=3D1 for getting any =indication >> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type =of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore =process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY =workload >> > (here
>> > db creation/restoration) across all processers? OR this is only =true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > -- >> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
ANthonys suggestion of using striped =backups to improve restore speed is a great idea...
And in response to your third question, =you will not see an difference in backup/restore speeds if you upgrade to =Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Anthony Thomas" wrote in =message news:eSs5dLZaFHA.3184=@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL =Server will first run a BACKUP VERIFICATION and you will first have to wait for =SQL Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.
If you need fast recovery, you =should consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore =phases; however, the raw database creation will still be solely dependent on =the disk subsystem throughput.
If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.
An alternative to these methods =would be to use one of several 3rd-party backup tools. Lightspeed =by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)"
--=_NextPart_000_0024_01C569A2.12FAEC60--
No comments:
Post a Comment