Slow Queries SQL Server Full-Text

0

I have created a catalog for Full-Text searches in SQL Server:

CREATE TABLE [dbo].[full_text](
[client_id] [int] NOT NULL,
[file_id] [bigint] NOT NULL,
[page_number] [int] NULL,
[contents] [nvarchar](max) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_full_text] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

create fulltext catalog dd_full_text;
GO

create fulltext index on full_text(contents)
key index PK_full_text on dd_full_text with change_tracking auto
GO

I use the following query as test:

SELECT *
FROM full_text
WHERE CONTAINS(contents, '"ban*" AND NOT "sab*"');
GO

The first consultations are relatively fast but as the minutes pass they slow down as shown in the following table:

Response Time ms

  • 2266
  • 3250
  • 3459
  • 11841 (after 10 minutes)
  • 15391
  • During the tests I have not inserted new rows, so I do not understand why each time the queries take longer to return the results.

        
    asked by Docu 18.05.2016 в 09:57
    source

    1 answer

    1

    due to performance problems can affect many variables, you need to add to the problem at least version of SQL Server, operating system, if in addition to SQL Server there are other applications running on the servers, resource state of the server% proc, % Disk use,% memory and free space in all disks, monitor performance counters in each execution, some trace, etc ... Greetings

        
    answered by 22.05.2016 в 13:27