asked on August 12, 2015

Can anyone give me an opinion about adding in the following indexes to our SQL database? I'm trying to improve performance where I can but want to make sure this would not cause any issues. I've already put some of the larger tables in their own file space like propval, searchresult, doc but am still having some performance issues here and there.

--doc indexes

CREATE INDEX [ix_doc_tocid_pagenum_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid], [pagenum])  INCLUDE ([img_size], [txt_size], [loc_size]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_pagenum_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid], [pagenum])  INCLUDE ([storeid], [page_id], [page_flags], [img_size], [txt_size], [loc_size], [lft_size], [img_etag], [txt_etag], [loc_etag], [lft_etag], [img_width], [img_height], [img_xdpi], [img_ydpi], [img_bpp], [img_cksum], [native_language]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_pagenum_txt_etag] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid], [pagenum], [txt_etag])  WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_pagenum_img_etag] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid], [pagenum], [img_etag])  WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_pagenum_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid], [pagenum])  INCLUDE ([img_size], [txt_size], [loc_size], [lft_size]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid])  INCLUDE ([img_size], [txt_size], [loc_size], [lft_size]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_storeid_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid], [storeid])  INCLUDE ([page_id], [page_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_page_shared_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid], [page_shared])  INCLUDE ([storeid]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_pagenum_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid], [pagenum])  INCLUDE ([txt_etag]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_tocid_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([tocid])  INCLUDE ([storeid]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_doc_storeid_includes] ON [LASERFICHEREPOSITORY].[dbo].[doc] ([storeid])  INCLUDE ([tocid], [page_id]) WITH (FILLFACTOR=100);

 

 

--toc indexes

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [created], [toc_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [creator], [toc_owner], [toc_flags], [toc_sdctl]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_pset_id_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([pset_id])  INCLUDE ([tocid], [parentid], [linkto], [toc_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [toc_owner], [toc_flags], [toc_sdctl], [toc_modifier]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_name_etype_tocid] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([name], [etype], [tocid])  WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_vol_id_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([vol_id])  INCLUDE ([altedoc_storeid]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [modified], [toc_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [created], [toc_owner], [toc_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_pset_id_tocid_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([pset_id], [tocid])  INCLUDE ([parentid], [linkto], [toc_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_creator_created_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([creator], [created])  INCLUDE ([tocid], [parentid], [linkto], [toc_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [toc_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [toc_owner], [toc_flags], [toc_sdctl]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [created], [toc_owner], [toc_flags], [toc_sdctl]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_name_tocid_etype] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([name], [tocid], [etype])  WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([etype])  INCLUDE ([tocid], [parentid]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [toc_owner], [toc_flags]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_name_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([name])  INCLUDE ([tocid]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_toc_parentid_etype_includes] ON [LASERFICHEREPOSITORY].[dbo].[toc] ([parentid], [etype])  INCLUDE ([tocid], [name], [linkto], [modified], [toc_owner], [toc_flags], [toc_sdctl]) WITH (FILLFACTOR=100);

 

 

-- ann & propval indexes

CREATE INDEX [ix_ann_ann_type_includes] ON [LASERFICHEREPOSITORY].[dbo].[ann] ([ann_type])  INCLUDE ([page_id], [attach_size]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_ann_ann_type_includes] ON [LASERFICHEREPOSITORY].[dbo].[ann] ([ann_type])  INCLUDE ([page_id]) WITH (FILLFACTOR=100);

CREATE INDEX [ix_propval_prop_id_includes] ON [LASERFICHEREPOSITORY].[dbo].[propval] ([prop_id])  INCLUDE ([tocid], [short_str_val]) WITH (FILLFACTOR=100);

0 0