April 20, 2016 at 2:27 am
Hi Oldhands
I partitioned one of my tables .
Into one schema with 9 filegroup.
And finaly Drop Clustered index And Create Clustered on Partititon key .
then drop and Create All Non-Clustered Indexes with this option : ON [Partitioning_SchemaLog]([RegDatetime])
But when I run query which needs one of the non-clustered indexes, Sql server suggests in execution-plan (with green color) that this index is missed.
But I have exactly that index.
what is happening ?
thank you
April 20, 2016 at 3:13 am
Without seeing the current index definitions, the query and the execution plan, no way to say for sure.
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
April 20, 2016 at 3:41 am
excuse me !!
I review execution plan percisly .
It used index (seek , ...)
But show the suggest that index again .
CREATE NONCLUSTERED INDEX [IDX_Nonc_TaxCode] ON [dbo].[Table1]
(
[TaxCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [Partitioning_SchemaLog]([RegDatetime])
GO
---------------------------------------------------------------------------------------
Even I Created another index on this field without [Partitioning_SchemaLog]([RegDatetime])
CREATE NONCLUSTERED INDEX [TaxCode_Index] ON [dbo].[Table1]
(
[TaxCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
---------------------------------------------------------------------------------------
this is execution plan suggestion :
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Table1] ([TaxCode])
GO
April 20, 2016 at 3:50 am
If it's recommending an index that you have exactly, ignore it.
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
April 20, 2016 at 4:59 am
Another question (If possible) :
Befor Partitioning I saved some result for some query on this table. (Logical reads and ...)
After partitioning
- logical read has incredibly increased . from 3 to about 9000.
- I added partititon Key to my query : Logical read reduced to about 1600.
- Finall I used $partition.PartitionFunction(regdatetime)=.... (exact partition number) : It reduced to 3 again.
Then partitioning does not have performance result nesesserily.
Yes , Is it true ?
April 20, 2016 at 5:16 am
Partitioning is not a performance tuning feature, it's for data management and maintenance.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply