SQL Version

DBCC commands

DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency.

They are categorized as:

Maintenance: Maintenance tasks on a database, index, or filegroup.

Miscellaneous: Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

Informational: Tasks that gather and display various types of information.

Validation: Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

Uses Of DBCC:

DBCC give details in form of statistics about the SQL Server. They can be used for Maintenance of database, index, or filegroup. DBCC Commands can be used to perform validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

They can perform miscellaneous tasks such as enabling trace flags or removing a DLL from memory. DBCC INDEXDEFRAG, DBCC ERRORLOG, DBCC CACHESTATS are one of the few DBCC commands

DBCC CHECKCATALOG
Used to perform consistency check for a table/catalog for a particular database.

DBCC CHECKCONSTRAINTS
Used to check integrity of a particular constraint or all constraints on a particular table for a database.

DBCC CHECKALLOC
Used to check page usage and allocation of a database.

DBCC CHECKTABLE(tablename)
Used to verify data page integrity, page offsets, data page links, page pointers, index pointers, index sort order for a specified table.

5. DBCC SQLPERF(logspace)

Displays T-log size and space used % for all databases.

6. DBCC SHOWFILESTATS

Display Extent information for a database.

7. DBCC CHECKIDENT(tablename)

Used to check identity information or to return current identity value for a particular table.

8. DBCC TRACEOFF

Used to disable tracing

9. DBCC TRACEON

Used to enable tracing.

10. DBCC TRACESTATUS

Displays the trace status.

11. DBCC USEROPTIONS

Displays the active SET options for a particular database

12. DBCC INPUTBUFFER(spid)

Used to get the last statement sent by session to SQL server.

13. DBCC SHRINKDATABASE(dbname)

Used to shrink data and log files for a particular database. It release the free space to OS.

14. DBCC SHRINKFILE(file_id)

Used to shrink individual database files.

15. DBCC LOGINFO

Displays virtual log file information contained in a T-log file.

16. DBCC HELP(<dbcc command>)

Displays the syntax of a DBCC command. To know syntax of DBCC SQLPERF, execute

17. DBCC MEMORYSTATUS

Displays SQL Server memory allocation status.

18. DBCC Cleantable

Used to reclaim space from dropped variable length columns in tables/indexed views

19. DBCC Opentran

Displays information about oldest active transaction and oldest distributed and non-distributed replication transaction

Important questions :- SQL Server Services

SQL Server Services

1) List out various services which are important for SQL Server and its components functionality?

·         SQL Server Service

·         SQL Server Agent service

·         SQL Server Analysis Service

·         SQL Server Browser service

·         SQL Server Integration Service

·         SQL Server Reporting Service

·         SQL Server VSS Writer service

·         Distributed Transaction Coordinator

·         SQL Server Active Directory Helper

·         SQL Full-Text Filter Daemon Launcher

·         SQL Server Distributed Replay Client service

·         SQL Server Distributed Replay Client service

2) What is SQL Server service and its importance?

SQL Server service is core of SQL Server instance. It runs the Database Engine and executes the client requests related to data processing.  If this service is not running, no users can connect to the any of the database, hence users will not be able to fetch, insert, update or delete the data.

3) What is SQL Server Agent service and its importance?

SQL Server Agent is the primary scheduling engine in SQL Server. This is used to execute scheduled administrative tasks like SSIS Packages, T-SQL Scripts, Batch Files and Subscriptions etc. which are referred to as Jobs. It uses msdb database to store the configuration, processing, and metadata information. Apart from SQL Server Agent related information, msdb database also stores similar information related to Backup, Restore, Log Shipping, SSIS Packages etc.

4) What is SQL Server Analysis service and its importance?

Microsoft SQL Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

5) What is SQL Server Integration service and its importance?

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

6) What is SQL Server Browser?

This service acts as a listener for the incoming requests for Microsoft SQL Server resources. It provides information about the list of installed SQL Server instances on the computer to the client computers/applications. It helps in browsing the list of servers, locating and connecting to the correct server.

This listener service responds to client requests with the names of the installed instances, and the ports or named pipes used by the instance.

7) What is SQL Server Reporting Services?

This service is primarily used by SQL Server Reporting Services (SSRS) for browsing and viewing the reports on Reports Server, through Report Server or Report Manager interface. It is used to manage the shared data sources, reports, shared data sets, report parts, folder, etc. hosted on the Report Server. Reporting services are managed using the Reporting Services Configuration Manager.

8) What is SQL Server VSS Writer?

The SQL Writer Service provides added functionality for backup and restore of SQL Server through the Volume Shadow Copy Service framework. When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.

Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running. It must be running when the Volume Shadow Copy Service (VSS) application requests a backup or restore. To configure the service, use the Microsoft Windows Services applet. The SQL Writer Service installs on all operating systems.

9) Which types of backups are supported by SQL Write Service?

SQL Writer supports:           

·         Full database backup and restore including full-text catalogs

·         Differential backup and restore

·         Restore with move

·         Copy-only backup

·         Auto-recovery of database snapshot

10) Which types of backups are not supported by SQL Write Service?

SQL Writer does not support:

·         Log backups

·         File and filegroup backup

·         Page restore

11) What is Full-Text Search service?

This service is used by the full-text search feature of SQL Server. It helps in starting the filter daemon host process, which manages the full-text indexing, querying, search filtering and word processing as part of the full-text search feature.

12) What is SQL Server Active Directory Helper?

This service enables the integration with the Active Directory. Irrespective of number of instances of SQL Servers installed on a computer, there is always only one instance of SQL Server Active Directory Helper service. This service is automatically started by SQL Server when required and is stopped once the operation is completed. This service is required whenever an SQL Server object needs to be created in the Active Directory to register an instance of SQL Server.

13) What is Distributed Transaction Coordinator?

This service coordinates distributed transactions between two or more database servers. Client applications use this service to work with data from multiple sources in one transaction. There is always only one instance of MSDTC service running on a computer irrespective of how many SQL server instances are installed. This service should be running on each of the servers which handle distributed transactions. This service is not a part of SQL Server installation. This service is installed with Windows OS installation.

14) How to check how many SQL Server instances are installed on a Window Server?

There are multiple ways through which we can check the No. of SQL Server instances which are running on a server like:

·         Check the SQL services for different Instances

·         SQL Server Configuration Manager Start- all programs – Microsoft SQL Server 2008 R2 -> configuration tools –> Microsoft SQL Server configuration Manager

·         List out SQL Services from Net Start command

·         Using Powershell commands

·         Readging the Registry Keys, Regedit-> HKEY_LOCAL_MACHINE–>SOFTWARE ->Microsoft ->Microsoft SQL Server –> InstalledInstance

15) What are the different ways to start and Stop SQL Server services?

There are different ways through which we can start or stop SQL Server services.

·         Go to Services –> Look for SQL server service related to  the Instance

Named Instance: SQL Server(Instance Name)

Default Instance: SQL Server(MSSQLServer)

·         Right Click on the SQL Server instance in management studio and click on restart

·         Go to SQL Server Configuration Mananger (SQLServermanager10.msc) and right click on the services and click restart.

·         Net stop command

·         use “Net START” command to list all the running services

·         Use “Net STOP MSSQL$Instancename”  to stop the SQL Service for a particular instance

16) List out the Service Display name, Service Name and Executable for SQL Serve related services?

Common Name	Service Display Name	Service Name	ExecutableName
Distributed Transaction Coordinator (DTC)	Distributed Transaction Coordinator	MSDTC	msdtc.exe
SQL Server	SQL Server (MSSQLSERVER)	MSSQLSERVER	sqlservr.exe
	SQL Server (NamedInstance)	MSSQL$NamedInstance
SQL Server Active Directory Helper	SQL Active Directory Helper Service	MSSQLServerADHelper100	SQLADHLP.EXE
SQL Server Agent	SQL Server Agent (MSSQLSERVER)	SQLSERVERAGENT	SQLAGENT.EXE
	SQL Server Agent (NamedInstance)	SQLAgent$NamedInstance
SQL Server Analysis Services	SQL Server Analysis Services (MSSQLSERVER)	MSSQLServerOLAPService	msmdsrv.exe
	SQL Server Analysis Services (NamedInstance)	MSOLAP$NamedInstance
SQL Server Browser	SQL Server Browser	SQLBrowser	sqlbrowser.exe
SQL Server Full Text Search	SQL Full-text Filter Daemon Launcher (MSSQLSERVER)	MSSQLFDLauncher	fdlauncher.exe
	SQL Full-text Filter Daemon Launcher (NamedInstance)	MSSQLFDLauncher$NamedInstance
SQL Server Integration Services	SQL Server Integration Services 10.0	MsDtsServer100	MsDtsSrvr.exe
SQL Server Reporting Services	SQL Server Reporting Services (MSSQLSERVER)	ReportServer	ReportingServicesService.exe
	SQL Server Reporting Services (NamedInstance)	ReportServer$NamedInstance
SQL Server VSS Writer	SQL Server VSS Writer	SQLWriter

17) What are the default parameters of SQL Server service start up process and from where these parameters can be changed?

master database data and log file and error log files are the default parameters which are passed to SQL Server service.

·         -dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf;

·         -eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG;

·         -lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

We can add trace flags and other parameters to the startup process from SQL Server Configuration manager.

18) How to start the SQL Server with minimal configuration?

If there are any configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option. This is the startup option -f.  Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

19) How to start SQL Server with single user model?

Under certain circumstances, we may have to start an instance of SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

20) What are trace flags and how can we apply trace flags on a SQL Server instance?

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behaviour.

Ex: Trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.

21) How the trace flags are enabled?

Trace flags are enabled at different levels.

·         Global

·         Session

Instance level

We can use the -T option in the startup configuration for the SQL Server Service to enable trace at instance level.

Session Level

We can use the DBCC TRACEON and DBCC TRACEOFF commands to enable it on a session level.

22) How do I know what Trace Flags are turned on at the moment?

We can use the DBCC TRACESTATUS command

23) Name some of the Important Trace flags and their functionality?

Trace Flag: 1204

This trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in text format. In SQL Server 2008, this trace flag is only available at the Global Level (i.e. applies to the SQL Server instance). In my experience, it is worth turning this trace flag on, only for debugging purposes.

Trace Flag: 1222

Similar to trace flag 1204, this trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in XML format.

Trace Flag: 3226

In an environment where database backup is frequently performed, it is a good idea to turn trace flag 3226 on, to suppress an entry to the SQL Server Error Log for each backup.  This is beneficial as the bigger the log, the harder it is to find other messages.  However, you will need to ensure that none of your scripts or systems rely on the backup entry detail on the SQL Server Error Log.

24) What are the mandatory databases to bring SQL Services up?

master, model, resource db, tempdb location.

26) What is Protocol is used by SQL Server Browser service?

SQL Server Browser service uses UDP protocol.

27) Which Port no. is used by SQL Server Browser service ?  1434

28) What will happen if SQL Server Browser service is stopped?

If the SQL Server Browser service is not running, the following connections do not work:

·         If we have just one instance installed on machine and it is running on default port 1433, then status of SQL Server Browser service does not make any difference in our connection parameters.

·         If there are more than one instances running on the same machine, in that case either we have to start SQL Server Browser service or provide the port number along with IP (or server name) and instance name, to access any other instance than default.

·         If SQL Server Browser service is stopped and IP along with port number is not provided then connection will be refused.

·         If SQL Server instance is configured using dynamic ports then browser service is required to connect to correct port number.

·         Also our named instances will not be published in the list of SQL Server instances on the network (which could be a good thing)

29) What is the high Level SQL Server start up process?

·         The service is authenticated by verifying the credentials provided in the logon account and the service is started.

·         PID is allocated at windows level

·         Authentication mode details are verified i.e either MIXED or WINDOWS

·         Information of the startup parameters is captured i.e mdf location of master database, SQL Server error log location and ldf file location

·         Some memory and CPU settings done at windows level, this is an informational message only

·         Starts the master database

·         model is the next database to start

·         set the port related information

·         Tempdb is recreated each time when we restart sql server

·         Start msdb and other user databases based on dbid

30) What is Distributed Replay?

Distributed Replay is a new functionality of Microsoft SQL Server 2012. It helps you assess the impact of future upgrades (SQL Server, hardware, OS) or SQL Server tunings by replaying a trace captured from a productive SQL Server environment to a new SQL Server test environment.

Important Questions:- Data File and Transaction Log File Architecture

Data File and Transaction Log File Architecture

1) Explain the SQL Server Database Data File structure?

SQL server divides the data file into 8 KB pages and page is the smallest unit of any IO operation. SQL server refer the first page in the data file as page number 0.In all the data file first 9  pages (till page number 8) are in same order as shown below. In the primary data file, the 10th page (Page number 9) will be the boot page which stores the metadata about the database.

2) What is a Data Page?

Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data.

3) What is an Index Page?

Contains information related to Indexes. This holds index records in the upper levels of a clustered index and all levels of non-clustered indexes.

4) What is a Page Header?

Page number 0- the file header (page type 15). It holds the file header information. There will be only one header page for each data file and that reside in the 0th position. The header page store the file level information like file size, minimum size, max size and file growth.

5) What is PFS?

PFS page is the second page (Page Number 1) in the data file followed by file header (Page Number 0). PFS pages are used to track page level allocation. PFS page repeat after every 8088 pages

6) What is a GAM Page?

GAM pages records what extents have been allocated for any use. GAM has bit for every extent. If the bit is 1, the corresponding extent is free, if the bit is 0, the corresponding extent is in use as uniform or mixed extent.

7) How many GAM pages will be there in a 7GB data file?

A GAM page can hold information of around 64000 extents. That is, a GAM page can hold information of (64000X8X8)/1024 = 4000 MB approximately. In short, a data file of size 7 GB will have two GAM pages.

8) What is SGAM page?

SGAM (Shared Global Allocation Map) page (Page type 8). SGAM pages record what extents are currently being used as mixed extent and also have at least one unused page. SGAM has bit for every extent. If the bit is 1, the corresponding extent is used as a mixed extent and has at least one page free to allocate. If the bit is 0, the extent is either not used as a mixed extent or it is mixed extent and with all its pages being used.

9) How many SGAM pages will be there in a 7GB data file?

A SGAM page can hold information of 64000 extents. That is, a SGAM page can hold information of (64000X8X8)/1024 = 4000 MB. In short, a data file of size 7 GB will have two SGAM page.

10) What is a DCM and its significance?

SQL Server uses DCM pages to track extent modified after the last full backup operation. DCM pages track the extents modified after the full backup. DCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last full backup, if the bit is 0, there is no change in the corresponding extent after the last full backup

11) How SQL Server engine will decide which extents need to be added into a differential backup?

A differential backup process scans through DCP page to identify the extents which are modified after the last full backup and add those extents in the Differential backup.

12) What is a BCM and its significance?

This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. If the bit for an extent is 1, the extent has been modified by a bulk logged operation after the last BACKUP LOG statement. If the bit is 0, the extent has not been modified by bulk logged operations.

Although BCM pages appear in all databases, they are only relevant when the database is using the bulk-logged recovery model. In this recovery model, when a BACKUP LOG is performed, the backup process scans the BCMs for extents that have been modified. It then includes those extents in the log backup.

13) What is Boot Page?

There is one special data page that exists only once per database. It is the database boot page. The database boot page is always stored in page 9 of file 1, the first file in the primary file group.

The database boot page contains a broad variety of data about the database itself. Here you can find the internal database version, the compatibility level, the database name and the date of the last log backup.

14) How many types of Allocation units are there?

There are three types of allocation units.

IN_ROW_DATA (default): If a table is relatively simple in desin (meaning record size is less than 8000 and no LOB data types are present), all records are stored in a pages refered as IN_ROW_DATA pages.

ROW_OVERFLOW_DATA: assume that a table is created with record size 12000 bytes having 4 varchar data types of size 4000 bytes. Whenever user inserts a record with size greater than 8000 (page size is 8K), then the excess data is moved to ROW_OVERFLOW_DATA pages. In simple terms, ROW_OVERFLOW_DATA pages will come in  to picture only when the row size exceed page maximum limit.

