Need to performance test a database design

Hello - This is a broader topic. You can do benchmarking. First, you need to understand the business SLA to know the timeline for the procs to produce the results. But based on that you need to write the code in the procedure. Use MS best practices to write the script. Execute proc and check execution plan by enabling stats io on and other features and check how is the performance of cpu, disk/IO and memory usage. 

For the point of performance, we cant judge the reason direclty untill we see the query and do some execution test.

We needs to test the query time by using the query optimizer.

Follow the below URL's for optimization :

https://technet.microsoft.com/en-us/library/ms172984(v=sql.110).aspx

https://www.itprotoday.com/microsoft-sql-server/top-10-sql-server-performance-tuning-tips

https://www.red-gate.com/simple-talk/sql/performance/performance-tuning-tips-for-database-developers/

Thank you.

Mark as answer If it helps.