Thursday, April 13, 2017

SQL Server Splits the page when I change an Integer Column Value

I have written few posts on Index Fragmentation and how they can be checked. External Fragmentation occurs as a result of Page-Split and it happens when there is no space in the page to accommodate the change (new record or an update). When no space, it moves approximately 50% of records to a new page, accommodating the change to relevant page.

Read Understanding Index Fragmentation – SS SLUG Aug 2013 – Demo I post for understanding fragmentation.

Read SQL Server - Checking Index Fragmentation - Understanding LIMITED, SAMPLED and DETAILED for understanding various modes that can be used for checking fragmentation.

For understanding page-split, lets take an example. Have a look on below table.

-- creating test table
CREATE TABLE dbo.TestTable
    Id int PRIMARY KEY
    , Value char(4000) NOT NULL
-- inserting 2 records
INSERT INTO dbo.TestTable
    (2, replicate('a', 4000)), (5, replicate('b', 4000));

It has two records. Since the approximate size of a record is 4004 bytes (4 for int column and 4000 for char column), SQL Server can use one page for holding both records. The size of a data page is 8KB. The below query proves that both records are in the same page.

SELECT Id, Value, ph.*
FROM dbo.TestTable
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph;

If I make a change to Value column, SQL Server does not need to split the page as the current value uses 4000 bytes. Example, if I change the first record value as REPLICATE('x', 4000), it can simply replace the old value, hence no page split. See below code and the result.

UPDATE dbo.TestTable
    SET Value = replicate('x', 4000)
WHERE id = 2
SELECT Id, Value, ph.*
FROM dbo.TestTable
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph;

Just like that, if we make a change to the integer value, we should not see any change on pages used. Let's make a small change and see.

UPDATE dbo.TestTable
    SET Id = 3
WHERE id = 2;
SELECT Id, Value, ph.*
FROM dbo.TestTable
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph;

Did you notice the change? Why the second record has been moved to a new page? Mean Page-Split has been occurred with this changed?

Is it because of the data type? It is not. Note that this is not just an integer column. It is the PRIMARY KEY; The clustered key. What happened was, SQL Server updated the Value column as an in-place update. It does not require additional space. However SQL Server does not perform in-place update for key columns. It performs delete-plus-insert operation for key column updates. It requires space for the INSERT. That is the reason for the page split. Understand that this behavior is not only for integer, it is for all key columns.

This is why we discourage you to select a column as the key column if it can be changed by a business operation. It is always recommended to use a non-volatile column as the key column that do not expect changes on values.

SQL Server - Checking Index Fragmentation - Understanding LIMITED, SAMPLED and DETAILED

Indexes get fragmented based on the operation we perform against records and modification we do against index keys. We use one of the Dynamic Management Function which is called sys.dm_db_index_physical_stats for checking both Internal Fragmentation and External Fragmentation. Today, I had to check one my clients data table for fragmentation which is a very large table. Since this is a quick look, I decided the use SAMPLED mode for checking the fragmentation instead of my usual mode DETAILED. Once the check is done, we had a short-conversation on the mode selected; difference between them and why should use them. This conversation resulted this post.

If you need to know Internal and External Fragmentation with a sample code, please see my post: Understanding Index Fragmentation – SS SLUG Aug 2013 – Demo I.

Here are the differences between three modes;

Mode Details
  • Fastest way of scanning the index for fragmentation.
  • For B-Tree indexes, only the Parent Level Pages are scanned.
  • For Heaps, associated PFS and IAM pages are checked and data pages are scanned.
  • This cannot be used for checking Internal Fragmentation.
  • This still shows External Fragmentation because it uses Pointers to the Leaf Level in Parent Pages for calculating the External Fragmentation.
  • Slower than LIMITED as it scans leaf pages too.
  • It uses 1% of all pages for scanning, hence the fragmentation values are approximate.
  • If the number of pages are lesser than 10,000, then DETAILED mode is used instead of SAMPLED mode.
  • Slower than LIMITED as it scans leaf pages too.
  • It uses 1% of all pages for scanning, hence the fragmentation values are approximate.
  • If the number of pages are lesser than 10,000, then DETAILED mode is used instead of SAMPLED mode.

The reason for me to use SAMPLED mode for checking is, the number of records it has. Since it takes long time for scanning all pages, I decided to use SAMPLED mode because it could help to me determine whether the index is fragmented or not.

This shows how the fragmentation is shown with all three modes;

Wednesday, April 12, 2017

How to find the related file and the page numbers of records in SQL Server data table

Sometime, for some administration works, we need to know which file has been used for holding our records and the related page numbers. How can we easily find these information?

