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 |
|
aakcse
Aged Yak Warrior
570 Posts |
 Posted - 2010-05-03 : 06:38:16
|
| A proc query is taking 3hrs to execute, it has joins using 8 tables which has records ranging from 1 to 9 million.I sure we only perform select operation on these table in futur alsowe are not going to perform DMLwhich index(culuster non culuster) would be useful to create on these table? approximate guess will also help to start with.Regards,-Neil |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-03 : 07:32:29
|
| No way to answer that with the information you've given.Please post the table definition, the definitions of all indexes on the table and the query in question. The query's current execution plan (saved as a .sqlplan file) would also be useful. [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]--Gail ShawSQL Server MVP |
 |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-03 : 08:06:05
|
| At the very least, you'll need to post the stored proc, and the table definitions for the tables it uses, as well as the execution plan for the query. Every table should have a clustered index. This is because non-clustered indexes are more effective if the table has a clustered index. Also, if performance is the major concern, then you should also make use of covering indexes (available in 2005 onwards), especially if storage space is plentiful and DML changes are minimal. If you're any DML on the tables, then any indexes you add should have a 100% fill factor. This will minimize the size of the index and increase performance.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
|
|
|
index help