I believe that when you use DBCCReindex the statistics are updated for each
table, but can anyone tell me if it does a fullscan update or a partial scan
update when using DBCCReindex?
The entire index is rebuild, so all values are touched and collected. A
partial scan would not make sense.
Gert-Jan
Rick wrote:
> I believe that when you use DBCCReindex the statistics are updated for each
> table, but can anyone tell me if it does a fullscan update or a partial scan
> update when using DBCCReindex?
|||Gert-Jan,
I have been there, done that and I disagree with you.
Rick,
If you want to get your performance back after rebuilding indexes you MUST
run update stats with a FULL SCAN because when you rebuild indexes or run
update stats statements the selectivity level by default is 10% that mean the
server scans only 10% and does not touch 90%.
DO that full scan, have your performance BACK, save some money for your
company and pray for me.
v/r
ktf
"Gert-Jan Strik" wrote:
> The entire index is rebuild, so all values are touched and collected. A
> partial scan would not make sense.
> Gert-Jan
>
> Rick wrote:
>
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Friday, March 23, 2012
Rebuilding Indexes and Updating Statistics
I believe that when you use DBCCReindex the statistics are updated for each
table, but can anyone tell me if it does a fullscan update or a partial scan
update when using DBCCReindex?The entire index is rebuild, so all values are touched and collected. A
partial scan would not make sense.
Gert-Jan
Rick wrote:
> I believe that when you use DBCCReindex the statistics are updated for each
> table, but can anyone tell me if it does a fullscan update or a partial scan
> update when using DBCCReindex?|||Gert-Jan,
I have been there, done that and I disagree with you.
Rick,
If you want to get your performance back after rebuilding indexes you MUST
run update stats with a FULL SCAN because when you rebuild indexes or run
update stats statements the selectivity level by default is 10% that mean the
server scans only 10% and does not touch 90%.
DO that full scan, have your performance BACK, save some money for your
company and pray for me.
v/r
ktf
"Gert-Jan Strik" wrote:
> The entire index is rebuild, so all values are touched and collected. A
> partial scan would not make sense.
> Gert-Jan
>
> Rick wrote:
> >
> > I believe that when you use DBCCReindex the statistics are updated for each
> > table, but can anyone tell me if it does a fullscan update or a partial scan
> > update when using DBCCReindex?
>|||ktf,
I have to disagree.
I could not believe your statement, so I tested it, as below.
-- drop table Test
create table Test(id int not null,id2 int not null)
create index CLIX_Test on Test(id)
create nonclustered index NCIX_Test on Test(id2)
insert into Test
select id,(id/2)+((id%2)*1000000000)
from sysobjects
insert into Test values (3,3)
declare @.i int
set @.i=250
while @.i>0
begin
insert into Test
select id+@.i,((id+@.i)/2)+(((id+@.i)%2)*1000000000)
from sysobjects
set @.i=@.i-1
end
go
dbcc show_statistics ("Test",CLIX_Test)
-- nothing
update statistics Test (CLIX_Test) with sample 10 percent
dbcc show_statistics ("Test",CLIX_Test)
-- low number in the "Rows Sampled" column
dbcc dbreindex("Test",CLIX_Test)
dbcc show_statistics ("Test",CLIX_Test)
-- high number in the "Rows Sampled" column
update statistics Test (CLIX_Test) with fullscan
dbcc show_statistics ("Test",CLIX_Test)
-- same number in the "Rows Sampled" column
Now the only thing I could not really explain is some (minor?)
inconsistency in the results after reindexing and after updating the
statistics with fullscan. The Rows Sampled would always be the same, but
sometimes the number of steps in the histogram would differ, causing
different results. I have only seen these differences a few times.
All of the times, both the Rows Sampled and the actual statistics
distribution would be different between the 10% sample and the
reindex/fullscan. There is no doubt about it that reindexing will not
(as a rule) sample just 10 percent of all rows, it is definitely
scanning all rows. I still have no reason to assume that a reindex would
not sample all rows for statistics purposes.
Gert-Jan
ktf wrote:
> Gert-Jan,
> I have been there, done that and I disagree with you.
> Rick,
> If you want to get your performance back after rebuilding indexes you MUST
> run update stats with a FULL SCAN because when you rebuild indexes or run
> update stats statements the selectivity level by default is 10% that mean the
> server scans only 10% and does not touch 90%.
> DO that full scan, have your performance BACK, save some money for your
> company and pray for me.
> v/r
> ktf
> "Gert-Jan Strik" wrote:
> > The entire index is rebuild, so all values are touched and collected. A
> > partial scan would not make sense.
> >
> > Gert-Jan
> >
> >
> > Rick wrote:
> > >
> > > I believe that when you use DBCCReindex the statistics are updated for each
> > > table, but can anyone tell me if it does a fullscan update or a partial scan
> > > update when using DBCCReindex?
> >
table, but can anyone tell me if it does a fullscan update or a partial scan
update when using DBCCReindex?The entire index is rebuild, so all values are touched and collected. A
partial scan would not make sense.
Gert-Jan
Rick wrote:
> I believe that when you use DBCCReindex the statistics are updated for each
> table, but can anyone tell me if it does a fullscan update or a partial scan
> update when using DBCCReindex?|||Gert-Jan,
I have been there, done that and I disagree with you.
Rick,
If you want to get your performance back after rebuilding indexes you MUST
run update stats with a FULL SCAN because when you rebuild indexes or run
update stats statements the selectivity level by default is 10% that mean the
server scans only 10% and does not touch 90%.
DO that full scan, have your performance BACK, save some money for your
company and pray for me.
v/r
ktf
"Gert-Jan Strik" wrote:
> The entire index is rebuild, so all values are touched and collected. A
> partial scan would not make sense.
> Gert-Jan
>
> Rick wrote:
> >
> > I believe that when you use DBCCReindex the statistics are updated for each
> > table, but can anyone tell me if it does a fullscan update or a partial scan
> > update when using DBCCReindex?
>|||ktf,
I have to disagree.
I could not believe your statement, so I tested it, as below.
-- drop table Test
create table Test(id int not null,id2 int not null)
create index CLIX_Test on Test(id)
create nonclustered index NCIX_Test on Test(id2)
insert into Test
select id,(id/2)+((id%2)*1000000000)
from sysobjects
insert into Test values (3,3)
declare @.i int
set @.i=250
while @.i>0
begin
insert into Test
select id+@.i,((id+@.i)/2)+(((id+@.i)%2)*1000000000)
from sysobjects
set @.i=@.i-1
end
go
dbcc show_statistics ("Test",CLIX_Test)
-- nothing
update statistics Test (CLIX_Test) with sample 10 percent
dbcc show_statistics ("Test",CLIX_Test)
-- low number in the "Rows Sampled" column
dbcc dbreindex("Test",CLIX_Test)
dbcc show_statistics ("Test",CLIX_Test)
-- high number in the "Rows Sampled" column
update statistics Test (CLIX_Test) with fullscan
dbcc show_statistics ("Test",CLIX_Test)
-- same number in the "Rows Sampled" column
Now the only thing I could not really explain is some (minor?)
inconsistency in the results after reindexing and after updating the
statistics with fullscan. The Rows Sampled would always be the same, but
sometimes the number of steps in the histogram would differ, causing
different results. I have only seen these differences a few times.
All of the times, both the Rows Sampled and the actual statistics
distribution would be different between the 10% sample and the
reindex/fullscan. There is no doubt about it that reindexing will not
(as a rule) sample just 10 percent of all rows, it is definitely
scanning all rows. I still have no reason to assume that a reindex would
not sample all rows for statistics purposes.
Gert-Jan
ktf wrote:
> Gert-Jan,
> I have been there, done that and I disagree with you.
> Rick,
> If you want to get your performance back after rebuilding indexes you MUST
> run update stats with a FULL SCAN because when you rebuild indexes or run
> update stats statements the selectivity level by default is 10% that mean the
> server scans only 10% and does not touch 90%.
> DO that full scan, have your performance BACK, save some money for your
> company and pray for me.
> v/r
> ktf
> "Gert-Jan Strik" wrote:
> > The entire index is rebuild, so all values are touched and collected. A
> > partial scan would not make sense.
> >
> > Gert-Jan
> >
> >
> > Rick wrote:
> > >
> > > I believe that when you use DBCCReindex the statistics are updated for each
> > > table, but can anyone tell me if it does a fullscan update or a partial scan
> > > update when using DBCCReindex?
> >
Rebuilding Indexes and Updating Statistics
I believe that when you use DBCCReindex the statistics are updated for each
table, but can anyone tell me if it does a fullscan update or a partial scan
update when using DBCCReindex?The entire index is rebuild, so all values are touched and collected. A
partial scan would not make sense.
Gert-Jan
Rick wrote:
> I believe that when you use DBCCReindex the statistics are updated for eac
h
> table, but can anyone tell me if it does a fullscan update or a partial sc
an
> update when using DBCCReindex?|||Gert-Jan,
I have been there, done that and I disagree with you.
Rick,
If you want to get your performance back after rebuilding indexes you MUST
run update stats with a FULL SCAN because when you rebuild indexes or run
update stats statements the selectivity level by default is 10% that mean th
e
server scans only 10% and does not touch 90%.
DO that full scan, have your performance BACK, save some money for your
company and pray for me.
v/r
ktf
"Gert-Jan Strik" wrote:
> The entire index is rebuild, so all values are touched and collected. A
> partial scan would not make sense.
> Gert-Jan
>
> Rick wrote:
>
table, but can anyone tell me if it does a fullscan update or a partial scan
update when using DBCCReindex?The entire index is rebuild, so all values are touched and collected. A
partial scan would not make sense.
Gert-Jan
Rick wrote:
> I believe that when you use DBCCReindex the statistics are updated for eac
h
> table, but can anyone tell me if it does a fullscan update or a partial sc
an
> update when using DBCCReindex?|||Gert-Jan,
I have been there, done that and I disagree with you.
Rick,
If you want to get your performance back after rebuilding indexes you MUST
run update stats with a FULL SCAN because when you rebuild indexes or run
update stats statements the selectivity level by default is 10% that mean th
e
server scans only 10% and does not touch 90%.
DO that full scan, have your performance BACK, save some money for your
company and pray for me.
v/r
ktf
"Gert-Jan Strik" wrote:
> The entire index is rebuild, so all values are touched and collected. A
> partial scan would not make sense.
> Gert-Jan
>
> Rick wrote:
>
Rebuilding an index and update statistics
Hi all,
My understanding is that once an index is rebuilt, there is no need to
run update statistics afterwards because that is automatically done. Is
this the case? For example, does this make any sense:
DBCC DBREINDEX('TableName')
EXEC ('UPDATE STATISTICS TableName')
Basically we have a vendor insisting that our performance problems with
their product are due to not updating statistics after a data load.
However, we are rebuilding the indexes after the load. Am I correct
here? Any links to MS documentation we could show to the vendor would
be a great help.
Thanks in advance.
If the indexes are being rebuilt the stats will be updated automatically
unless you have turned off AUTO UPDATE STATS or set a property of the index
to disallow the updates. As a matter of fact rebuilding with DBREINDEX will
do a FULL scan which gives the most accurate information of the breakdown.
Unless you specify a sample rate Update Stats will do a limited sample by
default. DBCC INDEXDEFRAG does not update the stats on it's own but
DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the DBREINDEX
to see for sure if they are getting updated. YOu can send the vendor the
results and tell them to take a hike<g>.
Andrew J. Kelly SQL MVP
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>
|||Hi SQLBoy
To my understanding DBCC REINDEX is equivalent to a DROP/CREATE index
statement. SQL does an update of the statistics after a CREATE INDEX
statement - unless you specify the STATISTCS_NORECOMPUTE option.
Does new data enter the table after you have loaded and rebuild the
indexes - in this case the statistics may slowly become out of date?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>
|||Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
updated during indexes' rebuild anyways.
And what's the name of index property are you referring to?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234S79xP8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> If the indexes are being rebuilt the stats will be updated automatically
> unless you have turned off AUTO UPDATE STATS or set a property of the
index
> to disallow the updates. As a matter of fact rebuilding with DBREINDEX
will
> do a FULL scan which gives the most accurate information of the breakdown.
> Unless you specify a sample rate Update Stats will do a limited sample by
> default. DBCC INDEXDEFRAG does not update the stats on it's own but
> DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the
DBREINDEX
> to see for sure if they are getting updated. YOu can send the vendor the
> results and tell them to take a hike<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
>
|||As mentioned in the other posts: the statistics of the indexed columns
will be recalculated when reindexing.
However, only the index statistics will be updated. Any manually or
automatically created column statistics will not be updated. The example
below proves this behavior.
Hope this helps,
Gert-Jan
use northwind
go
select * into Test from orders
alter table Test add constraint PK_Test primary key clustered (OrderID)
select * into Test2 from "order details"
alter table Test2 add constraint PK_Test2 primary key clustered
(OrderID,ProductID)
go
-- used to display the autocreate stats on Test2
create procedure test_showstats as
begin
declare @.sql varchar(4000)
select @.sql='dbcc show_statistics (Test2,'+name+')'
from sysindexes
where id=object_id('Test2')
and name <> 'PK_Test2'
exec (@.sql)
end
go
-- this will auto create stats on Test2.UnitPrice if "autocreate stats"
is turned on
SELECT O.OrderID,CustomerID,Freight
FROM Test O
INNER JOIN Test2 OD
ON OD.OrderID=O.OrderID
WHERE CustomerID >= 'S'
AND UnitPrice >= 40.00
go
-- shows current stats: no rows with UnitPrice=270.00
exec test_showstats
go
insert into Test2 values (10248,15, 270.00 ,1,0.0)
insert into Test2 values (10248,16, 270.00 ,1,0.0)
insert into Test2 values (10248,17, 270.00 ,1,0.0)
insert into Test2 values (10248,18, 270.00 ,1,0.0)
insert into Test2 values (10248,19, 270.00 ,1,0.0)
insert into Test2 values (10248,20, 270.00 ,1,0.0)
insert into Test2 values (10248,21, 270.00 ,1,0.0)
insert into Test2 values (10248,22, 270.00 ,1,0.0)
insert into Test2 values (10248,23, 270.00 ,1,0.0)
insert into Test2 values (10248,24, 270.00 ,1,0.0)
go
dbcc dbreindex(Test2,PK_Test2)
go
-- will show the updated value of 13 rows for OrderID=10248
dbcc show_statistics(test2,pk_test2)
go
-- shows that the stats on column UnitPrice have not been updated
exec test_showstats
go
update statistics Test2
go
-- now the stats show 10 rows with UnitPrice=270.00
exec test_showstats
go
-- cleanup
drop table Test
drop table Test2
drop procedure test_showstats
sqlboy2000 wrote:
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
|||but if you do dbcc dbreindex(Test2, '') instead the column statistics will
be updated as well
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41D854D4.9B1CCF3E@.toomuchspamalready.nl...[vbcol=seagreen]
> As mentioned in the other posts: the statistics of the indexed columns
> will be recalculated when reindexing.
> However, only the index statistics will be updated. Any manually or
> automatically created column statistics will not be updated. The example
> below proves this behavior.
> Hope this helps,
> Gert-Jan
> use northwind
> go
> select * into Test from orders
> alter table Test add constraint PK_Test primary key clustered (OrderID)
> select * into Test2 from "order details"
> alter table Test2 add constraint PK_Test2 primary key clustered
> (OrderID,ProductID)
> go
> -- used to display the autocreate stats on Test2
> create procedure test_showstats as
> begin
> declare @.sql varchar(4000)
> select @.sql='dbcc show_statistics (Test2,'+name+')'
> from sysindexes
> where id=object_id('Test2')
> and name <> 'PK_Test2'
> exec (@.sql)
> end
> go
> -- this will auto create stats on Test2.UnitPrice if "autocreate stats"
> is turned on
> SELECT O.OrderID,CustomerID,Freight
> FROM Test O
> INNER JOIN Test2 OD
> ON OD.OrderID=O.OrderID
> WHERE CustomerID >= 'S'
> AND UnitPrice >= 40.00
> go
> -- shows current stats: no rows with UnitPrice=270.00
> exec test_showstats
> go
> insert into Test2 values (10248,15, 270.00 ,1,0.0)
> insert into Test2 values (10248,16, 270.00 ,1,0.0)
> insert into Test2 values (10248,17, 270.00 ,1,0.0)
> insert into Test2 values (10248,18, 270.00 ,1,0.0)
> insert into Test2 values (10248,19, 270.00 ,1,0.0)
> insert into Test2 values (10248,20, 270.00 ,1,0.0)
> insert into Test2 values (10248,21, 270.00 ,1,0.0)
> insert into Test2 values (10248,22, 270.00 ,1,0.0)
> insert into Test2 values (10248,23, 270.00 ,1,0.0)
> insert into Test2 values (10248,24, 270.00 ,1,0.0)
> go
> dbcc dbreindex(Test2,PK_Test2)
> go
> -- will show the updated value of 13 rows for OrderID=10248
> dbcc show_statistics(test2,pk_test2)
> go
> -- shows that the stats on column UnitPrice have not been updated
> exec test_showstats
> go
> update statistics Test2
> go
> -- now the stats show 10 rows with UnitPrice=270.00
> exec test_showstats
> go
> -- cleanup
> drop table Test
> drop table Test2
> drop procedure test_showstats
>
> sqlboy2000 wrote:
|||> Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
> updated during indexes' rebuild anyways.
Sorry I was thinking ahead of myself.
> And what's the name of index property are you referring to?
There are several ways to disable AutoUpdate stats for an index or table
such as Update Stats with NoRecompute, Create Index with
STATISTICS_NORECOMPUTE etc but the most common is probably sp_autostats.
For more details check BOL under this topic "statistical information,
indexes"
Andrew J. Kelly SQL MVP
|||Yes indeed! Thanks for the heads up.
Gert-Jan
Alex wrote:
> but if you do dbcc dbreindex(Test2, '') instead the column statistics will
> be updated as well
>
<snip>
sql
My understanding is that once an index is rebuilt, there is no need to
run update statistics afterwards because that is automatically done. Is
this the case? For example, does this make any sense:
DBCC DBREINDEX('TableName')
EXEC ('UPDATE STATISTICS TableName')
Basically we have a vendor insisting that our performance problems with
their product are due to not updating statistics after a data load.
However, we are rebuilding the indexes after the load. Am I correct
here? Any links to MS documentation we could show to the vendor would
be a great help.
Thanks in advance.
If the indexes are being rebuilt the stats will be updated automatically
unless you have turned off AUTO UPDATE STATS or set a property of the index
to disallow the updates. As a matter of fact rebuilding with DBREINDEX will
do a FULL scan which gives the most accurate information of the breakdown.
Unless you specify a sample rate Update Stats will do a limited sample by
default. DBCC INDEXDEFRAG does not update the stats on it's own but
DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the DBREINDEX
to see for sure if they are getting updated. YOu can send the vendor the
results and tell them to take a hike<g>.
Andrew J. Kelly SQL MVP
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>
|||Hi SQLBoy
To my understanding DBCC REINDEX is equivalent to a DROP/CREATE index
statement. SQL does an update of the statistics after a CREATE INDEX
statement - unless you specify the STATISTCS_NORECOMPUTE option.
Does new data enter the table after you have loaded and rebuild the
indexes - in this case the statistics may slowly become out of date?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>
|||Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
updated during indexes' rebuild anyways.
And what's the name of index property are you referring to?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234S79xP8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> If the indexes are being rebuilt the stats will be updated automatically
> unless you have turned off AUTO UPDATE STATS or set a property of the
index
> to disallow the updates. As a matter of fact rebuilding with DBREINDEX
will
> do a FULL scan which gives the most accurate information of the breakdown.
> Unless you specify a sample rate Update Stats will do a limited sample by
> default. DBCC INDEXDEFRAG does not update the stats on it's own but
> DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the
DBREINDEX
> to see for sure if they are getting updated. YOu can send the vendor the
> results and tell them to take a hike<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:1104690140.021556.95790@.z14g2000cwz.googlegro ups.com...
>
|||As mentioned in the other posts: the statistics of the indexed columns
will be recalculated when reindexing.
However, only the index statistics will be updated. Any manually or
automatically created column statistics will not be updated. The example
below proves this behavior.
Hope this helps,
Gert-Jan
use northwind
go
select * into Test from orders
alter table Test add constraint PK_Test primary key clustered (OrderID)
select * into Test2 from "order details"
alter table Test2 add constraint PK_Test2 primary key clustered
(OrderID,ProductID)
go
-- used to display the autocreate stats on Test2
create procedure test_showstats as
begin
declare @.sql varchar(4000)
select @.sql='dbcc show_statistics (Test2,'+name+')'
from sysindexes
where id=object_id('Test2')
and name <> 'PK_Test2'
exec (@.sql)
end
go
-- this will auto create stats on Test2.UnitPrice if "autocreate stats"
is turned on
SELECT O.OrderID,CustomerID,Freight
FROM Test O
INNER JOIN Test2 OD
ON OD.OrderID=O.OrderID
WHERE CustomerID >= 'S'
AND UnitPrice >= 40.00
go
-- shows current stats: no rows with UnitPrice=270.00
exec test_showstats
go
insert into Test2 values (10248,15, 270.00 ,1,0.0)
insert into Test2 values (10248,16, 270.00 ,1,0.0)
insert into Test2 values (10248,17, 270.00 ,1,0.0)
insert into Test2 values (10248,18, 270.00 ,1,0.0)
insert into Test2 values (10248,19, 270.00 ,1,0.0)
insert into Test2 values (10248,20, 270.00 ,1,0.0)
insert into Test2 values (10248,21, 270.00 ,1,0.0)
insert into Test2 values (10248,22, 270.00 ,1,0.0)
insert into Test2 values (10248,23, 270.00 ,1,0.0)
insert into Test2 values (10248,24, 270.00 ,1,0.0)
go
dbcc dbreindex(Test2,PK_Test2)
go
-- will show the updated value of 13 rows for OrderID=10248
dbcc show_statistics(test2,pk_test2)
go
-- shows that the stats on column UnitPrice have not been updated
exec test_showstats
go
update statistics Test2
go
-- now the stats show 10 rows with UnitPrice=270.00
exec test_showstats
go
-- cleanup
drop table Test
drop table Test2
drop procedure test_showstats
sqlboy2000 wrote:
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
|||but if you do dbcc dbreindex(Test2, '') instead the column statistics will
be updated as well
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41D854D4.9B1CCF3E@.toomuchspamalready.nl...[vbcol=seagreen]
> As mentioned in the other posts: the statistics of the indexed columns
> will be recalculated when reindexing.
> However, only the index statistics will be updated. Any manually or
> automatically created column statistics will not be updated. The example
> below proves this behavior.
> Hope this helps,
> Gert-Jan
> use northwind
> go
> select * into Test from orders
> alter table Test add constraint PK_Test primary key clustered (OrderID)
> select * into Test2 from "order details"
> alter table Test2 add constraint PK_Test2 primary key clustered
> (OrderID,ProductID)
> go
> -- used to display the autocreate stats on Test2
> create procedure test_showstats as
> begin
> declare @.sql varchar(4000)
> select @.sql='dbcc show_statistics (Test2,'+name+')'
> from sysindexes
> where id=object_id('Test2')
> and name <> 'PK_Test2'
> exec (@.sql)
> end
> go
> -- this will auto create stats on Test2.UnitPrice if "autocreate stats"
> is turned on
> SELECT O.OrderID,CustomerID,Freight
> FROM Test O
> INNER JOIN Test2 OD
> ON OD.OrderID=O.OrderID
> WHERE CustomerID >= 'S'
> AND UnitPrice >= 40.00
> go
> -- shows current stats: no rows with UnitPrice=270.00
> exec test_showstats
> go
> insert into Test2 values (10248,15, 270.00 ,1,0.0)
> insert into Test2 values (10248,16, 270.00 ,1,0.0)
> insert into Test2 values (10248,17, 270.00 ,1,0.0)
> insert into Test2 values (10248,18, 270.00 ,1,0.0)
> insert into Test2 values (10248,19, 270.00 ,1,0.0)
> insert into Test2 values (10248,20, 270.00 ,1,0.0)
> insert into Test2 values (10248,21, 270.00 ,1,0.0)
> insert into Test2 values (10248,22, 270.00 ,1,0.0)
> insert into Test2 values (10248,23, 270.00 ,1,0.0)
> insert into Test2 values (10248,24, 270.00 ,1,0.0)
> go
> dbcc dbreindex(Test2,PK_Test2)
> go
> -- will show the updated value of 13 rows for OrderID=10248
> dbcc show_statistics(test2,pk_test2)
> go
> -- shows that the stats on column UnitPrice have not been updated
> exec test_showstats
> go
> update statistics Test2
> go
> -- now the stats show 10 rows with UnitPrice=270.00
> exec test_showstats
> go
> -- cleanup
> drop table Test
> drop table Test2
> drop procedure test_showstats
>
> sqlboy2000 wrote:
|||> Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
> updated during indexes' rebuild anyways.
Sorry I was thinking ahead of myself.
> And what's the name of index property are you referring to?
There are several ways to disable AutoUpdate stats for an index or table
such as Update Stats with NoRecompute, Create Index with
STATISTICS_NORECOMPUTE etc but the most common is probably sp_autostats.
For more details check BOL under this topic "statistical information,
indexes"
Andrew J. Kelly SQL MVP
|||Yes indeed! Thanks for the heads up.
Gert-Jan
Alex wrote:
> but if you do dbcc dbreindex(Test2, '') instead the column statistics will
> be updated as well
>
<snip>
sql
Rebuilding an index and update statistics
Hi all,
My understanding is that once an index is rebuilt, there is no need to
run update statistics afterwards because that is automatically done. Is
this the case? For example, does this make any sense:
DBCC DBREINDEX('TableName')
EXEC ('UPDATE STATISTICS TableName')
Basically we have a vendor insisting that our performance problems with
their product are due to not updating statistics after a data load.
However, we are rebuilding the indexes after the load. Am I correct
here? Any links to MS documentation we could show to the vendor would
be a great help.
Thanks in advance.If the indexes are being rebuilt the stats will be updated automatically
unless you have turned off AUTO UPDATE STATS or set a property of the index
to disallow the updates. As a matter of fact rebuilding with DBREINDEX will
do a FULL scan which gives the most accurate information of the breakdown.
Unless you specify a sample rate Update Stats will do a limited sample by
default. DBCC INDEXDEFRAG does not update the stats on it's own but
DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the DBREINDEX
to see for sure if they are getting updated. YOu can send the vendor the
results and tell them to take a hike<g>.
Andrew J. Kelly SQL MVP
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>|||Hi SQLBoy
To my understanding DBCC REINDEX is equivalent to a DROP/CREATE index
statement. SQL does an update of the statistics after a CREATE INDEX
statement - unless you specify the STATISTCS_NORECOMPUTE option.
Does new data enter the table after you have loaded and rebuild the
indexes - in this case the statistics may slowly become out of date?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>|||Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
updated during indexes' rebuild anyways.
And what's the name of index property are you referring to?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234S79xP8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> If the indexes are being rebuilt the stats will be updated automatically
> unless you have turned off AUTO UPDATE STATS or set a property of the
index
> to disallow the updates. As a matter of fact rebuilding with DBREINDEX
will
> do a FULL scan which gives the most accurate information of the breakdown.
> Unless you specify a sample rate Update Stats will do a limited sample by
> default. DBCC INDEXDEFRAG does not update the stats on it's own but
> DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the
DBREINDEX
> to see for sure if they are getting updated. YOu can send the vendor the
> results and tell them to take a hike<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> > Hi all,
> > My understanding is that once an index is rebuilt, there is no need to
> > run update statistics afterwards because that is automatically done. Is
> > this the case? For example, does this make any sense:
> >
> > DBCC DBREINDEX('TableName')
> > EXEC ('UPDATE STATISTICS TableName')
> >
> > Basically we have a vendor insisting that our performance problems with
> > their product are due to not updating statistics after a data load.
> > However, we are rebuilding the indexes after the load. Am I correct
> > here? Any links to MS documentation we could show to the vendor would
> > be a great help.
> >
> > Thanks in advance.
> >
>|||As mentioned in the other posts: the statistics of the indexed columns
will be recalculated when reindexing.
However, only the index statistics will be updated. Any manually or
automatically created column statistics will not be updated. The example
below proves this behavior.
Hope this helps,
Gert-Jan
use northwind
go
select * into Test from orders
alter table Test add constraint PK_Test primary key clustered (OrderID)
select * into Test2 from "order details"
alter table Test2 add constraint PK_Test2 primary key clustered
(OrderID,ProductID)
go
-- used to display the autocreate stats on Test2
create procedure test_showstats as
begin
declare @.sql varchar(4000)
select @.sql='dbcc show_statistics (Test2,'+name+')'
from sysindexes
where id=object_id('Test2')
and name <> 'PK_Test2'
exec (@.sql)
end
go
-- this will auto create stats on Test2.UnitPrice if "autocreate stats"
is turned on
SELECT O.OrderID,CustomerID,Freight
FROM Test O
INNER JOIN Test2 OD
ON OD.OrderID=O.OrderID
WHERE CustomerID >= 'S'
AND UnitPrice >= 40.00
go
-- shows current stats: no rows with UnitPrice=270.00
exec test_showstats
go
insert into Test2 values (10248,15, 270.00 ,1,0.0)
insert into Test2 values (10248,16, 270.00 ,1,0.0)
insert into Test2 values (10248,17, 270.00 ,1,0.0)
insert into Test2 values (10248,18, 270.00 ,1,0.0)
insert into Test2 values (10248,19, 270.00 ,1,0.0)
insert into Test2 values (10248,20, 270.00 ,1,0.0)
insert into Test2 values (10248,21, 270.00 ,1,0.0)
insert into Test2 values (10248,22, 270.00 ,1,0.0)
insert into Test2 values (10248,23, 270.00 ,1,0.0)
insert into Test2 values (10248,24, 270.00 ,1,0.0)
go
dbcc dbreindex(Test2,PK_Test2)
go
-- will show the updated value of 13 rows for OrderID=10248
dbcc show_statistics(test2,pk_test2)
go
-- shows that the stats on column UnitPrice have not been updated
exec test_showstats
go
update statistics Test2
go
-- now the stats show 10 rows with UnitPrice=270.00
exec test_showstats
go
-- cleanup
drop table Test
drop table Test2
drop procedure test_showstats
sqlboy2000 wrote:
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.|||but if you do dbcc dbreindex(Test2, '') instead the column statistics will
be updated as well
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41D854D4.9B1CCF3E@.toomuchspamalready.nl...
> As mentioned in the other posts: the statistics of the indexed columns
> will be recalculated when reindexing.
> However, only the index statistics will be updated. Any manually or
> automatically created column statistics will not be updated. The example
> below proves this behavior.
> Hope this helps,
> Gert-Jan
> use northwind
> go
> select * into Test from orders
> alter table Test add constraint PK_Test primary key clustered (OrderID)
> select * into Test2 from "order details"
> alter table Test2 add constraint PK_Test2 primary key clustered
> (OrderID,ProductID)
> go
> -- used to display the autocreate stats on Test2
> create procedure test_showstats as
> begin
> declare @.sql varchar(4000)
> select @.sql='dbcc show_statistics (Test2,'+name+')'
> from sysindexes
> where id=object_id('Test2')
> and name <> 'PK_Test2'
> exec (@.sql)
> end
> go
> -- this will auto create stats on Test2.UnitPrice if "autocreate stats"
> is turned on
> SELECT O.OrderID,CustomerID,Freight
> FROM Test O
> INNER JOIN Test2 OD
> ON OD.OrderID=O.OrderID
> WHERE CustomerID >= 'S'
> AND UnitPrice >= 40.00
> go
> -- shows current stats: no rows with UnitPrice=270.00
> exec test_showstats
> go
> insert into Test2 values (10248,15, 270.00 ,1,0.0)
> insert into Test2 values (10248,16, 270.00 ,1,0.0)
> insert into Test2 values (10248,17, 270.00 ,1,0.0)
> insert into Test2 values (10248,18, 270.00 ,1,0.0)
> insert into Test2 values (10248,19, 270.00 ,1,0.0)
> insert into Test2 values (10248,20, 270.00 ,1,0.0)
> insert into Test2 values (10248,21, 270.00 ,1,0.0)
> insert into Test2 values (10248,22, 270.00 ,1,0.0)
> insert into Test2 values (10248,23, 270.00 ,1,0.0)
> insert into Test2 values (10248,24, 270.00 ,1,0.0)
> go
> dbcc dbreindex(Test2,PK_Test2)
> go
> -- will show the updated value of 13 rows for OrderID=10248
> dbcc show_statistics(test2,pk_test2)
> go
> -- shows that the stats on column UnitPrice have not been updated
> exec test_showstats
> go
> update statistics Test2
> go
> -- now the stats show 10 rows with UnitPrice=270.00
> exec test_showstats
> go
> -- cleanup
> drop table Test
> drop table Test2
> drop procedure test_showstats
>
> sqlboy2000 wrote:
> >
> > Hi all,
> > My understanding is that once an index is rebuilt, there is no need to
> > run update statistics afterwards because that is automatically done. Is
> > this the case? For example, does this make any sense:
> >
> > DBCC DBREINDEX('TableName')
> > EXEC ('UPDATE STATISTICS TableName')
> >
> > Basically we have a vendor insisting that our performance problems with
> > their product are due to not updating statistics after a data load.
> > However, we are rebuilding the indexes after the load. Am I correct
> > here? Any links to MS documentation we could show to the vendor would
> > be a great help.
> >
> > Thanks in advance.|||> Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
> updated during indexes' rebuild anyways.
Sorry I was thinking ahead of myself.
> And what's the name of index property are you referring to?
There are several ways to disable AutoUpdate stats for an index or table
such as Update Stats with NoRecompute, Create Index with
STATISTICS_NORECOMPUTE etc but the most common is probably sp_autostats.
For more details check BOL under this topic "statistical information,
indexes"
Andrew J. Kelly SQL MVP|||Yes indeed! Thanks for the heads up.
Gert-Jan
Alex wrote:
> but if you do dbcc dbreindex(Test2, '') instead the column statistics will
> be updated as well
>
<snip>
My understanding is that once an index is rebuilt, there is no need to
run update statistics afterwards because that is automatically done. Is
this the case? For example, does this make any sense:
DBCC DBREINDEX('TableName')
EXEC ('UPDATE STATISTICS TableName')
Basically we have a vendor insisting that our performance problems with
their product are due to not updating statistics after a data load.
However, we are rebuilding the indexes after the load. Am I correct
here? Any links to MS documentation we could show to the vendor would
be a great help.
Thanks in advance.If the indexes are being rebuilt the stats will be updated automatically
unless you have turned off AUTO UPDATE STATS or set a property of the index
to disallow the updates. As a matter of fact rebuilding with DBREINDEX will
do a FULL scan which gives the most accurate information of the breakdown.
Unless you specify a sample rate Update Stats will do a limited sample by
default. DBCC INDEXDEFRAG does not update the stats on it's own but
DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the DBREINDEX
to see for sure if they are getting updated. YOu can send the vendor the
results and tell them to take a hike<g>.
Andrew J. Kelly SQL MVP
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>|||Hi SQLBoy
To my understanding DBCC REINDEX is equivalent to a DROP/CREATE index
statement. SQL does an update of the statistics after a CREATE INDEX
statement - unless you specify the STATISTCS_NORECOMPUTE option.
Does new data enter the table after you have loaded and rebuild the
indexes - in this case the statistics may slowly become out of date?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>|||Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
updated during indexes' rebuild anyways.
And what's the name of index property are you referring to?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234S79xP8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> If the indexes are being rebuilt the stats will be updated automatically
> unless you have turned off AUTO UPDATE STATS or set a property of the
index
> to disallow the updates. As a matter of fact rebuilding with DBREINDEX
will
> do a FULL scan which gives the most accurate information of the breakdown.
> Unless you specify a sample rate Update Stats will do a limited sample by
> default. DBCC INDEXDEFRAG does not update the stats on it's own but
> DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the
DBREINDEX
> to see for sure if they are getting updated. YOu can send the vendor the
> results and tell them to take a hike<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> > Hi all,
> > My understanding is that once an index is rebuilt, there is no need to
> > run update statistics afterwards because that is automatically done. Is
> > this the case? For example, does this make any sense:
> >
> > DBCC DBREINDEX('TableName')
> > EXEC ('UPDATE STATISTICS TableName')
> >
> > Basically we have a vendor insisting that our performance problems with
> > their product are due to not updating statistics after a data load.
> > However, we are rebuilding the indexes after the load. Am I correct
> > here? Any links to MS documentation we could show to the vendor would
> > be a great help.
> >
> > Thanks in advance.
> >
>|||As mentioned in the other posts: the statistics of the indexed columns
will be recalculated when reindexing.
However, only the index statistics will be updated. Any manually or
automatically created column statistics will not be updated. The example
below proves this behavior.
Hope this helps,
Gert-Jan
use northwind
go
select * into Test from orders
alter table Test add constraint PK_Test primary key clustered (OrderID)
select * into Test2 from "order details"
alter table Test2 add constraint PK_Test2 primary key clustered
(OrderID,ProductID)
go
-- used to display the autocreate stats on Test2
create procedure test_showstats as
begin
declare @.sql varchar(4000)
select @.sql='dbcc show_statistics (Test2,'+name+')'
from sysindexes
where id=object_id('Test2')
and name <> 'PK_Test2'
exec (@.sql)
end
go
-- this will auto create stats on Test2.UnitPrice if "autocreate stats"
is turned on
SELECT O.OrderID,CustomerID,Freight
FROM Test O
INNER JOIN Test2 OD
ON OD.OrderID=O.OrderID
WHERE CustomerID >= 'S'
AND UnitPrice >= 40.00
go
-- shows current stats: no rows with UnitPrice=270.00
exec test_showstats
go
insert into Test2 values (10248,15, 270.00 ,1,0.0)
insert into Test2 values (10248,16, 270.00 ,1,0.0)
insert into Test2 values (10248,17, 270.00 ,1,0.0)
insert into Test2 values (10248,18, 270.00 ,1,0.0)
insert into Test2 values (10248,19, 270.00 ,1,0.0)
insert into Test2 values (10248,20, 270.00 ,1,0.0)
insert into Test2 values (10248,21, 270.00 ,1,0.0)
insert into Test2 values (10248,22, 270.00 ,1,0.0)
insert into Test2 values (10248,23, 270.00 ,1,0.0)
insert into Test2 values (10248,24, 270.00 ,1,0.0)
go
dbcc dbreindex(Test2,PK_Test2)
go
-- will show the updated value of 13 rows for OrderID=10248
dbcc show_statistics(test2,pk_test2)
go
-- shows that the stats on column UnitPrice have not been updated
exec test_showstats
go
update statistics Test2
go
-- now the stats show 10 rows with UnitPrice=270.00
exec test_showstats
go
-- cleanup
drop table Test
drop table Test2
drop procedure test_showstats
sqlboy2000 wrote:
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.|||but if you do dbcc dbreindex(Test2, '') instead the column statistics will
be updated as well
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41D854D4.9B1CCF3E@.toomuchspamalready.nl...
> As mentioned in the other posts: the statistics of the indexed columns
> will be recalculated when reindexing.
> However, only the index statistics will be updated. Any manually or
> automatically created column statistics will not be updated. The example
> below proves this behavior.
> Hope this helps,
> Gert-Jan
> use northwind
> go
> select * into Test from orders
> alter table Test add constraint PK_Test primary key clustered (OrderID)
> select * into Test2 from "order details"
> alter table Test2 add constraint PK_Test2 primary key clustered
> (OrderID,ProductID)
> go
> -- used to display the autocreate stats on Test2
> create procedure test_showstats as
> begin
> declare @.sql varchar(4000)
> select @.sql='dbcc show_statistics (Test2,'+name+')'
> from sysindexes
> where id=object_id('Test2')
> and name <> 'PK_Test2'
> exec (@.sql)
> end
> go
> -- this will auto create stats on Test2.UnitPrice if "autocreate stats"
> is turned on
> SELECT O.OrderID,CustomerID,Freight
> FROM Test O
> INNER JOIN Test2 OD
> ON OD.OrderID=O.OrderID
> WHERE CustomerID >= 'S'
> AND UnitPrice >= 40.00
> go
> -- shows current stats: no rows with UnitPrice=270.00
> exec test_showstats
> go
> insert into Test2 values (10248,15, 270.00 ,1,0.0)
> insert into Test2 values (10248,16, 270.00 ,1,0.0)
> insert into Test2 values (10248,17, 270.00 ,1,0.0)
> insert into Test2 values (10248,18, 270.00 ,1,0.0)
> insert into Test2 values (10248,19, 270.00 ,1,0.0)
> insert into Test2 values (10248,20, 270.00 ,1,0.0)
> insert into Test2 values (10248,21, 270.00 ,1,0.0)
> insert into Test2 values (10248,22, 270.00 ,1,0.0)
> insert into Test2 values (10248,23, 270.00 ,1,0.0)
> insert into Test2 values (10248,24, 270.00 ,1,0.0)
> go
> dbcc dbreindex(Test2,PK_Test2)
> go
> -- will show the updated value of 13 rows for OrderID=10248
> dbcc show_statistics(test2,pk_test2)
> go
> -- shows that the stats on column UnitPrice have not been updated
> exec test_showstats
> go
> update statistics Test2
> go
> -- now the stats show 10 rows with UnitPrice=270.00
> exec test_showstats
> go
> -- cleanup
> drop table Test
> drop table Test2
> drop procedure test_showstats
>
> sqlboy2000 wrote:
> >
> > Hi all,
> > My understanding is that once an index is rebuilt, there is no need to
> > run update statistics afterwards because that is automatically done. Is
> > this the case? For example, does this make any sense:
> >
> > DBCC DBREINDEX('TableName')
> > EXEC ('UPDATE STATISTICS TableName')
> >
> > Basically we have a vendor insisting that our performance problems with
> > their product are due to not updating statistics after a data load.
> > However, we are rebuilding the indexes after the load. Am I correct
> > here? Any links to MS documentation we could show to the vendor would
> > be a great help.
> >
> > Thanks in advance.|||> Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
> updated during indexes' rebuild anyways.
Sorry I was thinking ahead of myself.
> And what's the name of index property are you referring to?
There are several ways to disable AutoUpdate stats for an index or table
such as Update Stats with NoRecompute, Create Index with
STATISTICS_NORECOMPUTE etc but the most common is probably sp_autostats.
For more details check BOL under this topic "statistical information,
indexes"
Andrew J. Kelly SQL MVP|||Yes indeed! Thanks for the heads up.
Gert-Jan
Alex wrote:
> but if you do dbcc dbreindex(Test2, '') instead the column statistics will
> be updated as well
>
<snip>
Rebuilding an index and update statistics
Hi all,
My understanding is that once an index is rebuilt, there is no need to
run update statistics afterwards because that is automatically done. Is
this the case? For example, does this make any sense:
DBCC DBREINDEX('TableName')
EXEC ('UPDATE STATISTICS TableName')
Basically we have a vendor insisting that our performance problems with
their product are due to not updating statistics after a data load.
However, we are rebuilding the indexes after the load. Am I correct
here? Any links to MS documentation we could show to the vendor would
be a great help.
Thanks in advance.If the indexes are being rebuilt the stats will be updated automatically
unless you have turned off AUTO UPDATE STATS or set a property of the index
to disallow the updates. As a matter of fact rebuilding with DBREINDEX will
do a FULL scan which gives the most accurate information of the breakdown.
Unless you specify a sample rate Update Stats will do a limited sample by
default. DBCC INDEXDEFRAG does not update the stats on it's own but
DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the DBREINDEX
to see for sure if they are getting updated. YOu can send the vendor the
results and tell them to take a hike<g>.
Andrew J. Kelly SQL MVP
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>|||Hi SQLBoy
To my understanding DBCC REINDEX is equivalent to a DROP/CREATE index
statement. SQL does an update of the statistics after a CREATE INDEX
statement - unless you specify the STATISTCS_NORECOMPUTE option.
Does new data enter the table after you have loaded and rebuild the
indexes - in this case the statistics may slowly become out of date?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>|||Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
updated during indexes' rebuild anyways.
And what's the name of index property are you referring to?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234S79xP8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> If the indexes are being rebuilt the stats will be updated automatically
> unless you have turned off AUTO UPDATE STATS or set a property of the
index
> to disallow the updates. As a matter of fact rebuilding with DBREINDEX
will
> do a FULL scan which gives the most accurate information of the breakdown.
> Unless you specify a sample rate Update Stats will do a limited sample by
> default. DBCC INDEXDEFRAG does not update the stats on it's own but
> DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the
DBREINDEX
> to see for sure if they are getting updated. YOu can send the vendor the
> results and tell them to take a hike<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
>|||As mentioned in the other posts: the statistics of the indexed columns
will be recalculated when reindexing.
However, only the index statistics will be updated. Any manually or
automatically created column statistics will not be updated. The example
below proves this behavior.
Hope this helps,
Gert-Jan
use northwind
go
select * into Test from orders
alter table Test add constraint PK_Test primary key clustered (OrderID)
select * into Test2 from "order details"
alter table Test2 add constraint PK_Test2 primary key clustered
(OrderID,ProductID)
go
-- used to display the autocreate stats on Test2
create procedure test_showstats as
begin
declare @.sql varchar(4000)
select @.sql='dbcc show_statistics (Test2,'+name+')'
from sysindexes
where id=object_id('Test2')
and name <> 'PK_Test2'
exec (@.sql)
end
go
-- this will auto create stats on Test2.UnitPrice if "autocreate stats"
is turned on
SELECT O.OrderID,CustomerID,Freight
FROM Test O
INNER JOIN Test2 OD
ON OD.OrderID=O.OrderID
WHERE CustomerID >= 'S'
AND UnitPrice >= 40.00
go
-- shows current stats: no rows with UnitPrice=270.00
exec test_showstats
go
insert into Test2 values (10248,15, 270.00 ,1,0.0)
insert into Test2 values (10248,16, 270.00 ,1,0.0)
insert into Test2 values (10248,17, 270.00 ,1,0.0)
insert into Test2 values (10248,18, 270.00 ,1,0.0)
insert into Test2 values (10248,19, 270.00 ,1,0.0)
insert into Test2 values (10248,20, 270.00 ,1,0.0)
insert into Test2 values (10248,21, 270.00 ,1,0.0)
insert into Test2 values (10248,22, 270.00 ,1,0.0)
insert into Test2 values (10248,23, 270.00 ,1,0.0)
insert into Test2 values (10248,24, 270.00 ,1,0.0)
go
dbcc dbreindex(Test2,PK_Test2)
go
-- will show the updated value of 13 rows for OrderID=10248
dbcc show_statistics(test2,pk_test2)
go
-- shows that the stats on column UnitPrice have not been updated
exec test_showstats
go
update statistics Test2
go
-- now the stats show 10 rows with UnitPrice=270.00
exec test_showstats
go
-- cleanup
drop table Test
drop table Test2
drop procedure test_showstats
sqlboy2000 wrote:
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.|||but if you do dbcc dbreindex(Test2, '') instead the column statistics will
be updated as well
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41D854D4.9B1CCF3E@.toomuchspamalready.nl...[vbcol=seagreen]
> As mentioned in the other posts: the statistics of the indexed columns
> will be recalculated when reindexing.
> However, only the index statistics will be updated. Any manually or
> automatically created column statistics will not be updated. The example
> below proves this behavior.
> Hope this helps,
> Gert-Jan
> use northwind
> go
> select * into Test from orders
> alter table Test add constraint PK_Test primary key clustered (OrderID)
> select * into Test2 from "order details"
> alter table Test2 add constraint PK_Test2 primary key clustered
> (OrderID,ProductID)
> go
> -- used to display the autocreate stats on Test2
> create procedure test_showstats as
> begin
> declare @.sql varchar(4000)
> select @.sql='dbcc show_statistics (Test2,'+name+')'
> from sysindexes
> where id=object_id('Test2')
> and name <> 'PK_Test2'
> exec (@.sql)
> end
> go
> -- this will auto create stats on Test2.UnitPrice if "autocreate stats"
> is turned on
> SELECT O.OrderID,CustomerID,Freight
> FROM Test O
> INNER JOIN Test2 OD
> ON OD.OrderID=O.OrderID
> WHERE CustomerID >= 'S'
> AND UnitPrice >= 40.00
> go
> -- shows current stats: no rows with UnitPrice=270.00
> exec test_showstats
> go
> insert into Test2 values (10248,15, 270.00 ,1,0.0)
> insert into Test2 values (10248,16, 270.00 ,1,0.0)
> insert into Test2 values (10248,17, 270.00 ,1,0.0)
> insert into Test2 values (10248,18, 270.00 ,1,0.0)
> insert into Test2 values (10248,19, 270.00 ,1,0.0)
> insert into Test2 values (10248,20, 270.00 ,1,0.0)
> insert into Test2 values (10248,21, 270.00 ,1,0.0)
> insert into Test2 values (10248,22, 270.00 ,1,0.0)
> insert into Test2 values (10248,23, 270.00 ,1,0.0)
> insert into Test2 values (10248,24, 270.00 ,1,0.0)
> go
> dbcc dbreindex(Test2,PK_Test2)
> go
> -- will show the updated value of 13 rows for OrderID=10248
> dbcc show_statistics(test2,pk_test2)
> go
> -- shows that the stats on column UnitPrice have not been updated
> exec test_showstats
> go
> update statistics Test2
> go
> -- now the stats show 10 rows with UnitPrice=270.00
> exec test_showstats
> go
> -- cleanup
> drop table Test
> drop table Test2
> drop procedure test_showstats
>
> sqlboy2000 wrote:|||> Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
> updated during indexes' rebuild anyways.
Sorry I was thinking ahead of myself.
> And what's the name of index property are you referring to?
There are several ways to disable AutoUpdate stats for an index or table
such as Update Stats with NoRecompute, Create Index with
STATISTICS_NORECOMPUTE etc but the most common is probably sp_autostats.
For more details check BOL under this topic "statistical information,
indexes"
Andrew J. Kelly SQL MVP|||Yes indeed! Thanks for the heads up.
Gert-Jan
Alex wrote:
> but if you do dbcc dbreindex(Test2, '') instead the column statistics will
> be updated as well
>
<snip>
My understanding is that once an index is rebuilt, there is no need to
run update statistics afterwards because that is automatically done. Is
this the case? For example, does this make any sense:
DBCC DBREINDEX('TableName')
EXEC ('UPDATE STATISTICS TableName')
Basically we have a vendor insisting that our performance problems with
their product are due to not updating statistics after a data load.
However, we are rebuilding the indexes after the load. Am I correct
here? Any links to MS documentation we could show to the vendor would
be a great help.
Thanks in advance.If the indexes are being rebuilt the stats will be updated automatically
unless you have turned off AUTO UPDATE STATS or set a property of the index
to disallow the updates. As a matter of fact rebuilding with DBREINDEX will
do a FULL scan which gives the most accurate information of the breakdown.
Unless you specify a sample rate Update Stats will do a limited sample by
default. DBCC INDEXDEFRAG does not update the stats on it's own but
DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the DBREINDEX
to see for sure if they are getting updated. YOu can send the vendor the
results and tell them to take a hike<g>.
Andrew J. Kelly SQL MVP
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>|||Hi SQLBoy
To my understanding DBCC REINDEX is equivalent to a DROP/CREATE index
statement. SQL does an update of the statistics after a CREATE INDEX
statement - unless you specify the STATISTCS_NORECOMPUTE option.
Does new data enter the table after you have loaded and rebuild the
indexes - in this case the statistics may slowly become out of date?
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.
>|||Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
updated during indexes' rebuild anyways.
And what's the name of index property are you referring to?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%234S79xP8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> If the indexes are being rebuilt the stats will be updated automatically
> unless you have turned off AUTO UPDATE STATS or set a property of the
index
> to disallow the updates. As a matter of fact rebuilding with DBREINDEX
will
> do a FULL scan which gives the most accurate information of the breakdown.
> Unless you specify a sample rate Update Stats will do a limited sample by
> default. DBCC INDEXDEFRAG does not update the stats on it's own but
> DBREINDEX will. You can always run DBCC SHOW_STATISTICS after the
DBREINDEX
> to see for sure if they are getting updated. YOu can send the vendor the
> results and tell them to take a hike<g>.
>
> --
> Andrew J. Kelly SQL MVP
>
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:1104690140.021556.95790@.z14g2000cwz.googlegroups.com...
>|||As mentioned in the other posts: the statistics of the indexed columns
will be recalculated when reindexing.
However, only the index statistics will be updated. Any manually or
automatically created column statistics will not be updated. The example
below proves this behavior.
Hope this helps,
Gert-Jan
use northwind
go
select * into Test from orders
alter table Test add constraint PK_Test primary key clustered (OrderID)
select * into Test2 from "order details"
alter table Test2 add constraint PK_Test2 primary key clustered
(OrderID,ProductID)
go
-- used to display the autocreate stats on Test2
create procedure test_showstats as
begin
declare @.sql varchar(4000)
select @.sql='dbcc show_statistics (Test2,'+name+')'
from sysindexes
where id=object_id('Test2')
and name <> 'PK_Test2'
exec (@.sql)
end
go
-- this will auto create stats on Test2.UnitPrice if "autocreate stats"
is turned on
SELECT O.OrderID,CustomerID,Freight
FROM Test O
INNER JOIN Test2 OD
ON OD.OrderID=O.OrderID
WHERE CustomerID >= 'S'
AND UnitPrice >= 40.00
go
-- shows current stats: no rows with UnitPrice=270.00
exec test_showstats
go
insert into Test2 values (10248,15, 270.00 ,1,0.0)
insert into Test2 values (10248,16, 270.00 ,1,0.0)
insert into Test2 values (10248,17, 270.00 ,1,0.0)
insert into Test2 values (10248,18, 270.00 ,1,0.0)
insert into Test2 values (10248,19, 270.00 ,1,0.0)
insert into Test2 values (10248,20, 270.00 ,1,0.0)
insert into Test2 values (10248,21, 270.00 ,1,0.0)
insert into Test2 values (10248,22, 270.00 ,1,0.0)
insert into Test2 values (10248,23, 270.00 ,1,0.0)
insert into Test2 values (10248,24, 270.00 ,1,0.0)
go
dbcc dbreindex(Test2,PK_Test2)
go
-- will show the updated value of 13 rows for OrderID=10248
dbcc show_statistics(test2,pk_test2)
go
-- shows that the stats on column UnitPrice have not been updated
exec test_showstats
go
update statistics Test2
go
-- now the stats show 10 rows with UnitPrice=270.00
exec test_showstats
go
-- cleanup
drop table Test
drop table Test2
drop procedure test_showstats
sqlboy2000 wrote:
> Hi all,
> My understanding is that once an index is rebuilt, there is no need to
> run update statistics afterwards because that is automatically done. Is
> this the case? For example, does this make any sense:
> DBCC DBREINDEX('TableName')
> EXEC ('UPDATE STATISTICS TableName')
> Basically we have a vendor insisting that our performance problems with
> their product are due to not updating statistics after a data load.
> However, we are rebuilding the indexes after the load. Am I correct
> here? Any links to MS documentation we could show to the vendor would
> be a great help.
> Thanks in advance.|||but if you do dbcc dbreindex(Test2, '') instead the column statistics will
be updated as well
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41D854D4.9B1CCF3E@.toomuchspamalready.nl...[vbcol=seagreen]
> As mentioned in the other posts: the statistics of the indexed columns
> will be recalculated when reindexing.
> However, only the index statistics will be updated. Any manually or
> automatically created column statistics will not be updated. The example
> below proves this behavior.
> Hope this helps,
> Gert-Jan
> use northwind
> go
> select * into Test from orders
> alter table Test add constraint PK_Test primary key clustered (OrderID)
> select * into Test2 from "order details"
> alter table Test2 add constraint PK_Test2 primary key clustered
> (OrderID,ProductID)
> go
> -- used to display the autocreate stats on Test2
> create procedure test_showstats as
> begin
> declare @.sql varchar(4000)
> select @.sql='dbcc show_statistics (Test2,'+name+')'
> from sysindexes
> where id=object_id('Test2')
> and name <> 'PK_Test2'
> exec (@.sql)
> end
> go
> -- this will auto create stats on Test2.UnitPrice if "autocreate stats"
> is turned on
> SELECT O.OrderID,CustomerID,Freight
> FROM Test O
> INNER JOIN Test2 OD
> ON OD.OrderID=O.OrderID
> WHERE CustomerID >= 'S'
> AND UnitPrice >= 40.00
> go
> -- shows current stats: no rows with UnitPrice=270.00
> exec test_showstats
> go
> insert into Test2 values (10248,15, 270.00 ,1,0.0)
> insert into Test2 values (10248,16, 270.00 ,1,0.0)
> insert into Test2 values (10248,17, 270.00 ,1,0.0)
> insert into Test2 values (10248,18, 270.00 ,1,0.0)
> insert into Test2 values (10248,19, 270.00 ,1,0.0)
> insert into Test2 values (10248,20, 270.00 ,1,0.0)
> insert into Test2 values (10248,21, 270.00 ,1,0.0)
> insert into Test2 values (10248,22, 270.00 ,1,0.0)
> insert into Test2 values (10248,23, 270.00 ,1,0.0)
> insert into Test2 values (10248,24, 270.00 ,1,0.0)
> go
> dbcc dbreindex(Test2,PK_Test2)
> go
> -- will show the updated value of 13 rows for OrderID=10248
> dbcc show_statistics(test2,pk_test2)
> go
> -- shows that the stats on column UnitPrice have not been updated
> exec test_showstats
> go
> update statistics Test2
> go
> -- now the stats show 10 rows with UnitPrice=270.00
> exec test_showstats
> go
> -- cleanup
> drop table Test
> drop table Test2
> drop procedure test_showstats
>
> sqlboy2000 wrote:|||> Andrew, but even if AUTO UPDATE STATS is OFF on DB the statistics will be
> updated during indexes' rebuild anyways.
Sorry I was thinking ahead of myself.
> And what's the name of index property are you referring to?
There are several ways to disable AutoUpdate stats for an index or table
such as Update Stats with NoRecompute, Create Index with
STATISTICS_NORECOMPUTE etc but the most common is probably sp_autostats.
For more details check BOL under this topic "statistical information,
indexes"
Andrew J. Kelly SQL MVP|||Yes indeed! Thanks for the heads up.
Gert-Jan
Alex wrote:
> but if you do dbcc dbreindex(Test2, '') instead the column statistics will
> be updated as well
>
<snip>
Wednesday, March 7, 2012
Realtime/streaming data
Is there a way to update data on a reporting services report without having
the user click the refresh button? I am wanting to show realtime stock
quotes in a reporting services report and update stock quotes as they come in
without having to call the refrsh method in the report viewer or having the
user have to click the refrsh button. I am using the report viewer component
in a C# winforms app.
Thanks in advance!!!Use the timer object in the C# winform and refresh the control. You could
have it refresh every minute or 30 sec - but anything more 'real time' than
that - Reporting Services is probably not a real good solution.
"David" <David@.discussions.microsoft.com> wrote in message
news:A05DA637-32B3-48CE-8972-14135DE7888B@.microsoft.com...
> Is there a way to update data on a reporting services report without
> having
> the user click the refresh button? I am wanting to show realtime stock
> quotes in a reporting services report and update stock quotes as they come
> in
> without having to call the refrsh method in the report viewer or having
> the
> user have to click the refrsh button. I am using the report viewer
> component
> in a C# winforms app.
> Thanks in advance!!!|||If you're using RS 2005 you can set the "Autorefresh" for the report
property to the desired refresh interval. It worked in VS preview and
in the the web browser. So I wouild think if you are using the report
viewer control the autorefresh would work there to.
On Sat, 13 May 2006 15:50:01 -0700, David
<David@.discussions.microsoft.com> wrote:
>Is there a way to update data on a reporting services report without having
>the user click the refresh button? I am wanting to show realtime stock
>quotes in a reporting services report and update stock quotes as they come in
>without having to call the refrsh method in the report viewer or having the
>user have to click the refrsh button. I am using the report viewer component
>in a C# winforms app.
>Thanks in advance!!!|||Thanks for trying guys. As I said in my initial email I want to update
values on the report without the report refreshing as a refresh is annoying
to the user and takes you back to the beginning of a report given you are in
backend pages of the report...
"Mark" wrote:
> If you're using RS 2005 you can set the "Autorefresh" for the report
> property to the desired refresh interval. It worked in VS preview and
> in the the web browser. So I wouild think if you are using the report
> viewer control the autorefresh would work there to.
> On Sat, 13 May 2006 15:50:01 -0700, David
> <David@.discussions.microsoft.com> wrote:
> >Is there a way to update data on a reporting services report without having
> >the user click the refresh button? I am wanting to show realtime stock
> >quotes in a reporting services report and update stock quotes as they come in
> >without having to call the refrsh method in the report viewer or having the
> >user have to click the refrsh button. I am using the report viewer component
> >in a C# winforms app.
> >Thanks in advance!!!
>
the user click the refresh button? I am wanting to show realtime stock
quotes in a reporting services report and update stock quotes as they come in
without having to call the refrsh method in the report viewer or having the
user have to click the refrsh button. I am using the report viewer component
in a C# winforms app.
Thanks in advance!!!Use the timer object in the C# winform and refresh the control. You could
have it refresh every minute or 30 sec - but anything more 'real time' than
that - Reporting Services is probably not a real good solution.
"David" <David@.discussions.microsoft.com> wrote in message
news:A05DA637-32B3-48CE-8972-14135DE7888B@.microsoft.com...
> Is there a way to update data on a reporting services report without
> having
> the user click the refresh button? I am wanting to show realtime stock
> quotes in a reporting services report and update stock quotes as they come
> in
> without having to call the refrsh method in the report viewer or having
> the
> user have to click the refrsh button. I am using the report viewer
> component
> in a C# winforms app.
> Thanks in advance!!!|||If you're using RS 2005 you can set the "Autorefresh" for the report
property to the desired refresh interval. It worked in VS preview and
in the the web browser. So I wouild think if you are using the report
viewer control the autorefresh would work there to.
On Sat, 13 May 2006 15:50:01 -0700, David
<David@.discussions.microsoft.com> wrote:
>Is there a way to update data on a reporting services report without having
>the user click the refresh button? I am wanting to show realtime stock
>quotes in a reporting services report and update stock quotes as they come in
>without having to call the refrsh method in the report viewer or having the
>user have to click the refrsh button. I am using the report viewer component
>in a C# winforms app.
>Thanks in advance!!!|||Thanks for trying guys. As I said in my initial email I want to update
values on the report without the report refreshing as a refresh is annoying
to the user and takes you back to the beginning of a report given you are in
backend pages of the report...
"Mark" wrote:
> If you're using RS 2005 you can set the "Autorefresh" for the report
> property to the desired refresh interval. It worked in VS preview and
> in the the web browser. So I wouild think if you are using the report
> viewer control the autorefresh would work there to.
> On Sat, 13 May 2006 15:50:01 -0700, David
> <David@.discussions.microsoft.com> wrote:
> >Is there a way to update data on a reporting services report without having
> >the user click the refresh button? I am wanting to show realtime stock
> >quotes in a reporting services report and update stock quotes as they come in
> >without having to call the refrsh method in the report viewer or having the
> >user have to click the refrsh button. I am using the report viewer component
> >in a C# winforms app.
> >Thanks in advance!!!
>
Subscribe to:
Posts (Atom)