Database Snapshot Part III

Database Snapshot Part III

How to create database snapshot

Create database snapshot_name on (Name=logicalfilename, filename=’path of snapshot file’) as snapshot of databasename

Create Database KalyanDB_Snap ON

 (NAME = 'KALYANDB', FILENAME ='C:\TEMP\KALYANDB_SNAP.SP') AS SNAPSHOT OF KALYANDB

If you observe the file which is created on c:\temp folder the size of file shows similar to size of database, but if you observer size on disk, it shows the exact value

To view the snapshots

SSMS – Databases – Database Snapshots

 

To retrieve information using snapshot

use KalyanDB_Snap

select * from dbo.OrderDetails

To drop snapshot

DROP database KALYANDB_SNAP

If database has multiple ndfs then we need to create snapshot files for each data file otherwise it will throw the below error

Msg 5127, Level 16, State 1, Line 2
All files must be specified for database snapshot creation. Missing the file

Syntax to create database snapshot for database which has multiple ndf files

create database salesdb_snap1 on

(name = 'primary_data', filename='c:\temp\sales_db_snap.sp'),

 (name = 'Sales2010', filename = 'c:\temp\sales2010.sp'),

 (name = 'Sales2011', filename = 'c:\temp\sales2011.sp'),

 --(name = 'Sales2012', filename = 'c:\temp\sales2012.sp'),

 (name = 'Sales2013', filename = 'c:\temp\sales2013.sp') as snapshot of salesdb

For which databases snapshot are created

select name, database_id, source_database_id from sys.databases where source_database_id is not null

To restore database from database snapshot

restore database kalyandb from database_snapshot='KALYANDB_SNAP'

 

SQL Server Database Snapshots Part II

How Database Snapshot works?

Initially while creating a database snapshot if source database has 100 pages then snapshot file (Sparse File NTFS sparse file gets created) created same as source database 100 pages, but the snapshot file size is very small even though the file size shows same size as database file.

If any update / insert happen on source database after creation of database snapshot before writing those pages to disk a pre change copy of those page is pushed into database snapshots which gives point in time view of the source database.  As discussed If we update data in database (for ex: 10 pages), the copy of those 10 pages were moved into database snapshot before updating in the source database that means we have two copies of those pages 1) before update pages in database snapshot sparse file and 2) after updating pages in original database, due to this size of sparse file increased and it may affect the performance of source database and IO overhead because it needs to copied pages if there are huge transactions on the database.

If we do a select on snapshot the unchanged pages will come from source database and changed paged will read from sparse file (already we have unchanged pages in sparse) this is how it gives a static view of point in time.

Note – The database snapshot is transactionally consistent, means while creating database snapshot if there are any active transactions in source database that will roll back on the database snapshot.

Advantages

We can generate snapshot while doing any production database upgrade in downtime, because if something goes wrong we can always restore database from Database Snapshot till point in time when it was created. Restoring database from database backup will take time depends on size of the database, whereas restoring database from database snapshot can be performed quickly

We can’t query the mirrored database but we can create database snapshots on mirror server and can be used for reporting purpose.
 

SQL Server Database Snapshots Part I

Database Snapshot

Database snapshot is a read only copy of database at a given point in time. Snapshot resides on the same server where source database exists and multiple snapshots can be created for one source database.

When Snapshots are helpful?

Snapshots help to maintain historical data for report generation.
Protect system from user or administrative errors
Database can be reverted back to the state when the snapshot was created. Reverting database from database snapshot is much faster than the backup file.

Note: Snapshots are not a HA solution, and we cannot assume snapshots as a database backups.

Limitations
If source database is unavailable then all of its snapshots become unavailable.
Snapshots are read only and static view of database in a given point of time.
Available from SQL 2005 EE and later
Not able to create snapshot using SSMS, Snapshots can be created only by using T-SQL.
We cannot attach or detach snapshots.
Backup cannot be done at database snapshot.
Snapshot must reside on the same instance where the source database is exists because they share pages.
We can create snapshots very quickly.
Full text indexes are not available in database snapshots.
You cannot grant a new user to access the database snapshot because permissions are inherited from the source database at the time of snapshot creations, later we can’t add users to database snapshot.
Initially snapshot files are very small and they grow large if database has frequent modifications.
Snapshots cannot be created for system databases master, model, msdb and tempdb.
Database snapshot become suspect if something goes wrong with source database.
Restoring database from database snapshot will breaks the log backup chain.

SQL Sever Security Part II

Principal -- This principal objects requests authentication to SQL Server resources. There is various types of principals Windows level principals, SQL Server level principals, Database level principals.

Windows level principals are windows domain login and windows local login

SQL server level principals are server level login and server role

Database level principals are database user, database role and application role

When we install SQL Server “sa” login server level principal will created by default. 

As discussed in earlier post public role (database principal) is created in every database by default.

Sys and Information_schema – These two schemas are appeared in users catalog of every database, they are not principals and these are required by SQL Server. We cannot drop or modified these entities.

Securables – This securable objects requests authorization to SQL Server resources. For example a table is securable. These securables has scopes like server, database and schema.

The server scope securables are Endpoint, login, server role and database.

The database scope securables are User, database role, application role, Assembly Schema etc.,

The Schema scope securables are tables, view, types etc.,

SQL Server Security Part I

SQL Server has ways of accessing, we will understand one by one. Access to SQL Server can be provided using Authentication and Authorization.

Authentication is a process of logging into SQL Server using login credentials. 

Authorization is a process of determining resource access for the login.


SQL Server supports two authentication modes 1) Windows authentication mode and 2) mixed mode.

Windows Authentication is the default mode in SQL Server, whoever has access to windows access those directly can connect to SQL Server without specifying credentials again because SQL Security model is tightly integrated with Windows.

Windows account has different types, Local windows account, Domain Account, Domain Group.
Local windows account is defined account in the SQL Server machine.
Domain account is individual account defined at Active directory level
Domain group is defined at Active directory which contains multiple domain accounts.

SQL Server Authentication - The SQL Server account is account defined within the SQL Server, it is unknown to Active directory, and while creating account at SQL Server level we need to provide login name and password for that account. This is the difference between Windows and SQL account, windows account password will be maintained by Active directory and SQL account password will maintained by SQL Server.

SQL Server uses role-based security for assigning permissions to users. There are fixed server roles and fixed database roles, Server roles will be defined at server level and database roles will be defined at database level.

SYSADMIN server level role is the super user in SQL Server it has every right on SQL Server.

We need to understand about database roles and default user accounts in the database. In both user and system databases there will be public role by default, we cannot remove this role from the database, and permissions which are assigned to public role will get inherited to all other users in the database. 

The DBO user account

The dbo or database owner has all permissions on the database. Users of sysadmin are automatically mapped to dbo.

The Guest user account

 This is a default account gets created automatically when new database is created and it was in disable state, If you enable this account then whoever has access on the server can able to access the database, (of course they are not able to view any information but if guest is assigned with any database role like db_owner those permissions will automatically inherited to all other logins on the server). So we should not enable guest account.

To enable guest account

Grant connect to guest;

To disable guest account

Revoke connect from guest;
 

Fill Factor in SQL Server

Fill Factor

Fill factor is a value that determines the percentage of space on each leaf level page to be filled with data. In SQL Server data is stored in the pages the size of page is 8kb, depending on the row size the number of rows will be stored in the page. The default value for fill factor is 0 or 100, which means there will be no free space left in the page. If fill factor value set to 80% then while rebuilding index 20% SQL Server will leave 20% of free space in each page which provides expansion when new data is added to the underlying table.

If Fill Factor value is set to 100% or 0 then there is no space on the page for index expansion. In that case SQL Server will move 50% of data into new page and page split will happen and SQL Server need to read more number of pages to retrieve same number of records hence performance decreased due to more IO.

Fill factor can be set in two ways
1. At server level
2. At Index level

Create Index index_name on table_name with (fillfactor = 80)

Alter Index Index_name on table_name rebuild with (fillfactor = 80)
Check out the best practices of setting fill factor value from BOL or from expert’s blogs.

Differences between Rebuild and Re Organize Indexes

Differences between rebuild and re-org index

Syntax

Alter Index Index_Name on Table_Name REORGANIZE

Alter Index Index_Name on Table_Name REBUILD

Rebuild Index drops the existing index and re-creates the index from the scratch.

Rebuild index can be executed online or offline.

Rebuild Index uses more CPU and it locks the database resources.

In rebuild index we can specify index options like fill factor or index padding.

Rebuild locks the table for whole operation except Online = ON (Enterprise Edition)

Rebuild index automatically updates indexed column statistics.

By default Rebuild index is offline process, it will cause some blocking, we can use online=on to build index online and reduce blocking during the process.

Reorganize index physical organize the leaf nodes of the index.

Reorganize always executed online, (It wont block applications) In Reorg SQL Server read through all leaf level pages of an index and reorders them.

Reorg index uses less CPU and it doesn’t lock the database resources.

In reorg index we cannot specify and index options.

Reorg doesn’t lock the table.

Reorganize index doesn’t update statistics (After reorg we need to manually run update stats on the table).   

Recommendation – Rebuild index when fragmentation is greater than 30% and Reorganize index if fragmentation is between 10 to 30%. 

Query to find fragmentation percent

SELECT Object_name(object_id) as Tablename,s.name as Indexname
,index_type_desc
,avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') d
join sysindexes s on d.object_id = s.id
and d.index_id = s.indid
and s.name ='Index_Name'
 

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).

SQL Server Installation is failed with Wait on the Database Engine recovery handle failed error.

I have already posted workaround for that issue, please search about that post.

After fixing the SQL Server issue, I observed SQL Server Agent is not starting, To troubleshoot the issue.

1. Connect to SSMS
2. Right click on SQL Server Agent  -- Properties
3. In General section there is path for SQL Server Agent.out error log file.
4. Open the file in notepad and verify the error, I have the following error in my agent.out file

Unable to connect to server '(local)'; SQLServerAgent cannot start
SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]
Logon to server '(local)' failed (DisableAgentXPs)
SQLServerAgent terminated (normally)

5. To fix this I have added NT Authoriy\System to SQL Security,
(The cause of this error is SQL Installation is failed and it doesn't added any logins to SQL Server, so we did it manually).

Fragmentation Part II

How to detect fragmentation?

SQL Server 2005 introduces a new DMV to identify the fragmentation levels using sys.dm_db_index_physical_stats and we can use DBCC ShowContig (deprecated) command to view the fragmentation.

The DMV can display information in 3 different modes.

DETAILED -- It displays information by reading all data pages and index pages. We need to be careful while using this option because entire index will read into memory and result I/O issues.

SAMPLED -- Reads 1% of the pages if more than 10,000 pages

LIMITED -- Reads only parent level of b-tree and it doesn’t displays the page density.

Syntax

SELECT *  FROM sys.dm_db_index_physical_stats(DB_ID('KalyanDB'), NULL, NULL, NULL , NULL);

Parameters ( 1st- Database Id, 2nd –ObjectId, 3rd – IndexId, 4th – Partition Number, 5th – mode)

If you specify NULL it displays Limited mode information by default for the given database for all objects and indexes.

We need to pay attention on two columns to identify the fragmentation

Avg_fragmentation_in_percent -- The percentage of logical fragmentation (out of order pages in the index). A value between 5% - 30% indicates moderate fragmentation, while any value >30% indicates high fragmentation

Avg_page_space_used_in_percent -- Average number of pages in one fragment in an index.

If avg_fragmentation_in_percent value is between 5% and 30% then Action = Reorganize Index (SQL Statement – Alter Index Reorganize which replaces DBCC INDEXDEFRAG)

If the value is >30% then Action = Rebuild Index (SQL Statement – Alter Index Rebuild which replaces DBCC REINDEX)

REORGANIZE will always executed online while REBUILD index is executed offline by default and can be executed online by giving ONLINE in Alter Index statement. 

Below are few T-SQL statements from BOL which helps to identify the fragmentation percentage of each index and for Alter statements

Displays fragmentation percentage of indexes for the specified database,
SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'KalyanDB'), OBJECT_ID(N'Students'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

To Reorganize any Index 

ALTER INDEX IndexName ON Students REORGANIZE ;
GO
To Reorganize all Indexes

 ALTER INDEX ALL ON Students REORGANIZE ;
GO

To Rebuild any Indexes

ALTER INDEX IndexName ON Students REBUILD ;
GO

To Rebuild All Indexes

ALTER INDEX ALL ON Students REBUILD ;
GO

Fragmentation Part I

Fragmentation means storing data in a non-contiguous manner. Due to fragmentation SQL Server need to do extra IO to read the indexes which causes slower performance.

Causes of Fragmentation

Inserts and updates causing page splits – The most common cause of index fragmentation is because of “page splits”, a page split occurs when there is no room to accommodate newly inserting / updating records, As a result SQL Server need to move 50% of page data into newly allocated page which causes page splits

Delete operations – The DML operation delete causes fragmentation because random delete will leave pages in use but not completely full or rows will result internal fragmentation.

There are two types of fragmentations 1) Internal Fragmentation 2) External Fragmentation

Internal Fragmentation (Logical Fragmentation) – When data pages are partially used and if each page has empty space creates fragmentation, In other words records are not stored contiguously in the page leads to internal fragmentation hence SQL Server needs to scan more pages to fetch the same number of rows which causes poor performance.

External Fragmentation -- It occurs when pages and extents are not stored contiguously in the physical disk. When the extents or pages scattered across the disk hence switching of extents causes high disk rotations and this is called Extent Fragmentation.

High fragmentation causes poor performance, we can get optimized performance of select queries only if the data pages are stored contiguously in the database file.

To detect fragmentation we can use DBCC SHOWCONTIG (deprecated) and sys.dm_db_index_physical_stats DMF.

SQL Server Memory Management Part II

SQL Server 32 bit memory architecture

We already discussed that in Win32 bit architecture 4GB address space is equally divided into user mode space and kernel mode space and by adding /3GB switch user space can be increased by 3GB and kernel space will become 1 GB. In that case for 32bit SQL Server 2 (or) 3 GB is the maximum memory available.

SQL Server address space is divided into two different regions

1) Buffer Pool

2) MemToLeave (Memory to leave)

Buffer pool -- The buffer pool area supports all memory requests upto 8KB size, As SQL Server page is 8 KB that means all data and index page allocation requests are supported from buffer pool region.

MemToLeave -- All requests that are greater than 8 KB are supported from this region. This includes memory used for COM objects, CLR Code, Extended stored procedures etc.,

The size of MemToLeave and Buffer pool was decided by SQL Server, On SQL Server startup it looks at the physical memory RAM in the machine and decides how much of VAS (Virtual address space) is needs to reserve for its usage ie., for MemToLeave and then allocation Buffer Pool regions.

MemToLeave = (Stacksize * MaxWorkerThreads) + DefaultReservationSize

(256 * 512 KB) + 256 MB = 384 MB by default

(Stack Size = 512 Kb in 32 bit system, 256 are default MaxWorkerThreads)

After completion of assigning MemoryToLeave then it reserves memory to Buffer Pool area, this was decided by considering other factors like AWE is enabled or not, If AWE is not enabled then the size was reserved by remaining memory user address space (2GB) – MemToLeave (384 by default)

Buffer Pool = Minimum (Physical memory, UserAddressSpace(VAS) – MemToLeave) – MemToLeaveSize

Buffer Pool = Minimum(4 GB, 2GB) – 384 MB

Buffer Pool = (2GB – 384) = 1664 MB

In MemToLeave portion SQL Server Multi Page Allocation (MPA) will be done that means if the page size is greater than 8KB this will be processed in this area and Extended procedures, linked servers providers, COM objects and any other DLL’s that are loaded into this process space.

Note – There is NO MemToLeave for 64 bit version of SQL Server Engine.

AWE -- Address Windowing Extension

It helps SQL Server to make use of large amount of physical memory, In SQL Server 32 bit machine if the physical memory is 8 GB the SQL Server will make use of only 2GB user mode address space, if you enable AWE then SQL Server can make use of more than 2GB address space and this only applies to 32 bit systems.

NOTE - To enable AWE we must run SQL Server using a domain account and that has to be added to Lock Pages in Memory option.

 

