January 19, 2010 at 4:07 am
The query optimizer for SQL Server 2005 is suggesting that we include a primary key as part of a nonclustered index. Is it OK to include that to increase SQL performance?
In many projects , we also follow the same architecture.
we create Pk with Non clustered on tables on one filegroup and create clustered index on other filegroup.
but whats the logic behind it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 19, 2010 at 4:53 am
Bhuvnesh (1/19/2010)
The query optimizer for SQL Server 2005 is suggesting that we include a primary key as part of a nonclustered index. Is it OK to include that to increase SQL performance?
Sure, why would it not be?
That said, test all recommendations carefully before implementing.
It may be worth reading through this series on indexes - http://www.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2010 at 5:03 am
i read that (you had asked me in my prior post)
but how it will help
when we have table (with Pk + non clustered ) on one filegroup
and clustered index on other filegroup?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 19, 2010 at 5:14 am
I don't understand what you're asking. Adding another column to an index to make it wider/covering has absolutely nothing to do with where in the files the indexes are stored. The latter is a storage consideration only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2010 at 5:35 am
GO
CREATE TABLE dbo.PENDING_ANSWER_FILE
(
acct_id dbo.ut_synthetic_key NOT NULL,
qstn_stub dbo.ut_stub NOT NULL,
entity_stub dbo.ut_stub NOT NULL,
evt_stub dbo.ut_stub NOT NULL,
invitee_stub dbo.ut_stub NOT NULL,
entity_type_id smallint NOT NULL,
answer_file_stub dbo.ut_stub NOT NULL,
physical_file_name dbo.ut_medium_description NOT NULL,
friendly_file_name dbo.ut_medium_long_description NOT NULL,
file_image dbo.ut_medium_long_description NOT NULL,
file_size int NOT NULL,
file_image_type dbo.ut_long_description NOT NULL
) ON PRIMARYDATA
GO
ALTER TABLE dbo.PENDING_ANSWER_FILE ADD CONSTRAINT
PK_PENDING_ANSWER_FILE_acct_id_qstn_stub_entity_stub PRIMARY KEY NONCLUSTERED
(
acct_id,
qstn_stub,
entity_stub
) WITH( PAD_INDEX = ON, FILLFACTOR = 75, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARYINDEX
GO
CREATE CLUSTERED INDEX cix_PENDING_ANSWER_FILE_acct_id_invitee_stub ON
dbo.PENDING_ANSWER_FILE
(
acct_id,
invitee_stub
)
WITH ( PAD_INDEX = ON, FILLFACTOR = 75, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARYDATA]
GO
------------------------
if you see above example , Primary key is made with non clustered key forcibly on "secondaryindex " filegroup
WHILE
Clustered index on other filegroup "secondarydata" filegroup
whats its advantage putting table and PK on different filegroup.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 19, 2010 at 5:41 am
Bhuvnesh (1/19/2010)
if you see above example , Primary key is made with non clustered key forcibly on "secondaryindex " filegroupWHILE
Clustered index on other filegroup "secondarydata" filegroup
Yes? I know what you meant about the two on different filegroups. I just don't see what the location of the indexes on disk has to do with the widening of the nonclustered index.
whats its advantage putting table and PK on different filegroup.
Spreading the IO load out over different disks. That's assuming those two filegroups are on different physical disks. If they're not, then there's no advantage whatsoever.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2010 at 1:25 pm
Bhuvnesh: Please stop posting your code without the [ code ] formatting tags, like this:
Bhuvnesh (1/19/2010)
GOCREATE TABLE dbo.PENDING_ANSWER_FILE
(
acct_id dbo.ut_synthetic_key NOT NULL,
qstn_stub dbo.ut_stub NOT NULL,
entity_stub dbo.ut_stub NOT NULL,
evt_stub dbo.ut_stub NOT NULL,
invitee_stub dbo.ut_stub NOT NULL,
entity_type_id smallint NOT NULL,
answer_file_stub dbo.ut_stub NOT NULL,
physical_file_name dbo.ut_medium_description NOT NULL,
friendly_file_name dbo.ut_medium_long_description NOT NULL,
file_image dbo.ut_medium_long_description NOT NULL,
file_size int NOT NULL,
file_image_type dbo.ut_long_description NOT NULL
) ON PRIMARYDATA
GO
ALTER TABLE dbo.PENDING_ANSWER_FILE ADD CONSTRAINT
PK_PENDING_ANSWER_FILE_acct_id_qstn_stub_entity_stub PRIMARY KEY NONCLUSTERED
(
acct_id,
qstn_stub,
entity_stub
) WITH( PAD_INDEX = ON, FILLFACTOR = 75, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARYINDEX
GO
CREATE CLUSTERED INDEX cix_PENDING_ANSWER_FILE_acct_id_invitee_stub ON
dbo.PENDING_ANSWER_FILE
(
acct_id,
invitee_stub
)
WITH ( PAD_INDEX = ON, FILLFACTOR = 75, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARYDATA]
GO
...
Posting code like this makes it come out unformatted and much harder for us to read, and frankly, while most of us will overlook it the first few times, it would help us a lot if you would make this small extra effort to make our lives easier. Like so:
GO
CREATE TABLE dbo.PENDING_ANSWER_FILE
(
acct_id dbo.ut_synthetic_key NOT NULL,
qstn_stub dbo.ut_stub NOT NULL,
entity_stub dbo.ut_stub NOT NULL,
evt_stub dbo.ut_stub NOT NULL,
invitee_stub dbo.ut_stub NOT NULL,
entity_type_id smallint NOT NULL,
answer_file_stub dbo.ut_stub NOT NULL,
physical_file_name dbo.ut_medium_description NOT NULL,
friendly_file_name dbo.ut_medium_long_description NOT NULL,
file_image dbo.ut_medium_long_description NOT NULL,
file_size int NOT NULL,
file_image_type dbo.ut_long_description NOT NULL
) ON PRIMARYDATA
GO
ALTER TABLE dbo.PENDING_ANSWER_FILE ADD CONSTRAINT
PK_PENDING_ANSWER_FILE_acct_id_qstn_stub_entity_stub PRIMARY KEY NONCLUSTERED
(
acct_id,
qstn_stub,
entity_stub
) WITH( PAD_INDEX = ON, FILLFACTOR = 75, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARYINDEX
GO
CREATE CLUSTERED INDEX cix_PENDING_ANSWER_FILE_acct_id_invitee_stub ON
dbo.PENDING_ANSWER_FILE
(
acct_id,
invitee_stub
)
WITH ( PAD_INDEX = ON, FILLFACTOR = 75, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARYDATA]
GO
See, now doesn't that look better?
I know that I am much more likely to respond to questions if I do not have to cut and paste the code into SSMS just to see what it is. If you do not understand how the [ code ] tags are used, then just click the "Quote" button on my post (this one) and you can see exactly how I did it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply