Category Archives: Microsoft SQL Server

Curing Data-Obesity in OLTP Databases

OLTP databases work best when data that becomes no longer current is then transferred to a separate database for analysis and reporting. There are many ways to do this, but Feodor describes a rapid technique that takes advantage of partitions to automates the rotation of the data and moving it to the analysis server.

The Problem

It is quite common to have an OLTP database that must store large amounts of data which pile up into hundreds of millions, even billions, of rows. What do we do in such cases? In this article I will describe a way to deal with constant flows of OLTP data into production systems, and how to offload this data, describing the process from beginning to end.

Here are some assumptions for the sake of this article:

  •  We have a 50M rows of data in a production database
  • The table contains log data. There are 100,000 rows coming per day of data related to website visits)
  • We are using SQL Server 2012 Enterprise edition
  • We have two SQL Servers – one is for highly-available transactional databases and another one for staging data purposes

How do we tackle the problem?

This is an illustration of the classic problem of mixing  OLTP and Analytics data. We have a database which  was designed to handle OLTP requirements, but it ends up gathering data which is required for analytical purposes. As a general rule, OLTP databases should be light on their feet. They ought to be small, storing only very recent data  that should fit in memory so we can easily do In-Memory-OLTP. (Please note that no fancy technology is needed for In-Memory-OLTP: anyone who is smart enough can do In-Memory-OLTP, since memory nowadays is very cheap and the amount of relevant transactional data is finally getting behind the available memory capacities).

There are several ways to deal with this situation:

  •  Ignore the problem until the data volume grows so much that it starts to jeopardize the database backups and restores, hence availability
  • Copy some of the older data to a different location and delete it from the main database
  • Leave a very small portion of the data in the production system and automate the rotation of the data and the copying of it to the secondary location

Neither of the first two options are much good, but the third one seems quite reasonable and we will explore it in this article.

The Solution

The setup:

For this article we can assume that we have a database called ProdLogDB, which has a table dbo.Log, which is designed like this:

CREATETABLE [dbo].[LOG]

    (

      [column1] [VARCHAR](50) NULL ,

      [column2] [VARCHAR](50) NULL ,

      [column3] [VARCHAR](50) NULL ,

      [column4] [VARCHAR](100) NULL ,

      [notes] [VARCHAR](MAX) NULL ,

      [timestamp] [DATETIME] NOT NULL ,

      [log_id] [INT] IDENTITY(1, 1)

                     NOT NULL ,

      CONSTRAINT [PK_LOG] PRIMARY KEY CLUSTERED ( [log_id] ASC )

    )

ON  [PRIMARY]

Now we need lots of data, and  good representative data too. For the sake of this article I will generate some data with the help of Redgate’s Data Generator. After the generation of data the dbo.Log  table will have data for the time between 2012-01-01 and 2015-01-20. For this time frame I have chosen to have 10 million rows and the data is 1.5Gb. I will be using the same data generation project to generate 100,000 rows for the next few days, while we do the database management and data distribution in this article.

The plan of action:

We have the data, but what should we do next? The idea is to have only a very small portion of the data in our production database, and to offload the rest of the data to a secondary server where the historical data will be stored. In our case we need only 7 days of data in the production system, so we can do analysis: The rest of the data can be someplace else. Furthermore, we want to automate the offloading process in such a way that a scheduled job will run every day and will move the oldest day’s data to the secondary location. (The code below is flexible enough to support the rotation of 1 month’s data, and we can provide a parameter to specify how many days of data to keep, up to 31 days.)

How do we achieve that?

Firstly, we need to ‘empty’ the dbo.Log table and move the data to a table containing the historical data. There are many ways to do this: We can, for example,  bulk-copy the data to a different table, and batch-delete most of the data from the primary table. This, of course, would work, but it will take a long time and use a lot of resources, even if we do batch deletes. I would prefer the following strategy:

  • We Create a new table called dbo.Log1, which has identical schema as the current production table, but it  is partitioned, and ready for data writes from the application.

    We run the following script to do this:

    CREATEPARTITION FUNCTION PF_RingRecyclerByMonth(TINYINT)

    ASRANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)

     

    CREATEPARTITION SCHEME PS_RingRecyclerByMonth

    ASPARTITION PF_RingRecyclerByMonth ALL TO ([PRIMARY])

     

    CREATETABLE [dbo].[LOG1]

        (

          [column1] [VARCHAR](50) NULL ,

          [column2] [VARCHAR](50) NULL ,

          [column3] [VARCHAR](50) NULL ,

          [column4] [VARCHAR](100) NULL ,

          [notes] [VARCHAR](MAX) NULL ,

          [timestamp] [DATETIME] NOT NULL ,

          [log_id] [INT] IDENTITY(11000000, 1)

                         NOT NULL ,

          [Offset] AS ( CONVERT([TINYINT], DATEPART(DAY, [timestamp])) ) PERSISTED

        )

    ON  PS_RingRecyclerByMonth(Offset)

     

    GO

    An important point to note here is that we are creating a new table, with one additional persisted computed column called [Offset]. This column is used to partition the table by the day number. We will use this functionality later on to find the oldest partitions and to send them to the historical table.

    Also, it is worth noting that we have the identity column [log_id], however the identity seed starts from a certain level. This is very important to get right, since we do not want to have duplicate ids later on. Make sure to set the identity seed from a level higher than the current one, even if there is a small gap.

    In my case I have 10,000,000 rows, and I am estimating that in the next few days I will have 10,300,000, so I will set the seed to be 11,000,000. There will be some gap but better to be on the safe side.

  • So far, we have one empty partitioned table (dbo.Log1), and one constantly growing production table (dbo.Log). Now it is time to do something buzzingly exciting: we will move data from one table to the other. In the matter of a second. Here is how:

    BEGINTRANSACTION;

    EXECsp_rename ‘dbo.LOG’, ‘LOG_History’;

    EXECsp_rename ‘dbo.LOG1′, ‘LOG’;

    COMMITTRANSACTION;

    So here is what just happened: our application was writing to the dbo.Log table, which was not partitioned and now, after renaming the tables, the application is writing to the newly created partitioned table. If the application is smartly written, we won’t need to schedule for downtime or maintenance window. The rename should take less than a second and the application should retry to write again any rows that fall in the time of the rename. 

  • At this point we have two tables – dbo.Log – this one is partitioned and growing, and dbo.Log_History – this one is our large table which is not written to anymore.

  • Now it is time to offload the historical data to our secondary server. One way to do it is to create a new database on the secondary server and bulk-copy the data. Another way to do it is to simply backup the current production database and restore it as a historical database on the secondary server. Either way, the result will be the same.

    After the successful copy of the data to the secondary server we can now delete the large table from our production server. (some magic may have to be performed to regain the allocated space after removing the large table, but this is a trivial task for a DBA)

    Also, keep in mind that your DBA will need to do some wizardry if your secondary server is not an Enterprise edition, since the database won’t restore if it contains enterprise edition objects in it.

  • At this point we have two databases on two different servers, one is called ProdLogDB and contains the partitioned table dbo.Log, and the other is called ProdLogDB_History and contains the dbo.Log_History table.

How do we rotate the production table, though?

The idea is to take the oldest partitions and to move them to the historical database.

First we need to create an empty table with the same structure so we can do partition switching. Create the same table in both databases:

CREATE TABLE [dbo].[LOG_SwitchTarget]

    (

      [column1] [VARCHAR](50) NULL ,

      [column2] [VARCHAR](50) NULL ,

      [column3] [VARCHAR](50) NULL ,

      [column4] [VARCHAR](100) NULL ,

      [notes] [VARCHAR](MAX) NULL ,

      [timestamp] [DATETIME] NOT NULL ,

      [log_id] [INT] NOT NULL ,

      [Offset] AS ( CONVERT([TINYINT], DATEPART(DAY, [timestamp])) ) PERSISTED

    )

ON  PS_RingRecyclerByMonth(Offset)

 

GO

Note that there is no identity specification on the [log_id] conlumn.

After creating the tables, we will use them to switch the partitions to them by using the following stored procedure:

IF OBJECT_ID(‘dbo.RingbufferRotate_ByMonth’) IS NULL

    EXEC (‘CREATE PROCEDURE dbo.RingbufferRotate_ByMonth AS RETURN 0;’)

GO

 

ALTER  PROCEDURE RingbufferRotate_ByMonth

    @Now DATETIME = NULL ,

    @PartitionsToKeep INT = 7

AS

    SET NOCOUNT ON

 

    BEGIN

        IF @Now IS NULL

            SET @Now = GETDATE()

 

        DECLARE @BufferSize INT

        SELECT  @BufferSize = COUNT(*)

        FROM    sys.partitions P

                JOIN sys.tables T ON P.object_id = T.object_id

        WHERE   T.name = ‘LOG’

 

        IF @PartitionsToKeep > @BufferSize

            BEGIN

                RAISERROR (‘Can”t keep more partitions than the current buffer size of: %i’, 16, 1, @BufferSize)

                RETURN

            END

 

        DECLARE @CurrentPartition INT

        SET @CurrentPartition = DATEPART(DAY, @Now)

 

        DECLARE @OldestPartitionToKeep INT

        SET @OldestPartitionToKeep = ( @CurrentPartition + @BufferSize

                                       - @PartitionsToKeep ) % @BufferSize

 

              /* Start from the next partition up from current and move forward */

        DECLARE @P INT = ( @CurrentPartition + 1 ) % @BufferSize

 

        DECLARE @I INT = 0

        WHILE @I < @BufferSize - @PartitionsToKeep

            BEGIN

                DECLARE @Sql NVARCHAR(4000) = ‘ALTER TABLE LOG SWITCH PARTITION

TO LOG_SwitchTarget PARTITION

                SET @Sql = REPLACE(@Sql,

, CAST(@P + 1 AS NVARCHAR))

 

                EXEC sp_executesql @Sql

               

                SET @I = @I + 1;

                SET @P = ( @P + 1 ) % @BufferSize

            END

    END

This procedure takes two parameters:

  • @Now, which by default is set to get the current timestamp. It can be used to set the time to a different point and rotate the partitions from there
  • @PartitionsToKeep, which is used to specify how many partitions (i.e. days of data) to keep in the current transactional table and how many to move to the [dbo].[LOG_SwitchTarget] table. This value can be up to 30, but for our case let’s choose to keep only 7 days of data and move all other data

At this point we don’t have that much data in our Log table, since we renamed the tables fairly recently. But in a few days the data will pile up and our procedure will be useful.

Here is a picture of how the data transfer looks like:

2140 clip image002 Curing Data Obesity in OLTP Databases

Note that there are many different ways to implement this solution, but the important part is the automated partitions switching, the bulk-copying of the data from the  [dbo].[LOG_SwitchTarget]

Table on the production server to the [dbo].[LOG_SwitchTarget] table on the secondary server and merging the data to the [dbo].[LOG_History] table.

Here is the MERGE procedure which should be created on the secondary server:

IF OBJECT_ID(‘dbo.Log_History_Merge’) IS NULL

    EXEC (‘CREATE PROCEDURE dbo.Log_History_Merge AS RETURN 0;’)

GO

 

ALTER PROCEDURE dbo.Log_History_Merge

AS

    SET IDENTITY_INSERT LOG_History ON

    MERGE INTO LOG_History AS T

    USING

        ( SELECT    [column1] ,

                    [column2] ,

                    [column3] ,

                    [column4] ,

                    [notes] ,

                    [timestamp] ,

                    [log_id]

          FROM      [dbo].[LOG_SwitchTarget]

        ) AS A

    ON A.[log_id] = T.[log_id]

    WHEN NOT MATCHED THEN

        INSERT ( [column1] ,

                 [column2] ,

                 [column3] ,

                 [column4] ,

                 [notes] ,

                 [timestamp] ,

                 [log_id]

               )

        VALUES ( A.[column1] ,

                 A.[column2] ,

                 A.[column3] ,

                 A.[column4] ,

                 A.[notes] ,

                 A.[timestamp] ,

                 A.[log_id]

               );

    SET IDENTITY_INSERT LOG_History OFF

    TRUNCATE TABLE [LOG_SwitchTarget];

From this point on it is very easy to implement the solution. For example, we can create an SSIS package which will run daily and will carry out the following tasks:

  • Execute the RingbufferRotate_ByMonth procedure, which will switch all older partitions to the LOG_SwitchTarget table
  • Bulk-copy the data from the LOG_SwitchTarget table on the primary server to the LOG_SwitchTarget table on the secondary server
  • Execute the Log_History_Merge procedure on the secondary server
  • Truncate the LOG_SwitchTarget tables on both servers

In order to test the code, let’s get back to our test scenario: we have 10 million rows in the Log_History table. Let’s generate some data in the LOG table, which will have timestamp between 2015-01-20 and 2015-01-28. We can then run the RingbufferRotate_ByMonth procedure and take a look at the data distribution per partition:

SELECT  $ PARTITION.PF_RingRecyclerByMonth(OFFSET) AS PARTITION ,

        COUNT(*) AS [COUNT] ,

        CONVERT(DATE, [timestamp]) Datestamp

FROM    dbo.[LOG]

GROUP BY $ PARTITION.PF_RingRecyclerByMonth(OFFSET) ,

        CONVERT(DATE, [timestamp])

ORDER BY CONVERT(DATE, [timestamp]);

This query returns data from the LOG table, which looks like this:

PARTITION

COUNT

Datestamp

23

111111

2015-01-23

24

111111

2015-01-24

25

111111

2015-01-25

26

111111

2015-01-26

27

111111

2015-01-27

28

111111

2015-01-28

And the following query will show us what we have in the LOG_SwitchTarget table:

SELECT  $ PARTITION.PF_RingRecyclerByMonth(OFFSET) AS PARTITION ,

        COUNT(*) AS [COUNT] ,

        CONVERT(DATE, [timestamp]) Datestamp

FROM    dbo.[LOG_SwitchTarget]

GROUP BY $ PARTITION.PF_RingRecyclerByMonth(OFFSET) ,

        CONVERT(DATE, [timestamp])

ORDER BY CONVERT(DATE, [timestamp]);

The data looks like this:

PARTITION

COUNT

Datestamp

20

111112

2015-01-20

21

111111

2015-01-21

22

111111

2015-01-22

Now it is time to do a bulk-copy of the data from the LOG_SwitchTarget table on the primary server to the LOG_SwitchTarget table on the secondary server.

After the bulk-copy has run, we can execute the dbo.Log_History_Merge procedure. Now we can see that we have the new data into our LOG_History table on the secondary server.

Conclusions

It is important to remove historical data from a fast-working OLTP database, and retain only any necessary summary and aggregate data. The requirement of transaction processing and analysis are quite different, and it is unwise to mix the two.

In this article I have described a simple but effective way to keep our log data under control by rotating the data in the production system and taking it incrementally to a secondary server. There are plenty of other ways to do it, but the method you choose must be able to comfortably handle the heaviest volume of data you are likely to experience.

This article focuses on a solution where the primary server is using Enterprise edition, but in reality there is a possibility to do similar logic even in standard edition, it will just take a bit more effort to code the data flows and some smart management of database file groups.

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Simple Talk RSS Feed

Troubleshooting Memory Issues with Reporting Services

We had a case where Reporting Services was crashing with the following error.

Failed allocate pages: FAIL_PAGE_ALLOCATION 2

The number at the end can be different.  In the customer’s case it was a 1.  In my local repro it was a 2.  This is basically an indication that we are out of memory for the process and the process crashes at that point as we can’t allocate any more memory.  You won’t see an OutOfMemory error, as the allocations come from SQL Operating System (SOS) which hosts the .NET CLR.  So, it is SOS that is trying to do the allocation that fails.  SOS is also used with the SQL Engine, so you will see this error from that side as well when you are out of memory.

Before we get into the meat of it, here is a link to an article that goes through the memory thresholds for Reporting Services and explains how it will handle memory when those thresholds are hit.

Configure Available Memory for Report Server Applications
https://msdn.microsoft.com/en-us/library/ms159206.aspx

When Reporting Services starts, it will calculate what the maximum amount of memory will be for the process.  This will be done in one of two ways.

First, if WorkingSetMaximum is not set in the rsreportserver.config (which it isn’t there by default), then Reporting Services will derive the maximum memory setting based on your total physical memory.  To see this happen, we can look at the Reporting Services Trace Log and look for Derived memory.

rshost!rshost!19c4!01/29/2015-05:03:22:: i INFO: Derived memory configuration based on physical memory as 33486264 KB

servicecontroller!DefaultDomain!b14!01/29/2015-05:03:22:: i INFO: Total Physical memory: 34289934336

You may or may not see something like the following entry if you configure WorkingSetMaximum.

library!DefaultDomain!3278!12/02/2014-16:11:18:: i INFO: Initializing WorkingSetMaximum to ‘12486264’ kilobytes as specified in Configuration file.

We also have the concept of MemorySafetyMargin and MemoryThreshold.  These are used to alert Reporting Services and to start to back off as we need to allocate more memory and we are already pretty full.  These are also configured within the rsreportserver.config.  The default values are 80% and 90% respectively of whatever our Maximum value is set to.

80
90

We can also validate these values within the Reporting Services Trace Log.

library!DefaultDomain!19c4!01/29/2015-05:03:19:: i INFO: Initializing MemorySafetyMargin to ’80’ percent as specified in Configuration file.
library!DefaultDomain!19c4!01/29/2015-05:03:19:: i INFO: Initializing MemoryThreshold to ’90’ percent as specified in Configuration file.

All of this amounts to when Reporting Services will start triggering memory pressure notifications.  These notifications can either be Low, Medium or High.  The link above has a great image that shows you when each one will trigger.

IC156890 Troubleshooting Memory Issues with Reporting Services

NOTE:  You will only see the NotifyMemoryPressure items in the log if you have the log set to Verbose.  Specifically the applicationdomain category.

You also have the opportunity to define WorkingSetMaximum and WorkingSetMinimum in the rsreportserver.config if you know that you have other items running on this machine and you don’t want Reporting Services to starve other items.  Or at the minimum, for Reporting Services and the other services to play nice with each other.  This allows us to cap Reporting Services manually instead of going on the derived value.

Out of Memory Condition

Let’s go back to the FAIL_PAGE_ALLOCATION error that I mentioned at the beginning.  If you receive this, we ran out of memory and couldn’t recover fast enough and then fail to allocate because nothing is available.

Without verbose logging, we can see the following type of behavior in the Reporting Services Log.

processing!ReportServer_0-2!199c!01/29/2015-05:28:37:: w WARN: Processing Scalability — Memory Shrink Request Received
processing!ReportServer_0-2!199c!01/29/2015-05:28:38:: w WARN: Processing Scalability — Memory Shrink Request Received
 Failed allocate pages: FAIL_PAGE_ALLOCATION 1

Here we can see that there were Memory Shrink Requests.  This is an indication that we are starting to hit the ceiling and Reporting Services wants to back off to have more breathing room.  The allocation error caused the process to crash.  It would then restart on it’s own.  There is no other information that is logged.  Nothing in the Event Logs or from the Reporting Services Trace log.

Troubleshooting

rsreportserver.log

The first thing I tend to look at for this is what are the settings defined in the rsreportserver.config.  In the customer’s case, we see the following.

80
90
1000000
400000

This alone is a giant red flag.  WorkingSetMaximum looks really low.  I don’t necessarily care about WorkingSetMinimum.  And the defaults for MemorySafetyMargin and MemoryThreshold are fine.

This is the definition of WorkingSetMaximum from the MSDN article referenced at the top of this blog.

WorkingSetMaximum

Specifies a memory threshold after which no new memory allocations requests are granted to report server applications.

By default, the report server sets WorkingSetMaximum to the amount of available memory on the computer. This value is detected when the service starts.

This setting does not appear in the RSReportServer.config file unless you add it manually. If you want the report server to use less memory, you can modify the RSReportServer.config file and add the element and value. Valid values range from 0 to maximum integer. This value is expressed in kilobytes.

When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum.

If existing requests continue to consume additional memory after the WorkingSetMaximum value has been reached, all report server application domains will be recycled. For more information, see Application Domains for Report Server Applications.

If this value is specified in kilobytes (KB), then the WorkingSetMaximum defined above is only about 1GB of memory.  That definitely won’t be enough.  We won’t get very far with only 1GB of maximum memory.  From the Reporting Services Trace Log, we can see what the customer’s total physical memory was.

servicecontroller!DefaultDomain!9e80!01/26/2015-13:48:48:: i INFO: Total Physical memory: 137372422144 <--137GB!!!

I’m guessing that the thought was that WorkingSetMaximum was defined in megabytes (MB) instead of kilobytes (KB).  So, if we really wanted 100GB for the WorkingSetMaximum, we would need to add two more 0’s.

Verbose Logging

Verbose Logging can also help you understand the situation a little better, especially if the out of memory condition doesn’t happen right away.  From a memory perspective, I only really care about the appdomainmanager category.  We can set that up for verbose by setting the following within the ReportingServicesService.exe.config file.


 
   
 



 
 
 
 
 
 
 

Here is what the output will look like.  I included some INFO messages to show the flow of what happened.

library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing MemorySafetyMargin to ’80’ percent as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing MemoryThreshold to ’90’ percent as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing WorkingSetMaximum to ‘1000000’ kilobytes as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing WorkingSetMinimum to ‘400000’ kilobytes as specified in Configuration file.

servicecontroller!DefaultDomain!3034!01/29/2015-05:44:35:: i INFO: Total Physical memory: 34289934336

library!ReportServer_0-2!17a0!01/29/2015-05:45:46:: i INFO: RenderForNewSession(‘/MemoryPressure/MemoryHogContoso’)
library!ReportServer_0-2!31f8!01/29/2015-05:45:46:: i INFO: RenderForNewSession(‘/MemoryPressure/MemoryHogContoso’) <-- This is the report request received before we started seeing the shrink requests.

appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:50:: v VERBOSE: Received NotifyMemoryPressure(pressureLevel=MediumPressure, kBytesToFree=33960)
appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:50:: v VERBOSE: Memory Statistics: 0 items, 0KB Audited, 0KB Freeable, 924384KB Private Bytes
appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:50:: v VERBOSE: Spent 3ms enumerating MAP items and 2ms dispatching notifications.

appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:51:: v VERBOSE: Appdomain (ReportServer) attempted to free 23016 KB.

appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:52:: v VERBOSE: Received NotifyMemoryPressure(pressureLevel=HighPressure, kBytesToFree=121216)

appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:52:: v VERBOSE: Skipping shrink request for appdomain (WindowsService_0) because no memory consuming requests are registered.
appdomainmanager!ReportServer_0-2!18e4!01/29/2015-05:45:52:: v VERBOSE: Skipping shrink request for appdomain (ReportServer_MSSQLSERVER_0-2-130670054877986461) because not enough time has passed since last shrink request.

Failed allocate pages: FAIL_PAGE_ALLOCATION 1

Assuming the issue was not due to a low value setting on the WorkingSetMaximum value, I would look to see what report executed before we started seeing the shrink requests and look at how that may be consuming a lot of memory.  Depending on your server, it may be a result of several report requests working together and we would need to see what we can do to stagger them or pull back the amount of data they are consuming. 

If it is due to the number of users hitting the Report Server, you may want to look at going into a scale-out configuration to spread the load. Also, if you are running subscriptions, you could look at offloading those to a separate server from on demand report requests.

Performance Monitor

Performance Monitor (perfmon) can be used to see the consumption as well.  Unfortunately, it won’t really do a lot to help you pinpoint the problem.  It will just help you confirm that you do have a problem.

6330.SNAGHTML601db6 5F00 thumb 5F00 4A19DD20 Troubleshooting Memory Issues with Reporting Services

0081.SNAGHTML60c783 5F00 thumb 5F00 102A9A34 Troubleshooting Memory Issues with Reporting Services

The counters I used were the following.

Process : Private Bytes (ReportingServicesService)
Process : Virtual Bytes (ReportingServicesService)
Process : Working Set (ReportingServicesService)
Process : Working Set – Private (ReportingServicesService)
ReportServer : Service : Memory Pressure State
ReportServer : Service : Memory Shrink Amount
ReportServer : Service : Memory Shrink Notifications/sec

Unfortunately, the ReportServer : Service counters did not appear to pick anything up.

To wrap up, there isn’t a magic bullet when it comes to memory issues. We need to investigate what the Report Server is doing and what is running. It could be because of a Report, a 3rd Part Extension, custom code segments.  Try to narrow it down to a specific report and go from there.  Also, make sure Reporting Services is playing nice with other services on the machine, if there are any.  You can use WorkingSetMaximum to do that.

Adam W. Saxton | Microsoft Business Intelligence Server Escalation Services
Twitter    YouTube

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

CSS SQL Server Engineers

Bowled Over by SQL Window Functions

What better way to learn how to construct complex CHECK CONSTRAINTs, use the SQL 2012 window frame capability of the OVER clause and LEAD analytic function, as well as how to pivot rows into columns using a crosstab query? Create the SQL code to score Ten-pin Bowling, of course. Dwain Camps explains the how, what and why.

There is nothing like a real task to illustrate how to use the SQL 2012 LEAD analytical function, the new window frame facility of the OVER clause and a bit about constructing CHECK CONSTRAINTs.

When I was growing up, one of my favorite sports was bowling. It was only later in life after I moved overseas that I realized that bowling actually has another connotations, both in the game of cricket and in lawn Bowls. Bowling is, in Europe called Ten-pin bowling or skittles.

 In bowling, the way that the game is scored is rather complicated, with some arcane conventions. Because of this, it makes a good example to illustrate the power of the analytic functions in SQL 2012, especially when used together with constraints.

Scoring in Bowling

In bowling, the objective is to roll a ball down the lane and knock down all of the ten pins sitting at the end of the lane in each frame. Ideally, you do this in one roll of the ball, but you get two tries.

While in the modern age, we have computerized bowling lanes that tally and provide an overhead projection of your score in bowling, in the old days a bowling game was scored using a score sheet that looks like this.

2131 1 ab73eb1a 638c 4968 8a22 c4602cd3e7e6 Bowled Over by SQL Window Functions

In bowling, balls are rolled and tallied in pairs, with the exception of the tenth frame where there are cases when you will be allowed to roll three balls. Each of the numbered boxes above represent a frame. There are three types of frame depending on the number of pins you knock down:

  • Strike – when the first ball you roll for the frame knocks down all of the ten pins (represented on the score sheet as an X).
  • Spare – when the first ball you roll knocks down (none or) some but not all of the ten pins, and you knock down the rest of the pins with your second ball (represented on the score sheet as a slash for the second ball).
  • Open – when you fail to knock down all ten pins on two attempts.

Strikes and spares are called “marks” (probably because they are represented by X’s and slashes), and if you get a mark in frame ten you’ll be allowed to roll three balls in that frame. We’ll explain the details of scoring in a moment, but for right now let’s focus on how many balls you can roll in a game.

The maximum number of balls you can roll in a game is 21, which results if you get a spare or open frame for the first nine frames, then roll a spare in the tenth frame allowing you to roll one additional ball in that frame. You’ll note that this is the same as the number of small boxes in the score sheet (two in each of the frames one through nine, plus three in the tenth frame).

The minimum number of balls you can roll in a game of bowling is 12, if you were to manage to roll a strike in each frame (knocking down all the pins on the first ball means you don’t need to throw another ball in that frame) and roll three strikes in the tenth frame. In this case, you would achieve a perfect game. The last two balls in the tenth frame are required to score the first strike in that frame.

Scoring for each frame depends on the type of frame you rolled:

  • Open – the frame score is the sum of the prior frame score plus the pin count for the two balls you rolled in the frame.
  • Spare – the frame score is the sum of the prior frame score plus ten (the spare) plus the next ball you roll, or the first ball of the next frame.
  • Strike – the frame score is the sum of the prior frame score plus ten (the strike) plus the number of pins you knock down on the next two balls. This could come from the next frame only or the next two frames if you roll two strikes in a row. In the tenth frame, if you roll a strike on your first ball you get to roll two more balls and these are all added together to get a maximum of 30 points for that frame.

I can still remember my dad teaching me how to score bowling!

Let’s look at a couple of example frames for Big Al and show how strikes and spares appear on the score sheet, and how this tallies out to a score.

2131 1 803597c5 3fbf 42e3 b4a8 d1c28d0668bb Bowled Over by SQL Window Functions

In this game, Big Al has rolled only nine pins (an open frame) in frame one and in the second frame he knocks down three pins on his first ball, plus seven more on his second ball (a spare). Frame two cannot be scored until he rolls one more ball.

2131 1 88c103cf 29b2 4dcd 8484 186bcc9c2504 Bowled Over by SQL Window Functions

Let’s say that Big Al rolls a six in frame three on the spare in frame two (so now frame two can be scored), follows with another spare and then a strike in frame four as shown above. Sixteen (3 + 7 + 6) is added to his score for frame one to get 25 in frame 2, while his spare plus strike combination in frames three and four net him twenty additional points in frame three.

Now let’s say he gets on a roll and bowls another strike. Frame four cannot yet be scored until he rolls one more ball, because of the scoring rule that a strike scores ten pins plus the next two balls.

2131 1 aa4fb541 7b26 43f0 abe6 b31579c99950 Bowled Over by SQL Window Functions

In bowling parlance, two strikes in a row is known as a “double.” So let’s say he really goes on a roll and bowls another strike.

2131 1 b505e0d2 893f 421b a4e3 22866cf51c55 Bowled Over by SQL Window Functions

Now we can score frame four, but frames five and six cannot yet be scored. Big Al’s three strikes in a row is called a “triple” or more commonly a “turkey.” Now we’ll fill out frames seven, eight and nine, and score the results.

2131 1 2e87ad0c 90de 42c6 a650 c22c96ec6531 Bowled Over by SQL Window Functions

At this point, Big Al is doing pretty good. All he needs is eight pins to bowl what is known as “scratch.” People that can average 180 pins or more per game are known as “scratch bowlers,” which in league bowling means they are assigned no handicap, which is usually calculated as 80% of 180 minus their league average.

Let’s look at a few possible outcomes for the tenth frame.

2131 1 933d0422 3df2 4d00 9417 d12e74297114 Bowled Over by SQL Window Functions

In this case, Big Al did not get a strike or a spare: He got an open frame instead, so he doesn’t get to roll a third ball. Still, his game is quite good at a total score of 181.

2131 1 4d6622dc 71f1 4662 a3f5 3835e8e36acc Bowled Over by SQL Window Functions

If Big Al rolls a spare in frame ten, he gets to roll a third ball. So his total score for that frame is nineteen. Another excellent game.

2131 1 2f6657e8 6404 4cb5 8496 34302f6c7393 Bowled Over by SQL Window Functions

If he bowls a strike on his first ball, he gets to roll two more balls. In this case, he managed a spare on top of the strike, to add twenty to his score in frame nine.

2131 1 baa47118 3a35 4773 9533 d47fe48a13be Bowled Over by SQL Window Functions

A perfect tenth frame consists of another turkey, adding thirty points to his frame nine score for a total of 202. Bowling a 200 game in bowling is kind of a magic number. Any time you can do that you’ve had a great game!

Here is what a perfect game looks like.

2131 1 de92d5a1 6ad7 4a75 b9c7 e0e755243aeb Bowled Over by SQL Window Functions

Nearly as rare as a perfect game, there is this symmetrical combination of alternating strikes and spares, resulting in a total score of 200.

2131 1 2c0dc149 afa2 4ad5 a08b 4b4e995bcdb0 Bowled Over by SQL Window Functions

This is called a “Dutch” game, for reasons I was never able to fathom. Like any other sport, bowling has its own lexicon of cool slang including gutter, curve and hook balls.

In case you’re wondering, I’ve never been a scratch bowler (without a handicap) but my high score was 233 and that came along with my personal best of seven strikes in a row! What can I say, I really messed up the other frames of that game to get only a 233.

Now that you’ve learned the ins-and-outs of scoring a game of bowling, let’s see how we might do this in SQL.

A Bowling Scores Table and some Validations

First let’s create a table to store our bowling scores, and provide you a drop statement you can use later to clean up your sandbox.

CREATE TABLE dbo.BowlingScores

(

    GameID      INT         NOT NULL

    ,PlayerID   INT         NOT NULL

    ,Frame      SMALLINT    NOT NULL

    ,PinsBall1  SMALLINT    NOT NULL

    ,PinsBall2  SMALLINT    NULL

    ,PinsBall3  SMALLINT    NULL

    ,PRIMARY KEY (GameID, PlayerID, Frame)

);

GO

– DROP TABLE dbo.BowlingScores;

Note that the first ball in any particular frame must always be present (NOT NULL), while rolling additional balls in a frame may be optional (so they are allowed to be NULL).

A game in bowling only has ten frames, so we’ll add a CHECK CONSTRAINT for that.

ALTER TABLE dbo.BowlingScores

ADD CONSTRAINT bs_frame CHECK

(

    Frame BETWEEN 1 AND 10

);

The first ball in any frame can knock down as few as zero pins (known as a “gutter ball”) or as many as ten (a strike).

ALTER TABLE dbo.BowlingScores

ADD CONSTRAINT bs_ball1 CHECK

(

    PinsBall1 BETWEEN 0 AND 10

);

The CHECK CONSTRAINTs get a little more tricky at this point because they depend on whether you’re in the tenth frame or not.

ALTER TABLE dbo.BowlingScores

ADD CONSTRAINT bs_ball2 CHECK

(

    (

        – In Frame 10, the second ball must always be rolled

        Frame = 10 AND PinsBall2 IS NOT NULL AND

            – If the first ball is a strike, the second ball can be gutter through strike

            (PinsBall1 = 10 AND PinsBall2 BETWEEN 0 AND 10) OR

            – If the first ball is not a strike, the total pins cannot exceed ten

            (PinsBall2 BETWEEN 0 AND 10-PinsBall1)

 

    ) OR

    – A strike in frames 1-9 requires that the second ball is not rolled (NULL)

    (

        PinsBall1 = 10 AND PinsBall2 IS NULL

    ) OR

        – Total pins for frames 1-9 must be <= 10

        PinsBall2 BETWEEN 0 AND 10-PinsBall1

);

Hopefully the comments above guide you through what is being checked to ensure a valid pin count for the second ball of each frame.

Once again, for the third ball we must break the check into pieces depending on whether it occurs in frame ten.

ALTER TABLE dbo.BowlingScores

ADD CONSTRAINT bs_ball3 CHECK

(

    – Before Frame 10 no score is allowed

    (Frame < 10 AND PinsBall3 IS NULL) OR

    – In Frame 10, if two strikes are made the third ball must be gutter through strike

    (Frame = 10 AND PinsBall1 = 10 AND PinsBall2 = 10 AND PinsBall3 BETWEEN 0 AND 10) OR

    – In Frame 10, if the first ball is a strike, the third ball can’t score more than a spare

    (Frame = 10 AND PinsBall1 = 10 AND ISNULL(PinsBall3, 0) BETWEEN 0 AND 10-PinsBall2) OR

    – In Frame 10, if the first two balls are a spare, the third ball must be gutter to strike

    (Frame = 10 AND PinsBall1+PinsBall2 = 10 AND ISNULL(PinsBall3, 0) BETWEEN 0 AND 10) OR

    – In Frame 10, if the first two balls are an open frame, the third ball cannot be rolled

    (Frame = 10 AND PinsBall1+PinsBall2 < 10 AND PinsBall3 IS NULL)

);

Because the PRIMARY KEY consists of game number, person ID and frame number, it won’t be possible to insert the same frame for a game/player more than once. About the only thing we can’t verify using CHECK CONSTRAINTs is a missing frame, or when the next frame is inserted but the second ball of the prior frame was not recorded (and that frame was not a strike).

As with any code you write, you should test your CHECK CONSTRAINTs thoroughly to make sure they don’t let any bad data get through. Here’s a series of test INSERTs that should all fail for the reasons specified in the comments.

– Frame number should be between 1 and 10

INSERT INTO dbo.BowlingScores VALUES(1, 1, 0, 9, NULL, NULL);

GO

– First ball in a frame cannot be more than 10

INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 11, NULL, NULL);

GO

– First ball in a frame cannot be < 0

INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, -1, NULL, NULL);

GO

– First two balls in a frame cannot be > 10

INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 8, 3, NULL);

GO

– Third ball not allowed unless this is frame 10

INSERT INTO dbo.BowlingScores VALUES(1, 1, 1, 8, 1, 1);

GO

– In Frame 10, this is not allowed

INSERT INTO dbo.BowlingScores VALUES(1, 1, 10, 8, 3, 1);

GO

– In Frame 10, this is not allowed either

INSERT INTO dbo.BowlingScores VALUES(1, 1, 10, 8, 2, 11);

GO

A Quick Overview of the T-SQL LEAD Analytical Function

The T-SQL LEAD analytical function consists of the call to LEAD with one to three arguments supplied, and an OVER clause.

LEAD(field_name, row_offset, default_value) OVER (…)

The second and third arguments are optional:

  • row_offset – default is 1 (return the next row).
  • default_value – default value returned if there is no next row (instead of NULL).

The OVER clause requires an ORDER BY, but PARTITION BY is optional.

Scoring a Bowling Game

Let’s start with some sample data that is captured on a per-ball basis, based on Big Al’s game above. The objective is to write a T-SQL query that will display the score for the frame even if the frame is incomplete. This is actually a bit harder to do than it would be if you had to insert the complete results for a frame.

– The first three balls for Big Al before he makes his spare

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 1, 7, NULL, NULL);

 

UPDATE dbo.BowlingScores

SET PinsBall2 = 2

WHERE GameID = 1 AND PlayerID = 1 AND Frame = 1;

 

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 2, 3, NULL, NULL);

We can begin creating our set-based scoring algorithm using the SQL 2012 window frame feature of the OVER clause to calculate a running sum across frames. As a starting point, we’ll mostly ignore strikes and spares, but we’ll create a flag (FrameType) for use later to tell us what kind of frame has been bowled.

WITH PinCounts AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType  = CASE

                        – Strike

                        WHEN PinsBall1 = 10 THEN 2

                        – Spare

                        WHEN PinsBall1 + PinsBall2 = 10 THEN 1

                        – Open frame

                        ELSE 0

                        END

        – Pick up next ball for scoring a spare or strike

        ,NextBall1  = LEAD(PinsBall1, 1) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike

        ,NextBall2  = LEAD(PinsBall2, 1) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike if

        – previous frame was a strike

        ,NextBall3  = LEAD(PinsBall1, 2) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

    FROM dbo.BowlingScores

),

    FrameScore AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType, NextBall1, NextBall2, NextBall3

        – Logic to calculate the pins for this frame will go here

        ,FramePins = PinsBall1 + PinsBall2

    FROM PinCounts

)

SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameScore = CASE

                        WHEN FrameType = 0 AND PinsBall2 IS NOT NULL

                        THEN SUM(FramePins) OVER

                            (

                                PARTITION BY GameID, PlayerID

                                ORDER BY Frame

                                ROWS UNBOUNDED PRECEDING

                            )

                        END

FROM FrameScore;

Note that we have included some results (NextBall1, NextBall2 and NextBall3) that we are not yet using but will use later when we add the logic for scoring strikes and spares. The LEAD function returns a NULL value in cases where the next frame has not been recorded. We’ve also included a stub Common Table Expression (CTE) named FrameScore where we will place most of the scoring logic.

This gives us the correct result for scoring Big Al’s first three balls.

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

1       1         1      7          2          NULL       9

1       1         2      3          NULL       NULL       NULL

While the results are just what we need, our CASE logic in the final SELECT will require some additional work to handle the scoring when spares and strikes are the result in a frame.

On his next ball, Big Al gets seven more pins making a spare.

UPDATE dbo.BowlingScores

SET PinsBall2 = 7

WHERE GameID = 1 AND PlayerID = 1 AND Frame = 2;

If we run the same query, these are our results:

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

1       1         1      7          2          NULL       9

1       1         2      3          7          NULL       NULL

We see that frame two has still not been scored, which is the correct result because Big Al must bowl another ball before it can be scored. On his next ball (on the first ball of frame three), Big Al knocks down six pins.

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 3, 6, NULL, NULL);

To score his spare in frame two properly, we must do two things to our query.

WITH PinCounts AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType  = CASE

                        – Strike

                        WHEN PinsBall1 = 10 THEN 2

                        – Spare

                        WHEN PinsBall1 + PinsBall2 = 10 THEN 1

                        – Open frame

                        ELSE 0

                        END

        – Pick up next ball for scoring a spare or strike

        ,NextBall1  = LEAD(PinsBall1, 1) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike

        ,NextBall2  = LEAD(PinsBall2, 1) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike if

        – previous frame was a strike

        ,NextBall3  = LEAD(PinsBall1, 2) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

    FROM dbo.BowlingScores

),

    FrameScore AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType, NextBall1, NextBall2, NextBall3

        – Logic to calculate the pins for this frame will go here

        ,FramePins = CASE

                        – Calculate the score for a spare

                        WHEN PinsBall1 + PinsBall2 = 10

                        THEN PinsBall1 + PinsBall2 + NextBall1

                        – Calculate the score for an open frame

                        ELSE PinsBall1 + PinsBall2

                        END

    FROM PinCounts

)

SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameScore = CASE

                        WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR

                                – Display the result for a completed spare

                                (FrameType = 1 AND NextBall1 IS NOT NULL)

                        THEN SUM(FramePins) OVER

                            (

                                PARTITION BY GameID, PlayerID

                                ORDER BY Frame

                                ROWS UNBOUNDED PRECEDING

                            )

                        END

FROM FrameScore;

Our changes are:

  • In the CTE named FrameScore we changed the calculation of FramePins to be a CASE statement that looks ahead to the next ball to calculate total pins correctly when the total for two balls is ten (a spare).
  • We’ve modified the calculation of FrameScore to contain the score in a frame with a spare if the next ball has been rolled.

We see that the results are now calculated correctly for his spare.

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

1       1         1      7          2          NULL       9

1       1         2      3          7          NULL       25

1       1         3      6          NULL       NULL       NULL

When Big Al gets four more pins in the third frame to make his spare:

UPDATE dbo.BowlingScores

SET PinsBall2 = 4

WHERE GameID = 1 AND PlayerID = 1 AND Frame = 3;

Our scoring algorithm remains correct.

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

1       1         1      7          2          NULL       9

1       1         2      3          7          NULL       25

1       1         3      6          4          NULL       NULL

Big Al then gets a strike in frame four.

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 4, 10, NULL, NULL);

Our scoring algorithm still works, but it will need some changes to make it score the next balls properly.

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

1       1         1      7          2          NULL       9

1       1         2      3          7          NULL       25

1       1         3      6          4          NULL       45

1       1         4      10         NULL       NULL       NULL

Here is that modified query.

WITH PinCounts AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType  = CASE

                        – Strike

                        WHEN PinsBall1 = 10 THEN 2

                        – Spare

                        WHEN PinsBall1 + PinsBall2 = 10 THEN 1

                        – Open frame

                        ELSE 0

                        END

        – Pick up next ball for scoring a spare or strike

        ,NextBall1  = LEAD(PinsBall1, 1) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike

        ,NextBall2  = LEAD(PinsBall2, 1) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike if

        – previous frame was a strike

        ,NextBall3  = LEAD(PinsBall1, 2) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

    FROM dbo.BowlingScores

),

    FrameScore AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType, NextBall1, NextBall2, NextBall3

        – Logic to calculate the pins for this frame will go here

        ,FramePins = CASE

                        – Calculate the score for a strike

                        WHEN PinsBall1 = 10

                        THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3)

                        – Calculate the score for a spare

                        WHEN PinsBall1 + PinsBall2 = 10

                        THEN PinsBall1 + PinsBall2 + NextBall1

                        – Calculate the score for an open frame

                        ELSE PinsBall1 + PinsBall2

                        END

    FROM PinCounts

)

SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameScore = CASE

                        WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR

                                – Display the result for a completed spare

                                (FrameType = 1 AND NextBall1 IS NOT NULL) OR

                                – Display the result for a completed strike

                                (FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL)

                        THEN SUM(FramePins) OVER

                            (

                                PARTITION BY GameID, PlayerID

                                ORDER BY Frame

                                ROWS UNBOUNDED PRECEDING

                            )

                        END

FROM FrameScore;

We’ve added the scoring logic for a strike (adding the next two balls) in the FrameScore CTE and modified the CASE in the last select to display the score for a strike once it is completed. Let’s complete Big Al’s turkey with two more strikes.

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 5, 10, NULL, NULL);

 

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 6, 10, NULL, NULL);

Our results show that the first strike has now been scored properly, and the interested reader who is following along can verify that other combinations of pin counts also work.

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

1       1         1      7          2          NULL       9

1       1         2      3          7          NULL       25

1       1         3      6          4          NULL       45

1       1         4      10         NULL       NULL       75

1       1         5      10         NULL       NULL       NULL

1       1         6      10         NULL       NULL       NULL

We’ll dispense with the statements that add an individual ball at a time and simply insert the next three frames for Big Al’s game.

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 7, 9, 1, NULL);

 

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 8, 10, NULL, NULL);

 

INSERT INTO dbo.BowlingScores

VALUES(1, 1, 9, 8, 1, NULL);

Big Al’s score now looks as follows (correct when matched against the scoring sheet we showed earlier):

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

1       1         1      7          2          NULL       9

1       1         2      3          7          NULL       25

1       1         3      6          4          NULL       45

1       1         4      10         NULL       NULL       75

1       1         5      10         NULL       NULL       104

1       1         6      10         NULL       NULL       124

1       1         7      9          1          NULL       144

1       1         8      10         NULL       NULL       163

1       1         9      8          1          NULL       172

Scoring the Tenth Frame

To score the tenth frame properly, just a little tweak is required to our query (in the PinCounts CTE).

