 |
 | 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 2008Database growing, performance degrading...
My program is using a SQL Server Express 2005 database. The program runs fine when you first start it, but over time the performance of the the database degrades and my program takes longer and longer to get responses from the database. Rebooting the database server solves this problem. I also notice that when the speed is becoming an issue, the file size of the database gets to over a gig, but when I reboot, the file size goes back down to 100MB. Does anyone know what could be happening and some possible fixes for the performance issues I'm having? Is the file size growing this much normal? Why is the performance degrading so much? I don't exactly know... but see if the following questions help you figure it out. Are you referring to log file? Is the DB in Simple model? Do you have any long running transactions or any old open transactions? > My program is using a SQL Server Express 2005 database. The program > runs fine when you first start it, but over time the performance of [quoted text clipped - 6 lines] > having? Is the file size growing this much normal? Why is the > performance degrading so much? It's the database file, not the log. How do I find the answers to the other questions and how would they affect performance? If I had any open transactions, wouldn't it lock up the database? > I don't exactly know... but see if the following questions help you figure it > out. [quoted text clipped - 14 lines] > > having? Is the file size growing this much normal? Why is the > > performance degrading so much? A database doesn't decrease in size unless you shrink it. In general shrinking databases is a bad idea unless except in the rare instance where a database has grown much larger than it needs to be now or in the reasonably forseeable future. There are two ways to shrink databases - manually and by using the autoshrink option. Don't use the autoshink option (see http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx) and be careful about when you use the manual shrinking (see http://www.karaszi.com/SQLServer/info_dont_shrink.asp). My guess is that you have both autoshrink and autogrow turned on. And you have autogrow set to grow by a percentage. So as your application starts running and the database is 100MB and it needs more space it grows by the autogrow percentage (maybe 10%), so it gets and initializes 10MB more. Your database essentially stops while it is getting this space (and that can take a while unless you are using instant initialization - see http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyA ndHow.aspx). But when your database has grown to 1GB and needs more space, 10% growth is now 100MB, so the initialization takes 10 times as long. If this is your problem, you will want to turn autoshrink off, set autogrow to a reasonable number of MB rather than a percentage, make your database as large as you expect it to need to be plus some additional space and consider turning on instant initialization.. Tom It's the database file, not the log. How do I find the answers to the other questions and how would they affect performance? If I had any open transactions, wouldn't it lock up the database? On Jun 27, 1:51 pm, Uday <U...@discussions.microsoft.com> wrote: > I don't exactly know... but see if the following questions help you figure > it [quoted text clipped - 15 lines] > > having? Is the file size growing this much normal? Why is the > > performance degrading so much? Thanks for the tips. I used the default options that get set when you create a new database. Autoshrink was on and autogrow was set to 10%. I changed them as you suggested, so that should take care of the file size, but do you think this will really help with my performance problems too? On Jun 28, 3:06 pm, "Tom Cooper" <tomcoo...@comcast.no.spam.please.net> wrote: > A database doesn't decrease in size unless you shrink it. In general > shrinking databases is a bad idea unless except in the rare instance where a [quoted text clipped - 47 lines] > > > having? Is the file size growing this much normal? Why is the > > > performance degrading so much? Possibly it will help. Performance problems are difficult to diagnose in a newsgroup. Common other problems that can cause performance to get slower as the databse gets bigger and/or the longer it has been since the last reboot include: Using cursors and/or other looping constructs (like WHILE) in your SQL Having a suboptimal indexing strategy A memory leak in your application Tom Thanks for the tips. I used the default options that get set when you create a new database. Autoshrink was on and autogrow was set to 10%. I changed them as you suggested, so that should take care of the file size, but do you think this will really help with my performance problems too? On Jun 28, 3:06 pm, "Tom Cooper" <tomcoo...@comcast.no.spam.please.net> wrote: > A database doesn't decrease in size unless you shrink it. In general > shrinking databases is a bad idea unless except in the rare instance where [quoted text clipped - 61 lines] > > > having? Is the file size growing this much normal? Why is the > > > performance degrading so much? When I get in a situation where a client has a database that is running slow, what can I do to diagnose the problem on their computer and figure out what is causing the slow down? On Jun 30, 11:56 am, "Tom Cooper" <tomcoo...@comcast.no.spam.please.net> wrote: > Possibly it will help. Performance problems are difficult to diagnose in a > newsgroup. Common other problems that can cause performance to get slower [quoted text clipped - 84 lines] > > > > having? Is the file size growing this much normal? Why is the > > > > performance degrading so much? That's a tough one. Not surprisingly, entire books have been written about performance. One thing to check for is blocking. See http://support.microsoft.com/kb/271509 http://support.microsoft.com/kb/224453/ http://msdn.microsoft.com/en-us/library/ms191168.aspx You can use SQL Profiler to find the queries that are using lots of time and/or resources like disk I/O's or CPU time. The problem is most likely in your schema or in your database queries, but remember performance problems can also come from other processes that are going on in your server and/or network. I would recommend reading the section in BOL on Monitoring and Tuning for Performance at http://msdn.microsoft.com/en-us/library/ms189081.aspx Whatever monitoring you do (SQL Profiler, and other tools), don't just do it when you have performance problems. When performance problems show up, but earlier everything was running fine, you want to know what has changed. The best way to know what has changed is if you were also monitoring when things were going fine. IMO, the "Bible" on SQL 2005 performance is "Inside Microsoft SQL Server 2005: Query Tuning and Performance" by Kalen Delany, et al. It covers lots of things you will eventually want to know. The only caveat I would have is that book is definitely not for beginners. Tom When I get in a situation where a client has a database that is running slow, what can I do to diagnose the problem on their computer and figure out what is causing the slow down? On Jun 30, 11:56 am, "Tom Cooper" <tomcoo...@comcast.no.spam.please.net> wrote: > Possibly it will help. Performance problems are difficult to diagnose in a > newsgroup. Common other problems that can cause performance to get slower [quoted text clipped - 92 lines] > > > > having? Is the file size growing this much normal? Why is the > > > > performance degrading so much? If its mainly the .ldf (log) file that is getting bigger, you may have the database running in full recovery mode; try switching it to simple mode using the Management Studio Express (could be you have to download it if it isn't on your system already). > My program is using a SQL Server Express 2005 database. The program > runs fine when you first start it, but over time the performance of [quoted text clipped - 6 lines] > having? Is the file size growing this much normal? Why is the > performance degrading so much? |
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.