How to get same Execution Plans in a test-db?

pdj

I'm currently trying to optimize indexes based on "expensive" queries found using scripts from Lars Lohndorf-Larsen. (http://blogs.msdn.com/b/nav/archive/200 ... story.aspx)
This database is rather big, so I can't just create and drop indexes in the live database, so I would like to do some testing in a test database first. However, after having restored the live-db into a new test-db, then SQL doesn't show the same estimated execution plan in the two databases.

Both the data and the statistics are identical, so how can the estimated execution plans be different?
And how do I fix it, so I can reproduce the same situation in my test-db?

Thanks in advance.

David_Singleton

pdj wrote:

I'm currently trying to optimize indexes based on "expensive" queries found using scripts from Lars Lohndorf-Larsen. (http://blogs.msdn.com/b/nav/archive/200 ... story.aspx)
This database is rather big, so I can't just create and drop indexes in the live database, so I would like to do some testing in a test database first. However, after having restored the live-db into a new test-db, then SQL doesn't show the same estimated execution plan in the two databases.

Both the data and the statistics are identical, so how can the estimated execution plans be different?
And how do I fix it, so I can reproduce the same situation in my test-db?

Thanks in advance.

The data you need is in RAM, which obviously doesn't come across. Maybe there is some way, but I don't think so ... but am very interested if there is. What you would need to do is to create artificial load to replicate the circumstances that got the live system where it is.

pdj

Hi David,

Thanks for you answer, but do you know what this information is called? Just so I know what to search for... :?

I guess I could do a long recording using the Profiler and replay it in the db, but I would like to avoid slowing down the live-db more then necessary. I guess I have to do my testing on the live-db outside working hours, if I don't find a way to copy this information

David_Singleton

pdj wrote:

Hi David,

Thanks for you answer, but do you know what this information is called? Just so I know what to search for... :?

I don't think it even exists ... sorry.

pdj wrote:

I guess I could do a long recording using the Profiler and replay it in the db, but I would like to avoid slowing down the live-db more then necessary. I guess I have to do my testing on the live-db outside working hours, if I don't find a way to copy this information

The only way I have found to correctly tune a database is to do it in live. I use the SQL Perform Analysis tool, which sits in the back ground and gathers all the statistics, but it is of no use in a test db for exactly the reasons you mention.

David_Singleton

Keep in mind also that in a live environment, you are going to have many users sharing cache, so you might get a test environment that is identical to live, but since there is plenty of available RAM, the process you test will run much faster, than in a server where there is limited RAM and it is being frequently flushed.

And then comes blocking...

And then the channel to you storage array...

pdj

It is not a problem the test-db is slower, I just need it to use the same execution plans. I can easily see which index the live-db is using for a specific statement, which should be changed.

I guess I have to do it in the live-db, if the customer can find a service window I can live with

DenSter

One thing about index tuning: Do it in the NAV table designer, not directly on SQL Server. You can't just change indexes either, you have to make sure the code is actually using the key as well.