Port of SQL Server Script

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

Slow proformance

First time Server is Running Slow:

Check if application is running slow

Check if there was any change in application site

Check if there is anything process is blocked or deadlocked (SP_who2)

Check with SP_Lock

Check if there was any change in windows

Check the memory CPU uses special consideration to be given if the machine is running under VM.

First time in SQL server:  Check backup job is set and upon successfully running. what are the databases in the server

Server is down:  RDP, Configuration Manager, Cluster manager, application event log, sql error log monitoring tool

Urgent Emergency: Get trusted consultant support, call Microsoft  call local partners

Non Urgency: Post question in forums like MSDN/ TechNett without any conf data, #sqlhelp twitter,

Query/ Storeporcedte Tuning  :  Check execution plan to see if it is doing any table scan, bookmark lookup or RID, review query, sometime removing the index, creating the covering index will help to have seek  operation.

Store procedure:
Include set no count on statement
Use schema name with object name
Use if exists instead of select *
Check if its using cursor try to replace it with set based operation or temp table
Keep transaction short as possible
Optimize query and fine tune index
Minimize joins and maximize where clauses

DBCC check DB, DBCC Check Table, DBCC check Counting, DBCC shrink File, DBCC Input buffer .

SQL Server is not starting: SQL Server error log, Windows server event log, take help with windows team, check if the server was patched or any configuration has been changed.

Slow Performance trouble shoot:

Gather information with help of SQL profiler, System store procedures like sp_who2 etc, Sql Server error Log, windows Server event log, Performance related DMVs third party monitoring tool DMV performance tool, Windows level performance like CPU high Momery use cycle etc.

Analyze and determine the problem,  Testing with dev or test , Implement in production with change process.

DBA  STORY

Redgate and SQL Server 2008 SP 2 CU 3 not compatible issue

Day to Day activities of a SQL Server DBA

1.       Take care of failed jobs like backup or weekly maintenance and fix them

2.       Monitor disk space issues

3.       Shrinking Log files to free the disc space

4.       Monitor and fix replication issues/ Log shipping  issues

5.       Create users and grant access as per approval

6.       Monitor SQL severe alerts produced by monitoring tool

7.       Deploy the approved change requests tasked

8.       Refresh QA or dev servers as per the need basis

9.       Create change requests required for DBA team such as to stop server for patching or failover etc

10.   Stop-start SQL agent jobs as per the app team need and approval

11.   Create DBS and Db objects  as per the request

12.   Solve day to day production issues like blocking, deadlocking, Server not responding, job failing

13.   Take care alerts produced by third party monitoring tools

14.   Clear the production bottlenecks by running pre-approved scripts in server to update data manually when needed.

15.   Removing logins related to resigned employees

16.   Patching Servers with service packs and cumulative updates and hotfix as needed.

Normal Forms

Normal Forms:

1st Normal Form: Every row is unique. No duplicate values are inserted. Similar data are stored together.

2nd Normal Form:  All non-key columns are depend on the entire key

3rd Normal Form: No columns are depended on Non-key column

SQL Database Migration Key Points

Migration related:

·         Make sure application will be supported with the upgraded or migrated version of SQL server

·         Run upgrade advisor to make sure there is no issues  migrating or upgrade.

·         Verify with the application team that the service pack is also compatible with the application

·         Take cutover full backup for all databases along with the system database.

·         Take scripts of replication, logins, agent job, linked server, Triggers, SQL operators, mail profiles, alerts, maintenance plans etc

·         Make sure enough space is available in target server to accommodate backup file and data file.

Methods of migration:

·         Backup Restore

·         Detach attach

·         Manual schema build and data import / Export

·         Transaction log shipping for vldbs

·         Copy Database wizard

Be careful always start migration with DEV QA and finally with prod making sure that there is no issue.

Migration from SQL Server 2008 to SQL Server 2014 Checklist

Source SQL ServerChecklist

Action Item	Yes/No, N/A
1.     Identify databases you would like to migrate
2.     Backup all user databases
3.     Script out all the existing login
4.     Script out all the server Roles if applicable
5.     Script out all the Audit and Audit Specifications if applicable
6.     Script out backup devices if Applicable
7.     Script out server level triggers if applicable
8.     Script out Replication along with Configuration if Applicable
9.     Script out Mirroring if Applicable
10.            Script out Data Collection if applicable
11.            Script out Resource Governor’s objects if applicable
12.            Script out Linked Server if Applicable
13.            Script out Log shipping if Applicable
14.            Script  out all SQL Server Agent Jobs
15.            Script out all DB Mail objects such as Profile and its settings
16.            Script out All Proxy accounts and credentials if Applicable
17.            Script out all Operators if Applicable
18.            Script out all alerts if applicable
19.            Save SQL Server, Server configuration in a file
20.            Data Encryption Keys

Destination SQL Server Checklist

Action Item	Yes/No, N/A
1.     Required SQL Server is installed
2.     DBA SQL Server Check list is completed
3.     Enough Space for storing Backup and source scripts
4.     Applications compatibility is signed off

