SQL Server MS Blogs

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

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

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

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

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

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

Reporting Services: Error creating HTTP endpoint – Access is Denied

SQL Server SQL CSS - Fri, 03/13/2015 - 14:34

I’ve seen this issue a few times.  We had a case come in where they were seeing a blank page when they went to Report Manager for Reporting Services.  You may also see an HTTP 503 error.  This just means that the service had a problem and there was probably an exception that occurred under the hoods.

In this case, the issue was with SQL 2008 R2.  When looking at the Reporting Services Logs, we can see the following exception when the service starts.

rshost!rshost!1380!03/13/2015-14:52:11:: e ERROR: Failed to register url=http://+:80/ReportServer_RS2008R2/ for endpoint 2, error=5. <—5 = Access Denied
rshost!rshost!1380!03/13/2015-14:52:11:: w WARN: Endpoint 2 is enabled but no url is registered for vdir=/ReportServer_RS2008R2, pdir=C:\Program Files\Microsoft SQL Server\MSRS10_50.RS2008R2\Reporting Services\ReportServer.
servicecontroller!DefaultDomain!1a20!03/13/2015-14:52:11:: e ERROR: Error creating HTTP endpoint. System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
   at Microsoft.ReportingServices.HostingInterfaces.IRsUnmanagedCallback.CreateHttpEndpoint(RsAppDomainType application, String[] urlPrefixes, Int32 cPrefixes, String[] hosts, Int32 cHosts, Boolean wildCardPresent, String virtualDirectory, String filePath, Int32 authType, Int32 logonMethod, String authDomain, String authRealm, Boolean authPersist, Int32 extendedProtectionLevel, Int32 extendedProtectionScenario, Boolean enabled)
   at Microsoft.ReportingServices.Library.ServiceAppDomainController.SetWebConfiguration(RunningApplication rsApplication, Boolean enabled, String folder)

This comes down to understanding URL Reservations and Reporting Services starting with RS 2008 and later.

About URL Reservations and Registration
https://msdn.microsoft.com/en-us/library/bb677364.aspx

What is happening here is that the Service Account was changed outside of Reporting Services Configuration Manager.  On my system, if we look, we see that the service is currently set to my RSService account.

However, if we look at the URL Reservations that are currently registered we will see that they are configured for the Network Service account.  You can see this by running the following command from an Admin Command Prompt.

netsh http show urlacl

The problem here is that the service account was changed within the Services area and not from within Reporting Services Configuration Manager.  As a result the URL Reservation permissions were not updated to the new service account.  So, when we try to use the reservation, we get an Access Denied because the RSService account doesn’t have permissions.  The Network Service Account does.

You could also encounter other issues by doing this.  For example, you would probably not have had the Encryption Key, so if you had Data Sources and what not present, you wouldn’t be able to use the encrypted content.

How do we get back to working?

We can go back to the services area, and change the account back to what it was before.  If Network Services doesn’t work, you can use the netsh command above to see what account is actually listed and change it back to that account.

Once the account is back, you can then go into Reporting Services Configuration Manager and change the account on the Service Account Tab to the one you want.  This will also prompt you to make a backup of the Encryption key.

You will also see, when you do it this way, that it will remove the reservations and re-add them.

Running the netsh command, we can also see the correct service account is applied.

NOTE: Changing the service account from Network Service to a domain user account will add the RSWindowsNegotiate tab into the authentication types within the rsreportserver.config.  So, if you don’t have Kerberos configured, you may get prompted 3 times followed by a 401 error when you go to Report Manager.

 

Adam W. Saxton | Microsoft Business Intelligence Support - Escalation Services
@GuyInACube | YouTube | Facebook.com\guyinacube

Categories: SQL Server MS Blogs

Moving Reporting Services off of a Cluster

SQL Server SQL CSS - Fri, 03/13/2015 - 05:29

We had a customer that had deployed Reporting Services to their Cluster. They now wanted to move the RS Instance off of the Cluster and onto its own machine and leave the Catalog Database on the Clustered SQL Server.

We have a blog talks about Reporting Services and clusters. You can find that at the following link.

Reporting Services, Scale Out and Clusters…
http://blogs.msdn.com/b/psssql/archive/2010/05/27/reporting-services-scale-out-and-clusters.aspx

This focuses more on why you shouldn’t do it and doesn’t address how to get out of the situation if you are in it. So, I wanted to just outline what we did for this customer and it may help others who get into the same situation.

Our goal is to not have RS running on either physical node of the Cluster and instead have RS running on a separate machine outside of the cluster. We want RS to be running on its own server.

NOTE: This is for Native Mode Reporting Services.

Let’s go through the steps to get this migration accomplished.

Backup the Encryption Key

The first thing we need to do is backup the Encryption key for the current instance that is running. We can do this by going to the Reporting Services Configuration Manager and going to the Encryption Keys section.

The Backup button should be enabled if you haven’t already backed up the key.

Make sure you have the Virtual Network Name (VNN) of your SQL Cluster

If you don’t know the VNN of your SQL Cluster, you can go to the Failover Cluster Manager to get this. Make sure you are looking at the SQL Cluster and not the Windows Cluster. We will need this name when we point the other machine to the Database holding the catalog database.

Assuming that the current RS Instance on the cluster is using that cluster for the catalog database, you can also get it from the Reporting Services Configuration Manager in the Database Section.

Stop Reporting Services

Make sure that the Reporting Services Service is stopped on both Cluster Nodes. You will also want to change the service to be disabled so it doesn’t start back up. To disable the service, you can do that within the SQL Server Configuration Manager.

Go to the properties of the Reporting Services Service. On the Service Tab, change the Start Mode to Disabled.

Install Reporting Services

Go ahead and install Reporting Services on the server you want it to run on. Depending on what you are going to do on that server, you should only need to choose the Native Mode RS Feature and nothing else.

Configure the new Reporting Services Instance

After the instance is installed on the new machine, start the Reporting Services Configuration Manager.

The setup will be the normal configuration steps you would do for configuring Reporting Services with the following exceptions.

Database

Make sure we are pointing to the Virtual Network Name of the SQL Cluster for the Database Server. Also make sure we select the Catalog Database that the other server was using. We want to use the same one to make sure we don’t lose any data. The default name will be ReportServer.

Scale-Out Deployment

After the database is configured, you can go to the Scale-Out Deployment section. If you see the Cluster Nodes listed here, you will want to remove them. As we only want this new server to be used.

Encryption Keys

We will now want to restore the Encryption Key that we already backed up. Go to the Encryption Keys tab and click on Restore.

That’s it! It should be up and running now on the new server and you should be able to browse to Report Manager and see your reports and they should render.

References

Host a Report Server Database in a SQL Server Failover Cluster
https://msdn.microsoft.com/en-us/library/bb630402.aspx

Configure a Native Mode Report Server Scale-Out Deployment
https://msdn.microsoft.com/en-us/library/ms159114.aspx

 

Robyn Ciliax
Microsoft Business Intelligence Support

Categories: SQL Server MS Blogs

Does rebuild index update statistics?

SQL Server SQL CSS - Fri, 03/06/2015 - 12:27

I recently did a talk to a group of SQL users.  Quite a few facts ended up surprising the audience.   I thought I’d share a few and wanted to start with index rebuild.

If someone asks you the question “Does rebuild index update statistics?”,  you probably will say “of course”.  You may be surprised to know that index rebuild doesn’t update all statistics. 

when you use alter index rebuild, only statistics associated with that index will be updated.   In order to illustrate this better, let’s draw a table

  Index Stats non-index stats ALTER INDEX REORG NO NO ALTER INDEX <index_name>  REBUILD yes but only for stats associated with  that index NO ALTER INDEX ALL  REBUILD yes, stats for all indexes will be updated NO DBREINDEX (old syntax) YES YES

Note that non-index stats means the statistics associated with a column/columns that are automatically created or manually created.

As you can see from above, don’t assume all of your statistics get updated just because you have a maintenance plan to rebuild index.   Sometimes, non-index statistics are very critical as well.  Manual updating statistics may be necessary because our current trigger threshold is high for large tables (20% in most cases as in KB “Statistical maintenance functionality (autostats) in SQL Server” ) though trace flag 2371 can help (in blog).

 

Demo

Here is a demo that alter index doesn’t update all stats.

first use the following script to setup

if object_id ('t') is not null
    drop table t
go
create table t(c1 int, c2 as c1 & 1)
go

create index t1_indx1 on t(c1 )
go
set nocount on
declare @i int
set @i = 0
while @i < 1000
begin
insert into t (c1) values (@i)
set @i = @i + 1
end
go

update statistics t with fullscan
go

go
--this will create a stats on c2
select count(*) from t where c2 =1

go

Because  I ran update statistics, the follow query will show that the t1_indx1 and _WA_Sys_00000002_162F4418 have the same value for laste_updated

SELECT
    obj.name, stat.name, stat.stats_id, last_updated
FROM sys.objects AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
where obj.name = 't'

 

Now, I ran alter index all rebuild.

-- alter all indexes
alter index all on t rebuild
--re-organize won't update even stats of the index
--alter index all on t reorganize

 

Then I ran the following query.  Note that  the last_updated for t1_indx1 has newer time stamp than _WA_Sys_00000002_162F4418 because _WA_Sys_00000002_162F4418 never got updated by alter index command.

--run theh following and note that the stats created by auto stats didn't get updated by rebuild
--only the stats from the index got updated

SELECT
    obj.name, stat.name, stat.stats_id, last_updated
FROM sys.objects AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
where obj.name = 't'

 

Past blog related to statistics: Case of using filtered statistics

 

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

Categories: SQL Server MS Blogs

How It Works: MAX DOP Level and Parallel Index Builds

SQL Server SQL CSS - Mon, 03/02/2015 - 14:53

I have been working on an issue where rebuilding an index leads to additional fragmentation.   Using XEvents I debugged the page allocations and writes and was able to narrow in on the behavior.

There are lots of factors to take into account when rebuilding the index.   I was able to break down the behavior to the worst possible case using a single file database, single heap table,  SORT IN TEMPDB and packing of the heap data to the beginning of the database file when create clustered index is issued.

When the index is build a portion of the data (range) is assigned to each of the parallel workers.  The diagram below shows a MAX DOP = 2 scenario.

Each parallel worker is assigned its own CBulkAllocator when saving the final index pages.   This means Worker 1 gets an extent and starts to fill pages from TEMPDB for Worker 1’s given key range.   Worker 2 is executing in parallel and has its own CBulkAllocator.  Worker 2 acquires the next extent and starts to spool the assigned key range.

Looking at the database a leap frog behavior of values, across extents occurs as the workers copy the final keys into place.

The diagram below shows the leap frog behavior from a MAX DOP = 4 index creation.   The saw tooth line represents the offsets in the file as read during an index order scan.  The horizontal access is the event sequence and the vertical access is the offset in the database file.  As you can see the leap frog behavior places key values all over the file.

Key 1 is at a low offset but Key 2 is at an offset higher than Key 9 as shown in the example above.  Each of the workers spreads 1/4th of the data across the entire file instead of packing the key values together in a specific segment of the file.

In comparison the a serial index build shows the desired layout across the drive.   Smaller offsets have the 1st set of keys and larger offsets always have higher key values.

This mattered to my customer because after a parallel index build an index ordered scan takes longer than a serial index build.  The chart below shows the difference in read size and IOPS requirements.

select count_big(*) from tblTest (NOLOCK)

Serial Built

Parallel Built

Avg Read Size

508K

160K

Duration

00:01:20

00:01:50

# Reads

15,000

52,000

SQL Server reads up to 512K in a chuck for read ahead behavior.   When doing an index order scan we read the necessary extents to cover the key range.  Since the key range is leap frogged, during the parallel build, the fragmentation limits SQL Server’s I/O size to 160K instead of 508K and drives the number of I/O requests much higher.  The same data in a serial built index maximizes the read ahead capabilities of SQL Server.

The testing above was conducted using:  select count_big(*) from tblTest with (NOLOCK)

Hint: You don’t have to rebuild the index in serial to determine how much a performance gain it may provide.   Using WITH(NOLOCK, INDEX=0) forces an allocation order scan, ignoring the key placement and scanning the object from first IAM to last IAM order.  Leveraging the statistics I/O, XEvents and virtual file statistics output you are able to determine the behaviors.

Workarounds
The obvious question is that a serial index rebuild can take a long time so what should I do to leverage parallel index builds and reduce the fragmentation possibilities?

1. Partition the table on separate files matching the DOP you are using to build the index.  This allows better alignment of parallel workers to specific partitions, avoiding the leap frog behavior.

2. For a non-partitioned table aligning the number of files with the DOP may be helpful.   With reasonably even distribution of free space in each file the allocation behavior is such that alike keys will be placed near each other.

3. For single partition rebuild operations consider serial index building behaviors to minimize fragmentation behaviors.

Future
I am working with the development team to evaluate the CBulkAllocator behavior.   Testing is needed but it could be that the CBulkAllocator attempts to acquire 9 (64K) extents to align with the read ahead (512K) chunk size.   Something like this idea could reduce the fragmentation by a factor of 8.

Bob Dorr - Principal SQL Server Escalation Engineer

Categories: SQL Server MS Blogs

Running SQL Server on Machines with More Than 8 CPUs per NUMA Node May Need Trace Flag 8048

SQL Server SQL CSS - Mon, 03/02/2015 - 12:39

Applies To:  SQL 2008, 2008 R2, 2012 and 2014 releases

Note:  The number of CPUs is the logical count, not sockets.   If more than 8 logical CPUs are presented this post may apply.

The SQL Server developer can elect to partition memory allocations at different levels based on the what the memory is used for.   The developer may choose a global, CPU, Node, or even worker partitioning scheme.   Several of the allocation activities within SQL Server use the CMemPartitioned allocator.  This partitions the memory by CPU or NUMA node to increase concurrency and performance.  

You can picture CMemPartitioned like a standard heap (it is not a HeapCreate) but this concept is the same.  When you create a heap you can specify if you want synchronized assess, default size and other attributes.   When the SQL Server developer creates a memory object they indicate that they want things like thread safe access, the partitioning scheme and other options.

The developer creates the object so when a new allocation occurs the behavior is upheld.  On the left is a request from a worker against a NODE based memory object.  This will use a synchronization object (usually CMEMTHREAD or SOS_SUSPEND_QUEUE type) at the NODE level to allocate memory local to the workers assigned NUMA NODE.   On the right is an allocation against a CPU based memory object.  This will use a synchronization object at the CPU level to allocate memory local to the workers CPU.

In most cases the CPU based design reduces synchronization collisions the most because of the way SQL OS handles logical scheduling.  Preemptive and background tasks make collisions possible but CPU level reduces the frequency greatly.  However, going to CPU based partitioning means more overhead to maintain individual CPU access paths and associated memory lists.  

The NODE based scheme reduces the overhead to the # of nodes but can slightly increase the collision possibilities and may impact ultimate, performance results for very specific scenarios.  I want to caution you the scenarios encountered by Microsoft CSS have been limited to very specific scopes and query patterns.

 

Newer hardware with multi-core CPUs can present more than 8 CPUs within a single NUMA node.  Microsoft has observed that when you approach and exceed 8 CPUs per node the NODE based partitioning may not scale as well for specific query patterns.   However, using trace flag 8048 (startup parameter only requiring restart of the SQL Server process) all NODE based partitioning is upgraded to CPU based partitioning.   Remember this requires more memory overhead but can provide performance increases on these systems.

HOW DO I KNOW IF I NEED THE TRACE FLAG?

The issue is commonly identified by looking as the DMVs dm_os_wait_stats and dm_os_spinlock_stats for types (CMEMTHREAD and SOS_SUSPEND_QUEUE).   Microsoft CSS usually sees the spins jump into the trillions and the waits become a hot spot.   

