:::: MENU ::::


  • Aug 21 / 2017
  • 0
Columnstore Indexes, dbDigger

Introduction to SQL Server Columnstore Indexes

Columnstore indexes are introduced in SQL Server 2012. These are used for large tables where primarily READ operations are performed. Suitable environment is DWH fact tables. Storing data in this structure may help to get 10 X read performance and 10 X compression as compared to traditional row structured storage of data.

In this technology data is physically stored, retrieved and managed in a columnar data format. There are two types of columnstore indexes

  • Clustered columnstore indexes (CCI)
  • Non clustered columnstore indexes

Clustered columnstore indexes (CCI)

are used for large tables in DWH where considerable size difference may be acheived by compression. And READ operations are intensive.

Non clustered columnstore indexes

are used for environment where OLTP load is also involved with analysis queries.

Creating columnstore index on existing table

While creating a clustered columnstore index (CCI) on a table there may be some issue due to data type or column type. For example we may not create CCI on a table with data type nvarchar(max) in it. Or with a computed column (persisted or non persisted) in that table.

Combining columnstore and rowstore indexes

Starting from SQL Server 2016 we can have non clustered columnstore indexes on a table with rowstore table. And we can also have non clustered rowstore indexes on a columnstore table.

Get columnstore indexes in a DB

We can get general information about columnstore indexes in a DB with following script

Columnstore indexes on partitioned tables

Columnstore indexes can be implemented with partitioned tables by keeping these partition aligned.

  • Aug 17 / 2017
  • 0
SQL Azure, SQL Server tools, SQL Server Utilities, SSMS tips and tricks

Authentication methods in SSMS 17.2

In previous post about 17.2 we briefly mentioned about its download flavors. There is much more to explore in it. Login box of new SSMS shows additional options along with conventional Windows Authentication and SQL Server Authentication.

Authentication types in SSMS 17.2

These additional options are

  • Active Directory – Universal with MFA support (Introduced in SSMS 17.2)
  • Active Directory – Password (Introduced in SSMS 17.0)
  • Active Directory – Integrated (Introduced in SSMS 17.0)

All three options have one thing common that they relate to Azure active directory. Azure SQL Database and Azure SQL Data Warehouse support connections from SSMS using Active Directory Universal Authentication.

Active Directory РUniversal with MFA support is multi factor authentication and involves two step verification by adding call, SMS, bio metric device, smart cards with pin, or mobile app notification and can result in pop up box for validation.

Remaining two options Password and integrated are not interactive and do not generate any dialogue box. Can be used in different applications like ADO.NET, JDBC, ODBC etc.

  • Aug 11 / 2017
  • 0
SQL Server tools, SQL Server Utilities, SSMS tips and tricks

SSMS 17.2 is available now

SQL Server management studio (SSMS) 17.x is the latest generation of SQL Server Management Studio and provides support for SQL Server 2017. Microsoft has released SSMS 17.2 with number of improvements and additional features. You can download the SSMS 17.2 from here and also have a look at “New in this Release”.

Download is provided with two options.

  1. Download whole setup for SSMS 17.2
  2. Download updates if you have already installed 17.0 or 17.1

In both cases you would have SSMS 17.2 available.

  • Aug 09 / 2017
  • 0
dbDigger, Monitoring and Analysis, System Administration, Windows Server

Get disk report by using PsInfo utility

In a previous post we have discussed PsInfo utility is part of PsTools set.Today we will utilize Psinfo to get disk information on any machine. Any of PsTools utility can be used by simply unzipping on disk. I have unzipped all PsTools files in D drive and PsInfo is also among those.

Get disk report by using PsInfo utilityNo we can utilize the PsInfo to get disk information for local or any other connected machine.

  • Open cmd and go to unzipped PsTools folder
  • Execute psinfo \\MachineNameHere -D disk

It would generate general information about drives like label, type, format, Size and free size.

Run PsInfo in cmd


  • Aug 07 / 2017
  • 0
Monitoring and Analysis, System Administration, Windows Server

PsTools suite for Windows administration

As a DBA we are often required to perform tasks that fall to sys admin category. It may be related to server processes, configuration, disk information etc. Recently i have used a free tool pack PsTools that provides facility to perform many such admin tasks on local or remote machine. PsTools suite includes several individual applications as mentioned in below table.

PsTools suite for Windows admin tasks

You can download the tools pack from here. Just unzip it on your disk and call any of these utilities through cmd. No installation package is required.

  • Jul 19 / 2017
  • 0
Power BI for beginners
Business Intelligence, Cloud Platform, Power BI

Introduction to Power BI

Power BI was Launched in 2014. It is a very power full tool that allows ETL, modeling, analysis, presentation and sharing. Using Powe BI does not require to learn a new language or scripting skills.

Everything you do in Power BI can be broken down into a few basic building blocks.

  • Visualizations (Graphs, charts, maps etc)
  • Data sets (collection/combination of data used to create visualizations)
  • Reports (collection of visualizations that appear on one or more pages and then arranges)
  • Dashboards (contains pages or set of visualizations on single page called canvas and can be shared)
  • Tiles (single visualization found in a report or dashboard)

Underlying Technologies of Power BI

Following technologies are used underlying in Power BI for different operations

  • Query/Table –> Power Query
  • Model –> Power Pivot
  • Presentation –> Power Map
  • Sharing –> Dashboards
  • Analysis –> Charts/Machine learning

Architecture of Power BI

Power BI has two architectural components

  • Power BI desktop (Desktop application for creating reports and visuals)
  • Power BI service (SaaS for sharing and collaborating the reports and dashboards)

Starting with Power BI

To start with power BI you would require office 360 account that can be created with your organization email. Then login to Power BI website to configure the service and download the desktop application as well. There is lot of work being done by Microsoft and community to add more and more features, visualizations and options in the tool. These additions may be included by regular updates provided.

Consult us to explore the Databases. Contact us