Feed aggregator

Mobile BI with SQL Server Reporting Services

Simple-Talk on SQL - Sun, 04/19/2015 - 17:00

For users to get the information they need from mobile reports, we frequently have to tailor the way we design those reports to the characteristics of the target device. Adam Aspin demonstrates some of the ways that we can deliver easy-to-use Business Intelligence to tablets and smartphones using Reporting Services.

Pssdiag Manager update 12.0.0.1001 released

SQL Server SQL CSS - Sun, 04/19/2015 - 07:07

We just released a pssdiag Manager update to codeplex.

Where to download

You can download both binary and source code at http://diagmanager.codeplex.com/.

What's New

This version support SQL Server 2012 and 2014

Requirements
  1. Diag Manager requirements
    • Windows 7 or above (32 or 63 bit)
    • .NET framework 2.0 installed
  2. Data collection
    • The collector can only run on a machine that has SQL Server with targeted version (either client tools only or full version) installed
Training
  1. Downloading and Installing Diag Manager
  2. Configuring and customizing pssdiag packages using Diag Manager
  3. Running pssdiag package
  4. PSSDIAG performance considerations
 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server Support

twitter| pssdiag |Sql Nexus

Categories: SQL Server MS Blogs

SQL Server 2014 Service Pack 1 has released - Updated

SQL Server Release Blog - Wed, 04/15/2015 - 16:41
Important Updates on 4/22: Immediately following the removal, we started preparing a new SQL Server 2014 Service Pack 1. We have learned several things that we wanted to pass along as well as share what is happening currently: In the 11 hours...(read more)
Categories: SQL Server MS Blogs

Understanding Cross-Database Transactions in SQL Server

Simple-Talk on SQL - Fri, 04/10/2015 - 17:00

Microsoft 'Always On' technology does not support distributed or cross-database transactions. Why not? Grahaeme Ross shows how to investigate cross-database transactions to understand the problem in more detail, and concludes that a Cross-Database transaction can cause loss of data integrity in the commit phase of the two-phase commit

What is RESOURCE_GOVERNOR_IDLE and why you should not ignore it completely

SQL Server SQL CSS - Fri, 04/10/2015 - 08:55

If you have query that runs slow, would you believe it if I tell you that you instructed SQL Server to do so?  This can happen with Resource Governor.

My colleague Bob Dorr has written a great blog about Resource Governor CPU cap titled “Capping CPU using Resource Governor – The Concurrency Mathematics”.

Today,, I will explore a customer scenario related to this topic. We have had a customer who complained that their queries ran slow.  Our support captured data and noticed that wait type “RESOURCE_GOVERNOR_IDLE” was very high.   Below is SQL Nexus Bottleneck Analysis report.

My initial thought was that this should be ignorable. We have many wait types that are used for idle threads for many different queues when queues are empty.   This must be one of those.

Since I haven’t seen it, I decided to check in the code.  It turned out to be significant.  This wait type is related to resource governor CPU cap implementation (CAP_CPU_PERCENT).     When you enable CAP_CPU_PERCENT for a resource pool, SQL Server ensures that pool won’t exceed the CPU cap.   If you configure 10% for CAP_CPU_PERCENT, SQL Server ensures that you only use 10% of the CPU for the pool.  If you pound the server (from that pool) with CPU bound requests, SQL Server will insert ‘idle consumer’ into runnable queue to take up the quantum that pools is not entitled to.   While the ‘idle consumer’ is waiting, we put RESOURCE_GOVERNOR_IDLE to indicate that the ‘idle consumer’ is taking up quantum.   here is what what the runnable queues for a particular resource pool would look like with and without CAP_CPU_PERCENT configured.

 

Not only you will see that wait type in sys.dm_os_wait_stats, but also you will see ring buffer entries like below:

select * from sys.dm_os_ring_buffers
where ring_buffer_type ='RING_BUFFER_SCHEDULER' and record like '%SCHEDULER_IDLE_ENQUEUE%'
<Record id = "139903" type ="RING_BUFFER_SCHEDULER" time ="78584090"><Scheduler address="0x00000002F0580040"><Action>SCHEDULER_IDLE_ENQUEUE</Action><TickCount>78584090</TickCount><SourceWorker>0x00000002E301C160</SourceWorker><TargetWorker>0x0000000000000000</TargetWorker><WorkerSignalTime>0</WorkerSignalTime><DiskIOCompleted>0</DiskIOCompleted><TimersExpired>0</TimersExpired><NextTimeout>6080</NextTimeout></Scheduler></Record>

 

Conclusion:

If you see wait type RESOURCE_GOVERNOR_IDLE, don’t ignore it.  You need to evaluate if you are setting the CPU cap correctly.  It may be what you wanted.  But you it may be that you have capped it too low and the queries are impacted in a way you didn’t intend to.  If it’s what you intended to do, you will need to explain to your user that they are “throttled”.

Demo

For the demo, observe how long the query runs before and after the CPU cap is configured.


--first measure how long this takes
select count_big (*) from sys.messages m1 cross join sys.messages m2  -- cross join sys.messages m3

