64mb limit for linked server selects?
in [Using MS SQL Server]
|
From: Erland Sommarskog on 30 Jun 2010 18:11 BitBuster (ivarru(a)gmail.com) writes: > I get 0 from both my linked servers! > (... but if I write 3000000 instead of 7000000, the result is > 60000000.) > > One is a SQL Server 2008 Express Edition on a (virtual) 64 bit > Windows Server 2008 located in a so-called DMZ in the same building. > The other is a SQL Server 2005 Standard Edition on a (virtual) 32 bit > Windows Server 2003 somewhere far away. > My local server is similar (to the second one). OK, I was able to reproduce it, and I also have an idea what is going on. The problem occurs on the local server, not the remote server. The keyword is 32-bit. On a 32-bit server there is by necessity some restrictions with memory. 32-bit SQL Server is able to access more than 4GB of memory through AWE, but this can only be used for the buffer pool. This means that everything else must be in the regular address space. It is not uncommon to run out of this memory. There is a certain area known as memtoleave, which is used for various things like memory for the OLE DB provider. By default this memory is 256 MB. You can increase it with the server option -g; I tried this, but on my server at least it did not help. This may simply be a hard limit. On the other hand, if I run the batch from a 64-bit machine, I get back 140 millions - even if the remote server is 32-bit. I also ran the test on a virtual machine with only 516 MB of memory in total. In this case the batch produced an error. I would suggest that in the case we get back 0, this is a bug; an error message should be produced. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 30 Jun 2010 18:15 One more thing: I didn't see this first, but there is a big fat error message in the SQL Server error log on the local server about PAGE_FAIL_ALLOCATION. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: BitBuster on 3 Jul 2010 19:11 On Jul 1, 12:11 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > OK, I was able to reproduce it, and I also have an idea what is > going on. > [...] > This may simply be a hard limit. > > On the other hand, if I run the batch from a 64-bit machine, I get > back 140 millions - even if the remote server is 32-bit. OK. Until we upgrade our servers, I will avoid the problem using compression (gzip). > I also ran the test on a virtual machine with only 516 MB of memory in > total. In this case the batch produced an error. I would suggest that > in the case we get back 0, this is a bug; an error message should be > produced. I agree. Will you inform Microsoft? On Jul 1, 12:15 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > One more thing: I didn't see this first, but there is a big fat error > message in the SQL Server error log on the localserver about > PAGE_FAIL_ALLOCATION. Strange. I did not see any such entries in my logs (and I can not find any info on PAGE_FAIL_ALLOCATION on the web). Did this happen when you tried with 516 MB of memory? -- Ivar
From: Erland Sommarskog on 4 Jul 2010 04:25
BitBuster (ivarru(a)gmail.com) writes: > On Jul 1, 12:11�am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: >> I also ran the test on a virtual machine with only 516 MB of memory in >> total. In this case the batch produced an error. I would suggest that >> in the case we get back 0, this is a bug; an error message should be >> produced. > > I agree. Will you inform Microsoft? I submitted https://connect.microsoft.com/SQLServer/feedback/details/573055/query- against-linked-may-return-incorrect-result-when-memory-runs-out but to be honest, I don't really expect them to fix it. It's after all quite a crazy thing to do on a 32-bit machine. > On Jul 1, 12:15�am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: >> One more thing: I didn't see this first, but there is a big fat error >> message in the SQL Server error log on the localserver about >> PAGE_FAIL_ALLOCATION. > > Strange. I did not see any such entries in my logs > (and I can not find any info on PAGE_FAIL_ALLOCATION on the web). > Did this happen when you tried with 516 MB of memory? Both. But I messed up the error code, it's FAIL_PAGE_ALLOCATION. Here is an extract from one my logs: 2010-06-30 23:12:47.81 spid53 Failed allocate pages: FAIL_PAGE_ALLOCATION 17090 2010-06-30 23:12:47.84 spid53 Memory Manager KB ---------------------------------------- ---------- VM Reserved 554912 VM Committed 158480 AWE Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0 2010-06-30 23:12:47.85 spid53 Memory node Id = 0 KB Note that the message occurs in the log on the server where you run the query. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |