Jul 20, 2005
Hello group.I have an issue, which has bothered me for a while now:I'm wondering why the column statistics, which SQL Server wants me tocreate, if I turn off auto-created statistics, are so important to theoptimizer?Example: from Northwind (with auto create stats off), I do the following:SELECT * FROM Customers WHERE Country = 'Sweden'My query plan show a clustered index scan, which is expected - no indexexists for Country. BUT, the query plan also shows, that the optimizer ismissing a statistic on Country, which tells me, that the optimizer wouldbenefit from knowing this.I cannot see why? (and I've been trying for a while now).If I create the missing statistics, nothing happens in the query plan (andwhy should it?). I could understand it, if the optimizer suggested an indexon Country - this would make sense, but if creating the missing index, queryanalyzer creates the statistics with an empty index, which seems to me to beless than usable.I've been thinking long and hard about this, but haven't been able to reacha conclusion :) It has some relevance to my work, because allowing theoptimizer to create missing statistics limits my options for designingindexes (e.g. covering) for some rather wide tables, so I'm thinking why notturn it off altogether. But I would like to know the consequences - hopesomebody has already delved into this, and knows a good explanation.RgdsJesper
View 5 Replies
View Related
Mar 14, 2006
This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?
--------------------------------------------------------------------------------------------------------------------
I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.
In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:
Command attempted:
drop Table "dbo"."MyReplTable"
(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)
Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)
I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.
I presume that the sp_addarticle option @pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)
Perhaps the sp_addpublication @repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.
If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).
/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @distributor = N'MyInstance', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData', @data_file_size = 4, @log_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:ThisFolderMY2005DBWorking
epldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:ThisFolderMY2005DBWorking
epldata', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'MyInstance', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:ThisFolderMY2005DBWorking
epldata', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
use [DatabaseA]
exec sp_replicationdboption @dbname = N'DatabaseA', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @publication = N'PubAtoB', @description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'PubAtoB', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
use [DatabaseA]
exec sp_addarticle @publication = N'PubAtoB', @article = N'MyReplTable', @source_owner = N'dbo', @source_object = N'MyReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyReplTable', @del_cmd = N'CALL sp_MSdel_dboMyReplTable', @upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
-----------------BEGIN: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseA]
exec sp_addsubscription @publication = N'PubAtoB', @subscriber = N'MyInstance', @destination_db = N'DatabaseB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubAtoB', @subscriber = N'MyInstance', @subscriber_db = N'DatabaseB', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20060314, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseB]
exec sp_replicationdboption @dbname = N'DatabaseB', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @publication = N'PubBtoC', @description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'PubBtoC', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
use [DatabaseB]
exec sp_addarticle @publication = N'PubBtoC', @article = N'MyReplTable', @source_owner = N'dbo', @source_object = N'MyReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyReplTable', @del_cmd = N'CALL sp_MSdel_dboMyReplTable', @upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
-----------------BEGIN: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseB]
exec sp_addsubscription @publication = N'PubBtoC', @subscriber = N'MyInstance', @destination_db = N'DatabaseC', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubBtoC', @subscriber = N'MyInstance', @subscriber_db = N'DatabaseC', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20060314, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'MyInstance'-----------------
View 7 Replies
View Related
Sep 13, 2006
Hi,
I have set up a publisher using transactional replication. ( all seems ok). The initial snapshot has been generated.
The replication share on the distributor has all the generated DDL in it.
I add a subscriber. The tables are generated up to tblCountry then I get an incorrect syntax near ')' error
The Replication Monitor shows the following code as the cause. ( bold indicates incorrect sql)
Is this a bug in Replication (as this is an autogenerated sp)or have I configured something incorrectly?
The ddl for the table index is as follows ( from the replication folder)
/----
CREATE TABLE [APP].[tblCountry](
[CountryId] AS ([ISO 3166-1 NUMERIC-3]) PERSISTED NOT NULL,
[CountryCode] AS ([ISO 3166-1 ALPHA-2]) PERSISTED NOT NULL,
[CountryName] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-2] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-3] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 NUMERIC-3] [int] NOT NULL
)
GO
---/
/------ Keys ddl (.dx)
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [PK_TBLCOUNTRY] PRIMARY KEY CLUSTERED ([CountryId])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA2] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-2])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA3] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-3])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_COUNTRYNAME] UNIQUE NONCLUSTERED ([CountryName])
go
--------/
/------------
Command attempted:
create procedure "sp_MSins_APPtblCountry_msrepl_ccs"
@c1 int,@c2 varchar(80),@c3 char(2),@c4 char(3),@c5 int
as
begin
if exists ( select * from "APP"."tblCountry"
where
)
begin
update "APP"."tblCountry" set
"CountryName" = @c2
,"ISO 3166-1 ALPHA-2" = @c3
,"ISO 3166-1 ALPHA-3" = @c4
,"ISO 3166-1 NUMERIC-3" = @c5
where
end
else
begin
insert into "APP"."tblCountry"(
"CountryName"
,"ISO 3166-1 ALPHA-2"
,"ISO 3166-1 ALPHA-3"
,"ISO 3166-1 NUMERIC-3"
)
values (
@c2
,@c3
,@c4
(Transaction sequence number: 0x00000016000004F2014500000000, Command ID: 213)
------------/
View 5 Replies
View Related
