What is non clustered column store index in SQL Server?

Column store index is a new feature introduced in SQL Sever 2012.In Non clustered column store index data is stored as column.Column order is not important in column store index.In this non clustered column store index we don’t have traditional row execution mode instead of that,we have batch execution mode.This improves query execution.Batch execution mode  executes several rows at a time as a batch.This reduces CPU consumption.

In column store execution, only columns that query needs will be read.This reduces I/O and memory utilization.

We cant have Unique,Primary or Foreign key constraints in column store index and also it does not have feature like INCLUDE.Once we have created the Non clustered column store index for a table,we can’t update the table with new values because the table is READ-ONLY.We could have only one non clustered column store index.Column store index could have only 1024 columns.And we don’t have clustered column store index only Non clustered is available.

This column store index uses the benefits of segment elimination based on some conditions.It gives faster performance for common data warehousing queries.

Create a Column Store Index in SQL Server by using below steps.

Using GUI:

1.In Object Explorer, expand Tables, right click -> Indexes and click New Index. Select non clustered column store index .



2.Add the column store columns.


  •   select the table column to be added to the index.



  • Selected Column store column will be added.



3.Set the options by selecting options from select page.In that give the value for Max degree of parallelism as greater than or equal to (2) to have batch execution.

Set other extended properties you need by clicking extended properties node.



4.Non clustered column store index is successfully created.



NONCLUSTERED COLUMNSTORE index without options:

Create database product;

Use product;


(ID [int] NOT NULL,

RecDate [int] NOT NULL,

DelDate [int] NOT NULL,

ExtDate[int] NOT NULL);




CREATE NONCLUSTERED COLUMNSTORE INDEX csindex_test ON Test (RecDate, DelDate, ExtDate);





(RecDate, DelDate, ExtDate)



ON “default”



How to create clustered and non-clustered index

Index is a database object, which can be created on one or more columns(max 16 columns).The index will improve the performance of data retrieval and adding.indexes are created in an existing table to locate rows quickly and efficiently.

What is clustered index?

Table can have only one clustered index.In clustered index data’s in table is sorted in particular order based on index keys(either AESC/DESC).In clustered index page chain that holds data pages is also sorted in same order as index keys.So,SQL server follows the page chain in order to retrieve the data rows.By this new rows could be added just by adjusting the links in the page chain without moving entire pages.The leaf nodes of a clustered index contains the data pages with index keys.
What is Non clustered index?
For a table we could have more than one non clustered index because it doesn’t affect data pages organization.The leaf node of a non clustered index does not consist of data pages. Instead, the leaf nodes contain index rows.If we have both the clustered index and non clustered index for a table then index row will have Clustered index key columns that point to the data row. If there is no clustered index then the index row contains Non-Clustered index key columns which is stored along with row locator (or)  row identifier.The pointer from an index row to a data row is called a row locator.
Both clustered and non clustered indexes can be unique. That is no two rows can have the same value for the index key. Also we have one special type of index called Non clustered column store index.
Creating a clustered index  using Object Explorer:
1.In Object Explorer, expand the table for which you want to create a clustered index.


2.Following window will allow as to select the key columns for index.


3.We could add other options by selecting options node from select page.



4.Click ok.Clustered index will be created.

Create Non-Clustered index using object explorer:

1.Similarly Select Non clustered index from New index.Select the Key columns for an index.


2.In non clustered index we could add included columns for your index.In order to overcome existing index limits.After including needed included columns ,Click ok.


3.Similarly other options could be added by selecting different nodes from select page.

4.Click ok .Index will be created.

How to set Max degree of parallelism (MAXDOP)

Max degrees of parallelism (a.k.a MAXDOP) in SQL Server is used to control the maximum number of threads per execution plan operators work.MAXDOP does not restrict the number of CPU’s/Threads used by single batch or Query.

Ideally MAXDOP should be equal to number of online schedulers per node. You can use the below query to get the number of  online schedulers per node. All the parallel threads for the tasks of the query will be assigned from schedulers of same node so having MAXDOP beyond the number of online schedulers per node may not really improve the performance (With some exceptions).  

select count(*) as Maxdopcount, parent_node_id from sys.dm_os_schedulers
 where status='VISIBLE ONLINE' group by parent_node_id

Depending upon the workload in your environment you may increase or decrease the value.

Note: Always ensure you have same number of online schedulers in each node else you may face uneven workload and memory distribution among the SQL Server schedulers more details in SQL Server NUMA load distribution.

To configure Max degree of parallelism follow the below steps.

1.Connect to the Database Engine.

2.From the Standard bar, click New Query.

3.Then execute the following query.


sp_configure 'show advanced options', 1;




sp_configure 'max degree of parallelism', 1; /*Replace 1 with your preferred MAXDOP value */




<span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">




1.In Object Explorer, right-click a server and select Properties.

2.Click  Advanced  from select a page.

