Press "Enter" to skip to content

Day: November 7, 2015

Grouping Clauses

William Brewer goes into nice detail on the grouping clauses ROLLUP, CUBE, and GROUPING SETS.

ROLLUP and CUBE had their heyday before SSAS. They were useful for providing the same sort of facilities offered by the cube in OLAP. It still has its uses though. In AdventureWorks, it is overkill, but if you are handling large volumes of data you need to pass over your data only once, and do as much as possible on data that has been aggregated. Events that happened in the past can’t be changed, so it is seldom necessary to retain historic data on an active OLTP system. Instead, you only need to retain the aggregated data at the level of detail (‘granularity’) required for all foreseeable reports.

Imagine you are responsible for reporting on a telephone switch that has two million or so calls a day. If you retain all these calls on your OLTP server, you are soon going to find the SQL Server labouring over usage reports. You have to retain the original call information for a statutory time period, but you determine from the business that they are, at most, only interested in the number of calls in a minute. Then you have reduced your storage requirement on the OLTP server to 1.4% of what it was, and the call records can be archived off to another SQL Server for ad-hoc queries and customer statements. That’s likely to be a saving worth making. The CUBE and ROLLUP clauses allow you to even store the row totals, column totals and grand totals without having to do a table, or clustered index, scan of the summary table.

As long as changes aren’t made retrospectively to this data, and all time periods are complete, you never have to repeat or alter the aggregations based on past time-periods, though grand totals will need to be over-written!

I’ve used ROLLUP and GROUPING SETS fairly regularly but not so much CUBE.  Read the whole thing and figure out that the aggregation & summarization monster you have to maintain can maybe be re-written in a much simpler way.

Via Database Weekly.

Comments closed

Session Wait Stats

SQL Server 2016 has a per-session wait stats DMV:  sys.dm_exec_session_wait_stats.  That’s exciting; wait stats are extremely interesting, but until now, impossible to use on a per-item level in a busy production system (where you’d most want to use them).

Daniel Farina looks at how the new DMV relates to sys.dm_exec_wait_stats (via Database Weekly):

[R]esetting the data of sys.dm_os_wait_stats operating system view doesn’t affect the values of sys.dm_exec_session_wait_stats view.

Based on my MSDN reading, the sys.dm_exec_session_wait_stats DMV resets if the connection pool context is re-used or if the session closes.  This is why DBCC SQLPERF doesn’t include a reset option for session-specific wait stats.

Comments closed

Microsoft & Red Hat Sittin’ In A Tree

Microsoft and Red Hat have joined together to support Linux in Azure.

Customers can already run Linux on Azure, but the new partnership will expand support for running so-called “hybrid clouds,” in which applications may exist in both private data centers and on public cloud services. More significantly, Microsoft and Red Hat support teams will work together from the same facilities to support Red Hat customers using Azure. Microsoft vice president of cloud and enterprise Scott Guthrie said during a webcast today that this is the first time that he knows of that Microsoft has “co-located” support teams with another company.

The deal is the latest example of Microsoft playing nice with a former rival. “When we started [Red Hat Enterprise Linux] I never would have thought we’d do this,” Red Hat president of product and technology Paul Cormier said during the webcast.

Free speculation with no evidence:  at some point, Microsoft will offer SQL Server on Linux.  My guess is 3-5 years from now, but other co-speculators have suggested maybe even as soon as 18 months.  Whatever the case, I’ll be a happy man when I can run SSMS in Linux.

Comments closed