Why & How do we create index on Foreign key

–When are index on foreign key required 1. You see many of “TABLE ACCESS FULL” operations against the child table when you perform DML against the parent table. 2. You update/delete against the primary key in the parent table –Importance of index on foreign key 1. Table-level locks are avoided & Proper/less IO consuming joint operations could be established 2. Without a foreign key index, SQL could not perform a nested loops join, and a more expensive sort merge join might be required –Script to detect all foreign key constraints that are unindexed set pagesize 5000 set linesize 350 column status format a10 column … Continue reading Why & How do we create index on Foreign key


Transperent data encryption TDE by Anthony B We can encrypt columns on a table as well as encrypt the entire tablespace and backups. There is a performance overhead associated with the encryption/decryption process as encrypted columns cannot be used in Index scans. TDE works by setting wallet and generating a key/password to access data as it will be needed to de-crypt the data. Hackers will therefore need this key in order to de-crypt and as such the data is useless to them if hacked. In this exercise, we will encrypt a column on a table as well as tablespaces in … Continue reading ORACLE TRANSPARENT DATA ENCRYPTION – TDE

Change SQL server database file location without taking the database offline

Changing SQL server database file location is not a frequent practice, but what happens when you require to change file location to different directory? To avoid any database interruption DBCC SHRINKFILE command with the EMPTYFILE option, will allow to perform this migration operation while the database is online. Step01: First check the database files size and available space SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0-CAST(FILEPROPERTY (name, ‘SpaceUsed’) AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files; Step02: Go to database property you intend to move location, add file with new Path. Property – files – add Step03: Now use … Continue reading Change SQL server database file location without taking the database offline

Change SMTP server name

                   Simple Mail Transfer Protocol or SMTP is what allows you to receive email alerts from your Instance to either yourself, your alerting system or group of people. Now, in order for SMTP to work, it must be configured with a server. Some of our Instances were still using old deprecated SMTP servers so I needed to update all the SMTP servers to the current one. I used the following script to resolve the issue across my multiple servers. FYI this script was executed via my CMS (central management server). If you have CMS  in your environment then great, if you do … Continue reading Change SMTP server name

Long Running Query in SQL server

While SQL server slow performance  the most common source are long running process, which can distress our entire instances performance  like others process, CPU, memory, disk and etc. In order to demonstrate how to capture a long running query for your database instance, with appropriate execution plan for the related process. This query will give you most expensive processes list with all possible details. SELECT r.session_id , r.start_time ,  TotalElapsedTime_ms = r.total_elapsed_time ,      r.[status] ,   r.command ,   DatabaseName = DB_Name(r.database_id) ,   r.wait_type ,      r.last_wait_type ,   r.wait_resource ,    r.cpu_time ,    r.reads ,      r.writes ,   … Continue reading Long Running Query in SQL server

SQL Server Database Restore History

  While working with databases, one of the most repeated task are database restore and refresh from production to test or dev environment. I’ve had situations where after a refresh (restore databases), our client would assume the refresh was not done or the refresh was not completed. They were not seeing the data they were expecting. Now how can you prove that you did the refresh from the most recent backup successfully? The below script will give you all restore details like restore date, which backup was restored, what sources location, and etc. use Master SELECT [rs].[destination_database_name], [rs].[restore_date], [bs].[backup_start_date], [bs].[backup_finish_date], … Continue reading SQL Server Database Restore History

Why am I getting a Backup alert

We use SQL server central management server (Master server) to manage all SQL instance centrally, in order to apply policy, update, query entire environment form one center location, other than login each server. We create all job in the master server and push to all target server, but job run individual server in different schedule. So we create one additional job (schedule job) individual server to call actual job. Many time we get backup alert through backup job, which check all backup jobs status if any backup job is not run based on schedule it throw alert. But when I … Continue reading Why am I getting a Backup alert

SQL Server Password Policy

SQL Server supports two authentication modes, Windows authentication mode Mixed mode. Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials. Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server. The windows password policy check against password minimum length, password history (password can’t be used if … Continue reading SQL Server Password Policy

~First week at a new Environment~

    As a DBA, your first week at a new environment can be testing to say the least. Worst case scenario is that you arrive in an environment where there is no SOP (Standard Operating Procedure), best practice is unheard of, the databases are running themselves (maybe in fantasy land), and they have no idea why you were hired.  If all the mentioned are true, then all I can say is RUN! We are DBAs, not Magicians, although that depends on whom you ask. The first couple weeks are challenging if you are not prepared, but if you come … Continue reading ~First week at a new Environment~