March 5, 2009 at 6:26 am
I have a primary users lookup table. I have userID and Domain used as composit key. I have following two different ways got from two different DBAs.
Script - 1
ALTER TABLE [dbo].[tbl_cv_user] ADD CONSTRAINT [PK_tbl_cv_user] PRIMARY KEY CLUSTERED
(
[CV_User_Name] ASC,
[Domain_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 50) ON [PRIMARY]
Script -2
ALTER TABLE dbo.tbl_cv_user ADD CONSTRAINT
PK_tbl_cv_user PRIMARY KEY CLUSTERED
(
CV_User_Name,
Domain_Name
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
both are doing same thing overall, but still something not clear to me why they both have difference in script. Could you plz let me know which one to follow and which one will be best in terms of performance? we have almost 500000 records into it.
Shamshad Ali
March 5, 2009 at 6:49 am
Edit: Misread the question. Nevermind......
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
March 5, 2009 at 7:40 am
I was just asking about the OPTIONS, why they are different and what options are better - I mean they should be added or NOT? does they make sence and how they help in terms of performance, good practice etc ...
Shamshad Ali.
March 5, 2009 at 7:41 am
All of the changes are already default or pure textual, except for the fillfactor. And that is only used on creation of the constraint, the fillfactor won't be evaluated dynamically. Only on the fillfactor you need to decide what's the best here, depending if this table is update-intensive or not.
In general, the first statement is slightly better if many updates are made in this table. As a result, you need more storage space for this option.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
March 5, 2009 at 7:59 am
50% fill factor is low. It means that half of each page is empty which means your table will take twice the space it needs and queries will do twice the IOs that would otherwise be necessary.
Fill factor that low is very seldom necessary. Can you ask the DBA who suggested that why he goes for such a low fill factor?
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
March 6, 2009 at 2:56 am
Yes we are doing daily updates on this table, if user changes his location, Region, or any other personal information then we take that data changes and update that user profile in this table. This update would be based on change only, not whole table.
I am now confused about both of your replies. one is saying script 1 is fine, other is saying fillfactor 50 is low. what would you suggest now?
Shamshad Ali.
March 6, 2009 at 3:04 am
Shamshad Ali (3/6/2009)
what would you suggest now?
That you ask the DBA who recommended that why he recommends such a low fill factor.
The main reason for something like that would be a table with a lot of inserts at random locations in the table or updates that increase the size of the row.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply