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:
>

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?
> >

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:
>

Rebuild Table Daily (sproc)

My goal is to recreate a table daily so that the data is updated. This could be a bad decision performance-wise, but I felt this was simpler than running a daily update statement. I created a stored procedure:

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOCREATE PROCEDURE sp_CreatetblImprintPhraseASDROP TABLE tblImprintPhraseGOCREATE TABLE tblImprintPhrase(CustIDchar(12),CustName varchar(40),TranNoRelchar(15))GO

 However, I was looking to edit the stored procedure, changing CREATE to ALTER, but when I do so, I am prompted with: Error 170: Line 2: Incorrect syntax near "(". If I change back to CREATE, the error goes away, but the sproc cannot be run because it already exists. Any thoughts?

To be more specific, is there a proper method to make changes to a stored procedure that already exists? I assumed that swapping the CREATE statement for ALTER would be it, but it has been causing issues.

|||

Simply needed to read more about stored procedures. Batch processes in particular.

Real Time Data

I currently have an application which retrieves stats from a SQL database that are updated throughout the day. My application updates every 3 seconds to retrieve these stats but I found that it's quite expensive to the server's CPU when there are several clients running my application. Are there any other methods for extracting the data that won't require so much CPU. The fact that the database is being hit so much isn't good either. I have already written a webservice hoping that this would improve things but I'm not sure it has.

Hi,

is it really necessary to have real time data? Actually I mean, does the data gets updated every 3 seconds or less? If not you could, in your webservice, make use of the Cache object and have, you need SQL 2005 for this I believe, have SqlCacheDependency so that only when something in a table changes only then the Cache gets updated.

Grz, Kris.

|||The data are phone stats which are used to show calls received in an application.The application shows desks in a plan and is updated when a phone is ringing etc... This is why it needs to be in real time but I can't think of any better way to do this. The call events are stored in a table called CallRecords but I also have a snapshot table which the application uses. It selects all from this table which has the desk number, answered, missed and total calls columns which are incremented based on each event that comes through to CallRecords.|||

Hi sichiu,

The accuracy of data and performance of the server are always a pair of conflicts.

You can for better performance, you can cache data and set an expire duration. However, that will cause data not being real time.

In this case, setting the expiration duration is very important.

Another way is that you can cache data locally, and use SqlDependency class to notify your app when the data is changed through SQL notification service. For more information, please check

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx