If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
Showing posts with label indexes. Show all posts
Showing posts with label indexes. Show all posts
Wednesday, March 28, 2012
Question Regarding Views and Indexes
If I have a table defined as follows:
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
TABLE Customer_Info
Customer_ID Int NOT NULL,
Country nvarchar(225) NOT NULL,
State_Province nvarchar(225) NULL,
Customer_Name nvarchar(225) NULL
The tables primary key and unique index is on Customer_ID
There is also an index on the Country column and a separate index on
the State_Province column.
And then I have a view names USA_Customers ( a standard view, NOT an
indexed view) defines as follows:
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE (Country = 'USA')
And I run the following query
Select * from USA_Customers
Where State_Province = 'PA'
Will this query use the index on the State_Province column of the base
table or not when the query is executed? Regardless of the answer, if
there is any documentation that you could point me to that explains
when an index will / will not be used, I would greatly appreciate it.
Please assume there is enough data in the table and enough cardinality
in the indexes that would be desirable to use the indexes.
Thanks
George<GCeaser@.aol.com> wrote in message
news:1151511288.326267.307760@.i40g2000cwc.googlegroups.com...
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
>
The important thing to know here is that views are expanded into the query
plan before optimization. So this query should behave exactly like
SELECT Customer_ID, County, State_Province, Customer_Name
FROM dbo.Customer_Info
WHERE Country = 'USA'
AND State_Province = 'PA'
SQL Server will consider usiing either, both or neither index, and then
should either use State index + Lookups, the Country Index + Lookups, Index
intersection between the two + Lookups, or do a table scan.
David|||GCeaser@.aol.com wrote:
> If I have a table defined as follows:
> TABLE Customer_Info
> Customer_ID Int NOT NULL,
> Country nvarchar(225) NOT NULL,
> State_Province nvarchar(225) NULL,
> Customer_Name nvarchar(225) NULL
> The tables primary key and unique index is on Customer_ID
> There is also an index on the Country column and a separate index on
> the State_Province column.
> And then I have a view names USA_Customers ( a standard view, NOT an
> indexed view) defines as follows:
> SELECT Customer_ID, County, State_Province, Customer_Name
> FROM dbo.Customer_Info
> WHERE (Country = 'USA')
>
> And I run the following query
> Select * from USA_Customers
> Where State_Province = 'PA'
> Will this query use the index on the State_Province column of the base
> table or not when the query is executed? Regardless of the answer, if
> there is any documentation that you could point me to that explains
> when an index will / will not be used, I would greatly appreciate it.
> Please assume there is enough data in the table and enough cardinality
> in the indexes that would be desirable to use the indexes.
> Thanks
> George
>
It's *probably* going to use the index on the Country column,
accompanied by a bookmark lookup to get the other fields. It depends on
several things - how much data is in the table, how the view is being
queried (directly as your example or as part of a join). The best way
to confirm is to look at the execution plan of your query.
Tuesday, March 20, 2012
question on recompiles
On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
TIA,
ChrisR
ChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
TIA,
ChrisR
ChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
question on recompiles
On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
>> On Monday I added a whole bunch of indexes. They were pretty much just
>> indexes on foreign keys. Should I have recomiled all the sp's? Since I
>> didn't, should I bother now? According to BOL:
>> But if a new index is added from which the stored procedure might
>> benefit, optimization does not automatically happen (until the next
>> time the stored procedure is run after SQL Server is restarted).
>> Wouldnt they also get recompiled the first time they were run,
>> whether SQL was restarted or not?
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
>> On Monday I added a whole bunch of indexes. They were pretty much just
>> indexes on foreign keys. Should I have recomiled all the sp's? Since I
>> didn't, should I bother now? According to BOL:
>> But if a new index is added from which the stored procedure might
>> benefit, optimization does not automatically happen (until the next
>> time the stored procedure is run after SQL Server is restarted).
>> Wouldnt they also get recompiled the first time they were run,
>> whether SQL was restarted or not?
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
question on recompiles
On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
--
TIA,
ChrisRChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
Monday, March 12, 2012
Question on partitioning indexes
We have two tables that have full text indexes, currently both are using the
same catalog.
One table is much larger and the column being indexed contains more data.
Would there be any advantage of seperating the indexes into tow seperate
catalogs?
Kyle!
Kyle,
This is one of those questions, where the answer is that it depends... First
of all, see SQL Server 2000 BOL title "Full-Text Search Recommendations" -
"There are also full-text indexing and searching considerations when
determining whether to include multiple SQL tables in one full-text catalog
versus one SQL table per full-text catalog. There is a trade-off between
performance and maintenance when considering this design question with large
SQL tables and you may want to test both options for your environment. If
you choose to have multiple SQL tables in one full-text catalog, you incur
the overhead of longer-running full-text search queries as well because
incremental populations will force the full-text indexing of all other SQL
tables in that full-text catalog. If you choose to have a single SQL table
per full-text catalog and have multiple SQL tables full-text indexed, you
have the overhead of maintaining separate full-text catalogs with a total
limit of 256 full-text catalogs per server."
Another consideration is whether or not you are using CONTAINSTABLE or
FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
affect the Ranking values...
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:epPuhv3wFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We have two tables that have full text indexes, currently both are using
> the same catalog.
> One table is much larger and the column being indexed contains more data.
> Would there be any advantage of seperating the indexes into tow seperate
> catalogs?
> Kyle!
>
|||This is good stuff. The article was good as well.
We can't seperate the catalog onto a different drive as we only have a RAID5
setup with 6 physical drives and one logical drive.
One table has over 100K records, the other over 94K records. It's not
millions of records, but we are trying to tweak search performace as much as
we can. I don't forsee ever adding 252 more catalogs anywhere in the
future. So seperating the FTI for each table into it's own catalog
shouldn't be an issue.
Thanks
Kyle
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OVq6y79wFHA.1456@.TK2MSFTNGP11.phx.gbl...
> Kyle,
> This is one of those questions, where the answer is that it depends...
> First of all, see SQL Server 2000 BOL title "Full-Text Search
> Recommendations" - "There are also full-text indexing and searching
> considerations when determining whether to include multiple SQL tables in
> one full-text catalog versus one SQL table per full-text catalog. There is
> a trade-off between performance and maintenance when considering this
> design question with large SQL tables and you may want to test both
> options for your environment. If you choose to have multiple SQL tables in
> one full-text catalog, you incur the overhead of longer-running full-text
> search queries as well because incremental populations will force the
> full-text indexing of all other SQL tables in that full-text catalog. If
> you choose to have a single SQL table per full-text catalog and have
> multiple SQL tables full-text indexed, you have the overhead of
> maintaining separate full-text catalogs with a total limit of 256
> full-text catalogs per server."
> Another consideration is whether or not you are using CONTAINSTABLE or
> FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
> affect the Ranking values...
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:epPuhv3wFHA.3812@.TK2MSFTNGP09.phx.gbl...
>
|||You're welcome, Kyle,
Actually, I wrote that years ago (before SQL 2000 shipped) while I was at
MSFT. You may want to review the collection of FTS related articles at
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
for more information on performance and problems/workarounds.
Enjoy!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23VlkPPDxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> This is good stuff. The article was good as well.
> We can't seperate the catalog onto a different drive as we only have a
> RAID5 setup with 6 physical drives and one logical drive.
> One table has over 100K records, the other over 94K records. It's not
> millions of records, but we are trying to tweak search performace as much
> as we can. I don't forsee ever adding 252 more catalogs anywhere in the
> future. So seperating the FTI for each table into it's own catalog
> shouldn't be an issue.
> Thanks
> Kyle
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OVq6y79wFHA.1456@.TK2MSFTNGP11.phx.gbl...
>
same catalog.
One table is much larger and the column being indexed contains more data.
Would there be any advantage of seperating the indexes into tow seperate
catalogs?
Kyle!
Kyle,
This is one of those questions, where the answer is that it depends... First
of all, see SQL Server 2000 BOL title "Full-Text Search Recommendations" -
"There are also full-text indexing and searching considerations when
determining whether to include multiple SQL tables in one full-text catalog
versus one SQL table per full-text catalog. There is a trade-off between
performance and maintenance when considering this design question with large
SQL tables and you may want to test both options for your environment. If
you choose to have multiple SQL tables in one full-text catalog, you incur
the overhead of longer-running full-text search queries as well because
incremental populations will force the full-text indexing of all other SQL
tables in that full-text catalog. If you choose to have a single SQL table
per full-text catalog and have multiple SQL tables full-text indexed, you
have the overhead of maintaining separate full-text catalogs with a total
limit of 256 full-text catalogs per server."
Another consideration is whether or not you are using CONTAINSTABLE or
FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
affect the Ranking values...
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:epPuhv3wFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We have two tables that have full text indexes, currently both are using
> the same catalog.
> One table is much larger and the column being indexed contains more data.
> Would there be any advantage of seperating the indexes into tow seperate
> catalogs?
> Kyle!
>
|||This is good stuff. The article was good as well.
We can't seperate the catalog onto a different drive as we only have a RAID5
setup with 6 physical drives and one logical drive.
One table has over 100K records, the other over 94K records. It's not
millions of records, but we are trying to tweak search performace as much as
we can. I don't forsee ever adding 252 more catalogs anywhere in the
future. So seperating the FTI for each table into it's own catalog
shouldn't be an issue.
Thanks
Kyle
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OVq6y79wFHA.1456@.TK2MSFTNGP11.phx.gbl...
> Kyle,
> This is one of those questions, where the answer is that it depends...
> First of all, see SQL Server 2000 BOL title "Full-Text Search
> Recommendations" - "There are also full-text indexing and searching
> considerations when determining whether to include multiple SQL tables in
> one full-text catalog versus one SQL table per full-text catalog. There is
> a trade-off between performance and maintenance when considering this
> design question with large SQL tables and you may want to test both
> options for your environment. If you choose to have multiple SQL tables in
> one full-text catalog, you incur the overhead of longer-running full-text
> search queries as well because incremental populations will force the
> full-text indexing of all other SQL tables in that full-text catalog. If
> you choose to have a single SQL table per full-text catalog and have
> multiple SQL tables full-text indexed, you have the overhead of
> maintaining separate full-text catalogs with a total limit of 256
> full-text catalogs per server."
> Another consideration is whether or not you are using CONTAINSTABLE or
> FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
> affect the Ranking values...
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:epPuhv3wFHA.3812@.TK2MSFTNGP09.phx.gbl...
>
|||You're welcome, Kyle,
Actually, I wrote that years ago (before SQL 2000 shipped) while I was at
MSFT. You may want to review the collection of FTS related articles at
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
for more information on performance and problems/workarounds.
Enjoy!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23VlkPPDxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> This is good stuff. The article was good as well.
> We can't seperate the catalog onto a different drive as we only have a
> RAID5 setup with 6 physical drives and one logical drive.
> One table has over 100K records, the other over 94K records. It's not
> millions of records, but we are trying to tweak search performace as much
> as we can. I don't forsee ever adding 252 more catalogs anywhere in the
> future. So seperating the FTI for each table into it's own catalog
> shouldn't be an issue.
> Thanks
> Kyle
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OVq6y79wFHA.1456@.TK2MSFTNGP11.phx.gbl...
>
Subscribe to:
Posts (Atom)