MS-SQL 2005/2008 addon indexes

 
Post new topic   Reply to topic    newsmanpro.com Forum Index : Scripts and User Contributions
View previous topic :: View next topic  
Author Message
administrator
Developer
Developer


Joined: 24 Jul 2004
Posts: 4824
Location: King William, VA

PostPosted: Sun May 31, 2009 3:08 pm    Post subject: MS-SQL 2005/2008 addon indexes Reply with quote

We have a user who spent some time tuning his database and added the following indexes to speed up loading of messages for browsing. We may add them eventually into NMP, but thought it best to see how these affect other SQL Server users.

Code:
USE [Newsman]
GO

/****** Object:  Index [_dta_index_wgheaders_6_1653580929__K14_K5_1]    Script Date: 05/28/2009 11:49:19 ******/
CREATE NONCLUSTERED INDEX [_dta_index_wgheaders_6_1653580929__K14_K5_1] ON [dbo].[wgheaders]
(
   [AvailParts] ASC,
   [WorkgroupID] ASC
)
INCLUDE ( [MessageRef]) 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) ON [PRIMARY]
GO

/****** Object:  Index [_dta_index_wgheaders_6_1653580929__K16_K5_K17_K15_K22_K21_1_8]    Script Date: 05/28/2009 11:49:19 ******/
CREATE NONCLUSTERED INDEX [_dta_index_wgheaders_6_1653580929__K16_K5_K17_K15_K22_K21_1_8] ON [dbo].[wgheaders]
(
   [IsAvailable] ASC,
   [WorkgroupID] ASC,
   [Retrieved] ASC,
   [Complete] ASC,
   [IsDeleted] ASC,
   [IsQueued] ASC
)
INCLUDE ( [MessageRef],
[Subject]) 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) ON [PRIMARY]
GO

/****** Object:  Index [_dta_index_wgheaders_6_1653580929__K4_K6_K24_K22]    Script Date: 05/28/2009 11:49:19 ******/
CREATE NONCLUSTERED INDEX [_dta_index_wgheaders_6_1653580929__K4_K6_K24_K22] ON [dbo].[wgheaders]
(
   [IsRead] ASC,
   [NewsgroupID] ASC,
   [FolderID] ASC,
   [IsDeleted] ASC
)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) ON [PRIMARY]
GO

/****** Object:  Index [_dta_index_wgheaders_6_1653580929__K5_K15_K1_8]    Script Date: 05/28/2009 11:49:19 ******/
CREATE NONCLUSTERED INDEX [_dta_index_wgheaders_6_1653580929__K5_K15_K1_8] ON [dbo].[wgheaders]
(
   [WorkgroupID] ASC,
   [Complete] ASC,
   [MessageRef] ASC
)
INCLUDE ( [Subject]) 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) ON [PRIMARY]
GO

/****** Object:  Index [_dta_index_wgheaders_6_1653580929__K6_K1]    Script Date: 05/28/2009 11:49:19 ******/
CREATE NONCLUSTERED INDEX [_dta_index_wgheaders_6_1653580929__K6_K1] ON [dbo].[wgheaders]
(
   [NewsgroupID] ASC,
   [MessageRef] ASC
)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) ON [PRIMARY]
GO


These will not work on SQL 2000...

Regards
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    newsmanpro.com Forum Index : Scripts and User Contributions All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Copyright 2003-2006, Daniel Cumpian
NMP Default By ::Dementeddogz.com::