The Query Optimizer initially locks the required Rows for DML operations / Retrieval operations. This also get relevant Pages and Completely table to be 'INTENT' Locked. If more than 50% of rows are specifically locked then this automatically gets the complete PAGE or TABLE to be locked. Lock Escalation mechanism can be controlled by using Locking Hints.
Policy Based Management in SQL SERVER 2012 Administration allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.
I rate the following five tasks as the key responsibilities of a DBA.
SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the queries and procedures. Based on performance, it identifies the slow executing queries. Capture any problems by capturing the events on production environment so that they can be solved.
Database Mirroring is to be configured with TCP Protocol and ensure that data over each endpoint is encrypted. Better to make use of TDE for more security.
Fill factor specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. Fill factor must be an integer value from 1 to 10@The default is @I prefer to keep my servers default fill factor as 90.
An unique key cant not be referenced as foreign key. And it may allow on null.
We can make use of SQL Profiler , SQL Server Log or use xp_readerrorlog extended Stored Procedure to retrieve the error log information.
TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.
Yes and No. This is tricky question. If you are using repair option with CHECKDB then you have to have the DB in single user mode. Following is the method to have your DB in a single user mode.
go sp_dboption dbname, single, true
Following is the error which you get when you run the DBCC CHECKDB with repair option wo having the DB in single user mode. The same is true for DBCC CHECKDB also
Types of backups available in SQL Server:
BACKUP LOG TestDB WITH TRUNCATE_ONLY is gone. SQL server does not allow you to truncate the log now otherwise whole purpose of a DB is defeated. You have to make sure whether you need log or not. If you do not need log then have the recovery model simple instead of full. If you do not want the log to be accumulated in some particular bulk logging then change the recovery model BULK LOGGED for that duration and take one TLog Backup just before and after this change. I shall discuss this later in my later blog. BACKUP LOG command backs up the t-log and frees the space in the log file.
SQL services will be in manual and stopped. Cluster service will be in automatic and started mode on both the nodes.
Yes resources can be moved after pausing the node. But we can't move them back till the node is paused.
A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.
understand that whole table would be locked for the processing thenn this is better to use TABLOCK hint and get complete table blocked. This is a nice way to avoid the wastage of sql server DB engine processing for lock escalation. Somewhere you may also need to use TABLOCKX when you want an exclusive lock on the table in the query.
Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.
Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.
In SQL SERVER 2008 R2, Data Compression comes in two types viz., Row Compression where It minimizes the metadata (column information, length, offsets, etc.) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar. Page compression uses the Row compression technique internally and also applies prefix level compression.For every column in a page, duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which reside after page headers. A reference number is assigned to these prefixes and that reference number is replaced wherever those prefixes are being used.
This is a very generic question. I would like to describe my generic database testing method as well as stored procedure testing methods.
Table Column data type and data value validation.
Index implementation and performance improvement.
Constraints and Rules should be validated for data integrity.
Application field length and type should match the corresponding database field.
Database objects like stored procedures, triggers, functions should be tested using different kinds of input values and checking the expected output variables.
Testing Stored Procedures:
Understand the requirements in terms of Business Logic.
Check if the code follows all the coding standards.
Compare the fields’ requirements of application to the fields retrieved by a stored procedure. They must match.
Repeatedly run the stored procedures several times with different input parameters and then compare the output with the expected results.
Pass invalid input parameters and see if a stored procedure has good error handling.
There are many ways but I prefer following method. Take a scenario when you want to find the error log when the DB was put in a single user mode.
CREATE TABLE #Errorlog (Logdate Datetime, Processinfo
INSERT INTO #Errorlog
SELECT * FROM #Errorlog
WHERE Text Like ‘%SINGLE%USER%’
Server’s configurations and logins.
Plan Freezing is a new feature I never thought of. I find it very interesting! It is included in SQL Server 2008 CTP@SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans. This empowers organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.
SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later.
You cannot restore the database without having a full database backup. However, if you have the copy of all the data files (.mdf and .ndf) and logs (.ldf) when database was in working condition (or your desired state) it is possible to attach the database using sp_attach_db.
Inner Join, Outer (Left Outer & Right Outer) Joins and Cross join.
Select * from sys.dm_db_missing_index_details
The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.
When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.
They get stored in System Catalog Views sys.server_principals and sys.sql_logins.
Definition of sys objects, which logically shows into all database and DMVs.
Well, your question is very difficult to answer without looking at the code, application and physical server. In such situations, there are a few things that must be paid attention to right away.
Check Indexes on Tables and Create Indexes if necessary Make sure SQL Server has priority over other operating system processes in SQL Server settings.
Update statistics on the database tables.
Blocking occurs when a process has acquired lock on a set of rows, and another process is trying to acquire a lock on the same set of rows. In such a case, the other process has to wait until the first process finishes its job and releases the lock on the above said rows.
It’s stored into root folder SQL server, LOG folder.
Templates of new database objects, like tables and column.
Row versions, cursor, temp objects.
Dynamic Management Views (DMV) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
create clustered index index_name on empmst(card)
Secondary server. This question is basically asked to find out whether you have a hands on work on log shipping or not.
Select @@version Or select serverproperty (‘productlevel’)
Left Outer join Retrieves the all records from LEFT table and matching from the RIGHT table, and null values where is no match. Right Outer Join just opposite.
You need to add only FAILOVER PARTNER information in your front end code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.
Use TRUNCATE_ONLY option for SQL Server 2005 systems while performing Backup. This option is not available in SQL Sever 2008 and R@Instead, we use ON_TRUNCATE option available in the Backup statement.
Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.
Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using
SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0
Deadlock: When something happens as follows: Error 1205 is reported by SQL Server for deadlock.
Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.
SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]
In SQL Server 2000, prior to dropping the user who owns database objects, all the objects belonging to that user either need to be dropped or their owner has to be changed. Every time a user is dropped or modified, system admin has to undergo this inconvenient process.
In SQL Server 2005 and the later versions, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects. Multiple users can be assigned to a single schema, and they all can automatically receive the same permissions and credentials as the schema to which they are assigned. Because of the same reason in SQL Server 2005 and the later versions – when a user is dropped from database – there is no negative effect on the database itself.
Steps to take to improve performance of queries:
Database isolation comes into play when we need to isolate the database and protect it from other things on the network. This protection is achieved using locks. The type of lock and the level of isolation level needed is referred as isolation level in SQL Server.
Types of isolation levels:
READ COMMITTED: Shared locks are held while any data is being read.
READ UNCOMMITTED: Specifies isolation level 0 locking. There are thus no shared locks or exclusive locks. Lease restrictive of all the isolation levels.
REPEATABLE READ: Locks are applied on all data being used by a query. However, new phantom rows can be inserted into the data set by another user and are included in later reads within the current transaction.
SERIALIZABLE: Issues a range lock on data set, preventing other users to update or insert data into dataset until the transaction is complete.
Scheduled jobs, Backup/Restore and DTA information.