SQL Server 2005 upgrade to SQL Server 2014 and compatibility levels

A database currently attached to an instance of SQL Server 2005 could be either backed up (or detached), and later restored (or attached) on top of an instance of SQL Server 2014, and SQL Server 2014 won’t complain as it typically did in the past when you tried to restore (or attach) databases across database…

2

Why is everyone classified in the internal workgroup?

A PFE colleague recently posted a question describing what seemed like a weird and unexpected situation he observed while he was onsite in one customer. From SQL Server Management Studio, every single entry in the Processes section of the Activity Monitor showed as being classified as the “internal” in the “Workload Group” column, just like…

0

Got a read only database and the contents of sys.dm_db_index_usage_stats.last_user_update contains today’s date. How’s that possible?

Any attempt to update the index (via any DML statement) updates that index usage statistics as part of the query scan setup/initialization, even before the iterator’s GetRow method is invoked (i.e. before the iterator attempts to actually insert, delete or update a single row.) In the case of a R/O database, it could happen that…

0

What’s that HTDELETE wait type?

One of the many improvements, shipped with the SQL Server 2014, made to the iterators used in the batch mode processing query execution paradigm applicable to a query that uses at least one columnstore index (Apollo) , corresponds to the enhancement and extension of aggregation. Among other things, it now uses one shared hash table…

0

What’s the optional_spid in sys.dm_exec_plan_attributes?

  My answer to a question asked by Vedran Kesegić asked earlier this week in the MCMs group: Queries accessing non-dynamically bound temp tables, if issued from different connections would recompile to bind to the right temp table. This would cause a lot of recompiles that would have a performance impact for a workload that…

2

Finding the list of invalidated plans in cache: Is that even possible?

    About three weeks ago, Kimberly approached me because she was seeking for a way to find a plan that’s in the cache that’s been invalidated such that the next execution will be recompiled. One example script to reproduce the conditions would include these steps:   Create a stored procedure Run it and it…

0

NEWSEQUENTIALID() defect in SSMS. Wasn’t it fixed already in SQL 2012 RTM?

I have recently received this question from different sources, so I think it’s worth documenting why some people still see validation error for the default value of a column when it’s defined as a NEWSEQUENTIALID() built-in. The tracking defect on Connect (https://connect.microsoft.com/SQLServer/feedback/details/472092/newsequentialid-bug-in-management-studio) keeps receiving complains about the observed, undesired behavior. It’s been fixed in Denali…

0

Encrypted text in Profiler for unencrypted modules

A colleague came up with this situation: ************ I am tracing some SQL activity on a SQL 2008 R2, Enterprise SP2 64-bit server. In the profiler trace, I keep seeing Encrypted Text in the textdata column for procedures that are clearly plain text and are not encrypted. Though, I do notice in certain scenarios, where…

0

How to set the default transaction isolation level server wide?

Last week I was involved in a discussion about the default transaction isolation level used by the transaction objects in WCF’s services (Serializable) versus the one used by the transaction objects in Entity Framework for SQL Server (ReadCommitted) whose details I’m not going to cover in this post.   At a given point during that…

0

Even if some client tools in SQL seem to be forward compatible with later versions, you may be missing something important

Today, a colleague exposed the following case:   In the execution plan I do see some exclamatory marks on Sort, Hash Joins operators but there is no warning information available. I am using SQL Server 2012.  How do I interpret the exclamatory marks?       Here goes my explanation to this problem: New execution…

0