Category Archives: sql server

Wait wait wait…

Let’s talk about why we have to wait and how to understand the wait types.

Paul Randal in his post Wait statistics, or please tell me where it hurts said:

A thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads that are SUSPENDED. In the meantime, the next thread on the FIFO (first-in-first-out) queue of threads waiting for the CPU (called being RUNNABLE) is given the CPU and becomes RUNNING. If a thread on the SUSPENDED list is notified that it’s resource is available, it becomes RUNNABLE and is put on the bottom of the RUNNABLE queue. Threads continue this clockwise movement from RUNNING to SUSPENDED to RUNNABLE to RUNNING again until the task is completed

That’s explain a lot, because the SQL Server threads doesn’t run all in the same time. A good example is when our query is doing physical reads. The IO subsystem is the slowest part of our resources and probably will take some time if the query is reading gigabytes of data.


After the CPU request the data we are querying the disk will run for it, but before send the data back there another step. All data need to go to memory first and imagine a scenario that you don’t have space and first need to open space for that data you need. How many threads are using to run this query? How long will take to the application to show that data?

So, in all this process we can see a couple of waits for example, like PAGEIOLATCH_XX , PAGELATCH_XX, ASYNC_NETWORK_IO, CXPACKET and I will write in detail about of each in the next posts.

Heap Tables

What’s a heap table? I would say it’s a table without clustered index.

What’s the characteristic of a heap table? The data isn’t ordered.

What’s the consequence having a heap table? There are a few:

  1. Specific data is not retrived quickly
  2. Data pages aren’t linked, that means sequential access needs to refer to the index allocation map (IAM) pages
  3. No cost to update indexes
  4. No additional space to store clustered index

SQL Operations Studio Linux

Great news, a visual way to manage SQL Server from Linux.

SQL Operations Studio came to fill a gap we were waiting to develop and manage SQL Server databases on Linux. In the link we can see more information like the features below.

Feature Highlights

  • Cross-Platform DB management for Windows, macOS and Linux with simple XCopy deployment
  • SQL Server Connection Management with Connection Dialog, Server Groups, and Registered Servers
  • Object Explorer supporting schema browsing and contextual command execution
  • T-SQL Query Editor with advanced coding features such as autosuggestions, error diagnostics, tooltips, formatting and peek definition
  • Query Results Viewer with advanced data grid supporting large result sets, export to JSON\CSV\Excel, query plan and charting
  • Management Dashboard supporting customizable widgets with drill-through actionable insights
  • Visual Data Editor that enables direct row insertion, update and deletion into tables
  • Backup and Restore dialogs that enables advanced customization and remote filesystem browsing, configured tasks can be executed or scripted
  • Task History window to view current task execution status, completion results with error messages and task T-SQL scripting
  • Scripting support to generate CREATE, SELECT and DROP statements for database objects
  • Workspaces with full Git integration and Find In Files support to managing T-SQL script libraries
  • Modern light-weight shell with theming, user settings, full screen support, integrated terminal and numerous other features

Let’s see how it works:

The process is very simple. First step download de file in this link

After download the next step is to extract the files in a folder wherever you want and run the sqlops file.

My first impression is quite good. A good visual tool to work with SQL Server on Linux. I like the execution plan view and how to manage the databases.



This slideshow requires JavaScript.

Comparing with SQL Server Management Studio I miss some options such as:

  • LinkedServer Objects
  • Maintenance Plans
  • Replication
  • Database Mail
  • Extended Events

The conclusion is I really can work on that and run all my scripts and create objects like linkedservers, extended events or replications. That helps a lot to try a new platform concepts.


Script to get the tables size in SQL Server

A simple script to know how much space the tables are taking from the disk.

, AS SchemaName
, p.rows AS RowCounts
, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalMB
, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedMB
, CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
, p.rows
ORDER BY 5 DESC; Continue reading Script to get the tables size in SQL Server

How Update works?

Update data in SQL Server is a simple task. Using the command update table set column = value where column = value. Don’t forget the where clause :).

But, what SQL Server does internally? Delete plus Insert? Modify? Well, the answer depends.

Let’s see examples how that works.

First, create a database and table for the test.


As you can see, I didn’t create an index so I will update a heap table. Before update the row, I will drop the log register and then update the row I want.


Ok, now the result of the DBCC LOG


As we can see, the result is a modified row in the log, what about using an index? Creating a nonclustered index in the column I’m updating.



The result is very different, now we can see a modified row in the heap and in the nonclustered index I got a delete/insert row. Very nice!

What if was a clustered index?


I dropped the nonclustered index and created a clustered and result is:


Again, only the modified row in the index clustered. And, if we try to update our key in the clustered index?



I got the result with delete/insert as I was expecting because I changed the key like the nonclustered index. Now, if I try to update the row with the same value, what SQL Server will do?


Nothing, no change at all.

SQL Examiner Tool

I like tools and I’ve used a couple of them. I had the opportunity to try SQL Examiner to compare database structures and even though compare data. The tool is quite simple to configure the options.

Talking about comparison, well we compare one database with another. Imagine a situation, there are two environments (development and test) and we must deploy the changes from one to another.

Let’s see step by step how it works

The steps are quite simple, need to select source and target type (Microsoft SQL Server, Oracle, MySQL, PostgreSQL, SQL Azure). After that, define the servers name, the authentication mode and the database. Below I’m showing how to connect in 2 local instances I have on my local computer.


After that, the SQL Examiner will compare what differences exist between databases.Now, I’d say I was expecting my objects differences and I got a great feature. In the image below the SQL Examiner is comparing the database options as well.

This is fantastic, the tool is showing different compatibility levels and it can be very helpful if you are doing performance tests, for example, the behavior is very different in this case. Also, I can compare my production environment and keep the database configurations equal in all environments.


Great tool, simple and efficient.

10 Configurations Best Practices

downloadThe first checklist before start using any application using SQL Server should be the default configurations.

What configurations I used to change?

  1. Max Server Memory: SQL Server will grab all memory available and that will cause concurrency with the OS
  2. MAXDOP: for OLTP also yes, the good thing is to use it
  3. Cost Threshold for Parallelism: the default value is too low, and the number will depend on the environment, I used to monitor and change as needed. I use to start with 40
  4. Model File Sizes: the model database will provide information when need to create more databases. (initial size and autogrowth are way too small and need to avoid file system fragmentation)
  5. Tempdb Files: look how many CPUs in the server and if the number is less than 8, so would be the number of CPUs, if it is more, so, start with 8 and the files should be the same size and the same autogrowth.
  6. Database Mail: used to send alerts, very important to get notified when something bad happens.
  7. Backup Compression: the benefit will be much more than the CPU cost.
  8. Remote Dedicated Administrator Connection: provides dedicated CPU, memory, and scheduler and by default only works via RPD or physically on server
  9. Maintenance: start as soon as possible with checkdb, index maintenance, statistics, backups. In this case, I like Ola Hallengren SQL Server solution.
  10. The last but not least, Security: this is the most important in the list, user accounts or even DBAs as sysadmin, look my tip in MSSQLTips. In addition, disable sa, disable unnecessary logins.