Browse by Tags

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....

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...

3988 - New transaction is not allowed because there are other threads running in the session. 3930 - The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.   If so, I would appreaciate you send me an email to ialonso@microsoft...

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...

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 scan operator...

This is the story of someone who claimed to be a member of the sysadmin fixed server role and, when he tried to run one stored procedure through a session in which any of the different settings that instruct the relational engine that the user is interested in retrieving the execution plan in one of...

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...

A few days ago I received an email from my good friend Kimberly telling me about an interesting discussion she was having with the attendees of one week of training sessions herself and other members from the SQL Skills Superb Gang were delivering in the UK to a group of SQL Server professionals who...

It uses FindFirstFile and FindNextFile to enumerate the files matching a pattern like this: C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.KILIMANJAROX86\MSSQL\LOG\SQLDump*.mdmp* For each file found, it retrieves its full physical path, name, and extension, its creation time, and its size in...

I came across the following question today: “sys.dm_os_performance_counters has a counter name called ‘DTC calls’, we have been monitoring this counter on sql servers where MSDTC is set up and linked servers are in use. We find the cntr_value is always 0. Is this counter accurate? Where do I find information...

There’s a topic in SQL Server’s documentation whose title is Configuring Parallel Index Operations , which mentions that the Database Engine can reduce the degree of parallelism if the leading key column of a non-partitioned index has a limited number of distinct values or the frequency of each distinct...

A colleague from PFE recently asked the following: ****************************** Hi Folks, I’m trying to find out when the Auto Update Stats event is generated assuming Auto Update Stats and Auto Create Stats db options are enabled. As far as I know the stats should be invalidated when the recompilation...

Starting with SQL Server 2012, the query engine folks introduced an extended event, inaccurate_cardinality_estimate, you can use to identify those running queries for which any of its iterators outputs significantly more rows than those estimated by the Query Optimizer. To be more precise, it is not...

A colleague who is a consultant in Chile, reported a weird scenario he was observing in one customer and for which he couldn’t articulate an explanation. I saw his question posted in one of our internal distribution lists but couldn’t put any work on it at the time because I didn’t...

John Huang , a colleague from the MCM community , recently exposed to the rest of us a weird case which he decided to present as an “Interesting blocking issue”. I don’t know the exact details of what he was trying to implement, but basically, he had a main table (tBase in his simplified repro) in which...

The INSTEAD OF INSERT Triggers topic in the documentation describes the following: An INSERT statement that is referencing a view that has an INSTEAD OF INSERT trigger must supply values for every view column that does not allow nulls . This includes view columns that reference columns in the base table...

This is something many users point out as a defect, but which is not. It is the fact that despite the version of SQL Server you are using, the column produced by SET SHOWPLAN_XML or SET STATISTICS XML containing the query plan, is named “Microsoft SQL Server 2005 XML Showplan”. Well...

Recent Note: SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 (10.50.2796.0) is the first build servicing showplanxml.xsd with the updated version. So, install that CU to get rid of this problem. Last week, while working with a customer in the optimization of some of his queries, he raised to my...

Imagine, for example, that you run DBCC TRACEON(1117, -1) to globally enable trace flag 1117 on an instance of SQL Server 2005, where it had no effect because nowhere in the Database Engine’s code honored such flag. We could then say trace flag 1117 was in a “reserved for future use”...

A colleague of mine exposed, a few days ago, a situation where his customer had some scripts they ran successfully against a SQL Server 2008 R2 instance they had in production. No errors there… happy and easy life. One day they decided to run those scripts also in a test server for a different platform...