asked
what is the suitable solution for a huge SQL server database
Hello I have a huge database 1- I have about more than 20 tables with some of them with many-to-many relations 2- I have some of these tables may contain a very large row size may exceed 8K 3- The expected data for these tables is about 1 million record for the master table and of course may be 10x (10 million or more) for the detail tables The question is What is the suitable solution for this situation as there is a lot of screens in the web application that may select from all these tables at the same time .. like the search pages that use a view that contains all these joined tables part of the database
http://imagizer.imageshack.us/a/img842/9901/i1f3.jpg (full size) [1]: /storage/temp/1488-diagram_0-1.jpg
Without a lot more detail, it's extremely difficult to suggest a viable solution. In general you need to ensure your database design is appropriate and properly normalized. You should very carefully pick the clustered indexes for your tables, usually on the most frequently used access path to the data. You should understand what the bottlenecks on your system are by using wait statistics so you can understand if you need to focus on hardware purchases to improve CPU, IO or memory. You should tune the queries so that they're properly constructed and use your existing indexes. You should add indexes in the right spots. You may need to consider using advanced functionality such as columnstore indexes or in-memory OLTP tables. I've detailed the process of tuning queries in my [book on the subject][1]. [1]:
http://www.amazon.com/Server-2012-Query-Performance-Tuning-ebook/dp/B008E6HOIS/ref=sr_1_2?ie=UTF8&qid=1399539376&sr=8-2&keywords=fritchey
15 People are following this question.
