SQL Server 2005 is installed on a brand new 64-bit server (Windows 2003 x64 std. Edition, 2.4 Ghz AMD opteron- 2cpu, 8.8 Gb of RAM). There is barely few hundred rows of data scattered among few tables in one database.
SQL server and SSIS performace grossly degrades overnight and in the morning everything is slow including the clicking of tool bar selection.It takes 3 seconds to execute a simple select statement against an empty table.
It takes15-20 seconds to execute a SSIS package that normally would take 2-3 seconds.
But once SQL Server is restarted, everything returns to normal and the performance is good all day and then the next day everything is slow again.
Thank you for your help.
Major SQL Server/SSIS 2005 Performance issue
sannasum
So no backup or maintenance plans. Or any other jobs.
I the morning what processes have what memory
Fishouille
It looks like you are running the development environment on the server, as well as running SSIS on the same server as SQL Server.
Without knowing what you have setup on your server overnight I can only guess at what the situation might be.
What I guess is that you have a process that runs over night which results in SQL Server reading data, this is therefore cached. When you try and do something in the tools you have a considerable amount of swapping as the OS tries to regain memory for the tools from SQL.
If this is how you want to work then you need to limit the memory SQL Server uses, this will enable you to run the tools (which do require a fair amount of memory).
Needo
Thank you for your reply. Sorry, I wasn't explicit in describing the issue. It is indeed a development server and there isn't anything that runs on the server overnight. The box is accessed through Terminal services, so there is a live windows session which I disconnect at night and then re-connect to the same Windows session in the morning, but SQL Server is not executing anything overnight.