SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Tag Archives: SQL Server 2012

Working with Data Alerts in SQL Reporting Services 2012 – Part1

With SQL Server 2012 Reporting Services, we introduced a new feature called Data Alerts. Data Alert is a data driven alerting mechanism, where in you can get automated responses if the data associated with a particular report changes. These alerts are send by email, with the user having control on the frequency of these emails and the rules (conditions) on which these alerts are fired.

Requirements

Data Alerts work only with reports which are deployed in a SharePoint library. This means if your Reporting Services is configured for Native Mode, you cannot configure Data Alerts.  SSRS 2012, unlike earlier versions of SSRS does not allow changing an existing Native Mode installation to SharePoint Integrated mode using the Reporting Services configuration manager. You need to install a new instance of reporting services in SharePoint mode to use it.

How Data Alerts work

Data Alerts work flow can be divided into two sections, configuration and processing of alerts. During the configuration phase, the user needs to be first run the report. Data Alerts can only be configured if the Report has data. Data Alerts can be configured for any report (containing any item type) as long as it has some data. In order to configure Data Alerts, the report data source must use Stored Credentials or no credentials (would use the SSRS execution account).

Power View reports cannot be configured for Data Alerts.

image

Once the Data Alert has been configured, it is put in the Alerting database. This database is automatically created when we install SSRS in SharePoint integrated mode. By default the name of the database is ReportingServices<GUID>_Alerting. This also creates a SQL Agent job, to automatically processes the data alert based on the schedule defined, while creating the alert.

When the SQL Agent Job starts at its schedule, it starts the processing of the data Alert. During processing, the Report is run and the data feed for the report is refreshed. Then the rules defined for the alert are applied to the data feed. It a data value matches the rules applied in the alert, it is included in the results for the Alert. An email messages is composed with all the data value which match the rules and sent to the recipients (specified during alert creation).

The email messages being sent, typically contains the following

  1. From email address, is the email address configured in your SSRS Email Settings.
  2. To addresses are the emails provided during Alert creation.
  3. The Email body consists of the following, but is not limited to
    • Data Values in the result set.
    • A link to the report on which the alert is built.
    • The name of the person who configured the alert.
    • The parameters and values used in the report during the time of alert creation.

image

In another post, I will talk about installing data alerts and using the data alert designer.

What’ New SQL Server 2012: Reporting Services Office based Renderers

With SQL Server 2012, Reporting Services, both Excel and Word rendering now creates a Office 2007-2010 compatible files. These files are also compatible with Excel/Word 2003.

Excel based files have the extension .xlsx, while the word based files have .docx extensions.

In addition, there are several other enhancements with the excel rendering

  1. Max row limitation of excel 2003 (65K) rows is no longer applicable. Now the max number of rows per worksheet can be over 1 million.
  2. Maximum columns per worksheet is 16,384.
  3. Number of colors allowed in a worksheet is approximately 16 million (24-bit color).
  4. ZIP compression provides smaller files sizes.

With word rendering, the .docx files are much smaller than the previously created .doc files.

What’s new SQL Server 2012–Server Level Auditing For all Editions

During my customer visits and over other offline interactions I have had with customer, one thing which they have always complained about is the fact that Server Level auditing in not available for other editions of SQL Server, except for the Enterprise, Developer and Evaluation Edition.

Now there is good news. With SQL Server 2012, Server level Auditing is now available for all editions of SQL Server. Database level auditing still remains limited to Enterprise, Developer and Evaluation Editions.

In addition to extending the Server level auditing to all editions, there are have been certain enhancements to the Auditing options.

  1. The logging is more resilient to failures. For example, if the target log is on a network share, and if the network connection fails, SQL Server audit would restart when the network connectivity is restored.
  2. A new Fail_Operations option has been added to On_Failure clause. With this new operations the operations which invoke the Audit, would be failed if the Audit cannot be written to be log. As mentioned above, if the write failure condition is resolved, the operations would continue to work without problems.
  3. A new MAX_Files options has been added to the Audits. Earlier either the no of log written were unlimited (limited by the max size option or the space on the destination directory) or the number of logs were regulated by the Max_Rollover_Files option. Now, we can define a limit on the maximum number of files to be created.
  4. A new option Predicate Expression has been added to the Create Server Audit command. These predicate expressions are used to determine if the Audit needs to be invoked or not. The Predicate Expressions can be specified in a where clause in the Create Server Audit command.

SQL Server 2012 — ColumnStore Index

In the past, if you have been plagued with Slow performance of your data warehouse queries, there is some good news for you. With SQL Server 2012, we can now create ColumnStore Indexes, which would improve your query performance significantly. ColumnStore indexes are targeted for queries which use a Aggregations over columns or a set of columns.

Traditional SQL Server Approach

SQL Server has traditionally been using a row store based storage. Under this approach the complete data row is stored on a Data Page, and is read or written to as a complete unit. As shown below, the Row is Stored on the page

image

In the traditional approach, lets say if we had a table, with the given definition

create table t1
(
    a int,
    b int,
    c datetime,
    d varchar(300)
)

Assume we are running the following query against the table and that the table has about 1 billion records on approximately 40 million pages.

select SUM(a),AVG(b), MAX(c) from t1

In this case, we just need to do a sum of the values of column a. Given the row store approach, we would need to read the entire row (all the columns) to execute this query, which means we would need to read all the 40 million pages for this query and then perform the aggregation operation.

How ColumnStore Index Helps

When we create a column store index on the table, it changes the way how data is organized. Data for each column which is included in the ColumnStore index is arranged separately. For example if we created a ColumnStore index on the above table on the columns (a, b and c). The columns would be organized independent of each other on different set of pages. as shown below.

image

Additionally, ColumnStore uses page compression by default. Most of the column values are similar if not same, and hence can greatly benefit from compression.

Now, with this arrangement, if we run the above mentioned query, we would only need to read pages for Column a, which in the above case would be only about ~300K(assuming 40% compression) of pages to read and sum the values of a, then another ~300K(assuming 40% compression) pages for the average of b and finally ~500(assuming 50% compression) pages to calculate the Max of c.

As mentioned, this type of organization helps queries where we are returning only some of the columns from a table. For queries where we need to return all the columns in a row, Row-Store approach works better. Typically, OLTP workloads benefit from row storage organization of data, while warehouse queries would benefit from ColumnStore index.

Restrictions on ColumnStore Indexes

The MSDN Article describes the restrictions or limitations to creating ColumnStore indexes. One thing to keep in mind, that when u create a ColumnStore index on a Table, you cannot Update the table. In Order to update the table, the quickest way would be to drop the column store index and then perform your insert, update, deletes. Once you are done with the DML’s, the ColumnStore Index can be recreated.

Example

I create two tables with the same schema but one with only a ColumnStore Index and another with a Clustered Index and a non clustered index. Both the table have about 7.7 million records.

This tables have been created on the AdventureWorks2012 sample database, which can be downloaded from here.

TableWithoutColumnStoreIndex

   1: use AdventureWorks2012

   2: go

   3:  

   4:  

   5: select * into dbo.TableWithoutColumnStoreIndex from Sales.SalesOrderDetail

   6: go

   7:  

   8: declare @count int =0

   9: while @count < 6

  10: begin

  11:     insert into dbo.TableWithoutColumnStoreIndex 

  12:     ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],

  13:     [UnitPriceDiscount],[LineTotal],rowguid, [ModifiedDate])

  14:         select [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],

  15:     [UnitPriceDiscount],[LineTotal],NEWID(), [ModifiedDate] from dbo.TableWithoutColumnStoreIndex

  16:  

  17:     set @count=@count+1

  18: end

  19:  

  20:  

  21: /****** Object:  Index [IDX_Clustered]    Script Date: 2/1/2012 3:01:42 AM ******/

  22: CREATE CLUSTERED INDEX [IDX_Clustered] ON [dbo].[TableWithoutColumnStoreIndex]

  23: (

  24:     [SalesOrderDetailID] ASC

  25: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  26: GO

  27:  

  28: /****** Object:  Index [IDX_NonClustered]    Script Date: 2/1/2012 3:01:58 AM ******/

  29: CREATE NONCLUSTERED INDEX [IDX_NonClustered] ON [dbo].[TableWithoutColumnStoreIndex]

  30: (

  31:     [SalesOrderID] ASC,

  32:     [ProductID] ASC

  33: )

  34: INCLUDE (     [ModifiedDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  35: GO

  36:  

  37:  

TableWithColumnStoreIndex

   1: use AdventureWorks2012

   2: go

   3:  

   4: select * into dbo.TableWithColumnStoreIndex from Sales.SalesOrderDetail where 1=2

   5: go

   6:  

   7: Alter Table dbo.TableWithColumnStoreIndex Alter Column LineTotal numeric(18,6)

   8: go

   9:  

  10: insert into dbo.TableWithColumnStoreIndex 

  11: ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],

  12: [UnitPriceDiscount],[LineTotal],rowguid, [ModifiedDate])

  13:     select [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],

  14: [UnitPriceDiscount],[LineTotal],rowguid, [ModifiedDate] from Sales.SalesOrderDetail

  15: go

  16:  

  17:  

  18: declare @count int =0

  19: while @count < 6

  20: begin

  21:     insert into dbo.TableWithColumnStoreIndex 

  22:     ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],

  23:     [UnitPriceDiscount],[LineTotal],rowguid, [ModifiedDate])

  24:         select [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],

  25:     [UnitPriceDiscount],[LineTotal],NEWID(), [ModifiedDate] from dbo.TableWithColumnStoreIndex

  26:     set @count=@count+1

  27: end

  28:  

  29:  

  30: -- Create a Column Store Index on the Table

  31: Create NonClustered ColumnStore Index IDX_ColumnStore on dbo.TableWithColumnStoreIndex

  32: (    [SalesOrderID],

  33:     [SalesOrderDetailID],

  34:     [CarrierTrackingNumber],

  35:     [OrderQty],

  36:     [ProductID],

  37:     [SpecialOfferID],

  38:     [UnitPrice],

  39:     [UnitPriceDiscount],

  40:     [LineTotal],

  41:     [ModifiedDate]

  42: )

I executed the below mentioned query on the two table to compare the performance. In the Query below, I am just substituting the Name of the tables([TableWithColumnStoreIndex], or  [TableWithoutColumnStoreIndex]) While executing.

   1: select

   2: ( ISNull(P.FirstName,'') + ' ' + ISNull(P.MiddleName,'') + ' ' + IsNull(P.LastName,'')) As CustomerName,

   3: SOH.OrderDate, SOH.DueDate, SOH.ShipDate,SOH.TotalDue,

   4: sum(TWC.OrderQty) As TotalOrderQuantity ,avg(TWC.UnitPrice) As AvgUnitPrice,

   5: Avg(TWC.UnitPriceDiscount) as AvgDiscountOnUnitPrice

   6: from 

   7: [dbo].[TableWithoutColumnStoreIndex] TWC 

   8: join Sales.SalesOrderHeader SOH on TWC.SalesOrderID = SOH.SalesOrderID

   9: join Sales.Customer C on SOH.CustomerID = C.CustomerID

  10: join Person.Person P on P.BusinessEntityID = C.PersonID

  11: where TWC.UnitPriceDiscount <> 0 and TWC.OrderQty > 500

  12: group by 

  13: ( ISNull(P.FirstName,'') + ' ' + ISNull(P.MiddleName,'') + ' ' + IsNull(P.LastName,'')),

  14: SOH.OrderDate, SOH.DueDate, SOH.ShipDate,SOH.TotalDue

Query Performance Against the Table without ColumnStore Index

When executed against the Table without ColumnStore index, the query table takes about 1248 millisecond.

image

Query Performance Against the Table with ColumnStore Index

When executed against the Table with ColumnStore Index, the query executes in about 218 milliseconds. A 5x performance improvement.

image

SQL Server 2012 RTM’s Today

Today, SQL Server 2012 has been released to Manufacturing. Get ready for some really cool features with SQL 2012, like File Table, ColumnStore Index and a very cool reporting environment PowerView. More information available here.