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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-19 : 07:24:25
|
| John writes "I have SQL server v8.00.194 running on windows 2000 advanced server service pack 3(If you need to know about the hardware involved send me an email and I'll give you the specifics.)I have a set of test data that replicates 6 months worth of consumer purchase data. Each month's data has 500000 purchases in it.I load the data into the database by using a C++ client through OLEDB to pass the data to a stored procedure on the server that loads the data into the database.For testing purposes the C++ app is the same everytime (no recompiles). The loads are done in sequence one after the other and the data for both loads is the exact same data. In other words there are no unknown variables other than network traffic.The time it takes to load the data is computed by capturing the time in the C++ app before the first purchase is loaded and after the last purchase is loaded for each file. The duration is the difference between the two and the time reported is 500000/duration which yields purchases/sec.If I load the data into the database one month's worth at a time and I plot how many purchases a second is inserted into the database I get a curve with the following data points:Month 1 = 521 purchases a secondMonth 2 = 323Month 3 = 204Month 4 = 137Month 5 = 106Month 6 = 85Now if instead of just loading them in one after the other I do a backup of the database in between each load I get these data points:Month 1 = 491 <do backup>Month 2 = 401 <do backup>Month 3 = 290 <do backup>Month 4 = 234 <do backup>Month 5 = 202 <do backup>Month 6 = 173As you can see there is a steady increase in performance so that by Month 6 there is a 103% increase in the performance (173-85)/85*100=103%.I do not do anything else in between the loads other than a backup. I do not reboot the server or the client. And the results are repeatable.Finally the question: Why does this performance increase after a backup occur?Thank you for taking a look at this for me. I am very curious to know why the performance increase occurs after a backup.Sincerely,John." |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-19 : 12:21:41
|
| What is your database's recovery model set to? I would bet that what is occuring when you do not backup the database between the loads is that you are filling up the transaction log which is causing it to expand (which in itself is a performance problem). When you do this process but with a backup in between, it frees up the transaction log so it doesn't need to expand.Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-19 : 12:38:00
|
| Oops - big mistake.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 05/19/2003 12:39:13 |
|
|
|
|
|
|
Why does performance improve after a backup?