What is "High Water Mark" in ETLing?

When extracting data from one database to another in continuous fashion, two common techniques are used; delete the destination and reload with extraction or load the destination as an incremental load. First technique is straight forward, but for the second, changes have to be identified, it is commonly done with a datatime column in the source that holds either created date or last modified date.

What is the link between this and "High Water Mark"? The High Water Mark is used for indicating the highest water level for a tide or flood. Same theory can be applied for our scenario, considering datetime column in the source as High Water Mark.

User needs sysadmin permission to the instance but he should be denied permission on some databases

If someone wants to manage the SQL Server instance completely and perform any activity in the server, she or he should be a member of sysadmin server-level role because it bypasses all security checks. But what if you need to someone be a member of sysadmin server-level role and restrict him to some databases? Is it possible?

Although it is not a common scenario, a requirement like this can be popped up and we may struggle to find a solution. How can we do it? If User1 is a member of sysadmin server-level role, we have no way of restricting User1 accessing a database. In order to handle the scenario, User1 needs to be given all permissions offered with sysadmin role manually without adding him to sysadmin role and limit his permission only for required databases. Yes, it is possible to address individual permission with individual statements up to some extent but it is time consuming and makes the whole process complex. It becomes more and more complex and increases the maintenance cost if more users have to be managed in similar way. The best solution is, grant Control Server permission to User1.

The Control Permission is similar to Sysadmin fixed server role but not identical. It offers all the permissions offered with Sysadmin role but there are some limitations with some objects such as DBCC commands and some system stored procedures. However, a requirement like above can possibly managed with it.

For more info on this: https://msdn.microsoft.com/en-us/library/ms188659.aspx

Let's test this. Assume that Jane and Joe are database administrators and they need full permissions on the instance. However, Joe should not be able to access AdventureWorksDW2014 database. Let's try to handles this scenario with Control Permission though it does not offer a 100% working solution.

Below code creates logins for Jane and Joe; Jane is in Sysadmin fixed server role and Joe has Control Server permission.

USE master;
GO

-- Create Jane login
CREATE LOGIN Jane
WITH PASSWORD = '123'
, CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF;
GO

-- Add Jane to Sysadmin role
ALTER SERVER ROLE sysadmin ADD MEMBER Jane;
GO

-- Create Joe Login
CREATE LOGIN Joe
WITH PASSWORD = '123'
, CHECK_EXPIRATION=OFF
, CHECK_POLICY=OFF;
GO

-- Grant Control Server permission to Joe
GRANT CONTROL SERVER TO Joe;
GO

Let's check permission assigned for both. Below query confirms that both have same permissions.

EXECUTE AS LOGIN = 'Jane';
SELECT * FROM sys.fn_my_permissions(null, null);
REVERT;
GO

EXECUTE AS LOGIN = 'Joe';
SELECT * FROM sys.fn_my_permissions(null, null);
REVERT;
GO

Now we need to restrict Joe to AdventureWorksDW2014 database. For testing purposes, let's deny permission to both Jane and Joe.

-- deny permission to AdventureWorksDW2014
USE AdventureWorksDW2014;
GO

-- We do not need User account in the database
-- because both Jane and Joe have admin rights
-- But let's create them
CREATE USER Jane FOR LOGIN Jane;
CREATE USER Joe FOR LOGIN Joe;
GO

-- This gives deny permission to Jane
ALTER ROLE db_denydatareader ADD MEMBER jane
ALTER ROLE db_denydatawriter ADD MEMBER Jane
GO

-- This gives deny permission to Joe
ALTER ROLE db_denydatareader ADD MEMBER Joe
ALTER ROLE db_denydatawriter ADD MEMBER Joe
GO

Let's see how this works now. Below code shows that Jane can still access the database (tables particularly) but Joe cannot access.

-- Jane tryint to access AdventureWorksDW2014
-- Jane will be able to access because she is in sysadmin
-- role and system bypasses security checks.
EXECUTE AS LOGIN = 'Jane';
SELECT * FROM AdventureWorksDW2014.dbo.DimProduct;
REVERT;
GO

-- Joe trying to access AdventureWorksDW2014
-- Joe will NOT be able to access because he is not in syadmin 
-- role and system will check for permissions
EXECUTE AS LOGIN = 'Joe';
SELECT * FROM AdventureWorksDW2014.dbo.DimProduct;
REVERT;
GO

Have we solve the problem with this solution completely? Since we just restrict table reading and writing, we have not addressed it fully but I believe that it is managed up to some extent now.

Can Joe change his User Account in the AdventureWorksDW2014 with high permissions?

Can Joe upgrade himself as Sysadmin?

As you see, many things are not possible with Joe's account. However this needs to be thoroughly tested as I have not used this in production.

I did not use my HDInsight cluster but my credit is over

Have you noticed this? Or have you already experienced this? Are you going to complain on it? Then wait.............

Microsoft Azure HDInsight billing is different with other billing methods (Once I saw that it is same for all Azure offers, but I am not sure). Billing starts once the cluster is created with the storage and you will be charged continuously even though you do not use it. I know that this annoys you but that is the fact unfortunately.