3.In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.


SQL Server Database tuning advisor

Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, statistics and partitions for the tables to improve the Query performance .

Database Engine Tuning Advisor uses trace files, trace tables, or plan cache as workload input when tuning databases or else it could use the query which you select for workload analysis.

The Database Engine Tuning Advisor (DTA) helps you to tune databases to improve query processing.

To start a Database Engine Tuning Advisor tuning session, follow these steps:

Before going into this session let us see how indexes (or)stats affects the performance of the query

1.I have created a new database(E.g..DTA).In the new database I have created a table named Test with 100000 rows in it. Let us see how to create necessary indexes and stats on tables to improve query performance based on the queries fired this table .

First let us enable statistics time on and statistics IO on to view the compile time,CPU time ,elapsed time and I/O’s performed by the query

set statistics time on
set statistics IO on


Example:Execute the query select * from Test where a=10,


Before creating index,it takes 1158 logical reads, cpu time=16ms and elapsed time=28ms.The performance is very low.In order to increase the performance we go for indexes.




After creating index, logical reads have become 329,cpu time=15ms and elapsed time= 11ms.



2. Let us see how to create this indexes and statistics using database tuning advisor. Copy the query which has to be tuned in SSMS new query windows.




3. select your query->right click->select Analyze query in database tuning advisor.


4.Then this window will appear .In that select the database  to do workload analysis.


5.Then click start analysis in tool bar.Tuning process will be started.


6.After tuning process completes, recommendations will be provided for your table to improve the query performance.


7.Either you could save the recommendation to apply later or you could apply it immediately .



8.We can also use  trace files as workload to tune the database. Let us create a trace file by using SQL Server Profiler  and see how that can be used to tune the database.

Open SQL Server Profiler


9.SQL Server Profiler window appears.In that give trace name.Then Run the profiler.



10. Now execute the queries which you would like to tune Trace file will capture the events.

For example: Let us execute the same select query which we used in the beginning.




11.  Save the trace file. 


12. Open the Database tuning advisor. In the Workload area, select the file that you created by using SQL Server Profiler.Select the database for workload analysis and to tune.Then click start analysis.





13.Index recommendations will be provided.By applying this recommendation the estimated improvement will be 56%.


14.Then apply the recommendation.Actions->Apply recommendation.You could apply  the recommendations immediately or you could schedule it for later.


15.Here I choose to apply the recommendations immediately.




16. Once the recommendations are applied performance of the query will improve.


When you run Profiler trace from client systems or on server with large number of events you will see below wait types.


There is no way to completely avoid this wait type without stopping all the traces. We can reduce this waitypes by configuring server side trace instead of client side trace.

select * from sys.traces will give you information about all the traces. ( Status column 0 stopped and 1 active)

For the traces collected using profiler you will find a NULL Path. Profiler traces can cause large number of above waittypes.

Thread which raises the trace event is responsible to Get buffers to write event and write event. So collecting the trace on network share or on slow disk or using profiler can slow down the trace write and make the threads wait, sometimes we may also end with dead locked schedulers.

So ideally you have to avoid running profiler when you see below waits and use sp_trace_create if you like to capture the trace and pass the local path for tracefile parameter.

Query to find SQL Server CPU utilization 2012.

Query to find SQL Server CPU utilization 2012. This query will help you to find the CPU utilization of server and SQL. If like to know the steps to bring down the SQL Server CPU utilization follow

	declare @ms_now bigint
	select @ms_now = ms_ticks from sys.dm_os_sys_info;
select top 15 record_id,
		dateadd(ms, -1 * (@ms_now - [timestamp]), GetDate()) as EventTime, 
		100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
	from (
			record.value('(./Record/@id)[1]', 'int') as record_id,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
		from (
			select timestamp, convert(xml, record) as record 
			from sys.dm_os_ring_buffers 
			where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			and record like '%<SystemHealth>%') as x
		) as y 
	order by record_id desc

Linked server from SQLServer2012 to SQLServer2000.

How to create Linked server from SQLServer2012 to SQLServer2000.
When you create linked server from SQLServer2012 to SQLServer2000 you cannot use SQL Native client 11 because it cannot connect with  SQL Server 2000
Each version of SQL native client will support the version of SQL Server that it ships with, two earlier versions and two later versions
When you use SQLOLED it will switch to latest SQLNCLI.
The Microsoft SQL Server Native Client OLE DB provider is the provider that is used with SQL Server if no provider name is specified or if SQL Server is specified as the product name. Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.
You create a data source and use MSDASQL (Microsoft OLE DB Provider for ODBC) as long as OS supports it.
sp_addlinkedserver ‘Server\Shiloh’, @provider = N’MSDASQL’, @datasrc=N’SQL2000datasource’, @location=N’System’,@srvproduct=N’MSDASQL’;
Alternatively you can use SQL Server native client 10.0 but it expects “begin distributed transaction” for all the queries.