Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect Compatibility level Error 15414:

John,
I ran the indexed views query and indexed computed columns
query on the database that has the compatability level set
to 70. Only one table returned from the indexed computed
columns query.
Output from indexed computed columns query
Object_Name Column_Name
AccountDetail AcctDtlSrceTble
__________________________________________________________
DROP INDEX AccountDetail.AcctDtlSrceTble
How do I obtain a sql script to regenerate the
AccountDetail.AcctDtlSrceTble indexed computed column?
What are the steps that I need to take to change the
compatiblity level 80 to 70?
Thank You,
John
This suggests you are using some capabilities available in
SQL2000 that were
not previously available in down level versions. To check
for indexed views
or indexes on computed columns run the following query in
Query Analyzer.
These indexes will need to be dropped in order to
downgrade the database
compatability
-- indexed views
select name from sysobjects where xtype = 'V'
and objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsIndexed')=1
-- indexed computed columns
select object_name(c.id),c.name
from syscolumns c join sysindexkeys k
on c.colid = k.colid and c.id=k.id
where columnproperty(c.id,c.name,'IsComputed')=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsIndexed')=1
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in
message
news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
I have a SQL Server 2000 Enterprise Edition along with
SP3A with several databases. These databases are 20 GB in
size. On one of the databases the Compatibility level was
changed from 70 to 80. I need to change the compatiblity
level 80 to 70.
When tried to change the compatibilty level I received the
following error:
Error 15414: Cannot set compatibilty level because
database has a view or computed column is indexed. These
indexes require a SQL Server compatible database.
Please help me resolve this problem.
John
name
---
---
-- ---
---
--
AccountDetail
AcctDtlSrceTble
(1 row(s) affected)The easiest way is to use the Query Analyzer Object Browser and expand the
node for the table and then the underlying one for Indexes. You can then
select the relavent index, right click on it and choose Script Object to New
Window As > Create.You can do the same to generate the script to drop it.
You can use the system stored procedure dbcmptlevel to change the
compatability mode e.g.
EXEC sp_dbcmptlevel 'pubs', 70
However, I'm not clear on why you need to do this ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:257801c427d3$568d9ac0$a101280a@.phx.gbl...
> John,
> I ran the indexed views query and indexed computed columns
> query on the database that has the compatability level set
> to 70. Only one table returned from the indexed computed
> columns query.
> Output from indexed computed columns query
> Object_Name Column_Name
> AccountDetail AcctDtlSrceTble
> __________________________________________________________
> DROP INDEX AccountDetail.AcctDtlSrceTble
> How do I obtain a sql script to regenerate the
> AccountDetail.AcctDtlSrceTble indexed computed column?
> What are the steps that I need to take to change the
> compatiblity level 80 to 70?
> Thank You,
> John
>
> This suggests you are using some capabilities available in
> SQL2000 that were
> not previously available in down level versions. To check
> for indexed views
> or indexes on computed columns run the following query in
> Query Analyzer.
> These indexes will need to be dropped in order to
> downgrade the database
> compatability
> -- indexed views
> select name from sysobjects where xtype = 'V'
> and objectproperty(id,'IsMSShipped')=0
> and objectproperty(id,'IsIndexed')=1
> -- indexed computed columns
> select object_name(c.id),c.name
> from syscolumns c join sysindexkeys k
> on c.colid = k.colid and c.id=k.id
> where columnproperty(c.id,c.name,'IsComputed')=1
> and objectproperty(c.id,'IsMSShipped')=0
> and objectproperty(c.id,'IsIndexed')=1
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "John" <anonymous@.discussions.microsoft.com> wrote in
> message
> news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
> I have a SQL Server 2000 Enterprise Edition along with
> SP3A with several databases. These databases are 20 GB in
> size. On one of the databases the Compatibility level was
> changed from 70 to 80. I need to change the compatiblity
> level 80 to 70.
> When tried to change the compatibilty level I received the
> following error:
> Error 15414: Cannot set compatibilty level because
> database has a view or computed column is indexed. These
> indexes require a SQL Server compatible database.
> Please help me resolve this problem.
> John
>
>
> name
>
> ---
> ---
> -- ---
> ---
> --
> AccountDetail
> AcctDtlSrceTble
> (1 row(s) affected)

Incorrect Compatibility level Error 15414:

John,
I ran the indexed views query and indexed computed columns
query on the database that has the compatability level set
to 70. Only one table returned from the indexed computed
columns query.
Output from indexed computed columns query
Object_Name Column_Name
AccountDetail AcctDtlSrceTble
__________________________________________________ ________
DROP INDEX AccountDetail.AcctDtlSrceTble
How do I obtain a sql script to regenerate the
AccountDetail.AcctDtlSrceTble indexed computed column?
What are the steps that I need to take to change the
compatiblity level 80 to 70?
Thank You,
John
This suggests you are using some capabilities available in
SQL2000 that were
not previously available in down level versions. To check
for indexed views
or indexes on computed columns run the following query in
Query Analyzer.
These indexes will need to be dropped in order to
downgrade the database
compatability
-- indexed views
select name from sysobjects where xtype = 'V'
and objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsIndexed')=1
-- indexed computed columns
select object_name(c.id),c.name
from syscolumns c join sysindexkeys k
on c.colid = k.colid and c.id=k.id
where columnproperty(c.id,c.name,'IsComputed')=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsIndexed')=1
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in
message
news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
I have a SQL Server 2000 Enterprise Edition along with
SP3A with several databases. These databases are 20 GB in
size. On one of the databases the Compatibility level was
changed from 70 to 80. I need to change the compatiblity
level 80 to 70.
When tried to change the compatibilty level I received the
following error:
Error 15414: Cannot set compatibilty level because
database has a view or computed column is indexed. These
indexes require a SQL Server compatible database.
Please help me resolve this problem.
John
name
---
-- ---
AccountDetail
AcctDtlSrceTble
(1 row(s) affected)
The easiest way is to use the Query Analyzer Object Browser and expand the
node for the table and then the underlying one for Indexes. You can then
select the relavent index, right click on it and choose Script Object to New
Window As > Create.You can do the same to generate the script to drop it.
You can use the system stored procedure dbcmptlevel to change the
compatability mode e.g.
EXEC sp_dbcmptlevel 'pubs', 70
However, I'm not clear on why you need to do this ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:257801c427d3$568d9ac0$a101280a@.phx.gbl...
> John,
> I ran the indexed views query and indexed computed columns
> query on the database that has the compatability level set
> to 70. Only one table returned from the indexed computed
> columns query.
> Output from indexed computed columns query
> Object_Name Column_Name
> AccountDetail AcctDtlSrceTble
> __________________________________________________ ________
> DROP INDEX AccountDetail.AcctDtlSrceTble
> How do I obtain a sql script to regenerate the
> AccountDetail.AcctDtlSrceTble indexed computed column?
> What are the steps that I need to take to change the
> compatiblity level 80 to 70?
> Thank You,
> John
>
> This suggests you are using some capabilities available in
> SQL2000 that were
> not previously available in down level versions. To check
> for indexed views
> or indexes on computed columns run the following query in
> Query Analyzer.
> These indexes will need to be dropped in order to
> downgrade the database
> compatability
> -- indexed views
> select name from sysobjects where xtype = 'V'
> and objectproperty(id,'IsMSShipped')=0
> and objectproperty(id,'IsIndexed')=1
> -- indexed computed columns
> select object_name(c.id),c.name
> from syscolumns c join sysindexkeys k
> on c.colid = k.colid and c.id=k.id
> where columnproperty(c.id,c.name,'IsComputed')=1
> and objectproperty(c.id,'IsMSShipped')=0
> and objectproperty(c.id,'IsIndexed')=1
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "John" <anonymous@.discussions.microsoft.com> wrote in
> message
> news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
> I have a SQL Server 2000 Enterprise Edition along with
> SP3A with several databases. These databases are 20 GB in
> size. On one of the databases the Compatibility level was
> changed from 70 to 80. I need to change the compatiblity
> level 80 to 70.
> When tried to change the compatibilty level I received the
> following error:
> Error 15414: Cannot set compatibilty level because
> database has a view or computed column is indexed. These
> indexes require a SQL Server compatible database.
> Please help me resolve this problem.
> John
>
>
> name
>
> -- ---
> --
> AccountDetail
> AcctDtlSrceTble
> (1 row(s) affected)

Incorrect Compatibility level Error 15414:

John,
I ran the indexed views query and indexed computed columns
query on the database that has the compatability level set
to 70. Only one table returned from the indexed computed
columns query.
Output from indexed computed columns query
Object_Name Column_Name
AccountDetail AcctDtlSrceTble
________________________________________
__________________
DROP INDEX AccountDetail.AcctDtlSrceTble
How do I obtain a sql script to regenerate the
AccountDetail.AcctDtlSrceTble indexed computed column?
What are the steps that I need to take to change the
compatiblity level 80 to 70?
Thank You,
John
This suggests you are using some capabilities available in
SQL2000 that were
not previously available in down level versions. To check
for indexed views
or indexes on computed columns run the following query in
Query Analyzer.
These indexes will need to be dropped in order to
downgrade the database
compatability
-- indexed views
select name from sysobjects where xtype = 'V'
and objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsIndexed')=1
-- indexed computed columns
select object_name(c.id),c.name
from syscolumns c join sysindexkeys k
on c.colid = k.colid and c.id=k.id
where columnproperty(c.id,c.name,'IsComputed')=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsIndexed')=1
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in
message
news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
I have a SQL Server 2000 Enterprise Edition along with
SP3A with several databases. These databases are 20 GB in
size. On one of the databases the Compatibility level was
changed from 70 to 80. I need to change the compatiblity
level 80 to 70.
When tried to change the compatibilty level I received the
following error:
Error 15414: Cannot set compatibilty level because
database has a view or computed column is indexed. These
indexes require a SQL Server compatible database.
Please help me resolve this problem.
John
name
---
---
-- ---
---
--
AccountDetail
AcctDtlSrceTble
(1 row(s) affected)The easiest way is to use the Query Analyzer Object Browser and expand the
node for the table and then the underlying one for Indexes. You can then
select the relavent index, right click on it and choose Script Object to New
Window As > Create.You can do the same to generate the script to drop it.
You can use the system stored procedure dbcmptlevel to change the
compatability mode e.g.
EXEC sp_dbcmptlevel 'pubs', 70
However, I'm not clear on why you need to do this ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:257801c427d3$568d9ac0$a101280a@.phx.gbl...
> John,
> I ran the indexed views query and indexed computed columns
> query on the database that has the compatability level set
> to 70. Only one table returned from the indexed computed
> columns query.
> Output from indexed computed columns query
> Object_Name Column_Name
> AccountDetail AcctDtlSrceTble
> ________________________________________
__________________
> DROP INDEX AccountDetail.AcctDtlSrceTble
> How do I obtain a sql script to regenerate the
> AccountDetail.AcctDtlSrceTble indexed computed column?
> What are the steps that I need to take to change the
> compatiblity level 80 to 70?
> Thank You,
> John
>
> This suggests you are using some capabilities available in
> SQL2000 that were
> not previously available in down level versions. To check
> for indexed views
> or indexes on computed columns run the following query in
> Query Analyzer.
> These indexes will need to be dropped in order to
> downgrade the database
> compatability
> -- indexed views
> select name from sysobjects where xtype = 'V'
> and objectproperty(id,'IsMSShipped')=0
> and objectproperty(id,'IsIndexed')=1
> -- indexed computed columns
> select object_name(c.id),c.name
> from syscolumns c join sysindexkeys k
> on c.colid = k.colid and c.id=k.id
> where columnproperty(c.id,c.name,'IsComputed')=1
> and objectproperty(c.id,'IsMSShipped')=0
> and objectproperty(c.id,'IsIndexed')=1
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "John" <anonymous@.discussions.microsoft.com> wrote in
> message
> news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
> I have a SQL Server 2000 Enterprise Edition along with
> SP3A with several databases. These databases are 20 GB in
> size. On one of the databases the Compatibility level was
> changed from 70 to 80. I need to change the compatiblity
> level 80 to 70.
> When tried to change the compatibilty level I received the
> following error:
> Error 15414: Cannot set compatibilty level because
> database has a view or computed column is indexed. These
> indexes require a SQL Server compatible database.
> Please help me resolve this problem.
> John
>
>
> name
>
> ---
> ---
> -- ---
> ---
> --
> AccountDetail
> AcctDtlSrceTble
> (1 row(s) affected)

Inconsistent Reads and performance problems

I have a fairly complex query which takes about 3-4 seconds. Since it
uses quite some tables and views which I won't be able to post, I will
just post my findings.
This SPROC takes a long time to comlete often propting to kill the
process. During such poor show the profiler records unsually high
number of reads. The problem goes away on DBCC DBRINDEX. After
examining I found this SPROC uses multiple tables/views which have DBCC
SHOWCONTIG as follows
IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes AveragePa
geDensity ScanDensity BestCount ActualCo
unt LogicalFragmentation ExtentFragm
entation MinimumRecordSize MaximumRecord
Size AverageRecordSize ForwardedReco
rds
PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 1
32 108.651 0
PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177
138.639 0
IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24 0
0 57 65 57.003 0
PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0 0
57 57 57 0
IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 100
107 107 0 1.869158864 96 247 96.353 0
PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 7
2 54.605 0
I apologize for the above formatting try reformatting with excel.
Should I try DROPPING an RECREATING all Indexes on these tables with
default fillfactor?
As you notice the the number of rows are not that significant.
The original fill factor on all tables is 70%. Any recommendations for
optimization would be appreciated.
Thanks
MasterofNoneAnybody?
MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes Average
PageDensity ScanDensity BestCount Actual
Count LogicalFragmentation ExtentFra
gmentation MinimumRecordSize MaximumReco
rdSize AverageRecordSize ForwardedRe
cords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100
132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 17
7 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24
0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0
0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 10
0 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51
72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone|||MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes Average
PageDensity ScanDensity BestCount Actual
Count LogicalFragmentation ExtentFra
gmentation MinimumRecordSize MaximumReco
rdSize AverageRecordSize ForwardedRe
cords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100
132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 17
7 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24
0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0
0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 10
0 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51
72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone
>
Forget the index stats for now. Start by analyzing the execution plan
for the query, determine where the bottleneck is (table or index
scans?), and focus on eliminating that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 30.08.2006 15:06, Tracy McKibben wrote:
> Forget the index stats for now. Start by analyzing the execution plan
> for the query, determine where the bottleneck is (table or index
> scans?), and focus on eliminating that.
I second that. You might see an improvement just after DBCC DBRINDEX
just because now those index pages are in memory. But this won't help
you in the real application situation.
Kind regards
robert

