Database Configuration Settings

You provision a 4-core VM. Install SQL Server. Everything auto-configures. TempDB gets 4 data files. Perfect. Three months later, you scale to 32 cores before go-live. More memory. More storage. Bigger instance. Your TempDB configuration? Still set for 4 cores. This happens constantly. SQL Server configures itself based on resources at installation time. When you scale the VM, the database settings don't scale with it. We see this in new installations less frequently now. SQL Server 2017 and newer versions auto-improve TempDB during setup. But here's what still breaks: page latch contention. Your 32-core server with 4 TempDB files creates bottlenecks. Heavy temporary object usage causes waits. Performance degrades. Best practice says 8 files for 32 cores. But depending on your workload, you might need 16. Or you might need 4. You find out by monitoring page latch statistics. Not by following generic recommendations. We monitor this for every client. When we spot contention, we add files. When files sit idle, we remove them. Configuration isn't set-it-and-forget-it. Your server changes. Your configuration should change with it.

Re-Post - Query Store - Best Practice ✍  SQL Server Query Store is a game-changer when it comes to performance monitoring and troubleshooting. But just enabling it isn’t enough you have to fine-tune it for the best results.  I recently optimized the Query Store settings for AdventureWorks2022, and here’s what I learned: => Set Query Store to Read Write - Sounds obvious, but without this, you're just looking at an empty dashboard. => Data Flush Interval: 30 minutes - Reduces potential data loss while keeping disk I/O manageable. => Query Retention: 60 Days means query data is stored for 60 days instead of 30. Keeping data for 60 days helps track performance over a longer time, find slow queries, improve database speed, and make better decisions. => Max Storage: 1GB - (1000 MB is 1 GB since 1 GB = 1024 MB). Adjust this based on workload; I recommend monitoring usage and scaling up as needed. => Wait Stats Capture: ON - Helps track query bottlenecks and wait types affecting performance. Pro Tip: If Query Store gets bloated, run 👉 'QUERY_STORE CLEAR' to reset it, but be mindful of losing historical data. Here’s the quick SQL script I used to optimize it:  ALTER DATABASE AdventureWorks2022  SET QUERY_STORE (  OPERATION_MODE = READ_WRITE,   DATA_FLUSH_INTERVAL_SECONDS = 1800,   CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 60) ); Bro Tip: Query Store is powerful, but only when properly tuned. If you’re running SQL Server and NOT using it, you’re missing out on a goldmine of performance insights! Final Recommendation: 😉  Not all settings work the same for every system. Carefully assess your workload, database performance, and environment before making changes. If troubleshooting CPU, I/O, or blocking issues, enabling Wait Statistics Capture Mode can be useful. Otherwise, keeping it off may help avoid unnecessary overhead. #Patience is performance. We rise with the help of others this is my core belief! #SQLServer #PerformanceTuning #QueryStore #DatabaseOptimization #DataEngineering

Hi Fam, In MySQL database administration, performance tuning is a crucial task to ensure efficient query execution, minimal resource consumption, and high availability. As a MySQL DBA, understanding how to fine-tune MySQL on RHEL and CentOS can significantly enhance database performance. Here’s a step-by-step guide to optimizing MySQL performance. 1️⃣ Check System Performance Before tuning MySQL, analyze system resources: ✅ Check CPU Usage top -o %CPU OR mpstat -P ALL 1 5 ✅ Check Memory Usage free -h ✅ Check Disk I/O iostat -x 1 5 ✅ Check Network Performance netstat -i ✅ Check Running MySQL Processes ps aux | grep mysqld 2️⃣ Optimize MySQL Configuration (my.cnf settings) Modify MySQL configuration file (/etc/my.cnf or /etc/my.cnf.d/server.cnf) to improve performance. ✅ Increase Buffer Pool Size (for InnoDB) [mysqld] innodb_buffer_pool_size=4G # Set to 70-80% of total RAM innodb_log_file_size=512M innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT innodb_read_io_threads=8 innodb_write_io_threads=8 ✅ Optimize Query Cache (if using MySQL <8.0) query_cache_size=64M query_cache_type=1 query_cache_limit=2M ✅ Tune Connection Limits max_connections=500 thread_cache_size=128 table_open_cache=4000 ✅ Adjust Read/Write Performance read_buffer_size=2M read_rnd_buffer_size=4M sort_buffer_size=4M join_buffer_size=4M ✅ Optimize Binary Logging (If Replication is Enabled) log_bin=mysql-bin sync_binlog=1 expire_logs_days=7 binlog_format=ROW Restart MySQL after changes: systemctl restart mysqld 3️⃣ Index Optimization & Query Performance Analysis ✅ Identify Slow Queries Enable slow query logging: slow_query_log=1 long_query_time=1 log_output=TABLE Restart MySQL: systemctl restart mysqld Check slow queries: SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10; ✅ Analyze Query Performance EXPLAIN SELECT * FROM orders WHERE customer_id = 12345; ✅ Identify Missing Indexes SHOW STATUS LIKE 'Handler_read%'; If Handler_read_rnd_next is high, consider adding indexes. ✅ Add Indexes for Faster Retrieval ALTER TABLE orders ADD INDEX idx_customer_id (customer_id); 4️⃣ Optimize Tables & Data Storage ✅ Check Table Fragmentation SHOW TABLE STATUS WHERE Data_free > 0; ✅ Optimize Tables OPTIMIZE TABLE orders; ✅ Convert MyISAM Tables to InnoDB (If Needed) ALTER TABLE my_table ENGINE=InnoDB; 5️⃣ Monitor Performance & Resource Usage ✅ Check MySQL Process List SHOW PROCESSLIST; ✅ Check InnoDB Status SHOW ENGINE INNODB STATUS\G; ✅ Monitor Active Connections SHOW STATUS LIKE 'Threads_connected'; ✅ Identify High Resource Usage Queries SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10; #MySQL #DBA #Linux #PerformanceTuning #MariaDB #DatabaseAdministration