Currently Browsing: SQL Monitoring

SQL Server / Query Optimization / TempDB Page Splits / Table Spool (Lazy Spool)

SQL Server / Query Optimization / TempDB Page Splits / Table Spool (Lazy Spool)
Recently I had a customer with a really slow query execution and a case of large page splits number on the temporary database. In this example, I tried to replicate the similar issue on my local SQL instance using StackOverflow database. You can download this StackOverflow DB from Brent Ozar web page, just follow the instructions How to Download the Stack Overflow Database via BitTorrent   Firstly I created an Extended Event session to follow up if the page splits occur. DBCC DROPCLEANBUFFERS CREATE EVENT SESSION [PageSplits] ON SERVER ADD EVENT sqlserver.page_split ADD TARGET...
read more

SQL Server / Monitor Backup and Restore progress

SQL Server / Monitor Backup and Restore progress
Recently I had a situation on QA environment where Backup and Restore took a long time to finish. It was HA environment with two nodes in Always On High Availability Group, so backup and restored occurred over network shared location. Drives were slow, database was huge and network latency not the best  so it took a long time to add database to the availability group. To follow up backup or restore progress with start time, total elapsed time and estimated completion time in similar situation you can use following script SELECT Session_id as SPID , Command , query.text AS "Query Text" , Start_Time ,...
read more

SQL Server / Extended Events / Finding Deadlocks

SQL Server / Extended Events / Finding Deadlocks
Recently we experienced deadlocks on one of our staging servers, so I wanted to capture those deadlocks using Extended Events. This how I did it. First of all you need location where you gonna keep your XEL file, then we can create Extended Event session Using SSMS Creating new Extended Event session   Selecting Events you want to capture, in this case xml_deadlock_report   Save the event data to an XEL file   Using T-SQL CREATE EVENT SESSION [Deadlock_Report] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET...
read more

SQL Server / Monitoring / SSMS Activity Monitor and Page Splits per Second on TempDB

SQL Server / Monitoring / SSMS Activity Monitor and Page Splits per Second on TempDB
On one of our older instances I noticed unusually high number of page splits per second, nothing spectacular, average around 20+ (normally below 5) just something unusual for that period of time.   Same period of time day before   I was curious and wanted to investigate what is causing this additional page splits. I did not noticed any performance issues. As well I checked the indexes of all user databases and there where no additional page splits. SQL Server provides a DMV to track page splits sys.dm_db_index_operationl_stats. You can use per table or for the whole DB. SELECT IOS.INDEX_ID,...
read more

Different SQL Versions different query hashes

Different SQL Versions different query hashes
Recently I did upgrade from SQL Server 2012 towards version 2016, and I had some PRTG maps in place for two really important queries. After upgrade monitoring failed. Queryhash has been changed for the exact same query. Honestly, I did not pay much attention to that previously since the majority of those PRTG maps checking same versions of SQL Server. Also, I was under impression that query optimizer computes the query hash during query compilation on base on query logic.  In that case, if query hash is changed that also means query has been changed.   I am calculating query speed on base of total...
read more

SQL Server / Backup File Size Issue

SQL Server / Backup File Size Issue
Yesterday, one of my SQL alerts just sent me an email about my daily backups size. I noticed that my backup has doubled in size. What happened is that I changed my Task Scheduler which is running my PowerShell backup script and accidentally ran backup twice for the same backup set.  Nevertheless, SQL Server is smart enough and instead of overwriting your existing backup file its just append the new one. You can see from the picture below that default for SQL server (2012 in this case) is “append to the existing backup set”. Can be confusing but it is really the safest option for the user. In this...
read more

SQL Server / Index Usage Report Project / Most Expensive Query Plans

SQL Server / Index Usage Report Project / Most Expensive Query Plans
I created this ETL to fetch and save expensive execution plans daily. When ETL process runs again firstly it creates directory and copy all plans from previous day to that directory, and only after that fetch new execution plans. This way I can look old plan later if I like. SSIS Package Description. Control Flow Screen   I am using File System Task to create new directory using the name of  day before, to store my yesterday’s execution plans.   As you can see I am using variable FolderPath to create folder name with day before date. This is Expression I used   From the valuated value...
read more

SQL Server / Index Usage Report Project / Most Expensive Queries

SQL Server / Index Usage Report Project / Most Expensive Queries
Index Usage Report Project / Expensive Queries Table I created this ETL process (SSIS) to populate my WH Expensive Queries table, so I can later join this table using plan_handle to determine if an index is used within those expensive queries. Every time I run this ETL I am truncating this WH table and inserting new rows. Control flow > SQL Task   SQL Task   After that under the Data Flow, I created data source and destination of my ETL process   For OLE DB Source I used SQL command SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; WITH XMLNAMESPACES (DEFAULT...
read more

SQL Server / Index Usage Report Project

SQL Server / Index Usage Report Project
 Index Usage Report Project   The general project idea was to create the report about every index usage within DB. To be able to see a daily number of scans, seeks, page splits for all indexes or key lookups for clustered. You also can change the date range to see how this index behaved in past.     Also, to be able to see all expensive queries using that index (red one are expensive).     To see and save their execution plans as well.       Project concept   Index Usage Stats system views [sys].[indexes] – Basic index information (name, type, etc)...
read more