Discussion on why BCP/Bulk Insert injects a sort into the query plan
Read MoreHow It Works: SQL Server Locking WAIT_WITH_LOW_PRIORITY – How does it avoid blocking other lock requests.
Read MoreSQL Server for Linux Performance Monitoring Improvements – Upgraded SQL Server PMDA
Read MoreFor SQL Server on Linux, there are additional logging capabilities …
Read MoreIf 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 MoreA high availability SQL Server BDC uses the Virtual Device Interface (VDI) to seed databases to secondary replicas.
Read MoreThis 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 MoreThe 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 MoreThe 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 MoreHow TEMPDB can impact the pod, evictions and disk space usage.
Read MoreIf you are new to Kubernetes (K8’s), like I am, you quickly learn that the servicing node’s journal holds a wealth of information.
Read MoreLearn how to troubleshoot SQL Server networking issues using network, XEvent and SNITracing.
Read MoreAdditional details of SQL Server I/O patterns and capabilities.
Read MoreFundamental SQL Server I/O patterns and capabilities.
Read MoreBCP fundamentals when using format file and UNICODE data.
Read MoreI 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 MoreI have previously tested and blogged about the NTFS, sparse, file system limitation error 665: https://blogs.msdn.microsoft.com/psssql/2015/06/10/operating-system-error-665-file-system-limitation-not-just-for-dbcc-anymore/ when running DBCC or using Snapshot databases with SQL Server. Recently a customer asked me if they would encounter the same limitation if they moved to Linux. The answer is not the same limitation and for EXT and XFS […]
Read MoreI was reviewing a test run this week that had more SQL 17830 (TCP 10054 / 0x2746) errors than I could explain from just kills or login timeouts. I started looking at the output in the connectivity ring buffer and the matching XEvent, error_reported event where error_number = 17830. I thought I would find my […]
Read MoreWhen 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 MoreTo 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 MorePrevious 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 MoreSQL Server will ship Azure SQL Database Edge: https://azure.microsoft.com/en-us/services/sql-database-edge With the announcement I can tell you more about one of the things we have been working on; SQL Server running on IoT Edge and Developer machines in under 500MB of memory. The effort goes beyond IoT Edge devices and extends to the common developer experience. […]
Read MoreYou need to make sure to order the events by event_sequence when doing analytics on the XEvent data. It has been proposed to me that you can use the causality tracking sequence as a sort target, which may work as long as you understand how the activity ids are associated with the parent and the […]
Read MoreSQL Server 2017 leverages up to 4 log writer workers, on hidden schedulers, to assist in transaction log processing activities. You may find the number of log write workers has been increased to 8 when running newer releases of SQL Server to accommodate larger systems. (Caution: Pre-release software may change before final release.) During SQL […]
Read MoreSQL Server 2017 introduced a small change to SQL Server’s default worker thread calculation, accounting for smaller environments. When running on smaller environments SQL Server reduces the worker target. For an X64 installation, using the sp_configure ‘max worker threads’ value of 0 SQL Server uses the following calculation. On small environments SQL Server always uses […]
Read MoreI was doing backups and clean-ups and ran across a couple of sample projects for XEvent and event_sequence processing I thought others might find helpful. – Enjoy! The sample code is provided “as is” and any express or implied warranties, including the implied warranties of merchantability and fitness for a particular purpose, are disclaimed. In […]
Read MoreWhen Creating, Renaming or Deleting (remove/unlinking) a file, Linux requires the direct parent directory to be synchronized. As documented in the manpage for fsync core changes to the directory require the directory itself to be synchronized. “Calling fsync does not ensure that the entry in the directory containing the file has also reached disk. For […]
Read MoreI was asked to validate the SQL Server behavior in light of the error condition involving fsync/fdatasync outlined in this article. An example of the error: The application does a buffered write and receives success. (This means data can be stored in file system cache, non-stable media.) An fsync/fdatasync is used to make sure the […]
Read MoreOverview SQL Server relies on Forced-Unit-Access (Fua) I/O subsystem capabilities to provide data durability, detailed in the following documents: SQL Server 2000 I/O Basic and SQL Server I/O Basics, Chapter 2 Durability: “In database systems, durability is the ACID property which guarantees transactions that have committed will survive permanently. For example, if a flight […]
Read MoreSQL Server Books Online documents Instant File Initialization and the associated security considerations. This blog highlights the underlying file system implementations and differences in behavior between Windows and Linux. SQL Server performs the following API calls when creating or extending (growing) data and log files. CreateFile – Create or open a file SetEndOfFile– Establish the […]
Read MoreToday I was tracking a large BCP.exe ‘IN’ operation and monitoring the sys.dm_exec_requests entries. The ‘BULK INSERT’ command entry appeared, showing the CPU, reads, writes, … but these values where getting reset from time to time. Here are 3 snapshots from my system. cpu_time total_elapsed_time writes session_id start_time command 1387 1396 32 51 2018-08-07 00:45:42.670 […]
Read MoreDylan (who kindly wrote up the contents of this blog for me) was modifying the tests for Active Directory Login activities. As Dylan and I reviewed the changes a specific behavior involving Active Directory Group Logins caught our attention. Imagine you have a group on your domain [CONTOSO\group] which has a member [CONTOSO\user], and the […]
Read MoreWindows provides an option for CreateFile to delete a file when the file is closed (FILE_FLAG_DELETE_ON_CLOSE.) Host Extension Handling Linux does not expose such an option as part of the open syscall. Instead the host extension remembers that the open request was made with the FILE_FLAG_ON_DELETE option and after closing a file issues the remove […]
Read MoreWhen starting SQL Server on Linux why are there two (2) sqlservr processes? systemctl status mssql-servermssql-server.service – Microsoft SQL Server Database Engine… CGroup: /system.slice/mssql-server.service ├─85829 /opt/mssql/bin/sqlservr <——— WATCHDOG | MONITOR └─85844 /opt/mssql/bin/sqlservr <——— SQLSERVER.EXE The simple answer is the first entry (85829) is not what you are used to on a Windows system as sqlservr.exe […]
Read MoreI have been asked several times about how to get a Performance Monitor like view on Linux. There are lots of Linux tools available (top, iotop, Grafana, and SQL Sentry just scratch the surface of available options) to monitor the Linux system. Allow me to share one such example to capture and monitor a system. […]
Read MoreWhen I graduated from college, one of the first computers I ever used was a MacIntosh. I loved the Mac, the user interface, and the overall footprint of that computer. I also started my career developing on UNIX systems with C++ and databases like Ingres. As I moved to other jobs, the PC was becoming […]
Read MoreWhat does improving resource usage on smaller machines really mean? https://support.microsoft.com/en-us/help/4078095 SQL Server is designed to scale across the spectrum of small machines to enterprise class servers. The changes made in SQL Server 2017 CU4 improve resource usage on smaller machines, such as a VM or Container used for testing and development. SQL Server always […]
Read MorePrior to SQL Server 2017 CU4 for Linux (Linux only) the generation of a sequential UUID may not function as expected. SQL Server’s NewSequentialId() calls the Windows API UuidCreateSequential, which is limited in SQLPAL, prior to the CU 4 update. SQL Server on Linux – CU4 advances the logic in the SQLPAL, in support […]
Read MoreI have the blessed opportunity to speak at many customer events. SQLBits is always one of my favorites because of the great community that attends and the opportunity for me to travel to a great city like London. Well, for SQLBits 2018, it is not just me. Microsoft engineering is actually sending quite a few […]
Read MoreI have found the term DTC is used in many ways so for the purposes of the discussion here are a few terms. When talking DTC, I am talking about the feature set, MSDTC as the OLE-TX implementation and XA for the X/Open XA implementation. DTC Distributed Transaction Coordination – Generic term MSDTC Microsoft specific […]
Read MoreI’ve had such a long, great history with the PASS Summit, Community, and all the great people that have run this organization. I owe a great debt to this community to help me build a brand and reputation for my passion and talks on SQL Server. Social Media was never really part of the equation […]
Read MoreBob Ward and I worked with our SQL Server Tool developers (thanks David) to enable ‘Quick XE Trace’ capabilities. The feature is available in the latest SQL Server Management Studio (SSMS) release. Despite the deprecation of SQL Profiler several years ago, as well as various documents and blogs pointing out the older trace facilities shortcomings […]
Read MoreSQL Server 2019 CTP2 SQL Server 2019 adds broad support for distributed transactions (both MSDTC/OLE-TX and XA) to SQL Server on Linux. SQL Server 2019 on Linux enables the MSDTC service in SQLPAL providing distributed transaction capabilities on Linux alongside our SQL Server on Windows counterpart, supporting MSDTC/OLE-TX and XA transaction types. You can […]
Read MoreUpdated Content! Please check out these new resources for SQL Server 2017 and SQL Server on Linux and Docker: Brk2045 upgrade sql server 2017 (on prem, iaa-s and paas) from Bob Ward Brk2051 sql server on linux and docker from Bob Ward With the 2017 PASS Summit approaching next week, I thought it would be […]
Read MorePlease check out a new blog post I created detailing how SQL Server 2017 is Fast, Built-In. Faster than you think running on Linux, using Columnstore, and with right features and configuration. How we keep you fast and tuned with Adaptive Query Processing and Automatic Tuning. And we are the fastest database everywhere you need […]
Read MoreScott Guthrie and Rohan Kumar officially made the announcement today at Ignite that SQL Server 2017 will be generally available on October 2nd, 2017. Check out ScottGu’s blog post at: https://azure.microsoft.com/en-us/blog/new-azure-advancements-remove-cloud-barriers-for-enterprises-at-ignite-2017 Rohan’s Blog post at: https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/25/microsoft-for-the-modern-data-estate/ Andrew Brust also wrote a very nice article detailing out all the great new features that come with SQL Server […]
Read MoreI ran into a scenario showing my session as suspended, awaiting command and blocked! My first thought was that this had to a bug. As many of us have documented, for years, a sleeping, awaiting command, session is a session waiting on the client to submit a TSQL command to the SQL Server. The […]
Read MoreThe 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 MoreI recently visited a customer onsite and presented to them topics on SQL Server 2016. After the talk, I opened up the floor for the audience to ask me questions. One question I got went like this “I’ve tried to restore a database on SQL Server using the WITH STATS option. When I run the […]
Read MoreI recently visited a customer onsite and presented to them topics on SQL Server 2016. After the talk, I opened up the floor for the audience to ask me questions. One question I got went like this “I’ve tried to restore a database on SQL Server using the WITH STATS option. When I run the […]
Read MoreAs pointed out in my previous post, debugging on Linux brings a few new twists to those of us used to the Windows debugging landscape. One of these twists is the need to produce a core dump. On Linux a common way to capture a dump is to generate a core dump (gcore, etc.) You […]
Read MoreScatter/gather capabilities allow more efficient memory to disk transfers reducing redundant memory copies, sorting and other activities applications may require to gain improved I/O performance. If my memory serves me correctly SQL Server started using the ReadFileScatter and WriteFileGather APIs in SQL Server 6.5 SP3. It may not have been this exact build but as […]
Read MoreYou are aware of our statements indicating “Microsoft Loves Linux.” Over the last couple of years the open source activities at Microsoft have accelerated all around me. Recently I blogged about the design of of the debugger bridge and the use of LLDB. In this post I want to highlight my recent debugging session into […]
Read MoreI’m a huge Sherlock Holmes fan (I’ve read all the books, watch Elementary on CBS every week, and loved the most recent season Four of Sherlock) so when I recently got a question about some unexplained behavior for SQL Server, I thought of the idea of posting some of these as I get and solve […]
Read MoreIn my last post I highlighted the marriage of PE and ELF images within the same process space to build SQL Server on Linux. In this post I will expand upon the dbgbridge component, as mentioned by Slava in his latest channel 9 video. The dbgbridge (Debugger Bridge) is a critical component in the SQL […]
Read MoreLast March I moved from 22 years in SQL Server support to the SQL Server development team, working on SQL Server on Linux project and reporting to Slava Oks. As Slava highlights in his recent blog post, he also contacted me in early 2015 to assist with supportability of SQL Server on Linux. I quickly […]
Read MoreBack in June, Bob Dorr gave you the thinking behind why SQL Server 2016 It Just Runs Faster due to changes in our SQL Server and NUMA configurations. Bob blogged about a new SQL Server 2016 feature called Auto Soft NUMA. As I’ve hit the road and talked more about SQL Server 2016: It Just […]
Read MoreNote: We received feedback that there was some confusion on us calling this functionality “tail of the log caching” because our documentation and prior history has referred to the tail of the log as the portion of the hardened log that has not been backed up. This feature is actually officially called Persisted Log Buffer […]
Read MoreBack in July, I told you about a new installation experience for SQL Server. One of our goals from the outset was to ensure we could iterate and enhance this experience as we obtained feedback from the community. Today, we are proud to announce Version 2 of the new SQL Server Installer. Here is the […]
Read MoreWhen we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). Performance issues surrounding Availability Groups typically were related to disk I/O or network speeds. As we […]
Read MoreYesterday I had a discussion with a customer and realized that the BCP, TDS pattern could use a bit of documentation. Customer Scenario/Question: I am using BCP.exe to import a large data set. BCP.exe takes 15 minutes to complete but I only see a few seconds for the BULK INSERT command when monitoring dm_exec_requests. What […]
Read MoreI was approached to revisit the details of how Error Message 833 is detected and reported. SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d). The OS file handle is 0x%p. The offset of the latest long I/O is: %#016I64x. […]
Read More