June 5, 2009 at 6:57 am
I have an isert query that I have been timing with set statistics time on. The times go like this:
221
12
3
3
3
3
3
Now I would expect the first query to take longer as the query plan has not been cached, but why is the second execution slower than all the rest but still faster than the first. It is like it takes two executions for the query to get fully cached. Can someone explain this to me. Thanks in advance.
June 5, 2009 at 7:08 am
Is the insert from a select? If so it could be the select data being in memory.
June 5, 2009 at 7:24 am
No it is just a insert statement with hard coded values for the insert.
June 5, 2009 at 7:54 am
If it's just time, it could be contention, blocking, that made it run longer the second time than the third. It could be that the second time required a page split in order to do the insert and the others did not, again, affecting the time. I could probably think of other reasons for the variation too. Time is never going to be perfectly consistent even with the same procedure on the same server in the same database with the same parameters. Something could always affect it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2009 at 7:57 am
Unfortunately anything we might say is simply guess work. You haven't provided us with the DDL for the table (including any indexes if defined), the insert statements you are timing, or the actual execution plan from the inserts. All of these would help us give you a better answer. Also, to profide the Actual Execution Plan, save it as an *.sqlplan file, zip it and attach it to a post. The rest you should be able to post directly.
June 5, 2009 at 12:53 pm
Here is the DDL for the table and the indexes. The first index is a covering index which is what I was testing to determine if it slowed down the insert query any(doesn't appear to much). The insert query follows the index definitions. The execution plan is simple just an insert with 0 cost and a clustered index insert with 100% cost. I have attached that as well. The issue is the same with and without the covering index, the first execution is the slowest, the second execution is faster, and all the rest are the fastest and about the same. These are all on my local development machine and not on a server so there is no load to speak of.
Note that I inherited the data model...
CREATE TABLE [dbo].[StudentServices](
[ID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
[ServiceText] [varchar](7500) NOT NULL,
[SubArea] [varchar](255) NULL,
[FundingDivision] [int] NULL,
[Narrative] [varchar](max) NULL,
[Rationale] [varchar](max) NULL,
[Disability] [int] NULL,
[GroupSize] [int] NULL,
[ProviderID] [int] NULL,
[TimeSpent] [varchar](16) NULL,
[TimeUnits] [int] NULL,
[TimePeriod] [int] NULL,
[NumSessions] [varchar](16) NULL,
[SessionTimePeriod] [int] NULL,
[PercentOfDay] [float] NULL,
[BeginDate] [smalldatetime] NULL,
[EndDate] [smalldatetime] NULL,
[Location] [int] NULL,
[Departmentalized] [bit] NOT NULL DEFAULT (0),
[Consultation] [bit] NOT NULL DEFAULT (0),
[ExtendedSchoolYear] [bit] NOT NULL DEFAULT (0),
[ServingSchool] [int] NULL,
[Inactive] [bit] NOT NULL DEFAULT (0),
[ServiceClass] [int] NOT NULL,
[ServiceID] [int] NULL,
[AuthorizationBeginDate] [smalldatetime] NULL,
[AuthorizationEndDate] [smalldatetime] NULL,
[TimesBySession] [int] NULL,
[ActTimeSpent] [float] NULL,
[ActNumSessions] [float] NULL,
[DoseAmount] [varchar](16) NULL,
[DoseUnits] [int] NULL,
[Route] [int] NULL,
[ServiceTime] [varchar](5) NULL,
[ServiceHours] [int] NULL,
[ServiceMinutes] [int] NULL,
[ProvidedMonday] [bit] NOT NULL DEFAULT (0),
[ProvidedTuesday] [bit] NOT NULL DEFAULT (0),
[ProvidedWednesday] [bit] NOT NULL DEFAULT (0),
[ProvidedThursday] [bit] NOT NULL DEFAULT (0),
[ProvidedFriday] [bit] NOT NULL DEFAULT (0),
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90)
ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_StID_R_DU_PID_SeID_L_GS_FD_D_SC] ON [dbo].[StudentServices]
(
[StudentID] ASC,
[Route] ASC,
[DoseUnits] ASC,
[ProviderID] ASC,
[ServiceID] ASC,
[Location] ASC,
[GroupSize] ASC,
[FundingDivision] ASC,
[Disability] ASC,
[ServiceClass] ASC
)
INCLUDE ( [ID],
[ServiceText],
[SubArea],
[Narrative],
[Rationale],
[TimeSpent],
[TimeUnits],
[TimePeriod],
[NumSessions],
[SessionTimePeriod],
[PercentOfDay],
[BeginDate],
[EndDate],
[Departmentalized],
[Consultation],
[ExtendedSchoolYear],
[ServingSchool],
[Inactive],
[AuthorizationBeginDate],
[AuthorizationEndDate],
[TimesBySession],
[ActTimeSpent],
[ActNumSessions],
[DoseAmount],
[ServiceTime],
[ServiceHours],
[ServiceMinutes],
[ProvidedMonday],
[ProvidedTuesday],
[ProvidedWednesday],
[ProvidedThursday],
[ProvidedFriday])
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 = 90)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [Indx_StudentID] ON [dbo].[StudentServices]
(
[StudentID] ASC
)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 = 90)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_Disability] ON [dbo].[StudentServices]
(
[Disability] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_DoseUnits] ON [dbo].[StudentServices]
(
[DoseUnits] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_FundingDivision] ON [dbo].[StudentServices]
(
[FundingDivision] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_GroupSize] ON [dbo].[StudentServices]
(
[GroupSize] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_Inactive] ON [dbo].[StudentServices]
(
[Inactive] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_Location] ON [dbo].[StudentServices]
(
[Location] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_ProviderID] ON [dbo].[StudentServices]
(
[ProviderID] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_Route] ON [dbo].[StudentServices]
(
[Route] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_ServiceClass] ON [dbo].[StudentServices]
(
[ServiceClass] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_ServiceID] ON [dbo].[StudentServices]
(
[ServiceID] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_ServingSchool] ON [dbo].[StudentServices]
(
[servingSchool] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_SessionTimePeriod] ON [dbo].[StudentServices]
(
[SessionTimePeriod] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_TimePeriod] ON [dbo].[StudentServices]
(
[TimePeriod] ASC
)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 = 90
)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_StudentServices_TimeUnits] ON [dbo].[StudentServices]
(
[TimeUnits] ASC
)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 = 90
)
ON [PRIMARY]
Thanks in advance!
June 5, 2009 at 12:55 pm
Forgot the insert...
INSERT INTO [StudentServices]
([ID]
,[StudentID]
,[ServiceText]
,[SubArea]
,[FundingDivision]
,[Narrative]
,[Rationale]
,[Disability]
,[GroupSize]
,[ProviderID]
,[TimeSpent]
,[TimeUnits]
,[TimePeriod]
,[NumSessions]
,[SessionTimePeriod]
,[PercentOfDay]
,[BeginDate]
,[EndDate]
,[Location]
,[Departmentalized]
,[Consultation]
,[ExtendedSchoolYear]
,[ServingSchool]
,[Inactive]
,[ServiceClass]
,[ServiceID]
,[AuthorizationBeginDate]
,[AuthorizationEndDate]
,[TimesBySession]
,[ActTimeSpent]
,[ActNumSessions]
,[DoseAmount]
,[DoseUnits]
,[Route]
,[ServiceTime]
,[ServiceHours]
,[ServiceMinutes]
,[ProvidedMonday]
,[ProvidedTuesday]
,[ProvidedWednesday]
,[ProvidedThursday]
,[ProvidedFriday])
VALUES
(601362
,853608
,'This is some text'
,''
,''
,'This is some text'
,'This is some text'
,1
,1
,149
,1700
,1
,1
,''
,''
,20.0
,''
,''
,28
,0
,0
,0
,0
,0
,1
,1
,''
,''
,1
,1
,1
,''
,1
,1
,''
,1
,1
,1
,1
,1
,1
,1)
June 5, 2009 at 1:12 pm
This index, IX_StudentServices_StID_R_DU_PID_SeID_L_GS_FD_D_SC, and this index, Indx_StudentID, are basically the same because the leading edge, the first column is the same. You really don't need the second index.
That's not getting into whether or not an index with EVERYTHING in it is a good idea.
How many different distinct values are there for the Disability column? I suspect this index,IX_StudentServices_Disability, is not going to be used because of the selectivity of the data. Same question & comment for DoseUnits, Inactive and possibly some of the other columns.
Do you select the data by the ID alone very often? If not, you might not want to have the primary key be clustered. It might be worth testing to take the columns that make up the uber-covering index and make those into a clustered index, if that's the most common access path for queries through the table. I think that would work better, despite it's width, than effectively making a copy of the table in that other index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2009 at 1:14 pm
Oh, and the ID column doesn't need to be included with the covering index since it's the clustered key. The key values in a clustered index are always available within a nonclustered index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 5, 2009 at 1:57 pm
Thanks Grant for your observations. That is what I am working on, getting control of the indexes. The tuning adviser recommended the covering index which sped up the view based on this table from 20 - 40%(depending on the box and database), decent improvement. I am now looking at getting rid of some of the other indexes. Your observations will be quite helpful to those ends as I haven't started yet. I was just verifying the insert performance with the new index and posted the question above. Any idea why the first insert query is slow, the second a little faster, then all the rest fast?
Thanks for your help!
June 5, 2009 at 2:32 pm
Steve Slaughter (6/5/2009)
Thanks Grant for your observations. That is what I am working on, getting control of the indexes. The tuning adviser recommended the covering index which sped up the view based on this table from 20 - 40%(depending on the box and database), decent improvement. I am now looking at getting rid of some of the other indexes. Your observations will be quite helpful to those ends as I haven't started yet. I was just verifying the insert performance with the new index and posted the question above. Any idea why the first insert query is slow, the second a little faster, then all the rest fast?Thanks for your help!
Oddly, enough, I have observed this many times myself when doing performance measurements, though much less often in the last year or so. What platform are you doing this on (SQL Server Version, SP#, HW platform)?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 5, 2009 at 2:45 pm
Barry, the OS is XP and SQL Server is 2005 SP2, details below, thanks.
Note that I am seeing the same behavior on our staging server which is W2K3.
Microsoft SQL Server Management Studio9.00.3042.00
Microsoft Analysis Services Client Tools2005.090.3042.00
Microsoft Data Access Components (MDAC)2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer7.0.5730.13
Microsoft .NET Framework2.0.50727.1433
Operating System5.1.2600
June 5, 2009 at 2:50 pm
It's like said before. It could be so many things. Especially with the indexes you have. I suspect page splits are going to be pretty expensive. So some inserts will fly and others will drag. That's not even taking into account resource contention. To be sure what's causing a particular slow down, you have to monitor the wait states and queue's.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply