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 question about clustered indexes forcing rebuild of non-clustered indexes.

So I'm readinghttp://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:

When selecting a column to base your clustered index on, try toavoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005]Updated 3-5-2004

Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?

If so does this "pseudocode" example also cause this to occur:

sqlstring="select * from item where itemid=12345"

rs.open sqlstring, etc, etc, etc

rs.Fields("ItemName")="My New Item Name"

rs.Fields("ItemPrice")=1.00

rs.Update

Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

Since it's been a while and no one responded, I thought I'd throw my 2 cents in. I'm not sure the link you posted is correct, but it might be. It would depend on whether SQL Server attempts to maintain clustering when you update a clustered index, and I don't know the answer to that. For example, suppose you have a clustered index on name and update someone from "AAAA" to "ZZZZ". This would change his position in the table, which is to say it would change the page number of that row (since it would move from the beginning of the DB to the end), which would mean that all other indexes would have to be updated too.

This depends entirely on whether SQL actually tries to maintain the clustering in real time, and I don't know the answer to that. With other products I've worked with, the DB just lets the data get out of cluster and you have to rebuild them from time-to-time.

|||

I posted this question on sql-server-performance and got a reply:http://sql-server-performance.com/Community/forums/p/23274/132088.aspx#132088

|||

dbland07666:

I posted this question on sql-server-performance and got a reply:http://sql-server-performance.com/Community/forums/p/23274/132088.aspx#132088

And that would be me ..

|||

I figured!

A little help with clustered SQL server setup.

Guys,
I keep getting errors while trying to install SQL 2K5 server on Win2K3 x64
R2 SP2 cluster.
The setup sequence was
1. Install SQL client/tools on all nodes
2. Install first instance of SQL server
3. Install SQL SP2 for client/tools on all nodes
4. Install SQL server SP2 for instance
5. Install second instance of SQL server
This step keeps failing no matter what I tried.
I did similar setup before in “slightly” different order and it works fine:
1. Install SQL client/tools on all nodes
2. Install first instance of SQL server
3. Install second instance of SQL server
4. Install third instance of SQL server
5. Install SQL SP2 for client/tools on all nodes
6. Install SQL server SP2 for all instances
With my current setup I don’t want (actually I can not) follow same order.
What if the first instance is already in production AND SP2 is applied
already, and then I need to install a new instance?
Anyway, setup keeps failing on remote nodes with this error:
(I tried both, GUI and command line with exact same result)
Running: LoadResourcesAction at: 2007/4/22 22:22:59
Complete: LoadResourcesAction at: 2007/4/22 22:22:59, returned true
Running: ParseBootstrapOptionsAction at: 2007/4/22 22:22:59
Loaded DLL:\\NYSQL01\C$\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\xmlrw.dll Version:2.0.3609.0
Error :Read private data failed 2
Complete: ParseBootstrapOptionsAction at: 2007/4/22 22:22:59, returned false
Error: Action "ParseBootstrapOptionsAction" failed during execution. Error
information reported during run:
Could not decrypt command line due to WinException.
Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.
Source File Name: cryptohelper\lsasecret.cpp
Compiler Timestamp: Sat Oct 7 09:43:52 2006
Function Name: sqls::LsaSecret::Read
Source Line Number: 107
Is the Cluster service account the same on all nodes?
Ayad Shammout
"OK" <OK@.discussions.microsoft.com> wrote in message
news:05D675D0-39FC-4621-AAFA-CD4E4FFD1B18@.microsoft.com...
> Guys,
> I keep getting errors while trying to install SQL 2K5 server on Win2K3 x64
> R2 SP2 cluster.
> The setup sequence was
> 1. Install SQL client/tools on all nodes
> 2. Install first instance of SQL server
> 3. Install SQL SP2 for client/tools on all nodes
> 4. Install SQL server SP2 for instance
> 5. Install second instance of SQL server
> This step keeps failing no matter what I tried.
> I did similar setup before in "slightly" different order and it works
> fine:
> 1. Install SQL client/tools on all nodes
> 2. Install first instance of SQL server
> 3. Install second instance of SQL server
> 4. Install third instance of SQL server
> 5. Install SQL SP2 for client/tools on all nodes
> 6. Install SQL server SP2 for all instances
> With my current setup I don't want (actually I can not) follow same order.
> What if the first instance is already in production AND SP2 is applied
> already, and then I need to install a new instance?
> Anyway, setup keeps failing on remote nodes with this error:
> (I tried both, GUI and command line with exact same result)
> Running: LoadResourcesAction at: 2007/4/22 22:22:59
> Complete: LoadResourcesAction at: 2007/4/22 22:22:59, returned true
> Running: ParseBootstrapOptionsAction at: 2007/4/22 22:22:59
> Loaded DLL:\\NYSQL01\C$\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\xmlrw.dll Version:2.0.3609.0
> Error :Read private data failed 2
> Complete: ParseBootstrapOptionsAction at: 2007/4/22 22:22:59, returned
> false
> Error: Action "ParseBootstrapOptionsAction" failed during execution.
> Error
> information reported during run:
> Could not decrypt command line due to WinException.
> Error Code: 0x80070002 (2)
> Windows Error Text: The system cannot find the file specified.
> Source File Name: cryptohelper\lsasecret.cpp
> Compiler Timestamp: Sat Oct 7 09:43:52 2006
> Function Name: sqls::LsaSecret::Read
> Source Line Number: 107
>
|||LOL :-)
Exactly the same of course!
OK
"Ayad Shammout" wrote:

> Is the Cluster service account the same on all nodes?
> Ayad Shammout
>
> "OK" <OK@.discussions.microsoft.com> wrote in message
> news:05D675D0-39FC-4621-AAFA-CD4E4FFD1B18@.microsoft.com...
>
>