|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Multiple databases performance
I have developed a system that potentially will be run by thousands of
customers. In order to make it easy to partition the system on multiple servers, I have opted to use a separate database for each customer. That way, as the number of customers grow, I can easily add SQL servers to divide the load. The number of transactions for each database is low, typically we have 3 transactions/hour with a typical peak usage of 120 transactions/hour. Are there any special issues in such a setup? What is the performance issues of using a large number of databases? How many databases will I be able to handle on each server? Regards; /jb Hi
The maximum number of database one SQLServer instance can support is 32,767 and i don't think anyone has ever reached this limit. Check this link for maximum capacity specification : http://technet.microsoft.com/en-us/library/ms143432.aspx For sure the server/s should be able to handle those database i menat in term of memory/disk capacity and etc... Also how do you consider to maintence the databases (backups/rebuild indexes/update statistics)?? Show quoteHide quote "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message news:OulDgr98JHA.1252@TK2MSFTNGP04.phx.gbl... >I have developed a system that potentially will be run by thousands of >customers. In order to make it easy to partition the system on multiple >servers, I have opted to use a separate database for each customer. That >way, as the number of customers grow, I can easily add SQL servers to >divide the load. > > The number of transactions for each database is low, typically we have 3 > transactions/hour with a typical peak usage of 120 transactions/hour. > > Are there any special issues in such a setup? What is the performance > issues of using a large number of databases? How many databases will I be > able to handle on each server? > > Regards; > /jb I am aware of the maximum number of databases allowed, and I think nobody
could ever get even near that figure without other reaching other limits. All databases are stored on SAN disks, so addings disk space as needed is not an issue. A typical database would be like 10-15 Mbytes. Backups are a limiting factor, since we need to assert that we never loose more than 30 minutes of data in any database if a crash occurs. Recovery of lost data is allowed to take up to 4 hours though. Regards; /jb Show quoteHide quote "Uri Dimant" <u***@iscar.co.il> skrev i meddelandet news:Odqhz798JHA.3544@TK2MSFTNGP04.phx.gbl... > Hi > The maximum number of database one SQLServer instance can support is > 32,767 and i don't think anyone has ever reached this limit. Check this > link for maximum capacity specification : > http://technet.microsoft.com/en-us/library/ms143432.aspx > > For sure the server/s should be able to handle those database i menat in > term of memory/disk capacity and etc... > Also how do you consider to maintence the databases (backups/rebuild > indexes/update statistics)?? > > > > > > "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message > news:OulDgr98JHA.1252@TK2MSFTNGP04.phx.gbl... >>I have developed a system that potentially will be run by thousands of >>customers. In order to make it easy to partition the system on multiple >>servers, I have opted to use a separate database for each customer. That >>way, as the number of customers grow, I can easily add SQL servers to >>divide the load. >> >> The number of transactions for each database is low, typically we have 3 >> transactions/hour with a typical peak usage of 120 transactions/hour. >> >> Are there any special issues in such a setup? What is the performance >> issues of using a large number of databases? How many databases will I be >> able to handle on each server? >> >> Regards; >> /jb > > The approach is fine if you expect the the load on the server will max out
the server resource before the number of databases becomes too large. Personally, I'd consider hundreds of databases on a single server to be too large. But from your description of the load, the traffic is so light that the number of databases may cause a maintnenance issue before any real computing resource (CPU, memory, disk I/Os, etc) becomes a bottleneck. If the second sceanrio is more likely, you may want to consider partitioning your data inside the same database instead. Linchi Show quoteHide quote "Jonny Bergdahl" wrote: > I have developed a system that potentially will be run by thousands of > customers. In order to make it easy to partition the system on multiple > servers, I have opted to use a separate database for each customer. That > way, as the number of customers grow, I can easily add SQL servers to divide > the load. > > The number of transactions for each database is low, typically we have 3 > transactions/hour with a typical peak usage of 120 transactions/hour. > > Are there any special issues in such a setup? What is the performance issues > of using a large number of databases? How many databases will I be able to > handle on each server? > > Regards; > /jb > > > The approach is fine if you expect the the load on the server will max out Question is what number of databases is "too large".> the server resource before the number of databases becomes too large. > Personally, I'd consider hundreds of databases on a single server to be Any special reason for this, or it is just a feeling?> too > large. Regards; /jb > Any special reason for this, or it is just a feeling? Just some non-scientific previous experience with various management tools. > Note that many of these tools would loop through the databases to do things. If there are too many databases, that looping can take long and give the user a bad experience. If you have an environment, I'd suggest you create a few hundred dummy databases, and check it out yourself. Maybe, the most current tools don't have that issue any more. Linchi Show quoteHide quote "Jonny Bergdahl" wrote: > > The approach is fine if you expect the the load on the server will max out > > the server resource before the number of databases becomes too large. > > Question is what number of databases is "too large". > > > Personally, I'd consider hundreds of databases on a single server to be > > too > > large. > > Any special reason for this, or it is just a feeling? > > Regards; > /jb > > I created 300 hundred databases on a test machine, and Management Studio
appears to be handling them fine. I didn't do a thorough check though. Linchi Show quoteHide quote "Linchi Shea" wrote: > > Any special reason for this, or it is just a feeling? > > > Just some non-scientific previous experience with various management tools. > Note that many of these tools would loop through the databases to do things. > If there are too many databases, that looping can take long and give the user > a bad experience. > > If you have an environment, I'd suggest you create a few hundred dummy > databases, and check it out yourself. Maybe, the most current tools don't > have that issue any more. > > Linchi > > "Jonny Bergdahl" wrote: > > > > The approach is fine if you expect the the load on the server will max out > > > the server resource before the number of databases becomes too large. > > > > Question is what number of databases is "too large". > > > > > Personally, I'd consider hundreds of databases on a single server to be > > > too > > > large. > > > > Any special reason for this, or it is just a feeling? > > > > Regards; > > /jb > > > > > I created 300 hundred databases on a test machine, and Management Studio I have a system with over 500 and am accessing pretty much everything -> appears to be handling them fine. I didn't do a thorough check though. remotely, no less - via Object Explorer, with no noticeable effects. Yeah, i tried 2,000 databases, and Management Studio appears to handle them
fine. I'm not sure if there is any implication on the startup process when SQL Server has to go through them to analyze, redo/undo, etc. Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > > I created 300 hundred databases on a test machine, and Management Studio > > appears to be handling them fine. I didn't do a thorough check though. > > I have a system with over 500 and am accessing pretty much everything - > remotely, no less - via Object Explorer, with no noticeable effects. > > Yes, that is a much different animal (Geoff has some very rigid opinions
about that). I was just addressing from the SSMS side of things. On 6/23/09 2:34 PM, in article F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote: Show quoteHide quote > Yeah, i tried 2,000 databases, and Management Studio appears to handle them > fine. I'm not sure if there is any implication on the startup process when > SQL Server has to go through them to analyze, redo/undo, etc. > > Linchi > > "Aaron Bertrand [SQL Server MVP]" wrote: > >>> I created 300 hundred databases on a test machine, and Management Studio >>> appears to be handling them fine. I didn't do a thorough check though. >> >> I have a system with over 500 and am accessing pretty much everything - >> remotely, no less - via Object Explorer, with no noticeable effects. >> >> The good news is that if the databases are clean (e.g. no dirty pages,
nothing to rollback/forward), the startup and shutdown processes are not so bad even with 2000 databases. It did take longer to shut down cleanly because of checkpoints. Now, if the databases are not shut down cleanly, it would be a different story. Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > Yes, that is a much different animal (Geoff has some very rigid opinions > about that). I was just addressing from the SSMS side of things. > > > > On 6/23/09 2:34 PM, in article > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi Shea" > <LinchiS***@discussions.microsoft.com> wrote: > > > Yeah, i tried 2,000 databases, and Management Studio appears to handle them > > fine. I'm not sure if there is any implication on the startup process when > > SQL Server has to go through them to analyze, redo/undo, etc. > > > > Linchi > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >>> I created 300 hundred databases on a test machine, and Management Studio > >>> appears to be handling them fine. I didn't do a thorough check though. > >> > >> I have a system with over 500 and am accessing pretty much everything - > >> remotely, no less - via Object Explorer, with no noticeable effects. > >> > >> > > > Yes, that is a much different animal (Geoff has some very rigid opinions What startup times would I be looking at after say a power loss? Any other > about that). I was just addressing from the SSMS side of things. issues? Regards; /jb Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev i meddelandet news:C6669E1F.2E948%ten.xoc@dnartreb.noraa... > Yes, that is a much different animal (Geoff has some very rigid opinions > about that). I was just addressing from the SSMS side of things. > > > > On 6/23/09 2:34 PM, in article > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi Shea" > <LinchiS***@discussions.microsoft.com> wrote: > >> Yeah, i tried 2,000 databases, and Management Studio appears to handle >> them >> fine. I'm not sure if there is any implication on the startup process >> when >> SQL Server has to go through them to analyze, redo/undo, etc. >> >> Linchi >> >> "Aaron Bertrand [SQL Server MVP]" wrote: >> >>>> I created 300 hundred databases on a test machine, and Management >>>> Studio >>>> appears to be handling them fine. I didn't do a thorough check though. >>> >>> I have a system with over 500 and am accessing pretty much everything - >>> remotely, no less - via Object Explorer, with no noticeable effects. >>> >>> > > What startup times would I be looking at after say a power loss? Any other It depends on how much rollback and/or roll forward SQL Server has to > issues? perform in each database, and that in turn depends on how much change has not been committed to the transaction logs and/or not flushed to data files on power loss. If you have many databases, it would take longer for SQL Server to get to some databases. Linchi Show quoteHide quote "Jonny Bergdahl" wrote: > > Yes, that is a much different animal (Geoff has some very rigid opinions > > about that). I was just addressing from the SSMS side of things. > > What startup times would I be looking at after say a power loss? Any other > issues? > > Regards; > /jb > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev i > meddelandet news:C6669E1F.2E948%ten.xoc@dnartreb.noraa... > > Yes, that is a much different animal (Geoff has some very rigid opinions > > about that). I was just addressing from the SSMS side of things. > > > > > > > > On 6/23/09 2:34 PM, in article > > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi Shea" > > <LinchiS***@discussions.microsoft.com> wrote: > > > >> Yeah, i tried 2,000 databases, and Management Studio appears to handle > >> them > >> fine. I'm not sure if there is any implication on the startup process > >> when > >> SQL Server has to go through them to analyze, redo/undo, etc. > >> > >> Linchi > >> > >> "Aaron Bertrand [SQL Server MVP]" wrote: > >> > >>>> I created 300 hundred databases on a test machine, and Management > >>>> Studio > >>>> appears to be handling them fine. I didn't do a thorough check though. > >>> > >>> I have a system with over 500 and am accessing pretty much everything - > >>> remotely, no less - via Object Explorer, with no noticeable effects. > >>> > >>> > > > > I don't know if I have understood this correctly, but I have assumed that
when a power failure occurs and SQL Server restarts, it replays the whole log file. In that case the recovery time it would depend on how large the log is, which in turn depends on how often the backup job is run. Are You saying that it only replays the part of the log file that has been committed but not flushed? Regards; /jb Show quoteHide quote "Linchi Shea" <LinchiS***@discussions.microsoft.com> skrev i meddelandet news:99B0A912-D45C-4CF1-85CA-A57BE9695860@microsoft.com... >> What startup times would I be looking at after say a power loss? Any >> other >> issues? > > It depends on how much rollback and/or roll forward SQL Server has to > perform in each database, and that in turn depends on how much change has > not > been committed to the transaction logs and/or not flushed to data files on > power loss. If you have many databases, it would take longer for SQL > Server > to get to some databases. > > Linchi > > "Jonny Bergdahl" wrote: > >> > Yes, that is a much different animal (Geoff has some very rigid >> > opinions >> > about that). I was just addressing from the SSMS side of things. >> >> What startup times would I be looking at after say a power loss? Any >> other >> issues? >> >> Regards; >> /jb >> >> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev i >> meddelandet news:C6669E1F.2E948%ten.xoc@dnartreb.noraa... >> > Yes, that is a much different animal (Geoff has some very rigid >> > opinions >> > about that). I was just addressing from the SSMS side of things. >> > >> > >> > >> > On 6/23/09 2:34 PM, in article >> > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi Shea" >> > <LinchiS***@discussions.microsoft.com> wrote: >> > >> >> Yeah, i tried 2,000 databases, and Management Studio appears to handle >> >> them >> >> fine. I'm not sure if there is any implication on the startup process >> >> when >> >> SQL Server has to go through them to analyze, redo/undo, etc. >> >> >> >> Linchi >> >> >> >> "Aaron Bertrand [SQL Server MVP]" wrote: >> >> >> >>>> I created 300 hundred databases on a test machine, and Management >> >>>> Studio >> >>>> appears to be handling them fine. I didn't do a thorough check >> >>>> though. >> >>> >> >>> I have a system with over 500 and am accessing pretty much >> >>> verything - >> >>> remotely, no less - via Object Explorer, with no noticeable effects. >> >>> >> >>> >> > >> >> >I don't know if I have understood this correctly, but I have assumed No, it will go to where the most recent checkpoint occurred and then >that when a power failure occurs and SQL Server restarts, it replays >the whole log file. first REDO from there and lastly possibly UNDO open transactions. In 2005, we have fast recovery, where SQL Server can possibly start before checkpoint, so it during REDO can lock pages which then will be rolled back (so if we have EE can get into the database after REDO), but that is a minor adjustment to above principal. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message news:etEQylW9JHA.1380@TK2MSFTNGP02.phx.gbl... >I don't know if I have understood this correctly, but I have assumed >that when a power failure occurs and SQL Server restarts, it replays >the whole log file. In that case the recovery time it would depend on >how large the log is, which in turn depends on how often the backup >job is run. > > Are You saying that it only replays the part of the log file that > has been committed but not flushed? > > Regards; > /jb > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> skrev i > meddelandet > news:99B0A912-D45C-4CF1-85CA-A57BE9695860@microsoft.com... >>> What startup times would I be looking at after say a power loss? >>> Any other >>> issues? >> >> It depends on how much rollback and/or roll forward SQL Server has >> to >> perform in each database, and that in turn depends on how much >> change has not >> been committed to the transaction logs and/or not flushed to data >> files on >> power loss. If you have many databases, it would take longer for >> SQL Server >> to get to some databases. >> >> Linchi >> >> "Jonny Bergdahl" wrote: >> >>> > Yes, that is a much different animal (Geoff has some very rigid >>> > opinions >>> > about that). I was just addressing from the SSMS side of >>> > things. >>> >>> What startup times would I be looking at after say a power loss? >>> Any other >>> issues? >>> >>> Regards; >>> /jb >>> >>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev i >>> meddelandet news:C6669E1F.2E948%ten.xoc@dnartreb.noraa... >>> > Yes, that is a much different animal (Geoff has some very rigid >>> > opinions >>> > about that). I was just addressing from the SSMS side of >>> > things. >>> > >>> > >>> > >>> > On 6/23/09 2:34 PM, in article >>> > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi >>> > Shea" >>> > <LinchiS***@discussions.microsoft.com> wrote: >>> > >>> >> Yeah, i tried 2,000 databases, and Management Studio appears to >>> >> handle >>> >> them >>> >> fine. I'm not sure if there is any implication on the startup >>> >> process >>> >> when >>> >> SQL Server has to go through them to analyze, redo/undo, etc. >>> >> >>> >> Linchi >>> >> >>> >> "Aaron Bertrand [SQL Server MVP]" wrote: >>> >> >>> >>>> I created 300 hundred databases on a test machine, and >>> >>>> Management >>> >>>> Studio >>> >>>> appears to be handling them fine. I didn't do a thorough >>> >>>> check though. >>> >>> >>> >>> I have a system with over 500 and am accessing pretty much >>> >>> verything - >>> >>> remotely, no less - via Object Explorer, with no noticeable >>> >>> effects. >>> >>> >>> >>> >>> > >>> >>> > Ok, that is interesting. So the question is when the checkpoint occurs? Is
that whenever a transaction has successfully been commited? Regards; /jb Show quoteHide quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> skrev i meddelandet news:eKZiPGX9JHA.4948@TK2MSFTNGP04.phx.gbl... > >I don't know if I have understood this correctly, but I have assumed that > >when a power failure occurs and SQL Server restarts, it replays the whole > >log file. > > No, it will go to where the most recent checkpoint occurred and then first > REDO from there and lastly possibly UNDO open transactions. > > In 2005, we have fast recovery, where SQL Server can possibly start before > checkpoint, so it during REDO can lock pages which then will be rolled > back (so if we have EE can get into the database after REDO), but that is > a minor adjustment to above principal. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message > news:etEQylW9JHA.1380@TK2MSFTNGP02.phx.gbl... >>I don't know if I have understood this correctly, but I have assumed that >>when a power failure occurs and SQL Server restarts, it replays the whole >>log file. In that case the recovery time it would depend on how large the >>log is, which in turn depends on how often the backup job is run. >> >> Are You saying that it only replays the part of the log file that has >> been committed but not flushed? >> >> Regards; >> /jb >> >> "Linchi Shea" <LinchiS***@discussions.microsoft.com> skrev i meddelandet >> news:99B0A912-D45C-4CF1-85CA-A57BE9695860@microsoft.com... >>>> What startup times would I be looking at after say a power loss? Any >>>> other >>>> issues? >>> >>> It depends on how much rollback and/or roll forward SQL Server has to >>> perform in each database, and that in turn depends on how much change >>> has not >>> been committed to the transaction logs and/or not flushed to data files >>> on >>> power loss. If you have many databases, it would take longer for SQL >>> Server >>> to get to some databases. >>> >>> Linchi >>> >>> "Jonny Bergdahl" wrote: >>> >>>> > Yes, that is a much different animal (Geoff has some very rigid >>>> > opinions >>>> > about that). I was just addressing from the SSMS side of things. >>>> >>>> What startup times would I be looking at after say a power loss? Any >>>> other >>>> issues? >>>> >>>> Regards; >>>> /jb >>>> >>>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev i >>>> meddelandet news:C6669E1F.2E948%ten.xoc@dnartreb.noraa... >>>> > Yes, that is a much different animal (Geoff has some very rigid >>>> > opinions >>>> > about that). I was just addressing from the SSMS side of things. >>>> > >>>> > >>>> > >>>> > On 6/23/09 2:34 PM, in article >>>> > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi Shea" >>>> > <LinchiS***@discussions.microsoft.com> wrote: >>>> > >>>> >> Yeah, i tried 2,000 databases, and Management Studio appears to >>>> >> handle >>>> >> them >>>> >> fine. I'm not sure if there is any implication on the startup >>>> >> process >>>> >> when >>>> >> SQL Server has to go through them to analyze, redo/undo, etc. >>>> >> >>>> >> Linchi >>>> >> >>>> >> "Aaron Bertrand [SQL Server MVP]" wrote: >>>> >> >>>> >>>> I created 300 hundred databases on a test machine, and Management >>>> >>>> Studio >>>> >>>> appears to be handling them fine. I didn't do a thorough check >>>> >>>> though. >>>> >>> >>>> >>> I have a system with over 500 and am accessing pretty much >>>> >>> verything - >>>> >>> remotely, no less - via Object Explorer, with no noticeable >>>> >>> effects. >>>> >>> >>>> >>> >>>> > >>>> >>>> >> > No, not at commit time, that would be too costly. SQL Server's goal is
for the REDO phase to not take longer than 1 minute - that is what controls how often the checkpoint occurs. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message news:eGn7tYX9JHA.200@TK2MSFTNGP05.phx.gbl... > Ok, that is interesting. So the question is when the checkpoint > occurs? Is that whenever a transaction has successfully been > commited? > > Regards; > /jb > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> > skrev i meddelandet news:eKZiPGX9JHA.4948@TK2MSFTNGP04.phx.gbl... >> >I don't know if I have understood this correctly, but I have >> >assumed that when a power failure occurs and SQL Server restarts, >> >it replays the whole log file. >> >> No, it will go to where the most recent checkpoint occurred and >> then first REDO from there and lastly possibly UNDO open >> transactions. >> >> In 2005, we have fast recovery, where SQL Server can possibly start >> before checkpoint, so it during REDO can lock pages which then will >> be rolled back (so if we have EE can get into the database after >> REDO), but that is a minor adjustment to above principal. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message >> news:etEQylW9JHA.1380@TK2MSFTNGP02.phx.gbl... >>>I don't know if I have understood this correctly, but I have >>>assumed that when a power failure occurs and SQL Server restarts, >>>it replays the whole log file. In that case the recovery time it >>>would depend on how large the log is, which in turn depends on how >>>often the backup job is run. >>> >>> Are You saying that it only replays the part of the log file that >>> has been committed but not flushed? >>> >>> Regards; >>> /jb >>> >>> "Linchi Shea" <LinchiS***@discussions.microsoft.com> skrev i >>> meddelandet >>> news:99B0A912-D45C-4CF1-85CA-A57BE9695860@microsoft.com... >>>>> What startup times would I be looking at after say a power loss? >>>>> Any other >>>>> issues? >>>> >>>> It depends on how much rollback and/or roll forward SQL Server >>>> has to >>>> perform in each database, and that in turn depends on how much >>>> change has not >>>> been committed to the transaction logs and/or not flushed to data >>>> files on >>>> power loss. If you have many databases, it would take longer for >>>> SQL Server >>>> to get to some databases. >>>> >>>> Linchi >>>> >>>> "Jonny Bergdahl" wrote: >>>> >>>>> > Yes, that is a much different animal (Geoff has some very >>>>> > rigid opinions >>>>> > about that). I was just addressing from the SSMS side of >>>>> > things. >>>>> >>>>> What startup times would I be looking at after say a power loss? >>>>> Any other >>>>> issues? >>>>> >>>>> Regards; >>>>> /jb >>>>> >>>>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev >>>>> i >>>>> meddelandet news:C6669E1F.2E948%ten.xoc@dnartreb.noraa... >>>>> > Yes, that is a much different animal (Geoff has some very >>>>> > rigid opinions >>>>> > about that). I was just addressing from the SSMS side of >>>>> > things. >>>>> > >>>>> > >>>>> > >>>>> > On 6/23/09 2:34 PM, in article >>>>> > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi >>>>> > Shea" >>>>> > <LinchiS***@discussions.microsoft.com> wrote: >>>>> > >>>>> >> Yeah, i tried 2,000 databases, and Management Studio appears >>>>> >> to handle >>>>> >> them >>>>> >> fine. I'm not sure if there is any implication on the startup >>>>> >> process >>>>> >> when >>>>> >> SQL Server has to go through them to analyze, redo/undo, etc. >>>>> >> >>>>> >> Linchi >>>>> >> >>>>> >> "Aaron Bertrand [SQL Server MVP]" wrote: >>>>> >> >>>>> >>>> I created 300 hundred databases on a test machine, and >>>>> >>>> Management >>>>> >>>> Studio >>>>> >>>> appears to be handling them fine. I didn't do a thorough >>>>> >>>> check though. >>>>> >>> >>>>> >>> I have a system with over 500 and am accessing pretty much >>>>> >>> verything - >>>>> >>> remotely, no less - via Object Explorer, with no noticeable >>>>> >>> effects. >>>>> >>> >>>>> >>> >>>>> > >>>>> >>>>> >>> >> > Ah! This means that (if the load is not too high to have stopped the
checkpoint from happening) it will have to replay the log file for at most one minute? That would mean that very few of the databases actually would have to replay the log. Regards; /jb Show quoteHide quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> skrev i meddelandet news:OdalgfX9JHA.1336@TK2MSFTNGP05.phx.gbl... > No, not at commit time, that would be too costly. SQL Server's goal is for > the REDO phase to not take longer than 1 minute - that is what controls > how often the checkpoint occurs. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message > news:eGn7tYX9JHA.200@TK2MSFTNGP05.phx.gbl... >> Ok, that is interesting. So the question is when the checkpoint occurs? >> Is that whenever a transaction has successfully been commited? >> >> Regards; >> /jb >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> skrev >> i meddelandet news:eKZiPGX9JHA.4948@TK2MSFTNGP04.phx.gbl... >>> >I don't know if I have understood this correctly, but I have assumed >>> >that when a power failure occurs and SQL Server restarts, it replays >>> >the whole log file. >>> >>> No, it will go to where the most recent checkpoint occurred and then >>> first REDO from there and lastly possibly UNDO open transactions. >>> >>> In 2005, we have fast recovery, where SQL Server can possibly start >>> before checkpoint, so it during REDO can lock pages which then will be >>> rolled back (so if we have EE can get into the database after REDO), but >>> that is a minor adjustment to above principal. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message >>> news:etEQylW9JHA.1380@TK2MSFTNGP02.phx.gbl... >>>>I don't know if I have understood this correctly, but I have assumed >>>>that when a power failure occurs and SQL Server restarts, it replays the >>>>whole log file. In that case the recovery time it would depend on how >>>>large the log is, which in turn depends on how often the backup job is >>>>run. >>>> >>>> Are You saying that it only replays the part of the log file that has >>>> been committed but not flushed? >>>> >>>> Regards; >>>> /jb >>>> >>>> "Linchi Shea" <LinchiS***@discussions.microsoft.com> skrev i >>>> meddelandet news:99B0A912-D45C-4CF1-85CA-A57BE9695860@microsoft.com... >>>>>> What startup times would I be looking at after say a power loss? Any >>>>>> other >>>>>> issues? >>>>> >>>>> It depends on how much rollback and/or roll forward SQL Server has to >>>>> perform in each database, and that in turn depends on how much change >>>>> has not >>>>> been committed to the transaction logs and/or not flushed to data >>>>> files on >>>>> power loss. If you have many databases, it would take longer for SQL >>>>> Server >>>>> to get to some databases. >>>>> >>>>> Linchi >>>>> >>>>> "Jonny Bergdahl" wrote: >>>>> >>>>>> > Yes, that is a much different animal (Geoff has some very rigid >>>>>> > opinions >>>>>> > about that). I was just addressing from the SSMS side of things. >>>>>> >>>>>> What startup times would I be looking at after say a power loss? Any >>>>>> other >>>>>> issues? >>>>>> >>>>>> Regards; >>>>>> /jb >>>>>> >>>>>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev i >>>>>> meddelandet news:C6669E1F.2E948%ten.xoc@dnartreb.noraa... >>>>>> > Yes, that is a much different animal (Geoff has some very rigid >>>>>> > opinions >>>>>> > about that). I was just addressing from the SSMS side of things. >>>>>> > >>>>>> > >>>>>> > >>>>>> > On 6/23/09 2:34 PM, in article >>>>>> > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi Shea" >>>>>> > <LinchiS***@discussions.microsoft.com> wrote: >>>>>> > >>>>>> >> Yeah, i tried 2,000 databases, and Management Studio appears to >>>>>> >> handle >>>>>> >> them >>>>>> >> fine. I'm not sure if there is any implication on the startup >>>>>> >> process >>>>>> >> when >>>>>> >> SQL Server has to go through them to analyze, redo/undo, etc. >>>>>> >> >>>>>> >> Linchi >>>>>> >> >>>>>> >> "Aaron Bertrand [SQL Server MVP]" wrote: >>>>>> >> >>>>>> >>>> I created 300 hundred databases on a test machine, and >>>>>> >>>> Management >>>>>> >>>> Studio >>>>>> >>>> appears to be handling them fine. I didn't do a thorough check >>>>>> >>>> though. >>>>>> >>> >>>>>> >>> I have a system with over 500 and am accessing pretty much >>>>>> >>> verything - >>>>>> >>> remotely, no less - via Object Explorer, with no noticeable >>>>>> >>> effects. >>>>>> >>> >>>>>> >>> >>>>>> > >>>>>> >>>>>> >>>> >>> >> > Yes, REDO shouldn't take more than one minute. Then we have UNDO, of
course and it takes as long as it need to take. And you are right that in vast majority of startups, it takes drastically shorter time since a checkpoint is performed for you when you shutdown SQL Server. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message news:e7IpkaZ9JHA.5704@TK2MSFTNGP03.phx.gbl... > Ah! This means that (if the load is not too high to have stopped the > checkpoint from happening) it will have to replay the log file for > at most one minute? That would mean that very few of the databases > actually would have to replay the log. > > Regards; > /jb > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> > skrev i meddelandet news:OdalgfX9JHA.1336@TK2MSFTNGP05.phx.gbl... >> No, not at commit time, that would be too costly. SQL Server's goal >> is for the REDO phase to not take longer than 1 minute - that is >> what controls how often the checkpoint occurs. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message >> news:eGn7tYX9JHA.200@TK2MSFTNGP05.phx.gbl... >>> Ok, that is interesting. So the question is when the checkpoint >>> occurs? Is that whenever a transaction has successfully been >>> commited? >>> >>> Regards; >>> /jb >>> >>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> >>> skrev i meddelandet news:eKZiPGX9JHA.4948@TK2MSFTNGP04.phx.gbl... >>>> >I don't know if I have understood this correctly, but I have >>>> >assumed that when a power failure occurs and SQL Server >>>> >restarts, it replays the whole log file. >>>> >>>> No, it will go to where the most recent checkpoint occurred and >>>> then first REDO from there and lastly possibly UNDO open >>>> transactions. >>>> >>>> In 2005, we have fast recovery, where SQL Server can possibly >>>> start before checkpoint, so it during REDO can lock pages which >>>> then will be rolled back (so if we have EE can get into the >>>> database after REDO), but that is a minor adjustment to above >>>> principal. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://sqlblog.com/blogs/tibor_karaszi >>>> >>>> >>>> "Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in >>>> message news:etEQylW9JHA.1380@TK2MSFTNGP02.phx.gbl... >>>>>I don't know if I have understood this correctly, but I have >>>>>assumed that when a power failure occurs and SQL Server restarts, >>>>>it replays the whole log file. In that case the recovery time it >>>>>would depend on how large the log is, which in turn depends on >>>>>how often the backup job is run. >>>>> >>>>> Are You saying that it only replays the part of the log file >>>>> that has been committed but not flushed? >>>>> >>>>> Regards; >>>>> /jb >>>>> >>>>> "Linchi Shea" <LinchiS***@discussions.microsoft.com> skrev i >>>>> meddelandet >>>>> news:99B0A912-D45C-4CF1-85CA-A57BE9695860@microsoft.com... >>>>>>> What startup times would I be looking at after say a power >>>>>>> loss? Any other >>>>>>> issues? >>>>>> >>>>>> It depends on how much rollback and/or roll forward SQL Server >>>>>> has to >>>>>> perform in each database, and that in turn depends on how much >>>>>> change has not >>>>>> been committed to the transaction logs and/or not flushed to >>>>>> data files on >>>>>> power loss. If you have many databases, it would take longer >>>>>> for SQL Server >>>>>> to get to some databases. >>>>>> >>>>>> Linchi >>>>>> >>>>>> "Jonny Bergdahl" wrote: >>>>>> >>>>>>> > Yes, that is a much different animal (Geoff has some very >>>>>>> > rigid opinions >>>>>>> > about that). I was just addressing from the SSMS side of >>>>>>> > things. >>>>>>> >>>>>>> What startup times would I be looking at after say a power >>>>>>> loss? Any other >>>>>>> issues? >>>>>>> >>>>>>> Regards; >>>>>>> /jb >>>>>>> >>>>>>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> >>>>>>> skrev i >>>>>>> meddelandet news:C6669E1F.2E948%ten.xoc@dnartreb.noraa... >>>>>>> > Yes, that is a much different animal (Geoff has some very >>>>>>> > rigid opinions >>>>>>> > about that). I was just addressing from the SSMS side of >>>>>>> > things. >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> > On 6/23/09 2:34 PM, in article >>>>>>> > F9F2ADF8-7265-4CAD-B490-C8A50FF47***@microsoft.com, "Linchi >>>>>>> > Shea" >>>>>>> > <LinchiS***@discussions.microsoft.com> wrote: >>>>>>> > >>>>>>> >> Yeah, i tried 2,000 databases, and Management Studio >>>>>>> >> appears to handle >>>>>>> >> them >>>>>>> >> fine. I'm not sure if there is any implication on the >>>>>>> >> startup process >>>>>>> >> when >>>>>>> >> SQL Server has to go through them to analyze, redo/undo, >>>>>>> >> etc. >>>>>>> >> >>>>>>> >> Linchi >>>>>>> >> >>>>>>> >> "Aaron Bertrand [SQL Server MVP]" wrote: >>>>>>> >> >>>>>>> >>>> I created 300 hundred databases on a test machine, and >>>>>>> >>>> Management >>>>>>> >>>> Studio >>>>>>> >>>> appears to be handling them fine. I didn't do a thorough >>>>>>> >>>> check though. >>>>>>> >>> >>>>>>> >>> I have a system with over 500 and am accessing pretty much >>>>>>> >>> verything - >>>>>>> >>> remotely, no less - via Object Explorer, with no >>>>>>> >>> noticeable effects. >>>>>>> >>> >>>>>>> >>> >>>>>>> > >>>>>>> >>>>>>> >>>>> >>>> >>> >> > The only problem I have seen is that SQL Management Console seems to be a
memory hog, it runs very slow in the staging environment (a VMWare image with limited RAM. Regards; /jb Show quoteHide quote "Linchi Shea" <LinchiS***@discussions.microsoft.com> skrev i meddelandet news:D9E46943-1564-44EE-B90A-8D3954D27BAA@microsoft.com... >I created 300 hundred databases on a test machine, and Management Studio > appears to be handling them fine. I didn't do a thorough check though. > > Linchi > > "Linchi Shea" wrote: > >> > Any special reason for this, or it is just a feeling? >> > >> Just some non-scientific previous experience with various management >> tools. >> Note that many of these tools would loop through the databases to do >> things. >> If there are too many databases, that looping can take long and give the >> user >> a bad experience. >> >> If you have an environment, I'd suggest you create a few hundred dummy >> databases, and check it out yourself. Maybe, the most current tools don't >> have that issue any more. >> >> Linchi >> >> "Jonny Bergdahl" wrote: >> >> > > The approach is fine if you expect the the load on the server will >> > > max out >> > > the server resource before the number of databases becomes too large. >> > >> > Question is what number of databases is "too large". >> > >> > > Personally, I'd consider hundreds of databases on a single server to >> > > be >> > > too >> > > large. >> > >> > Any special reason for this, or it is just a feeling? >> > >> > Regards; >> > /jb >> > >> > > The only problem I have seen is that SQL Management Console seems to be a Do you HAVE to use Management Studio inside the VM? With that number of> memory hog, it runs very slow in the staging environment (a VMWare image > with limited RAM. databases, or with such a limited VM, and especially with both, I would manage the instance remotely, not locally. Your VM is on the network, right? If not, can it be? If not, can't you just increase the allocated RAM? This was just a reflection of the state of the SQL Server Managment Studio
application. As with any other Microsoft product, it grows for each version to consume all available CPU and memory. :-) When opening a table with say 30.000 rows, the Management Console CPU usage goes up to 75% while the SQL Server CPU usage is like 5%. During such operations, which may takes 10-30 seconds, the web application also gets very sluggish as the SQL Service is unable to get as much CPU as it needs. We have no LAN access as the application runs in a hosted environment. But as I said, this is in the staging environment running in a VM model Small (1 CPU, 512 Mb RAM), so it not an actual problem. Regards; /jb Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev i meddelandet news:C667A00A.1DB9F%ten.xoc@dnartreb.noraa... >> The only problem I have seen is that SQL Management Console seems to be a >> memory hog, it runs very slow in the staging environment (a VMWare image >> with limited RAM. > > Do you HAVE to use Management Studio inside the VM? With that number of > databases, or with such a limited VM, and especially with both, I would > manage the instance remotely, not locally. Your VM is on the network, > right? If not, can it be? If not, can't you just increase the allocated > RAM? > > When opening a table with say 30.000 rows, the Management Console CPU usage Patient: "It hurts when I do this."> goes up to 75% while the SQL Server CPU usage is like 5%. Doctor: "Don't do that." Why would you need to use open table and return 30,000 rows? Are you really going to look at all 30,000 rows? I strongly suggest using a more judicious where clause or inspecting counts instead of the actual data, but most importantly, not using the "open table" feature at all. > Why would you need to use open table and return 30,000 rows? Are you Because I am lazy and can't be bothered to type a SELECT clause? :-)> really Basically it is because there is no "Display first 100 rows" or something like that in the context menu. I find the available option "Open table" quite stupid, it would make far more sense if the grid control was actually paged so it just read the relevant data into the display. But no, instead it is designed to be a memory hog. Regards; /jb > Basically it is because there is no "Display first 100 rows" or something Then maybe you should install the 2008 tools, where "Open Table" no longer> like that in the context menu. exists, and instead you can say "SELECT TOP n" or "Edit Top n" ... Both n's are configurable in the options. The SELECT just generates a select query in a new query window and executes it, while the Edit gives you the grid you are using now (but with a limited number of rows). Unfortunately there is no way initially to dictate what the TOP means (e.g. there is no ORDER BY). They fixed this precisely because of the problems with open table. Of course there was a lot of pushback because people like you are lazy and, to your credit, most don't have any concept of how much toll it takes on both ends, or the guts to complain about it. If you stick with the 2005 tools, then you could of course right-click the table and say Script table as > SELECT > to new query window. Then you can add a TOP and ORDER BY to the output and hit F5. A couple more clicks but definitely much more reliable and less resource intensive. A > Patient: "It hurts when I do this." Patient: Okay, doctor, you win! From now on, I'll use SELECT TOP 100.> > Doctor: "Don't do that." Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > > When opening a table with say 30.000 rows, the Management Console CPU usage > > goes up to 75% while the SQL Server CPU usage is like 5%. > > Patient: "It hurts when I do this." > > Doctor: "Don't do that." > > Why would you need to use open table and return 30,000 rows? Are you really > going to look at all 30,000 rows? I strongly suggest using a more judicious > where clause or inspecting counts instead of the actual data, but most > importantly, not using the "open table" feature at all. > > Hi Jonny
Thank you for the reply. accoring to your description, I understand that when there are a lot of database running in the SQL Server instance, you meet a performance problem (run slowly) with the SQL Management Console. if i misunderstand anything, please let me know. in order to help you to resolve the issue, I need to better understand the issue. therfore, please help to confirm the following 1 based on your description, the sql management console is running on a virtual server. is it correct? if so, I would like to know if the virtial server is biuld by Microsoft product? 2 as you say SQL Management Console, do you mean SQL Server Management Studio? if not, please tell me which tool you use when the issue happens 3 please describe the symptom in detail. For example , what action you are doing when the issue happens? does the issue always happens? Best regards, Mark Han Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msd***@microsoft.com. ========================================================= This posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= 1. It is a VMWare Server VM, one CPU, 512 Mbyte RAM.
2. Yes. 3. I select Open on a large table, then the CPU goes up to 100% and it takes a 10-30 seconds to get the result set. During this time the Managment Studio uses 75% of the CPU. Regards; /jb Show quoteHide quote "Mark Han[MSFT]" <v-fat***@online.microsoft.com> skrev i meddelandet news:KrbMrNU9JHA.5192@TK2MSFTNGHUB02.phx.gbl... > Hi Jonny > > Thank you for the reply. > > accoring to your description, I understand that when there are a lot of > database running in the SQL Server instance, you meet a performance > problem > (run slowly) with the SQL Management Console. if i misunderstand anything, > please let me know. > > in order to help you to resolve the issue, I need to better understand the > issue. therfore, please help to confirm the following > 1 based on your description, the sql management console is running on a > virtual server. is it correct? if so, I would like to know if the virtial > server is biuld by Microsoft product? > > 2 as you say SQL Management Console, do you mean SQL Server Management > Studio? if not, please tell me which tool you use when the issue happens > > 3 please describe the symptom in detail. For example , what action you are > doing when the issue happens? does the issue always happens? > > Best regards, > Mark Han > Microsoft Online Community Support > ========================================================= > Delighting our customers is our #1 priority. We welcome your > comments and suggestions about how we can improve the > support we provide to you. Please feel free to let my manager > know what you think of the level of service provided. You can > send feedback directly to my manager at: msd***@microsoft.com. > ========================================================= > This posting is provided "AS IS" with no warranties, and confers no > rights. > ========================================================= > Hi Jonny
Thank you for the reply. according to your description, i understand that when you use SSMS to manually open the table, the result will be returned in 10-30 seconds and the SSMS will take 75% CPU. Based on my experience, we need to troubleshooting the issue from 2 parts. 1 to check if the issue is related to the SSMS client Tool 2 to analysis the system resouce to see if there are some Bottlenecks when the issue happens. Based on the above, we need to the following information to further diagnose the issue. Therefore, please send me an email so that I can create a workspace for you to upload the requested file. 1 the backup file of the database. the version of the SQL Server and the SSMS. i would like to reproduce the issue. 2 the performance log. I would like to analysis the performance log to check bottlenecks. Besides, to further narrow down the issue, please do the test. test: run the T-SQL, select command to return the data from the table to see if it will take 10-30 seconds to return the result. note, please tell me the time when we use T-SQL select command to get the data. if you have any questions on the above, please let me know. Best regards, Mark Han Microsoft Online Community Support Get Secure! - www.microsoft.com/security ==================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ==================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ==================================================== Jonny;
Back to Aaron's comment on using SSMS in a VM, you should really just leave the server do what it is supposed to do, that is to service the client requests, and run client tools on your workstation (of course unless that VM is also your workstation). Client tools typically don't scale well with a large volume of data because that's not what they are deigned for and that is not how they should be used (i.e. to consume very large memory data structures). If you run these client tools on your own workstation, at the worst you end up hurting yourself and at the worst you can always reboot your workstation without anybody else screaming at you. Linchi Show quoteHide quote "Jonny Bergdahl" wrote: > The only problem I have seen is that SQL Management Console seems to be a > memory hog, it runs very slow in the staging environment (a VMWare image > with limited RAM. > > Regards; > /jb > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> skrev i meddelandet > news:D9E46943-1564-44EE-B90A-8D3954D27BAA@microsoft.com... > >I created 300 hundred databases on a test machine, and Management Studio > > appears to be handling them fine. I didn't do a thorough check though. > > > > Linchi > > > > "Linchi Shea" wrote: > > > >> > Any special reason for this, or it is just a feeling? > >> > > >> Just some non-scientific previous experience with various management > >> tools. > >> Note that many of these tools would loop through the databases to do > >> things. > >> If there are too many databases, that looping can take long and give the > >> user > >> a bad experience. > >> > >> If you have an environment, I'd suggest you create a few hundred dummy > >> databases, and check it out yourself. Maybe, the most current tools don't > >> have that issue any more. > >> > >> Linchi > >> > >> "Jonny Bergdahl" wrote: > >> > >> > > The approach is fine if you expect the the load on the server will > >> > > max out > >> > > the server resource before the number of databases becomes too large. > >> > > >> > Question is what number of databases is "too large". > >> > > >> > > Personally, I'd consider hundreds of databases on a single server to > >> > > be > >> > > too > >> > > large. > >> > > >> > Any special reason for this, or it is just a feeling? > >> > > >> > Regards; > >> > /jb > >> > > >> > > > Hello Jonny,
Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han. I am glad to work with you on this issue. From your description, I understand that you would like to know if there are some performance issues when we use a large number of databases. If I have misunderstood, please let me know. Based on my experience, the concern is related to the scenario. To get a idea about the number of the databases which might case the heavy load for the server resource, it is suggested to do tests to monitor the performance by perfornamce log to see the changing of the perfornace of the server. about the performance log, there are some related articles to share with you: Performance Counters:http://msdn.microsoft.com/en-us/library/aa373083(VS.85).aspx How to: Create a Performance Log:http://msdn.microsoft.com/en-us/library/ms172556(SQL.90).aspx Besides, if you meet a detail performance issue, we welcome you to post it here. if you have any questions or concerns on the above, please let me know. I look forward to hearing from you. Best regards, Mark Han Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msd***@microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 2 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx ============================================================ This posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= > From your description, I understand that you would like to know if there Yes, and what I am looking for is any experiences of issues like caching of > are some performance issues when we use a large number of databases. result sets, optimizations, etc, things that are not part of the documentation. I know this setup is somewhat unusual, that is why I ask in the first place. The documentation simply states that the maximum number of databases is 32768, but it says nothing about the performance impact that number of databases would impose on a system. I would think this would have a rather large impact on the internal function of SQL Server, as the available memory needs to be divided to serve the numerous different databases with caches, statistics and query optimizations. Regards; /jb
Show quote
Hide quote
"Jonny Bergdahl" <jonnybergdahl@newsgroup.nospam> wrote in message Hinews:OulDgr98JHA.1252@TK2MSFTNGP04.phx.gbl... >I have developed a system that potentially will be run by thousands of >customers. In order to make it easy to partition the system on multiple >servers, I have opted to use a separate database for each customer. That >way, as the number of customers grow, I can easily add SQL servers to >divide the load. > > The number of transactions for each database is low, typically we have 3 > transactions/hour with a typical peak usage of 120 transactions/hour. > > Are there any special issues in such a setup? What is the performance > issues of using a large number of databases? How many databases will I be > able to handle on each server? > > Regards; > /jb You are allowed 32,767 databases per instance see http://msdn.microsoft.com/en-us/library/ms143432.aspx. With your configuration it will be easy to move databases to other instances/servers if you do hit performance issues. John  
Other interesting topics
Can I script out SQL Server jobs programmatically?
Sql transaction log size because of reindexing User login date change date from Mon dd yyyy Backup and restore sql server 2005 Identifying Memory Pressures Jobs cannot running understanding metrics/performance SQL Server 2005 Restore DB Name **INCOMPLETE** Replication syncobj views |
|||||||||||||||||||||||
