I have a table in SQL Server 2016 for which I am receiving data from an API. The data was in XML format so I have to break it into columns and bulk saving it into SQL. There are 32 columns in the table including identity and almost all the columns will be having data(means very few null values).
Now the data inserting is very huge(390120 rows per minute on average) and I should have the records of atleast 2-3 days.
The issues I am currently facing is:
- When the rows reached beyond 100 million(within first day) I couldn't even execute simple count query because it gives error of something like I/O paging error.
- The data keep bulk insert till around 200-300 million and after that the database go into 'Suspect' mode while there is still empty space on the drive.
What steps should I take to improve the performance and avoiding the above error?
If I use MongoDB, will it help solving the above issues?