Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kpsreenath
Yak Posting Veteran
52 Posts |
 Posted - 2006-06-13 : 10:02:13
|
| Hi AllEvery night we have a Informatica load to one of our database.On a monthly basis, most days the load takes just 15 minutes whereas 2 or 3 times it takes more than 2 hrs for the load to complete.Every day the load is having almost the same number of inserts/deletes. We monitored this for sometime and identified, during these days checkpoint is issued on this particular database.Is there anyway we can avoid this delay.The database size is 25GBThe server cpu is around 5% and its a DL580 machine with 8 CPU and 24GB of RAM. The memory utilization of SQL Server is 1.8GB.This is really critical.Any Comments/inputs are most welcomeThanksSreenathThanksSree |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 10:55:27
|
| On the times when it is slow are there other tasks running - such as maintenance of indexes, updating statistics, etc?Kristen |
 |
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-06-13 : 11:15:50
|
| Nope, we dont have any regular maintenance happening during this time.The server performance is very good.ThanksSree |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 11:32:24
|
| When it is slow is the load happening into a database that has indexes / statistics "out of shape" (including SProcs / Triggers that need recompiling) perhaps?Can you predict when it will next run slow? If so and quick stop-start of SQL before the import [assuming you can do that] fixes it then it is most likely stale query-plan-caching etc. (That won't cause them to run with the latest statistics, but I think the issue would only arise when the stats are different to the cached query plans - having AutoUpdate Stats turned on too would further help diagnose if this is the problem)Kristen |
|
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-06-13 : 11:56:23
|
| The slow load is not predicatable as its happening once in a while.Current database properties include auto update stat and auto create statistics.Also quick start stop is not possible as we have other critical processes running during the same time.I will explore the possibilty of a query plan caching. In the meantime, I am planning to put a perfmon with couple of counters for memory, disk io,general sql server stats etc.I read a couple of forums where in there was suggestion to look out for disk contention..i.e drive configuration is not able to handle that many writes at a time.Couple of more inputs1) Any specific counters would you like to suggest to put in the perfmon2) How about the option of increasing the Recovery interval configuration for the server. Personally i feel this is not a good idea as there are other databases in teh server.What do you guys thinkThanksSree |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-13 : 13:48:08
|
| "Also quick start stop is not possible as we have other critical processes running during the same time"I was afraid of that! You could empty the query plan cache, but its a bit of a blunt weapon if other people are using the server for other activities - unless the server is going to be lightly loaded anyway, I suppose."disk contention"Not sure why that would be different on a specific run - but if over tasks were busier than normal it could be.I'm not much of an expert on perfmon, but others here should be able to help.Kristen |
|
|
|
|
|
|
|
|
Checkpoint Issue - Urgent, Plz help