 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008Help on performance - rows per table
Hi, We are using 2005 64bit, the server is very decent. We have a project which will use a table to store quite large numbers of records, I know rows per table depends on the limitation of storage, however, I like to know what's the rocemmened rows per table which won't affect performance. On this table, they will do select, update and so on. Is over 1 milli ok? Thanks, Sarah Sarah, Performance of rows per table is actually a matter of proper indexes. If you have indexes that are appropriate to your primary queries, 1 million rows should be no problem for your server. (At the other extreme, if there are no indexes at all it would always have to scan all of the data which could be rather slow.) When should you start partitioning the data by some method? Not at 1 million rows, perhaps at 100 million. Where in-between? It depends on your use, queries, required response time, and so forth. RLF > Hi, > We are using 2005 64bit, the server is very decent. We have a project [quoted text clipped - 7 lines] > Thanks, > Sarah Thanks very much and this is helpful. > Sarah, > [quoted text clipped - 21 lines] > > Thanks, > > Sarah If it helps, I'd like to point out that on many of the well-performing databases that I've worked with, tables with over one million rows are common. However, if you can keep your database small, by all means keep it small. When all the other necessary work is done, a smaller database is always better to handle and easier to get it to perform. Linchi > Thanks very much and this is helpful. > [quoted text clipped - 23 lines] > > > Thanks, > > > Sarah Talking about performance, here is a very interesting article that I think you would enjoy: 'Spending Moore's Dividend' by James Larus, Microsoft Research ftp://ftp.research.microsoft.com/pub/tr/TR-2008-69.pdf This is a big picture look at what software vendors have done with hardware advances over the last 30 years. I think many would be surprised with the authors view. For a bit more insight into where his head is see: 'Operating Systems Should Control the Execution Environment' http://research.ihost.com/vee/vee06/larus.html (additional links available in above.) Remarkably, I can find next to nothing in response to this article on the net. I think this is a very important article and shows not everyone at MS is in the same box :) best, www.beyondsql.blogspot.com > If it helps, I'd like to point out that on many of the well-performing > databases that I've worked with, tables with over one million rows are > common. However, if you can keep your database small, by all means keep it > small. When all the other necessary work is done, a smaller database is > always better to handle and easier to get it to perform. >Talking about performance, here is a very interesting article that I think >you would enjoy: [quoted text clipped - 6 lines] >advances over the last 30 years. I think many would be surprised with the >authors view. I'm not surprised. Back in the Windows 3.x days, Microsoft had so many wait loops in the code, that alone ate up all CPU power - it just waited faster! Even in early NT days, many drivers were badly written, or maybe it was the OS, to single-thread the processor. With the advent of multi-core processors, at least this kind of bad coding has less impact. Other than that, I do not much share the author's idea that multi-core processing represents a paradigmatic move to parallelism. SQLServer has always run much better with multiple processors, at least when the OS (especially if trying to run SQLServer on the PDC!) pended on some single-thread, there were better odds that SQLServer could keep up other activities. The proper use of parallelism even within SQLServer is still problematic, we need to avoid one parallel plan hogging a bunch of idle processors rather than parcelling them out better with lower MAXDOP settings per spid. Heck, I'd settle for better documentation on what it means when you have 15 or 27 copies of the same spid showing on sp_who2 displays! Even modern workstations, with gigabytes of RAM, multiple cores, even a single fast disk, etc, truly CRANK compared to things just a few years ago. And the big 64-bit servers are generally ahead of the curve, in terms of hardware power. These are good times! Josh >>Talking about performance, here is a very interesting article that I think >>you would enjoy: [quoted text clipped - 32 lines] > years ago. And the big 64-bit servers are generally ahead of the > curve, in terms of hardware power. These are good times! There is a difference between a good time and an orgy. Isn't that his point?:) The 'model' that software is built on didn't take full advantage of hardware advances. We should be doing much better than we are. If we missed the boat (and got the bloat) in the past perhaps it would be wise to look before we leap in the future. www.beyondsql.blogspot.com >> Even modern workstations, with gigabytes of RAM, multiple cores, even >> a single fast disk, etc, truly CRANK compared to things just a few [quoted text clipped - 8 lines] > >www.beyondsql.blogspot.com 'twas ever thus, see: Parkinson's Law J. >We are using 2005 64bit, the server is very decent. We have a project which >will use a table to store quite large numbers of records, I know rows per >table depends on the limitation of storage, however, I like to know what's >the rocemmened rows per table which won't affect performance. On this table, >they will do select, update and so on. Is over 1 milli ok? One million is pretty modest by modern standards, danger zone starts upwards of ten million, depending of course on a zillion other possible complications. If you can load the full million into a few gig of RAM, all the better, things will (in general) really fly. Josh |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.