go
--alter to 5 (make sure you revert it back later)
ALTER RESOURCE POOL [default]
WITH ( CAP_CPU_PERCENT = 5 );
go
ALTER RESOURCE GOVERNOR RECONFIGURE;
go

--see the configuration
select * from sys.dm_resource_governor_resource_pools

go

--now see how long it takes
select count_big (*) from sys.messages m1 cross join sys.messages m2  -- cross join sys.messages m3


go
--While the above query is running, open a different connection and run the following query
--you will see that it keeps going up. note that if you don't configure CAP_CPU_PERCENT, this value will be zero
select * from sys.dm_os_wait_stats where wait_type ='RESOURCE_GOVERNOR_IDLE'

go


--revert it back
ALTER RESOURCE POOL [default]
WITH ( CAP_CPU_PERCENT = 100 );
go
ALTER RESOURCE GOVERNOR RECONFIGURE;
go

 

Jack Li | Senior Escalation Engineer | Microsoft SQL Server

Categories: SQL Server MS Blogs

Schema-Based Access Control for SQL Server Databases

Simple-Talk on SQL - Wed, 04/08/2015 - 17:00

Access-control within the database is important for the security of data, but it should be simple to implement. It is easy to become overwhelmed by the jargon of principals, securables, owners, schemas, roles, users and permissions, but beneath the apparent complexity, there is a schema-based system that, in combination with database roles and ownership-chaining, provides a relatively simple working solution.

Understanding SQL Server’s Spatial Precision Filtering

SQL Server SQL CSS - Thu, 04/02/2015 - 19:37

A spatial index is not precise on its own. The spatial index is grid design requiring a precision filter as part of the query plan. In this blog I will provide a high level (10,000 foot) overview of the design.

The spatial index overlays a series of grids. If the shape has any area (representation) that falls into a cell, the shape’s primary key (PK) and cell location are added to the spatial index.

In the figure below:

· The ‘red’ point results in row 2, col 1.
· The ‘green’ point results in row 1, col 2.
· The ‘blue’ polygon results in row 1, col 1, 2 and 3; row 2 col 2; row 3 col 1, 2, and 3; and row 4 col 1, 2 and 3

Let us use the following example.

Select * from MySpatialTable mst
  Inner join poly_SpatialDataSet mlt on
         mst.GeoColumn.STIntersect(mlt.GeoColumn) = 1 -- Predicate constant on right side to allow optimizer to select spatial index

The plan below is a portion of this sample query. The spatial index on MySpatialTable is leveraged to determine ‘possible’ rows of interest. MySpatialTable holds the polygon and query is searching for the points intersecting the polygon.

1. For each row in poly_SpatialDataSet the nested loop feeds the spatial, tessellation function. Tessellation determines the cell, mapped on to the same grid as the polygon index. For each point the cell identifier and primary key is passed through the nested loop.

2. The nested loop uses the polygon’s spatial index to determine if the cell containing the point is a cell contained in the polygon. If any part of the polygon and point appear in the same cell identifier a possible hit is confirmed. This does not mean the polygon and point intersect, only that they fall within the same grid cell. The primary key for the point and polygon are then passed to the consumer of the nested loop.

3. Once a primary key for the polygon and point are identified the precision filter is invoked. The precision filter deserializes the spatial objects and performs a full STIntersects evaluation confirming if the point truly intersects the polygon.

Deserialization can be expensive. To deserialize a spatial object SQL Server uses the primary key to lookup the row and read the associated blob data storing the spatial data. SQL Server then leverages the Microsoft.SqlServer.Types .NET library to create a spatial object, deserializing the points and other metadata from the blob. The larger the blob the more work to instantiate the spatial object. You can monitor the performance counter (Access Methods : Count of LOB Read Aheads). The performance counter is helpful as deserialization leverages blob read-ahead capabilities.

The Precise Filter is a Non-Caching operator. When looking at query plans in SQL Server you may see rewind, rebind, table spool as such activities. These actions can be used to cache or reset specific portions of the execution plan, reducing the overhead of re-fetching a row, for example. The spatial, precision filter does not provide caching operations. In the example we have 2 points and a single polygon. 1 of those points will flow to the precision filter for evaluation.

Let’s say we had a million points that fell in a cell matching that of the polygon. The primary keys for a million points would flow to the precision filter. The primary key for the polygon would also flow a million times to the precision filter. The query plan logic does not assume the same, polygon row can arrive at the filter multiple times. Each time the filter is executed the polygon is materialized, used and destroyed. If the polygon was cached by the spatial filter (DCR is filed with the SQL development team) the polygon would only need to be created 1 time and it could compare itself to each of the 1 million points. Instead, it creates and destroys the polygon object 1 million times.

Because deserialization can be expensive reducing the size of the spatial object and the number of rows arriving at the precision filter helps your query execute faster.

The following is sample output from 185,000 points using statistics profile. You can see the filter was only invoked (executed) 80 times. This is indicating that only 80 of the 185,000 points fell within a cell also occupied by the polygon. Of those 80 possible hits the precision filter found 0 intersections. These 80 points are fell outside the bounds of our polygon.

· Without these 80 rows to precision filter, the query runs in 12 seconds

· With this 80 rows to precision filter, the query runs in 52 seconds

The original spatial index was built using a CELLS_PER_OBJECT setting of 64 and HIGH grid levels. Rebuilding the grid with CELLS_PER_OBJECT = 8192 changed the comparisons required from 80 to 27, reducing runtime.

CAUTION: Changing CELLS_PER_OBJECT or grid levels may not yield better results. Study your spatial objects carefully to determine optimal index options.

Another option to reduce the time spent in the precision filter is reducing the size of the spatial object. If you can reliably split the spatial object into multiple rows it may reduce the size of the deserialization and improve performance.

Here is an example taking a MultiPolygon and creating a row for each Polygon. Using smaller polygons reduces the workload for the filter.

CAUTION: Use caution when breaking up a spatial object to accommodate the desired query results.

CAUTION: Be careful when attempting to split the spatial object. Creating too many, small objects can increase the number of rows that fall into a single cell causing lots of rows to match a single cell and degrading performance over a larger object.

declare @iObjects int = 0
select @iObjects = Geography.STNumGeometries() from MyObjects

while(@iObjects > 0)
begin
  insert into MultiplePolyObjects
  select @iObjects, FeatureSid, Geography.STGeometryN(@iObjects) /*.ToString()*/ from MyObjects
  set @iObjects = @iObjects – 1
end

Leverage the knowledge of your spatial data, index precision and the precision filter invocations to tune your spatial queries.

Related Spatial References

http://blogs.msdn.com/b/psssql/archive/2013/12/09/spatial-index-is-not-used-when-subquery-used.aspx
http://blogs.msdn.com/b/psssql/archive/2013/11/19/spatial-indexing-from-4-days-to-4-hours.aspx
http://blogs.msdn.com/b/psssql/archive/2015/01/26/a-faster-checkdb-part-iv-sql-clr-udts.aspx

Bob Dorr - Principal SQL Server Escalation Engineer

Apr 14 2015 – UPDATE (rdorr)

The SQL Server development team provided me with additional information, special thanks to Milan Stojic.

Hint: The precision filtering is often referred to as the secondary filter in various publications.  The index filter is the primary filter and the precision filter is the secondary filter.

The query hint SPATIAL_WINDOW_MAX_CELLS allows fine tuning between primary and secondary filtering.   Adjusting the SPATIAL_WINDOWS_MAX_CELLS can provide increased filtering of the spatial index similar to increasing the index precision (CELLS_PER_OBJECT ) outlined in this blog.    The query hint allows targeting of specific queries instead of complete index changes.

… WITH

( INDEX ( P1 ), SPATIAL_WINDOW_MAX_CELLS=8192 ) …

Reference: SPATIAL_WINDOW_MAX_CELLS - http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/SQLServer_Denali_Spatial.docx 

Index Hint: A general recommendation is HHHH for point data and AUTO grid for other spatial data types.

geography.Filter: When using STIntersects you may consider .Filter instead.  Filter is not an exact match but if your application tolerance allows for such variance it may perform better.  Reference: https://msdn.microsoft.com/en-us/library/cc627367.aspx 

geography.Reduce: Another option may be to reduce large spatial objects, retrieving the possible rows before doing more precise work.  This may require a two step process; first to reduce and get a intermediate result set of possible rows and a final step using the reduced row possibilities against the complete object.

Bob Dorr - Principal SQL Server Escalation Engineer

 

Categories: SQL Server MS Blogs

How to Get SQL Server Data-Conversion Horribly Wrong

Simple-Talk on SQL - Wed, 04/01/2015 - 17:00

One of the most certain ways of going wrong with any relational database is to get data conversion wrong. Implicit data conversion is a good feature for the expert but can cause a lot of trouble to the unwary. These boil down to seven ways of failing to get data conversion right. Rob Sheldon explains and gives sage advice on how to avoid the problems

Deploying the same database to many different RDBMSs

Simple-Talk on SQL - Tue, 03/31/2015 - 17:00

With the idea of a generic Dacpac defined by international standard, comes the potential for a Visual Studio developer to use SSDT to create a generic database model to a SQL-92 compliant standard that can then be deployed to any one of the major RDBMSs. The same database model would be deployable to Oracle, MySQL, or SQL Server, for example. Professor Hugh Bin-Haad explains the reasoning and technology behind this.

How DBAs Can Adopt the Craftsman Mindset

Simple-Talk on SQL - Tue, 03/31/2015 - 17:00

The job of a DBA requires a fusion of skill and knowledge. To acquire this requires a craftsman mindset. Craftsmen find that the better they get at the work, the more enjoyable the work gets, and the more successful they become. Deliberate practice, Specialization and an appetite for overcoming difficulty are good habits to deliberately adopt to successfully grow those craftsmanlike skills to the point that you become “so good they can’t ignore you”.

Apply Row-Level Security to all tables -- helper script

SQL Server Security Team - Mon, 03/30/2015 - 17:41

Developing multi-tenant applications with Row-Level Security (RLS) just got a little easier. This post makes available a script that will automatically apply an RLS predicate to all tables in a database.

Applications with multi-tenant databases, including those using Elastic Scale for sharding, commonly have a “TenantId” column in every table to indicate which rows belong to each tenant. As described in Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security, the recommended approach for filtering out rows that don't belong to a tenant querying the database is to create an RLS security policy that filters out rows whose TenantId doesn't match the current value of CONTEXT_INFO. However, for large applications with perhaps hundreds of tables, it can be tedious to write out "ADD FILTER PREDICATE..." for every table when creating or altering the RLS security policy.

To streamline this common RLS use case, we’ve created a helper stored procedure to automatically generate a security policy that adds a filter predicate on all tables with a TenantId column. See below for syntax and three common usage examples.

Script available here: http://rlssamples.codeplex.com/SourceControl/latest#RLS-Auto-Enable.sql

 

Syntax: CREATE PROCEDURE dbo.sp_enable_rls_auto (
/* The type for the tenant ID column. It could be short, int or bigint. */
@rlsColType sysname,

/* The name for the tenant ID column. All tables that match the column name & type will be affected. */
@rlsColName sysname,

/* The schema name where the policy will be applied.
If null (default), the policy will be applied to tables in all schemas in the database. */
@applyToSchema sysname = null,

/* Set to 1 to disable all existing policies that affect the identified target tables.
If set to 0 (default), this function will fail if there is an existing policy on any of these tables. */
@deactivateExistingPolicies bit = 0,

/* Schema name for new RLS objects. If it does not exist, it will be created. */
@rlsSchemaName sysname = N'rls',

/* The name of an existing function in the RLS schema that will be used as the predicate.
If null (default), a new function will be created with a simple CONTEXT_INFO = tenant ID filter. */
@rlsPredicateFunctionName sysname = null,

/* Set to 1 to allow CONTEXT_INFO = null to have access to all rows. Default is 0.
Not applicable if @rlsPredicateFunctionName is set with a custom predicate function. */
@isNullAdmin bit = 0,

/* If @isNullAdmin = 1, set to 1 to optimize the CONTEXT_INFO = null disjunction into a range query.
Not applicable if @rlsPredicateFunctionName is set with a custom predicate function. */
@isNullAdminOptimized bit = 1,

/* If set, the predicate function will allow only this user to access rows.
Use only for middle-tier scenarios, where this is the shared application user name.
Not applicable if @rlsPredicateFunctionName is set with a custom predicate function. */
@restrictedToAppUserName sysname = null,

/* Set to 1 to print the commands (on by default). */
@printCommands bit = 1,

/* Set to 1 to execute the commands (off by default). */
@runCommands bit = 0
)

 

Examples: Example 1: Typical CONTEXT_INFO usage

Generate a security policy that adds a new filter predicate (using CONTEXT_INFO as described in Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security) on all tables with a "TenantId" column of type "int." Only allow access to "AppUser," the shared application user in our app's connection string. If CONTEXT_INFO is null, filter all rows by default.

EXEC sp_enable_rls_auto
@rlsColType = 'int',
@rlsColName = 'TenantId',
@applyToSchema = null,
@deactivateExistingPolicies = 1,
@rlsSchemaName = N'rls',
@rlsPredicateFunctionName = null,
@isNullAdmin = 0,
@isNullAdminOptimized = 0,
@restrictedToAppUserName = 'AppUser',
@printCommands = 1,
@runCommands = 0 -- set to 1 to execute output
go

Sample output:

CREATE FUNCTION [rls].[fn_predicate_TenantId_2015-03-30T17:36:51.010](@TenantId [int] )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_accessResult
WHERE
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID ('AppUser')
AND CONVERT([int], CONVERT(varbinary(4), CONTEXT_INFO())) = @TenantId
go

CREATE SECURITY POLICY [rls].[secpol_TenantId_2015-03-30T17:36:51.073]
ADD FILTER PREDICATE [rls].[fn_predicate_TenantId_2015-03-30T17:36:51.010]([TenantId]) ON [dbo].[Sales],
ADD FILTER PREDICATE [rls].[fn_predicate_TenantId_2015-03-30T17:36:51.010]([TenantId]) ON [dbo].[Products],
ADD FILTER PREDICATE [rls].[fn_predicate_TenantId_2015-03-30T17:36:51.010]([TenantId]) ON [dbo].[PriceHistory],
ADD FILTER PREDICATE [rls].[fn_predicate_TenantId_2015-03-30T17:36:51.010]([TenantId]) ON [dbo].[OrderDetails]
go


Example 2: Custom predicate function

Generate a security policy that adds a custom predicate function as a filter predicate on all tables with a "TenantId" column of type "int." 

CREATE FUNCTION rls.customTenantAccessPredicate(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult WHERE
(
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser') -- shared app user
AND CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) = @TenantId
)
OR
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('ReportUser') -- reporting user can see all rows
go

EXEC sp_enable_rls_auto
@rlsColType = 'int',
@rlsColName = 'TenantId',
@applyToSchema = null,
@deactivateExistingPolicies = 1,
@rlsSchemaName = N'rls',
@rlsPredicateFunctionName = N'customTenantAccessPredicate',
@isNullAdmin = 0, -- n/a
@isNullAdminOptimized = 0, -- n/a
@restrictedToAppUserName = null, -- n/a
@printCommands = 1,
@runCommands = 0 -- set to 1 to execute output
go

Sample output:

CREATE SECURITY POLICY [rls].[secpol_TenantId_2015-03-30T18:22:14.213]
ADD FILTER PREDICATE [rls].[customTenantAccessPredicate]([TenantId]) ON [dbo].[Sales],
ADD FILTER PREDICATE [rls].[customTenantAccessPredicate]([TenantId]) ON [dbo].[Products],
ADD FILTER PREDICATE [rls].[customTenantAccessPredicate]([TenantId]) ON [dbo].[PriceHistory],
ADD FILTER PREDICATE [rls].[customTenantAccessPredicate]([TenantId]) ON [dbo].[OrderDetails]
go


Example 3: Optimized "superuser" if CONTEXT_INFO is null

Same as Example 1, but if CONTEXT_INFO is null, make all rows visible to the application and utilize the performance optimization for disjunctions described in Row-Level Security for Middle-Tier Apps – Using Disjunctions in the Predicate.

EXEC sp_enable_rls_auto
@rlsColType = 'int',
@rlsColName = 'TenantId',
@applyToSchema = null,
@deactivateExistingPolicies = 1,
@rlsSchemaName = N'rls',
@rlsPredicateFunctionName = null,
@isNullAdmin = 1,
@isNullAdminOptimized = 1,
@restrictedToAppUserName = 'AppUser',
@printCommands = 1,
@runCommands = 0 -- set to 1 to execute output
go

Sample output:

CREATE FUNCTION [rls].[int_lo_2015-03-30T18:30:46.993]() RETURNS [int]
WITH SCHEMABINDING
AS BEGIN
RETURN CASE WHEN context_info() is null THEN
-2147483648 ELSE
convert([int], convert(varbinary(4), context_info())) END
END
go

CREATE FUNCTION [rls].[int_hi_2015-03-30T18:30:46.993]() RETURNS [int]
WITH SCHEMABINDING
AS BEGIN
RETURN CASE WHEN context_info() is null THEN
2147483647 ELSE
convert([int], convert(varbinary(4), context_info())) END
END
go

