jwcolby
jwcolby at colbyconsulting.com
Fri Sep 4 16:36:38 CDT 2009
>whenever a vendor comes forward with a solution that involves ODBC connectivity to SQL server I immediately raise red flags. ROTFL. Some day I will port it all to .Net but it won't be today, or even this month. I am going to try the new array with separate disks. I'll report back and let you know what that does for me. John W. Colby www.ColbyConsulting.com Eric Barro wrote: > 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 > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >