SQL Server : large RAM and DB Checkpointing

SQL Server : large RAM and DB Checkpointing   Hi everyone, This post’s purpose is to establish a summary of the specific behaviors with relation to DB Checkpoint that may happen within SQL Server when running with a large quantity of allocated memory and when applicable, how to best address them. SQL Server 2016 improves…


SQL Server Parallel Query Placement Decision Logic

Recently I had a conversion with (Jonathan Kehayias jonathan@sqlskills.com) about the placement of workers belonging to a parallel query.   As I asked around and reviewed the code I quickly found that the assumption is still ‘Least Loaded Node’ but this changed in SQL Server 2012 and broad awareness for both our support engineers and customers…

2

MultiSubnet = TRUE Is Now Default Behavior

I get to be the a good new messenger today.    We have made changes to the SQL Server Client Provider.  The provider detects when multiple IP addresses are present for a listener.   The links below detail the behavior making it easier for your multi-subnet AlwaysOn deployments. Improved MultiSubnet Listener Behavior With Newly Released SQL Client…

1

Are My Statistics Correct?

The question is often “Are my statistics up-to-date?” which can be a bit misleading.   I can make sure I have up-to-date statistics but the statistics may not be accurate.  I recently engaged in an issue where the statistics were rebuilt nightly.   A maintenance job change had been made moving from FULLSCAN to WITH SAMPLE statistics…

1

Sparse Files – Supported on both NTFS and REFS

The history of sparse file support capabilities has lead to confusion that I would like to clear up. Sparse files ARE SUPPORTED on NTFS and REFS.    SQL Server 2014 takes full advantage of the sparse support for online DBCC and database snapshots. There are older blogs indicating limited support for sparse files on REFS that…

1

How It Works: SQL Server (SQLNCLI11) ODBC Driver–Keyset Cursor

This blog is based on SQL Server 2014 CU7’s updated release of SQLNCLI11 and MSSQLODBC drivers. The basics behind KEYSET cursor behavior are described here: https://msdn.microsoft.com/en-us/library/windows/desktop/ms675119(v=vs.85).aspx  The critical part of the referenced link is the keyset cursors ability to see changes in the data.   When you open the cursor the sp_cursoropen or sp_cursorprepexec procedure is…

0

Operating System Error (665 – File System Limitation) Not just for DBCC Anymore

The operating system error 665, indicating a file system limitation has been reached continues to gain momentum beyond DBCC snapshot files.    Over the last ~18 months I have reproduced issues with standard database files, BCP output, backups and others. We have posted previous blogs talking about the NTFS attribute design and associated limitations (665) as…

5

XEvent Timestamp is a large integer value not the expected datatime value

The timestamp column for XEvent is stored internally as an offset from the start of the trace.   The XEvent header contains the starting, UTC time and each event stores the offset in ticks from the value stored in the header. On a system where the time is adjusted, for example daylight savings time falls backward,…

1

Understanding SQL Server’s Spatial Precision Filtering

A spatial index is not precise on its own. The spatial index is grid design requiring a precision filter as part of the query plan. In this blog I will provide a high level (10,000 foot) overview of the design. The spatial index overlays a series of grids. If the shape has any area (representation)…

1

How It Works: MAX DOP Level and Parallel Index Builds

I have been working on an issue where rebuilding an index leads to additional fragmentation.   Using XEvents I debugged the page allocations and writes and was able to narrow in on the behavior. There are lots of factors to take into account when rebuilding the index.   I was able to break down the behavior to…

4