DEV Community 👩‍💻👨‍💻

Garry Xiao
Garry Xiao

Posted on

SQL Server huge data performance points

Based on the instance:

  1. Indexes, https://odetocode.com/articles/237.aspx
  2. Query Hints, https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15
  3. Data Partitioning: https://www.mssqltips.com/sqlservertip/1200/handling-large-sql-server-tables-with-data-partitioning/
  4. Computed Column to avoid unnecessary calculation: https://www.mssqltips.com/sqlservertip/1682/using-computed-columns-in-sql-server-with-persisted-values/

Replication
Database replication is the technology to distribute data from the primary server to secondary servers. There are two main benefits of using SQL Server replication: 1. Using replication, we can get nearly real-time data which can be used for reporting purpose. 2. Define and schedule the replication. SQL Server supports three replication types: 1) Transactional Replication. 2) Snapshot Replication. 3) Merge Replication.SQL Server Database Replication: https://codingsight.com/sql-server-database-replication

Schedule
The schedule is a timed logic calculation. A Job in the database side: https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-ver15. Implementing a similar solution with Windows Service is also a choice.

Message Queuing
Messages can be queued and delivered later if the destination is unavailable or busy or need to consume a lot of time and external resources. Using the SQL Server Service Broker for Asynchronous Processing:
https://www.sqlshack.com/using-the-sql-server-service-broker-for-asynchronous-processing/

Caching
Caching is a popular mechanism used to accelerate response times and help applications scale while reducing the load on RDBMS systems, and save on resources. Using Redis with SQL Server: https://lynnlangit.com/2016/09/27/getting-started-with-sql-server-redis/

Top comments (0)

Timeless DEV post...

How to write a kickass README

Arguably the single most important piece of documentation for any open source project is the README. A good README not only informs people what the project does and who it is for but also how they use and contribute to it.

If you write a README without sufficient explanation of what your project does or how people can use it then it pretty much defeats the purpose of being open source as other developers are less likely to engage with or contribute towards it.