The following are the general rules you can (have to) follow...
1. Identify whether any sessions/process are Blocked(Which Query, Session ID,... Etc)
2. If you find any blocking query requested by the Web users, Try to validate that queries/procedures whether the queries were properly used Table hint, Short transactions - Properly handled/not
3. Identify what kind operations are performed by the web users freequently (Read / Write).
4. If you find any READ operations, Try to put high FillFactor value(Because, in that case you don't need that much of free space on pages), High fillfactor increase the Read performance.
5. If you find any WRITE operations, Try to put low FillFactor value(Because, in that case you need more free space on pages, so the space allocation on data pages will not perform when writing data), Low fillfactor increase the Write performance.
6. Try to schedule some maintenance plan for Daily, Weekly & Monthly activity(Monitor & apply any Missing Indexes, Updating statistics, Defragmentations, Truncating transaction logs,... Etc.,)
7. Try to keep Tempdb database in highly available free space disk/drive.
8. Try to keep your database's Data file & Log file in seperate location, so, It can perform parallel operation.
9. Try to keep your database's Growth with maximum value. So you can avoide unnecessary growth on regular write operations at peak hours...
10. Try to use Stored procedures instead of any Adhoc queries / inlike queries from your web application
11. Try to NOT use Join with "Table variable" and "Physical table" instead you can join "Temp table" with "Physical table", anyway both will use Tempdb resource directly/indirectly..., But comparing Temp table with table variable, The temp table will perform better.
12. Try to use ANSI-92 form of Join( i.e: JOIN, LEFT JOIN, RIGHT JOIN,...), But not use ANSI-89 form(i.e: using Comma(,) between tables as It produces NxM rows and applies the condition on work table in Cache)
13. Try to avoide Uniqueidentifier type for Key columns as it consumes more memory than Int.
14. Try to avoid unneccessary Check, Default & compute constraints or operations on tables as It always decrease the write operations.
15. Try to maintain Clustered Index on each table with Primary key. Try to avoide Nonclustered Primary key, In some situations, It may not produce/perform Seek operations properly
16. Additional attention required when designing Indexes, specifically Composit Index, Because, The first column only will have SEEK operations, the Remaining columns in composit index will perform SCAN operation
17. Try to avoid of using Unicode data type like NVARCHAR, as It consumes more memory & additional parsing overhead. But, You can use this data type, If you really needed to maintain other than English languages (Multi-Lingual).
www.sqlserverbuddy.blogspot.comvijay7yadav-8738, if this helps please login to Mark As Answer. | Alert Moderator