SQL Server Performances Issue

Question

I am running 7 XML Parsers (grabbing data from 3rd party websites) on my server.

When I am running each XML Parser separately, it takes around 1 minute to go thru 15MB XML (which is pretty huge).

When I running all the 7 together, it takes around 10 minutes each to finish running.

Each XML is adding a lot of information to the DB, and running around 50,000 queries.

The server I am running my parsers on is:

• Server type: Hp Dl180 g6
• Processor type: Intel Xeon E5504
• Processor speed: 2000 Mhz
• Number of CPU's: 2
• Number of cores: 4
• RAM: 16 GB

Running SQL Server Web Edition on 2 CPU's

The problem comes when all the parsers run parallel, the SQL Server's CPU goes to 100%.

And the really big issue, is that I need to have around 50 XML Parsers in the future :(

What do you advice guys? should I upgrade the XML server maybe? or the server itself?

Maybe the SQL Server is running on 1 CPU and not on 2 CPU's? how can I check that?

Any advice would help...

Answer 1

Even though the CPU is at 100%, I think your bottleneck here is I/O. Specially because you said that you have several process reading and writing data to the disk at the same time. Can you check the I/O on your server?

If the processes are hitting different tables there may be somethings you can do, like using Filegroups to split your DB into different HD.

If they are hitting the same tables, you probably have a blocking issue where parser A tries to read data to the same table parser B is writing. I would recommend, JUST TO TEST and find out if this is really the problem, running your process with isolation level read uncommited. If it runs fine, you'll know what the issue was.

Answer 2

Had the same problem. i had 600,000 lines of XML which didn't finish processing even after an hour. What worked for me was to break the XML into smaller chunks and insert the chunks to a temp table (my XML described TV shoes so i broke it to 200 different channels) and now it takes 1 minute for the entire XML