For clarifying this, I checked with my account, this is what I see;

When click on View more details;

This clears shows that I am being charged for the cluster I have created even though it is not being used.

How do we stop charging? There is no way of stopping, only possible way is, once done with the cluster, drop it and if you want it again, re-create it (This annoys more, I know that).

I prefer to have some kind of facility that allows us to enable/disable but I cannot see something like that and did not see any upcoming similar facility with searches made.

If you want to know about pricing on this, have a look on this page: http://azure.microsoft.com/en-gb/pricing/details/hdinsight/

How to navigate HDInsight cluster easily: CloudXplorer

Once HDInsight cluster is created for Big Data implementations, navigating the cluster (or Azure Blob Storage) via logical folders and accessing files inside, writing and copying files in and out is something we really require. For that, rather than navigating via Azure Management Portal, an application like Windows File Explorer is very beneficial. There are few tools available for accessing the cluster, some can be installed as an add-on to Visual Studio too. However I found that CloudXplorer is a good tool for accessing it, hence thought to write a post on how to use it.

Let me create a HDInsight cluster. First of all, a Storage is required, below screen shows creating a storage called azure01dineshhdistorage.

And then creating a Cluster  called azure01dineshhdinsight.

Once created, need to download CloudXplorer from http://download.cnet.com/CloudXplorer/3000-18500_4-75940217.html and install it. After installation, tool can be opened and add the storage account.

In order to connect with Windows Azure Account, we need to get the Name of our storage used for hdinsight configured and the Primary Access Key of it. Follow below steps for that;

1. Open Windows Azure Management Portal.
2. Click on Storage on left panel for opening all storage added.
3. Select the one configured for hdinsight and click on Manage Access Keys button on bottom panel.
4. Get Storage Account Name and Primary Access Key copied.

Next steps are;

1. Open CloudXplorer installed.
2. Click on Accounts icon in the ribbon.
3. Click on New button and select Windows Azure Account.
4. Paste copied Storage Account Name and Primary Access Key to Name and Secret Key text boxes.

5. Click OK to save setting. Select the account added and click OK again to access the cluster.

Now we can see, copy, add, create files just like we do with Windows File Explorer. If need to see all items in the cluster, click on Report icon in the ribbon and list as you want.

SQL Server Brain Basher of the Week #015 - SSIS Failing package

Just like last Brain Basher, this week one also based on Integration Services. Let's try to understand a very common misconception on package failures.

If one of the tasks is failed during the execution, does it stop the execution of entire package?

Assume that you have configured two Script Tasks as below and the first one is configured to ForceExecutionResult to Failure.

When this package is executed, Script Task 1 gets failed. Does the execution continue with Script Task 2 or not? Let's execute and see.

As you see, the second task is not getting executed. If a question "Why" is asked, a common answer is "failure of previous task stops executing the package".

Yes, execution does not continue but it is not exact reason. The reason for this (and for many cases) is Precedence Constraint set between these two. Since it is set as Success, execution does not continue with the second task, hence it stops executing the package. However, if the Precedence Constraint is set as Completion, it executes the second task too.

Execution of the entire package at a failure of a task will be stopped if FailPackageOnFailure property is set as true for the task. It forces to stop the execution of entire package regardless of the Precedence Constraint set.

SQL Server Brain Basher of the Week #014 - SSIS Variables and Parameters

We have been using variables inside Integration Services packages for maintaining temporary values for various computations and passing external values to the package when executing. With SQL Server 2012, Parameters were introduced which is similar to variables and many still get confused with these two; what to be used and when and why they should be used, hence this week Brain Basher is based on Integration Services Variable and Parameters.

What is the difference between Variables and Parameters with Integration Services?

Variables are used to hold values temporarily for calculations and configurations. A common usage of variables is, maintaining one for Connection String of one of Connection Managers. Before SQL Server 2012, we were able to pass new values for these variables at the execution time using Configuration Files, however with SQL Server 2012 and later, it is not possible with Project-Level-Deployment.

Parameters are similar to variables. However,  they can be defined at Package Level, making them as Private to the package. They can also be defined at Project Level, making them as Global to the project. Once defined, parameters can be used for calculations and holding values for configurations. And values them can be passed at the execution time with Project Deployment Model.

The main difference between these two is, with modern deployment, variable is for internal calculations and not for getting external values at the execution time. Parameter can be used for assigning values to variables and value of it can be passed at the execution time.

SSRS: The parameter is missing a value

Are you experiencing the following error with one of your Reporting Services parameters? Specifically with a hidden parameter?

To overcome this issue, make sure you have a default value for the parameter. Here are the steps;

1. General Parameter Properties. Note that visibility is hidden.

2. Then set as source for the parameter as you want.

3. And set the default value as you want.

Once this is done, it will work without any issue.

SSRS: How to format datetime as Oracle YYYYMMDDHH24

If a SSRS report has to be designed for an Oracle database and datetime needs to be displayed with date and hour, there are many different ways for handling it. Oracle supports many different formatting on datetime and sometime values in the report need to be displayed as per Oracle format such as YYYYMMDDHH24. That was one of the specific requirements I had, so thought to share the way I did it assuming that it will be useful for you too.

