Wednesday, December 11, 2013

Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517)


I got the below error while trying to modify the database properties (example changing recovery model), and I have db_owner privilege on the database. db_owner is sufficient to update the properties but we got the below error while trying to update it.

Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517)

Try the below steps to resolve the issue

1. Verify database owner is set to database or not, If database owner is null then assign "sa" or any other user to the database

2. If the user is already a member of database then it wont allow us to assign the same user as owner, so assign any other user as owner (but dont leave it as null)

3. Run the command "Alter authorization database::db_name to login_name"

4. Verify view any database option is selected for that user or not, if not assign that securable to the login.

I tried all the above options, later I realized we are using SSMS 2012 Tools RTM after upgrading the RTM tools with SP1 we are able to do view / modify the database properties.

 

Tuesday, December 10, 2013

Groupwise Sequence Number Generation , Rank and Row Number Functions

Rank and Row_Number Functions

In the below table I have couple of duplicate image numbers, I need to assign sequence number for duplicate image numbers. For example if there are three duplicate image numbers then sequence should be 1, 2, 3 for those three and again the sequence will start from 1 for next image number.

ROW_NUMBER : Returns a unique number for each row starting with 1. For rows that have duplicate values,numbers are arbitarily assigned.

Rank : Assigns a unique number for each row starting with 1,except for rows that have duplicate values,in which case the same ranking is assigned and a gap appears in the sequence for each duplicate ranking.

CREATE TABLE #tmp(IdNo INT, Image_No VARCHAR(10), Seq_No INT)
GO
INSERT #tmp (IdNo, Image_No) VALUES(1,'FS1')
INSERT #tmp (IdNo, Image_No) VALUES(2,'FS1')
INSERT #tmp (IdNo, Image_No) VALUES(3,'FS2')
INSERT #tmp (IdNo, Image_No) VALUES(4,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(5,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(6,'FS3')
INSERT #tmp (IdNo, Image_No) VALUES(7,'FS2')
GO
Below query used to return sequence number without using Row_Number and Rank Functions

SELECT IdNo, Image_No, (select count(*)+1 from #tmp b where IdNo < a.IdNo and a.Image_No = b.Image_No) FROM #tmp a order by Image_No

Rank Function

SELECT IdNo, Image_No,  RANK() OVER(PARTITION BY Image_No  ORDER BY idno) as 'Rank'  FROM #tmp

Row_Number

SELECT IdNo, Image_No,  ROW_NUMBER() OVER(PARTITION BY Image_No  ORDER BY idno) as 'RowNumber'  FROM #tmp
 

Wednesday, November 27, 2013

There is insufficient system memory in resource pool 'default' to run this query. (Microsoft SQL Server, Error: 701)

There is insufficient system memory in resource pool 'default' to run this query. (Microsoft SQL Server, Error: 701)

I got the above error in my SQL Server 2012 Instance when I am trying to execute any job from the SQL Server Agent and there are couple of memory errors in error log file.

After troubleshooting various issues I find SQL Server 2012 Instance memory is limited to 2 GB, I changed it to 3 GB (system memory is 4GB) then I can able to execute jobs or executing other queries.

Will post a seperate article about SQL Server 2012 memory architecture.

Monday, November 18, 2013

The report server installation is not initialized. (rsReportServerNotActivated) Fix for Reporting Services Issues

The report server installation is not initialized. (rsReportServerNotActivated) 

We can fix this issue by using the following ways : (This may occur when you migrate report server database from other server)

1. If it is new server then remove the existing Report Server Database and re-create new one using reporting serivces confiruation window

2. Connect to SSMS - Report Server Database -- Delete the row which machine is our server name

3. Restart Reporting Services service

An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.  (rsReportServerDatabaseError) Get Online Help Keys lock row not found

If you delete any records in ReportServer database or due to misconfiguration you may receive that error, ReCreating Reporting Services help to solve the issue,

Scale-out deployment is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Scale-out deployment error was encountered while migrating reporting services database from one server to another.
After migrating database from Server A to Server B, for the first time I opens the report I got this error:

We can remove the old server key entry from key table in ReportServer database also remove the server name from scale-out deployment window on reporting services configuration manager
 

Friday, November 15, 2013

DDL Trigger to Audit newly created databases, Send Mail when new database is created on the server

The below script send a mail to DBA whenever a new database was created / dropped by any user, It will help us to audit newly created and dropped databases.


CREATE TRIGGER Audit_Databases ON ALL SERVER for CREATE_DATABASE, DROP_DATABASE --for DDL_DATABASE_LEVEL_EVENTS
AS

BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @data XML
SET @data = EVENTDATA()
DECLARE @IP_ADDRESS VARCHAR(20)
DECLARE @SQL1 VARCHAR(100)
DECLARE @SQL2 VARCHAR(100)
DECLARE @SQL3 VARCHAR(100)

SET @SQL1 = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')
SET @SQL2 = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
SET @SQL3 = @@SERVERNAME

DECLARE @SQL VARCHAR(2000)
SET @SQL = 'EXEC MSDB..sp_send_dbmail @profile_name = ''Kalyan_Notifications'',
@recipients =''kalyan@yahoo.com'',
@SUBJECT = '' DATABASE ALERT'',
@body ='' '+ @SQL2+' DATABASE IS "'+@SQL1 + '" +  AT SERVER "' + @SQL3 +'"'''
--PRINT @SQL
EXEC (@SQL)
END
 

Thursday, November 14, 2013

Migrating Reporting Services To Another Server

At the time of hardware upgrade or version upgrade we may need to move SSRS databases to new server, Below are the steps which helps to move SSRS databses

For example we are moving reporting services from Server A to Server B

1. Backup ReportServer, ReportServerTempDB Database in Source from Server A database (ReportServerTempDB is not mandatory)

2. Backup SSRS Encryption Key from Reporting Services Configuration manager from Server A

3. Copy SSRS database backups and encryption key to Server B manually.

4. Stop Reporting Services in Server B

5. Restore ReportServer and ReportServerTempDB databases on Server B

6. Configure ReportServer using Reporting Services Configuration window and while selecting databases select use existing option and select ReportServer database from Server B

7. Restore Encryption key on Server B

8. Restart Reporting Services

9. From SSMS, Query the keys table in ReportServer Database
You will able to see two different keys in that tables, One key belongs to current machine and another key belongs to Server A (because we restored database from Server A)

10. Delete that ServerA entry from the keys table

11. Remove the Server A entry from the Scale-out deployment tab Reporting Services Configuration wizard

12. Restart Reporting Services

Now you can able to access the report without any issues.

Friday, November 8, 2013

Reading SQL Server Error Log Files using TSQL


Sp_readerrorlog and xp_readerrorlog there are two stored procedures which helps to read SQL Server error log information using T-SQL Commands.

Sys.sp_readerrorlog syntax has 4 parameters Log File Number, LogFileType, SearchString1, SearchString2

Log File Number -- Indicates which file you want to read 0 for current and 1 for Archive#1 and so on

LogFile Type  -- 1 is for SQL Server Error log and 2 is for SQL Agent log

SearchString1  -- Displays lines which has matched string  value (for example if you want to search
for Failed keyword you need to specify “Failed” as parameter)

SearchString2   -- Further filter on search string1.

sys.sp_readerrorlog 2,2, 'Failed' -- Displays the lines which contains Failed Keyword from Archive#2 file

sys.sp_readerrorlog 2,2, 'Failed', ‘Logins’  -- Displays the lines which contains Failed Keyword with only Login failures (it is a further search on searchstring1)

In addition to sp_readerrorlog there is another extended stored procedure xp_readerrorlog which took 7 parameters

Xp_readerrorlog Lognumber, Logtype, SearchString1, SearchString2, StartTime, EndTime, SortOrder

First 4 parameters are common for sp_Readerrorlog and xp_readerrorlog sps,
StartTime -- Starts reading logs from the specified start date onwards
EndTime -- Reads logs till specified end date
SortOrder -- ASC or DESC

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20131105', NULL,’DESC’ – This command displays all lines from current server log file start searching from 05-Nov-2013 onwards to till date becoz the second parameter is null and displays results in DESC order

Xp_enumerrorlogs  -- Displays list of errorlog files for the current instance

Xp_enumerrorlogs (or) xp_enumerrorlogs 1

xp_enumerrorlogs 2  -- Displays list of agent error log files for the current instance

exec sp_cycle_errorlog -- Recycles SQL Server error log

exec sp_cycle_agent_errorlog -- Recycles SQL Server Agent error log.

xp_readerrorlog 0,1, "Logging SQL Server messages in file"  -- Simple command to display path of SQL Server log file.

Wednesday, October 30, 2013

SQL Server Error Log Management


SQL Server maintains Error log file which can store information messages, and errors with numbers similar to windows event viewer. When we encounter any issue in the SQL Server this error log is the first place we need to check whats wrong with the server. These error log files are stored in the LOG folder of each instance example "c:\program files\microsoft sql server\mssql10.50\mssql\log",
If required we can change the location of error log files by modifying -e parameter in the SQL Server Engine Configuration.

We can see these error logs in SSMS - Management - SQL Server Logs - Current, Archive#1, Archive#2,  Archive#3, Archive#4, Archive#5, Archive#6
These will be maintained by SQL Server, Each time if you restart SQL Server these files get recycle, what it means is the Archive#6 file gets deleted  and Current will become Archive#1 and so on and new file will be created and named as Current.

There are couple of disadvantages using the default behavior of SQL Server that there wont be any clue for size of the error log files, that means you cannot except the size of all error logs should be same; one file can contain 10 days of data and other file contains only 2 days of data.  And recycling happend thru SQL Server so users may not control the default behaviour, so that we need to set seperate process to make sure one file has only 1 day of information.

Also we need to modify the default error log files from 7 to some large number, otherwise we may lost the history.

As a first step we need to increase the error log files from 7 to 50, To do so
SSMS - Management - SQL Server Logs - RightClick - Configure - Check Limit the number of error files before they are recycled We can also create one error log file for every one day by calling the sys.sp_cycle_errorlog on daily basis, It will be very helpful while managing error log files.

One day data will be stored for one log file so that we can get go back and verify those files at any time easily. The same is applicable for SQL Server Agent also, but there 10 files will be maintained instead of 7 files.

Like SQL Server Error logs we cannot change increase the number of error logs for SQL Server Agent, sp_cycle_agent_errorlog helps to recycle Agent error logs without restarting Agent.

Monday, October 28, 2013

Convert Run Duration in sysjobhistory


Convert run duration of sysjobhistory to hh:mm:ss

The run_time 25823 means started at 02:58:23 and run_duration 355 means the job executed for 00:03:55 means 3 minutes 55 seconds


select j.name, step_name, run_date, run_duration,
substring(cast(replicate('0',6-len(run_duration)) + cast(run_duration as varchar) as varchar),1,2) + ':' +
substring(cast(replicate('0',6-len(run_duration)) + cast(run_duration as varchar) as varchar),3,2) + ':' +
substring(cast(replicate('0',6-len(run_duration)) + cast(run_duration as varchar) as varchar),5,2)
as 'hh:mm:ss'
from sysjobs j, sysjobhistory jh where j.job_id = jh.job_id and j.name like 'JobName%'
and jh.step_name like '%outcome%'
order by run_date desc

 

Wednesday, October 23, 2013

Encryption Examples

TDE -- Transparent Data Encryption

Below are the steps for TDE

1. Create master key for the database

2. Create certificate that protected by master key

3. Create a special key Database Encryption Key that used to protect database, and secure it using certificate.

4. Enable encryption on database.

use master
create master key encryption by password = 'calyansql@123'
create certificate encryption_cert with subject = 'KalyanDB_Certificate'

use KalyanDB
create database encryption key with algorithm = AES_128 encryption by server certificate encryption_cert
--Alogirthm can be AES_128, AES_192, AES_256, or Triple_DES_3Key

Alter database KalyandB set encryption on

-- Backup the certificate is very important step to move this to target server while restoring the Encrypted Database

use master

backup certificate encryption_cert to file ='c:\temp\encryption1_cert.cert'
with private key (file='c:\temp\encryptionprivatekey.key', encryption by password='calyansql@123')

backup database kalyandb to disk='c:\temp\kalyandb.bak'
select * from sys.certificates

How to restore encrypted database on target server?

1. Move database backup and certificate files to target server
2. Create master key
3. Create certificate using the transferred file from the source server

Example

use master
create master key encryption by password='calyansql@123'
create certificate encryption_cert from file='E:\temp\encryption_cert.cert'
with private key (file='E:\temp\encryptionprivatekey.key', Decryption by password='calyansql@123')

After creating certificate we can restore the database in normal way, without creating the certificate we cannot do restore.
 

Tuesday, October 22, 2013

SQL Server Encryption Part I

Encryption is the process of altering data in such a way that hackers cannot read it whereas authorized users can read it. In SQL Server we can encrypt data using a key or password, without having key or password the data cannot be decrypted. Companies that are maintaining sensitive data should meet various compliance requirements such as Gramm-Leach-Bliley Act (GLBA), European Union Data Protection Directive (EUDPD), Health Insurance Portability and Accountability Act (HIPAA), Payment Card Industry Data Security Standard (PCI DSS) and Sarbanes-Oxley (SOX) act. They require encryption of sensitive information like (account numbers, credit card numbers etc) at database levels as well as OS levels.

SQL Server provides various encryption options like database-level encryption, OS level encryption, column-level encryption, and transport-level encryption. 

Transparent Data Encryption (TDE) -- It introduced in SQL Server 2008 and available in Enterprise, Developer in 2008R2 and 2012 versions. It has ability to encrypt an entire database and it is completely transparent to application. It encrypts mdf and ldf files data using AES (Advanced Encryption Standard) and 3DES (Triple DES) encryption methods. This method encrypts the backup file so if we lost the backup media then this backup cannot be restored without a key.

Cell Level Encryption -- It is also known as column level encryption introduced in SQL Server 2005 and this feature is available in all editions including express edition. This methods needs application must be changed to use encryption and decryption operation, in addition it affects performance.

Encrypting and Decrypting Data with .NET Framework -- SQL Server stores encrypted data but encryption and decryption is performed thru application. Application need to perform encryption and decryption by calling specific methods.

SQL Server encrypts data with a hierarchical encryption and key management infrastructure. It is three layer hierarchies, OS Level, SQL Server Level and Database Level.

The service master key (SMK) is the top-level key and is the father of all the keys in SQL Server. The SMK is an asymmetric key that encrypt by the Windows Data Protection API (DPAPI). The SMK is automatically created when you encrypt something for the first time and tied to SQL Service account. The SMK is used to encrypt database master key (DMK). The second layer of encryption is DMK, It encrypts symmetric keys and asymmetric keys and certificates. Each database will have only one DMK.

Symmetric Key -- In this cryptography the sender and receiver will share a common key to encrypt or decrypt the message. This is easy to implement and the sender and receiver can encrypt and decrypt the messages.

Asymmetric Key -- This cryptography is also known as public-key cryptography in which sender and receiver will have a pair of cryptography keys known as public key and private key to encrypt and decrypt messages. In this method sender has to use his key to encrypt the message whereas he couldn’t decrypt the message and receiver has to use his key to decrypt the message whereas he couldn’t encrypt it. This is a resource intensive process. 

Friday, October 18, 2013

Cluster network name resource 'SQL Network Name (VirtualServerName)' failed to create its associated computer object in domain 'domain.com' for the following reason: Unable to create computer account.

The following error has occurred:
The cluster resource 'SQL Server (virtual name)' could not be brought online due to an error bringing the dependency resource 'SQL Network Name (virtual name)' online.

We may recieve this error message while performing SQL Server cluster installation if there is no computer object defined at Active Directory,

1. Create computer object with virtual server name on associated OU
2. Provide full rights to Cluster Name for the added computer object
3. Provide full rights to DBA group (recommended)


 

Wednesday, October 9, 2013

Database Mirroring Part IV Suspended (Error: 1453, Severity: 16, State: 1) Add Log files to Mirrored Database

How to add additional log files to Mirrored database?

If you add new transaction log file for the same folder at the time of configuration of mirroring there wont be any issues, whereas if you add a transaction log file in new folder on principal server (if that folder is not exists on the mirror server) then we will get the below error and Mirroring will goes into suspended state
Error: 1453, Severity: 16, State: 1 (In principal server)
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file Path (In Mirror server)

To fix the issue we should create the folder in mirror server same as principal server then we can resume the mirroring.



 

Tuesday, October 8, 2013

The server network address can not be reached or does not exist Microsoft SQL Server, Error: 1418

The server network address "TCP://ServerName.domainname.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

The above error may occur while configuring database mirroring

1. Make sure you restored full backup on Mirror server (with norecovery mode)
2. Make sure you restored atleast one log backup on Mirror server (with norecovery mode)
3. Try to connect mirror instance from principal server and vice versa ( to make sure mirror is contacting principal and principal is contacting mirror)
3. Verify the listener port using the below command
telnet sqlservername portnumber
telnet sqlservername 5022   -- If the port is not working it displays connection could not open, If it works then it doesn't prompt you anything on the console
4. Issue the below command from both principal and mirror instances

Grant connect on endpoint::mirroring to domain\account (If this doesnot works, use the below command)

Grant connect on endpoint::mirroring to public

Monday, October 7, 2013

Recovery Models in SQL Server


Recovery Models -- Recovery model defines how to maintain log file in SQL Server
There are 3 recovery models

1. Simple 2. Bulk – Logged 3. FULL

Simple Recovery Model 

Every DML & DDL operation will record into T-log file (to support basic recovery ability when SQL Server shutdown or a long operation is being cancelled)
Using this recovery model we can’t achieve point in time recovery
Log file gets truncated after the transactions being committed or checkpoint occurred.
No log backups can be generated in this mode
Not able to configure Logshipping or mirroring using this mode
In simple recovery model we can generate FULL, Differential backups (except TLog backups)
It is suitable for Test / DEV environments where point in recovery is not required.

Bulk Logged Recovery Model

Every DML & DDL operation will record into T-Log file except Bulk Logged operations such as SELECT INTO, BULK INSERT, CREATE INDEX etc.,
The advantage of using this recovery model is it doesn’t occupy large space of transaction log while doing bulk operations
If there are no Bulk operations then it acts like a FULL recovery model.
Logshipping can be configured using this recovery model, Mirroring cannot be configured.
Point in time recovery is possible however we need to re-run the Bulk operations.
In this we can generate all kinds of backups (full, diff, tlog etc)
It is suitable where normal data is critical whereas bulk operations are not critical.

FULL Recovery model

Every DML & DDL operation will record into T-Log file
Using this recovery model Point in time recovery is possible
Logshipping and Mirroring can be configured in this recovery model
Log file grows very faster
Checkpoint Process
It is a program executed by SQL Server to transfer all committed transactions from Transaction log to data file
It takes all committed transactions PageIds, LSN from buffer pool and writes into respective pages in the data file
Lazy writer is a process which takes pages from data cache and writes into data file
Checkpoint truncates TLog file, If recovery model is set to Simple
Checkpoint occurs periodically (based on recovery interval) or can be issued manually.
It occurs automatically when a backup start.
It occurs if size of Tlog file is 70% full.
When a new data file or log file was added to database.

Saturday, October 5, 2013

How to attach MDF without transaction log file

We can attach MDF file without LDF in two ways:
Using SSMS
Using T-SQL


Using SSMS

SSMS - Databases - Rightclick - Attach - Browse and select the MDF file
In the database details section it displays two files
1) MDF file with location from where you want to attach
2) LDF file with some other location (Select this file and Click on Remove button)
3) Click OK (Database will attached to that location)

Using TSQL

 CREATE  DATABASE AdventureWorksDW2012 ON (FILENAME = N'E:\DATA1\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG


If Database has multiple log files you may not attach with SSMS use the T-SQL Syntax to do so, it will create only one ldf file while attaching not multiples.

Friday, October 4, 2013

SQL Server Database Mirroring Part III

Steps to add secondary data file to Mirrored Database

1. Make sure mirrored database is in synchronized state using below query - at principal server

SELECT db_name(database_id) as database_name, mirroring_state_desc,
mirroring_role_desc, mirroring_partner_name, mirroring_partner_instance
FROM sys.database_mirroring where mirroring_state_desc = 'SYNCHRONIZED'
2. Disable backup jobs at principal server if any

3. Run the below command to stop the mirroring -- at principal server
Alter database MirrorNew set partner off

Now you can observe db mirroring will be removed and mirror server database state becomes restoring

4. Add secondary data file / filegroup to principal server database - at principal server
alter database mirrornew add file
(name='second_datafile',
filename='E:\Program Files\Microsoft SQL Server\DATA\MirrorNew_second.ndf',
size = 50mb)
5. Make sure file got added to principal server database -- at principal server
select * from MirrorNew..sysfiles

6. Generate log backup of principal database -- at principal server
backup log mirrornew to disk='e:\backups\mirrornewwithsecondary.trn' with init

7. You can verify whether log backup has newly added file or not using the below command
restore filelistonly from disk='e:\backups\mirrornewwithsecondary.trn'

8. Copy the log backup to mirror server

9. Run the below command to verify the file location of the database
select * from sysaltfiles where dbid = DB_ID('mirrornew')

10. Restore the log file on mirror server database using with move option -- at mirror server

restore log mirrornew from disk='e:\backups\mirrornewwithsecondary.trn'
with norecovery, move 'second_datafile' to 'E:\Program Files\Microsoft SQL Server\\DATA\MirrorNew_second.ndf'
11. Verify newly created file added to database or nore -- at mirror server
select * from sysaltfiles where dbid = DB_ID('mirrornew')

12. Reinitiate the mirroring from mirror server -- at mirror server
alter database mirrornew set partner ='tcp://kalyanmirror:5022' -- It moves database into Inrecovery mode

13. Finally initiate mirroring step from principal server -- at principal server
alter database mirrornew set partner='tcp://kalyanprimary:5023'

Now database mirroring will established between principal and mirror, Execute the step 1 to make sure both databases are in sync state.

Wednesday, October 2, 2013

SQL Server Database Mirroring Part II


EndPoints -- These are used to define how servers will communicate each other. Database mirroring is based on TCP endpoints to send and receive information between servers instances that are exists on mirroring sessions.  One TCP endpoint will be used for multiple mirroring sessions. In database mirroring only committed transaction data is sent to mirror instance, rolled-back transaction data is not sent to the mirror server, however in log shipping both committed and rolled-back information will sent to secondary server.

Requirements

Service Accounts will need to be granted permission to connect to endpoints on the other partner instances (principal, mirror and witness). If SQL Server is not running in single domain the certificate authentication need to be used.

Creating Endpoints -- Configuring endpoints (port listeners) where the principal and mirror are going to establish communication.

Principal – Create Endpoint Principal_Endpoint state=Started as TCP(Listener_port =  5022) for database_mirroring (role = partner) – Enable as Partner only

Mirror – Create Endpoint Mirror_Endpoint state=Started as TCP(Listner_port=5023) for database_mirroring (role = all) – Enable as mirror or witness server
To view mirroring endpoints
Select name, role_desc, state_desc from sys.database_mirroring_endpoints;

Important points

Database mirroring is high availability solution for database
Database mirroring can be configured only with FULL recovery model database
Express edition can be configured only as witness server
Database mirroring cannot be configured with multiple mirror servers
Mirroring is specific to database, so logins will not moved to mirror server, need to move them manually
We cannot configure mirror for master, model, msdb and tempdb databases
Include failover partner connection string in ADO.NET to make use of automatic failover
One witness can be configured with multiple mirror servers
Mirror can be used as read-only using snapshots.
Snapshots can be created on mirror server.
Write a script on active instance to enable Agent jobs when failover happened, if automatic failover is configured.

Tuesday, October 1, 2013

SQL Server Database Mirroring Part I

Database Mirroring is a high availability solution for database availability. It maintains two copies of database to ensure database availability to minimize or not downtime. These two copies maintained on two different servers, one principal server – which serves the user requests and database on principal server is in available mode to users and the second mirror – which acts as a hot standby for principal server and database is in restoring mode (not available to users). We can bring secondary server (mirror server) online if something goes wrong with Principal server or it can be automated using Witness server.

When databases are in synchronized state the secondary server acts as a hot standby that supports immediate failover without data loss from committed transactions

When databases are not in synchronized state the secondary server acts as a warm standby (with data loss).

Requirements

Database mirroring is available in SQL 2005, 2008, 2008R2 and 2012
Database mirroring is available in enterprise and standard edition (In STD there are certain limitations)
Database mirroring works only on FULL recovery model, Simple and bulk-logged recovery models doesnot support.
Mirroring cannot be configured on system databases Master, Model, MSDB and Tempdb.
For automatic failover you need to configure witness server must have SQL Server 2005+ and can be any edition (standard, enterprise or express edition also)
We need to initialize the mirroring database (need to restore principal server full backup on mirror server on norecovery mode)

Mirroring Modes

Synchronous (High Protection Mode) – With this mode transactions cannot commit on the principal until all the transactions logs are copied to mirror server, mirror server sends acknowledgement to principal server in this mode. This mode guarantees that both principal and mirror servers are in sync and there wont be any data loss if principal server fails. It acts similar to High availability mode except the automatic failover (principal to mirror) process is manual.

Asynchronous mode (High Performance) – With this mode the overall architecture as a warm standby and doesn’t support automatic failover. The data transfer between principal and mirror server are asynchronous, Transaction log records are committed only on the principal server before the log records shipped to mirror server, in this mode if there is anything happen to primary database there will be a data loss.

High Availability (High Safety with witness) – It provides synchronous transfer of data between principal and mirror server databases also it supports automatic failure detection and supports automatic failover. This mode requires all three servers principal, mirror, witness servers, There will be no data loss because it uses synchronous data transfer.

In SQL Server 2008 few additional functionality been added to Database Mirroring

Automatic Page Repair -- If a corrupt page found in the principal (or mirror) server then database mirroring system will attempt to obtain page from the mirror (or principal) database then repair the corruption.

Logstream compression -- By compressing the log records before sending them to the mirror database which increase the performance of log records while travel across the network.
 

Friday, September 27, 2013

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'
 

Thursday, September 26, 2013

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.
 

Wednesday, September 25, 2013

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.

Tuesday, September 24, 2013

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.,

Monday, September 23, 2013

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;
 

Friday, September 20, 2013

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.

Wednesday, September 18, 2013

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).

Monday, September 16, 2013

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

Saturday, September 14, 2013

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.

Friday, September 13, 2013

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.

 

Thursday, September 12, 2013

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.

Tuesday, September 10, 2013

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

Saturday, September 7, 2013

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

Thursday, September 5, 2013

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.

 

Tuesday, September 3, 2013

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

 

Monday, September 2, 2013

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 =


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.