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.
No comments:
Post a Comment