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

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

How much is crash recovery parallelized? In which order are databases recovered?

Following is the answer I provided to the interesting question asked by my colleague Pat Martin about the insides of crash recovery in SQL Server.   The iterator used to scan the catalog for the databases that need startup and recovery returns them sorted by database id. So, if you want a database to start…

4

FAQ around sys.dm_db_index_usage_stats

For those of you who may not be familiarized with this object, sys.dm_db_index_usage_stats is one of the many Dynamic Management Views built into SQL Server. This one specifically falls into the category index related DMVs, and it returns counts of different types (scans, seeks, and lookups) of index operations, triggered by user or system initiated…

2

Another dirty trick played by an antivirus

A PFE colleague from Italy, recently exposed the following situation in an internal distribution list: From: <removed_to_protect_identities> Sent: Wednesday, July 18, 2012 8:36 AM To: <removed_to_protect_identities> Subject: Report hanging when rendering on IE9 Hi there, I am browsing a set of reports on RS2008 Sp3 with Internet Explorer 8 and IE9. When browsing with IE8,…

1

How do you explain that sys.dm_os_spinlock_stats.backoffs keeps increasing and no sqlos.spinlock_backoff event is produced?

A PFE colleague from the UK approached me yesterday with the following concern: Hi Nacho, I may need to find out the call stacks when a spinlock backoff occurs at a customer tomorrow. So I found this script from a SQLCAT article: http://sqlcat.com/sqlcat/b/msdnmirror/archive/2010/05/11/resolving-dtc-related-waits-and-tuning-scalability-of-dtc.aspx create event session lock_hash_spin on server add event sqlos.spinlock_backoff (action (package0.callstack) where…

1

What’s the maximum level of recursion for the hash iterator before forcing bail-out?

This is a question I was asked recently whose answer I had to look in the code because it wasn’t documented anywhere. So, I’ll use this mean to propagate that piece of knowledge. The value is a constant, hard coded in the product, and its value is five (5). This means that before the hash…

1

What’s in SOS_PHYS_PAGE_CACHE?

My colleague Assaf Fraenkel asked a few days ago about that wait type he noticed was showing up in some of the instances he works with, and that he hadn’t seen before. Where that wait type is used inside the engine and what it accounts for wasn’t publicly documented, so I took a peep into…

0

Where is the table cardinality taken from when that information doesn’t match in the different statistics associated to an object?

My colleague Tony O’Grady asked last week the following question, which I think is interesting to share here. <MESSAGE> I have been testing working with an export of statistics when performance tuning and have come across the following scenario: Exported statistics (histogram) and table definition from Adventure works The table is the Sales.SalesOrderDetail from 2008…

1