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 |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2008-01-04 : 13:43:02
|
| Hi Experts,In our production system, there are high number activities involving very huge tables ( around 250 million records ).For performance benefits , we are using dynamic queries in the stored procedures. We are also using WITH clause to FORCE appropriate indexes. Will forcing the indexes have any negative effects ? or Forcing the index would REALLY improve the performance.Any inputs would be highly appreciated.Thanks in advance.Hariarul |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-01-04 : 13:45:38
|
| It depends. If you know your index is the proper one to use and the optimizer is not selecting it, then yes you should force it. This is more common with composite indexes. But the optimizer is often correct as it takes into account statistics and other information, forcing an index may make your query perform worse. So you've just got to test this thoroughly to ensure you've done the right thing. We are forcing indexes in several places.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
Forcing Indexes...