In place VS Side by Side Migration :

Action Item	In Place Upgrade	Parallel or side by side upgrade
Upgrade Efforts	·         Minimum ·         Upgrade is Automated ·         No data migration needed	·         Efforts needs to be planned ·         Manual migration ·         Data migration required
Downtime	·         Requires downtime	·         Minimum or no downtime required ·         Production will be online while migration is taking place
Configuration Changes	·         Configuration changes are Automated by the system ·         Application configuration is not needed since it would use same connection information	·         All configurations needs to be preserved and migrated over to new SQL Server instance ·         Applications needs to change configuration to connect to new SQL Server instance ·         Requires manual check of all configurations (system and user)
Data Transfer	·         No Data Transfer required ·         All Server integration changes remains intact and gets upgraded by the system (Replication, Mirroring, Linked Server and SQL Server Agent Jobs etc.	·         Data Transfer required and can be planned in staging manner ·         All server integrations needs to be created over to new SQL Server Instance
Server Name and IP	·         No changes in Server and SQL server instance ·         Application can continue using same server Name and IP address	·         Server and IP address will change ·         All application needs to change their connection to connect to new server with new IP address
Testing	·         Testing is relatively faster as same testing script and be used once upgrade is completed	·         Thorough testing required as there are chances to a configuration mismatch ·         Testing needs to be planned and new testing script required since Server Instance has changed for testing script
Rollback	·         Rollback usually is rebuilding the whole system ·         Requires backup of all configuration, databases including system databases	·         Rollback is very easy as production system remains online during migration, if testing fails, it can be remediated with no downtime required and all application can connect to previous Online production system
SQL Versioning and Architecture	·         Versioning upgrade would be possible with same architecture (32 bit to 32 bit), 32bit to 64 bit is not supported	·         Since it would be new install, any version with any architecture can be installed and tested

SQL DBA Basic Interview question answers

SQL server Corruption error No:  IO Errors 823,824,825 Most of the time because of disk IO system. Read analyze message carefully may need to run dbcc checkdb

Repair Option: Only In single user mode, System table can't be  repaired. Take backup before doing repair.while repair with allow data loss.

Primary Key: Unique Identifier of a row of a database table. Its unique and not allows Null. It prevents duplicate.

Foreign Key:  Relationship Between two or more  tables  of a database. Basically have one parent multiple child relationship. Used to ensure referral database Integrity.

Unique Key: Enforces the uniqueness of the value in the set of column. The value is unique max one null value accepted.

Having and where clause: Most likely same, having is used with aggregate function group by clause and with select statement.

OLTP: Online transaction processing relational database management system with codd rule of data normalization.

Delete:  Can be rolled back, Only row level lock Truncate: No rollback, table level lock, can't do if has FK can't truncate the replicated table.

2nd last 3rd last: Can  calculate with correlated query, Sub Query : Can't have order by clause.

Stored Procedure: Group of sql statement created and stored earlier  can be used to query insert update delete  data accepts input parameter and can be used with multiple server in the network. can be reused  optimizing SP:  Maximum use of where, select only required fields, minimize  join, Replace cursor with temp table/Set based operation.

View:  Like a stored sql statement as a virtual table used to retrieve the data.

Cursor:  Database object used by application in the procedural basis to manipulate the data on  row by row basis.

Identity field:  Is a column that automatically generates numeric values as it has been set.

BCP: Bulk copy program. SQL CMD : is a command line utility.

Statistics: Intelligence system of sql server sql server can estimate the amount of data to be extracted  uses best execution plan based upon the stats.

Execution Plan : A road map to be used by sql server to get the data.

RPO: Recovery point objective, RTO: Recovery time objective

Enterprise Edition : Transaction Log shipping, Online indexing, If 2008R2 Backup compression.

Difficulties with SQL 2000: No DMV, Clustering is pretty hard. recent application doesn't support. No SSMS, Replication is also hard.

TEMPDB: Used to store temp db temp table as sql need. used to shorting index rebuild action uncommitted trans for rollback. Shorting the query results if needed.

 Restore verify only option: To verify the backup

Backup best practice: Store backup in a same network, Don't store backup and data file in the same disk. If doing backup in a same SAN, make sure the backup timing are set differentely.

DBCC Index defrag: SQL 200 Feature depreciated. Alter Index: Being used after sql 2005

Feature to encrypt all data in database: Transparent data encryption

DMV: Built in views which give you in site into configuration and performance of sql

Priority turn on : Server might stop responding while its busy (Depreciated)

Problem shrinking DB File: Causes Fragmentation

Can see with performance counters not with DMV: Counters from operation system prospective like disk performance.

Raid Zero: presents multiple physical drive to single logical drive. Doesn't provide any redundancy  dont use this option

Windows power management setting: high performance (otherwise hurts the performance)

Network setting to transfer the large files: Jumbo Frame

Always on advantage: Multiple secondary, No shared storage, secondary readable

Default Isolation level : Read committed, No Lock Hint: Read Uncommitted read data from dirty page.

Recompile hint: Not to cache the execution plan, plan can't be reused

Hint  to force a query to use specific index risk: Query fails if the index dropped or renamed or changed

Nvarchar : Unicode needs more space Varchar: just regular

How to know additional index in needed:  We can check with missing index DMV

Favorite scripts : SP_who2, Backup, Security, DMV to find wait stats, long running queries etc

Weekly Maintenance: Check Database Integrity, Rebuild Index, Update Statistics, clean up history etc

Connection Pooling:

Ado.net uses an optimization technique called connection pooling to minimize the cost of opening and closing the connection. Once you close the connection it puts such connection into the connection pool rather to close it  and later if similar type of request comes it uses that existing connection which reduces resource cost to opening and closing the connection.

Types of Lock:

Shared: used for the operation like select which doesn't change the data

Update: Used on such operation that can update the data

Exclusive:  Used for data modification operation such as update insert delete etc. Its maximum stage of lock and doesn't release until the operation is completed.

Schema lock:  Comes into the picture when there is any operation causing the schema change.

To check restart log in SQL Server  check NETBIOS in error log

 To find database size and free space - Sp_ Spaceused, Dashboard report, properties> task > Shrink

Maximum row size in a SQL Server table is 8086 Bytes

Maximum no of column in a table is 1024 in a table

Transaction Log Shipping supports enterprise edition only

 Linked Server: any OLEDB servers can be added as a linked server and data can be queried as local server.

Execution Plan - A road Map to show the data retrieval process to be used by sql server

Fill Factor:

The percentage of space to be filled by index in leaf level 8 kb pages is decided by fill factor. Its normally 100% and the fill factor for identity column should always be 100%.

We can't do any insert transaction if clustered index of that table is disabled.

SQL Express edition :  Its free edition and can have maximum of 10 GB data. It doesn’t have SQL agent windows task scheduler can be used as an alternative.

To make backup run faster:  Compress the backup, Faster  storage, More Network, Differencate timing if multiple server backups are running in a same SAN.

Two features of enterprise edition: Online index maintenance and supports transaction log shipping

Clustering  good for HA  its instance level, Mirroring  database level can't have more than one mirrored db its for DR.

Tools to check storage speed: SQL IO, Crystal Disk

Things to be considered capping SQL Memory:  No of instance running in the box, application other than SQL running in the machine.

Isolation Levels in SQL Server:

·         Read Committed: Default Isolation level only committed transactions can be read.

·         Read uncommitted: Can be enabled by using no lock hint, data can be read from dirty page.

·         Readable Read:

·         Snapshot: Transaction 2 will be reading the data in the stage that before transaction 1 begains.

·         Serialize able:

DMV: we can use select statement to get the data. It’s a system view which insight us to performance and configuration.

DMF: Can only be used with input parameter.

Clustered Index: an order of the data in the table itself is a clustered index PK creates clustered index

Non clustered index: Secondary structure to help sql server while retrieving data.  It can be more than  one up to 999. It stays separately from the table only pointers are available in the table.  UK creates non clustered index.

Recompile Hint: Instruct sql server not to use the execution plan so the plan can't be reused.

Hint to force a query to use specific index: Query fails if if something happens to that index like index dropped or disabled

Union:  Selects distinct values only from two or more table.

Union all: selects all the values from the tables along with the duplicate values is applicable.

Bookmark lookup, Rid lookup, B Tree, Index Scan, Index Seek In SQL Server

Bookmark Lookup and Rid Lookup:

When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query. If the query requests data from columns not present in the non-clustered index, SQL Server must go back to the data pages to get the data in those columns. Even if the table has a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data.

In the above scenario, if table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called RID lookup. This operation is very expensive. To optimize any query containing bookmark lookup or RID lookup, it should be removed from the execution plan to improve performance.

Index Scan and Index Seek:

Before we go over the concept of scan and seek we need to understand what SQL Server does before applying any kind of index on query. When any query is ran SQL Server has to determine that if any particular index can be applied on that particular query or not.

SQL Server uses search predicates to make decision right before applying indexes to any given query. Let us see the definition of predicates from Book On-Line: Predicate is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.

In you have not understood so far what I am trying to suggest. Let me put this in simple words in following scenario.

Table1 has five columns : Col1,Col2,Col3,Col4,Col5
Index1 on Table1 contains two columns : Col1,Col3
Query1 on Table1 retrieves two columns : Col1,Col5

Now when Query1 is ran on Table1 it will use search predicates Col1,Col5 to figure out if it will use Index1 or not. As Col1,Col5 of Query1are not same as Col1,Col3 or Index1 there are good chances that Query1 will not use Index1. This scenario will end up in table scan. If Query1 would have used Index1 it would have resulted in table seek.

Index Scan happens when index definition can not point close on single row to satisfy search predicates. In this case SQL Server has to scan multiple pages to find range of rows which satisfy search predicates. In case of Index Seek SQL Server finds single row matchign search predicates using index definition.

B- Tree is a structure to organize index information in sql server which has root node, connecting node and leaf node, actual data are stored under leaf node.