Need help on indexing
-
Hello...
I'm a beginner in Indexing
I've table as following,
CREATE TABLE [dbo].[tbl_Penyata]([idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[nogaji] [char](6) NOT NULL,
[TkhTransaksi] [date] NOT NULL,
[NoSiri] [varchar](6) NULL,
[kodBank] [char](6) NOT NULL,
[AkaunBank] [varchar](16) NOT NULL,
[singkatan_bank] [nvarchar](10) NULL,
[AktPst] [char](4) NOT NULL,
[edtype] [char](2) NOT NULL,
[edcode] [char](4) NOT NULL,
[eln_payslip] [varchar](30) NULL,
[edAmount] [decimal](10, 2) NOT NULL,
CONSTRAINT [pk_tbl_penyata] PRIMARY KEY CLUSTERED
(
[idx] ASC,
[nogaji] ASC,
[TkhTransaksi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS =
ON, ALLOW_PAGE_LOCKS = ON) ON [ps_TkhTransaksi]([TkhTransaksi])
) ON [ps_TkhTransaksi]([TkhTransaksi])
tbl_Penyata using partitioned tables. This is the info
object_idTbNameindex_nameindex_type_descpartition_schemedata_space_idfunction_namefunction_id
1266103551tbl_Penyatadbo.tbl_Penyata_UQ1NONCLUSTEREDps_TkhTransaksi65601pf_TkhTransaksi65536
1266103551tbl_Penyatadbo.tbl_Penyata_UQ2NONCLUSTEREDps_TkhTransaksi65601pf_TkhTransaksi65536
1266103551tbl_Penyatadbo.tbl_Penyata_UQ3NONCLUSTEREDps_TkhTransaksi65601pf_TkhTransaksi65536
1266103551tbl_Penyatapk_tbl_penyataCLUSTEREDps_TkhTransaksi65601pf_TkhTransaksi65536
object_idTbNameindex_idpartition_numberrowsindex_nameindex_type_descdata_space_idFILEGROUP_NAMEfunction_idPf_Nametype_descboundary_value_on_rightdestination_data_space_idparameter_idvalue
1266103551tbl_Penyata11166296pk_tbl_penyataCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1112012-06-01 00:00:00.000
1266103551tbl_Penyata12337293pk_tbl_penyataCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1212012-12-01 00:00:00.000
1266103551tbl_Penyata13219018pk_tbl_penyataCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1312013-06-01 00:00:00.000
1266103551tbl_Penyata140pk_tbl_penyataCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1412013-12-01 00:00:00.000
1266103551tbl_Penyata150pk_tbl_penyataCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE15NULLNULL
1266103551tbl_Penyata131166296dbo.tbl_Penyata_UQ1NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1112012-06-01 00:00:00.000
1266103551tbl_Penyata132337293dbo.tbl_Penyata_UQ1NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1212012-12-01 00:00:00.000
1266103551tbl_Penyata133219018dbo.tbl_Penyata_UQ1NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1312013-06-01 00:00:00.000
1266103551tbl_Penyata1340dbo.tbl_Penyata_UQ1NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1412013-12-01 00:00:00.000
1266103551tbl_Penyata1350dbo.tbl_Penyata_UQ1NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE15NULLNULL
1266103551tbl_Penyata141166296dbo.tbl_Penyata_UQ2NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1112012-06-01 00:00:00.000
1266103551tbl_Penyata142337293dbo.tbl_Penyata_UQ2NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1212012-12-01 00:00:00.000
1266103551tbl_Penyata143219018dbo.tbl_Penyata_UQ2NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1312013-06-01 00:00:00.000
1266103551tbl_Penyata1440dbo.tbl_Penyata_UQ2NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1412013-12-01 00:00:00.000
1266103551tbl_Penyata1450dbo.tbl_Penyata_UQ2NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE15NULLNULL
1266103551tbl_Penyata151166296dbo.tbl_Penyata_UQ3NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1112012-06-01 00:00:00.000
1266103551tbl_Penyata152337293dbo.tbl_Penyata_UQ3NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1212012-12-01 00:00:00.000
1266103551tbl_Penyata153219018dbo.tbl_Penyata_UQ3NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1312013-06-01 00:00:00.000
1266103551tbl_Penyata1540dbo.tbl_Penyata_UQ3NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE1412013-12-01 00:00:00.000
1266103551tbl_Penyata1550dbo.tbl_Penyata_UQ3NONCLUSTERED65601PRIMARY65536pf_TkhTransaksiRANGE15NULLNULL
I execute SQL as following,
selecteln_payslip,
edamount
from dbo.tbl_Penyata
where nogaji='047911' and edtype='01' and edcode not in ('0157')
and DATEPART(YEAR,tkhtransaksi)=DATEPART(YEAR,'20121201')
and DATEPART(MONTH,tkhtransaksi)=DATEPART(MONTH,'20121201')
order by edcode
My Execution Plan as following,
Please guide me to add Indexes (if any)
-
Please post index definitions (they're not in your script) and execution plan (not the picture of it), as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Also, you can simplify that query as:
selecteln_payslip,
edamount
from dbo.tbl_Penyata
where nogaji='047911' and edtype='01' and edcode not in ('0157')
and tkhtransaksi >= '2012/12/01' and tkhtransaksi < DATEADD(mm,1,'2012/12/01')
order by edcode
That makes the date predicate SARGable and hence able to use an index seek, assuming you have an appropriate 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (1/15/2013)
Please post index definitions (they're not in your script) and execution plan (not the picture of it), as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Also, you can simplify that query as:
selecteln_payslip,
edamount
from dbo.tbl_Penyata
where nogaji='047911' and edtype='01' and edcode not in ('0157')
and tkhtransaksi >= '2012/12/01' and tkhtransaksi < DATEADD(mm,1,'2012/12/01')
order by edcode
That makes the date predicate SARGable and hence able to use an index seek, assuming you have an appropriate index.
Hi GilaMonster,
I attached my Execution Plan
-
Current index definitions please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This is my Index so far
CREATE NONCLUSTERED INDEX [dbo.tbl_Penyata_IX1] ON [dbo].[tbl_Penyata]
(
[AktPst] ASC,
[edcode] ASC
)
INCLUDE ( [nogaji],
[TkhTransaksi],
[edAmount]) WITH (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)
CREATE NONCLUSTERED INDEX [dbo.tbl_Penyata_IX2] ON [dbo].[tbl_Penyata]
(
[AktPst] ASC,
[edtype] ASC,
[edcode] ASC
)
INCLUDE ( [nogaji],
[TkhTransaksi],
[eln_payslip],
[edAmount]) WITH (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)
CREATE NONCLUSTERED INDEX [dbo.tbl_Penyata_IX3] ON [dbo].[tbl_Penyata]
(
[AktPst] ASC,
[edtype] ASC,
[edcode] ASC
)
INCLUDE ( [nogaji],
[TkhTransaksi],
[eln_payslip],
[edAmount]) WITH (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)
-
You've got redundant indexes there. The 2nd and 3rd are identical, you should drop one of them.
For the revised query that I gave you, you probably want one of these, which I can't tell, you need to test, see which is more optimal
Index on (nogaji, edtype, tkhtransaksi ) Include (eln_payslip, edamount, edcode )
or
Index on (nogaji, edtype, edcode ) Include (eln_payslip, edamount, tkhtransaksi )
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
tq mam
-
Better yet, forget the dopey "cluster first by identity" and cluster the table the best way: that is THE most important thing for performance.
Based on what you've posted, it seems to be:
CONSTRAINT [pk_tbl_penyata] PRIMARY KEY CLUSTERED
([nogaji] ASC, [TkhTransaksi] ASC, [idx] ASC /*included to make the PK unique*/)
--<or>
--([TkhTransaksi] ASC, [nogaji] ASC, [idx] ASC /*included to make the PK unique*/)
and assuming that "nogaji" and "TkhTransaksi" are naturally ascending[/u].
If not, you have a choice to make:
-- if only TkhTransaksi is naturally ascending, but you specify it on (almost) all SELECTs, just make it the first column in the clustered index, as in the 2nd definition above;
-- if only nogaji is ascending, put it first;
-- if both naturally ascend, put whichever column you specify more often in SELECTs first in the key.
Then you won't need all those extra indexes, which force every DELETE, INSERT and UPDATE to occur mutliple times in multiple indexes, just to provide reasonable access time for (almost) every basic SELECT!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
First and most important thing to do is to choose a good clustering key.
Currently your CL key columns are also a primary key columns: idx, nogaji, TkhTransaksi
Your table is partitioned by date column: TkhTransaksi
Your NC (non-clustered) indexes are global, not partitioned (or you scripted them wrong?).
It would open some cool features for you if all indexes would be aligned, that is partitioned equally as CL index
instead of having non-partitioned, global indexes. One of the benefits would be partition switching and easier management.
Three-column CL key is wider than needed to be unique and ever-increasing.
Consider changing your CL index key to TkhTransaksi, idx.
Or, if TkhTransaksi is not ever-increasing and queries do not filter very often by TkhTransaksi column: idx, TkhTransaksi
I think better option would be the first one, but test how fast fragmentation grows and a define fill factor bit lower than 100 (e.g. 90-95%).
Depending on combined density of columns, those 3 NC indexes probably could be consolidated into just one index
with key columns: AktPst, edtype, edcode
and included columns: nogaji, TkhTransaksi, eln_payslip, edAmount
Could you post selectivity (1/number of distinct values) for columns: nogaji, edtype, and edcode?
Also combined density of (AktPst, edtype), (AktPst, edtype, edcode) and (AktPst, edcode) ?
If the most selective (the one with the most distinct values) is nogaji, then index with leading column nogaji as Gail suggested, would be good.
Also Gail's suggested change in your query not to use functions (DATEPART, etc) around key index columns is really very important for performance,
because if you do, index cannot be and will not be used at all in that query!
_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply