Q: Why is the same query sometimes fast and sometimes slow, depending on where I run it?
It’s most likely parameter sniffing.
Q: What should you do when your index maintenance jobs take forever?
When you rebuild a 50GB table’s indexes, you’re basically reloading that table from scratch. SQL server has to make a brand-new copy of the table on new data pages, and it logs all this stuff in the transaction log – which means your backups take longer and your Availability Group gets way far behind.
If you’ve been rebuilding indexes nightly, consider easing that off to weekends instead. If you’re worried that will affect performance, you’re probably mixing up the difference between rebuilding indexes and updating statistics. Consider doing daily stats update jobs rather than rebuilding your indexes.
In general, statistics updates are way better than daily index rebuilds.
Q: Which cloud provider should you use to host SQL server?
- If you run SQL Server in a VM, you can get m ore VM selection & performance at AWS
- If you want to rent SQL server as a service, Microsoft’s Azure SQL DB Managed instances are quite a bit ahead of Amazon RDS
Database Blog 