jwcolby
jwcolby at colbyconsulting.com
Fri Sep 4 17:11:28 CDT 2009
OK so it appears that I have a raid1 500g drive that I set up previously and I just created a new 600 gb Raid 0. So now, how do I move things around? I do not see a gui way to determine where the temp db goes, nor for that matter where the transaction logs go. I assume that transaction logs go right in the database unless you do something specific to split them out? 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 > >