"FOR XML Explicit" why poor performance, and any "quick wins"

Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:

> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, yo
u
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhe
ad
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in messa
ge
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event becau
se
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that the
se
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables
in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
>

"DBCC DBReindex" VS "Update Statistics"

Hi all,
Is it neccessay to run the "Update Statistics" after run "DBCC DBReindex"? Does "Update Statistics" automatically update the stats?
Thanks in advance
Kim,As you can see DBCC DBREINDEX updates statistics by itself - it is no need to update statistics after DBCC DBREINDEX.

drop table test
create table test(id int identity,code varchar(10))
create index test_i on test(id)
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
UPDATE STATISTICS test
go
SELECT STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'test' AND o.id = i.id and i.name='test_i'
go
DBCC DBREINDEX ('test')
SELECT STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'test' AND o.id = i.id and i.name='test_i'
go

"Auto Create Statistics" make queries run (really) slower

Hello,
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
.. WITH NORECOMPUTE.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c|||pinformaticien@.yahoo.fr wrote:
> Ok I think I've got an interesting workaround. As we cannot disable
> autocreate statistics for a specific table, the idea is to update
> those unwanted stats with two clauses :
> - SAMPLE 0 ROWS : to empty the statistics, so that they don't
> infuence the query optimizer anymore.
> - NORECOMPUTE : to avoid the "auto update stats" option to repopulate
> them later
> Here is the SQL statement I wrote to do this automatically on SQL 2005
> (you just need to set @.dbtname correctly). It's just necessary to run
> it from time to time, to ensure that new autocreated stats are
> disabled.
> The first tests shows exactly the same performance compared to queries
> with "auto create stats" disabled.
> DECLARE @.dbtname NVARCHAR(255)
> SET @.dbtname = 'You_Table_Name_Here'
> DECLARE c CURSOR FOR
> SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
> auto_created = 1
> DECLARE @.statname NVARCHAR(255)
> OPEN c
> FETCH next FROM c INTO @.statname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT @.statname
> EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
> SAMPLE 0 ROWS, NORECOMPUTE')
> FETCH NEXT FROM c INTO @.statname
> END
> CLOSE c
> DEALLOCATE c
Auto create statistics is only invoked if there are no existing
statistics for that column. So another solution to prevent automatic
statistics creation for a particular column is to manually create
statistics before the query is run that triggers the statistics
creation.
It could be as simple as:
create statistics ST_Test on dbo.test(my_column) with sample 0 rows,
norecompute
before running a query like
select * from dbo.test where my_column = 5
--
Gert-Jan

"Auto Create Statistics" make queries run (really) slower

Hello,
I'm experiencing a strange problem with query performance runing on
SQL2005. The database has 10+ tables, but we need to run really
specific queries in only 1 table with these caracteristics :
- 1 million rows
- we run everyday a few thousands queries on that table, each query
is unique (adhoc plan), and not parameterizable. (we cannot optimize
this)
- rows have a lot of nvarchar data
- all queries use a lot of LIKE / NOT LIKE statement (we cannot find
any work-around to that point, Fulltext is not adequate in that case)
- when LIKE operations are performed on columns, we always create a
duplicate column to optimize some search stuff, like putting
everything in Low Case, using Latin1_General_BIN collation, ...
- we have some indexes on short nvarchar columns, only those where we
use an exact '=' statemen
- we have another index on a float column
- all usefull indexes and statistics are manually created on that
table
- the nvarchar content of the table changes only once a day. It means
we do all optimization (indexes / stats) just after the update, and
there is no change on nvarchar data until the next update (24 hours
later)
I found that when "Auto Create Statistics" is enabled on the database,
that queries are really runing slower :
- "Auto Create Statistics" enabled : 57 min to run all queries
- "Auto Create Statistics" disabled and all auto-created stats
deleted : 7 min to run the same queries
It means that queries are running 8x slower when "Auto Create
Statistics" is enabled!
Another interesting point : just after disabling "Auto Create
Statistics", the queries continue to perform slowly until I manually
delete all statistics created automatically for that table (the one
begining with "_WA_Sys_"). It could mean that it's not a stat creation
issue, but only the existence of that statistics that could change the
query plan. But in both cases, the execution plan for the same query
seems to be exactly the same (same aspect, same costs). I also tried
to enable the Async stats update : no change.
The problem is that for all the other tables in the database, the
"Auto Create Statistics" is a good thing and useful. But not for that
specific table. Two questions :
- Is it possible to disable "Auto Create Statistics" on a specific
table? (I did not find anything about that in the BOL)
- If not, is there another work-around to deal with that kind of
performance drop?
Thanks.
We need the query plan with before and after to tell you why. It sounds like
there was an inaccurate estimate which might be fixed with a larger sample
than the default but that is a guess. SQL Server 2005 keeps better stats on
string column and it may be able to do a seek on a covering index in a LIKE
query especially with a larger sample. It just needs to be tested heavily.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:8384b744-0444-4f91-a1ce-b4d302317302@.13g2000hsb.googlegroups.com...
> Hello,
> I'm experiencing a strange problem with query performance runing on
> SQL2005. The database has 10+ tables, but we need to run really
> specific queries in only 1 table with these caracteristics :
> - 1 million rows
> - we run everyday a few thousands queries on that table, each query
> is unique (adhoc plan), and not parameterizable. (we cannot optimize
> this)
> - rows have a lot of nvarchar data
> - all queries use a lot of LIKE / NOT LIKE statement (we cannot find
> any work-around to that point, Fulltext is not adequate in that case)
> - when LIKE operations are performed on columns, we always create a
> duplicate column to optimize some search stuff, like putting
> everything in Low Case, using Latin1_General_BIN collation, ...
> - we have some indexes on short nvarchar columns, only those where we
> use an exact '=' statemen
> - we have another index on a float column
> - all usefull indexes and statistics are manually created on that
> table
> - the nvarchar content of the table changes only once a day. It means
> we do all optimization (indexes / stats) just after the update, and
> there is no change on nvarchar data until the next update (24 hours
> later)
> I found that when "Auto Create Statistics" is enabled on the database,
> that queries are really runing slower :
> - "Auto Create Statistics" enabled : 57 min to run all queries
> - "Auto Create Statistics" disabled and all auto-created stats
> deleted : 7 min to run the same queries
> It means that queries are running 8x slower when "Auto Create
> Statistics" is enabled!
> Another interesting point : just after disabling "Auto Create
> Statistics", the queries continue to perform slowly until I manually
> delete all statistics created automatically for that table (the one
> begining with "_WA_Sys_"). It could mean that it's not a stat creation
> issue, but only the existence of that statistics that could change the
> query plan. But in both cases, the execution plan for the same query
> seems to be exactly the same (same aspect, same costs). I also tried
> to enable the Async stats update : no change.
> The problem is that for all the other tables in the database, the
> "Auto Create Statistics" is a good thing and useful. But not for that
> specific table. Two questions :
> - Is it possible to disable "Auto Create Statistics" on a specific
> table? (I did not find anything about that in the BOL)
> - If not, is there another work-around to deal with that kind of
> performance drop?
> Thanks.
|||For what I've tried, creating then updating statistics on nvarchar
columns with the "WITH FULLSCAN" clause doesn't help. But here are
some interesting results : I setup a test server, and ran 2 times 10
queries, first time with "Auto Create Statistics" enabled, second time
with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
all the automatically created statistics (the one begining with
"_WA_Sys_"), then restarted SQL server service. Here are the results
for the following query
Select * from sys.dm_exec_query_optimizer_info where counter in
('optimizations','elapsed time')
"Auto Create Statistics" enabled
optimizations 11 1
elapsed time 11 2,80751895306448
"Auto Create Statistics" disabled
optimizations 11 1
elapsed time 11 0,0665338534973798
It confirms that all the performance drop goes in optimization time
(2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
anything in my case (it leads to the same execution plan is the same
is both cases). It means I need to find a way to disable / reduce that
optimization time when "Auto Create Statistics" is enabled. Any idea?
Is it possible to disable "Auto Create Statistics" on a specific
table?
|||It sounds like you are right. It sounds like optimizer is spending more time
try to compile since there are more options only to come up with the same
plan. You can disable autostats on a particular table with UPDATE STATISTICS
... WITH NORECOMPUTE.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<pinformaticien@.yahoo.fr> wrote in message
news:1e199977-1bc2-4614-ad72-036bb3111ce6@.d21g2000prf.googlegroups.com...
> For what I've tried, creating then updating statistics on nvarchar
> columns with the "WITH FULLSCAN" clause doesn't help. But here are
> some interesting results : I setup a test server, and ran 2 times 10
> queries, first time with "Auto Create Statistics" enabled, second time
> with "Auto Create Statistics" disabled. Between the 2 tests, I deleted
> all the automatically created statistics (the one begining with
> "_WA_Sys_"), then restarted SQL server service. Here are the results
> for the following query
> Select * from sys.dm_exec_query_optimizer_info where counter in
> ('optimizations','elapsed time')
> "Auto Create Statistics" enabled
> optimizations 11 1
> elapsed time 11 2,80751895306448
> "Auto Create Statistics" disabled
> optimizations 11 1
> elapsed time 11 0,0665338534973798
> It confirms that all the performance drop goes in optimization time
> (2.8 sec average vs 0.07 sec), that finally almost doesn't otimize
> anything in my case (it leads to the same execution plan is the same
> is both cases). It means I need to find a way to disable / reduce that
> optimization time when "Auto Create Statistics" is enabled. Any idea?
> Is it possible to disable "Auto Create Statistics" on a specific
> table?
|||Thanks for the reply. UPDATE STATISTICS ... WITH NORECOMPUTE would
just avoid statistics to be updated. In my case, it's not the stat
update which is problematic, but the existence of the automatically
created statistics (as they badly influence the query optimizer on
that table). One solution could be to move that table on a dedicated
database and turn "Auto Create Statistics" OFF, but we would like to
avoid this solution.
I'm really surprised that SQL Server doesn't allow to disable
automatic creation of statistics on a per table basis. That could be
just really helpful in some cases.
|||Ok I think I've got an interesting workaround. As we cannot disable
autocreate statistics for a specific table, the idea is to update
those unwanted stats with two clauses :
- SAMPLE 0 ROWS : to empty the statistics, so that they don't
infuence the query optimizer anymore.
- NORECOMPUTE : to avoid the "auto update stats" option to repopulate
them later
Here is the SQL statement I wrote to do this automatically on SQL 2005
(you just need to set @.dbtname correctly). It's just necessary to run
it from time to time, to ensure that new autocreated stats are
disabled.
The first tests shows exactly the same performance compared to queries
with "auto create stats" disabled.
DECLARE @.dbtname NVARCHAR(255)
SET @.dbtname = 'You_Table_Name_Here'
DECLARE c CURSOR FOR
SELECT name FROM sys.stats WHERE object_id = object_id(@.dbtname) AND
auto_created = 1
DECLARE @.statname NVARCHAR(255)
OPEN c
FETCH next FROM c INTO @.statname
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.statname
EXEC ('UPDATE STATISTICS ' + @.dbtname + ' (' + @.statname + ') WITH
SAMPLE 0 ROWS, NORECOMPUTE')
FETCH NEXT FROM c INTO @.statname
END
CLOSE c
DEALLOCATE c

error creating maintenance plan

Hello,
I'm creating a maintenance plan for a SQL SERVER DB in 64 bits. The plan
includes rebuild indexes, check integrity, update statistics, etc.
I'm using the wizard to create to plan, but at the when I press the finish
button at the end of the wizard there is an error message and I'm unable to
create the plan.
The error message is include at the end of this email.
Does anybody know what could be causing the error?
In advance I appreciate your help and comments.
Edmundo J. Davila
ERROR
TITLE: Maintenance Plan Wizard Progress
Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
BUTTONS:
OK
This question has already been answered.
Ekrem nsoy
"Edmundo J. Davila" <edavila@.monisa.com> wrote in message
news:4F578C0F-8F8A-4371-88B1-C6FDA8C0D4AD@.microsoft.com...
> Hello,
> I'm creating a maintenance plan for a SQL SERVER DB in 64 bits. The plan
> includes rebuild indexes, check integrity, update statistics, etc.
> I'm using the wizard to create to plan, but at the when I press the finish
> button at the end of the wizard there is an error message and I'm unable
> to create the plan.
> The error message is include at the end of this email.
> Does anybody know what could be causing the error?
> In advance I appreciate your help and comments.
> Edmundo J. Davila
> ERROR
>
> TITLE: Maintenance Plan Wizard Progress
> --
> Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
> --
> BUTTONS:
> OK
> --
>

error creating maintenance plan

Hello,
I'm creating a maintenance plan for a SQL SERVER DB in 64 bits. The plan
includes rebuild indexes, check integrity, update statistics, etc.
I'm using the wizard to create to plan, but at the when I press the finish
button at the end of the wizard there is an error message and I'm unable to
create the plan.
The error message is include at the end of this email.
Does anybody know what could be causing the error?
In advance I appreciate your help and comments.
Edmundo J. Davila
ERROR
---
TITLE: Maintenance Plan Wizard Progress
--
Create maintenance plan failed.
--
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
--
BUTTONS:
OK
--This question has already been answered.
--
Ekrem Önsoy
"Edmundo J. Davila" <edavila@.monisa.com> wrote in message
news:4F578C0F-8F8A-4371-88B1-C6FDA8C0D4AD@.microsoft.com...
> Hello,
> I'm creating a maintenance plan for a SQL SERVER DB in 64 bits. The plan
> includes rebuild indexes, check integrity, update statistics, etc.
> I'm using the wizard to create to plan, but at the when I press the finish
> button at the end of the wizard there is an error message and I'm unable
> to create the plan.
> The error message is include at the end of this email.
> Does anybody know what could be causing the error?
> In advance I appreciate your help and comments.
> Edmundo J. Davila
> ERROR
> ---
>
> TITLE: Maintenance Plan Wizard Progress
> --
> Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
> --
> BUTTONS:
> OK
> --
>