Database slowness is stunted performance. The main difference between that simple SELECT statement and database slowness is that the latter is a chronic ailment of your databases.
Slowness can be attributed to multiple factors such as:
- network problems
- missing indexes
- improper caching
- unoptimized queries
- database architecture
What can you do?
1. Monitor your network and memory
Familiarize yourself with your hardware components and network connections. These provide an easier time to limit workloads from
- other nodes
- estimate disk space and cache memory
- determine the capacity of your database
2. Review your database architecture and structure
What is the probability of data duplicate data per row? Is it low? Is it high?
When I query, will I need to go to another schema just to retrieve that other table?
Are there redundant data that will be stored in every field?
Are the data type properly defined? Are there composite data types I should be wary about?
Are the primary and foreign keys properly defined? Will they lead to a conflict of inputting data?
Are chosen indexing approach sufficient to optimize my searches?
Should I use VIEWS as a substitute for this table?
How will we handle orphan data?
Will we automatically flush or clean cache memory once data rows are deleted?
3. Store all Backups off site.
- safe offline storage in a virtual machine
- set up database plans to do data log clean ups
- shrink your .ldf as it is the main cause of slowness
Comments