Hi all,
I have a query connecting users to my products by and user groups and
today I added a nonclustered index to my table the one that maps users
to user groups (added a nonclusted index to both userID and UserGroups
in ASC order). About 6 hours later our CPU usage went from .01 - .10%
to 100+%? So I removed it and disable some parts of my SQL to
improve the speed. What's happening here? Please help? Would
removing the index change my table back to it's previous index'
please helpppppppppppppppp...
Thx
MStill UPDATE STATISTICS.
"mazdotnet" <maflatoun@.gmail.com> wrote in message
news:1194993911.116978.257150@.v2g2000hsf.googlegroups.com...
> Hi all,
> I have a query connecting users to my products by and user groups and
> today I added a nonclustered index to my table the one that maps users
> to user groups (added a nonclusted index to both userID and UserGroups
> in ASC order). About 6 hours later our CPU usage went from .01 - .10%
> to 100+%? So I removed it and disable some parts of my SQL to
> improve the speed. What's happening here? Please help? Would
> removing the index change my table back to it's previous index'
> please helpppppppppppppppp...
>
> Thx
> M
>|||Can you show us the query + info about the indexes?
"mazdotnet" <maflatoun@.gmail.com> wrote in message
news:1194993911.116978.257150@.v2g2000hsf.googlegroups.com...
> Hi all,
> I have a query connecting users to my products by and user groups and
> today I added a nonclustered index to my table the one that maps users
> to user groups (added a nonclusted index to both userID and UserGroups
> in ASC order). About 6 hours later our CPU usage went from .01 - .10%
> to 100+%? So I removed it and disable some parts of my SQL to
> improve the speed. What's happening here? Please help? Would
> removing the index change my table back to it's previous index'
> please helpppppppppppppppp...
>
> Thx
> M
>
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Friday, March 30, 2012
Tuesday, March 20, 2012
Plan not using Index
Hi all, I have been running into this same issue for some time now and can't
come up with a valid reason why SQL is doing what s is doing...
In a nut shell I have three tables
t1 is a fact table
t2 is a time dimension table
t3 is a parameter table that get loaded prior to the query being executed.
Simple code
Select count (t1.stuff)
From t1
Inner join t2
On
t1.date_key = t2.date_key
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date
The optimizer performs a full table scan on the fact then applies the date
range filter... Though if I were to replace the start_date and end_date
values with hardcode values then the optimizer uses the index and pulls only
those records needed from the fact.
Really weird...
Any thoughts?
Thanks
EricStatistics for the tables/index are probably out of date. Update them
using UPDATE STATISTICS command. See if that helps. Or provide an index
hints to the query to specify which index you want it to use. Like:
Select count (t1.stuff)
>From t1
Inner join t2
On
t1.date_key = t2.date_key
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date
WITH (INDEX(index_name))|||Statistics for the tables/index are probably out of date. Update them
using UPDATE STATISTICS command. See if that helps. Or provide an index
hints to the query to specify which index you want it to use. Like:
Select count (t1.stuff)
>From t1
Inner join t2
On
t1.date_key = t2.date_key
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date
WITH (INDEX(index_name))|||Hello Green thank you for your quick reply.
Thats the odd thing... everything looking good. here are the stats: in
addtion supplying the index hint doesn't seem to change the plan result.
DBCC SHOW_STATISTICS
Statistics for FACT INDEX
Updated Rows Rows Sampled Steps
Density Average key length
-- -- -- --
-- --
Mar 6 2006 2:40PM 71680256 304424 197
160.55275 4.0
Statistics for TIME DIMENSION INDEX .
Updated Rows Rows Sampled Steps
Density Average key length
-- -- -- --
-- --
Feb 28 2006 6:46PM 7306 7306 4
1.368738E-4 4.0
DBCC SHOWCONTIG
DBCC SHOWCONTIG scanning Fact_TABLE' table...
Table: 'Pharmacy_Data_Fact_TABLE' (421576540); index ID: 4, database ID: 29
LEAF level scan performed.
- Pages Scanned........................: 155003
- Extents Scanned.......................: 24942
- Extent Switches.......................: 90108
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 21.50% [19376:90109]
- Logical Scan Fragmentation ..............: 13.55%
- Extent Scan Fragmentation ...............: 11.16%
- Avg. Bytes Free per Page................: 1143.1
- Avg. Page Density (full)................: 85.88%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'Time_Dimension' table...
Table: 'Time_Dimension' (597577167); index ID: 27, database ID: 29
LEAF level scan performed.
- Pages Scanned........................: 14
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 268.1
- Avg. Page Density (full)................: 96.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Green" wrote:
> Statistics for the tables/index are probably out of date. Update them
> using UPDATE STATISTICS command. See if that helps. Or provide an index
> hints to the query to specify which index you want it to use. Like:
> Select count (t1.stuff)
> Inner join t2
> On
> t1.date_key = t2.date_key
> Inner join t3
> on
> t2.date_number between t3.start_date and t3.end_date
> WITH (INDEX(index_name))
>|||Just a guess, but I don't think the optimizer knows what is in table T3, so
it cannot make a decision based on those dates. As a result, it is unable
to use the index on the dates, since they are unknown. Again, this is just
a guess and could be totally off.
Also, I don't think the statistics on table T3 reflect the current data, but
rather the (completely unrelated) data that was present when the statistics
were generated. I am assuming that statistics on this table are either non
existent, or were generated when the table had no data in it. Try inserting
a row into the table and generating statistics on it, then removing that
row, and running your process again. It is possible that having statistics
with a single valid row in this table will cause the optimizer to choose the
correct path.
Just a couple of ideas.
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C7CA8FC0-BD0A-4779-870E-9658E4C72084@.microsoft.com...
> Hi all, I have been running into this same issue for some time now and
can't
> come up with a valid reason why SQL is doing what s is doing...
> In a nut shell I have three tables
> t1 is a fact table
> t2 is a time dimension table
> t3 is a parameter table that get loaded prior to the query being executed.
> Simple code
> Select count (t1.stuff)
> From t1
> Inner join t2
> On
> t1.date_key = t2.date_key
> Inner join t3
> on
> t2.date_number between t3.start_date and t3.end_date
> The optimizer performs a full table scan on the fact then applies the date
> range filter... Though if I were to replace the start_date and end_date
> values with hardcode values then the optimizer uses the index and pulls
only
> those records needed from the fact.
> Really weird...
> Any thoughts?
> Thanks
> Eric
>|||Hi Jim,
I confirmed that the stats on t3 were up to date and truncated the table,
reloaded in it and once again updated the stats. Unfortunately the plan
result was the same.
I assume the optimizer know what is in a table based on the table stats
now taking what you had mentioned into count I think you may be on to
something... but the wrench is why is it if I use a SARG value or create an
additional year column in t3 and make the year column in t2 equal to year i
n
t3 the index get used.
Select count (t1.stuff)
From t1
Inner join t2
On
t1.date_key = t2.date_key
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date and
t2.year_number = t3.year_number
Thanks|||Eric,
It's impossible for the optimizer to do anything but make generic rowcount
estimates for predicates that involve comparing columns from one table
with columns from another. Here, for example, the actual limiting values
of t2.date_number are unknown until run-time, so the histogram of
t2.date_number values is basically useless for plan costing.
By adding an extra superfluous condition to your join's ON clause,
you are reducing the generic rowcount estimate used by the optimizer.
With nothing else to go on, the optimizer will assume that fewer rows
match (<this condition> AND <that condition> ) than match (<this condition> ).
Assuming that fewer rows from t2 match the condition, the estimated cost
of using the index drops, while the cost of a table scan does not drop, and
the change is enough to make the plan that uses an index appear cheapter.
Steve Kass
Drew University
Eric wrote:
>Hi Jim,
>I confirmed that the stats on t3 were up to date and truncated the table,
>reloaded in it and once again updated the stats. Unfortunately the plan
>result was the same.
>I assume the optimizer know what is in a table based on the table stats
>now taking what you had mentioned into count I think you may be on to
>something... but the wrench is why is it if I use a SARG value or create an
>additional year column in t3 and make the year column in t2 equal to year
in
>t3 the index get used.
>Select count (t1.stuff)
>From t1
>Inner join t2
>On
>t1.date_key = t2.date_key
>Inner join t3
>on
>t2.date_number between t3.start_date and t3.end_date and
>t2.year_number = t3.year_number
>Thanks
>
>|||I would expect that equality is handled very different then between for
optimizational purposes. This makes perfect sense since a single value is
more exlicit and easier to determine ahead of time than a range of values.
However, you did not state whether or not this year_number column is indexed
or not. If there is an index on this field , then I would expect it to make
a difference simply because it is easier for the optimizer to know that it
is targeting a certain percentage of rows with the join. If there is no
index, then Steve Kass's explanation seems the only way to explain it.
Regardless, I find that the behavior of the optimizer never ceases to amaze
me.
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2DC2E151-6AB0-472B-B385-3B9918F34F73@.microsoft.com...
> Hi Jim,
> I confirmed that the stats on t3 were up to date and truncated the table,
> reloaded in it and once again updated the stats. Unfortunately the plan
> result was the same.
> I assume the optimizer know what is in a table based on the table stats
> now taking what you had mentioned into count I think you may be on to
> something... but the wrench is why is it if I use a SARG value or create
an
> additional year column in t3 and make the year column in t2 equal to year
in
> t3 the index get used.
> Select count (t1.stuff)
> From t1
> Inner join t2
> On
> t1.date_key = t2.date_key
> Inner join t3
> on
> t2.date_number between t3.start_date and t3.end_date and
> t2.year_number = t3.year_number
> Thanks
>|||Try running the SQL through the SQL Optimizer for Visual Studio. It will
rewrite your statement in every possible fashion to get you the best
performing SQL. It has a free 30 day trial. You can find it at
http://www.extensibles.com/modules...=Products&op=NS
examnotes <Eric@.discussions.microsoft.com> wrote in
news:C7CA8FC0-BD0A-4779-870E-9658E4C72084@.microsoft.com:
>
The Relentless One
Debugging is a state of mind
http://www.extensibles.com/
come up with a valid reason why SQL is doing what s is doing...
In a nut shell I have three tables
t1 is a fact table
t2 is a time dimension table
t3 is a parameter table that get loaded prior to the query being executed.
Simple code
Select count (t1.stuff)
From t1
Inner join t2
On
t1.date_key = t2.date_key
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date
The optimizer performs a full table scan on the fact then applies the date
range filter... Though if I were to replace the start_date and end_date
values with hardcode values then the optimizer uses the index and pulls only
those records needed from the fact.
Really weird...
Any thoughts?
Thanks
EricStatistics for the tables/index are probably out of date. Update them
using UPDATE STATISTICS command. See if that helps. Or provide an index
hints to the query to specify which index you want it to use. Like:
Select count (t1.stuff)
>From t1
Inner join t2
On
t1.date_key = t2.date_key
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date
WITH (INDEX(index_name))|||Statistics for the tables/index are probably out of date. Update them
using UPDATE STATISTICS command. See if that helps. Or provide an index
hints to the query to specify which index you want it to use. Like:
Select count (t1.stuff)
>From t1
Inner join t2
On
t1.date_key = t2.date_key
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date
WITH (INDEX(index_name))|||Hello Green thank you for your quick reply.
Thats the odd thing... everything looking good. here are the stats: in
addtion supplying the index hint doesn't seem to change the plan result.
DBCC SHOW_STATISTICS
Statistics for FACT INDEX
Updated Rows Rows Sampled Steps
Density Average key length
-- -- -- --
-- --
Mar 6 2006 2:40PM 71680256 304424 197
160.55275 4.0
Statistics for TIME DIMENSION INDEX .
Updated Rows Rows Sampled Steps
Density Average key length
-- -- -- --
-- --
Feb 28 2006 6:46PM 7306 7306 4
1.368738E-4 4.0
DBCC SHOWCONTIG
DBCC SHOWCONTIG scanning Fact_TABLE' table...
Table: 'Pharmacy_Data_Fact_TABLE' (421576540); index ID: 4, database ID: 29
LEAF level scan performed.
- Pages Scanned........................: 155003
- Extents Scanned.......................: 24942
- Extent Switches.......................: 90108
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 21.50% [19376:90109]
- Logical Scan Fragmentation ..............: 13.55%
- Extent Scan Fragmentation ...............: 11.16%
- Avg. Bytes Free per Page................: 1143.1
- Avg. Page Density (full)................: 85.88%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SHOWCONTIG scanning 'Time_Dimension' table...
Table: 'Time_Dimension' (597577167); index ID: 27, database ID: 29
LEAF level scan performed.
- Pages Scanned........................: 14
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 268.1
- Avg. Page Density (full)................: 96.69%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Green" wrote:
> Statistics for the tables/index are probably out of date. Update them
> using UPDATE STATISTICS command. See if that helps. Or provide an index
> hints to the query to specify which index you want it to use. Like:
> Select count (t1.stuff)
> Inner join t2
> On
> t1.date_key = t2.date_key
> Inner join t3
> on
> t2.date_number between t3.start_date and t3.end_date
> WITH (INDEX(index_name))
>|||Just a guess, but I don't think the optimizer knows what is in table T3, so
it cannot make a decision based on those dates. As a result, it is unable
to use the index on the dates, since they are unknown. Again, this is just
a guess and could be totally off.
Also, I don't think the statistics on table T3 reflect the current data, but
rather the (completely unrelated) data that was present when the statistics
were generated. I am assuming that statistics on this table are either non
existent, or were generated when the table had no data in it. Try inserting
a row into the table and generating statistics on it, then removing that
row, and running your process again. It is possible that having statistics
with a single valid row in this table will cause the optimizer to choose the
correct path.
Just a couple of ideas.
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C7CA8FC0-BD0A-4779-870E-9658E4C72084@.microsoft.com...
> Hi all, I have been running into this same issue for some time now and
can't
> come up with a valid reason why SQL is doing what s is doing...
> In a nut shell I have three tables
> t1 is a fact table
> t2 is a time dimension table
> t3 is a parameter table that get loaded prior to the query being executed.
> Simple code
> Select count (t1.stuff)
> From t1
> Inner join t2
> On
> t1.date_key = t2.date_key
> Inner join t3
> on
> t2.date_number between t3.start_date and t3.end_date
> The optimizer performs a full table scan on the fact then applies the date
> range filter... Though if I were to replace the start_date and end_date
> values with hardcode values then the optimizer uses the index and pulls
only
> those records needed from the fact.
> Really weird...
> Any thoughts?
> Thanks
> Eric
>|||Hi Jim,
I confirmed that the stats on t3 were up to date and truncated the table,
reloaded in it and once again updated the stats. Unfortunately the plan
result was the same.
I assume the optimizer know what is in a table based on the table stats
now taking what you had mentioned into count I think you may be on to
something... but the wrench is why is it if I use a SARG value or create an
additional year column in t3 and make the year column in t2 equal to year i
n
t3 the index get used.
Select count (t1.stuff)
From t1
Inner join t2
On
t1.date_key = t2.date_key
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date and
t2.year_number = t3.year_number
Thanks|||Eric,
It's impossible for the optimizer to do anything but make generic rowcount
estimates for predicates that involve comparing columns from one table
with columns from another. Here, for example, the actual limiting values
of t2.date_number are unknown until run-time, so the histogram of
t2.date_number values is basically useless for plan costing.
By adding an extra superfluous condition to your join's ON clause,
you are reducing the generic rowcount estimate used by the optimizer.
With nothing else to go on, the optimizer will assume that fewer rows
match (<this condition> AND <that condition> ) than match (<this condition> ).
Assuming that fewer rows from t2 match the condition, the estimated cost
of using the index drops, while the cost of a table scan does not drop, and
the change is enough to make the plan that uses an index appear cheapter.
Steve Kass
Drew University
Eric wrote:
>Hi Jim,
>I confirmed that the stats on t3 were up to date and truncated the table,
>reloaded in it and once again updated the stats. Unfortunately the plan
>result was the same.
>I assume the optimizer know what is in a table based on the table stats
>now taking what you had mentioned into count I think you may be on to
>something... but the wrench is why is it if I use a SARG value or create an
>additional year column in t3 and make the year column in t2 equal to year
in
>t3 the index get used.
>Select count (t1.stuff)
>From t1
>Inner join t2
>On
>t1.date_key = t2.date_key
>Inner join t3
>on
>t2.date_number between t3.start_date and t3.end_date and
>t2.year_number = t3.year_number
>Thanks
>
>|||I would expect that equality is handled very different then between for
optimizational purposes. This makes perfect sense since a single value is
more exlicit and easier to determine ahead of time than a range of values.
However, you did not state whether or not this year_number column is indexed
or not. If there is an index on this field , then I would expect it to make
a difference simply because it is easier for the optimizer to know that it
is targeting a certain percentage of rows with the join. If there is no
index, then Steve Kass's explanation seems the only way to explain it.
Regardless, I find that the behavior of the optimizer never ceases to amaze
me.
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2DC2E151-6AB0-472B-B385-3B9918F34F73@.microsoft.com...
> Hi Jim,
> I confirmed that the stats on t3 were up to date and truncated the table,
> reloaded in it and once again updated the stats. Unfortunately the plan
> result was the same.
> I assume the optimizer know what is in a table based on the table stats
> now taking what you had mentioned into count I think you may be on to
> something... but the wrench is why is it if I use a SARG value or create
an
> additional year column in t3 and make the year column in t2 equal to year
in
> t3 the index get used.
> Select count (t1.stuff)
> From t1
> Inner join t2
> On
> t1.date_key = t2.date_key
> Inner join t3
> on
> t2.date_number between t3.start_date and t3.end_date and
> t2.year_number = t3.year_number
> Thanks
>|||Try running the SQL through the SQL Optimizer for Visual Studio. It will
rewrite your statement in every possible fashion to get you the best
performing SQL. It has a free 30 day trial. You can find it at
http://www.extensibles.com/modules...=Products&op=NS
examnotes <Eric@.discussions.microsoft.com> wrote in
news:C7CA8FC0-BD0A-4779-870E-9658E4C72084@.microsoft.com:
>
The Relentless One
Debugging is a state of mind
http://www.extensibles.com/
Friday, March 9, 2012
PL/SQL cursors and index tables
I'm trying to write a script that stores values in an index table using a cursor. My syntax looks write but i'm getting the following message:
Please enter the number of rows to be selected: 5
last_name_tbl last_name_tbl_type;
*
ERROR at line 9:
ORA-06550: line 43, column 27:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 43, column 4:
PL/SQL: SQL Statement ignoredOriginally posted by bbk
I'm trying to write a script that stores values in an index table using a cursor. My syntax looks write but i'm getting the following message:
Please enter the number of rows to be selected: 5
last_name_tbl last_name_tbl_type;
*
ERROR at line 9:
ORA-06550: line 43, column 27:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 43, column 4:
PL/SQL: SQL Statement ignored
Show your code
Please enter the number of rows to be selected: 5
last_name_tbl last_name_tbl_type;
*
ERROR at line 9:
ORA-06550: line 43, column 27:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 43, column 4:
PL/SQL: SQL Statement ignoredOriginally posted by bbk
I'm trying to write a script that stores values in an index table using a cursor. My syntax looks write but i'm getting the following message:
Please enter the number of rows to be selected: 5
last_name_tbl last_name_tbl_type;
*
ERROR at line 9:
ORA-06550: line 43, column 27:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 43, column 4:
PL/SQL: SQL Statement ignored
Show your code
PL/sql
I have problem in creating index on particular column with pL/SQL procedure . I dont know what command to and how to use?
how to run that PL/sql procedure. And howto check that wether index is created or not?
Please guide meHere's the example:
/* create a procedure */
CREATE OR REPLACE PROCEDURE brisime
AS
ci VARCHAR2 (255);
BEGIN
ci := 'create index i1 on tob_pool_ocit (pool_id, omm_id)';
EXECUTE IMMEDIATE ci;
END;
/* execute the procedure */
BEGIN
brisime ();
END;
/* checking whether it is created */
SELECT *
FROM user_indexes
WHERE index_name = 'I1';
how to run that PL/sql procedure. And howto check that wether index is created or not?
Please guide meHere's the example:
/* create a procedure */
CREATE OR REPLACE PROCEDURE brisime
AS
ci VARCHAR2 (255);
BEGIN
ci := 'create index i1 on tob_pool_ocit (pool_id, omm_id)';
EXECUTE IMMEDIATE ci;
END;
/* execute the procedure */
BEGIN
brisime ();
END;
/* checking whether it is created */
SELECT *
FROM user_indexes
WHERE index_name = 'I1';
PK vs Unique Clustered Index
When not using any enforced relationships in a database (which I know we should, but I like most of you, have inherited sub-optimal design), is there any real difference between using a Primary Key (to which no foreign keys are tied) and using a Unique Clustered index?
Thanks for your thoughts
Other that the NULL value issue, both will maintain uniqueness if that is your purpose.
|||Yeah, the "UNIQUE" will assure uniqueness. I know that created a PK on a table creates a UNIQUE CLUSTERED index on whatever field(s) are included in the key.
My question is, is there a real difference between creating a PK on a table, and creating the equivalent UNIQUE CLUSTERED INDEX on the same table instead of a PK? If the PK is not referenced in any FK, then in my thinking, there is no difference.
Thoughts...anyone?
PK vs Unique Clustered Index
When not using any enforced relationships in a database (which I know we should, but I like most of you, have inherited sub-optimal design), is there any real difference between using a Primary Key (to which no foreign keys are tied) and using a Unique Clustered index?
Thanks for your thoughts
Other that the NULL value issue, both will maintain uniqueness if that is your purpose.
|||Yeah, the "UNIQUE" will assure uniqueness. I know that created a PK on a table creates a UNIQUE CLUSTERED index on whatever field(s) are included in the key.
My question is, is there a real difference between creating a PK on a table, and creating the equivalent UNIQUE CLUSTERED INDEX on the same table instead of a PK? If the PK is not referenced in any FK, then in my thinking, there is no difference.
Thoughts...anyone?
PK still shows fragmentation after ALTER INDEX.. REBUILD
I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can’t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?
How are you measuring the extent of the fragmentation?
Thanks
ray
|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||Can you please post the results of the DBCC SHOWCONTIG statement?
Thanks
Ray
|||Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set
TableID ,TableName, IndexID ,IndexName ,Fragmentation
87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333
Results from: DBCC SHOWCONTIG (87671360,1)
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (87671360); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]
- Logical Scan Fragmentation ..................: 83.33%
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 387.8
- Avg. Page Density (full).....................: 95.21%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
|||Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.
SET NOCOUNT ON;
DECLARE @.v_Object_Id int;
DECLARE @.v_Index_Id int;
DECLARE @.v_Schema_Nm nvarchar(130);
DECLARE @.v_Object_Nm nvarchar(130);
DECLARE @.v_Index_Nm nvarchar(130);
DECLARE @.v_Rebuild_Stmt nvarchar(2000);
DECLARE @.v_Frag_Flt float
DECLARE @.v_Start_DtTm datetime;
DECLARE @.v_End_DtTm datetime;
DECLARE @.v_Duration_DtTm datetime;
--Uncomment if you are running the code and not the procedure
DECLARE @.ip_Frag_Limit Int
SET @.ip_Frag_Limit = 30
IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))
BEGIN
PRINT 'You cannot execute this procedure in this database'
RETURN;
END
-- We want records in a permenant table so we see if it exists from a previous run
-- If it does exist, then we TRUNCATE it, otherwise, we create it
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))
TRUNCATE TABLE [dbo].[DBA_IndexStatistics]
ELSE
CREATE TABLE [dbo].[DBA_IndexStatistics](
[Object_Id] int NULL,
[Table_Nm] varchar(255) NULL,
[Index_Id] int NULL,
[Index_Nm] varchar(255) NULL,
[Frag] float NULL,
[IndexRebuilt_Ind] BIT NULL,
[Start_DtTm] datetime NULL,
[End_DtTm] datetime NULL,
[Duration] varchar(20) NULL
) ON [PRIMARY]
-- Load our temporary working table that will contain all of our index information
SELECT object_id AS [Object_Id], CAST('' AS sysname) AS [Table_Nm], index_id AS [Index_Id], CAST('' AS sysname) AS [Index_Nm], avg_fragmentation_in_percent AS [Frag],0 AS [IndexRebuilt_Ind], GetDate() AS [Start_DtTm], GetDate() AS [End_DtTm], '00:00:00' AS [Duration]
INTO #Tmp_Index_Stats
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)
WHERE Index_ID > 0
ORDER BY [Object_Id],[Index_Id];
-- Create a cursor to run through each distinct table object_id from our working table
DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]
OPEN cur_Object
FETCH NEXT FROM cur_Object INTO @.v_Object_Id
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure
IF ((SELECT MIN([Index_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id) = 1) AND ((SELECT COUNT([Object_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id AND [Frag] >= @.ip_Frag_Limit) > 0)
BEGIN
-- If true then rebuild the Clustered Index only
SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.v_Object_Id;
SELECT @.v_Index_Nm = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @.v_Object_Id AND Index_ID = 1;
-- Set Start time before rebuild
SET @.v_Start_DtTm = GetDate()
--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
EXEC sp_executesql @.v_Rebuild_Stmt
--PRINT @.v_Rebuild_Stmt
-- Set End Time and Duration after rebuild
SET @.v_End_DtTm = GetDate()
SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)
-- Update values in our temp table that we will store in the permenant table.
UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
END
ELSE
-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits
BEGIN
-- Get a list of NonClustered indexes for this one table object_id
DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]
OPEN cur_Index
FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.
IF @.v_Frag_Flt >= @.ip_Frag_Limit
-- If index is more fragmented than our limit then rebuild this index
BEGIN
SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.v_Object_Id;
SELECT @.v_Index_Nm = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;
-- Set Start time before rebuild
SET @.v_Start_DtTm = GetDate()
SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
EXEC sp_executesql @.v_Rebuild_Stmt
--PRINT @.v_Rebuild_Stmt
-- Set End Time and Duration after rebuild
SET @.v_End_DtTm = GetDate()
SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)
-- Update values in our temp table that we will store in the permenant table.
UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
END
FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt
END
CLOSE cur_Index
DEALLOCATE cur_Index
END
FETCH NEXT FROM cur_Object into @.v_Object_Id
END
CLOSE cur_Object
DEALLOCATE cur_Object
-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.
INSERT [DBA_IndexStatistics] SELECT [Object_Id], [Table_Nm], [Index_Id], [Index_Nm], [Frag], [IndexRebuilt_Ind], [Start_DtTm], [End_DtTm], [Duration] FROM #Tmp_Index_Stats WHERE [IndexRebuilt_Ind] = 1 ORDER BY [Duration] DESC
-- Drop our temporary table
DROP TABLE #Tmp_Index_Stats
SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC
|||One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.
Would that explain the behaviour you are seeing?
|||No, I realized that. If you look at the code block for the Clustered Indexes, there is a line commented out that runs the ALTER INDEX ALL ON... However, this still shows that the Primary Key Clustered Indexes retain the same fragmentation levels.PK still shows fragmentation after ALTER INDEX.. REBUILD
I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can’t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?
How are you measuring the extent of the fragmentation?
Thanks
ray
|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||Can you please post the results of the DBCC SHOWCONTIG statement?
Thanks
Ray
|||Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set
TableID ,TableName, IndexID ,IndexName ,Fragmentation
87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333
Results from: DBCC SHOWCONTIG (87671360,1)
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (87671360); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]
- Logical Scan Fragmentation ..................: 83.33%
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 387.8
- Avg. Page Density (full).....................: 95.21%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
|||Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.
SET NOCOUNT ON;
DECLARE @.v_Object_Id int;
DECLARE @.v_Index_Id int;
DECLARE @.v_Schema_Nm nvarchar(130);
DECLARE @.v_Object_Nm nvarchar(130);
DECLARE @.v_Index_Nm nvarchar(130);
DECLARE @.v_Rebuild_Stmt nvarchar(2000);
DECLARE @.v_Frag_Flt float
DECLARE @.v_Start_DtTm datetime;
DECLARE @.v_End_DtTm datetime;
DECLARE @.v_Duration_DtTm datetime;
--Uncomment if you are running the code and not the procedure
DECLARE @.ip_Frag_Limit Int
SET @.ip_Frag_Limit = 30
IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))
BEGIN
PRINT 'You cannot execute this procedure in this database'
RETURN;
END
-- We want records in a permenant table so we see if it exists from a previous run
-- If it does exist, then we TRUNCATE it, otherwise, we create it
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))
TRUNCATE TABLE [dbo].[DBA_IndexStatistics]
ELSE
CREATE TABLE [dbo].[DBA_IndexStatistics](
[Object_Id] int NULL,
[Table_Nm] varchar(255) NULL,
[Index_Id] int NULL,
[Index_Nm] varchar(255) NULL,
[Frag] float NULL,
[IndexRebuilt_Ind] BIT NULL,
[Start_DtTm] datetime NULL,
[End_DtTm] datetime NULL,
[Duration] varchar(20) NULL
) ON [PRIMARY]
-- Load our temporary working table that will contain all of our index information
SELECT object_id AS [Object_Id], CAST('' AS sysname) AS [Table_Nm], index_id AS [Index_Id], CAST('' AS sysname) AS [Index_Nm], avg_fragmentation_in_percent AS [Frag],0 AS [IndexRebuilt_Ind], GetDate() AS [Start_DtTm], GetDate() AS [End_DtTm], '00:00:00' AS [Duration]
INTO #Tmp_Index_Stats
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)
WHERE Index_ID > 0
ORDER BY [Object_Id],[Index_Id];
-- Create a cursor to run through each distinct table object_id from our working table
DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]
OPEN cur_Object
FETCH NEXT FROM cur_Object INTO @.v_Object_Id
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure
IF ((SELECT MIN([Index_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id) = 1) AND ((SELECT COUNT([Object_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id AND [Frag] >= @.ip_Frag_Limit) > 0)
BEGIN
-- If true then rebuild the Clustered Index only
SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.v_Object_Id;
SELECT @.v_Index_Nm = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @.v_Object_Id AND Index_ID = 1;
-- Set Start time before rebuild
SET @.v_Start_DtTm = GetDate()
--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
EXEC sp_executesql @.v_Rebuild_Stmt
--PRINT @.v_Rebuild_Stmt
-- Set End Time and Duration after rebuild
SET @.v_End_DtTm = GetDate()
SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)
-- Update values in our temp table that we will store in the permenant table.
UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
END
ELSE
-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits
BEGIN
-- Get a list of NonClustered indexes for this one table object_id
DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]
OPEN cur_Index
FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.
IF @.v_Frag_Flt >= @.ip_Frag_Limit
-- If index is more fragmented than our limit then rebuild this index
BEGIN
SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.v_Object_Id;
SELECT @.v_Index_Nm = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;
-- Set Start time before rebuild
SET @.v_Start_DtTm = GetDate()
SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
EXEC sp_executesql @.v_Rebuild_Stmt
--PRINT @.v_Rebuild_Stmt
-- Set End Time and Duration after rebuild
SET @.v_End_DtTm = GetDate()
SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)
-- Update values in our temp table that we will store in the permenant table.
UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
END
FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt
END
CLOSE cur_Index
DEALLOCATE cur_Index
END
FETCH NEXT FROM cur_Object into @.v_Object_Id
END
CLOSE cur_Object
DEALLOCATE cur_Object
-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.
INSERT [DBA_IndexStatistics] SELECT [Object_Id], [Table_Nm], [Index_Id], [Index_Nm], [Frag], [IndexRebuilt_Ind], [Start_DtTm], [End_DtTm], [Duration] FROM #Tmp_Index_Stats WHERE [IndexRebuilt_Ind] = 1 ORDER BY [Duration] DESC
-- Drop our temporary table
DROP TABLE #Tmp_Index_Stats
SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC
|||One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.
Would that explain the behaviour you are seeing?
|||No, I realized that. If you look at the code block for the Clustered Indexes, there is a line commented out that runs the ALTER INDEX ALL ON... However, this still shows that the Primary Key Clustered Indexes retain the same fragmentation levels.PK still shows fragmentation after ALTER INDEX.. REBUILD
I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can’t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?
How are you measuring the extent of the fragmentation?
Thanks
ray
|||I run sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) again to see the fragmentation. I also run DBCC SHOWCONTIG with the same results.|||Can you please post the results of the DBCC SHOWCONTIG statement?
Thanks
Ray
|||Results from: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) -- Note, I added table names and Index names to the result set
TableID ,TableName, IndexID ,IndexName ,Fragmentation
87671360, [MyTable], 1 ,[MyIndex] ,83.3333333333333
Results from: DBCC SHOWCONTIG (87671360,1)
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (87671360); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 6
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 16.67% [1:6]
- Logical Scan Fragmentation ..................: 83.33%
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 387.8
- Avg. Page Density (full).....................: 95.21%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
|||Here is the code that I put in a proc but it can be run in any non sys databases. the only thing is it does create a permanent table for reporting but you can drop it any time.
SET NOCOUNT ON;
DECLARE @.v_Object_Id int;
DECLARE @.v_Index_Id int;
DECLARE @.v_Schema_Nm nvarchar(130);
DECLARE @.v_Object_Nm nvarchar(130);
DECLARE @.v_Index_Nm nvarchar(130);
DECLARE @.v_Rebuild_Stmt nvarchar(2000);
DECLARE @.v_Frag_Flt float
DECLARE @.v_Start_DtTm datetime;
DECLARE @.v_End_DtTm datetime;
DECLARE @.v_Duration_DtTm datetime;
--Uncomment if you are running the code and not the procedure
DECLARE @.ip_Frag_Limit Int
SET @.ip_Frag_Limit = 30
IF (((SELECT DB_NAME(DB_ID())) = 'master') OR ((SELECT DB_NAME(DB_ID())) = 'model') OR ((SELECT DB_NAME(DB_ID())) = 'msdb') OR ((SELECT DB_NAME(DB_ID())) = 'tempdb'))
BEGIN
PRINT 'You cannot execute this procedure in this database'
RETURN;
END
-- We want records in a permenant table so we see if it exists from a previous run
-- If it does exist, then we TRUNCATE it, otherwise, we create it
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBA_IndexStatistics]') AND type in (N'U'))
TRUNCATE TABLE [dbo].[DBA_IndexStatistics]
ELSE
CREATE TABLE [dbo].[DBA_IndexStatistics](
[Object_Id] int NULL,
[Table_Nm] varchar(255) NULL,
[Index_Id] int NULL,
[Index_Nm] varchar(255) NULL,
[Frag] float NULL,
[IndexRebuilt_Ind] BIT NULL,
[Start_DtTm] datetime NULL,
[End_DtTm] datetime NULL,
[Duration] varchar(20) NULL
) ON [PRIMARY]
-- Load our temporary working table that will contain all of our index information
SELECT object_id AS [Object_Id], CAST('' AS sysname) AS [Table_Nm], index_id AS [Index_Id], CAST('' AS sysname) AS [Index_Nm], avg_fragmentation_in_percent AS [Frag],0 AS [IndexRebuilt_Ind], GetDate() AS [Start_DtTm], GetDate() AS [End_DtTm], '00:00:00' AS [Duration]
INTO #Tmp_Index_Stats
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)
WHERE Index_ID > 0
ORDER BY [Object_Id],[Index_Id];
-- Create a cursor to run through each distinct table object_id from our working table
DECLARE cur_Object CURSOR FAST_FORWARD FOR SELECT DISTINCT [Object_Id] FROM #Tmp_Index_Stats ORDER BY [Object_Id]
OPEN cur_Object
FETCH NEXT FROM cur_Object INTO @.v_Object_Id
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Check to see if the table has a Clustered index AND has any other NonClustered index with fragmentation >= the limit passed in the procedure
IF ((SELECT MIN([Index_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id) = 1) AND ((SELECT COUNT([Object_Id]) FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id AND [Frag] >= @.ip_Frag_Limit) > 0)
BEGIN
-- If true then rebuild the Clustered Index only
SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.v_Object_Id;
SELECT @.v_Index_Nm = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @.v_Object_Id AND Index_ID = 1;
-- Set Start time before rebuild
SET @.v_Start_DtTm = GetDate()
--SET @.v_Rebuild_Stmt = N'ALTER INDEX ALL ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
EXEC sp_executesql @.v_Rebuild_Stmt
--PRINT @.v_Rebuild_Stmt
-- Set End Time and Duration after rebuild
SET @.v_End_DtTm = GetDate()
SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)
-- Update values in our temp table that we will store in the permenant table.
UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = 1
END
ELSE
-- In this case, we do not have a Clustered index. Then we need to check each individual NonClustered index for fragmentation limits
BEGIN
-- Get a list of NonClustered indexes for this one table object_id
DECLARE cur_Index CURSOR FAST_FORWARD FOR SELECT [Index_Id], [Frag] FROM #Tmp_Index_Stats WHERE [Object_Id] = @.v_Object_Id ORDER BY [Index_Id]
OPEN cur_Index
FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Compare actual index fragmentation to our fragmentation limit passed in to the procedure.
IF @.v_Frag_Flt >= @.ip_Frag_Limit
-- If index is more fragmented than our limit then rebuild this index
BEGIN
SELECT @.v_Object_Nm = QUOTENAME(o.name), @.v_Schema_Nm = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.v_Object_Id;
SELECT @.v_Index_Nm = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @.v_Object_Id AND Index_ID = @.v_Index_Id;
-- Set Start time before rebuild
SET @.v_Start_DtTm = GetDate()
SET @.v_Rebuild_Stmt = N'ALTER INDEX ' + @.v_Index_Nm + N' ON ' + @.v_Schema_Nm + N'.' + @.v_Object_Nm + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON)';
EXEC sp_executesql @.v_Rebuild_Stmt
--PRINT @.v_Rebuild_Stmt
-- Set End Time and Duration after rebuild
SET @.v_End_DtTm = GetDate()
SET @.v_Duration_DtTm = (@.v_End_DtTm - @.v_Start_DtTm)
-- Update values in our temp table that we will store in the permenant table.
UPDATE #Tmp_Index_Stats SET [IndexRebuilt_Ind] = 1 WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Table_Nm] = @.v_Object_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Index_Nm] = @.v_Index_Nm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Start_DtTm] = @.v_Start_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [End_DtTm] = @.v_End_DtTm WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
UPDATE #Tmp_Index_Stats SET [Duration] = CONVERT(Varchar(20),@.v_Duration_DtTm,108) WHERE [Object_Id] = @.v_Object_Id AND [Index_Id] = @.v_Index_Id
END
FETCH NEXT FROM cur_Index into @.v_Index_Id, @.v_Frag_Flt
END
CLOSE cur_Index
DEALLOCATE cur_Index
END
FETCH NEXT FROM cur_Object into @.v_Object_Id
END
CLOSE cur_Object
DEALLOCATE cur_Object
-- Populate our permamnet table for our viewing. we only want to see what was rebuilt and how long it took.
INSERT [DBA_IndexStatistics] SELECT [Object_Id], [Table_Nm], [Index_Id], [Index_Nm], [Frag], [IndexRebuilt_Ind], [Start_DtTm], [End_DtTm], [Duration] FROM #Tmp_Index_Stats WHERE [IndexRebuilt_Ind] = 1 ORDER BY [Duration] DESC
-- Drop our temporary table
DROP TABLE #Tmp_Index_Stats
SELECT * FROM [DBA_IndexStatistics] ORDER BY [Duration] DESC
|||One thing i would say is that in SQL2005 rebuilding a clustered index does NOT rebuild all associated nonclustered indexes.
Would that explain the behaviour you are seeing?
|||No, I realized that. If you look at the code block for the Clustered Indexes, there is a line commented out that runs the ALTER INDEX ALL ON... However, this still shows that the Primary Key Clustered Indexes retain the same fragmentation levels.PK Index name
I have table tipDok with primary key, which is varchar data type.
The name of the index(primary key) is generated automatically and is:
PK__TipDOK__1EC48A19
I select: ALL tasks- Export data in enterprice manager and copy objects
option and then select this table to be copied to some other database.
The table is copied successfully and is identical, only the name of index is
now different:
PK__TipDOK__267ABA7A
Why?
I would like that also the name is identical.
Because I have program for comparing the datatbases and it gives me the
difference between this table just because of the index name.
Then I have errors when I synchronize databases, because the index can't
be dropped.
Any idea?
lp,
Simonwhat you need is a program that recognises that the primary keys are the sam
e
and only the name is different so it renames the key. DB Ghost
(http://www.dbghost.com) does this and does it for foreign keys as well.
"simon" wrote:
> I have table tipDok with primary key, which is varchar data type.
> The name of the index(primary key) is generated automatically and is:
> PK__TipDOK__1EC48A19
> I select: ALL tasks- Export data in enterprice manager and copy objects
> option and then select this table to be copied to some other database.
> The table is copied successfully and is identical, only the name of index
is
> now different:
> PK__TipDOK__267ABA7A
> Why?
> I would like that also the name is identical.
> Because I have program for comparing the datatbases and it gives me the
> difference between this table just because of the index name.
> Then I have errors when I synchronize databases, because the index can't
> be dropped.
> Any idea?
> lp,
> Simon
>
>|||In Enterprise Manager, right-click on your database, select All Tasks, then
Generate SQL Script.
Select whatever tables you want to copy, then go to the Options tab and make
sure "Select PRIMARY keys, FOREIGN keys, defaults, and check constriants" is
selected.
Use the script this generates to create a new, empty database. Then you can
use the DTS wizard to copy the data in from the old database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"simon" <simon.zupan@.iware.si> wrote in message
news:iQ%Td.9584$F6.1864547@.news.siol.net...
> I have table tipDok with primary key, which is varchar data type.
> The name of the index(primary key) is generated automatically and is:
> PK__TipDOK__1EC48A19
> I select: ALL tasks- Export data in enterprice manager and copy objects
> option and then select this table to be copied to some other database.
> The table is copied successfully and is identical, only the name of index
is
> now different:
> PK__TipDOK__267ABA7A
> Why?
> I would like that also the name is identical.
> Because I have program for comparing the datatbases and it gives me the
> difference between this table just because of the index name.
> Then I have errors when I synchronize databases, because the index can't
> be dropped.
> Any idea?
> lp,
> Simon
>
The name of the index(primary key) is generated automatically and is:
PK__TipDOK__1EC48A19
I select: ALL tasks- Export data in enterprice manager and copy objects
option and then select this table to be copied to some other database.
The table is copied successfully and is identical, only the name of index is
now different:
PK__TipDOK__267ABA7A
Why?
I would like that also the name is identical.
Because I have program for comparing the datatbases and it gives me the
difference between this table just because of the index name.
Then I have errors when I synchronize databases, because the index can't
be dropped.
Any idea?
lp,
Simonwhat you need is a program that recognises that the primary keys are the sam
e
and only the name is different so it renames the key. DB Ghost
(http://www.dbghost.com) does this and does it for foreign keys as well.
"simon" wrote:
> I have table tipDok with primary key, which is varchar data type.
> The name of the index(primary key) is generated automatically and is:
> PK__TipDOK__1EC48A19
> I select: ALL tasks- Export data in enterprice manager and copy objects
> option and then select this table to be copied to some other database.
> The table is copied successfully and is identical, only the name of index
is
> now different:
> PK__TipDOK__267ABA7A
> Why?
> I would like that also the name is identical.
> Because I have program for comparing the datatbases and it gives me the
> difference between this table just because of the index name.
> Then I have errors when I synchronize databases, because the index can't
> be dropped.
> Any idea?
> lp,
> Simon
>
>|||In Enterprise Manager, right-click on your database, select All Tasks, then
Generate SQL Script.
Select whatever tables you want to copy, then go to the Options tab and make
sure "Select PRIMARY keys, FOREIGN keys, defaults, and check constriants" is
selected.
Use the script this generates to create a new, empty database. Then you can
use the DTS wizard to copy the data in from the old database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"simon" <simon.zupan@.iware.si> wrote in message
news:iQ%Td.9584$F6.1864547@.news.siol.net...
> I have table tipDok with primary key, which is varchar data type.
> The name of the index(primary key) is generated automatically and is:
> PK__TipDOK__1EC48A19
> I select: ALL tasks- Export data in enterprice manager and copy objects
> option and then select this table to be copied to some other database.
> The table is copied successfully and is identical, only the name of index
is
> now different:
> PK__TipDOK__267ABA7A
> Why?
> I would like that also the name is identical.
> Because I have program for comparing the datatbases and it gives me the
> difference between this table just because of the index name.
> Then I have errors when I synchronize databases, because the index can't
> be dropped.
> Any idea?
> lp,
> Simon
>
PK Index in SQL Server
I'm a little confused on the details of how a PK index is implemented in SQL
Server.
It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
_filegroup_" you are specifying the index the PK will use in its entirety.
What I'm accustomed to (under Informix) is to create the table, create the
index the constraint will use and then create the constraint, which will
detect the appropriate index and use it (Informix has much more robust
storage options, which are available in create index, but not add
constraint).
However, under SQL Server when I do this, I seem to get two unique indexes
on the same column(s). Also, SQL Server seems to limit indexes to a single
filegroup negating anything fancy in the first place (this is still true
with DPV's).
So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
index, period. Also, except for the entry in sysconstraints, a constraint
and an index are implemented completely identically in SQL Server.
Am I correct?
Thanks,
JayHi Jay,
In SQL Server you create a primary key, adding a constraint. The way SS
implement such constraint, is creating a unique index, behind the scene, that
could be clustered or nonclustered. If you do not specify what kind of index
to use, SS will use clustered by default. It will be the same with UNIQUE
constraints.
You can not disable a primary key or unique constraint using "alter table
... nocheck constraint_name", but you can disable the index associated to the
costraint. Notice that if the index is a clustered one, then disabling it
will stop you from inserting into the table.
CREATE TABLE dbo.t(
c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
)
GO
ALTER TABLE dbo.t
NOCHECK constraint pk_t
GO
ALTER TABLE dbo.t
NOCHECK constraint uq_c2
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
ALTER INDEX pk_t ON dbo.t DISABLE
go
ALTER INDEX uq_c2 ON dbo.t DISABLE
go
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
INSERT INTO dbo.t VALUES(1, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
SELECT * FROM dbo.[t]
GO
DROP TABLE dbo.[t]
GO
AMB
"Jay" wrote:
> I'm a little confused on the details of how a PK index is implemented in SQL
> Server.
> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> _filegroup_" you are specifying the index the PK will use in its entirety.
> What I'm accustomed to (under Informix) is to create the table, create the
> index the constraint will use and then create the constraint, which will
> detect the appropriate index and use it (Informix has much more robust
> storage options, which are available in create index, but not add
> constraint).
> However, under SQL Server when I do this, I seem to get two unique indexes
> on the same column(s). Also, SQL Server seems to limit indexes to a single
> filegroup negating anything fancy in the first place (this is still true
> with DPV's).
> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
> index, period. Also, except for the entry in sysconstraints, a constraint
> and an index are implemented completely identically in SQL Server.
> Am I correct?
> Thanks,
> Jay
>
>|||Thank Alejandro,
So, if I understand you correctly, with the exceptions of focusing on
clustering and disabling indexes, adding a primary key constraing is the
same as adding an index, except that you get the extra stuff that comes with
a primary key.
Thanks,
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> Hi Jay,
> In SQL Server you create a primary key, adding a constraint. The way SS
> implement such constraint, is creating a unique index, behind the scene,
> that
> could be clustered or nonclustered. If you do not specify what kind of
> index
> to use, SS will use clustered by default. It will be the same with UNIQUE
> constraints.
> You can not disable a primary key or unique constraint using "alter table
> ... nocheck constraint_name", but you can disable the index associated to
> the
> costraint. Notice that if the index is a clustered one, then disabling it
> will stop you from inserting into the table.
>
> CREATE TABLE dbo.t(
> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> )
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint pk_t
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint uq_c2
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> ALTER INDEX pk_t ON dbo.t DISABLE
> go
> ALTER INDEX uq_c2 ON dbo.t DISABLE
> go
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> INSERT INTO dbo.t VALUES(1, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> SELECT * FROM dbo.[t]
> GO
> DROP TABLE dbo.[t]
> GO
>
> AMB
>
> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in
>> SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its
>> entirety.
>> What I'm accustomed to (under Informix) is to create the table, create
>> the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique
>> indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a
>> single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
>> own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>|||> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
Yes. A PK and UQ constraint is "implemented" though a unique index, which is created for you when
you defined (add) your constraint. The index has the same name as the constraint. This is also why
the constraint definition is "littered" with physical attributes like clustered/nonclustered and
filegroup information. It is just a matter of getting used to that this is the way they've done it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:ejzlYVICIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> Hi Jay,
>> In SQL Server you create a primary key, adding a constraint. The way SS
>> implement such constraint, is creating a unique index, behind the scene, that
>> could be clustered or nonclustered. If you do not specify what kind of index
>> to use, SS will use clustered by default. It will be the same with UNIQUE
>> constraints.
>> You can not disable a primary key or unique constraint using "alter table
>> ... nocheck constraint_name", but you can disable the index associated to the
>> costraint. Notice that if the index is a clustered one, then disabling it
>> will stop you from inserting into the table.
>>
>> CREATE TABLE dbo.t(
>> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> )
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint pk_t
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint uq_c2
>> GO
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> ALTER INDEX pk_t ON dbo.t DISABLE
>> go
>> ALTER INDEX uq_c2 ON dbo.t DISABLE
>> go
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> INSERT INTO dbo.t VALUES(1, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> SELECT * FROM dbo.[t]
>> GO
>> DROP TABLE dbo.[t]
>> GO
>>
>> AMB
>>
>> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its entirety.
>> What I'm accustomed to (under Informix) is to create the table, create the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>
>|||Technically, yes.
However, the point of having constraints and indexes is because
logically, they have different purposes.
If you are adding a constraint (such as a Primary Key), you are saying
something about your data, you are determining "business rules". With a
Primary Key you are saying: this is the key that identifies the row, and
it should be unique. Then, it is up to the RDBMS how it enforces this
constraint. If you have a compound primary key, then from this point of
view, the order of the columns does not matter.
The current and all past versions of SQL Server enforce a Primary Key
and Unique Constraint with a unique index. This could be changed in the
future. Maybe one day SQL Server might enforce it using hashing or some
other technique.
If you add an index, you are optimizing your data access. When you add a
compound index, the column order matters a lot, because of the nature of
B-tree indexes. It also matters because a join between two tables with a
compound key in different order is very inefficient.
I consider it a good practice to always use constraints to determine
keys and uniqueness, and to use indexes for additional tuning. This way,
the person optimizing the data access knows which indexes can be removed
without destroying integrity, and the person (re)modelling the schema
does not have to look at indexes.
--
Gert-Jan
Jay wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>|||Jay,
You got very good answers from Tibor and Gert-Jan. It is important, at least
for me, to point what Gert-Jan already mentioned, that primary key is part of
the models earlier than the physical. We do not talk about indexes, during
logical and conceptual models, to enforce a constraint.
AMB
"Jay" wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>
>
>|||Thanks Alejandro,
But I have no issues with the logical constructs as I've been working with
relational databases for just under 20 years, it was just the physical
implementation I needed to grasp.
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2700F07B-B429-4829-BD6A-83DDDB464703@.microsoft.com...
> Jay,
> You got very good answers from Tibor and Gert-Jan. It is important, at
> least
> for me, to point what Gert-Jan already mentioned, that primary key is part
> of
> the models earlier than the physical. We do not talk about indexes, during
> logical and conceptual models, to enforce a constraint.
>
> AMB
> "Jay" wrote:
>> Thank Alejandro,
>> So, if I understand you correctly, with the exceptions of focusing on
>> clustering and disabling indexes, adding a primary key constraing is the
>> same as adding an index, except that you get the extra stuff that comes
>> with
>> a primary key.
>> Thanks,
>> Jay
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message
>> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> > Hi Jay,
>> >
>> > In SQL Server you create a primary key, adding a constraint. The way SS
>> > implement such constraint, is creating a unique index, behind the
>> > scene,
>> > that
>> > could be clustered or nonclustered. If you do not specify what kind of
>> > index
>> > to use, SS will use clustered by default. It will be the same with
>> > UNIQUE
>> > constraints.
>> >
>> > You can not disable a primary key or unique constraint using "alter
>> > table
>> > ... nocheck constraint_name", but you can disable the index associated
>> > to
>> > the
>> > costraint. Notice that if the index is a clustered one, then disabling
>> > it
>> > will stop you from inserting into the table.
>> >
>> >
>> > CREATE TABLE dbo.t(
>> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> > )
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint pk_t
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint uq_c2
>> > GO
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > ALTER INDEX pk_t ON dbo.t DISABLE
>> > go
>> >
>> > ALTER INDEX uq_c2 ON dbo.t DISABLE
>> > go
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(1, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > SELECT * FROM dbo.[t]
>> > GO
>> >
>> > DROP TABLE dbo.[t]
>> > GO
>> >
>> >
>> > AMB
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> I'm a little confused on the details of how a PK index is implemented
>> >> in
>> >> SQL
>> >> Server.
>> >>
>> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_)
>> >> ON
>> >> _filegroup_" you are specifying the index the PK will use in its
>> >> entirety.
>> >>
>> >> What I'm accustomed to (under Informix) is to create the table, create
>> >> the
>> >> index the constraint will use and then create the constraint, which
>> >> will
>> >> detect the appropriate index and use it (Informix has much more robust
>> >> storage options, which are available in create index, but not add
>> >> constraint).
>> >>
>> >> However, under SQL Server when I do this, I seem to get two unique
>> >> indexes
>> >> on the same column(s). Also, SQL Server seems to limit indexes to a
>> >> single
>> >> filegroup negating anything fancy in the first place (this is still
>> >> true
>> >> with DPV's).
>> >>
>> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create
>> >> its
>> >> own
>> >> index, period. Also, except for the entry in sysconstraints, a
>> >> constraint
>> >> and an index are implemented completely identically in SQL Server.
>> >>
>> >> Am I correct?
>> >>
>> >> Thanks,
>> >> Jay
>> >>
>> >>
>> >>
>>
Server.
It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
_filegroup_" you are specifying the index the PK will use in its entirety.
What I'm accustomed to (under Informix) is to create the table, create the
index the constraint will use and then create the constraint, which will
detect the appropriate index and use it (Informix has much more robust
storage options, which are available in create index, but not add
constraint).
However, under SQL Server when I do this, I seem to get two unique indexes
on the same column(s). Also, SQL Server seems to limit indexes to a single
filegroup negating anything fancy in the first place (this is still true
with DPV's).
So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
index, period. Also, except for the entry in sysconstraints, a constraint
and an index are implemented completely identically in SQL Server.
Am I correct?
Thanks,
JayHi Jay,
In SQL Server you create a primary key, adding a constraint. The way SS
implement such constraint, is creating a unique index, behind the scene, that
could be clustered or nonclustered. If you do not specify what kind of index
to use, SS will use clustered by default. It will be the same with UNIQUE
constraints.
You can not disable a primary key or unique constraint using "alter table
... nocheck constraint_name", but you can disable the index associated to the
costraint. Notice that if the index is a clustered one, then disabling it
will stop you from inserting into the table.
CREATE TABLE dbo.t(
c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
)
GO
ALTER TABLE dbo.t
NOCHECK constraint pk_t
GO
ALTER TABLE dbo.t
NOCHECK constraint uq_c2
GO
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
ALTER INDEX pk_t ON dbo.t DISABLE
go
ALTER INDEX uq_c2 ON dbo.t DISABLE
go
SELECT
OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
GO
INSERT INTO dbo.t VALUES(1, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
INSERT INTO dbo.t VALUES(2, 1)
GO
SELECT * FROM dbo.[t]
GO
DROP TABLE dbo.[t]
GO
AMB
"Jay" wrote:
> I'm a little confused on the details of how a PK index is implemented in SQL
> Server.
> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> _filegroup_" you are specifying the index the PK will use in its entirety.
> What I'm accustomed to (under Informix) is to create the table, create the
> index the constraint will use and then create the constraint, which will
> detect the appropriate index and use it (Informix has much more robust
> storage options, which are available in create index, but not add
> constraint).
> However, under SQL Server when I do this, I seem to get two unique indexes
> on the same column(s). Also, SQL Server seems to limit indexes to a single
> filegroup negating anything fancy in the first place (this is still true
> with DPV's).
> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
> index, period. Also, except for the entry in sysconstraints, a constraint
> and an index are implemented completely identically in SQL Server.
> Am I correct?
> Thanks,
> Jay
>
>|||Thank Alejandro,
So, if I understand you correctly, with the exceptions of focusing on
clustering and disabling indexes, adding a primary key constraing is the
same as adding an index, except that you get the extra stuff that comes with
a primary key.
Thanks,
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> Hi Jay,
> In SQL Server you create a primary key, adding a constraint. The way SS
> implement such constraint, is creating a unique index, behind the scene,
> that
> could be clustered or nonclustered. If you do not specify what kind of
> index
> to use, SS will use clustered by default. It will be the same with UNIQUE
> constraints.
> You can not disable a primary key or unique constraint using "alter table
> ... nocheck constraint_name", but you can disable the index associated to
> the
> costraint. Notice that if the index is a clustered one, then disabling it
> will stop you from inserting into the table.
>
> CREATE TABLE dbo.t(
> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> )
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint pk_t
> GO
> ALTER TABLE dbo.t
> NOCHECK constraint uq_c2
> GO
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> ALTER INDEX pk_t ON dbo.t DISABLE
> go
> ALTER INDEX uq_c2 ON dbo.t DISABLE
> go
> SELECT
> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> GO
> INSERT INTO dbo.t VALUES(1, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> INSERT INTO dbo.t VALUES(2, 1)
> GO
> SELECT * FROM dbo.[t]
> GO
> DROP TABLE dbo.[t]
> GO
>
> AMB
>
> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in
>> SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its
>> entirety.
>> What I'm accustomed to (under Informix) is to create the table, create
>> the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique
>> indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a
>> single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
>> own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>|||> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
Yes. A PK and UQ constraint is "implemented" though a unique index, which is created for you when
you defined (add) your constraint. The index has the same name as the constraint. This is also why
the constraint definition is "littered" with physical attributes like clustered/nonclustered and
filegroup information. It is just a matter of getting used to that this is the way they've done it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:ejzlYVICIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on clustering and disabling
> indexes, adding a primary key constraing is the same as adding an index, except that you get the
> extra stuff that comes with a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> Hi Jay,
>> In SQL Server you create a primary key, adding a constraint. The way SS
>> implement such constraint, is creating a unique index, behind the scene, that
>> could be clustered or nonclustered. If you do not specify what kind of index
>> to use, SS will use clustered by default. It will be the same with UNIQUE
>> constraints.
>> You can not disable a primary key or unique constraint using "alter table
>> ... nocheck constraint_name", but you can disable the index associated to the
>> costraint. Notice that if the index is a clustered one, then disabling it
>> will stop you from inserting into the table.
>>
>> CREATE TABLE dbo.t(
>> c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> )
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint pk_t
>> GO
>> ALTER TABLE dbo.t
>> NOCHECK constraint uq_c2
>> GO
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> ALTER INDEX pk_t ON dbo.t DISABLE
>> go
>> ALTER INDEX uq_c2 ON dbo.t DISABLE
>> go
>> SELECT
>> OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> GO
>> INSERT INTO dbo.t VALUES(1, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> INSERT INTO dbo.t VALUES(2, 1)
>> GO
>> SELECT * FROM dbo.[t]
>> GO
>> DROP TABLE dbo.[t]
>> GO
>>
>> AMB
>>
>> "Jay" wrote:
>> I'm a little confused on the details of how a PK index is implemented in SQL
>> Server.
>> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
>> _filegroup_" you are specifying the index the PK will use in its entirety.
>> What I'm accustomed to (under Informix) is to create the table, create the
>> index the constraint will use and then create the constraint, which will
>> detect the appropriate index and use it (Informix has much more robust
>> storage options, which are available in create index, but not add
>> constraint).
>> However, under SQL Server when I do this, I seem to get two unique indexes
>> on the same column(s). Also, SQL Server seems to limit indexes to a single
>> filegroup negating anything fancy in the first place (this is still true
>> with DPV's).
>> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its own
>> index, period. Also, except for the entry in sysconstraints, a constraint
>> and an index are implemented completely identically in SQL Server.
>> Am I correct?
>> Thanks,
>> Jay
>>
>|||Technically, yes.
However, the point of having constraints and indexes is because
logically, they have different purposes.
If you are adding a constraint (such as a Primary Key), you are saying
something about your data, you are determining "business rules". With a
Primary Key you are saying: this is the key that identifies the row, and
it should be unique. Then, it is up to the RDBMS how it enforces this
constraint. If you have a compound primary key, then from this point of
view, the order of the columns does not matter.
The current and all past versions of SQL Server enforce a Primary Key
and Unique Constraint with a unique index. This could be changed in the
future. Maybe one day SQL Server might enforce it using hashing or some
other technique.
If you add an index, you are optimizing your data access. When you add a
compound index, the column order matters a lot, because of the nature of
B-tree indexes. It also matters because a join between two tables with a
compound key in different order is very inefficient.
I consider it a good practice to always use constraints to determine
keys and uniqueness, and to use indexes for additional tuning. This way,
the person optimizing the data access knows which indexes can be removed
without destroying integrity, and the person (re)modelling the schema
does not have to look at indexes.
--
Gert-Jan
Jay wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>|||Jay,
You got very good answers from Tibor and Gert-Jan. It is important, at least
for me, to point what Gert-Jan already mentioned, that primary key is part of
the models earlier than the physical. We do not talk about indexes, during
logical and conceptual models, to enforce a constraint.
AMB
"Jay" wrote:
> Thank Alejandro,
> So, if I understand you correctly, with the exceptions of focusing on
> clustering and disabling indexes, adding a primary key constraing is the
> same as adding an index, except that you get the extra stuff that comes with
> a primary key.
> Thanks,
> Jay
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
> > Hi Jay,
> >
> > In SQL Server you create a primary key, adding a constraint. The way SS
> > implement such constraint, is creating a unique index, behind the scene,
> > that
> > could be clustered or nonclustered. If you do not specify what kind of
> > index
> > to use, SS will use clustered by default. It will be the same with UNIQUE
> > constraints.
> >
> > You can not disable a primary key or unique constraint using "alter table
> > ... nocheck constraint_name", but you can disable the index associated to
> > the
> > costraint. Notice that if the index is a clustered one, then disabling it
> > will stop you from inserting into the table.
> >
> >
> > CREATE TABLE dbo.t(
> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
> > )
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint pk_t
> > GO
> >
> > ALTER TABLE dbo.t
> > NOCHECK constraint uq_c2
> > GO
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > ALTER INDEX pk_t ON dbo.t DISABLE
> > go
> >
> > ALTER INDEX uq_c2 ON dbo.t DISABLE
> > go
> >
> > SELECT
> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
> > GO
> >
> > INSERT INTO dbo.t VALUES(1, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > INSERT INTO dbo.t VALUES(2, 1)
> > GO
> >
> > SELECT * FROM dbo.[t]
> > GO
> >
> > DROP TABLE dbo.[t]
> > GO
> >
> >
> > AMB
> >
> >
> > "Jay" wrote:
> >
> >> I'm a little confused on the details of how a PK index is implemented in
> >> SQL
> >> Server.
> >>
> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_) ON
> >> _filegroup_" you are specifying the index the PK will use in its
> >> entirety.
> >>
> >> What I'm accustomed to (under Informix) is to create the table, create
> >> the
> >> index the constraint will use and then create the constraint, which will
> >> detect the appropriate index and use it (Informix has much more robust
> >> storage options, which are available in create index, but not add
> >> constraint).
> >>
> >> However, under SQL Server when I do this, I seem to get two unique
> >> indexes
> >> on the same column(s). Also, SQL Server seems to limit indexes to a
> >> single
> >> filegroup negating anything fancy in the first place (this is still true
> >> with DPV's).
> >>
> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create its
> >> own
> >> index, period. Also, except for the entry in sysconstraints, a constraint
> >> and an index are implemented completely identically in SQL Server.
> >>
> >> Am I correct?
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>
>
>|||Thanks Alejandro,
But I have no issues with the logical constructs as I've been working with
relational databases for just under 20 years, it was just the physical
implementation I needed to grasp.
Jay
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2700F07B-B429-4829-BD6A-83DDDB464703@.microsoft.com...
> Jay,
> You got very good answers from Tibor and Gert-Jan. It is important, at
> least
> for me, to point what Gert-Jan already mentioned, that primary key is part
> of
> the models earlier than the physical. We do not talk about indexes, during
> logical and conceptual models, to enforce a constraint.
>
> AMB
> "Jay" wrote:
>> Thank Alejandro,
>> So, if I understand you correctly, with the exceptions of focusing on
>> clustering and disabling indexes, adding a primary key constraing is the
>> same as adding an index, except that you get the extra stuff that comes
>> with
>> a primary key.
>> Thanks,
>> Jay
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message
>> news:2B48C256-79BC-45C6-92A4-13D940FD6980@.microsoft.com...
>> > Hi Jay,
>> >
>> > In SQL Server you create a primary key, adding a constraint. The way SS
>> > implement such constraint, is creating a unique index, behind the
>> > scene,
>> > that
>> > could be clustered or nonclustered. If you do not specify what kind of
>> > index
>> > to use, SS will use clustered by default. It will be the same with
>> > UNIQUE
>> > constraints.
>> >
>> > You can not disable a primary key or unique constraint using "alter
>> > table
>> > ... nocheck constraint_name", but you can disable the index associated
>> > to
>> > the
>> > costraint. Notice that if the index is a clustered one, then disabling
>> > it
>> > will stop you from inserting into the table.
>> >
>> >
>> > CREATE TABLE dbo.t(
>> > c1 INT NOT NULL CONSTRAINT pk_t PRIMARY KEY NONCLUSTERED,
>> > c2 INT NOT NULL CONSTRAINT uq_c2 UNIQUE NONCLUSTERED
>> > )
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint pk_t
>> > GO
>> >
>> > ALTER TABLE dbo.t
>> > NOCHECK constraint uq_c2
>> > GO
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > ALTER INDEX pk_t ON dbo.t DISABLE
>> > go
>> >
>> > ALTER INDEX uq_c2 ON dbo.t DISABLE
>> > go
>> >
>> > SELECT
>> > OBJECTPROPERTY(OBJECT_ID('pk_t'), 'CnstIsDisabled '),
>> > OBJECTPROPERTY(OBJECT_ID('uq_c2'), 'CnstIsDisabled ')
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(1, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > INSERT INTO dbo.t VALUES(2, 1)
>> > GO
>> >
>> > SELECT * FROM dbo.[t]
>> > GO
>> >
>> > DROP TABLE dbo.[t]
>> > GO
>> >
>> >
>> > AMB
>> >
>> >
>> > "Jay" wrote:
>> >
>> >> I'm a little confused on the details of how a PK index is implemented
>> >> in
>> >> SQL
>> >> Server.
>> >>
>> >> It looks like when you say "ADD CONSTRAINT _name_ PRIMARY KEY (_col_)
>> >> ON
>> >> _filegroup_" you are specifying the index the PK will use in its
>> >> entirety.
>> >>
>> >> What I'm accustomed to (under Informix) is to create the table, create
>> >> the
>> >> index the constraint will use and then create the constraint, which
>> >> will
>> >> detect the appropriate index and use it (Informix has much more robust
>> >> storage options, which are available in create index, but not add
>> >> constraint).
>> >>
>> >> However, under SQL Server when I do this, I seem to get two unique
>> >> indexes
>> >> on the same column(s). Also, SQL Server seems to limit indexes to a
>> >> single
>> >> filegroup negating anything fancy in the first place (this is still
>> >> true
>> >> with DPV's).
>> >>
>> >> So, I'm guessing that the ADD CONSTRAINT clause in T-SQL will create
>> >> its
>> >> own
>> >> index, period. Also, except for the entry in sysconstraints, a
>> >> constraint
>> >> and an index are implemented completely identically in SQL Server.
>> >>
>> >> Am I correct?
>> >>
>> >> Thanks,
>> >> Jay
>> >>
>> >>
>> >>
>>
Wednesday, March 7, 2012
PK Index Cluster/Non Cluster
I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clusterin
g
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set u
p
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys tha
t
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clusterin
g
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set u
p
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys tha
t
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
PK Index Cluster/Non Cluster
I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
PK Index Cluster/Non Cluster
I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,
Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,
Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
PK And Index
I have a primary key that comprises 2 columns (lets say ReportDate and
Symbol).
Symbol).
I know that if I submit a statement like SELECT * FROM T1 WHERE
ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
Do I need to create another index on symbol alone?Jason (JayCallas@.hotmail.com) writes:
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?
For best performance, yes.
But the query may use the existing index, if the index is non-clustered.
If SQL Server finds that XYZ is not a very common value, it may opt
scan the index to find the rows. This is faster than scanning the entire
table. If the value is common, however, the bookmark lookups will be
more expensive than scanning.
If the existing index is clustered, it can not help to speed up the
retrieval. Ah, that wasn't completely true, either. Because if the
there is a non-clustered index on the table as well, the keys of the
clustered index appears in the non-clustered index, so SQL Server can
scan that index.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erlands response. You could check the Execution Plan when using
Query Analyzer to see how SQL Server is using your indexes.
BZ
"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0312190912.1c1ea341@.posting.google.c om...
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?
Subscribe to:
Posts (Atom)