A question about execution plans

Hello, I'm using SQL 2000 with the latest updates.
I have a large table Call_Record (5 million rows) that has three indexes:
1. A clustered index on account_no ASC, date_start DESC
2. A non-clustered index on date_end DESC
3. A non-clustered index on call_record_id ASC
I'm querying it for failed calls in the last hour:
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
The problem is that the execution plan shows that it is using index 1 to
perform this query, whereas index 2 is clearly the best choice. If I
replace the call to GetUtcDate() with a literal date constant like so:
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE (c.date_end >= '2005/11/23')
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
then it does use index (2) as expected, and executes in a fraction of
the time. My question is, why does it pick the "incorrect" index for
the first query, and is there any way to force it to pick index (2)?
MikeMike
I tried to rewrite a little bit your SELECT
DBCC FREEPROCCACHE
GO
SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
D.description AS disconnect_reason
FROM dbo.Call_Record c
INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
WHERE c.date_end >=dateadd(hour,-1,GetUtcDate()) and c.date_end <
dateadd(day,+1,GetUtcDate()) --replace with the date that is relevant for
the searching
(GetUtcDate() - (1.0/24.0)) )
AND D.is_failure = 1
GROUP BY C.master_id_carrier, C.location_name, D.description
Do you see now any changes in the execution plan , I'd put the CI on
date_end column since your criteria is based on range date seraching and CI
is probably a good choice for it, but you'll have to test it.
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:%23ibexQI8FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Hello, I'm using SQL 2000 with the latest updates.
> I have a large table Call_Record (5 million rows) that has three indexes:
> 1. A clustered index on account_no ASC, date_start DESC
> 2. A non-clustered index on date_end DESC
> 3. A non-clustered index on call_record_id ASC
> I'm querying it for failed calls in the last hour:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> The problem is that the execution plan shows that it is using index 1 to
> perform this query, whereas index 2 is clearly the best choice. If I
> replace the call to GetUtcDate() with a literal date constant like so:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= '2005/11/23')
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> then it does use index (2) as expected, and executes in a fraction of the
> time. My question is, why does it pick the "incorrect" index for the
> first query, and is there any way to force it to pick index (2)?
> Mike|||Mike Chamberlain (none@.hotmail.com) writes:
> I have a large table Call_Record (5 million rows) that has three indexes:
> 1. A clustered index on account_no ASC, date_start DESC
> 2. A non-clustered index on date_end DESC
> 3. A non-clustered index on call_record_id ASC
> I'm querying it for failed calls in the last hour:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= (GetUtcDate() - (1.0/24.0)) )
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> The problem is that the execution plan shows that it is using index 1 to
> perform this query, whereas index 2 is clearly the best choice. If I
> replace the call to GetUtcDate() with a literal date constant like so:
> SELECT COUNT(*) AS failure_count, C.master_id_carrier, C.location_name,
> D.description AS disconnect_reason
> FROM dbo.Call_Record c
> INNER JOIN dbo.Disconnect_Code D ON C.disconnect_code = D.code
> WHERE (c.date_end >= '2005/11/23')
> AND D.is_failure = 1
> GROUP BY C.master_id_carrier, C.location_name, D.description
> then it does use index (2) as expected, and executes in a fraction of
> the time. My question is, why does it pick the "incorrect" index for
> the first query, and is there any way to force it to pick index (2)?
When making the choice between scanning a clustered index, or using a
non-clustered index + bookmark lookup, the optimizer always have a
delicate choice. If the condition on the column in the NC-index hits
few rows is small, the NC index is good. But if the condition hits many
rows, the NC index is a lot worse than the table scan, as SQL Server
would have to access many data pages more than once.
To determine which to use, SQL Server makes estimates from statistics
saved for the table. When you put in a date literal, SQL Server can see
that the query will only hit a small number of rows, and thus the index
is good.
But for the first query, the problem is that getutcdate() is a non-
deterministic function, and thus will return different values each
time. I guess, therefore, the optimizer does not care about the
expression, but uses the clustered index instead. Since you have a
condition with >= there could potentially be many rows that are
hit in the condition.
In a situation like this an index hint may be a good idea:
FROM dbo.Call_Record c WITH (INDEX = DateEnd_ix)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisRI got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);

> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> and
> to
> it,
> them
>|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> You can test the partition function using the $PARTITION and sample
values:
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
>
the[vbcol=seagreen]
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
(PF)[vbcol=seagreen]
and[vbcol=seagreen]
know[vbcol=seagreen]
>

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisR
I got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>
|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);

> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> and
> to
> it,
> them
>
|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> You can test the partition function using the $PARTITION and sample
values:[vbcol=seagreen]
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
the[vbcol=seagreen]
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
(PF)[vbcol=seagreen]
and[vbcol=seagreen]
know
>

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisRI got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
>> Howdy all. I've got a couple questions regarding Partition Function (PF)
> and
>> Partition Scheme (PS) and I was wondering if someone could assist?
>> 1. How do I assign a table to a Partition Scheme? I created a test PF and
>> PS, but how could they really partition my table if the table doesnt know
> to
>> use them? I know I can assign the table to a filegroup that has a PS on
> it,
>> but didn't know if that alone would cut it?
>> 2. Is there some place I can verify all my settings once I think I have
> them
>> right?
>> 3. Would it ever make sense to partition an index without partitioning
>> the
>> table it resides on?
>> TIA, ChrisR
>>
>|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> > 2. Is there some place I can verify all my settings once I think I have
> > them right?
> You can test the partition function using the $PARTITION and sample
values:
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
> > 3. Would it ever make sense to partition an index without partitioning
the
> > table it resides on?
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
> >I got number 1, but could still use help with 2 and 3. Here is number 1.
> >
> > CREATE PARTITION FUNCTION myRangePF2 (int)
> >
> > AS RANGE LEFT FOR VALUES (1, 100, 1000);
> >
> > GO
> >
> >
> >
> > CREATE PARTITION SCHEME myRangePS2
> >
> > AS PARTITION myRangePF2
> >
> > TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> >
> > [TransactionNumber] [int] NOT NULL)
> >
> > ON myRangePS2 (TransactionNumber)
> >
> >
> >
> >
> >
> >
> >
> > "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> > news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> >> Howdy all. I've got a couple questions regarding Partition Function
(PF)
> > and
> >> Partition Scheme (PS) and I was wondering if someone could assist?
> >>
> >> 1. How do I assign a table to a Partition Scheme? I created a test PF
and
> >> PS, but how could they really partition my table if the table doesnt
know
> > to
> >> use them? I know I can assign the table to a filegroup that has a PS on
> > it,
> >> but didn't know if that alone would cut it?
> >>
> >> 2. Is there some place I can verify all my settings once I think I have
> > them
> >> right?
> >>
> >> 3. Would it ever make sense to partition an index without partitioning
> >> the
> >> table it resides on?
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>