Parameterized Expression: index

Showing posts with label index. Show all posts

Wednesday, March 28, 2012

Pareser take wrong index when using @name runtime variable

Hi,
Today one of our developers came to me with a question which totaly
flabbergasted me. He had 2 little examples (see below) which lead to totally
different execution plans and I couldn't tell him why. Maybe anyone in this
newsgroups know the problem and can tell me what's going on.
The first SELECT runs less than one second because it uses an Index for the
index seek. The second SELECT however stunned me completetly because it does
an index scan on the Primary Key resulting in endless waiting.
The question is: Why does the Optimizer take the wrong index for the second
SELECT?
Paul Sinnema
DECLARE @.SVERARJOURNAL NUMERIC(21,0),
@.SFIRMA NUMERIC(21,0)
-- TIME : 0 SEC
SELECT SBUCHZEILE, XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = 15884
AND SFIRMA = 0
AND CEINZSTATUS = '40'
SET @.SFIRMA = 0
SET @.SVERARJOURNAL = 15884
-- RUNS INDEFENITLY
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'Its called "parameter sniffing" ,google it and you will find the more info
Run those command between the queries
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>|||There are a couple of possibilities. First, make sure the actual column
data types match the variable declarations. If these are different data
types, the expressions will not be sargable if the column value needs to be
converted to the variable declaration (numeric).
Also, when the optimizer generates the plan for the first query, the actual
values are known so the best plan can be generated. However, the values of
@.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
distribution statistics of existing data to guess what actual values might
be provided. This might yield a different plan.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> Hi,
> Today one of our developers came to me with a question which totaly
> flabbergasted me. He had 2 little examples (see below) which lead to
> totally
> different execution plans and I couldn't tell him why. Maybe anyone in
> this
> newsgroups know the problem and can tell me what's going on.
> The first SELECT runs less than one second because it uses an Index for
> the
> index seek. The second SELECT however stunned me completetly because it
> does
> an index scan on the Primary Key resulting in endless waiting.
> The question is: Why does the Optimizer take the wrong index for the
> second
> SELECT?
> Paul Sinnema
> DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> @.SFIRMA NUMERIC(21,0)
> -- TIME : 0 SEC
> SELECT SBUCHZEILE, XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = 15884
> AND SFIRMA = 0
> AND CEINZSTATUS = '40'
> SET @.SFIRMA = 0
> SET @.SVERARJOURNAL = 15884
> -- RUNS INDEFENITLY
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
>|||Thanks Uri and Dan,
You're probably right. I've also tried the following:
DECLARE @.COMMAND AS VARCHAR(200)
SET @.COMMAND = 'SELECT SBUCHZEILE, XBUCHTEXT' +
' FROM BUCHZEILE ' +
' WHERE SVERARJOURNAL = ' + CAST ( @.SVERARJOURNAL AS VARCHAR) +
' AND SFIRMA = ' + CAST(@.SFIRMA AS VARCHAR) +
' AND CEINZSTATUS = ''40'''
EXEC ( @.COMMAND )
It's a dirty trick but it results in the right index being taken. It
probably proves your suggestion right, that the parser is better at
determining which path to take with constants that with variables.
We could also use the INDEX HINT but that's not very nice. The indexes in
our Project are generated by a tool called Uniface (from Compuware). It
generates the indexes with a sequence number (i.e. BuchzeileI1, BuchzeileI2,
etc.). When one is removed from Uniface all indexes, behind the one removed,
become different names which could lead to dissaster for our INDEX HINTS.
Paul.
"Dan Guzman" wrote:
> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to be
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actual
> values are known so the best plan can be generated. However, the values of
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>|||Nop, doesn't change a thing. See reply to Guzman!
"Uri Dimant" wrote:
> Its called "parameter sniffing" ,google it and you will find the more info
> Run those command between the queries
> DBCC FREEPROCCACHE
> DBCC DROPCLEANBUFFERS
>
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>
>|||Dan,
Is there a way to trick the Optimizer. We've already tried the following:
- Added an index for SVERARJOURNAL. No result.
- Tried changing the order of the WHERE Clause. No result.
- Use the INDEX HINT. Good result, for us bad solution.
Paul.
"Dan Guzman" wrote:
> There are a couple of possibilities. First, make sure the actual column
> data types match the variable declarations. If these are different data
> types, the expressions will not be sargable if the column value needs to be
> converted to the variable declaration (numeric).
> Also, when the optimizer generates the plan for the first query, the actual
> values are known so the best plan can be generated. However, the values of
> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer uses
> distribution statistics of existing data to guess what actual values might
> be provided. This might yield a different plan.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> > Hi,
> >
> > Today one of our developers came to me with a question which totaly
> > flabbergasted me. He had 2 little examples (see below) which lead to
> > totally
> > different execution plans and I couldn't tell him why. Maybe anyone in
> > this
> > newsgroups know the problem and can tell me what's going on.
> >
> > The first SELECT runs less than one second because it uses an Index for
> > the
> > index seek. The second SELECT however stunned me completetly because it
> > does
> > an index scan on the Primary Key resulting in endless waiting.
> >
> > The question is: Why does the Optimizer take the wrong index for the
> > second
> > SELECT?
> >
> > Paul Sinnema
> >
> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> > @.SFIRMA NUMERIC(21,0)
> >
> > -- TIME : 0 SEC
> > SELECT SBUCHZEILE, XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = 15884
> > AND SFIRMA = 0
> > AND CEINZSTATUS = '40'
> >
> > SET @.SFIRMA = 0
> > SET @.SVERARJOURNAL = 15884
> >
> > -- RUNS INDEFENITLY
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> > AND SFIRMA = @.SFIRMA
> > AND CEINZSTATUS = '40'
> >
>|||If the query is in a stored proc, try setting the stored procedure
parameters default values to the ones used for the 'good' plan. The
optimizer will use those values for plan generation instead guessing based
on distribution stats. If that doesn't work, post your table DDL,
including indexes.
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) = 0.0,
@.SFIRMA NUMERIC(21,0) = 15884.0
AS
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.SVERARJOURNAL
AND SFIRMA = @.SFIRMA
AND CEINZSTATUS = '40'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
> Dan,
> Is there a way to trick the Optimizer. We've already tried the following:
> - Added an index for SVERARJOURNAL. No result.
> - Tried changing the order of the WHERE Clause. No result.
> - Use the INDEX HINT. Good result, for us bad solution.
> Paul.
>
> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual column
>> data types match the variable declarations. If these are different data
>> types, the expressions will not be sargable if the column value needs to
>> be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the values
>> of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index for
>> > the
>> > index seek. The second SELECT however stunned me completetly because it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >|||I addition to Dan's great explanation , you can declare a local variable to
perevent parameter sniffing
CREATE PROC dbo_select_BUCHZEILE
@.SVERARJOURNAL NUMERIC(21,0) ,
@.SFIRMA NUMERIC(21,0)
AS
DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
DECLARE @.Local_SFIRMA NUMERIC(21,0)
SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
SET @.Local_SFIRMA =@.SFIRMA
SELECT SBUCHZEILE,XBUCHTEXT
FROM BUCHZEILE
WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
AND SFIRMA = @.Local_SFIRMA
AND CEINZSTATUS = '40'
> GO
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
> If the query is in a stored proc, try setting the stored procedure
> parameters default values to the ones used for the 'good' plan. The
> optimizer will use those values for plan generation instead guessing based
> on distribution stats. If that doesn't work, post your table DDL,
> including indexes.
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
> @.SFIRMA NUMERIC(21,0) = 15884.0
> AS
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> AND SFIRMA = @.SFIRMA
> AND CEINZSTATUS = '40'
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> Dan,
>> Is there a way to trick the Optimizer. We've already tried the following:
>> - Added an index for SVERARJOURNAL. No result.
>> - Tried changing the order of the WHERE Clause. No result.
>> - Use the INDEX HINT. Good result, for us bad solution.
>> Paul.
>>
>> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual column
>> data types match the variable declarations. If these are different data
>> types, the expressions will not be sargable if the column value needs to
>> be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the values
>> of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index
>> > for
>> > the
>> > index seek. The second SELECT however stunned me completetly because
>> > it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >
>|||Good point, Uri. Paul's initial script used local variables instead of proc
parameters so I didn't go there. To clarify the issue for Paul, SQL Server
'sniffs' parameter values as follows:
1) When stored procedure parameters are used directly in the WHERE clause,
SQL Server uses the default parameters to generate the execution plan. If
you don't specify default values, SQL Server uses NULL which is often the
unusual case and can result in a poor plan for non-trivial queries.
Specifying typical values as parameter defaults instead of NULL will
generate a plan that is good for data of similar cardinality.
2) When local variables are used, SQL Server estimates what values may be
based on table index and column statistics. This is often better than #1
but may perform poorly when unusual values are specified.
3) With constants, the actual values are known so the best execution plan
is generated.
SQL server 2005 introduces features such as statement-level recompilation
that help address this issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>I addition to Dan's great explanation , you can declare a local variable to
>perevent parameter sniffing
> CREATE PROC dbo_select_BUCHZEILE
> @.SVERARJOURNAL NUMERIC(21,0) ,
> @.SFIRMA NUMERIC(21,0)
> AS
> DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
> DECLARE @.Local_SFIRMA NUMERIC(21,0)
> SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
> SET @.Local_SFIRMA =@.SFIRMA
> SELECT SBUCHZEILE,XBUCHTEXT
> FROM BUCHZEILE
> WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
> AND SFIRMA = @.Local_SFIRMA
> AND CEINZSTATUS = '40'
>> GO
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
>> If the query is in a stored proc, try setting the stored procedure
>> parameters default values to the ones used for the 'good' plan. The
>> optimizer will use those values for plan generation instead guessing
>> based on distribution stats. If that doesn't work, post your table DDL,
>> including indexes.
>> CREATE PROC dbo_select_BUCHZEILE
>> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
>> @.SFIRMA NUMERIC(21,0) = 15884.0
>> AS
>> SELECT SBUCHZEILE,XBUCHTEXT
>> FROM BUCHZEILE
>> WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> AND SFIRMA = @.SFIRMA
>> AND CEINZSTATUS = '40'
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> Dan,
>> Is there a way to trick the Optimizer. We've already tried the
>> following:
>> - Added an index for SVERARJOURNAL. No result.
>> - Tried changing the order of the WHERE Clause. No result.
>> - Use the INDEX HINT. Good result, for us bad solution.
>> Paul.
>>
>> "Dan Guzman" wrote:
>> There are a couple of possibilities. First, make sure the actual
>> column
>> data types match the variable declarations. If these are different
>> data
>> types, the expressions will not be sargable if the column value needs
>> to be
>> converted to the variable declaration (numeric).
>> Also, when the optimizer generates the plan for the first query, the
>> actual
>> values are known so the best plan can be generated. However, the
>> values of
>> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
>> uses
>> distribution statistics of existing data to guess what actual values
>> might
>> be provided. This might yield a different plan.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> > Hi,
>> >
>> > Today one of our developers came to me with a question which totaly
>> > flabbergasted me. He had 2 little examples (see below) which lead to
>> > totally
>> > different execution plans and I couldn't tell him why. Maybe anyone
>> > in
>> > this
>> > newsgroups know the problem and can tell me what's going on.
>> >
>> > The first SELECT runs less than one second because it uses an Index
>> > for
>> > the
>> > index seek. The second SELECT however stunned me completetly because
>> > it
>> > does
>> > an index scan on the Primary Key resulting in endless waiting.
>> >
>> > The question is: Why does the Optimizer take the wrong index for the
>> > second
>> > SELECT?
>> >
>> > Paul Sinnema
>> >
>> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> > @.SFIRMA NUMERIC(21,0)
>> >
>> > -- TIME : 0 SEC
>> > SELECT SBUCHZEILE, XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = 15884
>> > AND SFIRMA = 0
>> > AND CEINZSTATUS = '40'
>> >
>> > SET @.SFIRMA = 0
>> > SET @.SVERARJOURNAL = 15884
>> >
>> > -- RUNS INDEFENITLY
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> > AND SFIRMA = @.SFIRMA
>> > AND CEINZSTATUS = '40'
>> >
>>
>|||Guzman, Uri
Thanks guys for the help. The first example of Guzman takes the right index.
The second one of Uri doesn't (i.e. we have the old problem back again). What
I didn't know is that the presented queries came from a stored procedure the
programmer had written. He added a default value for the @.SVERARJOURNAL
parameter and voila, it works as desired.
Thanks again. We've learned something today.
Paul.
"Dan Guzman" wrote:
> Good point, Uri. Paul's initial script used local variables instead of proc
> parameters so I didn't go there. To clarify the issue for Paul, SQL Server
> 'sniffs' parameter values as follows:
> 1) When stored procedure parameters are used directly in the WHERE clause,
> SQL Server uses the default parameters to generate the execution plan. If
> you don't specify default values, SQL Server uses NULL which is often the
> unusual case and can result in a poor plan for non-trivial queries.
> Specifying typical values as parameter defaults instead of NULL will
> generate a plan that is good for data of similar cardinality.
> 2) When local variables are used, SQL Server estimates what values may be
> based on table index and column statistics. This is often better than #1
> but may perform poorly when unusual values are specified.
> 3) With constants, the actual values are known so the best execution plan
> is generated.
> SQL server 2005 introduces features such as statement-level recompilation
> that help address this issue.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
> >I addition to Dan's great explanation , you can declare a local variable to
> >perevent parameter sniffing
> >
> > CREATE PROC dbo_select_BUCHZEILE
> > @.SVERARJOURNAL NUMERIC(21,0) ,
> > @.SFIRMA NUMERIC(21,0)
> > AS
> >
> > DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
> > DECLARE @.Local_SFIRMA NUMERIC(21,0)
> >
> > SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
> > SET @.Local_SFIRMA =@.SFIRMA
> >
> > SELECT SBUCHZEILE,XBUCHTEXT
> > FROM BUCHZEILE
> > WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
> > AND SFIRMA = @.Local_SFIRMA
> > AND CEINZSTATUS = '40'
> >> GO
> >
> >
> >
> >
> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> > news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
> >> If the query is in a stored proc, try setting the stored procedure
> >> parameters default values to the ones used for the 'good' plan. The
> >> optimizer will use those values for plan generation instead guessing
> >> based on distribution stats. If that doesn't work, post your table DDL,
> >> including indexes.
> >>
> >> CREATE PROC dbo_select_BUCHZEILE
> >> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
> >> @.SFIRMA NUMERIC(21,0) = 15884.0
> >> AS
> >> SELECT SBUCHZEILE,XBUCHTEXT
> >> FROM BUCHZEILE
> >> WHERE SVERARJOURNAL = @.SVERARJOURNAL
> >> AND SFIRMA = @.SFIRMA
> >> AND CEINZSTATUS = '40'
> >> GO
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> >> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
> >> Dan,
> >>
> >> Is there a way to trick the Optimizer. We've already tried the
> >> following:
> >> - Added an index for SVERARJOURNAL. No result.
> >> - Tried changing the order of the WHERE Clause. No result.
> >> - Use the INDEX HINT. Good result, for us bad solution.
> >>
> >> Paul.
> >>
> >>
> >> "Dan Guzman" wrote:
> >>
> >> There are a couple of possibilities. First, make sure the actual
> >> column
> >> data types match the variable declarations. If these are different
> >> data
> >> types, the expressions will not be sargable if the column value needs
> >> to be
> >> converted to the variable declaration (numeric).
> >>
> >> Also, when the optimizer generates the plan for the first query, the
> >> actual
> >> values are known so the best plan can be generated. However, the
> >> values of
> >> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the optimizer
> >> uses
> >> distribution statistics of existing data to guess what actual values
> >> might
> >> be provided. This might yield a different plan.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
> >> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
> >> > Hi,
> >> >
> >> > Today one of our developers came to me with a question which totaly
> >> > flabbergasted me. He had 2 little examples (see below) which lead to
> >> > totally
> >> > different execution plans and I couldn't tell him why. Maybe anyone
> >> > in
> >> > this
> >> > newsgroups know the problem and can tell me what's going on.
> >> >
> >> > The first SELECT runs less than one second because it uses an Index
> >> > for
> >> > the
> >> > index seek. The second SELECT however stunned me completetly because
> >> > it
> >> > does
> >> > an index scan on the Primary Key resulting in endless waiting.
> >> >
> >> > The question is: Why does the Optimizer take the wrong index for the
> >> > second
> >> > SELECT?
> >> >
> >> > Paul Sinnema
> >> >
> >> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
> >> > @.SFIRMA NUMERIC(21,0)
> >> >
> >> > -- TIME : 0 SEC
> >> > SELECT SBUCHZEILE, XBUCHTEXT
> >> > FROM BUCHZEILE
> >> > WHERE SVERARJOURNAL = 15884
> >> > AND SFIRMA = 0
> >> > AND CEINZSTATUS = '40'
> >> >
> >> > SET @.SFIRMA = 0
> >> > SET @.SVERARJOURNAL = 15884
> >> >
> >> > -- RUNS INDEFENITLY
> >> > SELECT SBUCHZEILE,XBUCHTEXT
> >> > FROM BUCHZEILE
> >> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
> >> > AND SFIRMA = @.SFIRMA
> >> > AND CEINZSTATUS = '40'
> >> >
> >>
> >>
> >
> >
>|||I'm glad we could help you out. Below is a link to the post by Bart Duncan
of Microsoft that describes the behavior in more detail.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a6ae4020b1e3621/0ff9e6e72122e1fb?lnk=st&rnum=1#0ff9e6e72122e1fb
Hope this helps.
Dan Guzman
SQL Server MVP
"PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
news:6D5B834D-3D4A-41E4-87B7-BD43F7DD32FF@.microsoft.com...
> Guzman, Uri
> Thanks guys for the help. The first example of Guzman takes the right
> index.
> The second one of Uri doesn't (i.e. we have the old problem back again).
> What
> I didn't know is that the presented queries came from a stored procedure
> the
> programmer had written. He added a default value for the @.SVERARJOURNAL
> parameter and voila, it works as desired.
> Thanks again. We've learned something today.
> Paul.
> "Dan Guzman" wrote:
>> Good point, Uri. Paul's initial script used local variables instead of
>> proc
>> parameters so I didn't go there. To clarify the issue for Paul, SQL
>> Server
>> 'sniffs' parameter values as follows:
>> 1) When stored procedure parameters are used directly in the WHERE
>> clause,
>> SQL Server uses the default parameters to generate the execution plan.
>> If
>> you don't specify default values, SQL Server uses NULL which is often the
>> unusual case and can result in a poor plan for non-trivial queries.
>> Specifying typical values as parameter defaults instead of NULL will
>> generate a plan that is good for data of similar cardinality.
>> 2) When local variables are used, SQL Server estimates what values may
>> be
>> based on table index and column statistics. This is often better than #1
>> but may perform poorly when unusual values are specified.
>> 3) With constants, the actual values are known so the best execution
>> plan
>> is generated.
>> SQL server 2005 introduces features such as statement-level recompilation
>> that help address this issue.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23cNvmdjAHHA.1224@.TK2MSFTNGP04.phx.gbl...
>> >I addition to Dan's great explanation , you can declare a local variable
>> >to
>> >perevent parameter sniffing
>> >
>> > CREATE PROC dbo_select_BUCHZEILE
>> > @.SVERARJOURNAL NUMERIC(21,0) ,
>> > @.SFIRMA NUMERIC(21,0)
>> > AS
>> >
>> > DECLARE @.Local_SVERARJOURNAL NUMERIC(21,0)
>> > DECLARE @.Local_SFIRMA NUMERIC(21,0)
>> >
>> > SET @.Local_SVERARJOURNAL =@.SVERARJOURNAL
>> > SET @.Local_SFIRMA =@.SFIRMA
>> >
>> > SELECT SBUCHZEILE,XBUCHTEXT
>> > FROM BUCHZEILE
>> > WHERE SVERARJOURNAL = @.Local_SVERARJOURNAL
>> > AND SFIRMA = @.Local_SFIRMA
>> > AND CEINZSTATUS = '40'
>> >> GO
>> >
>> >
>> >
>> >
>> > "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> > news:D7B098AA-8C4D-482C-8C5C-393A2BF401A5@.microsoft.com...
>> >> If the query is in a stored proc, try setting the stored procedure
>> >> parameters default values to the ones used for the 'good' plan. The
>> >> optimizer will use those values for plan generation instead guessing
>> >> based on distribution stats. If that doesn't work, post your table
>> >> DDL,
>> >> including indexes.
>> >>
>> >> CREATE PROC dbo_select_BUCHZEILE
>> >> @.SVERARJOURNAL NUMERIC(21,0) = 0.0,
>> >> @.SFIRMA NUMERIC(21,0) = 15884.0
>> >> AS
>> >> SELECT SBUCHZEILE,XBUCHTEXT
>> >> FROM BUCHZEILE
>> >> WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> >> AND SFIRMA = @.SFIRMA
>> >> AND CEINZSTATUS = '40'
>> >> GO
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in message
>> >> news:DD30F8F8-5DC7-43D5-84C3-1E2CFEEA60E0@.microsoft.com...
>> >> Dan,
>> >>
>> >> Is there a way to trick the Optimizer. We've already tried the
>> >> following:
>> >> - Added an index for SVERARJOURNAL. No result.
>> >> - Tried changing the order of the WHERE Clause. No result.
>> >> - Use the INDEX HINT. Good result, for us bad solution.
>> >>
>> >> Paul.
>> >>
>> >>
>> >> "Dan Guzman" wrote:
>> >>
>> >> There are a couple of possibilities. First, make sure the actual
>> >> column
>> >> data types match the variable declarations. If these are different
>> >> data
>> >> types, the expressions will not be sargable if the column value
>> >> needs
>> >> to be
>> >> converted to the variable declaration (numeric).
>> >>
>> >> Also, when the optimizer generates the plan for the first query, the
>> >> actual
>> >> values are known so the best plan can be generated. However, the
>> >> values of
>> >> @.SVERARJOURNAL and @.SFIRMA are unknown in second query so the
>> >> optimizer
>> >> uses
>> >> distribution statistics of existing data to guess what actual values
>> >> might
>> >> be provided. This might yield a different plan.
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "PaulSinnema" <PaulSinnema@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:AD80F6D0-9E5F-4D85-9086-C43B1B57300C@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > Today one of our developers came to me with a question which
>> >> > totaly
>> >> > flabbergasted me. He had 2 little examples (see below) which lead
>> >> > to
>> >> > totally
>> >> > different execution plans and I couldn't tell him why. Maybe
>> >> > anyone
>> >> > in
>> >> > this
>> >> > newsgroups know the problem and can tell me what's going on.
>> >> >
>> >> > The first SELECT runs less than one second because it uses an
>> >> > Index
>> >> > for
>> >> > the
>> >> > index seek. The second SELECT however stunned me completetly
>> >> > because
>> >> > it
>> >> > does
>> >> > an index scan on the Primary Key resulting in endless waiting.
>> >> >
>> >> > The question is: Why does the Optimizer take the wrong index for
>> >> > the
>> >> > second
>> >> > SELECT?
>> >> >
>> >> > Paul Sinnema
>> >> >
>> >> > DECLARE @.SVERARJOURNAL NUMERIC(21,0),
>> >> > @.SFIRMA NUMERIC(21,0)
>> >> >
>> >> > -- TIME : 0 SEC
>> >> > SELECT SBUCHZEILE, XBUCHTEXT
>> >> > FROM BUCHZEILE
>> >> > WHERE SVERARJOURNAL = 15884
>> >> > AND SFIRMA = 0
>> >> > AND CEINZSTATUS = '40'
>> >> >
>> >> > SET @.SFIRMA = 0
>> >> > SET @.SVERARJOURNAL = 15884
>> >> >
>> >> > -- RUNS INDEFENITLY
>> >> > SELECT SBUCHZEILE,XBUCHTEXT
>> >> > FROM BUCHZEILE
>> >> > WHERE SVERARJOURNAL = @.SVERARJOURNAL
>> >> > AND SFIRMA = @.SFIRMA
>> >> > AND CEINZSTATUS = '40'
>> >> >
>> >>
>> >>
>> >
>> >

Friday, March 23, 2012

Parent Child index?

I have a table that has recursive relationship with itself. The primary key
is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier). I
would like to create an index for these two fields, including cascading
deletes (regardless of the number of decendants). Is this possible? If so,
can someone step me thru it?
Thanks,
Craig BuchananHi
You can't create a FK with cascading deletes when the FK refers to the same
table. If you implemented this with a trigger you would be restricted to 32
levels. What you may want to do is to run a batch process that clears up the
table.
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message
news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> I have a table that has recursive relationship with itself. The primary
key
> is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier).
I
> would like to create an index for these two fields, including cascading
> deletes (regardless of the number of decendants). Is this possible? If
so,
> can someone step me thru it?
> Thanks,
> Craig Buchanan
>|||John-
How would I create a trigger between two tables where PK and FK are
uniqueidentifiers?
Thanks,
Craig
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> Hi
> You can't create a FK with cascading deletes when the FK refers to the
same
> table. If you implemented this with a trigger you would be restricted to
32
> levels. What you may want to do is to run a batch process that clears up
the
> table.
> John
> "Craig Buchanan" <someone@.microsoft.com> wrote in message
> news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> key
(uniqueidentifier).
> I
> so,
>|||Hi Craig
Something like:
CREATE TABLE ParentChildTable ( id int not null CONSTRAINT
PK_ParentChild PRIMARY KEY,
parentid int not null )
DROP TABLE ParentChildTable
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
SELECT * FROM deleted
DELETE FROM ParentChildTable
FROM ParentChildTable P JOIN DELETED D ON P.Parentid = d.id
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
sp_configure 'nested triggers'
/*
Needs to be 1
name minimum maximum
config_value run_value
-- -- --
-- --
nested triggers 0 1 1
1
*/
sp_dboption testdb, 'recursive triggers'
/*
Needs to be ON
OptionName CurrentSetting
-- --
recursive triggers ON
*/
DELETE FROM ParentChildTable WHERE id = 3
/* id parentid
-- --
3 2
(1 row(s) affected)
id parentid
-- --
4 3
(1 row(s) affected)
id parentid
-- --
5 4
(1 row(s) affected)
id parentid
-- --
6 5
(1 row(s) affected)
id parentid
-- --
7 6
(1 row(s) affected)
id parentid
-- --
8 7
(1 row(s) affected)
id parentid
-- --
9 8
(1 row(s) affected)
id parentid
-- --
10 9
(1 row(s) affected)
id parentid
-- --
11 10
(1 row(s) affected)
id parentid
-- --
12 11
(1 row(s) affected)
id parentid
-- --
13 12
(1 row(s) affected)
id parentid
-- --
14 13
(1 row(s) affected)
id parentid
-- --
15 14
(1 row(s) affected)
id parentid
-- --
16 15
(1 row(s) affected)
id parentid
-- --
17 16
(1 row(s) affected)
id parentid
-- --
18 17
(1 row(s) affected)
id parentid
-- --
19 18
(1 row(s) affected)
id parentid
-- --
20 19
(1 row(s) affected)
id parentid
-- --
21 20
(1 row(s) affected)
id parentid
-- --
22 21
(1 row(s) affected)
id parentid
-- --
23 22
(1 row(s) affected)
id parentid
-- --
24 23
(1 row(s) affected)
id parentid
-- --
25 24
(1 row(s) affected)
id parentid
-- --
26 25
(1 row(s) affected)
id parentid
-- --
27 26
(1 row(s) affected)
id parentid
-- --
28 27
(1 row(s) affected)
id parentid
-- --
29 28
(1 row(s) affected)
id parentid
-- --
30 29
(1 row(s) affected)
id parentid
-- --
31 30
(1 row(s) affected)
id parentid
-- --
32 31
(1 row(s) affected)
id parentid
-- --
33 32
(1 row(s) affected)
id parentid
-- --
34 33
(1 row(s) affected)
Server: Msg 217, Level 16, State 1, Procedure TRG_ParentChildTable,
Line 7
Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32).
*/
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message news:<eOG$Y6BEEHA.2404@.TK2MSFTNGP
11.phx.gbl>...
> John-
> How would I create a trigger between two tables where PK and FK are
> uniqueidentifiers?
> Thanks,
> Craig
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> same
> 32
> the
> key
> (uniqueidentifier).
> I
> so,|||Hi Craig
This may be an alternative, but if it may cause contention on this table and
you may be better off running a scheduled task to do the deletions in a qui
et period.
sp_dboption testdb, 'recursive triggers', false
sp_dboption testdb, 'recursive triggers'
/*
Needs to be off
OptionName CurrentSetting
-- --
recursive triggers off
*/
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
WHILE @.@.ROWCOUNT > 0
DELETE FROM ParentChildTable
FROM ParentChildTable
WHERE ParentId <> 0
AND ParentId NOT IN ( SELECT Id FROM ParentChildTable )
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
DELETE FROM ParentChildTable WHERE Id = 3
SELECT * FROM ParentChildTable
John

Partitioning an existing table

I am running SQL Server 2005 and am interested in partitioning a multi-
million row table, that contains a clustered index (which is comprised of two
columns), but the partitioning key is not part of that clustered index.
I have read about partitioning using ALTER TABLE on BOL and have searched the
web for examples of partitioning existing tables, but have had no success.
The only true examples I have come across use a CREATE TABLE statement. I
assume the ALTER TABLE would contain such a mechanism, but apparently I do
not understand. Is this possible using the ALTER TABLE statement where the
partitioning key is not part of the clustered index?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200706/1Hi cbrichards
The way to partition an existing table is to rebuild the clustered index on
a partition scheme. If the index is unique, partition keys must be a subset
of the index keys. So since you are rebuilding the index anyway, you can
redefine it to include the partitioning keys, or to make it nonunique. The
index rebuild would look something like this:
CREATE UNIQUE CLUSTERED INDEX your_index_name ON your_table
(original_index_key1, origininal_index_key2, partitioning_column)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
-- OR --
CREATE CLUSTERED INDEX your_index_name ON your_table (original_index_key1,
origininal_index_key2)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:73b0eb04f61ce@.uwe...
>I am running SQL Server 2005 and am interested in partitioning a multi-
> million row table, that contains a clustered index (which is comprised of
> two
> columns), but the partitioning key is not part of that clustered index.
> I have read about partitioning using ALTER TABLE on BOL and have searched
> the
> web for examples of partitioning existing tables, but have had no success.
> The only true examples I have come across use a CREATE TABLE statement. I
> assume the ALTER TABLE would contain such a mechanism, but apparently I do
> not understand. Is this possible using the ALTER TABLE statement where the
> partitioning key is not part of the clustered index?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200706/1
>

