Query Analyzer vs ASP.NET Performance
-
I have a stored procedure that executes in query analyzer with 163,301 reads and a duration of 1.5 seconds. When I execute that same stored procedure via SqlCommand, a timeout occurs after a specified 30 seconds and 19,629,197 reads (If I set the CommandTimeout to 90 seconds, the query still times out). The table has just over 9.7 million rows.
Sql Profiler displays the execution plans as very different.
Is my error on the ASP.NET side or do I need to do something different in SQL to force the better execution plan?
=============================================
using(SqlCommand cmd = new SqlCommand()) {
cmd.Connection = sqlConn;
cmd.CommandText = sp;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
param = cmd.Parameters.Add(new SqlParameter("@OrgID",SqlDbType.Int,4));
param.Direction = ParameterDirection.Input;
param.Value = orgID;
param = cmd.Parameters.Add(new SqlParameter("@StartDate",SqlDbType.DateTime));
param.Direction = ParameterDirection.Input;
param.Value = startDate;
param = cmd.Parameters.Add(new SqlParameter("@EndDate",SqlDbType.DateTime));
param.Direction = ParameterDirection.Input;
param.Value = endDate;
cmd.ExecuteNonQuery();
}
=============================================
CREATE PROCEDURE [dbo].[sp_oReportTotalsFetch]@OrgID int,
@StartDate smalldatetime,
@EndDate smalldatetime
AS
SELECT
ISNULL(SUM(StatLCount), 0) StatLCount,
ISNULL(SUM(StatLSize), 0) StatLSize,
ISNULL(SUM(StatVCount), 0) StatVCount,
ISNULL(SUM(StatVSize), 0) StatVSize,
ISNULL(SUM(StatQCount), 0) StatQCount,
ISNULL(SUM(StatQSize), 0) StatQSize,
ISNULL(SUM(StatLCount) + SUM(StatVCount) + SUM(StatQCount), 0) StatCountTotal,
ISNULL(SUM(StatLSize) + SUM(StatVSize) + SUM(StatQSize), 0) StatSizeTotal
FROM
Stat st WITH(NOLOCK)
JOIN Sub sb WITH(NOLOCK) ON sb.SubID = st.SubID
JOIN Domain d WITH(NOLOCK) ON d.DomainID = sb.DomainID
WHERE
d.OrgID = @OrgID
AND
StatCD >= @StartDate
AND
StatCD < @EndDate
GO
=============================================CREATE TABLE [dbo].[Stat] (
[StatID] [int] IDENTITY (1000, 1) NOT NULL ,
[SubID] [int] NOT NULL ,
[StatQCount] [bigint] NOT NULL ,
[StatQSize] [bigint] NOT NULL ,
[StatLCount] [bigint] NOT NULL ,
[StatLSize] [bigint] NOT NULL ,
[StatVCount] [bigint] NOT NULL ,
[StatVSize] [bigint] NOT NULL ,
[StatCD] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Stat_SubID_StatID] ON [dbo].[Stat]([SubID], [StatID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Stat] ADD
CONSTRAINT [DF_Stat_StatQCount] DEFAULT (0) FOR [StatQCount],
CONSTRAINT [DF_Stat_StatQSize] DEFAULT (0) FOR [StatQSize],
CONSTRAINT [DF_Stat_StatLCount] DEFAULT (0) FOR [StatLCount],
CONSTRAINT [DF_Stat_StatLSize] DEFAULT (0) FOR [StatLSize],
CONSTRAINT [DF_Stat_StatVCount] DEFAULT (0) FOR [StatVCount],
CONSTRAINT [DF_Stat_StatVSize] DEFAULT (0) FOR [StatVSize],
CONSTRAINT [DF_Stat_StatCD] DEFAULT (convert(smalldatetime,floor(convert(float,getdate ())))) FOR [StatCD],
CONSTRAINT [PK_Stat] PRIMARY KEY NONCLUSTERED
(
[StatID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [Stat3] ON [dbo].[Stat]([StatCD], [SubID], [StatQCount]) ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_1522312683_2A_9A_3A] ON [dbo].[Stat] ([SubID], [StatCD], [StatQCount]) ')
GO
ALTER TABLE [dbo].[Stat] ADD
CONSTRAINT [FK_Stat_SubID] FOREIGN KEY
(
[SubID]
) REFERENCES [dbo].[Sub] (
[SubID]
) ON DELETE CASCADE
GO
-
Do this in the proc :
For each parameter, declare a new variable (same type/length/almost same name). Then copy the parameters' value to those variables, then use those variables in the select. Recompile/execute. You should be all set to go.
-
it would be helpful if you actually showed the stored procedure and the table structure.
Also how you called it from query analyzer.
-
Thanks for the responses. I added the stored procedure to my original post.
exec sp_oReportTotalsFetch @OrgID = 1002, @StartDate = 'Jan 1 2005 12:00:00:000AM', @EndDate = 'Jan 1 2006 12:00:00:000AM'
Fortunately or maybe unfortunately, the problem resolved itself about 1 hour after I posted my original message yesterday. I will still gladly welcome any thoughts as to why.
Thanks again for your responses.
-
Remember this if it happens again.
-
Thanks, I am hoping it will happen again so I can try your method.
-
Make sure you catch what are the values of the parameters. This is usually a recreatable situation.
5K
-
This is directed to Remi Gregoire. Can you explain why you should reassign the parameters inside the procedure?
-
Long story short :
You have a stored proc that's run often on the server. So the plan is cached and reused (this is when everything's fine). Now for any number of reason the plan is dropped and the procedure is rerun with a set of unusual parameters. The plan is recompiled/resaved, usually with a scan or 2 in place of seeks. Now the first run is longer than usuall but since it returns more data nobody sees the difference, but when you recall the proc with the normal parameters, the new plan is reused and performance suffers. The use of locally set variables force the server to recompile (or at least recheck) for the best possible plan with is then reused.
Do a search for parameter sniffing, you'll get all the info you need.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply