7 deleted 3 characters in body
source | link
Select files.ID, 
    files.FileName, 
    files.SZ, 
    files.FT, 
    files.OID, 
    files.Type,
    files.CTP,
    files.Comment
From files, 
Where files.OID IN (Select ID From dbo.anotherTable)
Order By files.OID      
Select files.ID, 
    files.FileName, 
    files.SZ, 
    files.FT, 
    files.OID, 
    files.Type,
    files.CTP,
    files.Comment
From files, 
Where files.OID IN (Select ID From dbo.anotherTable)
Order By files.OID      
Select files.ID, 
    files.FileName, 
    files.SZ, 
    files.FT, 
    files.OID, 
    files.Type,
    files.CTP,
    files.Comment
From files
Where files.OID IN (Select ID From dbo.anotherTable)
Order By files.OID      
6 Incorporated comments
source | link

Bytes per cluster (returned by fsutil fsinfo ntfsinfo) is 4096.

Results of Waits


WaitType ASYNC_NETWORK_IO Wait_S 44.69 Res_S 38.68 signal_S 6.01 WCnt 92961 Perc 48.97%                  
WaitType PREEMPTIVE_OS_WAITFORSINGLEOBJECT Wait_S 43.59 Res_S 43.59 Signal_s 0.00 WCnt 92527 Perc 47.77%

Bytes per cluster (returned by fsutil fsinfo ntfsinfo) is 4096.

Results of Waits


WaitType ASYNC_NETWORK_IO Wait_S 44.69 Res_S 38.68 signal_S 6.01 WCnt 92961 Perc 48.97%                  
WaitType PREEMPTIVE_OS_WAITFORSINGLEOBJECT Wait_S 43.59 Res_S 43.59 Signal_s 0.00 WCnt 92527 Perc 47.77%
5 Slight modification to pretify code
source | link
CREATE TABLE [dbo].[files]  
(
    [ID]             UNIQUEIDENTIFIER CONSTRAINT [a] DEFAULT (newidNEWID()) NOT NULL,
  [OOUID]  [OOUID]      UNIQUEIDENTIFIER NOT NULL,
    [UserID]         UNIQUEIDENTIFIER NOT NULL,
  [OID]  [OID]        UNIQUEIDENTIFIER NOT NULL,
    [CTY]     INT             INT NOT NULL,
  [CTP]  [CTP]    VARCHAR (100)   VARCHAR(100) NOT NULL,
    [FileName]       NVARCHAR (200)   NOT NULL,
    [Comment]          VARCHAR (200)    NOT NULL,
    [SZ]           INT              NOT NULL,
    [WD]          INT             INT NULL,
    [HE]         INT             INT NULL,
    [Type]           INT              NOT NULL,    
    [BIN]        IMAGE           IMAGE NULL,
    [DEL]        TINYINT     TINYINT NOT NULL,
    [ADAT]     DATETIME      DATETIME NOT NULL,
  [CRB]  [CRB]       VARCHAR VARCHAR(50)     NULL,
    [UPDAT]     DATETIME        DATETIME NOT NULL,
  [HDD]  [HDD]       VARCHAR VARCHAR(50)     NULL,
    [ZZTIX]   INT             INT NOT NULL,
  [TAGID]  [TAGID]      UNIQUEIDENTIFIER NULL,
    [FT]       DATETIME        DATETIME NULL,
    [UIOFG]    TINYINT         TINYINT NULL,
    [SRC] INT             INT NOT NULL,
    [RV]     RV      RV NOT NULL,
  [TID]  [TID]      VARCHAR  VARCHAR(100)    NULL,
    CONSTRAINT [PK1] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (FILLFACTOR = 80)
);  
GO  
CREATE CLUSTERED INDEX [IX_files_OID]
    ON [dbo].[files]([OID] ASC)  
WITH (FILLFACTOR = 80);

GO
CREATE NONCLUSTERED INDEX [IX_files_CTY]
    ON [dbo].[files]([OID] ASC, [CTY] ASC, [Type] ASC, [DEL] ASC)  
WITH (FILLFACTOR = 90);

GO
CREATE NONCLUSTERED INDEX [IX_files_RV] ON [dbo].[files]
(
    [RV] ASC,       [CTP] ASC,      [TID] ASC   )
INCLUDE ([ID])  
WITH (FILLFACTOR = 90) ON [PRIMARY];    

GO
CREATE NONCLUSTERED INDEX [IX_files_SZ]
    ON [dbo].[files]([OID] ASC, [CTY] ASC, [DEL] ASC, [SZ] ASC)  
WITH (FILLFACTOR = 90);    

GO
CREATE NONCLUSTERED INDEX [IX_files_TID]
    ON [dbo].[files]([TID] ASC);    

GO
CREATE NONCLUSTERED INDEX [IX_files_CTP_RV]
    ON [dbo].[files]([CTP] ASC, [RV] ASC)
    INCLUDE([ID], [DEL]);

GO
CREATE NONCLUSTERED INDEX [IX_files_ID]
    ON [dbo].[files]([ID] ASC)
    INCLUDE([OID], [SZ]);
CREATE TABLE [dbo].[files] (
    [ID]             UNIQUEIDENTIFIER CONSTRAINT [a] DEFAULT (newid()) NOT NULL,
    [OOUID] UNIQUEIDENTIFIER NOT NULL,
    [UserID]         UNIQUEIDENTIFIER NOT NULL,
    [OID]       UNIQUEIDENTIFIER NOT NULL,
    [CTY]     INT              NOT NULL,
    [CTP]    VARCHAR (100)    NOT NULL,
    [FileName]       NVARCHAR (200)   NOT NULL,
    [Comment]          VARCHAR (200)    NOT NULL,
    [SZ]           INT              NOT NULL,
    [WD]          INT              NULL,
    [HE]         INT              NULL,
    [Type]           INT              NOT NULL,    
    [BIN]        IMAGE            NULL,
    [DEL]        TINYINT      NOT NULL,
    [ADAT]     DATETIME       NOT NULL,
    [CRB]       VARCHAR (50)     NULL,
    [UPDAT]     DATETIME         NOT NULL,
    [HDD]       VARCHAR (50)     NULL,
    [ZZTIX]   INT              NOT NULL,
    [TAGID]    UNIQUEIDENTIFIER NULL,
    [FT]       DATETIME         NULL,
    [UIOFG]    TINYINT          NULL,
    [SRC] INT              NOT NULL,
    [RV]     RV       NOT NULL,
    [TID]      VARCHAR (100)    NULL,
    CONSTRAINT [PK1] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (FILLFACTOR = 80)
);
GO
CREATE CLUSTERED INDEX [IX_files_OID]
    ON [dbo].[files]([OID] ASC) WITH (FILLFACTOR = 80);

GO
CREATE NONCLUSTERED INDEX [IX_files_CTY]
    ON [dbo].[files]([OID] ASC, [CTY] ASC, [Type] ASC, [DEL] ASC) WITH (FILLFACTOR = 90);

GO
CREATE NONCLUSTERED INDEX [IX_files_RV] ON [dbo].[files]
(
    [RV] ASC,       [CTP] ASC,      [TID] ASC   )
INCLUDE ([ID]) WITH (FILLFACTOR = 90) ON [PRIMARY];    

GO
CREATE NONCLUSTERED INDEX [IX_files_SZ]
    ON [dbo].[files]([OID] ASC, [CTY] ASC, [DEL] ASC, [SZ] ASC) WITH (FILLFACTOR = 90);    

GO
CREATE NONCLUSTERED INDEX [IX_files_TID]
    ON [dbo].[files]([TID] ASC);    

GO
CREATE NONCLUSTERED INDEX [IX_files_CTP_RV]
    ON [dbo].[files]([CTP] ASC, [RV] ASC)
    INCLUDE([ID], [DEL]);

GO
CREATE NONCLUSTERED INDEX [IX_files_ID]
    ON [dbo].[files]([ID] ASC)
    INCLUDE([OID], [SZ]);
CREATE TABLE [dbo].[files] 
(
  [ID]           UNIQUEIDENTIFIER CONSTRAINT [a] DEFAULT(NEWID()) NOT NULL,
  [OOUID]        UNIQUEIDENTIFIER NOT NULL,
  [UserID]       UNIQUEIDENTIFIER NOT NULL,
  [OID]          UNIQUEIDENTIFIER NOT NULL,
  [CTY]          INT NOT NULL,
  [CTP]          VARCHAR(100) NOT NULL,
  [FileName]     NVARCHAR(200) NOT NULL,
  [Comment]      VARCHAR(200) NOT NULL,
  [SZ]           INT NOT NULL,
  [WD]           INT NULL,
  [HE]           INT NULL,
  [Type]         INT NOT NULL,
  [BIN]          IMAGE NULL,
  [DEL]          TINYINT NOT NULL,
  [ADAT]         DATETIME NOT NULL,
  [CRB]          VARCHAR(50) NULL,
  [UPDAT]        DATETIME NOT NULL,
  [HDD]          VARCHAR(50) NULL,
  [ZZTIX]        INT NOT NULL,
  [TAGID]        UNIQUEIDENTIFIER NULL,
  [FT]           DATETIME NULL,
  [UIOFG]        TINYINT NULL,
  [SRC]          INT NOT NULL,
  [RV]           RV NOT NULL,
  [TID]          VARCHAR(100) NULL,
  CONSTRAINT [PK1] PRIMARY KEY NONCLUSTERED([ID] ASC) WITH (FILLFACTOR = 80)
);  
GO  
CREATE CLUSTERED INDEX [IX_files_OID]
ON [dbo].[files]([OID] ASC)  
WITH (FILLFACTOR = 80);

GO
CREATE NONCLUSTERED INDEX [IX_files_CTY]
ON [dbo].[files]([OID] ASC, [CTY] ASC, [Type] ASC, [DEL] ASC)  
WITH (FILLFACTOR = 90);

GO
CREATE NONCLUSTERED INDEX [IX_files_RV] ON [dbo].[files]
([RV] ASC, [CTP] ASC, [TID] ASC)
INCLUDE([ID])  
WITH (FILLFACTOR = 90) ON [PRIMARY];    

GO
CREATE NONCLUSTERED INDEX [IX_files_SZ]
ON [dbo].[files]([OID] ASC, [CTY] ASC, [DEL] ASC, [SZ] ASC)  
WITH (FILLFACTOR = 90);    

GO
CREATE NONCLUSTERED INDEX [IX_files_TID]
ON [dbo].[files]([TID] ASC);    

GO
CREATE NONCLUSTERED INDEX [IX_files_CTP_RV]
ON [dbo].[files]([CTP] ASC, [RV] ASC)
INCLUDE([ID], [DEL]);

GO
CREATE NONCLUSTERED INDEX [IX_files_ID]
ON [dbo].[files]([ID] ASC)
INCLUDE([OID], [SZ]);
4 added 18 characters in body
source | link
3 added 773 characters in body
source | link
2 added details & anon. exec plans
source | link
1
source | link