SQL Server

/SQL Server

SSMS & ADS – July 2020 Release

This blog post first appeared on SQLMaestros SQL Server Management Studio 18.6 and Azure Data Studio July 2020 release, 1.20. Your favourite SSMS has been updated again. Version 18.6 is released. The long pending Database Diagram issue has been fixed along with couple of other fixes. There are new features too. Checkout the release notes. For [...]

By |July 28th, 2020|SQL Server|Comments Off on SSMS & ADS – July 2020 Release

Double Read – Reading The Same Record Twice

In this blog post, we will observe Double Read Phenomenon in SQL Server. You will see how the same record is read twice in a single SELECT query/operation. First watch this video. Now, explanation. So why does this happen? Let’s go step by step. Step 1 First, user 1 fires an UPDATE in an explicit [...]

By |July 8th, 2020|SQL Server|Comments Off on Double Read – Reading The Same Record Twice

Script: Find Top 10 CPU Intensive Queries

This blog post first appeared on SQLMaestros If you want to find the top ‘n’ queries that are currently in the cache, which are consuming more CPU, then you are at the right place. sys.dm_exec_query_stats DMV contains all the information about the resource (CPU, Memory, I/O) consuming queries that are currently in the cache. We [...]

By |July 8th, 2020|Scripts, SQL Server|Comments Off on Script: Find Top 10 CPU Intensive Queries

Script: How to Identify Whether Optimizer is Reusing the Plans or Not

This blog post first appeared on SQLMaestros If you want to identify non parameterized queries that are hogging your SQL Server plan cache then you are at the right place. sys.dm_exec_query_stats DMV becomes very handy when we are looking out for statistical information related to queries. Upon executing the above DMV, we will see a [...]

By |July 7th, 2020|Scripts, SQL Server|Comments Off on Script: How to Identify Whether Optimizer is Reusing the Plans or Not

Deadlock Due To Bookmark Lookups

Deadlock Due To Bookmark Lookups In this blog post, we will see how deadlocks can happen in SQL Server due to Bookmark Lookups. Let’s create a dummy table WideWorldImporters & associated indexes. This will be our test data to play with. use WideWorldImporters GO -- create a opy of sales.customers select * into sales.customers2 from [...]

By |July 7th, 2020|SQL Server|Comments Off on Deadlock Due To Bookmark Lookups

Script: Find FillFactor of All Indexes in a Database

This blog post first appeared on SQLMaestros In our previous blog posts, we have seen how to find fragmented indexes in a database and how to defrag them by using rebuild/reorganize. While creating or rebuilding indexes, we can also provide an option called “FILLFACTOR” which is a way to tell SQL Server, how much percentage [...]

By |July 6th, 2020|Scripts, SQL Server|Comments Off on Script: Find FillFactor of All Indexes in a Database

Script: How to Defragment All The Indexes In a Database

This blog post first appeared on SQLMaestros In our previous blog post, we got to know, how to find out all fragmented indexes in a database. Now we will see how to defrag indexes. According to Microsoft guidelines, based upon index fragmentation percentage, we may choose to either REORGANIZE, REBUILD indexes or just ignore. Here [...]

By |July 3rd, 2020|Scripts, SQL Server|Comments Off on Script: How to Defragment All The Indexes In a Database

Script: Find The Fragmented Indexes In A Database

This blog post first appeared on SQLMaestros We do create indexes to improve the search performance of our queries. But over a period of time, because of DML operations (inserts/deletes/updates), the data will get spread across the disc (Physical order of pages are NOT in sequence). This is called fragmentation. Watch this video by @A_Bansal [...]

By |July 2nd, 2020|Scripts, SQL Server|Comments Off on Script: Find The Fragmented Indexes In A Database

Script: Find Tables Without Clustered Index

This blog post first appeared on SQLMaestros Before we start fine tuning SQL Server, it is good to make sure all basic best practices are in place. One such best practice is to have a clustered index on every table in the database (well, not always but most of the times, having clustered index per [...]

By |July 2nd, 2020|Scripts, SQL Server|Comments Off on Script: Find Tables Without Clustered Index

Faster INSERTs in Tempdb – Optimized/Minimal Tempdb Logging

This blog post first appeared on SQLMaestros . Faster INSERTs in Tempdb – Optimized/Minimal Tempdb Logging In this blog post, we will see how SQL Server optimises Tempdb logging for faster CRUD operations. For the purpose of this tutorial, we will perform the same operations in a regular user database versus Tempdb, and note down [...]

By |July 2nd, 2020|SQL Server|Comments Off on Faster INSERTs in Tempdb – Optimized/Minimal Tempdb Logging