Wired behaviour of microsoft.ace.oledb.12.0 64bit

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

We are using a SQL Server 2014 64 bit cluster in Windows server 2012 R2. We have huge amount of MS Access.mdb files coming from distributed areas. We use OpenRowSet for loading data from Access.mdb to SQL Server. Until we upgraded to 64bit server it was working fine. After migration to 64bit SQL Server we found that Jet.4.0 has no 64bit version so we switched to Ace.12.0 64bit. After upgrading to Ace.12.0 64bit performance has been degraded. Test result is wired.

SQL 2008R2 64 bit-7sec to 10min
SQL 2008R2 32 bit-7sec
SQL 2014 64 bit cluster-7sec-10min
We cannot take initiatives to test in SQL Server 2014 in 32 bit mode.

Tested on the same server on same query. I found that after restart either Server or SQL Server SQL 2014 64bit and 2008R2 32bit/64bit take 7 seconds to execute and it is gradually increased for 64bit as time elapsed. Execution time is not stable on 64bit SQL server but 32 bit SQL server takes fixed 7seconds to executes the query anytime. Ace.12.0 and Jet.4.0 takes the same time in 32bit mode but Ace.12.0 is not stable in 64bit mode.

We use 16core processor with 128GB RAM, 2Node with SAN switch. All network switches are gigabyte switch. 9tb usable storage.

Any help will be highly appreciated.

What I have tried:

select * from Openrowset('Microsoft.ACE.OLEDB.15.0','\\data\HD2016050088.mdb';'Admin';'',xxx)
select * from Openrowset('Microsoft.ACE.OLEDB.12.0','\\data\HD2016050088.mdb';'Admin';'',xxx)
select * from Openrowset('Microsoft.Jet.OLEDB.4.0','\\data\HD2016050088.mdb';'Admin';'',xxx)

Posted 29-Oct-16 22:53pm

Member 10521029

Updated 30-Oct-16 1:07am

OriginalGriff

Add a Solution

Comments

Wendelius 30-Oct-16 5:20am

How the loading is done? Do you use SSIS, do you have a stored procedure, a separate program, or something else?

Member 10521029 30-Oct-16 5:49am

We have a vb.net program that send a openrowset query to SQL server to execute. Actually I have tested the fact with my vb.net program as well as from sql server management studio also.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-10-30T01:07:00

Solution 1

I would try to use BULK options for the OPENROWSET. Especially defining ROWS_PER_BATCH and ORDER could have an impact on the performance depending where you load the data. For more information, see OPENROWSET (Transact-SQL)[^] and Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)[^]

If that doesn't reveal anything, try executing the commands directly on the SQL Server. At the same time monitor the memory, disk, network, and CPU usage to see which actually causes the bottleneck. You can use for example Windows Performance Monitor[^] for collecting data.

Posted 30-Oct-16 1:07am

Wendelius

Comments

Member 10521029 30-Oct-16 7:53am

Thanks Mika. I will try bulk and I have already checked performance monitor long time but no findings. I don't think there could be a bottle neck. In 32 bit mode everything is fine. After restart the server 64bit also file. It becomes slow gradually.

Wendelius 30-Oct-16 8:45am

If it's slowing down bit by bit, it could indicate for example a memory leak etc. Because of this the main memory could become the bottleneck and cause for example swapping. These are the things you would see when monitoring the server performance. After all there has to be a reason for the slowdown.

Member 10521029 30-Oct-16 9:24am

You are right Mika, obviously there should be a reason. I think there is something like memory leaking. So there should be a fixing also. Hope you know how to shield this. If you share I will be grateful.

Wendelius 30-Oct-16 11:22am

I think the best option is to gather data and report to Microsoft if the findings show a problem. Does the memory consumption grow constantly when the query is ran, what happens when the SQL Server is restarted, what do the meters in performance monitor show and so on. If this really is a memory leakage, then MS should have a fix for it.

I tried to find info about such situations but couldn't find anything that matches. It still could be a known problem after all.

Member 10521029 30-Oct-16 23:40pm

I found that memory consumption increasing gradually up to full memory(128gb). I googled and found that SQL Server 2014 has this feature to consume full memory. I have changed maximum server memory to 50% in the server properties. Now around 60gb of memory is always free.

Wendelius 31-Oct-16 11:27am

That behavior is by design. If I recall correctly, it was introduced in 2008. The idea is that if you have free memory, why not use it. If memory is needed by another application SQL Server also releases it's memory reservations as long until the minimum is reached.

So basically this shouldn't be the cause for the behavior unless freeing memory gets slow, but I can't see any reason for that unless the server is under heavy load or you typically have a lot of uncommitted data.

However, I'd be interested in hearing if this really solved the problem!

Member 10521029 1-Nov-16 1:55am

After a restart the server I found full performance yesterday. Then I executed the same query by some interval and get the same performance along the day which is 7sec for a lager table. Last I executed the query on 6pm which worked with charm. After then I went home and come back today 9am and I executed the same and it behave weird again. It takes 22sec for the same query. There is no load on the server. It is idle exactly. God knows what is going on.

Thanks Mika for your cooperation.

Wendelius 5-Nov-16 5:00am

Glad if it worked out!

Member 10521029 5-Nov-16 23:58pm

I found a network related issue on Node 01. I am working on Node02 now. It is working fine till now. Hope it will be working fine till the process completed. Thanks again Mika.

Member 10521029 15-Nov-16 23:56pm

Mika, at last I found there is a network level error. When ping to cluster name or IP reply time is very much high.It goes up to 500ms+. So I think there should be issue lying. Our network experts are working regarding this. Hope it will be fine soon.

Wendelius 23-Nov-16 13:00pm

That's good to hear! Sounds like a nasty problem and I hope you get it working properly. All the best!

Member 10521029 1-Feb-17 0:56am

After a long analysis, I stooped the SQL Analysis Service and everything start working fine. As well as I stopped the SQL Reporting Service it became more better in performance.
So finally I found that SQL Analysis Service/SQL Reporting Service could causes performance slow down. There should be configurations for optimization in those services but in my case stopping those services has done the tricks.

Thank you very much Mika for your cooperation.