March 4, 2004 at 3:18 am
hi there...I have a similar problem to another post - wasnt sure if I should post this as a reply...
We have a server with 795 MB of RAM. We have 5 databases on the server. The one used the most is 1.27 Gb,and is by far the largest. The other dbs are about half the size or less.
We have a strange set-up as our database is hosted by an external company in Visual FoxPro and they just send us a copy once a week of the entire database, which we then put on the same server for 7 users to access. As this is useless for reporting we import the database into Sql server. Because I have no way of knowing what data has been altered, I have set up a DTS to truncate all the tables and re-import them every week. We can then do pretty simple reporting on the tables (the largest table has just reached 1,000,000 rows, but the others have 250,000 or less...) I haven't put any indexes on the tables as the query time was quick, and as I was replacing all the data every week I didn't think there was much point.. This set-up has been wroking for the past year now...
The queries we were runnning worked fine until yesterday morning, when all of a sudden there was an immediate performance hit in how long it took queries to run. Queries on smaller tables haven't been affected, but a query that used to take less than 5 seconds on Tuesday, (an aggregate query, with a join on 2 tables), I was having to cancel after 5 minutes....Even just a simple select from a table with 238,000 rows is now taking 10 seconds or more....
I was wondering how I go about figuring out what the problem is, as it has not been a slow degradation in performance, it has been sudden. Could it be because I am truncating the tables every week and re-importing them? There are only 2 users, and there is no blocking going on...
I don't think there is a network problem as running queries on the actual server is slow as well, and we pulled it off the network and the queries are still really slow. Sql server is grabbing a lot of memory, but it always does doesn't it?
I have been using performance monitor to see what is happening, but I am a novice on how to read it... Activity goes mad though when I run a query...Bytes Total/sec and % Disk Time and Pages/Sec are oscillating wildly - is this normal????
Our network manager wants to just wipe the server and re-install it, but I would rather find out what the problem is.....SQL server is the only service running on the machine other than our FoxPro standalone database app which has about 7 users...There is no issue with disk space either....
Any help much appreciated....
March 4, 2004 at 5:47 am
Most likely it uses table scan instead of indexes, that explains large tables are most effected.
Do a reindex may help.
DBCC DBReindex or DBCCIndexDefrag
March 4, 2004 at 6:42 am
None of the tables have any indexes on them as they are not that big....The queries were running fine until yesterday morning. I am a novice so am not really savvy, but would have expected a gradual slowing of query speed, not a sudden one......
It took 28 minutes today to run a query that on Tuesday took 10 seconds...
March 4, 2004 at 7:01 am
Performance can change dramaticly once a threshold has been reached.
If restart sql server help, it may relate to log file or memory leaking etc.
Also check log file growing limitation.
March 4, 2004 at 9:41 am
If statistics don't get periodically updated, then changes to tables size that may affect access plans aren't taken into account. Try running sp_updatestats.
No indexes doesn't sound good. Your right though, it does depend on table size but still it does sound odd.
Francis
March 5, 2004 at 12:17 am
Hello,
can you see the log file size?? To see log file size, you need to find the Data folder of SQL server where in typical log file name is databaseName_Log.ldf. If this file size is very large then too you may start getting problem of performance. If this is the csae then you have to deatch the database delete the log file and attach database again.
Regards,
MaheshB
March 5, 2004 at 2:06 am
Hi there,
I've just come off a project that experienced a sudden degradation in performance. It was like turning off a tap. We found that restarting the server did solve the problem for a short while, then suddenly ... bang!! Perfoamce slowed to a crawl.
The difference was that we had around 120 users, but nonetheless, the server was more than approrpaite for the load.
After consulting with MS on this, they released a patch to us which corrects a memory leak in SQL Server. Once we applied the patch, the problem disappeared. The product was SQL Server 2000 Enterprise SP3a.
If you suspect this might be your problem, you might want to talk to MS.
Regards,
Morpheus
"See how deep the rabbit hole goes..."
March 5, 2004 at 3:33 am
Hia All,
This is driving me mad.... I moved the database to a different server to see if it was still as slow, and it was.... As this database is a read-only and only used for reporting, we tried restoring a version from January when we know that everything was running ok, and it is STILL really really slow.I put an index on one table, and doing a simple select * from is taking 18 seconds, there are only 250,000 rows in this table....
Restarting the server makes no difference, and restarting SQL server makes no difference -there is nothing I can see obviously wrong with the server... I really don't know what to do... I did a defrag of the server and it said it couldn't defragment one of the backup files, but everything else was fine...
I just dont know whether it is a problem with the database, sql server or the server and don't know where to start looking....I would have understood a gradual degradation in performance, but not a sudden one... Nothing changed, and it was like a tap turned off as Morpheus said....
March 5, 2004 at 4:24 am
Here are something to check with.
Does local select makes difference? if fast, then check network.
Check query execution plan on select, setup index to see the effect. It should unless the table is small.
Check CPU utilization and memory allocation.
March 5, 2004 at 6:54 am
I think we are going to rebuild the server as am really stumped about what is the problem...I recreated the tables and imported the data onto my computer (without indexes), and a query that takes 28 minutes to run on the server (local and across network) took 1 minute 19 seconds first run through, then19 seconds, then 2... I think there must be a problem with the server, though I am unable to figure out what. ...Thanks for your help...
January 12, 2005 at 3:49 pm
Besides applying latest patches, just do what was already adviced, just basic things:
1. Create clustered index on each table and WITH FILLFACTOR = 95 ON [COREINDEX] - for read
2. After loading data update statistics
3. Run your database maintenance plan for all databases including master database.
3. Truncate transaction log
4. Drop and recreate indexes with dbcc commands on a regular bases.
5. Check if your database is not too big for too less data, shrink it.
5.Check if objects have permissions for users, check anyway.
6. May be there is a need to change char datatype to varchar
7. Look into code to modify:
Eliminate temporary tables if have and replace with table variables
use union instead of OR, list items in "IN" clouse in the order most often used, not less one
Eliminate NULLs, use isnull to set value to 0 in cases where you do agregation and join.
Isabella
January 14, 2005 at 9:47 am
Not sure if you are having the exact symptoms that I am having, but here is a link to my post:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=154092#bm154306
I have a case open with MS. Basically they are telling me that it is OK that the servers (not just this server) use twice the resources when I apply indexes that should give huge improvements in performance. (One thing that MS and I do agree on.) My server runs at 6% the day after a reboot regardless of any day of the year without the indexes. The next day it runs 40%. When I applied the indexes, it runs at 12%?!?!
I'll post updates as I get more information.
JL
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply