-1

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.

Now,

  • 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?

  • This sounds like you've got hardware problems. A DB shouldn't go into suspect mode unless somethings amiss. What's the exact error message for "something like I/O paging error"? – vonPryz May 30 '18 at 9:38
0

If I use MongoDB, will it help solving the above issues?

Given that the problem is in front of the database, no.

What steps should I take to improve the performance and avoiding the above error?

You see how you never talk about your hardware EXCEPT: "while there is still empty space on the drive"? I would not run something that hard on something with A drive - more a number of independent SSD for performance reasons. It is quite obvious that there is a signiicant discrepancy between the required hardware and the available hardware.

Also, are you using staging tables? How are you builk uploading? Some of the bulk upload mechanisms are not so nice (table locks) and you want to avoid that (staging tables). 390120 is NOT very hugh - last time I had do so that I was hitting around 60000 rows per second. Make the math. On a low end server that by now hits around 12 years age - but has around a dozen SSD.

Start optimizeing your setup. Moving to MongoDB will not change that you have obviously hardware totally unsuitable for the task at hand. Paging errors are brutal - paging means not enough RAM and should not be an issue on a sql server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.