Eric Barro
ebarro at roadrunner.com
Fri Sep 4 16:06:34 CDT 2009
John, Access has to connect to SQL server and SQL server treats the Access connection as a Linked Server using ODBC. As already mentioned and it is a well-known fact, ODBC is such a lumbering beast when it comes to large datasets. Trust me I've seen this happen many times over and whenever a vendor comes forward with a solution that involves ODBC connectivity to SQL server I immediately raise red flags. Eric -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Friday, September 04, 2009 1:26 PM To: Discussion concerning MS SQL Server Subject: Re: [dba-SQLServer] SQL Server 2005 unresponsive Francisco, Thanks for the suggestions. > It's not Sql Server ALLOWING the ODBC call to send it to turtle mode, it's ODBC, that library is inherently slow, and has problems with larger datasets. Yes but - how can it cause SQL server to lock it up? All that is going on is that Access is running Stored procedures. Now these stored procedures do big operations, but still... I thought that SQL Server would magically prevent anyone from bringing it to it's knees. Naive I know. You may very well be right of course. I have so much data to store that I have been concentrating on size instead of "spindles". I have some spare drives I can use. I will throw those on there Raid 0. I doubt seriously that the CURRENT bottle neck is the controller, though it may very well change to that if I do a what we are discussing. Actually I do have a spare card hanging around. In fact i put it in my other server (just to see if it could be used simultaneously) and when I took it back out the raid software puts up a "raid missing" message at boot up. I cannot get rid of that message. Anyway... I could put that in and throw some extra drives on that controller. My bigger problem really is a another power supply. That server currently has 11 hard drives in it. I will definitely do that though just to see what that gets me. I have to do something!. John W. Colby www.ColbyConsulting.com Francisco Tapia wrote: > It's not Sql Server ALLOWING the ODBC call to send it to turtle mode, > it's ODBC, that library is inherently slow, and has problems with > larger datasets. > > If you are seeing slowness on your SQL Server you'll need to fine tune > a few things > > > First check the size of of your transaction logs, whenever they exceed > 50% of the data file size, then you can begin running into some > performance problems. (normally i see this if my transaction log file > is 2x or more than my data file.) > > also check the tempdb > > When you are MOVING a lot of data around, the way you are, you want to > have each data partition on it's own set of spindle disks, this > applies largely to your transaction logs, because you don't want to > loose the data but you want the system to process extremely quick, you > should look into raid 10 if you can afford it. For TempDB it's > possible to squeeze a lot of performance on the disks, first tempdb is > always rebuilt when the server comes back up, and all data is just > temporary, so you could go with a pair of raid 0 drives, possibly 3 > spindles if you need the extra speed. Striping is the fastest > possible solution to speeding up IO, ideally of course you'll want to have this on it's own separate i/o card and own channel if possible. > > benchmark your system by running it through the paces of the SQLIO > benchmarking tool from MS > http://www.microsoft.com/DOWNLOADS/details.aspx?familyid=9A8B005B-84E4 > -4F24-8D65-CB53442D9E19&displaylang=en > and benchmark as you move towards faster drives to see the new throughput. > In your case you might be easily impressed by moving the tempdb to a > set of raid 0 drives. > > > -Francisco > http://sqlthis.blogspot.com | Tsql and More... > > > On Fri, Sep 4, 2009 at 10:12 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > >> Jim, >> >> Thanks for the response. I have to agree, it does seem to be >> something ODBC / Access related in that pretty much everything I do >> directly inside of SQL Server works just fine. It is annoying that >> SQL Server would ALLOW an ODBC call from Access to cause it to go >> into turtle mode though. >> >> > Have you tried .Net? >> >> Oddly enough I have, but it was some time ago. I was doing this >> stuff in VB.Net / ADO, but I was so slow at the .Net development that >> when I needed to "get something done" I just went back to Access. >> I cannot make any valid comparisons however as back when I was doing >> the .Net work I also was a complete nube at the SQL Server stuff as >> well, couldn't even write a SP. >> >> I am taking a C# class now and will be moving squarely into C# in the >> future. I have pretty much made a decision to make that my future. >> But in the here and now... >> >> John W. Colby >> www.ColbyConsulting.com >> >> >> Jim Lawrence wrote: >>> I think the issue is Access related and not MS SQL. Had exactly the >>> same issues when connected to an Oracle DB. I ended up writing code >>> that would save my results to a temporary table, in the Oracle DB >>> and then would >> have >>> Access just retrieve the table results. >>> >>> The single process step of gathering the data and downloading a >> recordset, >>> via ADO would sometimes cause a hang up. If I used ODBC, it would >>> always cause a hold up especially on larger data sets. >>> >>> Have you tried .Net? >>> >>> Jim >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com