By Lori Brown | Miscellaneous , SQL Administration , SQL Server
Available in SQL 2012 & 2014 Enterprise Edition ColumnStore Indexes were introduced in SQL 2012 and are designed to improve performance in data warehouses. These indexes are good for batch processing and read only queries that use large data sets. Tables that are bulk loaded are good candidates. ColumnStore Indexes stores data in a columnar […]
Continue readingBy Lori Brown | Helpful Scripts , Miscellaneous , Performance Tuning , SQL Server , Tip of the Month
There are two new system views and two system views that have new columns added in SQL 2014. Most notable is sys.databases since this is widely used. sys.xml_indexes – Has 3 new columns: xml_index_type, xml_index_type_description, and path_id. https://msdn.microsoft.com/en-us/library/ms176003.aspx sys.databases – Has 3 new columns: is_auto_create_stats_incremental_on, is_query_store_on, and resource_pool_id. https://msdn.microsoft.com/en-us/library/ms178534.aspx sys.column_store_row_groups – New view. Provides clustered […]
Continue readingBy Lori Brown | Miscellaneous , SQL Administration , SQL Server
Available in SQL Server 2014 – All Editions Delayed Durabilty is a way to defer the acknowledgement of the transaction log records getting written to disk, i.e. transactions can continue without waiting, and SQL will assume that the log record *will* be written. You may know the ACID rules (Atomicity, Consistency, Isolation, Durability) that guarantee […]
Continue readingBy Lori Brown | SQL Administration , SQL Server
Available in SQL Server 2014 Enterprise Edition In-Memory OLTP is a new lock-free, latch-free, optimistic concurrency approach to storing data in memory. While it may seem reminiscent of DBCC PINTABLE is it definitely not the same since data in memory-optimized tables is not organized in pages, and does not use the Buffer Pool. It is […]
Continue readingBy Lori Brown | Helpful Scripts , Miscellaneous , SQL Server , Tip of the Month
Recently I had to do some R&D for a project and needed to generate a large table to test performance on. Since it is important to test queries on large data sets I thought that I would post how I populated a few data types in my table. To test query results I decided that […]
Continue reading