Hello everyone,
I have a tough problem I've been working on all morning. We have a very
large table which we've divided up into smaller monthly tables and now
access using a partitioned view. Queries on the table have been working
quite well in the past. However, on July 1 we added a new table to the
view to hold July data and now the performance is quite bad.
Queries which access either data up to June 30th run quickly, as does
any query which accesses solely July data. However, when I attempt to
run a query which spans the last few days of June and the first few
days of July, the query time goes from about 8 seconds to over 3
minutes! I do not have this problem when spanning earlier months (ie:
May to June, or April to May).
I've checked the indexes on the new table, and they are identical to
ones on the June table. I've looked at the query plan, but I don't see
anything out of the ordinary. At this point, I am really stumped. Any
suggestions? Let me know what other data I can provide to help diagnose
this.
Thanks!!rich@.adgooroo.com wrote:
> Hello everyone,
> I have a tough problem I've been working on all morning. We have a very
> large table which we've divided up into smaller monthly tables and now
> access using a partitioned view. Queries on the table have been working
> quite well in the past. However, on July 1 we added a new table to the
> view to hold July data and now the performance is quite bad.
> Queries which access either data up to June 30th run quickly, as does
> any query which accesses solely July data. However, when I attempt to
> run a query which spans the last few days of June and the first few
> days of July, the query time goes from about 8 seconds to over 3
> minutes! I do not have this problem when spanning earlier months (ie:
> May to June, or April to May).
> I've checked the indexes on the new table, and they are identical to
> ones on the June table. I've looked at the query plan, but I don't see
> anything out of the ordinary. At this point, I am really stumped. Any
> suggestions? Let me know what other data I can provide to help diagnose
> this.
> Thanks!!
>
Run a query from May 24 - June 7, and one from June 23 - July 2. Run
these together in the same Query Analyzer session, compare the execution
plans of the two. What is different?|||Have you got check constraints on the date column that is being used as the
partitioning column? Are all such check constraints mutually exclusive?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<rich@.adgooroo.com> wrote in message
news:1151867705.674924.96330@.p79g2000cwp.googlegroups.com...
Hello everyone,
I have a tough problem I've been working on all morning. We have a very
large table which we've divided up into smaller monthly tables and now
access using a partitioned view. Queries on the table have been working
quite well in the past. However, on July 1 we added a new table to the
view to hold July data and now the performance is quite bad.
Queries which access either data up to June 30th run quickly, as does
any query which accesses solely July data. However, when I attempt to
run a query which spans the last few days of June and the first few
days of July, the query time goes from about 8 seconds to over 3
minutes! I do not have this problem when spanning earlier months (ie:
May to June, or April to May).
I've checked the indexes on the new table, and they are identical to
ones on the June table. I've looked at the query plan, but I don't see
anything out of the ordinary. At this point, I am really stumped. Any
suggestions? Let me know what other data I can provide to help diagnose
this.
Thanks!!|||Hi,
The check constraints are properly set up on all tables:
June: ([observationtime] >= '6/1/2006' and [observationtime] <='6/30/2006')
July: ([observationtime] >= '7/1/2006' and [observationtime] <='7/31/2006')
I did discover one difference between the tables. The June table had
its indexes on our high-speed write drive. I moved it over to the
high-speed read drive (because the table is no longer being updated),
just as the April and May tables are. However, this did not improve
things any.
When running the two queries together in the same QA session, there are
some differences.
1. The query has a nested SELECT query within it. When running the
May-June query, the July table has a FILTER step applied to it (in the
Q/A execution plan). I assume that this is indicative of the date
constraint being applied. What I don't understand is why this FILTER
step isn't being applied to the April table.
2. When running the June-July query, the July table has an INDEX SEEK
step applied to it. This causes 46% of the execution time. The other
tables have a CLUSTERED INDEX SEEK applied to them, and they are each
accounting for only 2% of the query time. Why would a different index
be used on July?
I've uploaded images of the query plan for you to look at. The June
query is the fast one, while the July query is the slow one.
June query: http://www.goorooreviews.com/June%20query.gif
July query: http://www.goorooreviews.com/July%20query.gif
Thanks again!|||Is the check constraint on the July table trusted? IOW, what do you get
from this:
select
objectproperty (object_id ('the name of the check constraint'),
'CnstIsNotTrusted')
If it is 1, then recreate the check constraint WITH CHECK.
One note - though this should not change anything - I see that your check
constraints are on a column called observationtime. If you are using the
time component of a datetime column, keep in mind that on June 30, for
example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
23:59:59.997.
Could you please extract and post the DDL for each of the June and July
tables - including indexes and constraints?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
Hi,
The check constraints are properly set up on all tables:
June: ([observationtime] >= '6/1/2006' and [observationtime] <='6/30/2006')
July: ([observationtime] >= '7/1/2006' and [observationtime] <='7/31/2006')
I did discover one difference between the tables. The June table had
its indexes on our high-speed write drive. I moved it over to the
high-speed read drive (because the table is no longer being updated),
just as the April and May tables are. However, this did not improve
things any.
When running the two queries together in the same QA session, there are
some differences.
1. The query has a nested SELECT query within it. When running the
May-June query, the July table has a FILTER step applied to it (in the
Q/A execution plan). I assume that this is indicative of the date
constraint being applied. What I don't understand is why this FILTER
step isn't being applied to the April table.
2. When running the June-July query, the July table has an INDEX SEEK
step applied to it. This causes 46% of the execution time. The other
tables have a CLUSTERED INDEX SEEK applied to them, and they are each
accounting for only 2% of the query time. Why would a different index
be used on July?
I've uploaded images of the query plan for you to look at. The June
query is the fast one, while the July query is the slow one.
June query: http://www.goorooreviews.com/June%20query.gif
July query: http://www.goorooreviews.com/July%20query.gif
Thanks again!|||Ok, I figured out a part of the problem. The table itself is on our
high-speed read drive. This means the clustered index is also on the
same drive. However, the index that the query needs was on the
high-speed write drive. Because of this, it wasn't incorporating the
clustered index.
By putting the index on the same drive, the query time has come down to
about 60 seconds. Still too long, but some improvement.
Looking at the query analyzer results, I'm seeing some BOOKMARK LOOKUP
steps. I'm now attempting to rebuild the indexes to make them cover the
query. I don't know how well this will work, because the column that is
missing is an NVARCHAR(255) column, and it is going to add
significantly to the width of the index. But we'll see.|||Having them on the same drive or not doesn't affect whether or not an index
will be used. It depends on the data, the index keys and the index
statistics - given that the query remains the same.
Make sure the indexes are identical across all tables and that the index
stats are current. Be sure that the CHECK constraints have been created
WITH CHECK - not NOCHECK.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151873610.900389.187190@.75g2000cwc.googlegroups.com...
Ok, I figured out a part of the problem. The table itself is on our
high-speed read drive. This means the clustered index is also on the
same drive. However, the index that the query needs was on the
high-speed write drive. Because of this, it wasn't incorporating the
clustered index.
By putting the index on the same drive, the query time has come down to
about 60 seconds. Still too long, but some improvement.
Looking at the query analyzer results, I'm seeing some BOOKMARK LOOKUP
steps. I'm now attempting to rebuild the indexes to make them cover the
query. I don't know how well this will work, because the column that is
missing is an NVARCHAR(255) column, and it is going to add
significantly to the width of the index. But we'll see.|||Hi Tom,
It looks like this is the problem! April-June are all returning 1 as a
result of your query. I updated each constraint with the following SQL:
ALTER TABLE [dbo].[NaturalRank_2006_06] WITH CHECK CHECK constraint
CK_NaturalRank_2006_06
Now all of the tables return 0 (trusted).
The query performs MUCH better as a result. It's now executing in 14
seconds, and now all of the tables have a FILTER step being applied to
them (which is what I would expect).
There are still some more optimizations to be made, but these appear to
be pretty straightforward indexing problems.
Thanks for helping out, this was truly appreciated!!
-Rich
Tom Moreau wrote:
> Is the check constraint on the July table trusted? IOW, what do you get
> from this:
> select
> objectproperty (object_id ('the name of the check constraint'),
> 'CnstIsNotTrusted')
> If it is 1, then recreate the check constraint WITH CHECK.
> One note - though this should not change anything - I see that your check
> constraints are on a column called observationtime. If you are using the
> time component of a datetime column, keep in mind that on June 30, for
> example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
> 23:59:59.997.
> Could you please extract and post the DDL for each of the June and July
> tables - including indexes and constraints?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
> Hi,
> The check constraints are properly set up on all tables:
> June: ([observationtime] >= '6/1/2006' and [observationtime] <=> '6/30/2006')
> July: ([observationtime] >= '7/1/2006' and [observationtime] <=> '7/31/2006')
> I did discover one difference between the tables. The June table had
> its indexes on our high-speed write drive. I moved it over to the
> high-speed read drive (because the table is no longer being updated),
> just as the April and May tables are. However, this did not improve
> things any.
> When running the two queries together in the same QA session, there are
> some differences.
> 1. The query has a nested SELECT query within it. When running the
> May-June query, the July table has a FILTER step applied to it (in the
> Q/A execution plan). I assume that this is indicative of the date
> constraint being applied. What I don't understand is why this FILTER
> step isn't being applied to the April table.
> 2. When running the June-July query, the July table has an INDEX SEEK
> step applied to it. This causes 46% of the execution time. The other
> tables have a CLUSTERED INDEX SEEK applied to them, and they are each
> accounting for only 2% of the query time. Why would a different index
> be used on July?
> I've uploaded images of the query plan for you to look at. The June
> query is the fast one, while the July query is the slow one.
> June query: http://www.goorooreviews.com/June%20query.gif
> July query: http://www.goorooreviews.com/July%20query.gif
> Thanks again!|||My pleasure. As it turns out, I'm working on partitioned views for a
client, so I know where all the bones are buried. :-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Rich" <rich@.adgooroo.com> wrote in message
news:1151874763.783714.81740@.j8g2000cwa.googlegroups.com...
Hi Tom,
It looks like this is the problem! April-June are all returning 1 as a
result of your query. I updated each constraint with the following SQL:
ALTER TABLE [dbo].[NaturalRank_2006_06] WITH CHECK CHECK constraint
CK_NaturalRank_2006_06
Now all of the tables return 0 (trusted).
The query performs MUCH better as a result. It's now executing in 14
seconds, and now all of the tables have a FILTER step being applied to
them (which is what I would expect).
There are still some more optimizations to be made, but these appear to
be pretty straightforward indexing problems.
Thanks for helping out, this was truly appreciated!!
-Rich
Tom Moreau wrote:
> Is the check constraint on the July table trusted? IOW, what do you get
> from this:
> select
> objectproperty (object_id ('the name of the check constraint'),
> 'CnstIsNotTrusted')
> If it is 1, then recreate the check constraint WITH CHECK.
> One note - though this should not change anything - I see that your check
> constraints are on a column called observationtime. If you are using the
> time component of a datetime column, keep in mind that on June 30, for
> example, you can have entries from 2006-06-30 00:00:00 up to 2006-06-30
> 23:59:59.997.
> Could you please extract and post the DDL for each of the June and July
> tables - including indexes and constraints?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1151871743.831829.208000@.a14g2000cwb.googlegroups.com...
> Hi,
> The check constraints are properly set up on all tables:
> June: ([observationtime] >= '6/1/2006' and [observationtime] <=> '6/30/2006')
> July: ([observationtime] >= '7/1/2006' and [observationtime] <=> '7/31/2006')
> I did discover one difference between the tables. The June table had
> its indexes on our high-speed write drive. I moved it over to the
> high-speed read drive (because the table is no longer being updated),
> just as the April and May tables are. However, this did not improve
> things any.
> When running the two queries together in the same QA session, there are
> some differences.
> 1. The query has a nested SELECT query within it. When running the
> May-June query, the July table has a FILTER step applied to it (in the
> Q/A execution plan). I assume that this is indicative of the date
> constraint being applied. What I don't understand is why this FILTER
> step isn't being applied to the April table.
> 2. When running the June-July query, the July table has an INDEX SEEK
> step applied to it. This causes 46% of the execution time. The other
> tables have a CLUSTERED INDEX SEEK applied to them, and they are each
> accounting for only 2% of the query time. Why would a different index
> be used on July?
> I've uploaded images of the query plan for you to look at. The June
> query is the fast one, while the July query is the slow one.
> June query: http://www.goorooreviews.com/June%20query.gif
> July query: http://www.goorooreviews.com/July%20query.gif
> Thanks again!
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment