Farcry Indexing Script for MS SQL
One of the downsides of a complex CMS is performance. I do not mean that a CMS can't perform well - it certainly can. I only mean that there is less margin for error when so many things are happening with each request. The more complex the application, the more the developer should pay attention to scalability issues. Probably nothing impacts your performance more than your database. I often tell customers who are prepared to spend money on hardward to buy the best server you can afford for your database server. In fact, a cheap web server and a solid Database server beat a great web server with a pokey db server every time. Anyway, I have been working with Farcry for some time now. I have used the information from Daemon on DB Indexing Strategies to make decisions about my Microsoft SQL Server indexing.
The suggestions they make are a bit generic. I have compiled my suggestions for indexing into an indexing script. I like using primary keys where it makes sense (where I need a unique index). Here's a tip. If you are in the habit of setting your primary keys in Enterprise Manager and you want a column other than the primary key column (or columns) to have a clustered index, make sure you create the cluster index first then set your primary key. Why? Because EM will automatically create a PK as a clustered index - which means you will need to drop it to create a clustered index anywhere else. Any way, here's the indexing script. Keep in mind that it will probably throw errors when you run it (null columns and the like) - so you may need to use it as a guide instead of simply running it in EM. If you have a pristine installation and you want the whole ting (constraints and defaults as well as just indexes and keys) give me a shout.
4 Comments
-
Hi Mark, I would appreciate getting the whole script! Thanks for this too! -
Indexing Script:
http://www.coldfusionmuse.com/files/farcry_MSSQL_i... -
Mark, looks like a great script. Would be great if we could give it a home somewhere like: http://docs.farcrycms.org:8080/confluence/display/... -
Mark - Maybe you didn't know this trick, but you can easily set the primary key to non-clustered by unchecking the "create as clustered" box in EM. Then you can set any other index to be clustered without dropping the primary key.
Sites to Watch
Aggregators
RSS