CREATE FUNCTION [rls].[fn_predicate_TenantId_2015-03-30T18:30:46.993](@TenantId [int] )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_accessResult
WHERE
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID ('AppUser') AND (
(@TenantId BETWEEN [rls].[int_lo_2015-03-30T18:30:46.993]() AND [rls].[int_hi_2015-03-30T18:30:46.993]())
go

CREATE SECURITY POLICY [rls].[secpol_TenantId_2015-03-30T18:30:47.047]
ADD FILTER PREDICATE [rls].[fn_predicate_TenantId_2015-03-30T18:30:46.993]([TenantId]) ON [dbo].[Sales],
ADD FILTER PREDICATE [rls].[fn_predicate_TenantId_2015-03-30T18:30:46.993]([TenantId]) ON [dbo].[Products],
ADD FILTER PREDICATE [rls].[fn_predicate_TenantId_2015-03-30T18:30:46.993]([TenantId]) ON [dbo].[PriceHistory],
ADD FILTER PREDICATE [rls].[fn_predicate_TenantId_2015-03-30T18:30:46.993]([TenantId]) ON [dbo].[OrderDetails]
go
Categories: SQL Server MS Blogs

Introduction to SQL Server Spatial Data

Simple-Talk on SQL - Wed, 03/25/2015 - 17:00

More and more applications require the handling of geospatial data. It is easy to store spatial data, but it takes rather more thought to retrieve and manipulate it. Tasks like searching neighborhoods, and calculating distances between points is often required from databases. But how do you start? Roy and Surenda take you through the basics.

Painless Refactoring of SQL Server Database Objects

Simple-Talk on SQL - Wed, 03/25/2015 - 17:00

Refactoring a database object can often cause unexpected behavior in the code that accesses that object. In this article, adapted from his excellent book, Defensive Database Programming with SQL Server, Alex Kuznetsov discusses several techniques that will harden your code, so that it will not break, or behave unpredictably, as a result such changes.

SQL Server Spatial Indexes

Simple-Talk on SQL - Tue, 03/24/2015 - 17:00

Spatial Data in SQL Server has special indexing because it has to perform specialised functions. It is able, for example, to break down an indexed space into a grid hierarchy by using a technique called tessellation. This is a rules-based system that, when you compare a shape to an index, works out how many cells in the the grid hierarchy are touched by that shape , and how deep down the grid hierarchy to search. There is powerful magic in Spatial Indexes as Surenda and Roy explain.

Row-Level Security: Blocking unauthorized INSERTs

SQL Server Security Team - Mon, 03/23/2015 - 15:14

Row-Level Security (RLS) for Azure SQL Database enables you to transparently filter all “get” operations (SELECT, UPDATE, DELETE) on a table according to some user-defined criteria.

Today, however, there is no built-in support for blocking “set” operations (INSERT, UPDATE) according to the same criteria, so it is possible to insert or update rows such that they will subsequently be filtered to you. In a multi-tenant middle-tier application, for instance, an RLS policy in your database can automatically filter results returned by “SELECT * FROM table,” but it cannot block the application from accidentally inserting rows for the wrong tenant. For additional protection against mistakes in application code, developers may want to implement constraints in their database so that an error is thrown if the application tries to insert rows that violate an RLS filter predicate. This post describes how to implement this blocking functionality using check and default constraints.

We’ll expand upon the example in a prior post, Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security. As a recap, we have a Sales table where each row has a TenantId, and upon opening a connection, our application sets the connection's CONTEXT_INFO to the TenantId of the current application user. After that, an RLS security policy automatically applies a predicate function to all queries on our Sales table to filter out results where the TenantId does not match the current value of CONTEXT_INFO.

Right now there is nothing preventing the application from errantly inserting a row with an incorrect TenantId or updating the TenantId of a visible row to a different value. For peace of mind, we’ll create a check constraint that prevents the application from accidentally inserting or updating rows to violate our filter predicate in this way:

-- Create scalar version of predicate function so it can be used in check constraints
CREATE FUNCTION rls.fn_tenantAccessPredicateScalar(@TenantId int)
RETURNS bit
AS
BEGIN
IF EXISTS(SELECT 1 FROM rls.fn_tenantAccessPredicate(@TenantId))
RETURN 1
RETURN 0
END
go

-- Add this function as a check constraint on our Sales table
ALTER TABLE Sales
WITH NOCHECK -- don't check data already in table
ADD CONSTRAINT chk_blocking_Sales -- needs a unique name
CHECK(rls.fn_tenantAccessPredicateScalar(TenantId) = 1)
go

Now if we grant our shared AppUser INSERT permissions on our Sales table and simulate inserting a row that violates the predicate function, the appropriate error will be raised:

GRANT INSERT ON Sales TO AppUser
go
EXECUTE AS USER = 'AppUser' -- simulate app user
go
EXECUTE rls.sp_setContextInfoAsTenantId 2 -- tenant 2 is logged in
go
INSERT INTO Sales (OrderId, SKU, Price, TenantId) VALUES (100, 'Movie000', 100, 1); -- fails: "The INSERT statement conflicted with CHECK constraint"
go
INSERT INTO Sales (OrderId, SKU, Price, TenantId) VALUES (101, 'Movie111', 5, 2); -- succeeds because correct TenantId
go
SELECT * FROM Sales -- now Movie001, Movie002, and Movie111
go
REVERT
go

Likewise for UPDATE, the app cannot inadvertently update the TenantId of a row to new value:

GRANT UPDATE ON Sales TO AppUser
go
EXECUTE AS USER = 'AppUser'
go
UPDATE Sales SET TenantId = 99 WHERE OrderID = 2 -- fails: "The UPDATE statement conflicted with CHECK constraint"
go
REVERT
go

Note that while our application doesn’t need to specify the current TenantId for SELECT, UPDATE, and DELETE queries (this is handled automatically via CONTEXT_INFO), right now it does need to do so for INSERTs. To make tenant-scoped INSERT operations transparent for the application just like these other operations, we can use a default constraint to automatically populate the TenantId for new rows with the current value of CONTEXT_INFO.

To do this, we’ll need to slightly modify the schema of our Sales table:

ALTER TABLE Sales
ADD CONSTRAINT df_TenantId_Sales DEFAULT CONVERT(int, CONVERT(varbinary(4), CONTEXT_INFO())) FOR TenantId
go

And now our application no longer needs to specify the TenantId when inserting rows:

EXECUTE AS USER = 'AppUser'
go
EXECUTE rls.sp_setContextInfoAsTenantId 2
go
INSERT INTO Sales (OrderId, SKU, Price) VALUES (102, 'Movie222', 5); -- don't specify TenantId
go
SELECT * FROM Sales -- Movie222 has been inserted with the current TenantId
go
REVERT
go

At this point, our application code just needs to set CONTEXT_INFO to the current TenantId after opening a connection. After that, no the application no longer needs to specify the TenantId; SELECTs, INSERTs, UPDATEs, and DELETEs will automatically apply only to the current tenant. Even if the application code does accidentally specify a bad TenantId on an INSERT or UPDATE, no rows will be inserted or updated and the database will return an error.

In sum, this post has shown how to complement existing RLS filtering functionality with check and default constraints to block unauthorized inserts and updates. Implementing these constraints provides additional safeguards to ensure that your application code doesn’t accidentally insert rows for the wrong users. We’re working to add built-in support for this blocking functionality in future iterations of RLS, so that you won’t need to maintain the check constraints yourself. We’ll be sure to post here when we have updates on that. In the meantime, if you have any questions, comments, or feedback, please let us know in the comments below.

Full demo scripthttp://rlssamples.codeplex.com/SourceControl/latest#RLS-Blocking-Inserts.sql
Categories: SQL Server MS Blogs

Never Ignore a Sort Warning in SQL Server

Simple-Talk on SQL - Tue, 03/17/2015 - 17:00

It is always bad news if your SQL queries are having to use the SORT operator. It is worse news if you get a warning that sort operations are spilling onto TempDB. If you have a busy, slow TempDB, then the effect on performance can be awful. You should check your query plans to try to eliminate SORTs and never leave a SORT warning unheeded. Fabiano Amorim shows the range of ways of getting information on what is going on with a query that is doing a SORT and when requests are made for memory.

Does statistics update cause a recompile?

SQL Server SQL CSS - Mon, 03/16/2015 - 18:51

This my “statistics series” blog.   See “Statistics blogs reference” at end of this blog.

In this blog, I will talk two scenarios related to recompile in conjunction with statistics update.  A statement can be recompiled for two categories of reasons.  First category is related to correctness (such as schema change).  Another category is related to plan optimality.   Statistics update related recompile falls into second category.

If I were to ask you a question “Does statistics update cause recompile for a query referencing the table?”,  what would your answer be?  In most cases, the answer is YES!  However there are a couple of scenarios where recompile is not necessary.   In other words, a query won’t recompile even you have updated statistics for the tables being accessed.  We actually got users who called in and inquired about the behaviors from time to time.

Scenario 1 – trivial plan

When a plan is trivial, it’s unnecessary to recompile the query even statistics has been updated.  Optimizer generates trivial plan for very simple queries (usually referencing a single table).  In XML plan, you will see statementOptmLevel="TRIVIAL". In such case, it’s futile and you won't get a better or different plan.

Let’s see this in action.  In the script below, I created a table and two procedures (p_test1 and p_test2).  p_test1 has a very simple statement.   I execute them once so that the plans will be cached.  Then one row is inserted (this is very important as it will be explained in the 2nd scenario).  Statistics then is updated.

use tempdb
go
if object_id ('t') is not null      drop table t
go
create table t(c1 int)
go
create procedure p_test1 @p1 int
as
    select c1 from t where c1 = @p1
go
create procedure p_test2 @p1 int
as
select t1.c1 from t t1 join t t2 on t1.c1=t2.c1 where t1.c1 = @p1
go
set nocount on
declare @i int = 0

while @i < 100000
begin
    insert into t values (@i)
    set @i += 1
end
go
create index ix on t(c1)
go
exec p_test1 12
exec p_test2  12
go
insert into t values (-1)
go
update statistics t
go

 

I started profiler trace to trace “SQL:StmtRecompile” event followed by running the following queries again

--no recompile because of trivial plan
exec p_test1 12
--recompile because of stats updated with data change and it's not a trivial plan
exec p_test2  12

Note that only the statement from p_test2 produced StmtRecompile event.   This is because the statement in p_test1 produced a trivial plan.  Recompile would be futile anyways.

 

Scenario 2 –no data change

In this scenario, the plan can be non-trivial plan but it still won’t recompile if the table whose statistics was updated hasn’t got any row modification(insert,delete and update) since last statistics update. 

Let’s use the same demo above to illustrate the behavior. 

let’s update statistics one more time  (update statistics t).  Note that I didn’t modify the table.  Now run the query (p_test2)  again below.  Note that no StmtRecompile event was produced.  It used the existing plan.    In short,  if there is no data change, there is no need to recompile.

--no recompile because there is no data change even though stats got updated
exec p_test2  12

 

Scenario 2 actually has complications.  Suppose that you updated statistics yesterday.  Today you decided that you need to update statistics with fullscan thinking that it may produce better statistics to benefit queries.   But there was no change in data.  You may be in for a surprise that SQL still used the same plans without recompileing.   In such case, you will need to manually free procedure cache to get rid of the plan.

Statistics blogs reference
  1.  filtered statistics
  2. statistics update with index rebuild
  3. partitioned table statistics
  4. sampling & statistics quality.

 

Jack LI | Senior Escalation Engineer | Microsoft SQL Server Support

Categories: SQL Server MS Blogs

Row-Level Security for Middle-Tier Apps – Using Disjunctions in the Predicate

SQL Server Security Team - Mon, 03/16/2015 - 16:51

In Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security, we discussed how CONTEXT_INFO could be used for middle-tier based RLS predicate definitions.

In many occasions it is necessary to introduce a disjunction to the predicate definition for scenarios that need to distinguish between filtered queries for some users and cases where a user must not be subject to filtering (i.e. administrator, etc.), and such disjunctions may potentially affect performance significantly.

The reason for this performance impact is that, once the RLS predicate is applied to a query, it will be applied as a predicate to the query. Because of the disjunction, the query may result in a scan. For details on the difference between scan and seek, I would recommend reading Craig Freedman’s “scans vs. seeks” article.

We are working on trying to optimize some of these scenarios for RLS usage, but we also know we may not be able to address all possible scenarios right away. Because of that, we would like to share an example on how to improve performance under similar circumstances on your own.

The scenario we will analyze is a slight modification to the scenario from the previous RLS blog post, but with one addition: The application needs to allow a super-user/administrator to access all rows.

The way we will identify the super-user in our application, is by not setting CONTEXT_INFO to any value (i.e. CONTEXT_INFO returns null). So we decide to modify the SECURITY POLICY to add the new logic:

CREATE FUNCTION [rls].[fn_userAccessPredicate_with_superuser](@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_accessResult
WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID ('AppUser')
AND
( CONVERT(int, CONVERT( varbinary(4), CONTEXT_INFO())) = @TenantId
OR CONTEXT_INFO() is null )
GO

ALTER SECURITY POLICY [rls].[tenantAccessPolicy]
ALTER FILTER PREDICATE [rls].[fn_userAccessPredicate_with_superuser]([TenantId]) on [dbo].[Sales]
GO

Unfortunately, this seemingly simple change seems to have triggered a regression in your application performance, and you decide to investigate, comparing the plan for the new predicate against the old one.

 
Fig 1. Plan when using [rls].fn_userAccessPredicate] as a predicate.


 
Fig 2. Plan when using [rls].fn_userAccessPredicate_with_superuser] as a predicate.

