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)