Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 Poor performance on loading Expand / Collapse
Author
Message
Posted Saturday, April 18, 2009 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 05, 2013 2:57 PM
Points: 4, Visits: 22
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
Post #699984
Posted Saturday, April 18, 2009 9:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:31 PM
Points: 32,780, Visits: 14,941
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?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #700017
Posted Saturday, April 18, 2009 7:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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.

Post #700135
Posted Sunday, April 19, 2009 4:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 05, 2013 2:57 PM
Points: 4, Visits: 22
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.
Post #700178
Posted Sunday, April 19, 2009 5:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 11:27 PM
Points: 170, Visits: 553
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.
Post #700186
Posted Monday, April 20, 2009 5:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:43 PM
Points: 4,128, Visits: 5,836
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 at GMail
Post #700520
Posted Monday, April 20, 2009 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 05, 2013 2:57 PM
Points: 4, Visits: 22
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
Post #700528
Posted Monday, April 20, 2009 10:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:43 PM
Points: 4,128, Visits: 5,836
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 at GMail
Post #700753
Posted Monday, April 20, 2009 11:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 05, 2013 2:57 PM
Points: 4, Visits: 22
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
Post #700821
Posted Monday, April 20, 2009 12:30 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:43 PM
Points: 4,128, Visits: 5,836
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 at GMail
Post #700887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse