| Author |
Topic |
|
John_Idol
Starting Member
39 Posts |
 Posted - 2007-04-24 : 05:43:37
|
| Hi All,since the last upgrade SQL 2000 --> 2005, the biggest database I am working on (1.4 GB, with loads of binary data) keeps freezin at least one time per week. When I say 'freezin' I mean that you cannot query some of the tables as it gives a timeout error when trying.I am not a DBA and I don't have any idea of what to do, the only thing which comes to my mind is that this database is too big, as if I go to the preperties window it says Space Available: 38 MB (what's that? there's really only 38 MB on this DB?).I tried re-writing all mainteinment plans (full backup every night, log backup every hour), but I realized that the database keeps freezing (and the backup jobs keep running untill you stop them).anyone could give any hint?Thanks in advance,Giovanni Idili |
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2007-04-24 : 09:29:31
|
| How are you querying the tables ? From a web page, from Analyser, stored procedure ? |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-24 : 09:33:06
|
| Chech with 'sp_who2 active' to see if have blocking issue. |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-04-24 : 09:52:01
|
| All,thanks for answering.Pinto, I am accessing the tables only from stored procedures.rmiao,am I supposed to run the procedure while experiencing the issue or at anytime?Thanks!G |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-24 : 10:13:07
|
| When you experience the issue. |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-04-24 : 10:16:42
|
| OK, thanks.What should I expect to see? I mean, I do I understand what's blocking what?I am not very familiar with this procedure and if I will have to wait untill the issue presnts itself again I'd want to be sure I am able to catch the problem.Cheers!G |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2007-04-24 : 10:21:05
|
| I had problems timing out when I ran stored procedures from an asp.net application. It was getting data from a SQL database and I had to amend settings in webconfig. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-24 : 10:31:26
|
| Just look at blkby column in the result. If has some number in that column, the process is blocked by that spid. |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-04-24 : 10:35:02
|
| Pinto,I am running stored procedure from an ASP.NET application as well (SQL2005), but if I restart the SQL Server everything's fine, so I dunno if it' related to web.config settings. Do you think so in this case?rmiao,OK, many thanks for that!Cheers,G |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-24 : 11:31:24
|
| "since the last upgrade SQL 2000 --> 2005"Check that the Database Compatibility has changed from SQL2000 to SQL2005 (it will be SQL2000 by default). There are performance issues running SQL2000 compatibility on SQL2005, HOWEVER you may have new bugs running in SQL2005 compatibility, so you will need to test that all your code is OK for SQL 2005!Make sure that all indexes have been rebuilt and the statistics updated since the upgrade. If you have a scheduled job to do this I guess wit will have run since you moved, even if you didn't update Stats. when you moved."but if I restart the SQL Server every thing's fine"Sounds like clearing cache is sorting it out then, or its now maxing out on memory where maybe it wasn't before.One other thing to watch out for is if it is extending the database by large amounts. This is supposed to be better under SQL 2005, but under SQL 2000 it could lock up a database for ages. If your database is set to 10% growth, and the database is now many-GB big, then 10% could be a large extension to do all at one time. Consider changing to a fixed about, e.g. 100MB."what's that? there's really only 38 MB on this DB?)."Check if it is set to auto-extend, if so then that's the amount of free space until it next has to auto-extend.Kristen |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2007-04-24 : 11:50:09
|
| Have a look at these two sites.http://www.powupload.com/System.Web.HttpException-Request-timed-out.aspxhttp://weblogs.asp.net/skoganti/archive/2004/02/22/78124.aspxWhat was happening with me was at remote sites it was taking too long to process the request to the server and it was that causing the app to timeout. Increased the timeout in the connection string in the webconfig which controls thre app if there is no activity. Also, I increased the maxRequestLength="4096" to a larger figure as this is the size of the amount of data being returned.<httpRuntime executionTimeout="90" maxRequestLength="4096" useFullyQualifiedRedirectUrl="false" minFreeThreads="8" minLocalRequestFreeThreads="4" appRequestQueueLimit="100"/> |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-04-24 : 12:13:33
|
| Kristen,many thanks for helping.Compatibility was set to 2000. I'll CROSS MY FINGERS and try to switch it to 2005.There's an autogrowth of 1 MB, unrestricted: d'you think It should be increased?It seems to be too few, yes, but I don't understand why before it was working fine.Pinto,thanks, I am having a look at the links.Though, when the database stops I cannot even open the tables from SQL Server Management Studio.Cheers,G |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-04-24 : 12:18:30
|
| About this:"Make sure that all indexes have been rebuilt and the statistics updated since the upgrade. If you have a scheduled job to do this I guess wit will have run since you moved, even if you didn't update Stats. when you moved."When I took over (new job as Software Developer - not as DBA!) the upgrade was already done, but I don't think no one did nothing at all about rebuilding indexes o stats (definitely there's noa job for that).Problem is I don't know how to. Any good tutorial?Thanks,G |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-04-24 : 12:27:46
|
| Again,if I switch compatibility to 2005, should I do it only for that DB (the only one which is throwing issues) or for all of them?Cheers,G |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-24 : 12:37:28
|
| I'll switch alldbs to 2005 if app uses them is ready. You can set a db maintenance plan to rebuild indexes and update statistics. |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-04-24 : 12:48:28
|
| What do you mean for 'if app uses them is ready'? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-24 : 12:56:40
|
| Have to ensure app uses those dbs supports sql2k5 before you switching db to sql2k5 compatibility level. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-24 : 13:09:53
|
| Changing the database compatibility level will not increase performance. It allows you to use features for the 90 version instead of 80. I'd recommend switching it after you've tested compatibility level 90 in a test environment. Do not make the switch in production without testing. Kristen, do you have a link that says otherwise (re the performance of 80 in a 90 installation)?Also, increasing your timeout value in your web.config or connection string is not a solution.Run SQL Profiler for a few hours during the busy times of the day. Trace the data to a table. Then see what the worst performing queries are with this:SELECT TOP 20 Duration, TextDataFROM YourTraceTableWHERE TextData IS NOT NULLORDER BY Duration DESCPost the queries here so that we can help you optimize them.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-24 : 15:14:32
|
"I'll CROSS MY FINGERS and try to switch it to 2005"I couldn't POSSIBLY comment  . But ... that's exactly what I would do !!!!!!!!!!"Changing the database compatibility level will not increase performance"Tara, I have read (sorry, no links to hand) that SQL 2005 in "SQL 2000 compatibility mode" runs slower than native SQL 2005 compatibility mode. I have no proof of this, however, sorry."There's an autogrowth of 1 MB, unrestricted"Again, going with "educated guess", this is Bad! I reckon 100MB growth is "Safe" (there is an issue that on a big database 100MB is "small" and thus going to lead to fragmentation, but 1MB is disastrously-small, by comparison. I recommend jacking this up to, say, 100MB.Kristen |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-04-24 : 17:00:44
|
| I switched it (let's say I did it in a test environment...) and it's working fine (I had to change just a few stored procedures).About autogrowth... I didn't want too be rude (Kristen you got it right) so I pushed it to 10MB, but sure as hell as soon as I get to put my hands on the guilty DB (let's say tomorrow) I'll pump it to 100MB - no matter what.Thanks again... I am learning a lot of useful stuff here!I'll let you know how it goes.G |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-25 : 04:03:52
|
| "let's say I did it in a test environment...) "I ain't going to tell anyone!"so I pushed it to 10MB"10MB is going to fragment the database - lots of little extensions (well, assuming that it does need to extend periodically). 100MB would be better I think, but again, if it is extending several times a day using a large block-size would be better.I thing I investigated was doing a "big" extension during the quiet hours, overnight. However, I never finished that project - I seem to remember I was finding it too difficult ti either find out how close the DB was to needing another extension, or forcing an extension of a defined-size.IF extending the DB is killing performance during working-hours that route might help though.Assuming your database files are physically fragments [from lots of small-ish extensions] next time you have some scheduled downtime you might like to de-frag the file.I use CONTIG.EXE from Sysinternals for that job - SQL Service needs to be stopped at the time [I believe]Kristen |
|
|
|
Next Page
|
Database keeps giving timeout error