#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. • [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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, [...]
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 [...]
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. [...]
#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 [...]
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 [...]
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 [...]
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 [...]