SQL Server Memory Management Part I

Understanding Physical and Virtual memory helps to understand SQL manages memory

Windows has both physical and virtual memory. Memory is managed in the form of pages, ideally processes demands memory as needed. Memory pages are in 4kb size in both virtual and physical memory.

On Win32 bit architecture total addressable memory is 4GB out of which 2GB is accessible from user mode and 2 GB from kernel mode, and kernel mode memory shares between all processes. Using /3GB switch we can change the process address space of kernel made to 1GB instead of 2GB and user address space will be expanded by 3GB instead of 2GB. We need to add /3GB switch to boot.ini file and need to reboot the computer. 

On Win64 bit architecture total addressable memory is 16 EB but due to architectural reasons it supports only 16 TB on which the user address space is 8 TB and system address space is 8 TB.

Physical Memory -- It is actual memory (RAM) installed on the system. It works very fast but it is limited. 

Virtual Memory -- As the name suggests it is not real memory, it is one classification of memory which was created by using hard disk for simulating additional RAM, and the addressable space available to the user (Pagefile.Sys). It is divided into three categories 

a) Private virtual memory -- The address space for each process is private and which is not shared by other process until it is shared.

b) Shared virtual memory -- Two or more process can share regions of their virtual address space.

c) Free memory -- As the name indicates memory is not defined use and it is available to be allocated to your process.

 Private and shared memory is further categorized in two ways a) Reserved & b) Committed

Reserved -- The page has been reserved for future use, and the range of addresses cannot be used by other allocation functions. This page is not accessible and has no physical storage attached to it and it is available to be committed.

Committed -- This is the space that is currently used, physical storage is allocated for the page. In this case operating system will decide either physical memory or space in the paging file is sufficient for this allocation.

Understanding Index Scan and Index Seek

Understanding Index Scan & Index Seek


When we examine the execution plan then we find operators like index scan and index seek, let see what it is

Index Scan – It means SQL Server reads all rows on the table from first data page to last data page and shows rows that satisfy the search criteria. Index scan happens when query optimizer unable to find useful index to locate particular record or the query is fetching large amount of data which means more than 50 percent of table data. The query optimizer may choose index or table scan in fact for small table’s data retrieval via index or table scan is faster than using the index itself for selection. This is because the added overhead of first reading index then reading data pages doesn’t increase any performance.


Index Seek – The SQL Server uses B-Tree structure of index to seek directly to the matching records. An index is most beneficial while retrieving 10-15% of data from the whole table. Query optimizer try to use index seek which indicate it found useful index to fetch the desired records, if it fails to use index or the amount of data it is returning is proportional to table rows then it uses index scan.

SQL Server Statistics Part II

Statistics are nothing but a histogram and associated density groups

Density –Uniqueness of values with a set of data, calculated as 1/distinct number of rows.

1/2000 = 0.0005 density

Histogram – Represents the distribution of values for a set of data.

Statistics can be created in different ways

1. Statistics are created when you create any index in SQL Server

To see this, create an index on any table

SSMS – Databases – KalyanDB – (Create some index on Sales Table)

SSMS – Databases – KalyanDB – Tables – Sales – Statistics – There will be a statistics created same as Index name, if you double click on it then it displays statistics and density information.

2. If Auto Create Statistics is set to TRUE then SQL Server optimizer will create statistics automatically on non indexed columns (column statistics starts with _WA_*), And this is a permanent database object in the database until you drop it manually. And we need to remember one thing here Auto Create Statistics doesn’t create multi column stats it creates only single column stats.

SSMS – Databases – KalyanDB – Tables – Sales – _WA_Sys_00000003_0CBAE877 – There will be a statistics created by WA.

3. User can define statistics on columns by using create statistics command

Sp_helpstats ‘Table_name’ -- Displays statistics of columns and indexes on the given table. If you don’t specify ALL parameter it tries to display only column level statistics not at Index level and if there is no Index level statistics it displays “This object does not have any statistics.” 

Sp_helpstats ‘Table_Name’, ‘ALL’ (or)

DBCC Show_statistics(‘TableName’,’StatisticsName’) 

The DBCC Show_Statistics command displays current query optimization statistics for a table or indexed views.

The command returns three different datasets which represents statistics header information, density vector and histogram subsets.

Statistics Header Information contains the below information

Updated -- When the statistics last updated

Rows -- Total number of rows in the table or indexed view when the statistics were last updated.

Rows Sampled – If total number of rows < rows sampled then displayed histogram and density results are estimated based on sampled rows.

Steps -- Number of steps in the histogram, each step spans a range of column values followed by an upper bound column value. The maximum steps are 200.

Density Vector information displays All Density, Average Length and Columns information

Histogram displays distribution of values in the first key column of the statistics object.

When Statistics got updated?

If Auto Update Statistics is set to true then SQL Server optimizer will trigger update statistics only if the 20% of table + 500 rows got updated in the table.

There are two options

Auto Update Statistics – SQL Server updates statistics before generating the execution plan if the statistics are out of date.

Auto Update Statistics Asynchronously – SQL Server uses the out dated statistics to generate the execution plan and then update statistics afterwards.

To view statistics information

Sp_autostats -- Displays or changes the statistics options.

Sp_autostats table_name -- displays tablename, statistics name, and last updated column

Sp_autostats ‘kalyandb.sales’, ‘OFF’ -- Set statistics to Off on sales table

Sp_autostats ‘kalyandb.sales’, ‘OFF’, Index_Name (without quotes)

How do we know that table is using statistics or not?

By verifying the estimated number of rows and actual number of rows from the actual / estimated execution plan. If the numbers are close then statistics are being used by optimizer.

Some times we may need to manually update statistics, for which we can use sp_updatestats or update statistics 

Sp_updatestats -- Update all statistics for the database.

Update statistics table_name -- Updates statistics for all indexes on the given table

SQL Server Statistics Part I

In my previous post we learned something about Index seek and Index scan, If the data is highly redundant then Index will not be used, Index will be used only if the data is highly selective enough (that means when query fetches low number of records). If the data is not highly redundant or highly selective then optimizer uses statistics to take decision which one to use like Index scan / Index seek / what Index to be used / full table scan etc.,

Statistics

It is a critical metadata that assists query optimizer to take better decisions. It helps optimizer to do cost based estimation by using system tables and system catalogs by reading how many pages that table has and how many rows that table has. Also it helps in deciding the usefulness of indexes by verifying where clause, order by, group by etc.., based on the information data access algorithms will change to accessing the data.

The purpose of optimizer is to find good plan with fast, not to find good plan.
By seeing the statistics query optimizer will do cost estimation at each step and come up with an efficient plan with lowest cost. So statistics plays important role in preparation of efficient cost based estimate plan so statistics should be accurate and we need to make sure these are up to date. 

This information will be stored in sys.indexes and sys.stats tables in SQL Server. We can 249 non clustered indexes in SQL 2005 and 999 non clustered indexes on SQL 2008, and we can have 2000 stats in SQL 2005 and 10000 stats in SQL 2008 per table and 30000 stats per table in SQL Server 2008 R2.

1. For every index statistics are automatically created
2. We can create statistics on individual columns automatically or manually
3. We can create statistics on multiple columns manually


To update statistics manually
Sp_updatestats (OR) Update Statistics

About Lookup Operators Bookmark lookup, Key Lookup, RID Lookup

When a select query requested rows the optimizer will try to use a non-clustered index on the column or columns in WHERE clause to retrieve the requested data, If the select query consists columns which are not present in the non-clustered index then SQL server need to retrieve actual data pages. If the table has clustered index it is called bookmark lookup (or key lookup). If a table doesn’t have clustered index but it has non-clustered index then it is called RID lookup. These kinds of lookup operations are very expensive and impacts query performance. To optimize any query we should try to remove bookmark lookups or RID lookups by verifying the execution plan.


Bookmark lookup -- It is a process of finding actual data in the SQL table, based on an entry found in the non-clustered index. When you search for a value in a non-clustered index and your query needs more fields than the key columns, then SQL Server need to retrieve the actual data pages, that’s what called a bookmark lookup.

RID Lookup -- You may see RID lookup operator in the execution plan while executing the select statements for those tables doesn’t have clustered index. A RID lookup is a lookup into a heap using a ROW ID. The Row is included with entries in a non-clustered index in order to find the rest of table’s data in a heap.


To overcome the RID lookup we need to define clustered index on the table.

To overcome the bookmark lookup we need to create a non-clustered all the columns which are being used in the select statement (OR)
We need to create non-clustered index with INCLUDED columns to avoid index limitation.

 Summary

Bookmark lookup and RID lookup are performs random IO operations into the clustered index, and Random IO operations are very expensive. To avoid this we need to create proper indexes on the tables

Filtered Indexes in SQL Server 2008

Filtered Indexes

Prior to SQL Server 2008 we can define index only on entire table, with filtered index we can define index on a filtered data or subset of data in the table. The definition of a filtered index contains where clause that determines if a row in the table will be index or not. The B-Tree containing the rows from the filtered index will contain only those rows which satisfy the filter criteria used while creating the index. These types of indexes are well-suited for scenarios where you frequently query a specific subset of data, for example date ranges etc.,

A normal clustered or non-clustered index would be created on entire table and size of index would be larger and occupy more space.

The filtered index statistics are more accurate because they consider only specific set of rows and due to smaller size it reduces cost/overhead of updating the statistics.


The impact of DML is less in the filtered index as it is updated only if an insert or update will satisfy the filter.

 A filtered index can be rebuilt online. You can create only non-clustered filtered index, it means you cannot created clustered filter index.

 Examples

Create Nonclustered index FI_EOJ on Employees(DOJ) where DOJ is not null

Create NonClustered Index FI_DeptID on Employess(DeptID) where IsActive = 1


Select Example by forcing Index (No need to provide index name)
Select * from Employee with (index(FI_DEPTID)) where isactive = 1

Note – If you pass variable or constant to where condition the optimizer will not choose filter index.

Indexed Views in SQL Server

Indexed Views -- An indexed view is a view that has a unique clustered index created on it. Normally the records of view don’t exist on the disk, whereas in Indexed views the rows are stored on the disk. Since the indexed view exists on the disk the disk space was taken by view.

We can use this Indexed views while writing query with a complex join on large amount of data. There will be good performance while retrieving the data because it exists on the disk and joins and aggregations are already defined in advance of running the query.

 Limitations for Indexed views
Tables must be referred only thru two-part names.
All the tables must reside on the same database
The view must be created with SCHEMABINDING option
The ANSI_NULLS must set to ON.
The select statement cannot contain Union, TOP, Distinct, Orderby, count(*) etc.,


SCHEMABINDING – This locks the underlying tables preventing schema changes being made to the tables that would affect the view. We cannot rename or drop the fields from the table which are referred in the indexed view.

We can add clustered index to view only after creating the view not possible at the time of creation. This makes SQL to materialize the view to disk.

Example

create view sales_view_indexed with schemabinding as select pp.Name, pp.ProductID, sd.SalesOrderID, sd.SalesOrderDetailID, sd.CarrierTrackingNumber, sd.OrderQty, sd.UnitPrice, convert(money,(sd.UnitPrice * sd.OrderQty * (1 - sd.UnitPriceDiscount)/100)*100) discount_price from sales.SalesOrderDetail sd , production.Product pp where pp.ProductID = sd.ProductID

The above statement creates a view with name sales_view_indexed on the database. As discussed above we need to add clustered index to it, otherwise only metadata will be stored on the view just like a normal view, you can verify it by using the below command

sp_spaceused sales_view_indexed

create unique clustered index idx_SalesOrderDetailID On sales_view_indexed (SalesOrderDetailID)

After running the above command it creates index on the view and becomes materialize, Now examine the space you will identify it occupied space on the database.

sp_spaceused sales_view_indexed

As discussed Indexed view is materialized and it has data on it after adding index to it, If you run the below command it should refer only view, but if you execute the below command it refers the physical tables like products and sales order details table. (see the execution plan)

set statistics io on

select * from sales_view_indexed where SalesOrderID = 75121

To make use of Indexed view we need to give command as below

set statistics io on

select * from sales_view_indexed with (noexpand) where SalesOrderID = 75121

