question

Mariam_Nagy avatar image
Mariam_Nagy 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
performancedatabase sizedatabase-sizedata-sizeserver-sizing
diagram_0-1.jpg (349.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.