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 - 2002-04-02 : 21:35:48
|
| Michael writes "Our company has 3 SQL Servers. One for development, one for testing, and one for production. We just recently created a database with a table that contains about 4 million rows. It's a year's worth of customer invoices, etc. On our development server, we have come up with indexes that make our searches lightning fast. One particular query returns the last 3 invoices. It first gets the last 3 invoice numbers with dates, then matches those invoices up to invoices in the table. In development, it runs in a fraction of a second and uses an Index Seek. On our testing server, it uses the SAME index, but does an Index Scan, which causes it to take approximately 12 seconds to run. Now, a copy of this database was made on our production server, and it too uses an Index Seek to make the search fast.Why would our testing server be using an Index Scan? Should I drop the database and re-copy it? Is there a way to fix this without dropping and recreating the database?" |
|
|
Jay99
468 Posts |
Posted - 2002-04-03 : 09:05:16
|
quote:
...On our testing server, it uses the SAME index, but does an Index Scan...
Step 1) Stop telling yourself everything is the same between the servers.Step 2) Find the difference and resolve it. Differeneces could include things like...- SQL Server versions
- service packs or updates
- database settings
- statistics
- table design
- indexes
- data
quote:
Why would our testing server be using an Index Scan?
Not enough information in your post to answer this. quote:
Should I drop the database and re-copy it?
You could, although you may want to look for other differences first. Restoring dev over beta and prod will 1) release all of you dev code that may not be ready for production 2) remove any live data that exists in prod, but not dev 3) probably get you fired for either number 1 or 2. On top of that, it may not fix the problem if the difference is SQL Server version or installed service pack.quote:
Is there a way to fix this without dropping and recreating the database?
Maybe . . .Enjoy!Jay<O> |
 |
|
|
|
|
|
|
|
Index Seek vs. Index Scan