SQL Server

Home/SQL Server

Virtual Symposiums | Geek Talks | DPS SQL Sessions | Bulletin #10

SQLM Bulletin #10. September 29, 2020. Add SQLMaestros to your address book. Spotlight: Data Platform Virtual Summit Updated Content & Pricing Model. Click Here. (200 SQL Sessions + Recordings – You cannot afford to skip this) Hi There, Here is the SQLMaestros Bulletin of 29 September, 2020. Highlights of Today’s Bulletin This Thursday, LIVE Geek Talk [...]

Extended Events Tips & Tricks (GUI)

In this video blog post, you will see a couple of tips and tricks related to SQL Server Extended Events Graphical User Interface. You will learn things like, Filtering, Grouping, Aggregation, Bookmarks, Saving/Opening Settings, Exporting XEvents data to a table, and more. Overall, the idea is to show you how you can be more productive [...]

SSMS & ADS – July 2020 Release

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 many good reasons, people are getting [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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 [...]

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. [...]

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 [...]

Faster INSERTs in Tempdb – Optimized/Minimal Tempdb Logging

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 execution time (performance comparison). Thanks to Hugo Kornelis, [...]

SARGABILITY, CAST, CONVERT & More…

The phrase Sargability is quite popular in SQL Server world. Wikipedia says: “In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument [...]

Pivoting Data in SQL Server

Hi Friends, In this blog post, I’ll be talking about Pivoting Data in SQL Server. Pivot refers to rotating rows into columns with or without aggregation. Let’s understand this with an example. I’ll be using SQLMaestros database. You can download the SQLMaestros database from here - https://sqlmaestros.com/download/sqlmaestros-sample-database/ The SQLMaestros has a TransactionDetails table that records [...]

Char vs Varchar (SQL Server Developer Series)

Hi Friends, Here is another small content  piece on Char v/s Varchar  from my Advanced SQL Developer Master Class. You can see all the blog posts in the Developer Series here. Datatypes is one of the most under rated thing which we ignore when designing tables. In most of the consulting assignments that we do, we [...]

SQL Server Wait Types – Ready Reference

Hi Friends, Till now we have posted two Ready Reference documents; SQL Server Performance Counter Ready Reference and DMVs Ready Reference, of which we had more than 7000 downloads as of this writing! We hope it was helpful for the SQL Community! You can download these documents from our Resource Center. Note that if you [...]

DMVs and DMFs for SQL Server – Ready Reference

Hi Friends, Last week we posted SQL Server Performance Counter Ready Reference, of which we had more than 1000 downloads instantly! We hope it was helpful for the SQL Community! You can download it from our Resource Center. Here is another wonderful piece of document that can help you find the availability of any DMV/DMF [...]

Performance Monitor Counters for SQL Server – Ready Reference

Hi Friends, Here is a wonderful piece of document that can help you find the availability of any Performance Monitor Counter for SQL Server in any version starting from SQL 2005. Many a times, when we are working with various versions of SQL Server, especially in the role of DBA, and when we have a [...]

SQL Server Large Pages – Am I using it?

Question from my class: How do I know if SQL Server is using Large Pages? SQL Server supports Large Page Allocations if the following conditions are true: You are running SQL Server Enterprise Edition Your box has 8 GB RAM or more “Lock Pages in Memory” privilege is set for the SQL service account (which [...]