Partitioning an existing table

I am running SQL Server 2005 and am interested in partitioning a multi-
million row table, that contains a clustered index (which is comprised of two
columns), but the partitioning key is not part of that clustered index.
I have read about partitioning using ALTER TABLE on BOL and have searched the
web for examples of partitioning existing tables, but have had no success.
The only true examples I have come across use a CREATE TABLE statement. I
assume the ALTER TABLE would contain such a mechanism, but apparently I do
not understand. Is this possible using the ALTER TABLE statement where the
partitioning key is not part of the clustered index?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200706/1
Hi cbrichards
The way to partition an existing table is to rebuild the clustered index on
a partition scheme. If the index is unique, partition keys must be a subset
of the index keys. So since you are rebuilding the index anyway, you can
redefine it to include the partitioning keys, or to make it nonunique. The
index rebuild would look something like this:
CREATE UNIQUE CLUSTERED INDEX your_index_name ON your_table
(original_index_key1, origininal_index_key2, partitioning_column)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
-- OR --
CREATE CLUSTERED INDEX your_index_name ON your_table (original_index_key1,
origininal_index_key2)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:73b0eb04f61ce@.uwe...
>I am running SQL Server 2005 and am interested in partitioning a multi-
> million row table, that contains a clustered index (which is comprised of
> two
> columns), but the partitioning key is not part of that clustered index.
> I have read about partitioning using ALTER TABLE on BOL and have searched
> the
> web for examples of partitioning existing tables, but have had no success.
> The only true examples I have come across use a CREATE TABLE statement. I
> assume the ALTER TABLE would contain such a mechanism, but apparently I do
> not understand. Is this possible using the ALTER TABLE statement where the
> partitioning key is not part of the clustered index?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200706/1
>

Partitioning an existing table

I am running SQL Server 2005 and am interested in partitioning a multi-
million row table, that contains a clustered index (which is comprised of tw
o
columns), but the partitioning key is not part of that clustered index.
I have read about partitioning using ALTER TABLE on BOL and have searched th
e
web for examples of partitioning existing tables, but have had no success.
The only true examples I have come across use a CREATE TABLE statement. I
assume the ALTER TABLE would contain such a mechanism, but apparently I do
not understand. Is this possible using the ALTER TABLE statement where the
partitioning key is not part of the clustered index?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200706/1Hi cbrichards
The way to partition an existing table is to rebuild the clustered index on
a partition scheme. If the index is unique, partition keys must be a subset
of the index keys. So since you are rebuilding the index anyway, you can
redefine it to include the partitioning keys, or to make it nonunique. The
index rebuild would look something like this:
CREATE UNIQUE CLUSTERED INDEX your_index_name ON your_table
(original_index_key1, origininal_index_key2, partitioning_column)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
-- OR --
CREATE CLUSTERED INDEX your_index_name ON your_table (original_index_key1,
origininal_index_key2)
WITH DROP_EXISTING ON your_partitioning_scheme (partitioning_column)
GO
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:73b0eb04f61ce@.uwe...
>I am running SQL Server 2005 and am interested in partitioning a multi-
> million row table, that contains a clustered index (which is comprised of
> two
> columns), but the partitioning key is not part of that clustered index.
> I have read about partitioning using ALTER TABLE on BOL and have searched
> the
> web for examples of partitioning existing tables, but have had no success.
> The only true examples I have come across use a CREATE TABLE statement. I
> assume the ALTER TABLE would contain such a mechanism, but apparently I do
> not understand. Is this possible using the ALTER TABLE statement where the
> partitioning key is not part of the clustered index?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200706/1
>

Wednesday, March 28, 2012

Pareser take wrong index when using @name runtime variable

Pareser take wrong index when using @name runtime variable

Pareser take wrong index when using @name runtime variable

Friday, March 23, 2012

Parent Child index?

Partitioning an existing table

Partitioning an existing table

Partitioning an existing table

Parameterized Expression

Blog Archive

About Me