Before you drop a foreign key to speed up a load, look at what it costs. This walk-through shows the Assert, the two parent lookups, and the trust trap.
SQL SERVER – Natively Compiled Stored Procedures and Location of Compiler
I recently implemented a Memory Optimized Table solution at one of the large financial organizations while working on Natively Compiled Stored Procedures.
Error 41368: Memory-Optimized Tables and Transactions
Error 41368 appears when an explicit transaction reads a memory-optimized table at the read committed level. This post reproduces the error, fixes it with a table hint or a database option, and shows the two related errors that the fixes do not cure. It also answers what the option changes for other tables.
Enforcing Formats With a REGEXP_LIKE Check Constraint
Stop badly shaped product codes before they reach a report. A short, tested walk-through of anchors, NULLs and adding the rule to old data.
How Much Memory is in Locked Pages? – Interview Question of the Week #228
Question: How Much Memory is in Locked Pages? Answer: Before we see the answer to this question, I suggest you read the blog post here first.
SQL SERVER – Script: Find Last System / Operating System Reboot or Restart Time
Sometimes we need to find last reboot time of the server via some script. In this blog, I am sharing the script to find the last system reboot time.
SQL SERVER – FIX: Msg 7416 – Access to the Remote Server is Denied Because No Login-mapping Exists
In this blog we would talk about fixing error 7416 – Access to the remote server is denied because no login-mapping exists.
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT: How to Check It Is On
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT is a database option, so DBCC USEROPTIONS never shows it. This post reads the option from sys.databases and from DATABASEPROPERTYEX, turns it on in a demo database and finds every database that has it. It also shows a guarded script you can reuse in deployments.
Indexes and SELECT Queries: A Coffee Mug, Tested
Indexes and SELECT queries met on a coffee mug from my friend Harvey Powell. Its two sides say indexes slow down SELECT queries and to drop unused indexes. The tests here show when a forced index reads 46 times more pages, what the usage counters report for an unused index, and why zero reads did not mean zero purpose.
SQL SERVER – 3 Questions: An Index Reduces Performance of SELECT Queries
An index can reduce the Performance of SELECT Queries when it changes a particular plan unfavorably. Readers asked three follow-up questions.