And after the analysis, the reason seems obvious: the disjunction you just added is transforming the query from a seek to a scan.  

You also realized that this particular disjunction has a particularity: one side would expect a seek (i.e. TenantId = value ) and the other side of the disjunction would result in a scan (Administrator case), so in this case it may be possible to get better performance by trying to change this particular characteristic and transform both sides of the disjunction into seeks.

How to address this problem? One possibility in a scenario like this one is to transform the disjunction into a range. How would we accomplish it? By transforming the notion of null into a range that encompasses all values.

First, we alter the security policy to use the older version, after all we don’t want to leave our table unprotected while we fix the new predicate:

ALTER SECURITY POLICY [rls].[userAccessPolicy]
ALTER FILTER PREDICATE [rls].[fn_userAccessPredicate]([TenantId]) on [dbo].[Sales]
GO

Then we create a couple of functions that will help us define the min and max for our range based on the current state of CONTEXT_INFO. Please notice that these functions will be data type-specific:

-- If context_info is not set, return MIN_INT, otherwise return context_info value as int
CREATE FUNCTION [rls].[int_lo]() RETURNS int
WITH SCHEMABINDING
AS BEGIN
RETURN CASE WHEN context_info() is null THEN -2147483648 ELSE convert(int, convert(varbinary(4), context_info())) END
END
GO

-- If context_info is not set, return MAX_INT, otherwise return context_info value as int
CREATE FUNCTION [rls].[int_hi]() RETURNS int
WITH SCHEMABINDING
AS BEGIN
RETURN CASE WHEN context_info() is null THEN 2147483647 ELSE convert(int, convert(varbinary(4), context_info())) END
END
GO

And then we proceed to redefine the predicate function and security policy using a range:

-- Now rewrite the predicate
ALTER FUNCTION [rls].[fn_userAccessPredicate_with_superuser](@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_accessResult
WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID ('AppUser') -- the shared application login
AND
-- tenant info within the range:
-- If context_info is set, the range will point only to one value
-- If context_info is not set, the range will include all values
@TenantId BETWEEN [rls].[int_lo]() AND [rls].[int_hi]()
GO

-- Replace the predicate with the newly written one
ALTER SECURITY POLICY [rls].[tenantAccessPolicy]
ALTER FILTER PREDICATE [rls].[fn_userAccessPredicate_with_superuser]([TenantId]) on [dbo].[Sales]
GO

To finalize let’s look at the new actual execution plans:

 
Fig 3. Plan when using [rls].fn_userAccessPredicate_with_superuser] as a predicate.

This new function will allow a ranged scan in both circumstances. In the case of CONTEXT_INFO being set, this range will be “between @min_value and @max_value”, which will allow the query optimizer to take advantage of the index on TenantID.

NOTE: When you test this functionality with a small table, you may see a scan instead of a seek, even though you have a nonclustered index on the tenantId Column. The reason for this is that the query optimizer may be calculating that for a particular table a scan may be faster than a seek. If you hit this behavior, try using “WITH (FORCESEEK)” at the end of your SELECT statement to give the optimizer a hint that a seek is preferred.

Obviously this is not the only scenario where you may need to rewrite a security predicate in order to improve performance, and this is certainly not the only workaround, but hopefully this example will serve to give you an example to follow for similar scenarios and ideas for other scenarios.

To conclude, I would like to reiterate that we are currently investigating how to improve performance on predicates similar to the one I showed here with a disjunction being used to distinguish between filtered queries and cases where a user must not be subject to filtering. We will update you with news on the potential solution once it becomes available.

*** Update. Sample source code available at https://rlssamples.codeplex.com/SourceControl/latest#RLS-Middle-Tier-Apps-Demo-using_disjunctions.sql

Categories: SQL Server MS Blogs

Cumulative Update #5 for SQL Server 2012 SP2

SQL Server Release Blog - Mon, 03/16/2015 - 12:21
Dear Customers, The 5 th cumulative update release for SQL Server 2012 SP2 is now available for download at the Microsoft Support site. Cumulative Update 5 contains all hotfixes which have been available since the initial release of SQL Server 2012...(read more)
Categories: SQL Server MS Blogs

Cumulative Update #15 for SQL Server 2012 SP1

SQL Server Release Blog - Mon, 03/16/2015 - 12:05
Dear Customers, The 15 th cumulative update release for SQL Server 2012 SP1 is now available for download at the Microsoft Support site. Cumulative Update 15 contains all the SQL Server 2012 SP1 hotfixes which have been available since the initial...(read more)
Categories: SQL Server MS Blogs