CPU Issues with sp_BlitzCache

Issues: Long Running Queries https://www.brentozar.com/blitzcache/long-running-queries/ Check for missing index – Check “Execution Plan” for missing index. Expensive Key Lookups https://www.brentozar.com/blitzcache/expensive-key-lookups/ Check “Execution Plan” for Key Lookup. A key lookup occurs when SQL uses a nonclustered index to satisfy all or some of a query’s predicates, but it doesn’t contain all the information needed to cover... » read more

SQL Server Editions and Indexing

Two things not available in Standard that caught my eye were: Online indexing Online schema change On the Standard version, SQL Server will lock your object (i.e. table) until the create index or alter table is complete. If you have the Enterprise Edition, then you could specify the WITH (ONLINE=ON) option to alter tables or create indexes,... » read more

SQL Server First Responder Kit for Performance Troubleshoot

Brent Ozark Unlimited https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit#how-to-install-the-scripts Focus on the top offenders from EXEC sp_BlitzCache @SortOrder = ‘cpu’ as the starting point. Review Steps Install the first responder toolkit.  Use the Install-All-Scripts.sql installation script in master. https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit Exec sp_Blitz Exec sp_BlitzCache During peak hours run Exec sp_BlitzFirst.  If the DB is already hitting 100% CPU constantly don’t... » read more

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This isolation level allows dirty reads. One transaction may see uncommitted changes made by some other transaction. To maintain the highest level of isolation, a DBMS usually acquires locks on data, which may result in a loss of concurrency and a high locking overhead. This isolation level relaxes this property. You may want to check... » read more