SQL Server

This week, Brent, Tara, and Richie discuss what they think about having no more service packs for SQL Server 2017 and newer versions, shrinking log files, how many servers can one DBA manage, log backups, mirroring vs Availability Groups vs log shipping, and their next company retreat location. Here’s the video on YouTube: You can…

[Video] Office Hours 2017/09/13 (With Transcriptions)

Last Updated October 7, 2017

Note: this is coming to you from the past due to technical difficulties. This week, Brent and Erik discuss Availability Group issues, database diagram tools, AWS vs Azure, diagnosing async io waits, troubleshooting deadlocks, extended events, using same login credentials for everyone in the company, adjusting and more. Here’s the video on YouTube: You can…

No More SQL Server Service Packs: Is CU12 the New SP1?

Last Updated October 3, 2017

Production Database Administration, SQL Server 2017

Starting with yesterday’s release of SQL Server 2017, Microsoft has a new servicing model: they’re only delivering Cumulative Updates, and not doing Service Packs. Pedro Lopes writes: SPs will no longer be made available. Only CUs, and GDRs when needed. CUs will be delivered more often at first and then less frequently. Every month for…

New System Stored Procedures in SQL Server 2017

Last Updated October 3, 2017

Columnstore Indexes, SQL Server 2017

Got a few new stored procs for columnstore indexes: sp_add_columnstore_column_dictionary – input params for @table_id, @column_id sp_is_columnstore_column_dictionary_enabled – as above, plus output param for @is_enabled bit sp_remove_columnstore_column_dictionary – input params for @table_id, @column_id And a couple for snapshot views (which appear to be related to the new snapshot messages in sys.messages) sp_refresh_single_snapshot_view – input param for @view_name…

New sys.messages Entries in SQL Server 2017

Last Updated October 2, 2017

SQL Server 2017

I’m only showing the English (1033) ones here, message ID and then text. Some of the new messages are very Azure-specific, and while I love to think that we’d get some of these features in the boxed product, I think Microsoft is just getting down to a standard shared list of message IDs. However, some…

New Wait Stats and DMV Columns in SQL Server 2017

Last Updated October 2, 2017

SQL Server 2017, Wait Stats

SQL Server 2017 is officially out, and here’s what I’ve found so far. New Wait Stats in 2017 BACKUP_INMEM_DIFFLIST_READ_ACCESS BACKUP_INMEM_DIFFLIST_WRITE_ACCESS BLOB_CONTAINER_TABLE COLUMNSTORE_MIGRATION_BACKGROUND_TASK EXTERNAL_SCRIPT_NETWORK_IO FOREIGN_REDO GHOSTCLEANUP_UPDATE_STATS GLOBAL_QUERY_CANCEL GLOBAL_QUERY_CONSUMER GLOBAL_QUERY_PRODUCER HADR_THROTTLE_LOG_RATE_LOG_SIZE HADR_THROTTLE_LOG_RATE_SEEDING HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE MEMORY_GRANT_UPDATE MIGRATIONBUFFER PREEMPTIVE_OS_BCRYPTIMPORTKEY PREEMPTIVE_OS_NCRYPTIMPORTKEY PREEMPTIVE_XE_PROXY_ADDSESSION PREEMPTIVE_XE_PROXY_PROCESSBUFFER PREEMPTIVE_XE_PROXY_REMOVESESSION PREEMPTIVE_XE_PROXY_SESSIONCOMMIT PWAIT_AUDIT_CLOSE_EXPIRED_LOGS_MGR_RWLOCK PWAIT_AUDIT_SESSIONS_LOCK PWAIT_EXTERNAL_SCRIPT_LIBMGMT_DIR_PERMS PWAIT_PREEMPTIVE_APP_USAGE_TIMER PWAIT_PREEMPTIVE_XE_STUB_LISTENER PWAIT_SBS_FILE_OPERATION QDS_HOST_INIT RBIO_WAIT_VLF REMOTE_BLOCK_IO RESTORE_MSDA_THREAD_BARRIER SBS_DISPATCH SBS_RECEIVE_TRANSPORT SBS_TRANSPORT SECURITY_CNG_PROVIDER_MUTEX SLEEP_SETUP SNI_WRITE_ASYNC…

SQL Server 2017 Installation Screenshot Tour (Windows)

Last Updated October 2, 2017

SQL Server 2017

It’s like an unboxing, but with setup.exe. For the vast majority of setup, it’s the same as 2016: Setup.exe opens, then click the Installation tab on the left The better part of Setup.exe Pick your edition – I’m going with Developer because I’m a…wait…. Yep, Developer Edition still phones home and you can’t turn it off…

[Video] Office Hours 2017/09/27 (With Transcriptions)

Last Updated October 1, 2017

This week, Richie and Erik discuss altering database columns, separating log files, encryption, NOLOCK issues, developing new skills and experience, troubleshooting deadlocks, Oracle, calling C# method from a stored procedure and the type of headset Erik uses. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to…

SQL Server 2017 Showplan Schema Is Available

Last Updated September 28, 2017

Execution Plans, SQL Server, SQL Server 2017

File this under… Things probably only I care about. But the Showplan Schema for SQL Server 2017 popped up… well, I caught it last night. I probably could have written this sooner, huh? So what’s new? Well, if I compare the 2016 SP1 schema using KDiff, a few things stand out. Some I’ve blogged about,…

Live Query Plans and Blocking

Last Updated September 21, 2017

Execution Plans, Indexing, Locking, Blocking, and Isolation Levels, SQL Server

You’d think this would be cool After all, you’ve got a query plan being executed LIVE ON NATIONAL TELEVISION! But it’s not. It’s actually one of those weird reasons why you still need other ways of looking at DMVs for issues. Demo Library Let’s take a closer look! First, we need an index. Indexes are…

[Video] Office Hours 2017/09/20 (With Transcriptions)

Last Updated September 25, 2017

This week, Brent, Tara, Richie, and Erik discuss a most excepted solution for SA and securing an instance, how many VLFs should a database have, merge replication issues, query tuning tips, database restores, and Larry Ellison’s self-driving database. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast…

Why Columnstore Indexes May Still Do Key Lookups

Last Updated June 25, 2021

Columnstore Indexes, Execution Plans, Indexing, SQL Server

I was a bit surprised that key lookups were a possibility with ColumnStore indexes, since “keys” aren’t really their strong point, but since we’re now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should get a…

Answering Questions For Fun And No Profit

Last Updated September 20, 2017

Humor, SQL Server

I love answering questions about SQL My forum of choice is dba.stackexchange.com because I find the voting and point system with badges for being a decent internet person addictive. If you like more traditional forums, SQLServerCentral.com has a lot of really smart people on it as well. When I post questions or answers, I try…

What Would You Put in SQL Server 2019?

Last Updated April 11, 2019

SQL Server 2019

186

If you were leading Microsoft’s development programs, what would you add or change in SQL Server 2019? Forget licensing or pricing changes – that’s dictated by the marketing teams, and those decisions are made really close to the release date. Instead, let’s think bigger: what are the really big improvements you’d make that would really…

The Ghosts of Temp Tables Past

Last Updated September 6, 2017

Humor, SQL Server

True story You may find it hard to believe, but I recently had to fix a small bug in sp_BlitzCache and sp_BlitzQueryStore. Since both stored procedures have similar functions, they also share some temp table names (mainly the ones for parsing down XML nodes to more manageable chunks). In the window where I was making…

Kickstarter-Style Ideas for our PASS Summit Pre-Con

Last Updated October 7, 2017

SQL Server

Boy, have I got a weird challenge for you, dear reader. When Erik & I started building Expert Performance Tuning for SQL Server 2016 & 2017, our PASS Summit pre-con, I talked with the PASS staff about the attendance record for pre-cons. I thought, “Wouldn’t it be cool if we had the biggest pre-con ever?” Turns…

Optional Parameters and Missing Index Requests

Last Updated September 15, 2017

Execution Plans, Indexing, SQL Server

That’s when it all gets blown away At one point or another in everyone’s SQL-querying career, they end up writing a query that goes something like this: Transact-SQL SELECT something FROM stuff WHERE (@thing1 is NULL or whatever = @thing1) AND ... 1234 SELECT somethingFROM stuffWHERE (@thing1 is NULL or whatever = @thing1)AND ... These…

Indexing for Windowing Functions: WHERE vs. OVER

Last Updated March 12, 2018

Execution Plans, Indexing, SQL Server

Life Is Messy Demo queries have this nasty habit of being clean. Even using a pit of despair like Adventure Works or World Wide Importers, it’s easy to craft demo queries that fit the scenario you need to make yourself look like a genius. Stack Overflow, in all its simplicity, makes this even easier (lucky…

How Much Can One Column Change A Query Plan? Part 2

Last Updated September 8, 2017