WITH PinCounts AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType  = CASE

                        – Strike

                        WHEN PinsBall1 = 10 THEN 2

                        – Spare

                        WHEN PinsBall1 + PinsBall2 = 10 THEN 1

                        – Open frame

                        ELSE 0

                        END

        – Pick up next ball for scoring a spare or strike

        ,NextBall1  = LEAD(PinsBall1, 1

                        – For frame 10, next row returns NULL

                        ,CASE

                            WHEN Frame = 10 AND PinsBall1 = 10

                            THEN ISNULL(NULLIF(PinsBall2, 10), PinsBall3)

                            WHEN Frame = 10 AND PinsBall1 + PinsBall2 = 10

                            THEN PinsBall3

                            END

                        ) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike

        ,NextBall2  = LEAD(PinsBall2, 1

                        – For frame 10, next row returns NULL

                        ,PinsBall3

                        ) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike if

        – previous frame was a strike

        ,NextBall3  = LEAD(PinsBall1, 2) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

    FROM dbo.BowlingScores

),

    FrameScore AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType, NextBall1, NextBall2, NextBall3

        – Logic to calculate the pins for this frame will go here

        ,FramePins = CASE

                        – Calculate the score for a strike

                        WHEN PinsBall1 = 10

                        THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3)

                        – Calculate the score for a spare

                        WHEN PinsBall1 + PinsBall2 = 10

                        THEN PinsBall1 + PinsBall2 + NextBall1

                        – Calculate the score for an open frame

                        ELSE PinsBall1 + PinsBall2

                        END

    FROM PinCounts

)

SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameScore = CASE

                        WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR

                                – Display the result for a completed spare

                                (FrameType = 1 AND NextBall1 IS NOT NULL) OR

                                – Display the result for a completed strike

                                (FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL)

                        THEN SUM(FramePins) OVER

                            (

                                PARTITION BY GameID, PlayerID

                                ORDER BY Frame

                                ROWS UNBOUNDED PRECEDING

                            )

                        END

FROM FrameScore;

The new logic that applies to frame ten can be seen as the third argument to LEAD (the result to return instead of NULL) for NextBall1 and NextBall2 in the PinCounts CTE.

Let’s see how this works for each of the outcomes of frame tens we suggested to complete Big Al’s game. I will show only the tenth frame for each.

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

– Open frame for Frame 10

1       1         10     6          3          NULL       181

– Spare in Frame 10 followed by third ball of 9 pins

1       1         10     7          3          9          191

– Strike in Frame 10 followed by spare in balls 2 and 3

1       1         10     10         7          3          192

– Turkey in Frame 10

1       1         10     10         10         10         202

The interested reader can verify that if frame ten is not complete, the FrameScore is NULL (for all possible cases).

Scoring a Game of Strike/Spare/Strike (in Consecutive Frames)

Let’s say that Big Al is on another roll in game two. We can insert a second game quite easily, making it consecutive strikes and spares.

INSERT INTO dbo.BowlingScores

          SELECT 2, 1, 1, 10, NULL, NULL

UNION ALL SELECT 2, 1, 2, 3, 7, NULL           

UNION ALL SELECT 2, 1, 3, 10, NULL, NULL

UNION ALL SELECT 2, 1, 4, 6, 4, NULL

UNION ALL SELECT 2, 1, 5, 10, NULL, NULL

UNION ALL SELECT 2, 1, 6, 9, 1, NULL

UNION ALL SELECT 2, 1, 7, 10, NULL, NULL

UNION ALL SELECT 2, 1, 8, 8, 2, NULL

UNION ALL SELECT 2, 1, 9, 10, NULL, NULL

UNION ALL SELECT 2, 1, 10, 7, 3, 10;

Our bowling-scoring algorithm shows us this for the result:

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

2       1         1      10         NULL       NULL       20

2       1         2      3          7          NULL       40

2       1         3      10         NULL       NULL       60

2       1         4      6          4          NULL       80

2       1         5      10         NULL       NULL       100

2       1         6      9          1          NULL       120

2       1         7      10         NULL       NULL       140

2       1         8      8          2          NULL       160

2       1         9      10         NULL       NULL       180

2       1         10     7          3          10         200

Scoring a Perfect Game

In the final game of his three game series, Big Al rolls a perfect game, basically the game of his life!

INSERT INTO dbo.BowlingScores

          SELECT 3, 1, 1, 10, NULL, NULL

UNION ALL SELECT 3, 1, 2, 10, NULL, NULL

UNION ALL SELECT 3, 1, 3, 10, NULL, NULL

UNION ALL SELECT 3, 1, 4, 10, NULL, NULL

UNION ALL SELECT 3, 1, 5, 10, NULL, NULL

UNION ALL SELECT 3, 1, 6, 10, NULL, NULL

UNION ALL SELECT 3, 1, 7, 10, NULL, NULL

UNION ALL SELECT 3, 1, 8, 10, NULL, NULL

UNION ALL SELECT 3, 1, 9, 10, NULL, NULL

UNION ALL SELECT 3, 1, 10, 10, 10, 10;

GameID  PlayerID  Frame  PinsBall1  PinsBall2  PinsBall3  FrameScore

3       1         1      10         NULL       NULL       30

3       1         2      10         NULL       NULL       60

3       1         3      10         NULL       NULL       90

3       1         4      10         NULL       NULL       120

3       1         5      10         NULL       NULL       150

3       1         6      10         NULL       NULL       180

3       1         7      10         NULL       NULL       210

3       1         8      10         NULL       NULL       240

3       1         9      10         NULL       NULL       270

3       1         10     10         10         10         300

If you ever do this in league bowling, trust me you will have too much to drink that night!

Averaging the Scores for a League Bowling Night

Typically in a league bowling night, each player bowls three games. To average all the games for the players, the same query can be used with some simple modifications (like encapsulating the former query into a second CTE).

WITH PinCounts AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType  = CASE

                        – Strike

                        WHEN PinsBall1 = 10 THEN 2

                        – Spare

                        WHEN PinsBall1 + PinsBall2 = 10 THEN 1

                        – Open frame

                        ELSE 0

                        END

        – Pick up next ball for scoring a spare or strike

        ,NextBall1  = LEAD(PinsBall1, 1

                        – For frame 10, next row returns NULL

                        ,CASE

                            WHEN Frame = 10 AND PinsBall1 = 10

                            THEN ISNULL(NULLIF(PinsBall2, 10), PinsBall3)

                            WHEN Frame = 10 AND PinsBall1 + PinsBall2 = 10

                            THEN PinsBall3

                            END

                        ) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike

        ,NextBall2  = LEAD(PinsBall2, 1

                        – For frame 10, next row returns NULL

                        ,PinsBall3

                        ) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

        – Pick up second following ball for scoring a strike if

        – previous frame was a strike

        ,NextBall3  = LEAD(PinsBall1, 2) OVER

                        (

                            PARTITION BY GameID, PlayerID

                            ORDER BY Frame

                        )

    FROM dbo.BowlingScores

),

    FrameScore AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

        ,FrameType, NextBall1, NextBall2, NextBall3

        – Logic to calculate the pins for this frame will go here

        ,FramePins = CASE

                        – Calculate the score for a strike

                        WHEN PinsBall1 = 10

                        THEN PinsBall1 + NextBall1 + ISNULL(NextBall2, NextBall3)

                        – Calculate the score for a spare

                        WHEN PinsBall1 + PinsBall2 = 10

                        THEN PinsBall1 + PinsBall2 + NextBall1

                        – Calculate the score for an open frame

                        ELSE PinsBall1 + PinsBall2

                        END

    FROM PinCounts

),

    GameScores AS

(

    SELECT GameID, PlayerID, Frame, PinsBall1, PinsBall2, PinsBall3

            ,FrameScore = CASE

                            WHEN (FrameType = 0 AND PinsBall2 IS NOT NULL) OR

                                    – Display the result for a completed spare

                                    (FrameType = 1 AND NextBall1 IS NOT NULL) OR

                                    – Display the result for a completed strike

                                    (FrameType = 2 AND ISNULL(NextBall2, NextBall3) IS NOT NULL)

                            THEN SUM(FramePins) OVER

                                (

                                    PARTITION BY GameID, PlayerID

                                    ORDER BY Frame

                                    ROWS UNBOUNDED PRECEDING

                                )

                            END

    FROM FrameScore

)

SELECT PlayerID, AverageScore=AVG(FrameScore)

FROM GameScores

WHERE Frame = 10

GROUP BY PlayerID;

The above query will show the three game averages for all players, assuming that you had that data available. In this case, we’ve used Big Al’s first game of 181 to calculate the average.

PlayerID  AverageScore

1         227

A Query to Display the Game Results Like a Score Sheet

Using the same three CTEs developed above (PinCounts, FrameScore, GameScores) we can construct a final SELECT that flattens the results for each player/game. The CTEs are omitted for brevity.

SELECT GameID, PlayerID

    ,F1B1   = MAX(CASE Frame WHEN 1 THEN Ball1 END)

    ,F1B2   = MAX(CASE Frame WHEN 1 THEN Ball2 END)

    ,F2B1   = MAX(CASE Frame WHEN 2 THEN Ball1 END)

    ,F2B2   = MAX(CASE Frame WHEN 2 THEN Ball2 END)

    ,F3B1   = MAX(CASE Frame WHEN 3 THEN Ball1 END)

    ,F3B2   = MAX(CASE Frame WHEN 3 THEN Ball2 END)

    ,F4B1   = MAX(CASE Frame WHEN 4 THEN Ball1 END)

    ,F4B2   = MAX(CASE Frame WHEN 4 THEN Ball2 END)

    ,F5B1   = MAX(CASE Frame WHEN 5 THEN Ball1 END)

    ,F5B2   = MAX(CASE Frame WHEN 5 THEN Ball2 END)

    ,F6B1   = MAX(CASE Frame WHEN 6 THEN Ball1 END)

    ,F6B2   = MAX(CASE Frame WHEN 6 THEN Ball2 END)

    ,F7B1   = MAX(CASE Frame WHEN 7 THEN Ball1 END)

    ,F7B2   = MAX(CASE Frame WHEN 7 THEN Ball2 END)

    ,F8B1   = MAX(CASE Frame WHEN 8 THEN Ball1 END)

    ,F8B2   = MAX(CASE Frame WHEN 8 THEN Ball2 END)

    ,F9B1   = MAX(CASE Frame WHEN 9 THEN Ball1 END)

    ,F9B2   = MAX(CASE Frame WHEN 9 THEN Ball2 END)

    ,F10B1  = MAX(CASE Frame WHEN 10 THEN Ball1 END)

    ,F10B2  = MAX(CASE Frame WHEN 10 THEN Ball2 END)

    ,F10B3  = MAX(CASE Frame WHEN 10 THEN Ball3 END)

FROM GameScores a

CROSS APPLY

(

    SELECT Ball1    = CASE PinsBall1 WHEN 10 THEN ‘X’ ELSE CAST(PinsBall1 AS VARCHAR) END

        ,Ball2      = CASE

                        WHEN PinsBall2 IS NULL THEN

                        WHEN PinsBall1+PinsBall2 = 10 THEN ‘/’

                        WHEN PinsBall1+PinsBall2 < 10 THEN CAST(PinsBall2 AS VARCHAR)

                        ELSE

                        END

        ,Ball3      = CASE

                        WHEN PinsBall3 IS NULL THEN

                        WHEN PinsBall3 = 10 THEN ‘X’

                        WHEN PinsBall2+PinsBall3 = 10 THEN ‘/’

                        WHEN PinsBall1+PinsBall2 < 10 THEN CAST(PinsBall3 AS VARCHAR)

                        ELSE

                        END

) b

GROUP BY GameID, PlayerID;

This is referred to as a crosstab query because the result pivots the rows to columns. The CROSS APPLY is used to calculate intermediate results that greatly simplifies the redundancy that would have otherwise been present in each MAX function.

If you’ve never seen or written a crosstab query before, we encourage you to add in the results for the total frame score where it makes sense to do so.

Alternative Data Modeling

A data modeling purist may argue that it would be better to have a BallID column in the table, with one column to record the pin score for that ball. Then BallID could be 1 or 2 in any frame, and 3 would be allowed in frame ten. This also allows for one row to be recorded in frames one through nine when a strike is obtained by the bowler.

However that approach would not allow for the same level of validations performed by the CHECK CONSTRAINTs that we’ve provided.

Another approach would be to include a column for FrameScore in the table. Since FrameScore can easily be calculated as we’ve shown, that is not a normalized data model. However if you chose to use that approach, you could also calculate the FrameScore in an AFTER INSERT, UPDATE TRIGGER. Because of the need to “look forward,” you would not be able to use a computed column.

Conclusions and What we have Learned

More than anything, I hope that you’ve concluded that learning complex SQL can be fun!

In this article, we have demonstrated quite a number of somewhat advanced T-SQL features including:

  • How to construct complex CHECK CONSTRAINTs to ensure that data being inserted into your target table is valid.
  • Using the SQL 2012 window frame capability of the OVER clause to calculating a running total.
  • Using the SQL 2012 LEAD analytic function to “look ahead” to later rows in a set.
  • Pivoting rows into columns using a crosstab query.

Because we have built up our query one little bit at a time working through a scoring example, it may be possible to further simplify the query and make it a little bit shorter. We’ll leave that as an exercise for our more intrepid readers, and we are interested to see those results posted to the comments section of this article.

Our methodology demonstrated how you can take a relatively complex problem and break it into sub-problems that you can solve one little bit at a time, and this is an essential skill in formulating and writing complex queries in any dialect of the SQL language.

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Simple Talk RSS Feed

Frequently used knobs to tune a busy SQL Server

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

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

CSS SQL Server Engineers

Do I really need to use DTC Transactions?

It is sometimes common practice to enable Distributed Transaction (DTC) behavior but it can be unnecessary, and adds unwanted overhead.  

DTC has the ability to determine single phase vs two phase commit requirements.  A DTC transaction involves resource managers (RMs) of which SQL Server can be one of them.  If a single resource manager is involved in the transaction there is no need to perform 2-phase commit.   DTC shortcuts the activity and performs a single-phase commit safely.   This reduces the communication between the DTC and RM managers.  However, the overhead of the DTC manager is still involved making the transaction slightly slower than a native TSQL transaction.

Single Phase

The following is a single phase DTC commit example.

begin distributed tran
go

update dbTest.dbo.tblTest set object_id = 100
go

commit tran
go

Notice the trace output does not indicate a prepared state.  This is a direct indication of a single phase commit.

8400.image 5F00 thumb 5F00 42B1CBB8 Do I really need to use DTC Transactions?

Two Phase

The following is a 2-phase commit example.

begin distributed tran
go

update MYREMOTESERVER.dbTest.dbo.tblTest set object_id = 100
go

commit tran
go

The transaction involved the local instance (RM=1) and a remote instance (RM=2).  With 2 RMs involved DTC commits the transaction under full, 2-phase commit protocol.   Notice the prepared state in the trace indicating full, 2-phase commit protocol is being used.

6787.image 5F00 thumb 5F00 7D98FE81 Do I really need to use DTC Transactions?

You may want to review the DTC transactions executing on your system, looking for prepared state.  If the DTC transactions running on your system are not using 2-phase commit protocol you should consider removing DTC from the transactions in order to improve performance.

Bob Dorr – Principal SQL Server Escalation Engineer

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

CSS SQL Server Engineers

SQL Server database recovery tool retrieves by the table

Michael Donnelly, president of the Minnesota SQL Server User Group, has worked as a database administrator (DBA) and a database architect during his career. As a DBA, time was always of the essence, but one thing he wouldn’t delegate was restoring the database from backups. He considered SQL Server database recovery too technical a task to leave to junior DBAs or developers.

Had restoration been less intense, “it would be hugely beneficial to hand off tasks to some of the junior DBAs,” said Donnelly, who today is a consultant at Digineer.

Donnelly described the process he had to go through to restore a 300 GB file. While he needed only the one table — the file — he still had to restore the entire database from backup and then move it to the development environment. Before that point, however, he had to clean the development environment so the database would fit. He believes it would have been much easier if he had to copy only the tables he needed.

After hearing about this challenge from Donnelly and other SQL Server users, Minneapolis-based data-recovery company Kroll Ontrack Inc. jumped at the business opportunity. The resulting product, a SQL Server version of the vendor’s Ontrack PowerControls software, became generally available in November. It lets users select and restore just the tables they need using drag-and-drop functionality.

The new product is welcome news for Shannon Roderick, a database administrator at Gillette Children’s Specialty Healthcare in St. Paul, Minn., who, along with Donnelly, participated in alpha testing for Ontrack PowerControls for SQL.

At his job prior to Gillette, Roderick frequently had to restore a database after a developer made an incorrect update. It would then take approximately 20 minutes to get the data back. Roderick used Dell’s LiteSpeed software to perform the restoration, which wasn’t as quick or efficient as he wanted.

“When you update production,” he said, “and everything goes haywire, you want [the data] back as soon as possible.”

Kroll Ontrack began the alpha test in July 2014. It found its testers through the Minnesota SQL Server User Group. Donnelly feels that Ontrack PowerControls allows DBAs to hand off database restoration responsibilities. “Even a developer who is a little bit SQL-Server-savvy can use this [product] to input their data,” he said. “I’ve seen third-party tools with something similar to this, but never something with drag-and-drop functionality.”

However, Donnelly has a few requests for later versions. Ontrack PowerControls is currently rated only for SQL Server 2012, and Donnelly uses SQL Server 2014. He also specifically asked for referential integrity in version two.

Roderick has been using Ontrack PowerControls for development, administration and recovery. However, he did not use it for recovery in the production server during the alpha test. “It’s quick and easy to get into and doesn’t have a lot of overhead,” he said.

He noted that with Ontrack PowerControls, there isn’t any need to go into the production server. All he has to do is go into the backup and pull the information. Compared to LiteSpeed and similar products, Roderick said, “It’s more efficient for the one task it does well.”

Roderick added that Ontrack PowerControls is also workflow-oriented. “It’s a very, very targeted recovery,” he added. Roderick wants to see more granular recovery in the next version.

“The best part about [the alpha test] is that Kroll has been so interested in getting feedback from the community,” Roderick said. Roderick and Donnelly have met regularly with Kroll Ontrack representatives and made suggestions that in turn they saw incorporated into the product.

Kroll Ontrack’s involvement with its alpha users has been far greater than in past tests, said Tom McCaffrey, product director for enterprise solutions. However, McCaffrey said, the process has been such a success that management at Kroll Ontrack is encouraging other teams to implement the same tactics.

McCaffrey intends to continue meeting with the Minnesota SQL Server User Group over the next year to get more feedback on the SQL Server database recovery technology. He said Kroll will be releasing monthly updates to Ontrack PowerControls for SQL, with the added features to be determined by user request. The top new feature right now is increasing the granularity of the restore — Roderick’s request. In the next release, according to McCaffrey, users can expect to see Donnelly’s requests — support for SQL Server 2014, 2008 and 2005, and enhanced referential integrity — as well as compressed backups.

Jessica Sirkin is associate site editor of SearchSQLServer. Email her at jsirkin@techtarget.com and follow us on Twitter: @SearchSQLServer.

Next Steps

Find out how and why to split your backup data into multiple files

Learn about SQL Server backup and restore best practices in this tutorial

Check out another third-party SQL Server recovery tool

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Microsoft SQLServer news and trends

Cumulative Update #5 for SQL Server 2014 RTM

Dear Customers,

The 5th 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:

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Microsoft SQL Server Release Services

SIOS SANLess cluster helps keep Epicure websites up and running

Traffic on the two primary websites operated by Canadian direct-sales food distributor Epicure went up substantially at the end of each month — so much that the spikes often made the sites unusable. Epicure tried working with the third-party vendor that hosts the sites to resolve the problem, but saw no improvement. And the North Saanich, British Columbia company’s business model is dependent on the two sites. It uses one website to market itself and its selection of food and cookware products; the other site is used by the independent “consultants” who sell the Epicure products at tasting parties to place orders and communicate with the company.

Russell Born, senior network infrastructure administrator at Epicure, looked for a way to keep the sites running smoothly through the end-of-month rush — and, while doing a Google search last year, he found SIOS Technology Corp. and its DataKeeper data replication software and SANLess clustering tools. The SIOS offerings enable users looking to guard against processing downtime to create Windows or Linux server clusters that utilize local storage only, avoiding the need to deploy a storage area network (SAN).

“Before that, I thought we required a SAN,” Born said. SIOS also supports SAN-based clusters, but Epicure would have faced some difficulties if [it] went that route,” he added. According to Born, a SAN leads to higher costs and more setup effort; it also creates a “single point of failure,” since the entire operation becomes dependent on it to function. To combat that, Born could have deployed multiple SANs, but he said doing so would have become prohibitively expensive.

No backup in case of outage

Epicure had another good reason to want to bolster its websites, which use SQL Server to process and fulfill orders and track the work of its consultants. Shortly before Born learned about SIOS, Epicure’s data center suffered an outage that lasted several hours. “We didn’t have redundant Web servers, so there was no failover externally,” he said.

The company had two servers, one for each site, and both were reaching their resource limit, which contributed to the end-of-month performance problems. In addition to SIOS, Born turned to cloud computing vendor Amazon Web Services (AWS) to help relieve the pressure. The new setup for Epicure includes a primary Web server running on-premises and a cloud-based failover system, and the SIOS software replicating data between them. Concurrently, Epicure is upgrading from SQL Server 2008 to SQL Server 2012, and from Windows 2008 to Windows 2012.

Jerry Melnick, COO of San Mateo, Calif.-based SIOS, said one of the primary motivators for the deployment was Epicure’s concerns about disaster protection and recovery. “There is so much depending on those sites,” he said. “Users are ordering off the sites. If they go down, it could take weeks to get [them] back up.”

One method for ensuring high availability is to deploy servers in a second location so that, if the primary data center goes down, you can failover to the backup one. But Melnick said that has a high startup cost, so he recommended companies like Epicure tap into a cloud provider, such as AWS, for disaster recovery. SIOS’ job, according to Melnick, is to be the linchpin of the SANLess cluster.

More money left on Epicure’s table

Consolidating the two servers into one primary system also saved the company some money on the DataKeeper software. “SIOS’ licenses are based on the number of servers, so it was half the cost,” Born said.

Also, prior to working with SIOS, Epicure had been using SQL Server Enterprise Edition solely because that was the only way to get the high-availability features included in the database. Now that Epicure is using the SIOS software, Born said it has been able to downgrade to the much less-expensive SQL Server Standard Edition, achieving “very substantial cost savings” on that as well.

Born started out with some reservations about the SIOS technology. “I did have concerns initially that if SIOS lost its data connection, there would be some corruption,” he said. “But we didn’t see that.” In fact, he was able to work out all of the startup issues with the SQL Server version of DataKeeper while testing the product, and only needed to make a couple calls to SIOS support. “It migrated over seamlessly,” Born said, adding that so far, he’s pleased with what the SIOS software has done for Epicure’s websites and the SQL Server systems that power them.

Jessica Sirkin is associate site editor of SearchSQLServer. Email her at jsirkin@techtarget.com and follow us on Twitter: @SearchSQLServer.

Next Steps

Mavis Tire uses SIOS DataKeeper to go SANLess and save time and money on SQL Server

Nottingham University choses SIOS host-based replication over HP Array features

SQL Azure business continuity features keep data available even when the system fails

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Microsoft SQLServer news and trends

JDBC: This driver is not configured for integrated authentication

I’ve had about 4 cases in the last two months that centered around the following error when trying to use Windows Integrated authentication with JDBC.

java.sql.SQLException: This driver is not configured for integrated authentication

The key environment point on this was that they were trying to do this on a Linux platorm and not a Windows platform.  Specifically they were running WebSphere on a Linux platform.  The last one I worked on was running WebSphere 8.5.

There is only one location within the JDBC Driver where this particular error is raised.  It is when we are trying to use Kerberos and the Authentication Scheme is set to NativeAuthentication, which is the default setting for this property. Starting in the JDBC 4.0 driver, you can use the authenticationScheme connection property to indicate how you want to use Kerberos to connect to SQL.  There are two settings here.

NativeAuthentication (default) – This uses the sqljdbc_auth.dll and is specific to the Windows platform.  This was the only option prior to the JDBC 4.0 driver.

JavaKerberos – Makes use of the Java API’s to invoke kerberos and does not rely on the Windows Platform.  This is java specific and not bound to the underlying operating system, so this can be used on both Windows and Linux platforms.

So, if you are receiving the error above, there are three possibilities that could be causing it to show up.  First, you actually specified authenticationScheme=NativeAuthentication in your connection string and you are on a Linux Platform.  Second, you specified integratedSecurity and omitted authenticationScheme, which defaulted to NativeAuthentication, and you are on a Unix/Linux Platform.  Third, you are using a version of the JDBC Driver prior to the 4.0 driver and trying to use Integrated Authentication on a Unix/Linux platform.  In the third case, even if you specify authenticationScheme=JavaKerberos, it won’t help as the older drivers aren’t aware of it, so it is ignored.

The following document outlines how to use Kerberos with the JDBC Driver and walks through what is needed to get JavaKerberos working properly.

Using Kerberos Integrated Authentication to Connect to SQL Server
http://msdn.microsoft.com/en-us/library/gg558122%28v=sql.110%29.aspx

Another aspect that was discovered was the that it appears that the WebSphere 8.5 release comes with the 3.0 version of the SQL JDBC Driver.  This will not honor the JavaKerberos setting and you will get the error listed above. 

Configuration

So, you will need to make sure your driver is updated to the 4.0 driver or later.  After that is done, you will need to make sure that the Kerberos Configuration file (krb5.ini or krb5.conf) is configured properly on your platform.  In the above referenced documentation we have a sample of what that should look like.  You will also need to generate keytab files for the platform to reference.  A login configuration file also needs to be setup.  If you don’t have one, the driver will automatically configure it using the Krb5LoginModule.  If you need to use a different Login Module, you will need to make sure that is configured for your environment.  Assuming all of that is in place, the driver should work using JavaKerberos to connect. 

The following blog does a good job of walking through the steps to get this setup for Java.  It indicates Weblogic, but really it just goes through the java aspects.  It walks through how to create the keytab files and what to do with the krb5.ini file.

Configure Kerberos with Weblogic Server (really just a Java reference)
https://blogbypuneeth.wordpress.com/configure-kerberos-with-weblogic-server/

Known Limitation

If you have a multiple domain environment with SQL Servers in different domains that you are trying to hit, you will run into issues.  We found that in order to get it to work properly, you need to set the default domain within the Kerberos configuration file, to the domain that the SQL Server resides in.  You can only have one default domain, so if you have multiple SQL Servers in different domains, you are going to have to pick one. 

SQL JDBC Driver Versioning and Files

I’ve also heard a lot of questions and seen confusion on the file versioning, file name and system requirements.  Here is a table where I tried to highlight what comes with what driver for reference.

JDBC Driver Version

JAR Files

JDBC API Support

Supported JVM

2.0

sqljdbc.jar

3.0

1.5

sqljdbc4.jar

4.0

1.6 or later

3.0

sqljdbc.jar

3.0

1.5

sqljdbc4.jar

4.0

1.6 or later

4.0

sqljdbc.jar

3.0

1.5

sqljdbc4.jar

4.0

1.6 or later

4.1

sqljdbc.jar

3.0

1.5

sqljdbc4.jar

4.0

1.6 or later

sqljdbc41.jar

4.0

1.7 or later

Also, we have documentation regarding the System Requirements that you can look at that goes a little further into this.

System Requirements for the JDBC Driver
http://msdn.microsoft.com/en-us/library/ms378422(v=sql.110).aspx

Hopefully this will help clear things up for you when using the SQL JDBC Driver on a Unix/Linux Platform.

Adam W. Saxton | Microsoft SQL Server Escalation Services
http://twitter.com/awsaxton

Recommended article: Chomsky: We Are All – Fill in the Blank.
This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

CSS SQL Server Engineers

vNext Failover Clustering in Windows Server Technical Preview

Interested in the new features coming for Failover Clustering?  I recently stopped over at Channel 9 and did an interview where we discussed some of the big clustering and availability features in Windows Server Technical Preview.

Here’s the link:
http://channel9.msdn.com/Shows/Edge/Edge-Show-125

 vNext Failover Clustering in Windows Server Technical Preview

Thanks!
Elden Christensen
Principal Program Manager Lead
Clustering & High-Availability
Microsoft 

Congratulations, all very exciting new features, especially the new storage spaces sharing nothing, just love that icon smile vNext Failover Clustering in Windows Server Technical Preview

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Clustering and High-Availability