With Oracle, following code returns 2015052813 if the date is 2015-05-28 and time is 1:30pm.

SELECT to_char(sysdate, "YYYYMMDDHH24") FROM dual;

If the same format has to be displayed with SSRS, here is the way to write the expression.

SQLSaturday #417 - Speakers and Sessions

Speaker: Pinal Dave

Pinal Dave is a Technology Evangelist for Pluralsight. He has written over 3,300 related articles on his blog at http://blog.sqlauthority.com. He has also authored 17 video courses and authored 11 books. He specializes in SQL Server performance tuning and has over 11 years of hands-on experience and holds a Master's of Science degree and a number of certifications, including MCTS, MCDBA, and MCAD (.NET). He has presented at many technology conferences and is consistently a top-rated speaker. 

Session I: Performance in 60 Seconds - SQL Tricks Everybody MUST Know

Data and Database are very important aspects of application development for businesses. Developers often come across situations where they face a slow server response, even though their hardware specifications are above par. This session is for all the Developers who want their server to perform at blazing fast speed, but want to invest very little time to make it happen. We will go over various database tricks which require absolutely no time to master and require practically no SQL coding at all. After attending this session, Developers will only need 60 seconds to improve performance of their database server in their implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. 

Session II: Indexes – The Unsung Hero

Slow Running Queries  are the most common problem that developers face while working with SQL Server. While it is easy to blame the SQL Server for unsatisfactory performance, however the issue often persists with the way queries have been written, and how Indexes has been set up. The session will focus on the ways of identifying problems that slow down SQL Server, and Indexing tricks to fix them. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session.

Speaker: Nilabja Ball

Nilabja is a Premier Field Engineer for Microsoft Global Business Support (GBS), India. He has more than 8 years of SQL Server and BI experience. He is engaged with the customer either for proactive activities including health check, assessment and support or for reactive tasks to troubleshoot and fix complex and critical issues. He has an engineering degree from Visvesvaraya technological university and keen to learn new technologies.

Session I: SQL Server 2014 In-Memory Overview

Overview of the in-memory technologies which are included in SQL Server 2014. We will cover In-Memory OLTP (AKA "Hekaton"), In-Memory Data Warehousing (AKA "Apollo") as well as SSD Buffer Pool Extensions. This session will give understanding of the broad in-memory strategy as well as a basic understanding of the technologies involved.

Session II: NoSQL on Azure: An Introduction to DocumentDB

NoSQL data stores have gone mainstream. In this session, we will survey the expanding NoSQL landscape on Azure, providing a big-picture view of these technologies. Also spend a bit more time on DocumentDB, Microsoft’s document database-as-a-service, and Azure Search. This session is aimed at anybody who’s new to NoSQL or is curious about the NoSQL options that Azure provides today.

Speaker: Sammani Palansuriya 

Sammani Palansuriya is a Visual C#.net MVP in Sri Lanka with more than 5 years' experience in various aspects of .net technologies. Currently she is stationed as a Senior Software Engineer at Exilesoft and working on projects based on asp.net and azure technologies.

Session I: My Data in the Cloud: Microsoft Azure SQL Database

Microsoft Azure SQL Database (formerly SQL Azure) is another Microsoft Azure service that offers relational database-as-a-service with proven performance, scalability, data security and near-zero administration.

This discussion will give you an understanding about Azure SQL Database; what this service is and what it offers. It will also help you understand what sort of requirements will benefit from Azure SQL Database, and what factors you need to know in order to choose this service, along with pros and cons of choosing Azure SQL Database

Speaker: Prabhakaran Bhaskaran 

Prabhakaran is SQL Server consultant at Pythian, a company based in Ottawa, Canada, specializing in Remote database services. He is former SQL Server Support Engineer at Microsoft. He always loves to learn and share his knowledge.

Session I: Inside Transactional Replication
You can probably do a good job in configuring and maintaining transactional replication in your environment. You can get these things done with reasonable quality without knowing how SQL Server does things under the covers. However if you want to take your skills to the next level and troubleshoot complex replication issues, it's crucial to know how transactional replication works internally.

This session will answers questions like; How does the log reader know where to start the scan? How does it add the commands to the Distribution database? How many threads are involved in a replication process? How does distribution agent look for pending transactions to distribute to its subscribers? Knowing all these will help you to manage and troubleshoot the replication very effectively. Come lets learn together!

Speaker: Thurupathan Vijayakumar 

A versatile technical speaker with 5 years of industry experience in SQL Server technologies, software development and cloud computing.

Session I: Setting your first foot in the world of Business Intelligence

Have you ever wondered why we need a data warehouse when we have the good old stringent relational database scheme. Do you want to get to know the concepts of data warehousing and business intelligence? Then this is the session you have been looking for: A tour from OLTP to OLAP with Microsoft technologies. Gear up for the excitement!                                                                                                                                                                                             

                                                                                                                    .

.

Speaker: Indika Ariyasinghe 

Indika Ariyasinghe is an Associate Architect - Technology in Virtusa (Pvt) Ltd. He holds a Software Engineering Degree from University of Peradeniya, Faculty of Engineering and possesses 11 years total experience in software development including business intelligence.

Session I: Enterprise MS BI for everyone, The Journey So Far
Virtusa is a rapidly evolving company with diverse business intelligence needs spanning all level of the organization; from CxOs to Engineers (numbering around 8000 employees); from mobile devices to public area dashboards. In this session, I will share with you the challenges faced and how we have over come them so far, and plans for beyond future.                                                                                                                                                                                                                                     .

.                                                                                                                                                                              

                                            .

Speaker: Gogula Aryalingam 

Gogula is a database and business intelligence professional with 9+ years on the Microsoft data platform. He is an MCSE, MCT, former MVP and an MSc in IT. His passion is in building BI and data-based solutions, writing about things he works with and speaking about them. He loves teaching and working with the technical community. He has spoken at local and foreign events, along with regular stints at the local SQL Server user group. He was also an SME for Microsoft Certifications.

Session I: Telling Stories with Data

Data is all around us; it's in bits and pieces; it's in shapeless chunks. These data can tell us stories. Organizations have loads of these data lying around, but seldom make use of it. Modern data analytics methods however, give us a plethora of methods to access this data, mash them up and tell stories using them. Stories, that can help make effective decisions.

This session looks at how you could collect data from the various sources in your organization, combine them, process them and tell stories using the wealth of tools given to us by Microsoft. Words you would hear are self-service data analytics, big data and machine learning.

Speaker: Dinesh Priyankara

Dinesh Priyankara (MSc IT) is a SQL Server MVP in Sri Lanka with 14 years’ experience in various aspects of database technologies including business intelligence. He has done many local and foreign business intelligence implementations and has worked as subject matter expert on various database and business intelligence related projects. Currently he is stationed as a Senior Architect - Specialist at Virtusa (Pvt) Ltd. He teaches, consults and runs training programs for various institutes.

Session I: Big Data Analytics: The Microsoft Way
Have you already incorporated  “Big Data” into your analytical solution, but still feel you are going nowhere with it? Or are you planning to bring “Big Data” into your analytical solution, but not sure which way to go? Do you wonder about how to get it done, and what tools and methods that you need to use? Let us talk about it; let us understand the “Big Data” requirement; how to and the best way of bringing it into analytical stack using the Microsoft way, of course.

For more info: Visit http://www.sqlsaturday.com/417/EventHome.aspx

Guest Post: How much data will be loss when DBCC CHECKDB reports Consistency Errors?

This post is written by: Priyanka Chouhan

Every company or an individual, who use database systems, always wants to ensure the database integrity of the stored database. Therefore, you should always run the database consistency against your database, to know the accuracy and purity. In order to regularize the database maintenance routine, run DBCC CHECKDB daily, weekly, monthly. You might have thinking that how do you know what data potentially gets lost? Your next question could be about the performance enhancements after running the DBCC CHECKDB and also about the need of running DBCC CHECKDB without any performance implications.

At times, database system specifically mentions that using this option leaves the database in an unsupported configuration, but still if you proceed then there are chances of database inconsistency. This write-up lets you know the process of identifying the possible potential data loss when DBCC CHECKDB reports corruption in your SQL Server database.

Carefully read the output from DBCC CHECKDB: After running the DBCC CHECKDB, it will show the information in bulk to get you started. Read the output thoroughly, and remember the below mentioned information.

Identify the affected objects: You need to use the output of DBCC CHECKDB to identify the affected object. Usually DBCC CHECKDB does not recommend the REPAIR_ALLOW_DATA_LOSS option for every corruption case. When you identify errors it will give you an Object in ID value of the affected object, which can be used to find out the affected object and then you can get the infected table.

Identify the affected page: After knowing the information about the affected object, you can decide whether the infected data is critical or not. If you talk about an organization, then all the data can be considered as critical and need a treatment. For example, let’s assume that the identified data is critical and you need to minimize the data loss. By identifying the unprocessed page number from the output of DBCC CHECKDB, you can determine the corrupted page. Additionally, it will also explain you about the reference pages related to the corrupt page. Ultimately, it will provide you the complete information that is required to know about the corruption in the DBCC CHECKDB output.

Resolve the corruption: After successfully find out the corrupted page along with its index format. You do not need to restore from backup or run REPAIR_ALLOW_DATA_LOSS to repair; instead you can just disable and rebuild the index. The time taken is dependent upon the size of the index and the impact it has on the system. It's up to you to decide that either you will do it during or after working hours.

Since you have the latest backup and you are planning to restore your database from it, then drop this idea. It would be useless to restore data from the backup, as this restoring process will consume lots of time, in case you own a big database. Therefore, it is better to repair only known corrupted pages. Disabling and rebuilding the index is always a better way to make your complete database offline to resolve the corrupted non-clustered index. However, whenever the DBCC CHECKDB reported a bunch of errors, it is not always compulsory to take your database offline to fix it.

SQL Server Brain Basher of the Week #013 - Global Temporary Tables

Though we create temporary tables frequently for manipulating temporary data, global temporary tables are infrequently created. As you know, local temporary table is limited to the current session, and global temporary table is for all available sessions. Local temporary table will be automatically dropped when your session is disconnected and global temporary table will be remained until last connection that has referenced the table is disconnected. Now the question for the week is;

Global Temporary Table is global for the database or for the instance?

When this question is asked during my classes/workshops, most common answer for this is, it is global to the database. However, it is not the correct answer, It is global to the entire instance. Once a global temporary table is created, it can be accessed, modified and deleted by any user in any database hosted in the same instance.

Here is a sample code for that;

-- Connect with AdventureWorks database
USE AdventureWorks2014;
GO

-- Create global temporary table
CREATE TABLE ##TestTable
(
 Id int PRIMARY KEY
 , Name varchar(200)
);
GO

-- Query the table in the same session
SELECT * FROM ##TestTable;

Here is the result of SELECT.

Here is the result when accessing the table in a different session.

And here we are accessing the table from a different database. It works without any issue.

Should I consider Collation when creating a temporary table?

We always create temporary tables for manipulating data for temporary processes and reports. However, unawareness of collation related issues with tempdb might introduce unexpected result with processes and reports, should we really consider it?

What is collation? Collation refers to set of rules that describe how to store and compare data. These rules includes order of characters, case-sensitivity of them, accent-sensitivity of them, kana character types and character width. SQL Server supports on setting collation at instance level, database level or even at column level.

Now, why we need to consider tempdb collation? Have a look on following example. It creates a Database. Note that collation of it is SQL_Latin1_General_CP1_CS_AS (Case Sensitive). Then the table is created and two sample records are added. Assume that this is the business requirement and last SELECT should be based on case and it returns only one record.

USE master;
GO

-- creating database with case-sensitive collation
CREATE DATABASE TestDatabase100 COLLATE SQL_Latin1_General_CP1_CS_AS;
GO

USE TestDatabase100;
GO

-- create a table
CREATE TABLE dbo.TestTable
(
 Id int PRIMARY KEY
 , Name varchar(100)
);
GO

-- insert two records
INSERT INTO dbo.TestTable 
VALUES
(1, 'abc'), (2, 'ABC');

-- Looking only for the second record
-- and this will return ONLY second one
-- as database is case sensitive (collation
-- for the columns are derived from top level)
SELECT * FROM dbo.TestTable WHERE Name = 'ABC';

Assume that same logic has to be implemented with another code using a temporary table, as a part of newly created database.

USE TestDatabase100;
GO

-- create a temporary table
CREATE TABLE #TestTable
(
 Id int PRIMARY KEY
 , Name varchar(100)
);
GO

-- Load data from original table
INSERT INTO #TestTable 
SELECT Id, Name FROM TestTable;

-- Check for result
SELECT * FROM #TestTable WHERE Name = 'ABC';

This will be the result of this.

It returns both records instead of last records. The reason for this behavior is, tempdb collation. Since temporary table has been created inside the tempdb, collation of the tempdb has been used instead of collation of user-defined database. This is why we need to consider the collation of tempdb.

Below code shows how to check the collation of tempdb and how to create the temporary table properly.

-- Checking collation of tempdb
SELECT name, collation_name
FROM sys.databases
WHERE name = 'tempdb'
GO

-- create the temporary table properly
CREATE TABLE #TestTable
(
 Id int PRIMARY KEY
 , Name varchar(100) COLLATE SQL_Latin1_General_CP1_CS_AS
);
GO

How to decide the number of data files required for tempdb?

If your tempdb is used heavily and experiences some slow performance with it, adding multiple data files might help to improve the overall performance of tempdb. Having multiple data files addresses issues related IO restrictions and avoids latch contention during Page Free Space (PFS) scans when temporary objects are created and dropped. But how do we decide the number of files should be added to the tempdb?

There is no specific formula to calculate it but a recommendation. Too much of files are not recommended at all as it might degrade performance rather improving performance. General rule is;

1 file per core or 0.25 file per core if as number of core increases. 

Optimal configuration can only be identified by doing some tests during peak hours but better to follow below guidelines for configuring your tempdb;

If the system has <= 8 cores, then number of data files = number of cores

If the system has > 8 cores, then number of data files = 8.

Here is a good explanation on latch contention: http://logicalread.solarwinds.com/sql-server-tempdb-latch-contention-w01/#.VWNYRE-qqko

Fact Table: Clustered index on date key is enough or partitioning should be done with it?

Fact table in a data warehouse (or a large table in an OLTP database) always holds millions of records and we keep on adding records largely. Generally, it is discouraged to add indexes and constraints to Fact Tables but with modern way of implementing data warehouse / BI solutions, if the table is open for business users for analysis, having indexes on Fact Tables would be beneficial. However, whether it is a traditional implementation or modern implementation, it is still recommended to have a clustered index on most frequent key used for analysis and loading which is Date Key.

While discussing Design Considerations of Fact Tables, a question came up: Clustered index on date key is enough or should we partition the table based on date key too? Do we get additional performance benefits by partitioning on the same?

In terms of performance, if the table is not significantly large, I doubt that clustered index on date key and partitioning on the same will give extra performance benefits. But if it is a very large table, performance of data retrieval will be surely improved. Does this mean that Partitioning is not required for Fact Tables?

Let's analyze with a slightly large table. Below code shows a structure of InternetSales table that contains 7 millions records. It has no indexes, not even a clustered index.

-- Table structure
CREATE TABLE dbo.InternetSales(
 OrderDateKey int NOT NULL,
 ProductKey int NOT NULL,
 DueDateKey int NOT NULL,
 ShipDateKey int NOT NULL,
 CustomerKey int NOT NULL,
 PromotionKey int NOT NULL,
 CurrencyKey int NOT NULL,
 SalesTerritoryKey int NOT NULL,
 SalesOrderNumber nvarchar(20) NOT NULL,
 SalesOrderLineNumber tinyint NOT NULL,
 RevisionNumber tinyint NOT NULL,
 OrderQuantity smallint NOT NULL,
 UnitPrice money NOT NULL,
 ExtendedAmount money NOT NULL,
 UnitPriceDiscountPct float NOT NULL,
 DiscountAmount float NOT NULL,
 ProductStandardCost money NOT NULL,
 TotalProductCost money NOT NULL,
 SalesAmount money NOT NULL,
 TaxAmt money NOT NULL,
 Freight money NOT NULL,
 CarrierTrackingNumber nvarchar(25) NULL,
 CustomerPONumber nvarchar(25) NULL,
 OrderDate datetime NULL,
 DueDate datetime NULL,
 ShipDate datetime NULL
) ON PRIMARY;
GO

If business user runs a query like below against the table, you can witness the number of pages (IO) have to be read for satisfying the query.

SET STATISTICS IO ON
GO

SELECT 
 DATENAME(m,[OrderDate])
 , p.EnglishProductName Product
  , SUM(SalesAmount) AS TotalSales
  , SUM(TaxAmt) AS TotalTaxAmount
FROM dbo.InternetSales f
 INNER JOIN dbo.DimProduct p
  ON f.ProductKey = p.ProductKey
 INNER JOIN dbo.DimDate d
  ON f.OrderDateKey = d.DateKey
WHERE d.CalendarYear = 2013 
 AND d.EnglishDayNameOfWeek = 'Monday'
GROUP BY DATENAME(m,[OrderDate])
 , p.EnglishProductName;

If we add a clustered index on Date Key and then test the same;

CREATE CLUSTERED INDEX ix_InternetSales
ON InternetSales (OrderDateKey);
GO

SELECT 
 DATENAME(m,[OrderDate])
 , p.EnglishProductName Product
  , SUM(SalesAmount) AS TotalSales
  , SUM(TaxAmt) AS TotalTaxAmount
FROM dbo.InternetSales f
 INNER JOIN dbo.DimProduct p
  ON f.ProductKey = p.ProductKey
 INNER JOIN dbo.DimDate d
  ON f.OrderDateKey = d.DateKey
WHERE d.CalendarYear = 2013 
 AND d.EnglishDayNameOfWeek = 'Monday'
GROUP BY DATENAME(m,[OrderDate])
 , p.EnglishProductName;

We will see a significant difference between previous number of pages read and number of pages read now.

Now the question is, partitioning this table will give more benefits or not. Let's partition and see.

-- Create a partition function
CREATE PARTITION FUNCTION pf_InternetSalesDate (int)
AS RANGE RIGHT
FOR VALUES ('20100101', '20110101', '20120101', '20130101', '20140101')
GO

-- Create the scheme
CREATE PARTITION SCHEME ps_InternetSalesDate
AS PARTITION pf_InternetSalesDate
TO (HistoryFileGroup, HistoryFileGroup, HistoryFileGroup, HistoryFileGroup, HistoryFileGroup, CurrentFileGroup)
GO

-- Dropping clustered index for partitioning
DROP INDEX ix_InternetSales ON InternetSales;
GO

-- Recreating with Partitioning scheme
CREATE CLUSTERED INDEX ix_InternetSales
ON InternetSales (OrderDateKey)
ON ps_InternetSalesDate (OrderDateKey);
go

-- And executing the same Business user's query
SELECT 
 DATENAME(m,[OrderDate])
 , p.EnglishProductName Product
  , SUM(SalesAmount) AS TotalSales
  , SUM(TaxAmt) AS TotalTaxAmount
FROM dbo.InternetSales f
 INNER JOIN dbo.DimProduct p
  ON f.ProductKey = p.ProductKey
 INNER JOIN dbo.DimDate d
  ON f.OrderDateKey = d.DateKey
WHERE d.CalendarYear = 2013 
 AND d.EnglishDayNameOfWeek = 'Monday'
GROUP BY DATENAME(m,[OrderDate])
 , p.EnglishProductName;

Here is the result of it;

This result does not show much improvement when compare with previous result, hence one can come to a conclusion saying that partitioning is not required when a clustered index is created on date key. But even with this table, we see a slight improvement, means that a large table will definitely benefits with partitioning.

Okay, now what about this table? should we keep partitioning or not? I prefer to have both implementations because Partitioning gives few more benefits in addition to the performance benefit it offers. It offers More Granular Manageability, letting us perform some operation such as indexing, compression and backing up at partition level instead of on the whole table. It improves Data Loading since loading can be managed at partition levels. Considering all of these, should not we create the clustered index on date key as well as partition the table using date key?

Analysis Services, Integration Services and Reporting Services menu items in the Connect menu are disabled, what is the reason?

If you need to connect with Business Intelligence services via Management Studio, you use Connect menu in the Object Explorer. Generally this is what you see with it;

However, what if you see them disabled and cannot use them for connecting with services like SSAS, SSIS and SSRS? There can be few reasons but obvious reason would be Not Installing  required components with SQL Server installation. How do we know whether we have installed them or not?

For that, you need to read the Configuration.ini file in the log folder. Navigate through C:\Program Files\Microsoft SQL Server\120 (your instance version)\Setup Bootstrap\Log\ folder and open the log folder related to the installation. You should see Configutation.ini file in the folder.

If you open the file and look for FEATURES, you should see all installed client components. If you do not see AS, RS, and IS, it means you have not installed them during client components installation. 

If you need them, installation has to be run again for client components and make sure you select Management Tools - Basic and Management Tools - Complete.

SQL Server Brain Basher of the Week #012 - Partitions

While I was discussing Partitioning with one of my discussions in this week, one enthusiast asked whether how many partitions can be created per table. Before answering, I asked the same from the audience; and thought to make it as SQL Server Brain Basher of the Week.

What is the maximum number of partitions can be added to a SQL Server table?

There are two answers for this because it is based on the installation; whether it is 32-bit or 64-bit. You can create 15,000 partitions if it is 64-bit version of SQL Server. If it is 32-bit version of SQL Server, only 1,000 partitions can be created.

Note that Partitioning is available only with Enterprise Edition and Developer Edition.

For more info related to Maximum Capacity Specification, refer: https://technet.microsoft.com/en-us/library/ms143432(v=sql.120).aspx.

What is a Junk Dimension?

Dimension represents entities that will be used for analyzing facts in the data warehouse. There are different type of dimensions like Regular, Slowly Changing, Self-Referencing and Junk. Many know about all of these except Junk.

Junk dimension is a dimension that combines multiple dimensions that have attributes with low cardinality and only few discrete values. This makes the process clean and reduce the complexity of the dimensional model.

Here is an example. Assume that three dimensions have been identified during requirement gathering process and they hold small number of values;

Rather than maintaining three tables for these three dimension, one dimension table can be introduced combining all values;

It represents all possible combinations of three dimensions and can be used with Fact tables without any issue. This dimension is called as Junk Dimension.

What are the reasons for introducing a surrogate key to a dimension table?

When designing a dimension table such as Product and Employee in a data warehouse, a surrogate key is introduced for implementing an unique column for identifying each row uniquely in the entity. Even though we usually see this with almost data warehouses, it is not a must to implement a surrogate key. If the key of the source table related to the entity can be used as the key column of the dimension table, there is no necessity to introduce another column. How do we decide? What are the factors for deciding whether we need a new surrogate key for the dimension table or we can use the source key as the key column in the dimension table?

Here are some factors that can be used for deciding whether you need a new surrogate key (or artificial key) for your dimension table;

1. Key of the source is a wide string such as CUS01X5679. It is not recommended to use a string data type as the dimension key unless it is fixed and small.
2. Key of the source is a GUID. This is a 16-bytes value and it is too lengthy for the dimension key. In addition to that, if GUIDs in the source are not sequentially generated, it is not good for the key at all.
3. Dimension table has to be loaded from multiple data sources. If table has to be populated from multiple sources, there is a chance to get same key from two differences sources for same record. This make impossible to make the source key as the dimension key because two records cannot be added with the same key.
4. Dimension table is a SCD type 2 dimension. If the dimension table is a Slowly Changing Type 2 dimension, it needs to maintain same record with multiple versions addressing changes and maintaining the history. If the sources key is used as the dimension key, it is not possible to make another version for the same record with the same key.
5.  Source reuses the key for new items. Some systems allow to delete records from entities and insert new records with the same keys that were used with the deleted records. Since the old record is already taken to dimension table, it is not possible to insert the new record with the same key if source key is the dimension key.

If you do not see any of reasons mentioned above, no harm, you can use the source key as the dimension key.

How to take a copy of database files while they are being used by SQL Server

I am sure that you have tried this at least once and have face the below issue.

Generally, we cannot copy database files or log files when they re being used by SQL Server, simply when the SQL Server service is running. If we need to get a copy of MDFs and LDFs, what we normally do is, stopping the service and copying files. However this is not possible with production databases, specifically when databases are used by 24x7. What is the solution for this?

There are many third party applications and tools available for getting this done. Some are paid tools and some are free tools. I have used one with SQL Server 2005 and 2008, unfortunately I cannot remember it now. While I am reading blogs, I came across this post: Backup/Copy Files that are "In Use" or "Locked" in Windows (Command Line) which is written on Copying Files. It explains how to use a tool called HoboCopy which is specifically for backing up / copying files but it can be used for copying our SQL Server files while they are being used.

In order to use HoboCopy, download the relevant file for you from this: https://github.com/candera/hobocopy/downloads,

Once downloaded, open Command Prompt as an Administrator and type the command as follows;

HoboCopy "FileFolderPath" "DestinationFolderPath" FileName.

For example, if you want to get the AdventureWorks2014 database file copied, you will be typing as;

HoboCopy "C:\Program Files\Microsoft SQL Server\MSSQL12,SQL2014_86\MSSQL\DATA" "C;\Test" AdventureWorks2014_Data.mdf

Note that, in order to run this command, you need to make sure Volume Shadow Copy and Microsoft Software Shadow Copy Provider services are running.

I want an execution plan optimized for my value, not for the value passed for parameters with first execution.

Since I got few questions on the posts written on stored procedure in last two weeks, thought to make few more posts on the same based on questions asked. One question asked was Can we make sure that the stored procedure is optimized for one range of values, not for exceptions. If we need to get the stored procedure optimized for one set of values, then instruction has to be passed during the creation. We should only do this if we know that there are different best plans for different values.

I have written a post called How to determine whether RECOMPILE is required? If so, at the creation or execution? and it shows the way of seeing whether different best plans are exist for different values. Have a look on it if you want to know the way.

Let's see how we can optimize a procedure for our values, not for values passed with first execution. Below code creates a stored procedure, an index on OrderDate. 

-- Clearing the cache  
DBCC FREEPROCCACHE  
GO  
  
USE AdventureWorks2014;  
GO  
  
-- Drop if it is exist  
IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL  
 DROP PROC dbo.GetOrderDetails;  
GO  
  
-- Create the procedure  
CREATE PROC dbo.GetOrderDetails @FromDate datetime, @ToDate datetime  
AS  
    SELECT h.* 
 FROM Sales.SalesOrderHeader h
 WHERE h.OrderDate BETWEEN @FromDate AND @ToDate
 ORDER BY h.OrderDate
GO  

-- Create an index on OrderDate
CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader (OrderDate DESC)
GO

Nest code executes the stored procedure with three different values. As you see, WITH RECOMPILE has been added to see whether same plan or different plans are used for the execution.

EXEC dbo.GetOrderDetails '2014-06-29T00:00:00', '2014-06-30T00:00:00' WITH RECOMPILE;  
EXEC dbo.GetOrderDetails '2014-06-26T00:00:00', '2014-06-30T00:00:00' WITH RECOMPILE;  
EXEC dbo.GetOrderDetails '2014-06-01T00:00:00', '2014-06-30T00:00:00' WITH RECOMPILE;  
GO

Here are the plans generated.

As you see, plan is same for first two executions, and a different plan is for third execution. The reason for this is, first and second one try to get orders within few number of days and third one is for many days, hence newly added index is used for first and second one with seek operation and clustered index is used for third one with scanning. 

Assume that most requests made on this procedure are based on small number of days and we need to optimize it for small number of days. If so, we need to alter the procedure instructing that create a plan optimized for a sample range that contains small number of days. If we do not do it and the first execution is based on large number of days, plan will be created for it and the consecutive executions will use the plan created even for small number of days which is not the optimized plan for them.

Okay, how can we instruct then? Here is the way.

-- Alter the procedure  
ALTER PROC dbo.GetOrderDetails @FromDate datetime, @ToDate datetime  
AS  
    SELECT h.* 
 FROM Sales.SalesOrderHeader h
 WHERE h.OrderDate BETWEEN @FromDate AND @ToDate
 ORDER BY h.OrderDate
 OPTION (OPTIMIZE FOR (@FromDate = '2014-06-29T00:00:00', @ToDate = '2014-06-30T00:00:00'))
GO 

-- Execute with a large range and see
EXEC dbo.GetOrderDetails '2014-01-01T00:00:00', '2014-06-30T00:00:00';  
GO

Here is the plan;

I have used OPTIMIZE FOR hint by passing two values for parameters. Note that values are set for a small range and this instructs engine to create the plan based on these values without considering the values submitted with executions. As you see, when the procedure is executed even with a wide range, still engine uses the plan optimized for small range.

Read more on this hint at: https://msdn.microsoft.com/en-us/library/ms181714.aspx.

SQL Server Brain Basher of the Week #011 - DBCC

An interview question.

What does DBCC stand for?

When classes related to Administration are being conducted, I always ask this question and as expected, I always get the same answer which is wrong. Most think that this is as Database Consistency Checker but that is the oldest way of describing this.

DBCC Command family was originally supporting Consistency related commands. Version upgrades added more and more commands to this family and they were not only for checking the consistency of our databases. Therefore, since SQL Server 2000, this was started referring as Database Console Commands. 

Read more on DBCC at: https://msdn.microsoft.com/en-us/library/ms188796.aspx.