Inconsistent Reads and performance problems

I have a fairly complex query which takes about 3-4 seconds. Since it
uses quite some tables and views which I won't be able to post, I will
just post my findings.
This SPROC takes a long time to comlete often propting to kill the
process. During such poor show the profiler records unsually high
number of reads. The problem goes away on DBCC DBRINDEX. After
examining I found this SPROC uses multiple tables/views which have DBCC
SHOWCONTIG as follows
IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
I apologize for the above formatting try reformatting with excel.
Should I try DROPPING an RECREATING all Indexes on these tables with
default fillfactor?
As you notice the the number of rows are not that significant.
The original fill factor on all tables is 70%. Any recommendations for
optimization would be appreciated.
Thanks
MasterofNoneAnybody?
MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone|||MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23 2393.199951 70.43241882 100 24 24 0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 2384.335938 70.54193115 100 61 61 0 0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155 107 106 2408.256104 70.24640656 100 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone
>
Forget the index stats for now. Start by analyzing the execution plan
for the query, determine where the bottleneck is (table or index
scans?), and focus on eliminating that.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 30.08.2006 15:06, Tracy McKibben wrote:
> Forget the index stats for now. Start by analyzing the execution plan
> for the query, determine where the bottleneck is (table or index
> scans?), and focus on eliminating that.
I second that. You might see an improvement just after DBCC DBRINDEX
just because now those index pages are in memory. But this won't help
you in the real application situation.
Kind regards
robert

Including Views in Replication

I have a three server peer-to-peer replication setup that includes articles for tables and views. As I understand the BOL, scheme changes -- which I take to mean changes, amont other things, changes in the design of a table or view -- should automatically replicate to the other servers in the topology. Here are my quesitons:

When including a view as part of the publication, what is it, exactly, that is getting replicated? If all the tables supporting a given view are being replicated, and the view exists on all three boxes, whatelse, besides the view schema (and changes thereto) is being replicated?

Secondly, if in fact schema changes are replicated, why can't I modify a view that is part of a publication? When I try to make a change to such a view, I get a server timeout message, every single time. When I remove the view from the publication, I can make the modification with no trouble. What does replicating schema changes mean if I can't make changes to the schema?

Thanks for any enlightenment on this.

Randy

In general, when views are replicated, the schema definition of the view object is actually what gets replicated. After initial sync, there are no data changes tied to a view.

In non-peer-to-peer transactional replication, view schema changes are propagated from the publisher to the subscribers automatically. i.e. A view schema change could be changing the list of columns included in the view select statement.

Since you have a peer-to-peer topology set up there are more restrictions to consider. Check out the General Considerations under Peer-to-Peer replication in SQL Server 2005 Books Online. One of the restrictions is that any schema changes require the peer-to-peer topology to be quiesced (stopping activity on published tables at all nodes and ensuring that each node has received all changes from all other nodes).

Hope this helps,

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

the resultset and content of the view can only be replicated if it is "indexed" if the view is not index the only thing that gets replicated is the view definition.

|||

Hi Randy,

I am interested to know more about your second issue (timeout when modify a view). Could you give me more details on your issue? For example, what statement do you use to modify a view, what is the origional view definition and what is the error message?

Thanks,

Peng

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anymore

Preface: I'm a newbie at 2005 and sql server in general.

I can connect to a sql server (2005 express) but I can only make views and not run queries. When I open up a new query and create it with query designer and click add table I see no tables, views or anything in the box. When I create a new view in the database I do see tables and views though.

The little circle part of the icon on the server in the left hand pane is clear now when before it was green. I still can connect to the database ok but I can't use the query analyzer for queries anymore. I need to do that because I do have some slow running queries.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anym

Preface: I'm a newbie at 2005 and sql server in general.

I can connect to a sql server (2005 express) but I can only make views and not run queries. When I open up a new query and create it with query designer and click add table I see no tables, views or anything in the box. When I create a new view in the database I do see tables and views though.

The little circle part of the icon on the server in the left hand pane is clear now when before it was green. I still can connect to the database ok but I can't use the query analyzer for queries anymore. I need to do that because I do have some slow running queries.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy