Losing rows from file to destination table - need troubleshooting help

I am losing data from time to time and can not figure out where the rows are vanishing to. In essence, I have 5 files that I process. The processing occurs on a daily basis by one SQLAgent job that calls 5 individual packages, each doing a different piece of work.

I stage each file to a temp table, then perform some minor transformations, then union the rowsets from each file before landing to another temporary table. A subsequent package performs additional transformations that apply to the entire dataset, before inserting them to their final destination. Along the way, I reject some records based on the business rules applied. Each package in the entire Job is logging(OnError, TaskFailed, Pre and Post execute). Their are no errors being generated. No rows are being rejected to my reject tables either.

Without getting into the specific transforms, etc. being used in this complex process, has anyone seen similar unexplained behaviour? I've not been able to identify any pattern, except that it is usually only 1 or 2 of the record types (specific to a source file), that will ever not be loaded. No patterns around volumes for specific source files. There are some lookups and dedupes, however I've seen the records 'drop off' before reaching these transforms. I have noticed I had my final desination load not using fast load. However sometimes the records disappear before even getting to my final staging table which is inserting using fast load. I am going to turn on logging the pipelinerowssent event. Any other suggestions for troubleshooting/tracking down these disappearing records?

Thanks

Couple more clarifications:

I have run the same files thru manually in debug mode to find that I can watch all the rows thru the entire process.

We have seen strange behaviour in running packages as scheduled jobs thru SQLAgent

Utilizes unions which seem a bit klunky.

|||

Joe,

We have seen similar problems on my current project so yesterday we turned on OnPipelineRowsSent logging.

Another thing we have done is output the data from each transform component to a file for later examination. The MULTICAST transform is invaluable in order to do this.

as yet we haven't found out what is going on. Its strange.

-Jamie

|||

I'm not sure I would use the adjective strange, but..

In trying to troubleshoot this process, I first changed the union tranform that was taking 6 input streams, and busted it out to 5 individual waterfalling unions each with 2 input streams. No change in behaviour.

I then changed the package that moves this data, by adding in multicasts to output to a file after every transform along the way up to the final destination, after the 5 unions. Just by adding the multicasts into the flow has resulted in no rows vanishing for the daily loads for the past week. Unfortunately, I don't have time to really troubleshoot further, but I think that this demonstrates that there is indeed a serious bug here. I still suspect it has to do with the union transform. I am quite scared for anyone else's shop that has decided to standardize ETL to this tool, as we have. As developers, we have only time to test our code, not testing that the native tool functionality is behaving as expected. In addition ,to have to monitor on a regular basis that it is performing properly, is not acceptable.

Hoping this problem magically went away with SP1....

JH

looking for some hints on SP performance

I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JD
does it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon
|||I suggest you first read up on the difference between constants, parameters and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with this.
> Thanks,
> JD
>
|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARCHAR(8),@.ST ART_DATE),101),CONVERT(DATETIME,CONVERT(VARCHAR(8) ,@.END_DATE),101))
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1,CHARINDEX (',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1,LEN(@.USER S))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('\',Resou rces.LogonName)+1),LEN(Resources.LogonName)-CHARINDEX('\',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('\',U.LogonName)+ 1),LEN(U.LogonName)-CHARINDEX('\',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>
|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>

looking for some hints on SP performance

I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JDdoes it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon|||I suggest you first read up on the difference between constants, parameters
and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is crea
ted and estimates
selectivity. Plan is created based on that and re-used (even if not optimal
for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average
of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equa
ls returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...reen">
> I'm trying to figure out why a SQL statement will run faster in a query wi
ndow then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5
minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting
it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I c
heck it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless index
es have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with thi
s.
> Thanks,
> JD
>|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARC
HAR(8),@.START_DATE),101),CONVERT(DAT
ETIME,CONVERT(VARCHAR(8),@.END_DATE),101)
)
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1
,CHARINDEX(',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1
,LEN(@.USERS))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('',Resources.LogonName)+1),LEN(Res
ources.LogonName)-CHARINDEX('',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('',SSIU.LogonName)+1),LEN(SSIU.LogonNam
e)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('',SSIU.LogonName)+1),LEN(SSIU.LogonNam
e)-CHARINDEX('',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('',U.LogonName)+1),LEN(U.LogonName)-CHARIN
DEX('',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>

looking for some hints on SP performance

I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JDdoes it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon|||I suggest you first read up on the difference between constants, parameters and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with this.
> Thanks,
> JD
>|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARCHAR(8),@.START_DATE),101),CONVERT(DATETIME,CONVERT(VARCHAR(8),@.END_DATE),101))
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1,CHARINDEX(',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1,LEN(@.USERS))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('\',Resources.LogonName)+1),LEN(Resources.LogonName)-CHARINDEX('\',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.LogonName)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.LogonName)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('\',U.LogonName)+1),LEN(U.LogonName)-CHARINDEX('\',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>> I'm trying to figure out why a SQL statement will run faster in a query
>> window then as a stored procedure. In a query window the SQL runs in 2
>> seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
>> cross a database select, so I'm not sure of posting it here in the group.
>> I've looked at Procedure cache seems to be more then enough but how do I
>> check it to be sure?
>> I've updates statistics but, that hasn't made any difference.
>> There are indexes that are being used, so I think that is ok. Unless
>> indexes have different affects on a interactive query vs. a SP?
>> I'm open to any other options that I can look at that may help me with
>> this.
>> Thanks,
>> JD
>