Can you repro this bug? Trace Flag 2363 Causes Fatal Exceptions on Windowed Aggregates

While testing SQL Server 2014 Enterprise edition, I wanted to see if I could use trace flag 2363 to gather meaningful information for statistics analysis. Part of this analysis included getting some information on the statistics in question.

With Trace Flag 2363 enabled:


DBCC TRACEON(2363,-1);
GO
select
schema_name = sh.name,
table_name = t.name,
stats_name = s.name,
leading_column = index_col(quotename(sh.name)+'.'+quotename(t.name),s.stats_id,1),
modification_percent = (100.*sp.modification_counter)/sp.unfiltered_rows
,sp.modification_counter
,sp.unfiltered_rows
,sp.rows_sampled
,avg_rows_in_db = avg(1.*sp.unfiltered_rows) over ()
from sys.stats s
join sys.tables t
on s.object_id = t.object_id
join sys.schemas sh
on t.schema_id = sh.schema_id
cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
where sh.name != 'sys';

This results in a severe error to the client

Msg 0, Level 11, State 0, Line 77
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 77
A severe error occurred on the current command. The results, if any, should be discarded.

Parsing the minidump with WinDbg, I arrived at this as the faulting module

sqllang!DRgCId::Print+0x68

I’ve attempted repro with user tables and different aggregates and still get the same issues. If you can reproduce, feel free to vote at the Connect item below.

https://connect.microsoft.com/SQLServer/feedback/details/1327021/trace-flag-2363-causes-fatal-exceptions-on-windowed-aggregates

SQL Saturday 358: ABQ – The Deuce

This Saturday I’ll be in Albuquerque, NM at SQL Saturday 358. Last year was my first time attending and speaking at a SQL Saturday event, so I’m excited to return to the place that gave me that first chance.

I’ll be talking about managing statistics (again), with an eye toward proactive management. This is just like the presentation I gave at SQL Saturday 331 (Denver) and to the Performance Virtual Chapter, except it’s been completely re-written to enjoy some of the undocumented features in SQL Server 2014 and to focus more on the proactive management. I hope to see you there!

SQL Server and ReFS: Part 2 – FIO Benchmarking NTFS vs. ReFS

As I was sitting in Bob Ward’s Inside SQL Server I/O presentation, something interesting caught my eye on a slide that noted that ReFS is now supported for SQL Server 2014. I’d run into problems with 2012 so I’d just given up but this looks promising. I am neither a filesystem aficionado, nor a dilettante but I know that there are some interesting features of ReFS at which Windows server admins are looking to see if it’s viable.

Previously, I examined a couple of features that were potential blockers for 2014/ReFS adoption: DBCC CHECKDB and In-Memory OLTP support.

So with it established that these issues have been addressed and that SQL Server 2014 does support ReFS, should we consider it as a viable option? Let’s see from a FIO benchmark perspective.
Continue reading

SQL Server and ReFS: Part 1 – DBCC and In Memory OLTP

As I was sitting in Bob Ward’s Inside SQL Server I/O presentation, something interesting caught my eye on a particular slide.

ward

It looks like ReFS is now supported for SQL Server 2014. I’d run into problems with 2012 so I’d just given up but this looks promising. I am neither a filesystem aficionado, nor a dilettante but I know that there are some interesting features of ReFS at which Windows server admins are looking to see if it’s viable.

There are a few known gotchas with ReFS that I’m going to (hopefully) test, along with performance characteristics. Performance will be addressed in a separate post because I’ve already got the numbers and the post will be a bit deeper and less along the lines of “work/no-work.”
Continue reading

Nerf-Herding in Denver (SQL Saturday #331)

In the Star Wars universe, the nerf was an adaptable, hairy, slow, grumpy animal that was important for its meat. In SQL Server, the Statistic is a flexible, hairy, (sometimes) slow, grumpy blob that is important in query compilation. The similarities don’t end there, though. Both nerfs and statistics can get unwieldy if not managed properly.

Continue reading

Data Compression Exploration

I recently wanted to explore potential candidates for data compression in our environment. I found a few ways to go about doing this, most notably this TechNet article that steps through strategy and planning. This was helpful, but I wanted to come up with a repeatable process. Since we have partitioned tables, I also wanted to examine candidates for compression at the partition level for each index.

Continue reading

Filtered Statistics and Tracking Their Use

As users and business activities generate data that is stored in SQL Server, the possibility for data skew increases. For the purposes of this conversation, data skew can be generally characterized as significant variance in frequencies of values in a column. Admittedly, the significance of the variance depends on factors such as data size and/or the informed subjectivity of the analyst. To illustrate, I’ll use some data from a recent SQL Saturday (#297) presentation I did on backup throughput analysis.
Continue reading

Getting a blocking chain

It’s been a while, primarily because I’ve been looking into a different blogging platform with actual Markdown support, and on a different host. It’s definitely better looking and undoubtedly easier to use from a publishing perspective. I may still end up self-hosting and just buying a domain name. However, I digress …

We’ve been having some issues with blocking on a pre-prod implementation recently. That’s not really a huge cause for alarm since blocking comes with the territory, but this has been excessive and was having some noticeable performance impact. I wanted to put together a query that would help us find the blocking chain (this was before we put the environment in the list of servers monitored by SQLSentry). Recursive CTEs really came in handy. The first iteration looked something like this:
Continue reading