Best Practices for Managing Databases

With a background in data engineering and business analysis, I’ve consistently seen the immense impact of optimized SQL code on improving the performance and efficiency of database operations. It indirectly contributes to cost savings by reducing resource consumption. Here are some techniques that have proven invaluable in my experience: 1. Index Large Tables: Indexing tables with large datasets (>1,000,000 rows) greatly speeds up searches and enhances query performance. However, be cautious of over-indexing, as excessive indexes can degrade write operations. 2. Select Specific Fields: Choosing specific fields instead of using SELECT * reduces the amount of data transferred and processed, which improves speed and efficiency. 3. Replace Subqueries with Joins: Using joins instead of subqueries in the WHERE clause can improve performance. 4. Use UNION ALL Instead of UNION: UNION ALL is preferable over UNION because it does not involve the overhead of sorting and removing duplicates. 5. Optimize with WHERE Instead of HAVING: Filtering data with WHERE clauses before aggregation operations reduces the workload and speeds up query processing. 6. Utilize INNER JOIN Instead of WHERE for Joins: INNER JOINs help the query optimizer make better execution decisions than complex WHERE conditions. 7. Minimize Use of OR in Joins: Avoiding the OR operator in joins enhances performance by simplifying the conditions and potentially reducing the dataset earlier in the execution process. 8. Use Views: Creating views instead of results that can be accessed faster than recalculating the views each time they are needed. 9. Minimize the Number of Subqueries: Reducing the number of subqueries in your SQL statements can significantly enhance performance by decreasing the complexity of the query execution plan and reducing overhead. 10. Implement Partitioning: Partitioning large tables can improve query performance and manageability by logically dividing them into discrete segments. This allows SQL queries to process only the relevant portions of data. #SQL #DataOptimization #DatabaseManagement #PerformanceTuning #DataEngineering

Stop treating Data Governance as a blocker. It's the ADVANCE framework for scalable, reliable data systems. Most data engineers see governance as bureaucracy. I see it as infrastructure. 👉 Here's how governance actually accelerates your work: 𝗔 – 𝗔𝗰𝗰𝗼𝘂𝗻𝘁𝗮𝗯𝗶𝗹𝗶𝘁𝘆: Clear Ownership Assign data domain owners for quality and schema. → When your pipeline fails at 3 AM, clear ownership means faster fixes, not Slack ping-pong. → Ex: Product table schema changes? Marketing owns definitions, you own the pipeline SLA. 𝗗 – 𝗗𝗲𝗳𝗶𝗻𝗶𝘁𝗶𝗼𝗻𝘀: One source of truth. Standardize metadata, naming conventions, and schema structure. → Stop rebuilding the same "revenue" metric five different ways across teams. → Ex: A central data dictionary means your JOIN logic matches the analyst's GROUP BY logic—every time. 𝗩 – 𝗩𝗮𝗹𝘂𝗲 𝗔𝗹𝗶𝗴𝗻𝗺𝗲𝗻𝘁: Build what matters. Prioritize governance efforts based on critical data assets. → Governance prioritizes pipelines that drive revenue, not vanity dashboards. → Ex: Customer churn pipeline gets priority over experimental metrics that nobody queries. 𝗔 – 𝗔𝗱𝗮𝗽𝘁𝗮𝗯𝗶𝗹𝗶𝘁𝘆: Your safety net for change. Implement automated validation and testing frameworks within pipelines. → Version control for schemas, automated data quality checks, rollback strategies. → Ex: When GDPR hits, proper governance means you know exactly where PII lives and can purge it in hours, not weeks. 𝗡 – 𝗡𝗲𝗰𝗲𝘀𝘀𝗶𝘁𝘆: Start small, scale smart. Focus initial efforts on foundational data quality in the most consumed tables. → Don't govern all 10,000 tables—govern the 10 that power your exec dashboard. → Ex: Focus on production tables first. That staging sandbox? Let it stay messy. 𝗖 – 𝗖𝗼𝗹𝗹𝗮𝗯𝗼𝗿𝗮𝘁𝗶𝗼𝗻: Stop being the "data says no" team. Build self-service data catalogs and open channels between engineering and data consumers. → Partner with analysts, scientists, and product teams to co-own quality. → Ex: Weekly data council meetings = fewer surprise schema breaks and angry Slack messages. 𝗘 – 𝗘𝗱𝘂𝗰𝗮𝘁𝗶𝗼𝗻: Self-service without chaos. Train engineers on best practices for metadata capture and quality testing. → Document your pipelines, teach SQL best practices, empower teams to fish for data safely. → Ex: A 10-minute onboarding doc prevents analysts from running queries that bring down your warehouse. The truth? Good governance means: ✔️ Fewer 2 AM incidents ✔️ Less "Why don't these numbers match?" ✔️ More time building, less time firefighting 𝘎𝘰𝘷𝘦𝘳𝘯𝘢𝘯𝘤𝘦 𝘦𝘮𝘱𝘰𝘸𝘦𝘳𝘴, 𝘯𝘰𝘵 𝘳𝘦𝘴𝘵𝘳𝘪𝘤𝘵𝘴. 𝘚𝘵𝘳𝘦𝘯𝘨𝘵𝘩𝘦𝘯𝘪𝘯𝘨 𝘢𝘯𝘺 𝘰𝘯𝘦 𝘱𝘪𝘭𝘭𝘢𝘳 𝘴𝘩𝘢𝘳𝘱𝘦𝘯𝘴 𝘺𝘰𝘶𝘳 𝘥𝘢𝘵𝘢 𝘨𝘢𝘮𝘦. Here's the amazing guide by George Firican from LightsOnData on "Practical Data Governance" - https://lnkd.in/gwBcYQg5 Pick ONE pillar. Implement it this sprint. Watch your data quality improve while your stress drops. Which pillar would fix your biggest pain point today?

