what do you mean regards to statistics? We are looking at the paging file this morning which is currently on the c drive which may not be big enough - we split across 2 other drives to see if this helps.
We have rebooted this morning so documenting performance via perfmon and profiler
the box has max 12gbRAM - we are only using 8gb - put are planning on upgrading to a more powerful box - also need to review the RAID set-up and this may not be correct either.
the main problem at moment must be around memory/paging
1) If you have to ask about statistics you probably ought to get a professional consultant onsite to help solve this problem and mentor you on other topics. Adding in data frequently (but < 20% of the table row count) can get you very poor query plans because autoupdate stats hasn't fired but needs to to 'take into account' newly added data.
QUERIES ARE NOT THE PROBLEM HERE BUT LOADING DATA I.E. BULK LOAD SO THAT WHY i WASN'T SURE WHAT U MEANT BY STATISTICS I.E. WERE YOU TALKING ABOUT INFROMATION GATHERED BY PERFMON ETC
2) what is your max sql memory setting? I didn't understand your statements about ram. I would like to see something like "our server has 8GB of ram, sql server is limited to 5.5GB ram max"
PHYSICAL RAM SUPPORTED BY THE OS
3) page fault delta in task manager is a quickie you can check for paging problems
PAGE FILE IS ON THE C DRIVE WHERE WE ARE EXPERIENCING DISK RETENTION - WE HAVE MOVED/SPLIT TO 2 DIFFERENT DRIVES WHICH CONTAIN MORE SPACE
4) search web for documentation on DBCC memorystatus and see if anything there pops out as problematic
LOADS MUCH QUCKER AFTER REBOOT - THIS UNSURE OF ROOT CAUSE WHETHER ITS MEMORY/PAGING ISSUE - ALOT OF INDEXING IS DONE WHICH COULD BE COMSUMING ALOT OF MEMORY - BUT WHAT IS GETTING FILLED UP AND AFTER REBOOT IS CLEARED DOWN
A) virtually every 'data loading' mechanism I have ever developed or come across does more than simply insert data straight into a table. Most have other stuff that is done such as populate subordinate tables, etc, and it is that other stuff that can benefit from up-to-date statistics. If you are truly simply bulk inserting data then stats isn't the problem (unless poor read queries that access the data are causing resource starvation).
B) I note that you still have not stated what the sql server configuration setting is for max server memory (MB). That is a critical piece of information here.
C) what did you notice when you used task manager and checked out page fault delta? what/which services were paging heavily?
D) Have you checked for blocking problems?
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service