Search Results for “sp_blitz”

Locking and Blocking in SQL Server SQL Server loves locks. SQL Server uses “pessimistic” locking by default– your queries are naturally defensive. This can lead to blocking problems that keep you from scaling SQL Server. Free video training class – before you troubleshoot blocking and deadlocks, start with How to Think Like the Engine. It’s a…

sp_BlitzFirst® Result: High Re-Compilations per Second According to sp_BlitzFirst®‘s diagnostics, your SQL Server is having to re-compile more than 10% of the incoming T-SQL requests. This is different from a high number of compilations – this means that SQL Server actually does have an execution plan in memory, but things have changed about the underlying objects,…

sp_BlitzFirst® Result: High Compilations per Second According to sp_BlitzFirst®‘s diagnostics, your SQL Server is having to compile more than 10% of the incoming T-SQL requests. When SQL Server sees a query for the first time, it has to build a new execution plan for it. This can cause high CPU use for building that plan –…

sp_BlitzFirst® Result: SQL is Shrinking a File According to sp_BlitzFirst®‘s diagnostics, your server is shrinking a data or log file to a larger size right now. This is a blocking operation that stops everything else in that particular database. To make things worse, when it’s time to grow again, you’re going to incur more storage waits.…

sp_BlitzFirst® Result: Low Page Life Expectancy According to sp_BlitzFirst®‘s diagnostics, Page Life Expectancy is under 5 minutes right now. This means SQL Server can only keep data pages in memory for that many seconds after reading those pages in from storage. (We’re not saying that PLE > 300 is enough to say SQL Server has enough memory,…

sp_BlitzFirst® Result: Query Rolling Back According to sp_BlitzFirst®‘s diagnostics, someone started a query, and it either ran into a heck of an error, or someone decided to do a ROLLBACK instead of a COMMIT. SQL Server is now cleaning up the aftermath, and everyone else’s queries are likely to suffer as a result. To improve performance,…

sp_BlitzFirst® Result: Sleeping Query with Open Transactions According to sp_BlitzFirst®‘s diagnostics, someone started a transaction, got some locks, and hasn’t released them. This can indicate a dangerous application query pattern like this: BEGIN TRAN SELECT somestuff FROM sometable (then do some processing in the app) COMMIT With this pattern, we might be holding locks much longer…

sp_BlitzFirst® Result: SQL is Growing a File According to sp_BlitzFirst®‘s diagnostics, your server is growing a data or log file to a larger size right now. This is a blocking operation that stops everything else in that particular database. When you load more data or you’ve got a long-running transaction, SQL Server has to grow out…

sp_BlitzFirst® Result: DBCC Running According to sp_BlitzFirst®‘s diagnostics, your server is running a DBCC (corruption check) right now. If your storage subsystem is fast enough, you can run DBCCs during regular production hours without causing a problem. The DBCC alone doesn’t do anything that breaks SQL Server performance. However, reading the contents of your database’s data…

sp_BlitzFirst® Result: Backups or Restores Running According to sp_BlitzFirst®‘s diagnostics, your server is running a database backup right now. If your storage subsystem is fast enough, you can run backups during regular production hours without causing a problem. The backup alone doesn’t do anything that breaks SQL Server performance. However, reading the contents of your…

When your query starts, it asks – begs, really – for a certain amount of memory to store its results, do sorts, join things together, and contemplate life. The amount of memory is determined by how much data will be pulled back from each table, how big the rows are, and so forth. SQL Server…

sp_Blitz Result: Change Tracking Enabled SQL Server’s Change Tracking feature can help data warehouses and homegrown replication solutions manage change and sync data with other databases. This part of our SQL Server sp_Blitz script looks in sys.change_tracking_databases to see if any databases have change tracking enabled. Is Change Tracking Performing Well? It can sometimes have…