May 23, 2013 at 12:59 pm
I have a indexed view and ı m trying to create UNIQUE CLUSTERED index with maxdop=64 setting but sql server engine does not use
this option ? Is it any problem or tip?
CREATE UNIQUE CLUSTERED INDEX XXXX ON [dbo].XXXX_1
(
[Column1] ASC,
[Column2] ASC,
[Column3] ASC,
[Column4] ASC,
[Column5] ASC
)WITH (MAXDOP=64,PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
May 23, 2013 at 1:02 pm
What exactly do you mean that it doesn't use that option? What are you seeing?
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
May 23, 2013 at 5:06 pm
I CAN NOT SEE ANY ACTIVITY IN ALL CPU BUT WHEN
I TRY IT TABLE INDEX 64 CPU ARE START TO RUNNING
IS THERE ANY DIFFERENCE
May 23, 2013 at 5:08 pm
I want to ask is there any difference maxdop setting table index
create and indexed view index create
May 23, 2013 at 11:48 pm
Shouldn't be a difference
Bear in mind though what MAXDOP is. It's telling SQL the maximum number of processors that it is allowed to use for the operation. So MAXDOP 64 doesn't tell SQL to use 64 processors, it tells SQL to use no more than 64. SQL will choose how many of those 64 that it's allowed to use to run the query over, so it will use anything between 1 and 64.
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
May 23, 2013 at 11:56 pm
But when i create a table index it is use what is changing in the indexed view index create operation
May 24, 2013 at 12:08 am
Table's bigger?
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
May 24, 2013 at 12:11 am
Yes it table takes 15 minutes to create index
and indexed view takes 2 hour 50 minutes to create
May 24, 2013 at 12:28 am
Not what I asked.
Is the table bigger than the view?
There's more going on when you index a view than indexing a table, the query that defines the view has to be run to get the data, then the index has to be created, so if the view's inefficient then the creation of the index will take a long time.
Indexing the table however just requires reading the table and then creating the index
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply