| Author |
Topic |
|
cornall
Posting Yak Master
148 Posts |
 Posted - 2007-06-14 : 08:20:29
|
| Hi,I am runing a sql DB around 11 gigs it had around 600 Mb free space in the data file.I was having terible peroformance of my application and the perfmon disk queue countert was running high. As a last resort I expanded the free space on the data file to 3 gig. This solved the problem perfmon counters are back to normalWhat could this be?Cheers D |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-14 : 08:27:08
|
| Sounds like a coincidence unless the data file was continually expanding - was the database growing?Maybe it just caused an aging of the query plans or recalculation of statistics.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-14 : 09:21:18
|
| It didnt seem to be growing. This problem has been ocurring for several days as soon as i expanded the file it was fine! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 09:34:41
|
| Do you have maintenance plans to rebuild indexes and statistics and the like?Have the physical files been fragmented "recently-ish"?You don't SHRINK the database at all, do you?Kristen |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-14 : 10:01:31
|
| We rebuild the indexes but not statistics?The files are baddly fragmented we are running a defrag but it is taking ages and we have to do a little each night. We don't shrink the DB. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 10:50:51
|
| "We rebuild the indexes but not statistics?"That's OK. An index REBUILD (rather than a Reorganise/Defrag) also updates the Stats.Have a look at CONTIG.EXE from Sysinternals for defragging the physical files. You will need to SQL Service STOPPED (AFAIK), but IME its pretty quick.Given that your maintenance procedures look OK I can't see anything obvious that's causing your problem, sorry.Kristen |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-14 : 10:53:40
|
| I am thinking and it is a long shot. That the partialy completed defrag had moved the free space files so they were very inefficiently placed and when i extended the databases avaliable size it reallocaed this space. (hope this makes sense)!!!D |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-14 : 10:54:38
|
| P.S. Thanks for all your help. Sorry I am not givign much back to these forums at the moment I was a JAVA and Oracle Developer till my curreent job so have just swicthed to SQL server. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 11:09:55
|
| "I am not givign much back to these forums"I give here because I take everywhere else on the internet! You can't be an expert at everything ...."That the partialy completed defrag had moved the free space files so they were very inefficiently placed and when i extended the databases avaliable size it reallocaed this space"I considered that (and it looks to me like Nigel did too). I can't see that happening.If the database is set to auto-extend it will just grab some more disk space for an Index Rebuild, or whatever.I suppose its possible that if the database is of FIXED size (and NO automatic extension) then REINDEX may use all sorts of free bits of disk space and so on. I dunno for sure.However, if you rebuild indexes and update stats and do NOT recompile anything then performance won't improve until the next SQL Service restart or similar.If performance dries up again try a SQL Service restart. If that fixes it then its clearing the cache which is causing the improvement, by forcing query plans to be recompiled.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-14 : 14:42:05
|
| How did you place db files on the server? Put everything in single disk? |
|
|
|
|
Bad server performance! Help needed