Troubleshooting Common Issues with Microsoft SQL Server 2012 Best Practices Analyzer

Top 10 Fixes Found by Microsoft SQL Server 2012 Best Practices Analyzer

1. Patch and service-pack updates

   • Ensure SQL Server engine, SQL Server Agent, and related components are patched to the latest service pack and cumulative updates to address known bugs and security issues.
   • Action: Apply tested service packs/CUs during maintenance windows; verify compatibility with applications and third-party drivers.

2. Enable and configure automated backups

   • Confirm full, differential, and transaction-log backups are scheduled and completing successfully; check retention and offsite copies.
   • Action: Create/verify SQL Agent jobs or use maintenance plans; monitor backup job history and periodically test restores.

3. Set appropriate recovery models

   • Detect databases with inappropriate recovery model settings (e.g., large production DBs in SIMPLE preventing point-in-time recovery).
   • Action: Change recovery model to FULL for critical data if point-in-time recovery is required; ensure log backups follow.

4. Fix orphaned users and mismatched SIDs

   • Identify database users not mapped to server logins or mismatched SIDs after restore/move operations.
   • Action: Use sp_change_users_login or ALTER USER … WITH LOGIN = to remap; script and verify permissions.

5. Resolve misconfigured max server memory

   • Flag servers where max server memory is set too high (causing OS starvation) or left at default for dedicated instances.
   • Action: Set max server memory based on total RAM, OS needs, and other apps (common starting formula: TotalRAM – 2-4GB for OS, adjust for workloads).

6. Correct tempdb configuration

   • Identify tempdb with single data file, incorrect initial sizes, or auto-growth set to tiny increments causing contention.
   • Action: Configure multiple tempdb data files (typically one per CPU core up to 8, then monitor), set fixed initial sizes, and use sensible autogrowth (MB, not percent).

7. Address TRACE flag and startup parameter issues

   • Detect undocumented or harmful trace flags/startup parameters left enabled that can affect stability or performance.
   • Action: Review required flags for your environment, remove any unnecessary flags, and document changes.

8. Index fragmentation and statistics maintenance

   • Report heavily fragmented indexes or stale statistics leading to poor query plans.
   • Action: Implement index rebuild/reorganize strategy based on fragmentation thresholds and update statistics regularly (with fullscan or sampled as appropriate).

9. Security and permissions hardening

   • Find excessive permission grants, public role abuse, or SA account issues.
   • Action: Apply principle of least privilege, remove logins from sysadmin unless required, secure SA (rename/disable if policy allows), and enforce strong passwords and auditing.

10. Check for deprecated features and compatibility issues

    • Flag databases using deprecated features or databases with compatibility levels that can cause unexpected behavior.
    • Action: Review flagged features, plan migrations or code changes; set database compatibility level appropriately and test applications after changes.

Notes for implementation:

• Prioritize fixes by production impact and risk; test changes in nonproduction first.
• Document each change and monitor effects (server metrics, job history, error logs).
• BPA findings are guidance — some recommendations may not apply depending on environment constraints.