Caution: Use trace flag 8048 as a startup parameter.   It is possible to use the trace flag dynamically but limited to only memory objects that are yet to be created when the trace flag is enabled.  Memory objects already built are not impacted by the trace flag.

References

http://blogs.msdn.com/b/psssql/archive/2012/12/20/how-it-works-cmemthread-and-debugging-them.aspx

 

 

Bob Dorr - Principal SQL Server Escalation Engineer

Categories: SQL Server MS Blogs

Cumulative Update #6 for SQL Server 2014 RTM

SQL Server Release Blog - Mon, 02/16/2015 - 18:52
Dear Customers, The 6 th cumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. To learn more about the release or servicing model, please visit: CU#6 KB Article: http://support.microsoft...(read more)
Categories: SQL Server MS Blogs

Our new engineering blog post

SQL Server Release Blog - Mon, 02/16/2015 - 12:17
In addition to this blog which is primarily intended to announce releases for different SQL Server versions (CUs, SPs etc), we have started a new blog which will mainly target technical content. You can follow this blog at http://blogs.msdn.com/b/sql_server_team...(read more)
Categories: SQL Server MS Blogs

SQL Server Reporting Services 2012 SP2 CU3 Report Rendering Issues

SQL Server Release Blog - Wed, 02/04/2015 - 18:27
I wanted to make you aware of an issue that we’ve seen on a few support cases this week. In these cases, the PDF, Print Preview, and TIFF rendering formats are affected. If you apply SQL Server 2012 SP2 CU3 or CU4, you may see a behavior where the...(read more)
Categories: SQL Server MS Blogs

Frequently used knobs to tune a busy SQL Server

SQL Server SQL CSS - Thu, 01/29/2015 - 23:01

In calendar year 2014, the SQL Server escalation team had the opportunity to work on several interesting and challenging customers issues. One trend we noticed is that many customers were migrating from old versions of SQL Server running on lean hardware to newer versions of SQL Server with powerful hardware configurations. Typical examples would look like this: SQL 2005 + Win 2003 on 16 cores + 128 GB RAM migrated to SQL 2012 + Win 2012 on 64 cores + 1 TB RAM. The application workload or patterns remained pretty much the same. These servers normally handle workloads that is multiple thousand batches per sec. Under these circumstances, the normal expectation is that the throughput and performance will increase in line with the increase in the capabilities of the hardware and software. That is usually the case. But there are some scenarios where you need to take some additional precautions or perform some configuration changes. These changes were done for specific user scenarios and workload patterns that encountered a specific bottleneck or a scalability challenge.

 

As we worked through these issues, we started to capture the common configuration changes or updates that were required on these newer hardware machines. The difference in throughput and performance is very noticeable on these systems when these configuration changes were implemented. The changes include the following:

- SQL Server product updates [Cumulative Updates for SQL Server 2012 and SQL Server 2014]

- Trace flags to enable certain scalability updates

- Configuration options in SQL Server related to scalability and concurrency

- Configuration options in Windows related to scalability and concurrency

 

All these recommendations are now available in the knowledge base article 2964518:

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads

As we continue to find new updates or tuning options that are used widely we will add them to this article. Note that these recommendations are primarily applicable for SQL Server 2012 and SQL Server 2014. Few of these options are available in previous versions and you can utilize them when applicable.

If you are bringing new servers online or migrating existing workloads to upgraded hardware and software, please consider all these updates and configuration options. They can save a lot of troubleshooting time and provide you with a smooth transition to powerful and faster systems. Our team is using this as a checklist while troubleshooting to make sure that SQL Servers running on newer hardware is using the appropriate and recommended configuration.

Several members of my team and the SQL Server product group contributed to various efforts related to these recommendations and product updates. We also worked with members of our SQL Server MVP group [thank you Aaron Bertrand and Glenn Berry] to ensure these recommendations are widely applicable and acceptable for performance tuning.

We hope that you will implement these updates and configuration changes in your SQL Server environment and realize good performance and scalability gains.

 

Suresh B. Kandoth

SQL Server Escalation Team

Microsoft SQL Server

Categories: SQL Server MS Blogs