You were all so helpful last time that I thought I would try again with a conundrum I am having:
•SQLA v10.0.1.3831 •Win Server 2008 64bit •DB size 6.5GB •12 GB RAM (max cache allocated 5GB) •4 x quad core •Approx 50 users
The database was migrated to this server from a much lower spec Server 2003R2 machine a few months ago. We had some initial speed problems that Breck Carter tracked down to Intra Query Parallelism which we have turned off and things improved dramatically.
During normal usage the server is dramatically faster than the old 2003 machine, (noticeably when opening windows etc.), however when it is being heavily used by reports there is a huge slowdown such that just opening a customer record can take 30 seconds as opposed to less than 1 second.
I hear you saying "well of course it would be", but we have started up the old server today and taken the current database doing the same tests. Yes opening the customer slows down on the old server as well but we are talking about taking 4 seconds as opposed to 2 seconds. All other processes are affected in the same way i.e. a factor of 20-30 times slower on the new box whereas it was 2-3 times slower on the old box.
We changed the server switches on the new box to take advantage of the increase in resources available and also added in some logging, (this might slow down but presumably not nearly the factors we are talking about). Details below:
//Old Box -x TCPIP(ServerPort=49153) -gp 4096 -ch 2024M -n XXXXSQL "D:\Database\Live Database\spaceman.db"
//New Box -n BLUESQL -x TCPIP(ServerPort=49153) -gp 4096 -c 5G -ca 0 -gb high -z -zr ALL -zo c:\MensajesDeDBLog\ZLog.txt -zs 400M -o c:\MensajesDeDBLog\XXXSQL.text -os 400M -n BLUETESTSQL "D:\Database\Live Database\spaceman.db" -n SpaceMan -n SpaceMan
It probably isn't a lot to go on and I understand that loading a server with heavy reports is going to slow down progress I just need to try and work out how/if I can bring it back to be more in line with the old server where performance was not radically affected.
asked 02 May '14, 07:20
Thanks all for your responses. The Request Logging was the killer so well done to Dmitri and Mikel.
We had a chance to stop the service again the other day and put back all setting except the logging and everything was still fine, (even better actually due to the 5G cache).
We will still need the logging on the live database at some point, (it was Sybase who asked us for these logs), to pinpoint a problem they are having with database disconnections but we will only use it for short periods not leave it on.
Thanks again everyone.
answered 21 May '14, 08:03