SQL Server According to Bob

Two guys who work on SQL Server

Category: How It Works

How It Works: Bulk Insert (BCP.exe) – Injecting a Sort Operation

Discussion on why BCP/Bulk Insert injects a sort into the query plan

Read More

How It Works: SQL Server Locking WAIT_WITH_LOW_PRIORITY

How It Works: SQL Server Locking WAIT_WITH_LOW_PRIORITY – How does it avoid blocking other lock requests.

Read More

How It Works: TSQL TRY/CATCH Behavior Fooled Me

If I would have simply read the documentation (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15) the answer to my problem is answered.

Read More

SQL Server BDC Hints and Tips: VDI Seeding for High Availability

A high availability SQL Server BDC uses the Virtual Device Interface (VDI) to seed databases to secondary replicas.

Read More

SQL Server BDC Hints and Tips: Contained AGs, Ports, containedag_msdb, logs, …

This blog post focuses on connecting to the SQL Server BDC, some helpful log files and utility outputs. Understanding a few basics about a SQL Server BDC and the Contained Availability Group (containedag) makes managing and troubleshooting easier.

Read More

SQL Server dbcc clonedatabase – Gotcha

The dbcc clonedatabase is a handy command to clone metadata and information such as query disk store. Then you can use the database for various activities …

Read More

SQL Server BDC Hints and Tips: Changing Grafana/Kibana Display Time

The Grafana and Kibana interfaces default to the local browser time zone on a SQL Server BDC cluster … how to adjust the time zone for Grafana and Kibana.

Read More

SQL Server I/O Basics Chapter #2

Additional details of SQL Server I/O patterns and capabilities.

Read More

SQL Server I/O Basics Chapter #1

Fundamental SQL Server I/O patterns and capabilities.

Read More

Using SQLBCP Native/Format File vs Text File and the BOM

BCP fundamentals when using format file and UNICODE data.

Read More

SQL Server 2019 Many No Longer Capture Mini-Dump (SQLDump####.mdmp)

I was looking at a SQL Server which had generated ten(10) SQLDump####.txt files but only a single(1) SQLDump####.mdmp file.  We introduced a new feature in SQL Server 2019 to save disk space for mini/crash dumps which can skip the suspension and capture for identical dump signatures, saving disk space and reducing the impact on the […]

Read More

How It Works: SQL Server Lock Iteration / Enumeration

When executing a query to enumerate the locks, such as select * from sys.dm_tran_locks, how does SQL Server scan the locks and avoid impacting the overall concurrency? I recently posted on aspects of the SQL Server Lock Manager and found the iteration of the locks interesting as I was stepping in the code. https://bobsql.com/how-it-works-sql-server-login_stats-sys-dm_exec_sessions https://bobsql.com/how-it-works-sql-server-lock-partitioning […]

Read More

How It Works: SQL Server LOGIN_STATS (sys.dm_exec_sessions)

To track the login statistical information, enable the sp_configure value ‘common criteria compliance enabled‘ setting and restart SQL Server. The sys.dm_exec_sessions DMV is one way to query the statistical login information. The rows shown in this example belong to the sa user on my system. The values are different because the values are obtained when […]

Read More

How It Works: SQL Server Lock Partitioning

Previous posts have discussed partitioning, for example, a partitioned memory object https://blogs.msdn.microsoft.com/psssql/2011/09/01/sql-server-20082008-r2-on-newer-machines-with-more-than-8-cpus-presented-per-numa-node-may-need-trace-flag-8048/ and how a latch maintains the desired access (exclusive, shared, …) https://blogs.msdn.microsoft.com/psssql/2009/01/28/hot-it-works-sql-server-superlatching-sub-latches/ Quick Refresher Over Partitioned Protection/Locking Acquiring shared access requires only the local partition be acquired (lightweight scalability) Acquiring exclusive access requires all partitions be acquired (heavier and can be slower) Shared […]

Read More

How It Works: SQL Server Deadlock Trace Flag 1222 Output

  The trace flag 1222 can be very powerful and helpful in tracking down the cause of a deadlock when used correctly. This week I was tracking down a blocking situation, which I expected the lock monitor to resolve as a deadlock.   The test in question is designed to cause large amounts of deadlocks in […]

Read More