SQL Server Blog

/SQL Server Blog/

SQLMaestros Bulletin: July 10, 2020

#2_July_10_2020_Friday Hi There, New Blog Posts Double Read – Reading The Same Record Twice. Watch a two-minute video on Twitter. Also read the full blog post. Click Here. Deadlock Due To Bookmark Lookups – Read the full blog post. The Scripts Garage • How to Defragment All The Indexes In a Database. Click Here. • [...]

By |July 10th, 2020|Bulletin|0 Comments

Hands-On-Lab: SQL Server Advanced Query Tuning

Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Advanced Query Tuning. This lab talks about a very common yet difficult problem, finding TOP n rows per group. The problem may seem easy, however in this lab we’ll discuss multiple ways to solve this problem and compare the different ways on performance [...]

By |July 10th, 2020|HOLs|0 Comments

Hands-On-Lab: SQL Server Advanced Aggregations

Hello Friends, Here is another popular lab from Hands-On-Lab library: SQL Server Advanced Aggregations. To meet various business requirements, sometimes we need to calculate cumulative aggregations, sliding window aggregations, Year-To-Date aggregations. This lab is divided into four exercises. In the first exercise, we will learn about cumulative aggregations and will learn to find running total [...]

By |July 9th, 2020|HOLs|0 Comments

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|0 Comments

Script: Find Top 10 CPU Intensive Queries

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 can just join the above DMV with [...]

By |July 8th, 2020|Scripts, SQL Server|0 Comments

Hands-On-Lab: Working With SEQUENCE in SQL Server

Hello Friends, Here is another popular lab from Hands-On-Lab library: Working With SEQUENCE in SQL Server. In Transact-SQL (T-SQL) 2012 SEQUENCE is a user-defined object to generate sequence of numbers based on the specifications while created. NEXT VALUE FOR function generates sequence number from the specified SEQUENCE object. This lab is divided into six exercises [...]

By |July 8th, 2020|HOLs|0 Comments

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

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 lot of columns in the output, but [...]

By |July 7th, 2020|Scripts, SQL Server|1 Comment

Hands-On-Lab: Pivoting in SQL Server

Hello Friends, Here is another popular lab from Hands-On-Lab library: Pivoting in SQL Server. To understand and analyze the data in more efficient way, we may have to rotate rows into columns and columns into rows. The rotation of rows to columns is called pivoting and the reverse operation is called unpivoting. This lab explains [...]

By |July 7th, 2020|HOLs|0 Comments

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 sales.Customers -- create clustered index [...]

By |July 6th, 2020|SQL Server|0 Comments

Script: Find FillFactor of All Indexes in a Database

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 of space should be filled with data [...]

By |July 6th, 2020|Scripts, SQL Server|0 Comments

Hands-On-Lab: Optimizing SQL Server Stored Procedures

Hello Friends, Here is another popular lab from Hands-On-Lab library: Optimizing SQL Server Stored Procedures. Whenever an application is performing slowly because of database, then we need to optimize the stored procedures in that database. This lab is divided into three exercise explaining various techniques that are used to optimize the stored procedure such as [...]

By |July 6th, 2020|HOLs|0 Comments

Hands-On-Lab: OFFSET and FETCH in SQL Server

Hello Friends, Here is another popular lab from Hands-On-Lab library: OFFSET and FETCH in SQL Server. In Transact-SQL (T-SQL) 2012 OFFSET-FETCH clause provides us an option to fetch page of results from the result set. This lab is divided into six exercises explaining different usages of OFFSET-FETCH clause such as skip few rows from the [...]

By |July 4th, 2020|HOLs|0 Comments

Script: How to Defragment All The Indexes In a Database

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 are the MS guidelines. Let’s say N [...]

By |July 3rd, 2020|Scripts, SQL Server|0 Comments

Hands-On-Lab: Merging Data in SQL Server

Hello Friends, Here is another popular lab from Hands-On-Lab library: Merging Data in SQL Server. MERGE statement is used to modify data in a target table based on data in a source table. The statement joins the target to the source by using a column which is common to both the tables. We can INSERT, [...]

By |July 3rd, 2020|HOLs|0 Comments

Hands-On-Lab: Logical and Physical Query Processing in SQL Server

Hello Friends, Here is another popular lab from Hands-On-Lab library: Logical and Physical Query Processing in SQL Server. In this lab, we’ll learn about how a query is logically and physically processed in SQL Server. In the first exercise, we will learn about logical execution order of the statements in select clause. In the second [...]

By |July 2nd, 2020|HOLs|0 Comments

Script: Find The Fragmented Indexes In A Database

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 to understand index fragmentation & page splits. [...]

By |July 2nd, 2020|Scripts, SQL Server|0 Comments

SQLMaestros Bulletin: July 01, 2020

#1_July_01_2020_Wednesday Hi There, SQL Server optimizes Tempdb so that your CRUD operations go faster. There are many ways how this is done. One of them is optimized logging or minimal logging. Read the full blog post. Also a two-minute video on Twitter. See here. S-arg-ability is a much spoken term in SQL Server world. We [...]

By |July 1st, 2020|Bulletin|0 Comments

Script: Find Tables Without Clustered Index

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 table yields better performance, especially if you [...]

By |July 1st, 2020|Scripts, SQL Server|0 Comments

Hands-On-Lab: LAG and LEAD Functions in SQL Server

Hello Friends, Here is another popular lab from Hands-On-Lab library: LAG and LEAD Functions in SQL Server. In Transact-SQL (T-SQL) 2012 LAG & LEAD are the new analytical functions. LAG access data from the previous row in the same result set without the use of self-join. LEAD access data from a subsequent row in the [...]

By |July 1st, 2020|HOLs|0 Comments

Hands-On-Lab: JSON Support in SQL Server 2016

Hello Friends, Here is another popular lab from Hands-On-Lab library: JSON Support in SQL Server 2016. JSON is a short form of Java Script Object Notation which is a simple and lightweight format for exchanging data between multiple sources. The main advantage of using JSON is, it is easy to read. SQL Server 2016 empowers [...]

By |June 30th, 2020|HOLs|0 Comments