Ykhjuy

I need proper guidelines to setup Database in SQL Server for sales Application (Its a huge Data Transaction System). Specifically i'm looking below information:

1. How to define initial size to create Database


2. Should i use different file and filegroups
   


3. What type of Collation should i select


4. Any tips for tempdb configuration


5. How to defined how much resource needed and how to allocate them.


6. How to handle Server Memory Setup (Minimum server memory, Maximum
   server memory, Index creation memory, Minimum memory per query)


7. How to calculate/estimated number of Maximum worker threads
   


8. How to calculate/estimated Maximum number of concurrent connections
   


9. How to configure SQL Server Parallelism (Cost Threshold for Parallelism, Locks, Max Degree, Query Wait)

Thanks in Advance

How to define initial size to create Database

Well I believe you are asking what value to select for initial size ?. If so, I am sorry to say that it depends on your environment. But please do not leave it to default of 2 MB (for data file) and 1 MB (for log file). I wish MS had better value set as default but this is AFAIK from SQL Server 2005 and has not been changed yet. If you ask me, I would suggest to keep initial size to as much as possible to avoid autogrowth. Suppose you know your database would be 500 GB in year keep it to 300 GB and forget the hassle of autogrowth and slowness due to it, there is no harm in giving as much space to data file as it needs this is only going to benefit the queries. This size if for data file, for log file you would have to figure out yourself. SQL Server Database growth and Autogrowth Setting article would help you more.

Should i use different file and filegroups

Well again the answer is "it depends", file and filegroups are more for making DBA's management job easier and segregating things. If you have large database where you want to offload non critical objects which are accessed less on slow drive and move more accessed objects on faster drive you can do it via filegroups. You can also move one BIG table to altogether different filegroup residing on fast drive to make queries faster. Please read below 2 discussions.

• Benefit to use multiple filegroups


• Benefit of filegroups and setting filegroups to read-only

What type of Collation should i select

That definitely I cannot tell and you should ask the respective teams.

Any tips for tempdb configuration

A lot, please refer to below links. Since you have not added what version of SQL Server you are talking I would only suggest very basic things. Putting tempdb on faster drive makes queries using temp tables fast. Make sure you correctly add temdb data files and keep initial size and autogrowth same for all the data files. For SQL Server 2014 and before enabling TF 1117 and 1118 is also recommended. For SQL Server 2016 and above TF's are not required. More details Here

• Correctly adding Tempdb Files


• Cheat Sheet How to Configure Tempdb

How to defined how much resource needed and how to allocate them

That needs lot of information from your side. There is no direct formula for it.

How to handle Server Memory Setup (Minimum server memory, Maximum server memory, Index creation memory, Minimum memory per query)

To configure max server memory please refer What is a deterministic method for evaluating a sensible buffer pool size?. Don't touch index creation memory and minimum memory per query. If you are having just one instance of SQL server leave min server memory to default.

Ignore points 7 should not touch those settings unless you are expert. The default just works fine. You should let SQL Server decide worker threads it does the job beautifully.

How to calculate/estimated Maximum number of concurrent connections

Unless you start putting some load on server there is hardly any point in getting concurrent connection. For getting max connections on SQL Server hitting see Stackexchange thread Max Number of connection

How to configure SQL Server Parallelism (Cost Threshold for Parallelism, Locks, Max Degree, Query Wait)

You just need to setup proper max degree of parallelism rest you should leave to default. For maxdop setting refer MAXDOP setting algorithm for SQL Server