Fast query, slow fetch in SQL Server 2008
-
We are about to change our configuration. From: 32 bit Server application running on Win 2000 32 bit server 32 bit MS SQL Server 2000 running on Win 2000 32 bit server To: 32 bit Server application running on virtual (VMWare) Win 2003 64 bit server 64 bit MS SQL Server 2008 running on Win 2008 64 bit server Furthermore, network resources are upgraded and are, according to tests, showing improved throughput. The new servers are equipped with 32 GB of RAM, as opposed to the old setup with 3 GB. But, allthough queries are performed aproximately 4 times faster, fetches are considerably slower. The overall performance of our system is now roughly the same as on the old servers. In fact, they seem to perform a bit slower. I have narrowed the problem down to fetch time. As mentioned earlier, the network seems to perform much better, so logically I should be able to take that out of the equation. Number crunching is also way faster on the new servers. Disk access on the application server is at least as fast as on the old configuration. Now I am totally out of reasonable ideas, does any of you guys know any issues about this configuration?
"God doesn't play dice" - Albert Einstein "God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr
-
We are about to change our configuration. From: 32 bit Server application running on Win 2000 32 bit server 32 bit MS SQL Server 2000 running on Win 2000 32 bit server To: 32 bit Server application running on virtual (VMWare) Win 2003 64 bit server 64 bit MS SQL Server 2008 running on Win 2008 64 bit server Furthermore, network resources are upgraded and are, according to tests, showing improved throughput. The new servers are equipped with 32 GB of RAM, as opposed to the old setup with 3 GB. But, allthough queries are performed aproximately 4 times faster, fetches are considerably slower. The overall performance of our system is now roughly the same as on the old servers. In fact, they seem to perform a bit slower. I have narrowed the problem down to fetch time. As mentioned earlier, the network seems to perform much better, so logically I should be able to take that out of the equation. Number crunching is also way faster on the new servers. Disk access on the application server is at least as fast as on the old configuration. Now I am totally out of reasonable ideas, does any of you guys know any issues about this configuration?
"God doesn't play dice" - Albert Einstein "God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr
Perhaps there is a buffer size issue with the fetch operations. If the buffers are too small, then many fetches will occur and this could slow things down. I'd first look at how the client side software is buffering any fetch operations. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
Perhaps there is a buffer size issue with the fetch operations. If the buffers are too small, then many fetches will occur and this could slow things down. I'd first look at how the client side software is buffering any fetch operations. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
Too bad then that our client application is Microsoft Axapta 3.0, which means that the client side is sealed from tampering :-(
"God doesn't play dice" - Albert Einstein "God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr
-
Too bad then that our client application is Microsoft Axapta 3.0, which means that the client side is sealed from tampering :-(
"God doesn't play dice" - Albert Einstein "God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr
It doesn't allow for any type of configuration at all? That's odd and not exactly customer friendly. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
It doesn't allow for any type of configuration at all? That's odd and not exactly customer friendly. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
Well, the idea is to encapsulate database handling to ease change of DBMS (Axapta is not originally created at Microsoft, so it actually supports Oracle too :-) ). There are some options that the Damgaard/Naviosion/Microsoft guys are exposing though. But the only promising field that I can find, is one labeled "ODBC options" which I presume is meant for additional options to a connection string. I haven't been able to find any documentation on valid ODBC options to SQL Server 2008 though...
"God doesn't play dice" - Albert Einstein "God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr