-1

I have a table with approximately 4.7 million records. I created a full text index on it. I am experiencing slow initial population of the full text index. Initial pricing tier that i had was S1, I upgraded it to S3 but i did not get better performance.

DTU and CPU are not high (usually staying around 0% ), the current velocity is about 175000 records per hour.

What can i do to speed this up?

Thanks in advance.

LE.

I tried same operation on a local instalation of SQL Server 2014, i had no problems with indexing the data.

Update 14.11.2016

Output to dm_Exec_requests session_id request_id start_time status command sql_handle statement_start_offset statement_end_offset plan_handle database_id user_id connection_id blocking_session_id wait_type wait_time last_wait_type wait_resource open_transaction_count open_resultset_count transaction_id context_info percent_complete estimated_completion_time cpu_time total_elapsed_time scheduler_id task_address reads writes logical_reads text_size language date_format date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority row_count prev_error nest_level granted_query_memory executing_managed_code group_id query_hash query_plan_hash statement_sql_handle statement_context_id dop parallel_worker_count external_script_request_id 90 0 57:45.2 running SELECT 0x020000004D4F6005A3E8119F3DD3297095832ABE63E312F20000000000000000000000000000000000000000 0 66 0x060005004D4F6005D04F998A6E00000001000000000000000000000000000000000000000000000000000000 5 1 70A61674-396D-47EB-82C7-F3C13DAA2AD0 0 NULL 0 MEMORY_ALLOCATION_EXT 0 1 141037 0x380035003100450039003200350032002D0045003700450032002D0034003600320041002D0039004200390041002D003200310037004400300036003700430032004100360039 0 0 1 1 0 0x7A218C885C2F7437 0 0 228 2147483647 us_english mdy 7 1 1 1 0 1 1 1 1 2 -1 0 1 0 0 0 0 2000000026 0xC1681A4180C2C052 0x63AD167562BDAE5D 0x0900A3E8119F3DD3297095832ABE63E312F20000000000000000000000000000000000000000000000000000 7 1 NULL NULL

As i can see on P1 this seems much faster though. It is strange because it is not much more powerfull than S3.

I will mark it as solved because, it seems this is an issue related to service tier levels.

Chiricescu Mihai
  • 239
  • 1
  • 9

1 Answers1

1

If you bump up the service tier of Azure database the full text indexing will run much faster than at standard level.

I could not sense a diference between S1 and S3, but P1 versus S3 is much faster. I do not know the resoning behind this, even though the diference in DTU's is only 25 (S3: 100 DTU, P1: 125 DTU )

Chiricescu Mihai
  • 239
  • 1
  • 9
  • Premium tier is also much more expensive. – wBob Nov 15 '16 at 23:45
  • 1
    @wBob Yes that is true, but you only need it while it updates the full-text index the first time, future operations are not problematic, and after this the search works also on an S0 price tier – Chiricescu Mihai Nov 17 '16 at 09:17