NOEXPAND – By specifying this key at select statement it fetches records directly from the indexed view without touching the base tables. Like this we can increase the performance of specific queries which has lot of joins and large amount of data on it.

Advantages
Improves the performance of SELECT statements
Indexes and data on multiple tables
Avoid complex joins and aggregations at run-time

Disadvantages
Increased disk space used by the database as the views are created with physical data.
Slows down the performance of the insert, update and delete statements on the tables used in indexed views.
Online re-index is not supported in indexed views.
High number of updates will not serve the benefit.

 

You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control Lists). Ask the Web server's administrator to give you access. sql server reporting services

Not able to access the SSRS reports and getting below issue

Description: An error occurred while accessing the resources required to serve this request. You might not have permission to view the requested resources

You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control Lists). Ask the Web server's administrator to give you access. sql server reporting services

Workaround

1. Make sure your group has been added to http://localhost/Reports -security tab
2. Make sure that report url is opening from the SSRS configuration wizard  without any issues.
3. Assign read, list, execute permissions to ReportServer directory and its child folder on
MSSQL10 / MSSQL11 folders

 

INCLUDED COLUMNS or Covering Index in SQL Server

INCLUDED Columns in SQL Server

To overcome the limitations of non-clustered indexes from SQL Server 2005 onwards they introduced a concept called Included Columns

Limitations of non-clustered indexes are
Only 16 columns can be included in the index.
Maximum index key cannot exceed 900 bytes.

Covering Index which is a non-clustered index that contains all columns that are appear in the select statement, below example shows how covering index works. We need to remember covering index is not always gives good performance advantage.


Examples

create table BookCLTest (BookID Int, BookName varchar(500), BookDescription varchar(500))

create index idxBookName on BookCLTest(BookName, BookDescription)

After executing the created index command you will see the below error because it has limitation of 900 bytes

Warning! The maximum key length is 900 bytes. The index 'idxBookName' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.

insert into bookClTest values (1, replicate('A',500), replicate('B',500))

If you insert any record with maximum size it wont allow you to insert and you will get the below error:

Msg 1946, Level 16, State 3, Line 1

Operation failed. The index entry of length 1000 bytes for the index 'idxBookName' exceeds the maximum length of 900 bytes.

Alter table BookCLTest add RackLocation varchar(max)

create index idxbooklocation on BookCLTest(RackLocation)

You cannot create non clustered index on large datatypes like text, image, varchar(max) etc., columns

From SQL Server 2005 version onwards we can overcome this problem of Index size and Column limitations using INCLUDED Columns also we can include large data types.

Benefits of Included columns

1. We can create index with more than 900 bytes size.
2. We can include large data types like varchar(max), nvarchar(max) etc.,
3. Savings Disk IO

How it saves Disk IO?

When a non-clustered column is required in the select list, the actual table row must be accessed to retrieve the column; this is called a bookmark lookup. The bookmark lookup is most expensive operation when there is large table. If we include the non-clustered column in the included list then IO performance will increase.

We will remove the existing index in our example
sp_helpindex BookCLTest

drop index idxBookName on BookCLTest

Included Columns example

create index idxBookName on BookCLTest(BookID) include (BookDescription, RackLocation)

In the above example we have included one varchar column and large type column.

Considerations
1. Column names cannot be repeat in the included columns
2. The same column name which used to create non-clustered index cannot be include in the included column list
3. All columns can be included except text, image.


Script to view table that has included columns

select object_name(sic.object_id), * from sys.index_columns sic, sys.syscolumns sc, sys.tables st where sic.object_id = sc.id and sic.column_id = sc.colid and st.object_id = sic.object_id and st.object_id = sc.id and

sic.is_included_column = 1 

What is Key column and Non-key column

 

The column which is specified to create a clustered or non clustered index is key column.

 

The columns which are added to INCLUDE clause are non-key columns

 

Note - A column cannot be a key and non-key column.

 

The primary difference between key and non key columns are where the data of the column fit in the B-Tree structure, All clustered index and non-clustered key columns are stored in every level of

B-tree ie., either leaf level or intermediate levels. Whereas non key column stored only at leaf level.