SQL Server Performance Forum – Threads Archive
Need Clustering Index Advice!!!!I’ve tried getting clustering advice to no avail. Everyone always tell me that there is no way of telling which columns should be clustered because of the numerous factors. Well, i’m sure someone should at least be able to tell me what they would do in a similar situation. My basic DB schema is found at www.geocities.com/jacobpressures The least obvious tables for me are the EmployeeTests, Completed_Categories, Completed_Questions, and Responses. Likewise the Categories, Questions and Answers tables. The problem is taht i don’t see any difference in setting PK or FK as clustered index over the other. Of course i realize that i may have to change things later. I just want some ideas. I’ve read many articles and haven’t gotten anywhere with this question of PK and FK. I know that using the PK can be considered a great waste in a valuable resource. Should that one statement be the deciding factor in using one of the FK in those tables? Thanks guys! In the past, i’ve even provided sample queries to no avail.
Hello Jacob -<br /><br />I’ll give you a few things to at least consider:<br /><br />1. What is the load on the system you are creating? If it’s small, then this detail does not make much difference, you can just cluster the tables on your primary key/identity column and call it a day.<br /><br />2. If the demand on the system warrants it, then the selection of a different clustered index would be based on the fact that the data in the table is actually, physically stored in the sort order of the index, and so you’d use that fact to pick a column where that ordering is helpful to speed queries. For example, if your application always gets the data from a particular table as the result of a join to another table, then it might help performance if the data in that table were sorted for the join to happen as fast as possible, e.g. the FK. Or if the queries always get a range of rows from x to y of a particular column, then it could help to have the table stored in that order, so that a range of rows is contiguous.<br /><br />That’s why you always get the answer "it depends" <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />. It’s highly, highly dependant on what your application is doing with the data.<br /><br />FWIW, the index tuning wizard can help suggest indexes for specific queries, which might help you zero in on the best solution.
Thanks, merrillaldrich! Should i set the clustered indexes before i use the Index Tuning Wizard or afterwards? Thanks again!
You’re quite welcome. No need to make the indexes first; you can use the wizard without them and make them afterward. Also, examine the query plans you get in Query Analyser with Display Estimated Execution Plan, using different index setups, to see what the optimizer is really doing differently. Don’t just take the wizard’s recommendations automatically, because it’s not perfect. Compare the plans to a resource like http://www.sqlservercentral.com/columnists/jsack/sevenshowplanredflags.asp to know what you want to have happen as a result of your indexing.
Thanks very much. This is probably the most beneficial info I’ve recieved. Everything i’ve read has told me something about indexes and i understand some of the obvious ones but i’ve been really left in the dark no matter how many times i read them. Most articles focus on teh importance of indexes and why not to use the PK. This at least gives me a good start. It takes all the worry out of trying to figure this out by myself and wasting time during development. i wish they could have been so cut and dry in the articles i’ve read. Thanks!
Moving thread to General DBA section as this has nothing to do with SQL Clustering environment.
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.