July 2, 2012 at 10:45 am
Hi All-
I have a query that's been giving me some problems lately and when I took a look at it all the time seems to be spent by an eager spool on the insert portion, not the actual select part. Both Eager spools are on the target table and are indicated as index inserts. This seems really strange to me since I am doing an insert, and not an update. The target table is about 8M rows, and I'm inserting about 1.3M rows. I have a clustered index on the ID column so there should be any reordering involved. The select query takes about a minute to run, with the total insert time taking about 45 minutes. I can drop and recreate the indexes before the insert though I'd rather not have to do that. I've updated all the statistics on the indexes with no help. Another strange thing is that in the query plan the eager spool estimates about 84k rows for the eager spool even though there are 8M+ even after the statistics update.
Any suggestions on things to look at?
Thanks
July 2, 2012 at 11:06 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2012 at 11:35 am
Gail, You can reproduce the same by using the following sql...
drop table a
drop table nums
go
select cnt
into nums
from
(
select ROW_NUMBER() over (order by (select 0)) cnt
from sys.all_columns ac inner join sys.all_columns ac1
on 1=1
) dta where cnt <= 100000
go
create unique clustered index idxnums on nums(cnt)
go
create table a(id int primary key ,col1 int ,col2 int,col3 int,col4 char(300) not null)
go
go
create nonclustered index idxcol1 on a(col1)
go
create nonclustered index idxcol23 on a(col2,col4)
go
select * from a
go
set statistics io,time on
go
insert into a
select cnt,cnt,cnt,cnt,CONVERT(varchar(10),cnt)
from Nums
go
last insert generated following
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 22 ms.
Table 'nums'. Scan count 5, logical reads 637, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'a'. Scan count 0, logical reads 929129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 317795, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 2, 2012 at 11:51 am
I can reproduce eager spools in a hundred ways, but that's not the point. I want to see what the OP is doing so I can advise on exactly his scenario.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2012 at 12:20 pm
This was not so show you, how to reproduce the eager spool. It was similar situation as OP has but at smaller scale like in 100 thousands rather than millions..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 2, 2012 at 12:23 pm
Gullimeel (7/2/2012)
This was not so show you, how to reproduce the eager spool. It was similar situation as OP has but at smaller scale like in 100 thousands rather than millions..
And you know this how? I don't see any code, ddl, or execution plans from the OP, so unless you have a working crystal ball not much can be done but shoot in the dark.
July 2, 2012 at 12:34 pm
I've updated all the statistics on the indexes with no help. Another strange thing is that in the query plan the eager spool estimates about 84k rows for the eager spool even though there are 8M+ even after the statistics update.
Eager spool estimates should be around 13.million rows and this estimates comes from the table from which you are selecting.
How wide are your indexes? Were you able to get the details like physical reads etc? By any chance were you able to get the waits stats?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 2, 2012 at 12:40 pm
Gullimeel (7/2/2012)
--------------------------------------------------------------------------------
This was not so show you, how to reproduce the eager spool. It was similar situation as OP has but at smaller scale like in 100 thousands rather than millions..
And you know this how? I don't see any code, ddl, or execution plans from the OP, so unless you have a working crystal ball not much can be done but shoot in the dark.
Lynn Pettis
I do not have a crystal ball. I just tried to create a table from what he mentioned in the post. Yes it could be tottaly wrong as i used small colums for indexes, he might be having much big column size and might have much more columns in the indexes or might have some included columns. Thus it is not exact situation but it can cause simialr issues. I tried to insert 1.3 million rows but had to cancel the query as my laptop doesnt have that much power and memory etc.. Thus tried at smaller scale..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 2, 2012 at 3:59 pm
Sorry for the delay, been slammed with other stuff today.
Query- Please ignore the syntax. I changed it to ANSI joins as a test and the query plan is still the same so I haven't changed it in the base code yet.
INSERT INTO student_attendance (SiteCode,StudentId,AttendanceDate,Track,Period,InstrMinutes,AttendanceCode,DistrictCode,FiscalYear)
Select track.schoolc AS siteCode, stugrp_active.stuuniq AS studentId, attend.ddate as attendanceDate, track.trackc as Track, trkper.periodn as period, '' as InstrMinutes,
attend.attendc as attendanceCode, 'XXXXX' as DistrictCode , '2011-12' AS FiscalYear FROM DXXXXX..stugrp_active, DXXXXX..stustat, DXXXXX..stusched, DXXXXX..track,
DXXXXX..mstmeet, DXXXXX..mstsched, DXXXXX..facdemo, DXXXXX..trkcycle, DXXXXX..trkper, DXXXXX..trkcal, DXXXXX..trkcrs, DXXXXX..attend
WHERE stusched.suniq = stugrp_active.suniq AND stugrp_active.suniq = stustat.suniq AND stustat.trkuniq = track.trkuniq AND stusched.meetuniq = mstmeet.meetuniq
AND mstmeet.mstuniq = mstsched.mstuniq AND mstsched.trkcrsuniq = trkcrs.trkcrsuniq AND trkcrs.trkuniq = track.trkuniq
AND mstsched.funiq = facdemo.funiq AND mstmeet.cycldayn = trkcycle.cycldayn AND mstmeet.periodn = trkper.periodn
AND track.trkuniq = trkcal.trkuniq AND track.trkuniq = trkcycle.trkuniq AND trkcal.cycldayn = trkcycle.cycldayn AND track.trkuniq = trkper.trkuniq
AND mstmeet.countattnd = 1 AND trkcal.ddate >= stusched.edate AND ( stusched.xdate IS NULL OR trkcal.ddate <= stusched.xdate )
AND trkcal.ddate >= stustat.edate AND ( stustat.xdate IS NULL OR trkcal.ddate <= stustat.xdate )
AND stusched.scduniq = attend.scduniq AND trkcal.ddate = attend.ddate
AND track.schoolc IN ('033','009','020','027','022','050','023','001','004','024','007','002','029','003','035','1996156','028','036','040','032','031','037','039','043','006','005','025','030','041','1995497','042','008')
AND (attend.ddate >= '2010-07-01') AND (track.trackc ='Y' OR track.trackc ='T')
I've also included the execution plan when I ran the query which took 36 minutes, plus the DDL for the table and the indexes.
July 2, 2012 at 4:03 pm
Rogman (7/2/2012)
Sorry for the delay, been slammed with other stuff today.Query- Please ignore the syntax. I changed it to ANSI joins as a test and the query plan is still the same so I haven't changed it in the base code yet.
INSERT INTO student_attendance (SiteCode,StudentId,AttendanceDate,Track,Period,InstrMinutes,AttendanceCode,DistrictCode,FiscalYear)
Select track.schoolc AS siteCode, stugrp_active.stuuniq AS studentId, attend.ddate as attendanceDate, track.trackc as Track, trkper.periodn as period, '' as InstrMinutes,
attend.attendc as attendanceCode, 'XXXXX' as DistrictCode , '2011-12' AS FiscalYear FROM DXXXXX..stugrp_active, DXXXXX..stustat, DXXXXX..stusched, DXXXXX..track,
DXXXXX..mstmeet, DXXXXX..mstsched, DXXXXX..facdemo, DXXXXX..trkcycle, DXXXXX..trkper, DXXXXX..trkcal, DXXXXX..trkcrs, DXXXXX..attend
WHERE stusched.suniq = stugrp_active.suniq AND stugrp_active.suniq = stustat.suniq AND stustat.trkuniq = track.trkuniq AND stusched.meetuniq = mstmeet.meetuniq
AND mstmeet.mstuniq = mstsched.mstuniq AND mstsched.trkcrsuniq = trkcrs.trkcrsuniq AND trkcrs.trkuniq = track.trkuniq
AND mstsched.funiq = facdemo.funiq AND mstmeet.cycldayn = trkcycle.cycldayn AND mstmeet.periodn = trkper.periodn
AND track.trkuniq = trkcal.trkuniq AND track.trkuniq = trkcycle.trkuniq AND trkcal.cycldayn = trkcycle.cycldayn AND track.trkuniq = trkper.trkuniq
AND mstmeet.countattnd = 1 AND trkcal.ddate >= stusched.edate AND ( stusched.xdate IS NULL OR trkcal.ddate <= stusched.xdate )
AND trkcal.ddate >= stustat.edate AND ( stustat.xdate IS NULL OR trkcal.ddate <= stustat.xdate )
AND stusched.scduniq = attend.scduniq AND trkcal.ddate = attend.ddate
AND track.schoolc IN ('033','009','020','027','022','050','023','001','004','024','007','002','029','003','035','1996156','028','036','040','032','031','037','039','043','006','005','025','030','041','1995497','042','008')
AND (attend.ddate >= '2010-07-01') AND (track.trackc ='Y' OR track.trackc ='T')
I've also included the execution plan when I ran the query which took 36 minutes, plus the DDL for the table and the indexes.
I have to ask, are you using Zangle? The table and column names are just too familiar.
July 2, 2012 at 4:34 pm
Lynn Pettis (7/2/2012)
I have to ask, are you using Zangle? The table and column names are just too familiar.
That would be a safe assumption, though we're not the ones using it, several of our customers are. I'm assuming that's what they used at Colorado Springs.
July 2, 2012 at 4:38 pm
Rogman (7/2/2012)
Lynn Pettis (7/2/2012)
I have to ask, are you using Zangle? The table and column names are just too familiar.
That would be a safe assumption, though we're not the ones using it, several of our customers are. I'm assuming that's what they used at Colorado Springs.
Last I heard, D11 still is. Been almost 2 years since I left the district and I can't fully remember the how the tables relate any more.
I am curious why you are linking to facdemo when it isn't used un the select itself. Is it to get only records with teachers?
July 2, 2012 at 4:52 pm
Lynn Pettis (7/2/2012)
Last I heard, D11 still is. Been almost 2 years since I left the district and I can't fully remember the how the tables relate any more.
I am curious why you are linking to facdemo when it isn't used un the select itself. Is it to get only records with teachers?
To be perfectly honest I haven't really scrutinized the query itself since it returns relatively quickly and is supposedly a straight port over from another system that was working and is maintained by someone else. I was really just trying to figure out the performance problem on the insert part. I'll have to take a look at the query in more detail when I get a chance.
July 3, 2012 at 12:24 am
Why everything is defined as nvarchar(50) like fiscalyear could be a smallint similarly others. nvarchar takes double space the corresponding varchar. You might need nvarchar for some columns for others try int,smallint or varchar fields.
Also you have 2 very big indexes.One has 10 columns and other has 9 columns .Table just has 12 columns. Thus you kind of inserting into 3 big tables 1.3 million rows. I am sure you have created these indexes for your select performance so that you have covering indexes.
I can not see the plan..
Thus I would suggest that revist the table column data type so that overall table and index size reduces. This will reduce the size of the temp table created for the eager spool and thus will make the perfromance better.
Why do you have fillfactor 70 for all indexes? In your case it doesnt make much sense specially for id column as that is ascending. Do you do inserts during day time as well ,like small inserts? Other index might get filled during the load,similarly last pages on the table(clustered index) will be full but already exsting pages might still have 30% page space for clustered index.
I doubt you will have quite a lot fragmenetation.What is the fragmentation of your indexes?
If fragmentation is high I would suggest that you follow the strategy of dropping indexes, loading table (maybe using minimal logging features in the sql 2k8) and then creating the indexes but with 100 fillfactor If you have inserts during day time as welll.Then you can have say around 95 as fillfactor.
Also, as you mentioned that the number of estimated rows are 84 K whereas actual are 1.3 million rows..Try to update the statistics for tables used in select statement.
Also, 1.3 million rows insert will produce a huge log and thus might put pressure on the log writer and log write to disk are serial and this could be another bottleneck. If you are running 2k8 then try extended events to capture all the wait events and see which of them is causing the issues.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 10:38 am
Gullimeel (7/3/2012)
Why everything is defined as nvarchar(50) like fiscalyear could be a smallint similarly others. nvarchar takes double space the corresponding varchar. You might need nvarchar for some columns for others try int,smallint or varchar fields.
Unfortunately we don't have a lot of control (ok, almost none) over the type of data that the customers can put in those fields. That's controlled by the verdors. They can, and do, put just about any type of data into the fields that they want. Even date fields don't have data typing or bounds checking so we get just about any type of data in any column. Believe me I wish it were different. If we strongly type the columns we end up rejecting quite a bit of data.
Also you have 2 very big indexes.One has 10 columns and other has 9 columns .Table just has 12 columns. Thus you kind of inserting into 3 big tables 1.3 million rows. I am sure you have created these indexes for your select performance so that you have covering indexes.
That is something I need to revisit. One of the indexes is one that was suggested by the tuning wizard. The other was created by another developer. I'll take another look at the indexes and see if they are really needed and adequate.
I can not see the plan..
Thus I would suggest that revist the table column data type so that overall table and index size reduces. This will reduce the size of the temp table created for the eager spool and thus will make the perfromance better.
I was able to open the plan fine of my comp. Not sure why you can't see it.
Why do you have fillfactor 70 for all indexes? In your case it doesnt make much sense specially for id column as that is ascending. Do you do inserts during day time as well ,like small inserts? Other index might get filled during the load,similarly last pages on the table(clustered index) will be full but already exsting pages might still have 30% page space for clustered index.
I doubt you will have quite a lot fragmenetation.What is the fragmentation of your indexes?
If fragmentation is high I would suggest that you follow the strategy of dropping indexes, loading table (maybe using minimal logging features in the sql 2k8) and then creating the indexes but with 100 fillfactor If you have inserts during day time as welll.Then you can have say around 95 as fillfactor.
This is a high transaction table with lots of reads and then bulk data loads with millions of rows being inserted at a time. We also delete all the previous information for the customer and load in the information. Unfortunately we end up loading in a lot of the same information every time, though that information could have changed and new data would also be inserted. So we have lots of deletes on the table which leads to a high amount of fragmentation. We rebuild indexes weekly so in the production system fragmentation is relatively low, but in our test system we had fragmentation of 80% before I rebuilt the indexes.
Also, as you mentioned that the number of estimated rows are 84 K whereas actual are 1.3 million rows..Try to update the statistics for tables used in select statement.
Also, 1.3 million rows insert will produce a huge log and thus might put pressure on the log writer and log write to disk are serial and this could be another bottleneck. If you are running 2k8 then try extended events to capture all the wait events and see which of them is causing the issues.
Actually, the target table has over 8M rows in it (47M in production) and we're inserting about 1.3M. The select tables are dropped and reloaded very time we import data and we rebuild the indexes after we load the data.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply