Adding a resource (Generic Script) to the SQL Cluster

I would like to add a VB Script to the SQL Cluster so that
in the event of a failover the script will execute on the
new active node. The script goes out and checks for the
existance of some key files once a day.
I am running Windows 2003 SQL 2000 in an Active / Passive
configuration.
I have the VBS running every morning through task Mgr, but
I would like to get it configured to run as a clustered
resource and failover to the active node.
Thanks,
In 2003, there is a "Generic Script" resource type that support VBS and Perl
scripts. Check out the following for more details:
http://msdn.microsoft.com/library/de...ric_script.asp
Regards,
John
"Rodney" <anonymous@.discussions.microsoft.com> wrote in message
news:441301c47333$a3be4aa0$a601280a@.phx.gbl...
> I would like to add a VB Script to the SQL Cluster so that
> in the event of a failover the script will execute on the
> new active node. The script goes out and checks for the
> existance of some key files once a day.
> I am running Windows 2003 SQL 2000 in an Active / Passive
> configuration.
> I have the VBS running every morning through task Mgr, but
> I would like to get it configured to run as a clustered
> resource and failover to the active node.
> Thanks,

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.sql

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Ad hoc query running crazy slow.

Hi,
I have a job that execute a stored proc. It was running fine like 3
minutes to finish but a week ago it started to take 4 hours. Its using
ad hoc quries in that stored procedure. I reindex all the tables that
it was using. I check execution plan there was no tabloe scans. I
recompile the stored proc but no use. its sql 2000 with service pack 3
installed on it. Here is the query anyone has any clue what went wrong
suddenly. Link server is working fine.
i am running it like this
exec usp_MO_ActivityTradesBPS 'CORP'
CREATE PROCEDURE dbo.usp_MO_PositionsBPS
@.Load varchar(25) = null,
@.AsOfDate datetime
AS
Set @.Load = '%'
If @.Load is null or @.Load = ''
begin
Set @.Load = '%'
end
If IsNull(@.AsOfDate,'') = ''
Set @.AsOfDate = dbo.fn_BusinessDate(GetDate()-1)
If @.Load = 'SANFRAN'
begin
Set @.Load = '%'
SELECT RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' +
T.ACCOUNTTYPECD + T.CUSIPNR [key],
RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' + T.ACCOUNTTYPECD
BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR, T.CUSIPNR,
T.ADPSECURITY1DS, T.TDQUANTITY, T.SECURITYPRICEAM, T.LEDGERBALANCEAM,
T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
T.DAILYNETPL, T.CURRENCYISOCD
FROM BPS_Positions T
WHERE T.BRANCHCD = '001'
and T.BRANCHCD + '-' + T.ACCOUNTNR not in (Select Distinct
SM.Bps_AcctNr
From MO_InvStrategyMap SM
Where SM.Bps_AcctNr is not null)
and (T.TDMTDSECFEE <> 0
or T.TDCOmsnAm <> 0
or T.TDQUANTITY <> 0
or T.MarketValAm <> 0
or T.MTDNETPL <> 0
or T.MTDPL <> 0
or T.LEDGERBAlanceAm <> 0
or T.DAILYNETPL <> 0
or T.TDDivIntAm <> 0
or T.TDGRCREDITMTDOP7 <> 0)
ORDER BY T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD, T.ADPSECURITYNR,
T.CUSIPNR, T.CURRENCYISOCD
end
Else
begin
SELECT AM.Inventory + T.CUSIPNR [Key], rtrim(T.BRANCHCD) + '-' +
rtrim(T.ACCOUNTNR) BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR,
T.CUSIPNR,
T.ADPSECURITY1DS, T.TDQUANTITY, case when T.TDQuantity <> 0
then T.MarketValAm/T.TDQuantity
else T.SecurityPriceAm end as SecurityPriceAm,
T.LEDGERBALANCEAM, T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
T.DAILYNETPL, T.CURRENCYISOCD
FROM BPS_PositionsHistorical T
JOIN MO_InvAccountMap AM
ON T.BRANCHCD + '-' + T.ACCOUNTNR = AM.AccountNr
JOIN (Select Distinct TraderCd
From MO_InvStrategyMap
Where PrimaryRecFl = 1
and (Desk like @.Load
or Category like @.Load
or SubCategory like @.Load
or TraderName like @.Load
or TraderCd like @.Load)) SM
ON SM.TraderCd = AM.Inventory
WHERE T.BusinessDate = @.AsOfDate
and AM.DataSource like 'BPS%'
and (T.TDMTDSECFEE <> 0
or T.TDCOmsnAm <> 0
or T.TDQUANTITY <> 0
or T.MarketValAm <> 0
or T.MTDNETPL <> 0
or T.MTDPL <> 0
or T.LEDGERBAlanceAm <> 0
or T.DAILYNETPL <> 0
or T.TDDivIntAm <> 0
or T.TDGRCREDITMTDOP7 <> 0)
ORDER BY AM.Inventory, T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD,
T.ADPSECURITYNR, T.CUSIPNR, T.CURRENCYISOCD
end
GO
Assuming this is consistently reproduced, a few things you might want to
investigate:
- Do you have the original (3-minute) query plan? If so, what's different
from the current one?
- Looking at the query plan, where are you spending most of your time?
- During execution, are you seeing any resource constraints? E.g. > 5 avg
disk queue length, high avg disk sec/read,
- Are you seeing waits on cxpackets when you run dbcc sqlperf (waitstats)?
Might indicate parallellized queries waiting.
Suggest you dig into the query plan to see where it's spending most of its
time and check to ensure you don't have resource bottlenecks.
joe.
"DARR" <umariqbaldar@.gmail.com> wrote in message
news:1168992585.559292.8530@.s34g2000cwa.googlegrou ps.com...
> Hi,
> I have a job that execute a stored proc. It was running fine like 3
> minutes to finish but a week ago it started to take 4 hours. Its using
> ad hoc quries in that stored procedure. I reindex all the tables that
> it was using. I check execution plan there was no tabloe scans. I
> recompile the stored proc but no use. its sql 2000 with service pack 3
> installed on it. Here is the query anyone has any clue what went wrong
> suddenly. Link server is working fine.
> i am running it like this
> exec usp_MO_ActivityTradesBPS 'CORP'
>
> CREATE PROCEDURE dbo.usp_MO_PositionsBPS
>
> @.Load varchar(25) = null,
> @.AsOfDate datetime
> AS
> Set @.Load = '%'
> If @.Load is null or @.Load = ''
> begin
> Set @.Load = '%'
> end
> If IsNull(@.AsOfDate,'') = ''
> Set @.AsOfDate = dbo.fn_BusinessDate(GetDate()-1)
> If @.Load = 'SANFRAN'
> begin
> Set @.Load = '%'
> SELECT RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' +
> T.ACCOUNTTYPECD + T.CUSIPNR [key],
> RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' + T.ACCOUNTTYPECD
> BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR, T.CUSIPNR,
> T.ADPSECURITY1DS, T.TDQUANTITY, T.SECURITYPRICEAM, T.LEDGERBALANCEAM,
> T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
> T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
> T.DAILYNETPL, T.CURRENCYISOCD
> FROM BPS_Positions T
> WHERE T.BRANCHCD = '001'
> and T.BRANCHCD + '-' + T.ACCOUNTNR not in (Select Distinct
> SM.Bps_AcctNr
> From MO_InvStrategyMap SM
> Where SM.Bps_AcctNr is not null)
> and (T.TDMTDSECFEE <> 0
> or T.TDCOmsnAm <> 0
> or T.TDQUANTITY <> 0
> or T.MarketValAm <> 0
> or T.MTDNETPL <> 0
> or T.MTDPL <> 0
> or T.LEDGERBAlanceAm <> 0
> or T.DAILYNETPL <> 0
> or T.TDDivIntAm <> 0
> or T.TDGRCREDITMTDOP7 <> 0)
> ORDER BY T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD, T.ADPSECURITYNR,
> T.CUSIPNR, T.CURRENCYISOCD
> end
> Else
> begin
> SELECT AM.Inventory + T.CUSIPNR [Key], rtrim(T.BRANCHCD) + '-' +
> rtrim(T.ACCOUNTNR) BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR,
> T.CUSIPNR,
> T.ADPSECURITY1DS, T.TDQUANTITY, case when T.TDQuantity <> 0
> then T.MarketValAm/T.TDQuantity
> else T.SecurityPriceAm end as SecurityPriceAm,
> T.LEDGERBALANCEAM, T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
> T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
> T.DAILYNETPL, T.CURRENCYISOCD
> FROM BPS_PositionsHistorical T
> JOIN MO_InvAccountMap AM
> ON T.BRANCHCD + '-' + T.ACCOUNTNR = AM.AccountNr
> JOIN (Select Distinct TraderCd
> From MO_InvStrategyMap
> Where PrimaryRecFl = 1
> and (Desk like @.Load
> or Category like @.Load
> or SubCategory like @.Load
> or TraderName like @.Load
> or TraderCd like @.Load)) SM
> ON SM.TraderCd = AM.Inventory
> WHERE T.BusinessDate = @.AsOfDate
> and AM.DataSource like 'BPS%'
> and (T.TDMTDSECFEE <> 0
> or T.TDCOmsnAm <> 0
> or T.TDQUANTITY <> 0
> or T.MarketValAm <> 0
> or T.MTDNETPL <> 0
> or T.MTDPL <> 0
> or T.LEDGERBAlanceAm <> 0
> or T.DAILYNETPL <> 0
> or T.TDDivIntAm <> 0
> or T.TDGRCREDITMTDOP7 <> 0)
> ORDER BY AM.Inventory, T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD,
> T.ADPSECURITYNR, T.CUSIPNR, T.CURRENCYISOCD
> end
> GO
>

Actual and Estimated Rows

I have a stored procedure that will execute with less than 1,000 reads one
time (with a specified set of parameters), then with a different set of
parameters the procedure executes with close to 500,000 reads (according to
Profiler).

In comparing the execution plans, they are the same, except for the actual
and estimated number of rows. When the proc runs with parameters that produce
reads that are less than 1,000 the actual and estimated number of rows equal
1. When the proc runs with parameters that produce reads are near 500,000 the
actual rows are approximately 85,000 and the estimated rows equal 1.

Then I run:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

If I then reverse the order of execution by executing the procedure that
initially executes with close to 500,000 reads first, the reads drop to less
than 2,000. The execution plan shows the acutual number of rows equal to 1,
and the estimated rows equal to 2.27. Then when I run the procedure that
initially executed with less than 1,000 reads, it continues to run at less
than 1,000 reads, and the actual number of rows is equal to 1 and the
estimated rows equal to 2.27. When run in this order, there is consistency in
the actual and estimated number of rows and the reads for both executions
with differing parameters are within reason.

Do I need to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on production
and then ensure that the procedure that ran close to 500,000 reads is run
first to ensure the proper plan, as well as using a KEEP PLAN option? Or,
what other options might you recommend?

I am running SQL 2000 SP4.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200609/1So perhaps a little more information will be helpful.

First of all, this is the query statement that generates the differing actual
versus estimated rows:

(The variables are of type int, sent as arguments to the stored procedure.)

SELECT y.rpFID, x.dCode, x.Desc, x.dcUID
FROM dbo.Table_X x
JOIN dbo.Table_Y y
ON x.dcUID = y.dcFID
AND x.MainID = y.MainID
JOIN dbo.Table_Z z
ON y.rpFID = z.rpUID
AND y.MainID = z.MainID
WHERE z.pFID = @.pID
AND z.MainID = @.MainID
AND x.MainID = @.MainID
AND y.MainID = @.MainID

I have run:
UPDATE STATISTICS dbname.Table_X WITH FULLSCAN
UPDATE STATISTICS dbname.Table_Y WITH FULLSCAN
UPDATE STATISTICS dbname.Table_Z WITH FULLSCAN

When I execute the stored proc with the following arguments and in this order
I get the following results:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
go
exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
go

What this did was drop the reads from 500,000 to 250,000, while the estimated
versus actual dropped on one clustered index seek (on Table_Z) from 85,000
actual and 1 estimated to 4 actual and 1 estimated. On the other hand, the
clustered index seek (on Table_Y) still reports 85,000 actual and 1
estimated.

If I just pull this query out and run on its own, there are 4 records
returned.

When I execute the stored proc with the following arguments and in this order
(note the arguments) I get the following results:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
go
exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
go

What this did was drop the reads from 250,000 to 79, while the estimated
versus actual on one clustered index seek (on Table_Z) remained 4 actual and
1 estimated while the clustered index seek (on Table_Y) dropped from 85,000
actual and 1 estimated to 5 actual and 1 estimated.

Any ideas how to always have the plan optimized to the lower read count, or
stated another way, optimized to the correct actual versus estimated row
counts?

cbrichards wrote:

Quote:

Originally Posted by

>I have a stored procedure that will execute with less than 1,000 reads one
>time (with a specified set of parameters), then with a different set of
>parameters the procedure executes with close to 500,000 reads (according to
>Profiler).
>
>In comparing the execution plans, they are the same, except for the actual
>and estimated number of rows. When the proc runs with parameters that produce
>reads that are less than 1,000 the actual and estimated number of rows equal
>1. When the proc runs with parameters that produce reads are near 500,000 the
>actual rows are approximately 85,000 and the estimated rows equal 1.
>
>Then I run:
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>
>If I then reverse the order of execution by executing the procedure that
>initially executes with close to 500,000 reads first, the reads drop to less
>than 2,000. The execution plan shows the acutual number of rows equal to 1,
>and the estimated rows equal to 2.27. Then when I run the procedure that
>initially executed with less than 1,000 reads, it continues to run at less
>than 1,000 reads, and the actual number of rows is equal to 1 and the
>estimated rows equal to 2.27. When run in this order, there is consistency in
>the actual and estimated number of rows and the reads for both executions
>with differing parameters are within reason.
>
>Do I need to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE on production
>and then ensure that the procedure that ran close to 500,000 reads is run
>first to ensure the proper plan, as well as using a KEEP PLAN option? Or,
>what other options might you recommend?
>
>I am running SQL 2000 SP4.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200609/1|||Adding on to my previous reply, when I execute the procedures in the
following order (getting the high reads and large actual rows), I obtain the
following explain plan:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
go
exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
go

Rows Executes StmtText
-- --- ---
4 1 Nested Loops(Inner Join, OUTER REFERENCES:([y].
[dcFID]) OPTIMIZED)
4 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([y].
[rpFID]) OPTIMIZED)
82331 1 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Y].[IX_MainID_rpdcUID] AS [y]), SEEK:([y].[MainID]=[@.MainID]) ORDERED
FORWARD)
4 82331 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Z].[IX_MainID_rpUID] AS [z]), SEEK:([z].[MainID]=[@.MainID] AND [z].
[rpUID]=[MyDB].[dbo].[Table_Y].[rpFID] as [y].[rpFID]), WHERE:([MyDB].[dbo].
[Table_Z].[pFID] as [z].[pFID]=[@.pID]) ORDERED FORWARD)
4 4 |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_X].[IX_MainID_dcUID] AS [x]), SEEK:([x].[MainID]=[@.MainID] AND [x].
[dcUID]=[MyDB].[dbo].[Table_Y].[dcFID] as [y].[dcFID]) ORDERED FORWARD)

when I execute the procedures in the following order (getting the low number
of reads and low number of actual rows), I obtain the following explain plan:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
go
exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
go

Rows Executes StmtText
-- --- ---
4 1 Nested Loops(Inner Join, OUTER REFERENCES:([y].
[dcFID]) OPTIMIZED)
4 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([z].
[rpUID]))
5 1 | |--Index Seek(OBJECT:([MyDB].[dbo].[Table_Z].
[IDX1_pFID] AS [z]), SEEK:([z].[pFID]=[@.pID]), WHERE:([MyDB].[dbo].[Table_Z].
[MainID] as [z].[MainID]=[@.MainID]) ORDERED FORWARD)
4 5 | |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_Y].[IX_MainID_rpdcUID] AS [y]), SEEK:([y].[MainID]=[@.MainID] AND [y].
[rpFID]=[MyDB].[dbo].[Table_Z].[rpUID] as [z].[rpUID]) ORDERED FORWARD)
4 4 |--Clustered Index Seek(OBJECT:([MyDB].[dbo].
[Table_X].[IX_MainID_dcUID] AS [x]), SEEK:([x].[MainID]=[@.MainID] AND [x].
[dcUID]=[MyDB].[dbo].[Table_Y].[dcFID] as [y].[dcFID]) ORDERED FORWARD)

Please help explain how I might always obtain the second explain plan, if
possible.

cbrichards wrote:

Quote:

Originally Posted by

>So perhaps a little more information will be helpful.
>
>First of all, this is the query statement that generates the differing actual
>versus estimated rows:
>
>(The variables are of type int, sent as arguments to the stored procedure.)
>
>SELECT y.rpFID, x.dCode, x.Desc, x.dcUID
>FROM dbo.Table_X x
>JOIN dbo.Table_Y y
ON x.dcUID = y.dcFID
AND x.MainID = y.MainID
>JOIN dbo.Table_Z z
ON y.rpFID = z.rpUID
AND y.MainID = z.MainID
>WHERE z.pFID = @.pID
>AND z.MainID = @.MainID
>AND x.MainID = @.MainID
>AND y.MainID = @.MainID
>
>I have run:
>UPDATE STATISTICS dbname.Table_X WITH FULLSCAN
>UPDATE STATISTICS dbname.Table_Y WITH FULLSCAN
>UPDATE STATISTICS dbname.Table_Z WITH FULLSCAN
>
>When I execute the stored proc with the following arguments and in this order
>I get the following results:
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>go
>exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
>go
>exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
>go
>
>What this did was drop the reads from 500,000 to 250,000, while the estimated
>versus actual dropped on one clustered index seek (on Table_Z) from 85,000
>actual and 1 estimated to 4 actual and 1 estimated. On the other hand, the
>clustered index seek (on Table_Y) still reports 85,000 actual and 1
>estimated.
>
>If I just pull this query out and run on its own, there are 4 records
>returned.
>
>When I execute the stored proc with the following arguments and in this order
>(note the arguments) I get the following results:
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>go
>exec dbo.dem_sel_combined @.pID = 476, @.MainID = 576003
>go
>exec dbo.dem_sel_combined @.pID = 440, @.MainID = 576021
>go
>
>What this did was drop the reads from 250,000 to 79, while the estimated
>versus actual on one clustered index seek (on Table_Z) remained 4 actual and
>1 estimated while the clustered index seek (on Table_Y) dropped from 85,000
>actual and 1 estimated to 5 actual and 1 estimated.
>
>Any ideas how to always have the plan optimized to the lower read count, or
>stated another way, optimized to the correct actual versus estimated row
>counts?
>

Quote:

Originally Posted by

>>I have a stored procedure that will execute with less than 1,000 reads one
>>time (with a specified set of parameters), then with a different set of

>[quoted text clipped - 27 lines]

Quote:

Originally Posted by

>>
>>I am running SQL 2000 SP4.

--
Message posted via http://www.sqlmonster.com|||cbrichards via SQLMonster.com (u3288@.uwe) writes:

Quote:

Originally Posted by

I have a stored procedure that will execute with less than 1,000 reads
one time (with a specified set of parameters), then with a different set
of parameters the procedure executes with close to 500,000 reads
(according to Profiler).
>
In comparing the execution plans, they are the same, except for the
actual and estimated number of rows. When the proc runs with parameters
that produce reads that are less than 1,000 the actual and estimated
number of rows equal 1. When the proc runs with parameters that produce
reads are near 500,000 the actual rows are approximately 85,000 and the
estimated rows equal 1.
>
Then I run:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
>
If I then reverse the order of execution by executing the procedure that
initially executes with close to 500,000 reads first, the reads drop to
less than 2,000. The execution plan shows the acutual number of rows
equal to 1, and the estimated rows equal to 2.27. Then when I run the
procedure that initially executed with less than 1,000 reads, it
continues to run at less than 1,000 reads, and the actual number of rows
is equal to 1 and the estimated rows equal to 2.27. When run in this
order, there is consistency in the actual and estimated number of rows
and the reads for both executions with differing parameters are within
reason.

So this is what is happening: SQL Server engage in something known as
parameter sniffing. When SQL Server runs a stored procedure for which
there is no plan available in the procedure cache, the optimizer builds
a plan for the procedure. It then uses the actual values of the input
parameters as guidance for the plan.

This can sometimes backfire, for instance if the first invocation is
for an atypical value. It appears that in your case, there is little
difference for one of the input cases, but the balance tips over in
favour of a plan that is detrimental for the other input cases.

The situation is not entirely trivial to handle. There are several
options, but all have their drawbacks.

To start with DBCC FREEPROCACHE, since this affects the entire server, this
is a really bad idea.

If you always want the same plan, you can use an index hint, so that
say

dbo.Table_Z z WITH (INDEX = IDX1_pFID)

to force the use of the non-clustred index. This may still not give
the plan you want, though, as the optimizer can choose some other
combination. (On SQL 2005 you can force the exact plan if you wish.) A
more seroius problem with this approach is that in two years when
the data has changed, another plan is much better. So index hints should
only be used when you are convinced that the index in question is
always the best.

Another alternative is to add WITH RECOMPILE to the procedure definition.
In this case the procedure gets recompiled every time it's called, which
means that you can get a new plan each time. The obvious drawback is
that you get a higher overhead. If the procedure is just that statement,
it's not that big deal, but if the procedure is several hundread lines
long, there is reason for concern.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||cbrichards via SQLMonster.com wrote:

Quote:

Originally Posted by

So perhaps a little more information will be helpful.
>
First of all, this is the query statement that generates the differing actual
versus estimated rows:
>
(The variables are of type int, sent as arguments to the stored procedure.)
>
SELECT y.rpFID, x.dCode, x.Desc, x.dcUID
FROM dbo.Table_X x
JOIN dbo.Table_Y y
ON x.dcUID = y.dcFID
AND x.MainID = y.MainID
JOIN dbo.Table_Z z
ON y.rpFID = z.rpUID
AND y.MainID = z.MainID
WHERE z.pFID = @.pID
AND z.MainID = @.MainID
AND x.MainID = @.MainID
AND y.MainID = @.MainID

Something likely unrelated to your issue (Erland answered that one
already) as it does not change the semantics of your statement but I
though I mention it nevertheless: it seems two of the last lines of this
SQL statement are pretty superfluous as all three tables are joined on
MainID anyway. So I'd leave only "AND x.MainID = @.MainID" in there
because Table_X seems to be your major table.

Ah, and another remark: I find these table and column names highly
unspecific. Changing them to something more meaningful (identifiers are
not restricted to 8 characters) will probably help maintain the
application.

Kind regards

robert

Activity Monitor

When I execute a task against my mssql 2005 server it remains visible in the
activity monitor for up to 10 minutes (status = sleeping. open transactions
= 0, command = awaiting command), I have tried and changed the
commandtimeout = 10, reset the connectiontimeout and still it remains. It
appears that they are consuming resources, my concern is that we have up to
5 million requests a months and as a result the table is exceptionally
large.
Any suggestions would be greatly appreciated.Did you think about connection pooling?
Connection pooling will hold a connection for a specific amount of time for
reuse.
You can disable connection pooling in your connectionstring
...pooling=false...
Don't forget to refresh your activity monitor ;-)
"Tlink" wrote:

> When I execute a task against my mssql 2005 server it remains visible in t
he
> activity monitor for up to 10 minutes (status = sleeping. open transaction
s
> = 0, command = awaiting command), I have tried and changed the
> commandtimeout = 10, reset the connectiontimeout and still it remains. It
> appears that they are consuming resources, my concern is that we have up t
o
> 5 million requests a months and as a result the table is exceptionally
> large.
> Any suggestions would be greatly appreciated.
>
>|||Did you refresh the monitor?
Andrew J. Kelly SQL MVP
"Tlink" <Tlink@.online.nospam> wrote in message
news:eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> When I execute a task against my mssql 2005 server it remains visible in
> the activity monitor for up to 10 minutes (status = sleeping. open
> transactions = 0, command = awaiting command), I have tried and changed
> the commandtimeout = 10, reset the connectiontimeout and still it remains.
> It appears that they are consuming resources, my concern is that we have
> up to 5 million requests a months and as a result the table is
> exceptionally large.
> Any suggestions would be greatly appreciated.
>|||Yes, I set the monitor to refresh ever 10 seconds.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl...
> Did you refresh the monitor?
> --
> Andrew J. Kelly SQL MVP
>
> "Tlink" <Tlink@.online.nospam> wrote in message
> news:eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl...
>|||Yes set pooling=false and rechecked the refresh rate on the activity monitor
and no change it still mounts.
"migeold" <migeold@.discussions.microsoft.com> wrote in message
news:9697B6DF-77A8-43B0-B6C7-1884420764BD@.microsoft.com...
> Did you think about connection pooling?
> Connection pooling will hold a connection for a specific amount of time
> for
> reuse.
> You can disable connection pooling in your connectionstring
> ...pooling=false...
> Don't forget to refresh your activity monitor ;-)
> "Tlink" wrote:
>|||Can you post your connectionstring and some more information to reproduce
your problem, please?
"Tlink" wrote:

> Yes set pooling=false and rechecked the refresh rate on the activity monit
or
> and no change it still mounts.
>
> "migeold" <migeold@.discussions.microsoft.com> wrote in message
> news:9697B6DF-77A8-43B0-B6C7-1884420764BD@.microsoft.com...
>
>|||Here is an extract of the connection string and some data collection. The
average number of records per database table is 5.6 million records, I have
optimised the files and they all contain indexes.
2 connection string
ConnectionString = "Provider=SQLOLEDB.1;"
or
ConnectionString = "Provider=SQLNCLI;"
ConnectionString = ConnectionString & ";MarsConn=yes"
One of the above sets &
ConnectionString = ConnectionString & ";Database=" & dbname
ConnectionString = ConnectionString & ";Uid=" & dbuid
ConnectionString = ConnectionString & ";Pwd=" & dbpassword
ConnectionString = ConnectionString & ";Encrypt=yes"
ConnectionString = ConnectionString & ";Pooling=false"
DBobj.connectionstring = ConnectionString
DBobj.CommandTimeout = 30
dbobj.open
dbobj.commandtimeout = 10
RTSOBJ = dbobj.execute("Select xxx,bbb,ccc,ddd,eee FROM ControlFile where
ooo = 'pppp';")
RTSOBJ2 = dbobj.execute("Select ffff,hhhh,iii,kkkk,llll FROM ControlFile2
where hhhh = 'pppp';")
RTSOBJ3 = dbobj.execute("Select * FROM ControlFile2 where hhhh = 'pppp';")
' all data returned has at least 1 record but may contain up to 100 records
which are processed in a loop
do while RTSOBJ.EOF = FALSE
:
:processed
:
Loop
RTSOBJ.close
RTSOBJ2.close
RTSOBJ3.close
dbobj.close
"migeold" <migeold@.discussions.microsoft.com> wrote in message
news:D2A10843-7293-4E2D-B3AD-5BBB62242B62@.microsoft.com...
> Can you post your connectionstring and some more information to reproduce
> your problem, please?
> "Tlink" wrote:
>|||Hello,
You may want to check sys.sysprocesses view to see if there is any
difference before and after you close the connection.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Tlink" <Tlink@.online.nospam>
>From: "Tlink" <Tlink@.online.nospam>
>References: <eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl>
<uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
>Subject: Re: Activity Monitor
>Date: Fri, 24 Mar 2006 01:07:03 +1100
>Lines: 26
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Response
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#JWRSMoTGHA.1672@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: bus-210-211-121-210.vic.veridas.net 210.211.121.210
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.programming:588412
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Yes, I set the monitor to refresh ever 10 seconds.
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>news:uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl...
in
that
>
>|||Not really sure what I am looking for, sorry can you please provide a little
bit more information.
""privatenews"" <petery@.online.microsoft.com> wrote in message
news:Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> You may want to check sys.sysprocesses view to see if there is any
> difference before and after you close the connection.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> <uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
> in
> that
>|||Hello,
To isolate if the issue is issue in activty monitor, you could monitor the
active process via the following query
select * from master.sys.sysprocesses
Usually if a connection is closed by client program, a process with SPID is
also terminated on server.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Tlink" <Tlink@.online.nospam>
>From: "Tlink" <Tlink@.online.nospam>
>References: <eiqciSmTGHA.4308@.TK2MSFTNGP10.phx.gbl>
<uoFITgnTGHA.5900@.tk2msftngp13.phx.gbl>
<#JWRSMoTGHA.1672@.tk2msftngp13.phx.gbl>
<Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Activity Monitor
>Date: Sat, 25 Mar 2006 18:57:34 +1100
>Lines: 80
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <#mitkH#TGHA.4300@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: bus-210-211-121-210.vic.veridas.net 210.211.121.210
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:588696
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>
>Not really sure what I am looking for, sorry can you please provide a
little
>bit more information.
>
>""privatenews"" <petery@.online.microsoft.com> wrote in message
>news:Sw6WnjuTGHA.4768@.TK2MSFTNGXA01.phx.gbl...
changed
>
>