Predict % completed

I'm doing a web search page on a db that has about 1 million records, and
the query is quite complicated. I would like to provide the user some
feedback will the db server is performing the search.
What is the best way to do this? any examples or tutorials?
The only way I can think of is doing 10000 records at a time and add 1% to a
counter.
There must be a better way.
Any input is greatly appreciated,
AaronWhat about firing first the Query to the Optimizer and sshowing the
execution plan for it, you might see there how long (CPU cycles) it will
last and how many rows are estimated (Requirement of maintained statistics
:-)
See for this one:
USE NORTHWIND
GO
SET SHOWPLAN_ALL ON
GO
Select CompanyName from Customers where CompanyName LIKE '%A%'
GO
SET SHOWPLAN_ALL OFF
GO
(Result gonna spread over the page, best thing you execute in on your own.)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Aaron" <kuya789@.yahoo.com> schrieb im Newsbeitrag
news:O503JyYQFHA.204@.TK2MSFTNGP15.phx.gbl...
> I'm doing a web search page on a db that has about 1 million records, and
> the query is quite complicated. I would like to provide the user some
> feedback will the db server is performing the search.
> What is the best way to do this? any examples or tutorials?
> The only way I can think of is doing 10000 records at a time and add 1% to
> a counter.
> There must be a better way.
> Any input is greatly appreciated,
> Aaron
>
>|||what's the Optimizer ? is that an addon to sql server?
i don't think i have that
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eu5VT4YQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> What about firing first the Query to the Optimizer and sshowing the
> execution plan for it, you might see there how long (CPU cycles) it will
> last and how many rows are estimated (Requirement of maintained statistics
> :-)
> See for this one:
>
> USE NORTHWIND
> GO
> SET SHOWPLAN_ALL ON
> GO
> Select CompanyName from Customers where CompanyName LIKE '%A%'
> GO
> SET SHOWPLAN_ALL OFF
> GO
> (Result gonna spread over the page, best thing you execute in on your
> own.)
> HTH, Jens Smeyer
>
> --
> http://www.sqlserver2005.de
> --
> "Aaron" <kuya789@.yahoo.com> schrieb im Newsbeitrag
> news:O503JyYQFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||On Fri, 15 Apr 2005 16:06:44 -0700, Aaron wrote:

>what's the Optimizer ? is that an addon to sql server?
>i don't think i have that
(snip)
Hi Aaron,
The optimizer is an integral part of SQL Server. It's the part of the
server that reviews your query and determines the execution plan (the
order and methods in which data will be fetched and combined to satisfy
your request).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
Thanks
Regardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks
|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.

>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
with[vbcol=seagreen]
power
>
>.
>
|||Peter
<http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power
|||Automatic recovery (what happens when you start SQL Server) doesn't have anything to do with
backups. SQL Server records all modifications in the transaction log, regardless of recovery model.
In simple, SQL Server removes log records from the transaction log when they aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of advantages, like backup
log even of the database becomes corrupt, point in time restore etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power
|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power

Power Failure for SQL Server 2000

We encounter a power failure for about half an hour and
the SQL Server 2000 is back again.
For production databases, we use FULL Recovery Model with
Transaction Log backed up every half an hour. After power
is up, everything works properly.
I would like to know what happens to the SQL Server 2000
when the power fails and how the data is recovered when
power is up again.
ThanksRegardless of the recovery model, each database is automatically recovered
when the instance starts. Data are read from the transaction log since the
last checkpoint and applied to the database. Uncommitted transactions are
then rolled back. The end result is that the database is recovered to the
point of the failure, less uncommitted transactions.
Your FULL recovery model and log backups provide extra protection in the
event of media loss due to hardware failure or data corruption.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
> We encounter a power failure for about half an hour and
> the SQL Server 2000 is back again.
> For production databases, we use FULL Recovery Model with
> Transaction Log backed up every half an hour. After power
> is up, everything works properly.
> I would like to know what happens to the SQL Server 2000
> when the power fails and how the data is recovered when
> power is up again.
> Thanks|||Dear Dan,
Thank you for your advice.
However, for SIMPLE Recovery Model, there will be no
transaction log backup. To what state does the database
recovered to ?
Besides, would you mind to elaborate on the extra benefit
of using FULL Recovery Model ?
Thanks again.

>--Original Message--
>Regardless of the recovery model, each database is
automatically recovered
>when the instance starts. Data are read from the
transaction log since the
>last checkpoint and applied to the database. Uncommitted
transactions are
>then rolled back. The end result is that the database is
recovered to the
>point of the failure, less uncommitted transactions.
>Your FULL recovery model and log backups provide extra
protection in the
>event of media loss due to hardware failure or data
corruption.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01f401c5446e$a4183b70$a601280a@.phx.gbl...
with[vbcol=seagreen]
power[vbcol=seagreen]
>
>.
>|||Peter
<.htm#Step1" target="_blank">http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
> --administaiting best practices
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power|||Automatic recovery (what happens when you start SQL Server) doesn't have any
thing to do with
backups. SQL Server records all modifications in the transaction log, regard
less of recovery model.
In simple, SQL Server removes log records from the transaction log when they
aren't needed anymore
for this automatic recovery.
Full recovery model allow you to backup transaction log. This has a lot of a
dvantages, like backup
log even of the database becomes corrupt, point in time restore etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power|||To add to the other responses, automatic recovery will recover databases to
the same consistent state regardless of the recovery model.
Separately, database and transaction log backups reduce your vulnerability
to potential data loss. For example, if your power outage caused a hardware
problem that corrupted your log file, you could still restore from your most
recent database backup and then apply your log backups. At most, you would
lose one half hour of work. If only data files were lost, you could backup
the current log with NO_TRUNCATE and then restore your database and log
backups. No data would be lost in this case.
In the SIMPLE recovery model, your only recourse after losing data or log
files is to restore from your most recent database backup. All data
modifications since the backup would be lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:077101c54475$9661ee70$a501280a@.phx.gbl...[vbcol=seagreen]
> Dear Dan,
> Thank you for your advice.
> However, for SIMPLE Recovery Model, there will be no
> transaction log backup. To what state does the database
> recovered to ?
> Besides, would you mind to elaborate on the extra benefit
> of using FULL Recovery Model ?
> Thanks again.
>
> automatically recovered
> transaction log since the
> transactions are
> recovered to the
> protection in the
> corruption.
> message
> with
> power