LOB_DATA: LOB data like text not stored along with data pages. LOB data is stored in pecial pages called LOB_DATA pages. 16 byte pointer in data page will be used to refer LOB_DATA page.

15) What is an IAM page?

IAM stands for Index Allocation Map: To catalog all the pages that belong to a single allocation unit, SQL Server utilizes a special type of pages, the Index Allocation Map or IAM pages. Each allocation unit has at least one IAM Page; depending on the size of the table there might be many. The page type of an IAM page is 10.

16) What is a ROW OFFSET and its importance?

Page header stored system information about the page. After the page header data row is stored serially. Row offset information is stored at the end of the page, after the data rows. Every data row has a row offset. Row offset stores information about how far the row is from the start of the page.

17) What are LOB pages?

SQL Server stores all data in 8192-byte sized blocks called pages. Several types of pages are in use within a typical database. One particularly interesting group is formed by the type-3 pages or Large Object Pages.

LOB_DATA Allocation Units

Most data types in SQL Server take up no more than 8000 bytes of storage. However, there are a few data types, which allow for larger pieces of information to be stored. Examples include the VARCHAR(MAX), VARBINARY(MAX) or XML data types.

Normal data pages that belong to a table are grouped in IN_ROW_DATA allocation units. However, if a value that is larger than 8000 bytes needs to be stored, SQL Server does not attempt to store it in those data pages anymore. It does not even store those values in the same allocation unit. Instead, Large Object data or LOB data is stored in special LOB_DATA allocation units.

18) What is a PAGE SPLIT?

Page splits are performed when there is no space in a data page to accommodate more inserts or updates. In a page spilt, SQL server removes some data from the current data page and moves it to another data page. This is done in order to make space for the new rows.

19) How Page Splits impacts the database performance?

Too many page splits can decrease the performance of the SQL server because of the large number of I/O operations.

Following remedies can be taken to avoid too many page splits:-

·         Rebuild indexes frequently to empty the fill factor space for more data.

·         Increased the Fill factor value after observing the page split behavior

20) How can we monitor or detect Page Splits?

Number of page splits can be observed by using the Performance Monitor and watch the SQLServer:Access Methods:Page Splits/sec counter

21) What is a Fill Factor?

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

22) What is a Logical Read?

Logical read indicates total number of data pages that are found in the data cache and read from Data cache without reading anything from Disk.

23) What is a Physical read?

Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.

24) How can we view the content of a page?

To see the row data stored in that page , we have to use the DBCC PAGE command. The syntax of DBCC PAGE

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]);

Print opt:

·         0 – print just the page header

·         1 – page header plus per-row hex dumps and a dump of the page slot array

·         2 – page header plus whole page hex dump

·         3 – page header plus detailed per-row interpretation

DBCC TRACEON(3604)  GO

 DBCC page('test',1,114,3)

 25) What is a Transaction log file?

A transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.  In the event of disaster, that causes SQL server to shutdown unexpectedly (Power failure/hardware failure), the transaction log is used to bring the database in a consistent state while restarting the server. On restarting the server, database goes through the recovery process.

During this recovery process, the transaction log is used to make sure that all committed transactions are written to respective data file (rolled forward) and rollback the uncommitted transaction.

Logically transaction log is a set of log records. Each record is identified by a log sequence number (LSN). The new log records are always written at the logical end of log file with a LSN which is greater than the previous one

26) What are Virtual Log files? How these are created?

SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.

27) How VLFs are created SQL Server in a Transaction log file?

SQL server decides the size and number of VLF in a log file based on the size of the log file growth as given below.

Growth upto 64 MB        = 4 VLF

From 64 MB to 1 GB       = 8 VLF

Larger than 1 GB             = 16 VLF

28) What is the way to check Virtual Log file details?   DBCC loginfo(‘mydb’)

29) What is Log Sequence No?

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

30) What is a Log Truncation?

Log truncation is essential to keep the log from filling. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. However, before the log can be truncated, a checkpoint operation must occur. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, the inactive portion can be freed by log truncation.

31) How Does Log Truncation occurs?

Log truncation occurs automatically after the following events, except when delayed for some reason:

·         Under the simple recovery model, after a checkpoint.

·         Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup.