SQL Server

/SQL Server

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

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

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

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

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

By |June 22nd, 2020|SQL Server|1 Comment

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

By |June 11th, 2020|SQL Server|2 Comments

Processor Time (Processor vs Process) – Monitoring SQL Server CPU Usage (by Amit Bansal)

In this video, you will understand the difference between Processor % Processor Time & Process % Processor Time. Get new content directly in your inbox - Click here You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code). SQLMaestros Master Classes Wish to Attend [...]

By |May 24th, 2019|SQL Server|0 Comments

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

By |February 28th, 2017|SQL Server|0 Comments

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

By |February 28th, 2017|SQL Server|1 Comment

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

By |January 28th, 2015|SQL Server|0 Comments

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

By |December 30th, 2014|SQL Server|2 Comments

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

By |December 22nd, 2014|SQL Server|1 Comment

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

By |October 4th, 2014|SQL Server|0 Comments

SQL Server Cardinality – Quick Notes, Part 3

Hi Friends, Read Part 1 & Part 2. SQL Server Cardinality Estimation is a complicated topic until you understand the internals of SQL Server Statistics. Here are some quick notes that will answer some common questions that you generally have: How does SQL Server Query Optimizer perform cardinality estimation in case of multiple columns? Let’s [...]

By |September 26th, 2014|SQL Server|0 Comments

Cardinality Estimation in SQL Server – Quick Notes, Part 2

Hi Friends, A few days back I penned down a few quick notes about Cardinality Estimation in SQL Server. You can read Part 1 here. SQL Server Cardinality Estimation is a complicated topic until you understand the internals of SQL Server Statistics. Here are some quick notes that will answer some common questions that you [...]

By |September 25th, 2014|SQL Server|0 Comments

SQL Server Cardinality Estimation – Quick Notes, Part 1

Hi Friends, SQL Server Cardinality Estimation is a complicated topic until you understand the internals of SQL Server Statistics. Here are some quick notes that will answer some common questions that you generally have: How does SQL Server Query Optimizer estimate a default cardinality? Let’s see and example (turn ON Actual Execution Plan or press [...]

By |September 23rd, 2014|SQL Server|0 Comments

SQL Server ALTER TABLE REBUILD

Hi Friends, I see a common practice with DBAs: If you have to rebuild a HEAP, you will create a clustered index and drop it – this two phase process does the rebuild job, but is expensive. SQL Server supports ALTER TABLE REBUILD command to rebuild a heap. Creating a dropping a clustered index, the [...]

By |September 22nd, 2014|SQL Server|3 Comments

SQL Server fn_dblog – An interesting observation

Hi Friends, I was experimenting with SQL Server fn_dblog function (undocumented and unsupported). This function allows you to read the active portion of the log file. This is what I observed: For an update transaction, the column “RowLog Contents 1” contains hex value of the entire record in case the table has variable length columns, [...]

By |September 18th, 2014|SQL Server|2 Comments

SQL Server NOT IN clause not working

Hi Friends, A very common issue that I see on forums is that SQL Server NOT IN clause not working. Here is a quick repro: Note: These are dummy tables just for a quick demo so please do not try to find the logic between the two tables :) I create a customers table with [...]

By |September 8th, 2014|SQL Server|7 Comments