SQL Query Optimization Best Practices Optimizing SQL queries in SQL Server is crucial for improving performance and ensuring efficient use of database resources. Here are some best practices for SQL query optimization in SQL Server: 1). Use Indexes Wisely: a. Identify frequently used columns in WHERE, JOIN, and ORDER BY clauses and create appropriate indexes on those columns. b. Avoid over-indexing as it can degrade insert and update performance. c. Regularly monitor index usage and performance to ensure they are providing benefits. 2). Write Efficient Queries: a. Minimize the use of wildcard characters, especially at the beginning of LIKE patterns, as it prevents the use of indexes. b. Use EXISTS or IN instead of DISTINCT or GROUP BY when possible. c. Avoid using SELECT * and fetch only the necessary columns. d. Use UNION ALL instead of UNION if you don't need to remove duplicate rows, as it is faster. e. Use JOINs instead of subqueries for better performance. f. Avoid using scalar functions in WHERE clauses as they can prevent index usage. 3). Optimize Joins: a. Use INNER JOIN instead of OUTER JOIN if possible, as INNER JOIN typically performs better. b. Ensure that join columns are indexed for better join performance. c. Consider using table hints like (NOLOCK) if consistent reads are not required, but use them cautiously as they can lead to dirty reads. 4). Avoid Cursors and Loops: a. Use set-based operations instead of cursors or loops whenever possible. b. Cursors can be inefficient and lead to poor performance, especially with large datasets. 5). Use Query Execution Plan: a. Analyze query execution plans using tools like SQL Server Management Studio (SSMS) or SQL Server Profiler to identify bottlenecks and optimize queries accordingly. b. Look for missing indexes, expensive operators, and table scans in execution plans. 6). Update Statistics Regularly: a. Keep statistics up-to-date by regularly updating them using the UPDATE STATISTICS command or enabling the auto-update statistics feature. b. Updated statistics help the query optimizer make better decisions about query execution plans. 7. Avoid Nested Queries: a. Nested queries can be harder for the optimizer to optimize effectively. b. Consider rewriting them as JOINs or using CTEs (Common Table Expressions) if possible. 8. Partitioning: a. Consider partitioning large tables to improve query performance, especially for queries that access a subset of data based on specific criteria. 9. Use Stored Procedures: a. Encapsulate frequently executed queries in stored procedures to promote code reusability and optimize query execution plans. 10). Regular Monitoring and Tuning: a. Continuously monitor database performance using SQL Server tools or third-party monitoring solutions. b. Regularly review and tune queries based on performance metrics and user feedback.  #sqlserver #performancetuning #database #mssql

Here are some SQL Best Practices for working with Large Datasets. SQL can be easy to get the hang of but there’s some key tips to keep in mind when working with large datasets. 𝟭. 𝗜𝗻𝗱𝗲𝘅 𝗪𝗶𝘀𝗲𝗹𝘆 Use indexing to speed up getting your data. Focus on common columns, generally PKs that are often used in joins 𝟮. 𝗔𝘃𝗼𝗶𝗱 𝗦𝗲𝗹𝗲𝗰𝘁 * Instead of selecting all your data, only select the columns you need. For really wide datasets selecting all of your data can result in an expensive query especially if you’re dealing with very large datasets 𝟯. 𝗘𝗳𝗳𝗶𝗰𝗶𝗲𝗻𝘁𝗹𝘆 𝘂𝘀𝗲 𝗝𝗼𝗶𝗻𝘀 Use only the joins you need. Don’t use a left join if you can use an inner join. The more efficient you are with joins the less unnecessary data you’ll carry over in your query. 𝟰. 𝗙𝗶𝗹𝘁𝗲𝗿 𝗘𝗮𝗿𝗹𝘆 Ideally use where clauses need the top of your query. Some data needs to be aggregated before filtering, but when you can only carry forward the data you need downstream and this can be done by moving up your where clauses early in your query 𝟱. 𝗢𝗽𝘁𝗶𝗺𝗶𝘇𝗲 𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀 Replace subqueries with CTEs and better yet Joins when you can. Subqueries can be slow and can often result in un-optimized outputs and usually there’s a more efficient way to do the same thing you need a subquery for. 𝟲. 𝗖𝗼𝗻𝘀𝗶𝗱𝗲𝗿 𝗱𝗲𝗻𝗼𝗿𝗺𝗮𝗹𝗶𝘇𝗮𝘁𝗶𝗼𝗻 Consider denormalizing your data to limit the number of joins you have to do. If you can simplify your base table so that the starting table has all the necessary columns you need to work with. Think of this as reporting level DBT tables 𝟳. 𝗟𝗲𝘃𝗲𝗿𝗮𝗴𝗲 𝗣𝗮𝗿𝘁𝗶𝘁𝗶𝗼𝗻𝗶𝗻𝗴 Especially with large data, partitioning can help you break down your datasets into smaller more manageable pieces that are easier to transform and aggregate (on the compute usage side) 𝟴. 𝗔𝗻𝗮𝗹𝘆𝘇𝗲 𝘆𝗼𝘂𝗿 𝗗𝗮𝘁𝗮 Use commands like EXPLAIN to you understand how your database is issuing and executing the query - this can be super helpful for debugging and seeing what parts of a large query are not running optimally. 𝟵. 𝗖𝗮𝗰𝗵𝗲 𝘄𝗵𝗲𝗻 𝗽𝗼𝘀𝘀𝗶𝗯𝗹𝗲 If you find yourself running the same query multiple times, maybe cache it and have the cache update at a certain time interval vs re-running the same logic several times, this can save on redundant calls and calculations that you often do. 𝟭𝟬. 𝗞𝗲𝗲𝗽 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 There’s so much more here - some people even have PHDs in optimization. If you want you can really get down to the nitty gritty and optimize on the under the hood database side and settings, it can get complex, but can be worthwhile and interesting depending on the project you have. All these can help you more efficiently deal with large data to avoid getting into the situation where you have large, expensive and slow queries. What else would you add?

7 Proven Database Optimization Techniques for High-Performance Applications ▶️ Indexing - Analyze query patterns in the application and create appropriate indexes. - On social media websites, index user IDs and post timestamps to quickly generate personalized news feeds. ▶️ Materialized views - Precompute complex query results and store them in the database for faster access. - On e-commerce websites, it speeds up product search and filtering by pre-calculating category aggregates and best-selling items. ▶️ Denormalization - Reduce complex joins to improve query performance. - In e-commerce product catalogs, store product details and inventory information together for faster retrieval. ▶️ Vertical Scaling  - Boost your database server by adding more CPU, RAM, or storage. - If the workload in applications is relatively predictable and doesn't experience sudden spikes, vertical scaling can be sufficient to meet the demands. ▶️ Caching - Store frequently accessed data in a faster storage layer to reduce database load. - Storing frequently accessed data, such as product information or user profiles, in a cache to reduce the number of database queries. ▶️ Replication - Create replicas of your primary database on different servers for scaling the reads. - Replicate data to geographically dispersed locations for faster access by local users, reducing latency and improving the user experience. ▶️ Sharding - Split your database tables into smaller pieces and spread them across servers. Used for scaling the writes as well as the reads. - In e-commerce platforms, shard customer data by region or last name to distribute read/write loads and improve response times.

SQL Server Maintenance best practices Out of the box, SQL Server does very little to help you maintain your databases. Auto-Update and Auto-Create Statistics are "true" in my model database, but that's pretty much it. Basic maintenance items for you to consider: 1 - Backups (see yesterday's post) 2 - Index and Statistics 3 - Checking for corruption 4 - Cleaning up the mess 💥Indexes 15 years ago, we were all defragmenting indexes every day...because we had to. Now that we are in the days of SSD storage, with its completely different write patterns vs. the old fidget spinners, that is no longer nearly as important. I was on a call with a senior Microsoft Escalation Engineer 2 days ago that says he only does Rebuild/Reorganize of indexes in extreme fragmentation of VLDBs. If you are severely limited on memory, this might need to happen more often, just to have fewer pages in memory. 💥Statistics Every Single Day. If the default sample size from Microsoft doesn't work for you, bump it. I'm also a fan of auto-update asynchronously, but there are some (low memory) situations where that won't work. If you want the Query Optimizer ("Optimus") to make good decisions about how to allocate resources to your queries, updated stats are critical. 💥Corruption Checking / CheckDB As often as possible, every server, every database. Corruption in a database is very likely to cause data loss. The faster you find it, the less you lose. If you get lucky its in a non-clustered index that you can rebuild. But that is just a warning that you have an issue somewhere that needs attention. Like...now. I use Test-DbaLastBackup from https://dbatools.io/ to offload this work to a dev server any time I can. If you have corruption it will carry to that box and be found there. Of course, this assumes you are backing up your databases. 💥Cleanup your mess! SQL Server Maintenance Plans have the option to clean up old backups, activity logs files, etc. but its not automatic. You have to add those tasks. I see servers every week with history files going back 5 years or more. Nobody needs to know about a successful log back from 2017. 30-90 days is usually sufficient unless you have regulatory or audit reasons to retain them longer. If you do, make a file share and a job to move them. 💥Everything else. There's more, but this should keep some of you busy for a bit :) Yes...we're going to have some differences of opinion on some of these, and that's ok. If your routines are different than mine, let me know in the comments so I can learn from you. Not just the what, but the why. No coffee meme today, as the dbatools link is more important ☕ ----------------- Ring the 🔔 on my profile to get notified when I post I post when inspiration strikes But not every day 😁 #SQLServer  #FractionalDBAs  #PocketDBA Join my Accidental SQL DBA group here: https://lnkd.in/eHPTa8y8

Stepping into a New DBA Role: Where would you start? Joining a new company as a DBA can be challenging. Here's how I'd approach it to ensure a smooth transition and begin making an impact. 𝟏. 𝐔𝐧𝐝𝐞𝐫𝐬𝐭𝐚𝐧𝐝 𝐭𝐡𝐞 𝐁𝐮𝐬𝐢𝐧𝐞𝐬𝐬 𝐍𝐞𝐞𝐝𝐬: - Engage with Stakeholders: Spend time talking to business leaders, key stakeholders, and teammates to understand data needs and pain points. This helps to align your strategies with business objectives. - Identify Priorities: Determine which database issues are affecting the business most and prioritize them. 𝟐. 𝐀𝐬𝐬𝐞𝐬𝐬 𝐭𝐡𝐞 𝐂𝐮𝐫𝐫𝐞𝐧𝐭 𝐃𝐚𝐭𝐚𝐛𝐚𝐬𝐞 𝐄𝐧𝐯𝐢𝐫𝐨𝐧𝐦𝐞𝐧𝐭: - Inventory Existing Systems: Conduct a thorough review of existing database infrastructure. Document the types of databases (i.e. data warehouse, OLTP, DSS), versions, and their configuration, and environments (Production, Dev, QA, UAT, etc). - Read any existing documentation and update where required. - Perform a comprehensive health check. At The SERO Group, we perform a health check that covers 4 distinct pillars: Reliability, Security, Performance, and Recovery - https://buff.ly/3AbGxag. - Evaluate Performance: Analyze current database performance and identify bottlenecks or inefficiencies. Make sure these align with priorities and business objectives. You wouldn't want to spend days and/or weeks on a database or instance soon to be retired. 𝟑. 𝐒𝐞𝐭 𝐑𝐞𝐚𝐥𝐢𝐬𝐭𝐢𝐜 𝐆𝐨𝐚𝐥𝐬 𝐚𝐧𝐝 𝐄𝐱𝐩𝐞𝐜𝐭𝐚𝐭𝐢𝐨𝐧𝐬: - Short-term Wins: Identify quick wins that can demonstrate immediate value and build trust with the stakeholders and teammates. (SMART Goals template - https://buff.ly/3WIC5IY ). The key here is to ensure these wins are measurable with a focus on business needs. - Long-term Strategy: Develop a clear, long-term strategy with milestones that align with the company's goals. 𝟒. 𝐔𝐩𝐝𝐚𝐭𝐞, 𝐔𝐩𝐠𝐫𝐚𝐝𝐞 𝐚𝐧𝐝 𝐎𝐩𝐭𝐢𝐦𝐢𝐳𝐞: - Modernize systems: Assess if the current technology stack is up-to-date and if not, plan for necessary upgrades or migrations (again, be sure that it aligns with business needs and priorities). 𝟓. 𝐄𝐬𝐭𝐚𝐛𝐥𝐢𝐬𝐡 𝐒𝐞𝐜𝐮𝐫𝐢𝐭𝐲 𝐏𝐫𝐨𝐭𝐨𝐜𝐨𝐥𝐬 (𝐨𝐫 𝐫𝐞𝐯𝐢𝐞𝐰 𝐰𝐡𝐚𝐭'𝐬 𝐚𝐥𝐫𝐞𝐚𝐝𝐲 𝐢𝐧 𝐩𝐥𝐚𝐜𝐞): - Data Security - Ensure strong security measures are in place to protect sensitive data. Implement encryption, access controls, and regular audits (preferably automated). - Compliance - Make sure the database environment complies with relevant regulations and standards. 𝟔. 𝐂𝐨𝐥𝐥𝐚𝐛𝐨𝐫𝐚𝐭𝐞: - Interdepartmental Collaboration: Encourage collaboration between the database team and other departments to ensure data solutions meet business needs. 𝟕. 𝐀𝐮𝐭𝐨𝐦𝐚𝐭𝐞: - Automate reoccurring tasks wherever possible. Get home at a reasonable time while still meeting objectives. What other advice would you have for DBAs starting at a new company? #dbachallenges #personaldevelopment