Generally we use DBCC IND and DBCC PAGE but there are two more great functions that can be used for finding the same.

The first function is %%PHYSLOC%%. This returns the RID (Record Identifier) as a hexadecimal value. The RID consists file number, page number and, record number. The second function is a table-valued function which is fn_PhysLocCracker. It accepts the RID returning from %%PHYSLOC%% and returns three-columned table for file number, page number and, record number. Combing these two, we can get the information as we want. Here is an example;

SELECT p.BusinessEntityID, p.Title
    , p.FirstName, p.LastName
    , ph.*
FROM [Person].[Person] p
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph

Monday, April 10, 2017

Adding Power BI Reports to Reporting Services

The integration between Reporting Services and Power BI was started with Reporting Services 2016, allowing us to pin SSRS Reports to Power BI Dashboards. Not only that, we can have Power BI files hosted in Reporting Services but Reporting Services does not support opening them inside the portal.

This integration has been extended and it will be available with the next version of SQL Server. With this, we can create reports using Power BI and add them directly to Reporting Services. Not only that, Reporting Services Portal supports opening Power BI Reports inside the portal. Can we see it now? Yes, Technical Preview is available for testing this.

In order to test this, you need to download two files; Power BI Desktop For SSRS (PBIDesktopRS_x64) and SQLServerReportingServices.

Power BI Desktop for SSRS is a separate Power BI instance and it can be installed side by side without removing existing Power BI installation. SQLServerReportingServices is the latest installation for SQL Server which is a standalone installation. It installs Reporting Services and allows us to configure just like the way we do with Reporting Services 2016.

Installing Reporting Services
Let's start it with Reporting Services installation. Just like the way you install any other software, double-click on SQLServerReportingServices.exe and start the installation. You will get the usual agreement window, accept it and continue.

And you should see the final output within less than one minute;

This works without any issue even if you have SQL Server 2016 installation in your machine. The configuration of newly installed Reporting Services can be started by either clicking the button in the last window or searching and opening the right Configuration Manager. If you search for it, you will see two with the exact name (if you have already installed 2016), need to pick the right one.

When you open the Configuration Manager for the one you installed, you should get a similar screen, note the instance name: RSServer.

If you remember the 2016 installation, you know that you get an option for configuring Reporting Services during the installation: Install and Configure or Install Only. Since we did not get anything like that with this installation, we need to configure everything manually. First thing is creating the database.

Go to Database page and click on Change Database.

It starts the wizard, make sure that you select Create a new report server database. This requires a SQL Server database engine. If you have not installed SQL Server, then you need to install it before configuring the report server database. If you have already installed Reporting Services 2016, then you have a Report Server database that is for 2016 instance. If you have, DO NOT OVERWRITE THE EXISTING ReportServer DATABASE. Make sure you give a different name for it. As you see below, I have named my Report Server database as ReportServer_2017.

You can accept the default values for other pages in the wizard unless you need to change it.

Once it is configured, you need to configure Web Portal URL and Web Service URL. Again, make sure it does not conflict with your existing Reporting Services URLs. As you see, I have set the Virtual Directory for Web Portal as Reports_2017.

You need to do the same for Web Service URL as well. Once done, you should be able to browse the Reporting Server with the configured URL.

Installing Power BI
No difference between this installation and standard Power BI Desktop installation.

Once the installation is completed, open it (Note that if you had previous Power BI Desktop installation, now you have two Power BI instances). You should see SQL Server Reporting Services tag when opening Power BI. That confirms that you open the right instance.

Creating Reports and Publishing to Reporting Services
Note that this can be used for creating standard Power BI reports as well. However, if you create a report for Reporting Services, you need to make sure the following;
  • Source is Analysis Services
  • Connection type is Live Connection

At the moment, it supports only Analysis Services but we will surely see all other types with future releases.

Connect with your Analysis Services database and create a report as you need. This is what I created.

In order to publish this to Reporting Services, you can either save this directly to Reporting Services or save as a local file and upload it using Reporting Services Upload File menu. Let's save it directly to Reporting Services.

You should see new saving option as SQL Server Reporting Services.

Select it and enter the URL configured for the portal.

Name it and click OK.

You should see the success message if it can connect with your Reporting Services. Once saved, go to the portal and see it. You should see the added Power BI report. With the previous version of Reporting Services, if you click on added Power BI file, it downloads the file. But with this version, if you click on it, it opens the report and all functionalities works just like the way it works with Power BI online service.

Good thing is, it allows you to edit the report using Power BI;

Once you modified, you do not need to upload the file again because Power BI can directly save it to Reporting Services. The changes will be immediately appear in Reporting Services.

See, how easy it is. Try and see, you will see a lot more options with future releases.