July 13, 2011 at 5:51 am
The DBA for this machine is out, and the developers are telling me that a query is taking 24 minutes that used to take under a minute to run. The same query runs in seconds on the TEST machine, but runs very long on DEV. This is on a server/db that I'm not familiar with.
I eyeballed the DDL of both test and dev, and they look the same. I updated statistics on the four tables involved in the query -- no change. I The execution plan on both TEST and DEV looks exactly the same to me.
Where do I need to look next?
Thanks,
Rob
Attached is query, execution plan and table structure
July 13, 2011 at 5:57 am
Can you just try to recompile the sp . Meanwhile we would be able to check the execution plan.
July 13, 2011 at 6:00 am
Have you tried running each of the three select statements outside of the UNION? It will help identify which if any of them is the cause to start from.
MCITP SQL 2005, MCSA SQL 2012
July 13, 2011 at 6:02 am
sqlzealot-81 (7/13/2011)
Can you just try to recompile the sp . Meanwhile we would be able to check the execution plan.
I don't think this is a stored proc (As I don't see any user defined stored procedures under the Programmability folder); I've just been running the SELECT in SSMS.
July 13, 2011 at 6:09 am
what a great job posting everything we might need; thank you for the actual execution plan!
i see a few places where the statistics are a little off; usually an order of magnitude off is what i'd look for , but this is only 2 or 3 times the estimated vs actual;
for now, do this on these three tables:
UPDATE STATISTICS dbo.[SCHOOL] WITH FULLSCAN ;
UPDATE STATISTICS dbo.[MEAP_SCORE] WITH FULLSCAN ;
UPDATE STATISTICS dbo.[STUDENT] WITH FULLSCAN ;
I'll keep looking, it's a pretty big plan to review.
Lowell
July 13, 2011 at 7:13 am
RTaylor2208 (7/13/2011)
Have you tried running each of the three select statements outside of the UNION? It will help identify which if any of them is the cause to start from.
I did the update statistics as suggested by Lowell.
UPDATE STATISTICS dbo.[SCHOOL] WITH FULLSCAN ;
UPDATE STATISTICS dbo.[MEAP_SCORE] WITH FULLSCAN ;
UPDATE STATISTICS dbo.[STUDENT] WITH FULLSCAN ;
Breaking down into parts is a good suggestion; so I ran the three pieces separately (attached is the execution plan for each part). The first part of the query runs in seconds; the second part of the query takes 2-3 minutes and the third part takes 25 minutes.
I broke out the three parts into a text editor and did a diff between them, and they are basically the same -- with minor differences in the WHERE clause. And that explains why the execution plans look the same for each part. The 3rd part (slowest) looks to be pulling for all non-private schools rather than narrowing down for a specific district or ISD.
Looking at the execution plan for part 3 looks like the biggest cost (80%) is for a Key Lookup on a PK clustered index on dbo.[MEAP_SCORE]. If there's already an index, then how do I improve on that?
Thanks for everyone's help so far,
Rob
July 13, 2011 at 7:41 am
90% + of the plan is caused by the seek + bookmark lookup on that index IX_UIC.
Are you able to add those 3 columns in the included part of the index?
math_pl
school_id
school_year_id
In theory that should whack the 80% bookmark lookup.
Please post the new plan for that query once you're done.
Edit : I was wrong, the columns are better off in the index directly rather than include.
July 13, 2011 at 8:23 am
Ninja's_RGR'us (7/13/2011)
90% + of the plan is caused by the seek + bookmark lookup on that index IX_UIC.Are you able to add those 3 columns in the included part of the index?
math_pl
school_id
school_year_id
In theory that should whack the 80% bookmark lookup.
I dropped and created the index adding the three columns from dbo.[MEAP_SCORE]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MEAP_SCORE]') AND name = N'IX_UIC')
DROP INDEX [IX_UIC] ON [dbo].[MEAP_SCORE] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [IX_UIC] ON [dbo].[MEAP_SCORE]
(
[uic] ASC,
[math_pl] ASC,
[school_id] ASC,
[school_year_id] 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) ON [PRIMARY]
GO
Rerunning the 3rd part of the query drops the time from 25 minutes to 2 1/2 minutes. (execution plan from after index update attached)
The whole (all three union'd) parts is down to 5 minutes!! Getting better 🙂
Another piece to the puzzle if this matters; counts on the four tables used:
dbo.[DISTRICT] - 922 records
dbo.[SCHOOL] - 7,268 records
dbo.[MEAP_SCORE] - 5,420,172
dbo.[STUDENT] - 16,789,068
July 13, 2011 at 8:48 am
Still 40% on bookmark lookup...
This should whack it completely (keep in mind that I'm guessing on the columns order, you need to put them in the best order possible (the columns with the most distinct values in front of the index).
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MEAP_SCORE]') AND name = N'IX_UIC')
DROP INDEX [IX_UIC] ON [dbo].[MEAP_SCORE] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [IX_UIC] ON [dbo].[MEAP_SCORE]
(
[uic] ASC,
[math_pl] ASC,
[school_id] ASC,
[school_year_id] ASC,
[GRADE] ASC,
[SE] 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) ON [PRIMARY]
GO
Run the query and see if you still have this warning on the operator that uses the index. IIRC you can solve it by right-click and create stats right from there.
Missing stats warning on this column : [data4ss].[dbo].[MEAP_SCORE].math_pl
July 13, 2011 at 8:49 am
I think The Ninja meant for you to add those columns to the included part of the index, rather than to the index key itself like this?
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MEAP_SCORE]') AND name = N'IX_UIC')
DROP INDEX [IX_UIC] ON [dbo].[MEAP_SCORE] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [IX_UIC] ON [dbo].[MEAP_SCORE]
(
[uic] ASC)
INCLUDE (
[math_pl] ASC,
[school_id] ASC,
[school_year_id] 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) ON [PRIMARY]
GO
This probably won't change the speed of the query any quicker (it may even make it a bit slower) but will make the index smaller which will make it easier to maintain
July 13, 2011 at 8:51 am
quan23 (7/13/2011)
I think The Ninja meant for you to add those columns to the included part of the index, rather than to the index key itself like this?
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MEAP_SCORE]') AND name = N'IX_UIC')
DROP INDEX [IX_UIC] ON [dbo].[MEAP_SCORE] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [IX_UIC] ON [dbo].[MEAP_SCORE]
(
[uic] ASC)
INCLUDE (
[math_pl] ASC,
[school_id] ASC,
[school_year_id] 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) ON [PRIMARY]
GO
This probably won't change the speed of the query any quicker (it may even make it a bit slower) but will make the index smaller which will make it easier to maintain
Actually the advantage of using include is to have much less page splits and cpu cycles. The 2nd point is less page reads while seeking the index.
However in this case with all those columns in the where clause he's better off with them outside the include.
July 13, 2011 at 8:52 am
Any reasons you don't have a clustered index on Schools and SCHOOL_YEARS?
July 13, 2011 at 8:53 am
I stand corrected :blush:
July 13, 2011 at 8:59 am
Ninja's_RGR'us (7/13/2011)
Any reasons you don't have a clustered index on Schools and SCHOOL_YEARS?
Good questions; both SCHOOL and SCHOOL_YEAR tables (and quite a number of other tables) have PK that are unique and non-clustered. I'm not sure why that would be; but it seems go against my (admittedly limited) understanding of indexes.
Since the clustered index is the physical order on the disk, anything using that PK index basically can read the data when it accesses the index which should speed up performance. Is that the basic idea?
July 13, 2011 at 9:01 am
Ninja's_RGR'us (7/13/2011)
Any reasons you don't have a clustered index on Schools and SCHOOL_YEARS?
No you had read what I meant correctly... but that was wrong too :w00t:.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply