April 18, 2009 at 5:22 am
Hi,
We rebooted the box a few weeks back due to bad performance and seen a great improvement in load times 10-15hrs for a few loads - over the past weeks the performance as greatly decline and now takes up 42 hrs to load.
Has anyone experience the same problem? The spec of box is great and we looking to upgrade but I want to ensure we don't have same issue with new box. What is reboot clearing down and what is building up over time i.e. tempdb related, cache related?
Thanks
April 18, 2009 at 9:00 am
What do you mean "to load"?
Reboots sometimes clear out things that are executing. When you get poor performance, are you documenting what is slow? Running traces to capture activity? Any counters?
April 18, 2009 at 7:39 pm
su5y (4/18/2009)
Hi,We rebooted the box a few weeks back due to bad performance and seen a great improvement in load times 10-15hrs for a few loads - over the past weeks the performance as greatly decline and now takes up 42 hrs to load.
As steve mentioned you should let us know what you mean by load?
Has anyone experience the same problem? The spec of box is great and we looking to upgrade but I want to ensure we don't have same issue with new box.
Theres much to consider in the server than just having great specifications and is it just because of this issue you are upgrading your server? Have you considered anything to improve at your application side?
What is reboot clearing down and what is building up over time i.e. tempdb related, cache related?
Thanks
You need to give us more insight into your server than the info which you have provided to us, then we can try to give you n optimal solution.
April 19, 2009 at 4:59 am
Thanks for yr reply both.
We have a datawarehouse - were we do weekly data loading as part of the batch i.e. all loads are a complete refresh of a table. We know the server spec isn't great which we are looking to upgrade. Disk drives are SAN base. We know we have disk retention issues, memory etc. What I want to establish is why a reboot makes a difference in our data loading time and then increases over time - want to ensure we don't have the same issue when we upgrade.
It's either 2 things (a) db related or (b) operationing system related. We plan to do a reboot tomorrow morning and then I was going to examine wait times and run a Performance Monitor on the box, also run trace from the dev box and connect to prod. I will have to same steps on the next data loading window and compare wait times etc - to see if this pin-points anything.
what do you mean by counters? Is it good practice to carry out reboots? Seen on the net good practice is to reboot every night - sounds crazy to me? Do you agree?
Any advise greatly appreicated.
April 19, 2009 at 5:55 am
There is no need to reboot regularly (unless you have a memory leak in Windows).
It could be a bad execution plan issue, with the execution plan flushed out of cache after the server is rebooted (and re-built when procedure is run). You could try DBCC FREEPROCCACHE (which will flush the execution plans).
The only way you will find out the cause for certain is to run profiler and perfmon, comparing against a benchmark.
April 20, 2009 at 5:47 am
1) Statistics are out of date. Consider updating them nightly.
2) You don't have a max memory setting and sql server is taking too much RAM.
need much more detail from you to hazard other guesses.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2009 at 5:55 am
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
April 20, 2009 at 10:16 am
su5y (4/20/2009)
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.
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"
3) page fault delta in task manager is a quickie you can check for paging problems
4) search web for documentation on DBCC memorystatus and see if anything there pops out as problematic
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2009 at 11:14 am
su5y (4/20/2009)
--------------------------------------------------------------------------------
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
April 20, 2009 at 12:30 pm
su5y (4/20/2009)
su5y (4/20/2009)--------------------------------------------------------------------------------
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?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply