The Curious Engineer

All blogs / Databases

SQL Database Engine Blog

learn.microsoft.com/en-us/archive/blogs/sqlserverstorageengine

This is the blog of the engineering team managing and developing the Database Engine for SQL Server and Azure SQL Database

DatabasesPerformance / optimizationMicrosoft blogsMSDN archive

’10’15’20’25
Posts
388 from 17 May 2006 to 28 Mar 2019
Posting
archived, read-only
Kind
company blog
Feed
https://learn.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/feed.xml

2019 12

  1. Our new Azure SQL Database blog site is live
  2. Identify log write limits on Azure SQL Managed Instance using QPI library
  3. Running Azure CosmosDB queries from SQL Server using ODBC driver
  4. Monitor local storage usage on General Purpose Azure SQL Managed Instance
  5. Sending resource alerts on Managed Instance using db_mail
  6. Analyzing wait statistics on Managed Instance
  7. Reduced recompilations for workloads using temporary tables across multiple scopes
  8. Measuring file io performance on Managed Instance using QPI
  9. We are moving!
  10. Azure Stream Analytics now supports Azure SQL Database as reference data input
  11. Getting started with Azure SQL Managed Instance
  12. Recreate dropped database on Azure SQL Managed Instance

2018 64

  1. Azure SQL Managed Instance Business Critical tier is Generally Available
  2. Modify Azure SQL Database Managed Instance using Azure CLI
  3. Create Azure SQL Managed Instance using Azure CLI
  4. Diagnostic Data for Synchronous Statistics Update Blocking
  5. What Azure permissions are required to create SQL Managed Instance?
  6. Introducing Scalar UDF Inlining
  7. Meet the Azure SQL Database team at PASS Summit 2018
  8. SQL Server IaaS Extension Query Service for SQL Server on Azure VM
  9. Announcing Public Preview of Accelerated Database Recovery
  10. September 2018 Leaderboard of Database Systems contributors on MSDN
  11. Analysing inventory of Managed Instances using T-SQL
  12. August 2018 Leaderboard of Database Systems contributors on MSDN
  13. Storage Configuration Guidelines for SQL Server on Azure VM
  14. Introducing Batch Mode on Rowstore
  15. The easiest way to create Azure VM that can access your Azure SQL Managed Instance
  16. Persisting job history in Azure SQL Managed Instance
  17. Meet the Azure SQL Database team at Microsoft Ignite Conference
  18. What is built-in intelligence in Azure SQL Database?
  19. Working with Azure SQL Managed Instance using Azure CLI
  20. Sending emails in Azure SQL Managed Instance
  21. Create linked server to readable secondary replica in Managed Instance Business Critical service tier
  22. Reaching Azure disk storage limit on General Purpose Azure SQL Database Managed Instance
  23. Database ownership chaining in Azure SQL Managed Instance
  24. July 2018 Leaderboard of Database Systems contributors on MSDN
  25. Pearl Abyss: Massive Scale using Azure SQL Database
  26. Approximate Count Distinct enters Public Preview in Azure SQL Database
  27. Public Preview of Row Mode Memory Grant Feedback in Azure SQL Database
  28. Public Preview of Table Variable Deferred Compilation in Azure SQL Database
  29. Validate your Azure network environment for SQL Managed Instance
  30. Using custom types as arrays in T-SQL
  31. June 2018 Leaderboard of Database Systems contributors on MSDN
  32. Deploy Azure SQL Managed Instance network environment using ARM
  33. Point-in-time restore of a database on Azure SQL Managed Instance using AzureRm.Sql PowerShell library
  34. Quick-start script: Create Azure SQL Managed Instance using PowerShell library
  35. May 2018 Leaderboard of Database Systems contributors on MSDN
  36. Quick-start script: Setup Azure network environment for Azure SQL Managed Instance
  37. Cross-instance point-in-time restore in Azure SQL Database Managed Instance
  38. Clustered Columnstore Index: Massively Parallel Trickle Insert
  39. Create database on Azure SQL Managed Instance using Azure.Rm PowerShell library
  40. Change size of Azure SQL Managed Instance using PowerShell
  41. Updating Azure SQL Managed Instance properties using ARM templates
  42. April 2018 Leaderboard of Database Systems contributors on MSDN
  43. Creating Azure SQL Managed Instance using ARM templates
  44. Meet the Azure SQL Database team at Microsoft Build Conference
  45. How Azure SQL Database makes database management easier?
  46. Azure SQL Database – scalability
  47. Azure SQL Database – the database engine that cannot die
  48. Calling Http endpoints in T-SQL using CURL extension
  49. March 2018 Leaderboard of Database Systems contributors on MSDN
  50. Monitoring performance of natively compiled stored procedures - database-scoped configuration options
  51. How to configure network for Azure SQL Managed Instance
  52. Demo - Azure SQL Database Managed Instance in action
  53. How to configure tempdb in Azure SQL Managed Instance(preview)
  54. What is Azure SQL Database Managed Instance?
  55. February 2018 Leaderboard of Database Systems contributors on MSDN
  56. Migrating to Azure SQL Database with Zero Downtime for Read-Only Workloads
  57. New Windows container images of SQL Server 2017 on Windows Server 1709!
  58. January 2018 Leaderboard of Database Systems contributors on MSDN
  59. JSON in SQL Server - Use cases
  60. Simplify REST API development for modern Single-page apps with SQL Server
  61. Simplify data access using de-normalized models
  62. Working with Azure IoT data in Azure SQL Database
  63. Compatibility Level 140 is now the default for Azure SQL Database
  64. Database scoped optimizing for ad hoc workloads

2017 44

  1. Injecting JSON objects into existing JSON text with JSON_MODIFY function
  2. Transitive closure clustering with CLR and JSON
  3. Storing JSON documents in SQL Database
  4. Building SQL REST API back-end for Angular apps
  5. ASP.NET Session State with SQL Server In-Memory OLTP
  6. JSON vs CLR UDT - performance comparison
  7. In-Memory OLTP Indexes - Part 2: Performance Troubleshooting Guide.
  8. JSON parsing is faster than Spatial equivalent
  9. JSON parsing 10x faster than XML parsing
  10. Azure SQL Database: Ingesting 1.4 million sustained rows per second with In-Memory OLTP & Columnstore Index
  11. In-Memory OLTP Indexes - Part 1: Recommendations.
  12. In-Memory OLTP Updated Overview and Case Studies
  13. Sentiment analysis with Python in SQL Server Machine Learning Services
  14. Configure Azure Storage for SQL Database backups
  15. Native database backup in Azure SQL Managed Instance
  16. SQL Server 2017 available for download
  17. Announcing general availability of Native Scoring using PREDICT function in Azure SQL Database
  18. Default compatibility level 140 for Azure SQL databases
  19. Demo: Identify and fix plan change regression in SQL Server 2017 RC1
  20. Monitoring automatic tuning actions using XEvents
  21. Public Preview of Compatibility Level 140 for Azure SQL Database
  22. “What are you waiting for?” – Introducing wait stats support in Query Store
  23. Azure SQL databases in logical servers, elastic pools, and managed instances
  24. What is plan regression in SQL Server?
  25. Use WITH clause in OPENJSON to improve parsing performance
  26. Automatic plan correction in SQL Server 2017
  27. Automatic index management in Azure SQL database
  28. "You Shall Not Regress!!!" - How SQL Server 2017 prevents plan regressions?
  29. How to find query plan choice regressions with SQL Server 2017 CTP2
  30. Introducing Interleaved Execution for Multi-Statement Table Valued Functions
  31. Introducing Batch Mode Adaptive Joins
  32. Comparing performance of data access libraries using StackExchange/Dapper benchmark
  33. Loading files from Azure Blob Storage into Azure SQL Database
  34. SQL Server 2016 Developer Edition in Windows Containers
  35. Parsing 4GB JSON with SQL Server
  36. Import and analyze IIS Log files using SQL Server
  37. Extreme 25x compression of JSON data using CLUSTERED COLUMNSTORE INDEXES
  38. Exporting tables from SQL Server in json line-delimited format using BCP.exe
  39. JSON data in clustered column store indexes
  40. Columnstore Index Performance: SQL Server 2016 – Multiple Aggregates
  41. SQL Server R Services - Why we built it
  42. Columnstore Index Performance: Batch Mode Execution
  43. Columnstore Index Performance: Rowgroup Elimination
  44. Columnstore Index Perfomance: Column Elimination

2016 65

  1. Transaction Commit latency acceleration using Storage Class Memory in Windows Server 2016/SQL Server 2016 SP1
  2. Introducing Batch Mode Adaptive Memory Grant Feedback
  3. Columnstore Index: Standard and Express editions with SQL Server 2016 SP1
  4. In-Memory OLTP in Standard and Express editions, with SQL Server 2016 SP1
  5. CREATE OR ALTER - another great language enhancement in SQL Server 2016 SP1
  6. Columnstore Index: Why do I need to create clustered columnstore Index on In-Memory OLTP- tables for Analytics?
  7. In-Memory OLTP: Is your database just in memory or actually optimized for memory?
  8. Columnstore Index: Which Columnstore Index is right for my workload?
  9. Columnstore index: Why do we refer to it as In-Memory Analytics?
  10. SQL Server 2016 Express Edition in Windows containers
  11. Columnstore Index- Should I partition my columnstore Index?
  12. Columnstore Index: In-Memory Analytics (i.e. columnstore index) Videos from Ignite 2016
  13. In-Memory OLTP Videos: What it is and When/How to use it
  14. SQL Server 2016 - It Just Runs Faster: Always On Availability Groups Turbocharged
  15. Implementing Product Catalogs in SQL Server and Azure SQL database
  16. JSON is Generally available in Azure Sql Database!
  17. Columnstore Index: Parallel load into clustered columnstore index from staging table
  18. Columnstore Index: Differences between Clustered/Nonclustered Columnstore Index
  19. Columnstore Index: Differences between Columnstore Index vs BTree index
  20. Increased Memory Size for In-Memory OLTP in SQL Server 2016
  21. IoT Smart Grid code sample
  22. Announcing availability of SQL Server 2014 Express Docker image
  23. IoT code sample - loading messages from Event Hub into Azure SQL Database
  24. Appending JSON arrays using JSON_MODIFY function
  25. Handling inheritance with JSON
  26. Encapsulate JSON parsing with inline table value functions
  27. In-Memory OLTP Performance Demo Available for Download
  28. Query Optimizer Additions in SQL Server 2016
  29. Improved Query Performance with Compatibility Level 130 in Azure SQL Database
  30. Database Scoped Configuration
  31. Using DB Compatibility Level 130 with Old CE in SQL Server 2016
  32. Increased nonclustered index key size with SQL Server 2016
  33. JSON is available in Azure SQL Database
  34. A Technical Case Study: High Speed IoT Data Ingestion Using In-Memory OLTP in Azure
  35. What's new for In-Memory OLTP in SQL Server 2016 since CTP3
  36. Generate stored procedure that imports array of JSON object in table
  37. Loading JSON files from Azure File Storage
  38. Moving data from relational to JSON columns and vice versa
  39. Loading line-delimited JSON files in SQL Server 2016
  40. SQL Server in Windows Containers
  41. Improving temp table and table variable performance using memory optimization
  42. JSON support in SQL Server - Details, tips and tricks
  43. Columnstore Index: A short video showcasing enhancements in SQL Server 2016
  44. Real-Time Operational Analytics: A short video on the value proposition in SQL Server 2016
  45. Columnstore Index: How does SQL Server delivers Industry leading performance for Analytic Queries?
  46. Columnstore Index: What is new in SQL Server 2016?
  47. Columnstore Index: Overview and Value Proposition
  48. Columnstore Index Merge Policy for REORGANIZE
  49. Columnstore Index Defragmentation using REORGANIZE Command
  50. Real-Time Operational Analytics: Memory-Optimized Tables and Columnstore Index
  51. Real-Time Operational Analytics: Compression Delay option with NCCI and the performance
  52. Real-Time Operational Analytics: Compression Delay Option for Nonclustered Columnstore Index (NCCI)
  53. Real-Time Operational Analytics: Filtered nonclustered columnstore index (NCCI)
  54. Real-Time Operational Analytics: DML operations and nonclustered columnstore index (NCCI) in SQL Server 2016
  55. Upsert JSON documents in SQL Server 2016
  56. SQL Server 2016 - Making DBCC CHECKDB Faster
  57. Real-Time Operational Analytics: Simple example using nonclustered clustered columnstore index (NCCI) in SQL Server 2016
  58. Real-Time Operational Analytics - Overview nonclustered columnstore index (NCCI)
  59. Clustered columnstore Index: Data Load Optimization - Parallel Bulk Import
  60. Columnstore Index: Scan and parallelism
  61. JSON parsing - performance comparison
  62. Returning spatial data in GeoJSON format - Part 2
  63. Clustered Columnstore Index: Data Load Optimizations - Minimal Logging
  64. Loading GeoJSON data into SQL Server
  65. Returning spatial data in GeoJSON format - Part 1

2015 31

  1. Indexing data in JSON product catalogs
  2. Designing Product Catalogs in SQL Server using JSON
  3. WITHOUT_ARRAY_WRAPPER - New important change in FOR JSON
  4. Columnstore Index: Real-Time Operational Analytics using In-Memory Technology
  5. CHECKSUM and BINARY_CHECKSUM might not return unique values for different inputs
  6. Create relational view over JSON text
  7. Columnstore Index: Speeding up analytics in SQL Server 2016
  8. Built-in functions for compression/decompression in SQL Server 2016
  9. AdventureWorksCTP3 JSON Sample
  10. Using non-relational models in SQL Server
  11. De-normalize database schema using JSON
  12. Query JSON data
  13. Log analysis with SQL Server
  14. Storing JSON in SQL Server
  15. In-Memory OLTP - what's new in SQL2016 CTP3
  16. FOR JSON performance - simple experiment
  17. Generate serie of numbers in SQL Server 2016 using OPENJSON
  18. DROP IF EXISTS - new thing in SQL Server 2016
  19. OPENJSON - one of best ways to select rows by list of ids
  20. Samples for SQL Server 2016 CTP3
  21. Returning child rows formatted as JSON in SQL Server queries
  22. Importing JSON files into SQL Server using OPENROWSET (BULK)
  23. Querying JSON documents in Sql Server 2016 and DocumentDB
  24. Parsing GeoJSON format in Sql Server 2016
  25. Indexing JSON documents in Sql Server 2016
  26. OPENJSON - The easiest way to import JSON text into table
  27. Loading non-relational data formatted as JSON text
  28. Clustered Columnstore Index: Enforcing uniqueness constraint in table
  29. Passing arrays to T-SQL procedures as JSON
  30. Combining relational and NoSQL concepts in SQL Server
  31. FOR JSON clause in SQL Server 2016 CTP2

2014 4

  1. Clustered Column Store Index: Bulk Loading the Data
  2. Clustered Column Store Index: Concurrency with INSERT Operations
  3. Clustered Column Store Index: Concurrency and Isolation Levels
  4. Clustered Column Store: Factors that impact size of a RowGroup

2012 1

  1. AlwaysOn: Comparing Readable Secondary with the similar functionality available in DB2

2011 17

  1. AlwaysOn: I just enabled Readable Secondary but my query is blocked?
  2. AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot
  3. AlwaysOn: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica
  4. AlwaysOn: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation
  5. AlwaysOn: Minimizing blocking of REDO thread when running reporting workload on Secondary Replica
  6. AlwaysOn: Impact on the primary workload when you run reporting workload on the secondary replica
  7. AlwaysOn: Readable Secondary and data latency
  8. AlwaysOn: Why there are two options to enable a secondary replica for read workload?
  9. AlwaysOn: Setting up Readable Seconary Replica
  10. AlwaysOn: Value Proposition of Readable Secondary
  11. SQL Server Team is hiring
  12. Data Compression and Fill Factor
  13. Concurrency Series: My application was running fine yesterday but why is it blocking today?
  14. Concurrency Series: Minimizing blocking between updaters
  15. Concurrency Series: Why do I get blocked when no one has locked the row(s) being queried?
  16. Concurrency Series: Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?
  17. Concurrency Series: Basics of Transaction Isolation Levels

2010 5

  1. Concurrency Series: Common Concurrency Issues with Applications
  2. Minimal logging and MERGE statement
  3. Backup compression and Virtual Device Interface (VDI)
  4. Performance improvement by orders of magnitude when merging partitions in SQL Server 2008R2
  5. Improvement in minimizing lockhash key collisions in SQL Server 2008R2 and its impact on concurrency

2009 11

  1. Changes to sp_estimate_data_compression_savings stored procedure in SQL2008R2
  2. A Unicode Compression example
  3. Unicode Compression in SQL Server 2008R2
  4. Customer feedback on Data Compression
  5. Update on data compression performance/space-savings and links to published white papers
  6. TempDB Monitoring and Troubleshooting: Out of Space
  7. TempDB Monitoring and Troubleshooting: DDL Bottleneck
  8. TempDB Monitoring and Troubleshooting: Allocation Bottleneck
  9. TempDB Monitoring and Troubleshooting: IO Bottleneck
  10. Managing TempDB in SQL Server: TempDB Configuration
  11. What is allocation bottleneck?

2008 25

  1. Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)
  2. Managing TempDB in SQL Server: TempDB Basics (Version Store: logical structure)
  3. Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)
  4. Managing TempDB in SQL Server: TempDB Basics (Version Store: Why do we need it?)
  5. Managing TempDB in SQL Server:TempDB Basics (cont..)
  6. Managing TempDB in SQL Server
  7. DML operations on a HEAP and compression
  8. Enabling compression on a HEAP
  9. New update on minimal logging for SQL Server 2008
  10. Does data compression lead to more fragmentation?
  11. Enabling FILESTREAM post SQL2008 Setup - a Known Issue in SQL Config Manager
  12. TempDB:: Table variable vs local temporary table
  13. Overhead of Row Versioning
  14. CHECKSUM and Tempdb
  15. Minimal Logging changes in SQL Server 2008 (part-3)
  16. Minimal Logging changes in SQL Server 2008 (part-2)
  17. Minimal Logging changes in SQL Server 2008
  18. FILESTREAM Configuration and Setup Changes in SQL Server 2008 February CTP
  19. Example: Index fragmentation with insert/updates, measuring it and fixing it
  20. Bulk Import Optimizations (Minimal Logging)
  21. What are the Bulk Import Optimizations?
  22. Why Bulk Import?
  23. Compression Strategies
  24. Details on page compression (page-dictionary)
  25. Details on PAGE compression (column-prefix)

2007 54

  1. Data Compression will be available in CTP-6
  2. Estimating the space savings with data compression
  3. Types of data compression in SQL Server 2008
  4. Why not use compressed disk files or disk volumes?
  5. When is too much success a bad thing?
  6. Fuzzy backups and RESTORE DATABASE WITH STOPAT
  7. Data compression techniques and trade offs
  8. Data Compression: Why Do we need it?
  9. ok - really the last blog post - Mondays, Mystic, and a Museum
  10. Splits and unions...
  11. TechEd session video available...
  12. What happens to non-clustered indexes when the table structure is changed?
  13. Database mirroring questions from TechEd: failovers and partner timeouts
  14. Katmai == SQL Server 2008 == available to start playing with!
  15. SQL Server's 'black-box' flight recorder
  16. White Paper: Reducing Database Size by Using Vardecimal Storage Format
  17. First TechEd of the year coming up!
  18. Would you like to join the Storage Engine PM team?
  19. Naked Tour of Australia
  20. Quick list of VLDB maintenance best practices
  21. What's the difference between database version and database compatibility level?
  22. How having too many filegroups can affect recovery time
  23. Can you mix-n-match backup devices?
  24. And an example corrupt 2000 database to play with
  25. How can you tell if an index is being used?
  26. Example corrupt database to play with and some backup/restore things to try
  27. How to avoid using shrink in SQL Server 2005?
  28. SP2 Maintenance Plan bugs fixed
  29. VLDB Maintenance - quick addendum
  30. VLDB Maintenance Practices and Problems?
  31. SSWUGtv interview with Paul and Kimberly
  32. How does your schema affect your shrink run-time?
  33. Where are all the whitepapers?
  34. Turn AUTO_SHRINK off!!
  35. Easy way to create a corrupt database for testing
  36. New whitepaper on Online Indexing Operations
  37. Can you INCLUDE columns of type TEXT, NTEXT, or IMAGE?
  38. How to create fake statistics for testing query plans
  39. More on Service Level Agreements...
  40. Oh no – my backup is corrupt too! Help!
  41. Using Visual Studio’s Code Model objects for code base understanding
  42. CHECKDB, User Groups and .NET Rocks!
  43. SQL Server 2005 Best Practices Analyzer CTP released
  44. TechEd interview video
  45. Wow - over 40 blogs from the SQL Product Team
  46. How to move databases using detach/attach functions?
  47. How long does *your* CHECKDB take?
  48. Three upcoming events in March to learn about HA/DR and DB Maintenance
  49. CHECKDB (Part 8): Can repair fix everything?
  50. CHECKDB (Part 7): How long will CHECKDB take to run?
  51. Fixing damaged pages using page restore or manual inserts
  52. Read-Only filegroups and Locking
  53. Data row before and after vardecimal storage format
  54. Boundary conditions for enabling vardecimal storage format

2006 55

  1. Vardecimal Storage Format and its implications on Backup/Recovery
  2. More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns
  3. Enabling vardecimal storage format
  4. How to give feedback on SQL Server 2005?
  5. Estimating the space savings with vardecimal storage format
  6. Reducing the Size of your Database in SQL Server 2005/SP2
  7. SQL Server 2005 SP2 CTP2 released
  8. When can allocation order scans be used?
  9. CHECKDB (Part 6): Consistency checking options for a VLDB
  10. SQLIOSim available for download
  11. Skipping rows in the datafile that contains NULL for a column that does not allow NULLs
  12. What Master???
  13. CHECKDB (Part 5): What does CHECKDB really do? (part 4 of 4)
  14. Fragmentation (part 4): what are heaps?
  15. CHECKDB (Part 4): What does CHECKDB really do? (part 3 of 4)
  16. Don't try this at home kids... (data recovery using DBCC PAGE)
  17. Can't I ever get a guarantee?
  18. Scale-up technology
  19. CHECKDB (Part 3): What does CHECKDB really do? (2 of 4)
  20. Under the covers: GAM, SGAM, and PFS pages
  21. Checksum in SQL2005
  22. Why is the NULL bitmap in a record an optimization?
  23. Fragmentation (part 3): What are extents?
  24. Fixed the problem with images in blog posts..
  25. Under the covers: IAM chains and allocation units in SQL Server 2005
  26. Under the covers: IAM chains in SQL Server 2000
  27. Why are those IOs taking so long?
  28. How can you prove that hardware is the problem? Use stress to reduce stress...
  29. What makes Paul tick?
  30. Fun with execution context switching
  31. Two new white papers by SQL Server Storage Engine Team
  32. Why you should NOT optimize your backup strategy for backup speed
  33. CHECKDB (Part 2): What does CHECKDB really do? (1 of 4)
  34. Another disaster recovery 'advice' gem...
  35. Do you need to run CHECKDB because of SQL Server?
  36. The best part of TechEd was meeting people...
  37. Which part of 'REPAIR_ALLOW_DATA_LOSS' isn't clear?
  38. When should you rebuild the transaction log?
  39. Just why are you backing up your database?
  40. Data files: To Autogrow Or Not To Autogrow?
  41. Here's a good reason not to run SHRINKDATABASE...
  42. We're at TechEd - stop by and see us!
  43. How to use DBCC PAGE
  44. New SQL Express Starter Kit and Sample Apps available on MSDN
  45. CHECKDB (Part 1): How does CHECKDB get a consistent view of the database?
  46. Importing UNIX data file
  47. Common bad advice around disaster recovery
  48. Repair vs Restore Dilemma
  49. How to recreate the msdb database in SQL Server 2005?
  50. Are you coming to TechEd? We'd like to see you there!
  51. New MSDN forum on Disaster Recovery and Availability
  52. Troubleshooting deadlocks in SQL2005
  53. Bulk Importing data with OPENROWSET in SQL2005
  54. Range locks
  55. Lock Escalation in SQL2005