increasing duration for the same query over time
-
Please excuse the double post for very similar question- I think I was on the wrong track with my previous post and want to pose a more general question.
I traced a process that repeatedly ran the same code IF EXISTS (select * from table where....) over the course of 5 hours. Initially, both duration and CPU were low. Duration about 10,000 microseconds, CPU close to 0. over time both measures gradually increased- running the exact same query. Any thoughts would be appreciated.
-
To post any performance related stuff, please check the article 'How to post performance problems - Gail Shaw]
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Without more detail it's hard to say, but if I had to guess... Is there a query with a sub-optimal execution plan against a table that is either growing in size over time, or is it using an index that is becoming rapidly fragmented? Are there other factors affecting it (blocking, other load on the server, etc.)?
Generally I would start with looking at the execution plan of this proc and go from there.
A.J.
DBA with an attitude -
Definitely not a plan issue- it's reusing the same plan over time. Table is not changing.
It runs in a loop from a connection that remains open and does not do sp_reset_connection. Thinking this is some residual info being carried over or something like that. The proc is executed somethinkg like 200,000 times, so anything that increments could be the culprit.
-
lokeshvij (7/25/2012)
To post any performance related stuff, please check the article 'How to post performance problems - Gail Shaw]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Thanks for this valuable input. If I were free to include DDL I would do so but I can not.
-
As A.J. suggested have you ruled out index fragmentation by running a DMV query ( even though the table has not changed as per your feedback) ?
Also are the no of logical reads for the query remaining constant as well?
Try gathering waittype stats information, that may provide you with some useful clues.
-
Vikrant S Patil (7/25/2012)
As A.J. suggested have you ruled out index fragmentation by running a DMV query ( even though the table has not changed as per your feedback) ?Also are the no of logical reads for the query remaining constant as well?
Try gathering waittype stats information, that may provide you with some useful clues.
Overall logical read rate decreased over time - presumably due to decreased rate of exeuction of the procs. I was not capturing reads per execution during the last run, but I will the next time.
There is no fragmentation issue.
Waits are all SOS_Scheduler_yield related.
-
ok i cannot mention this with a great deal of authority but this points to a possible CPU contention \ pressure on your server.But then we are bound to see some sos_scheduler_yield waittypes even on systems where performance is fine and acceptable so this needs to investigated in detail.
it would really have been nice if we had some baseline stats when the query was running fine so that they could have been compared with current stats.
Following are a couple of URLs that may provide some information (if you already are not aware of the same)
http://blog.sqlauthority.com/2011/02/08/sql-server-sos_scheduler_yield-wait-type-day-8-of-28/[/url]
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/e2ddf26f-f86c-4368-b4a7-17b2b7c64aa5/[/url]
-
NJ-DBA (7/25/2012)
Please excuse the double post for very similar question- I think I was on the wrong track with my previous post and want to pose a more general question.I traced a process that repeatedly ran the same code IF EXISTS (select * from table where....) over the course of 5 hours. Initially, both duration and CPU were low. Duration about 10,000 microseconds, CPU close to 0. over time both measures gradually increased- running the exact same query. Any thoughts would be appreciated.
At least post the rest of the SELECT in the IF EXISTS. Otherwise, we're just guessing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (7/25/2012)
NJ-DBA (7/25/2012)
Please excuse the double post for very similar question- I think I was on the wrong track with my previous post and want to pose a more general question.I traced a process that repeatedly ran the same code IF EXISTS (select * from table where....) over the course of 5 hours. Initially, both duration and CPU were low. Duration about 10,000 microseconds, CPU close to 0. over time both measures gradually increased- running the exact same query. Any thoughts would be appreciated.
At least post the rest of the SELECT in the IF EXISTS. Otherwise, we're just guessing.
object names have been modified below. probably worth noting that this database runs in 8.0 compatability mode on SQL SErver 2008 SP1. The proc is called hundreds of thousands of times from a C# app running it in a loop (the loop is not a within SQL Server). It is reusing the same plan.
The query is:
IF EXISTS (select * from tablename where filename1=@filenameparam and typeidcol = @TypeIdparam and STATUS <> 5)
it's called from a proc like this:
------
CREATE procedure [dbo].[procname]
@TypeIdparam int,
@status int,
@filename2 varchar(500),
@filenameparam varchar(500),
@companyid int,
@remotedbName varchar(100)
AS
declare @nextDId int
declare @msg varchar(500)
declare @anotherId int
BEGIN TRY
begin transaction
-- first, check to see if this remote file name already exists in the inbound_file_control table and has not been pulled
IF EXISTS (select * from tablename where filename1=@filenameparam and typeidcol = @TypeIdparam and STATUS <> 5)
BEGIN
set @msg = 'return a message about: ' + @remoteFileName
RAISERROR (@msg, 16, 1)
END
<do some other stuff that is not relevant. we see this problem when if exists returns true >
END TRY
BEGIN CATCH
rollback transaction
declare @errorMessage nvarchar(4000)
set @errorMessage = ERROR_MESSAGE()
RAISERROR (@errorMessage, 16, 1)
END CATCH
-----
The table involved looks like this:
CREATE TABLE[dbo].[tablename](
idcol [int] IDENTITY(1,1) NOT NULL,
typeidcol [int] NOT NULL,
transferid [int] NOT NULL,
status [tinyint] NOT NULL,
filename1 [varchar](100) NOT NULL,
filename2 [varchar](200) NULL,
parentid [int] NULL,
foriegnid [int] NULL,
errordesc [varchar](1000) NULL,
timecol [datetime] NULL,
lastupdatedbyapp[varchar](50) NOT NULL,
lastupdateddt[datetime] NOT NULL,
companynum [int] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE[dbo].[tablename] ADD [remotedbname] [varchar](100) NULL
/****** Object: Index [PK_tablename] Script Date: 07/26/2012 07:52:55 ******/
ALTER TABLE[dbo].[tablename] ADD CONSTRAINT [PK_tablename] PRIMARY KEY CLUSTERED
(
idcol ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [IX_tablename_01] Script Date: 07/26/2012 07:52:55 ******/
CREATE NONCLUSTERED INDEX [IX_tablename_01] ON[dbo].[tablename]
(
typeidcol ASC,
status ASC,
lastupdateddtASC,
[remotedbname] ASC
)
INCLUDE ( transferid,
filename1) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE[dbo].[tablename] WITH CHECK ADD CONSTRAINT [FK_anothertable1] FOREIGN KEY(foriegnid)
REFERENCES [dbo].[anothertable1] (foriegnid)
GO
ALTER TABLE[dbo].[tablename] CHECK CONSTRAINT [FK_anothertable1]
IF EXISTS (select * from tablename where filename1=@filenameparam and typeidcol = @TypeIdparam and STATUS <> 5)
-
NJ-DBA (7/25/2012)
Vikrant S Patil (7/25/2012)
As A.J. suggested have you ruled out index fragmentation by running a DMV query ( even though the table has not changed as per your feedback) ?Also are the no of logical reads for the query remaining constant as well?
Try gathering waittype stats information, that may provide you with some useful clues.
Overall logical read rate decreased over time - presumably due to decreased rate of exeuction of the procs. I was not capturing reads per execution during the last run, but I will the next time.
There is no fragmentation issue.
Waits are all SOS_Scheduler_yield related.
To add some info, in a subsequent capture, while total logical reads on the server did go down, I added in "reads" to my trace and found that along with duration and CPU for the "if exists", reads went up over time... for the exact same query, using the exact same plan, on a table with very little changes if any.
-
NJ-DBA (7/26/2012)
Jeff Moden (7/25/2012)
NJ-DBA (7/25/2012)
Please excuse the double post for very similar question- I think I was on the wrong track with my previous post and want to pose a more general question.I traced a process that repeatedly ran the same code IF EXISTS (select * from table where....) over the course of 5 hours. Initially, both duration and CPU were low. Duration about 10,000 microseconds, CPU close to 0. over time both measures gradually increased- running the exact same query. Any thoughts would be appreciated.
At least post the rest of the SELECT in the IF EXISTS. Otherwise, we're just guessing.
object names have been modified below. probably worth noting that this database runs in 8.0 compatability mode on SQL SErver 2008 SP1. The proc is called hundreds of thousands of times from a C# app running it in a loop (the loop is not a within SQL Server). It is reusing the same plan.
The query is:
IF EXISTS (select * from tablename where filename1=@filenameparam and typeidcol = @TypeIdparam and STATUS <> 5)
it's called from a proc like this:
------
CREATE procedure [dbo].[procname]
@TypeIdparam int,
@status int,
@filename2 varchar(500),
@filenameparam varchar(500),
@companyid int,
@remotedbName varchar(100)
AS
declare @nextDId int
declare @msg varchar(500)
declare @anotherId int
BEGIN TRY
begin transaction
-- first, check to see if this remote file name already exists in the inbound_file_control table and has not been pulled
IF EXISTS (select * from tablename where filename1=@filenameparam and typeidcol = @TypeIdparam and STATUS <> 5)
BEGIN
set @msg = 'return a message about: ' + @remoteFileName
RAISERROR (@msg, 16, 1)
END
<do some other stuff that is not relevant. we see this problem when if exists returns true >
END TRY
BEGIN CATCH
rollback transaction
declare @errorMessage nvarchar(4000)
set @errorMessage = ERROR_MESSAGE()
RAISERROR (@errorMessage, 16, 1)
END CATCH
-----
The table involved looks like this:
CREATE TABLE[dbo].[tablename](
idcol [int] IDENTITY(1,1) NOT NULL,
typeidcol [int] NOT NULL,
transferid [int] NOT NULL,
status [tinyint] NOT NULL,
filename1 [varchar](100) NOT NULL,
filename2 [varchar](200) NULL,
parentid [int] NULL,
foriegnid [int] NULL,
errordesc [varchar](1000) NULL,
timecol [datetime] NULL,
lastupdatedbyapp[varchar](50) NOT NULL,
lastupdateddt[datetime] NOT NULL,
companynum [int] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE[dbo].[tablename] ADD [remotedbname] [varchar](100) NULL
/****** Object: Index [PK_tablename] Script Date: 07/26/2012 07:52:55 ******/
ALTER TABLE[dbo].[tablename] ADD CONSTRAINT [PK_tablename] PRIMARY KEY CLUSTERED
(
idcol ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [IX_tablename_01] Script Date: 07/26/2012 07:52:55 ******/
CREATE NONCLUSTERED INDEX [IX_tablename_01] ON[dbo].[tablename]
(
typeidcol ASC,
status ASC,
lastupdateddtASC,
[remotedbname] ASC
)
INCLUDE ( transferid,
filename1) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE[dbo].[tablename] WITH CHECK ADD CONSTRAINT [FK_anothertable1] FOREIGN KEY(foriegnid)
REFERENCES [dbo].[anothertable1] (foriegnid)
GO
ALTER TABLE[dbo].[tablename] CHECK CONSTRAINT [FK_anothertable1]
IF EXISTS (select * from tablename where filename1=@filenameparam and typeidcol = @TypeIdparam and STATUS <> 5)
also worth noting this is a high performance system- 24 cores, 120GB SQL Server memory.
Average CPU utilization on the box was under 25% during the run and no single processor spiked above 80%
-
also, we are aware there is a missing index that would cover the "if exists" query, but I dont think a missing index would make the duration increase over time... it would be slower, but constantly slower.
-
last one... I really should have waited and put this all as one post...
The app keeps the connection open for the duration of the run and does not do sp_reset_connection.
-
ok this suggestion may seem a bit out of context but maybe you can execute a Update statistics for all tables with full scan and then attempt executing the proc \ query again.
I mean you are trying hard enough, this may not hurt too much.
Update statistics is an online operation but will take up some resources and cause overhead so you may to schedule it properly After statistics are updated you may possibly end up with a new plan when you execute the proc again
you did mention that the proc was using the same plan that worked fine earlier but updating statistics may still be worth a try.( if you have already not done that)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply