Important Question & Answer for SqlServer

Questionsql-server-Important-questions/

Top Answers to SQL Server INV Questions

1. Compare SQL Server & Oracle

Criteria	SQL Server	Oracle
Platform Supported	Windows Server	All Unix & Linux
Replication done	Only databases	Instances & databases
Ease of use	Good	Average

2. How can SQL Server instances be hidden?

To hide a SQL Server instance, we need to make a change in SQL Server Configuration Manager. To do this launch SQL Server Configuration Manager and do the following: select the instance of SQL Server, right click and select Properties. After selecting properties you will just set Hide Instance to “Yes” and click OK or Apply. After the change is made, you need to restart the instance of SQL Server to not expose the name of the instance.

3. Can we hot add CPU to SQL server?

Yes. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Starting with SQL Server 2008, SQL Server supports hot add CPU.
• Requires hardware that supports hot add CPU.
• Requires the 64-bit edition of Windows Server 2008 Datacenter or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems operating system.
• Requires SQL Server Enterprise.
• SQL Server cannot be configured to use soft NUMA
Once the CPU is added just run RECONFIGURE then sql server recognizes the newly added CPU.

4. How can we check whether the port number is connecting or not on a Server DBA?

TELNET PORTNUMBER
TELNET PAXT3DEVSQL24 1433
TELNET PAXT3DEVSQL24 1434
Common Ports:
MSSQL Server: 1433
HTTP TCP 80
HTTPS TCP 443

5. How can you start SQL Server in different modes?

Single User Mode (-m): sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P *******
DAC (-A): sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P *******
Emergency: ALTER DATABASE test_db SET EMERGENCY

6. Suppose, there is a trigger defined for INSERT operations on a table in an OLTP system. The trigger is written to instantiate a COM object and passes the newly inserted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

• Instantiating COM objects is a time consuming process and since it is done within a trigger, it impedes the data insertion process. Same is the case with sending emails from triggers. This rundown can be better implemented by logging all the necessary data into a separate table, and have a job which checks this table and does the needful.

7. What different steps will a SQL Server Developer takes to secure SQL Server?

• Preferring NT authentication
•  Using server, database and application roles to control access to the data
•  Securing the physical database files using NTFS permissions
•  Using an unusable SA password, restricting physical access to the SQL Server
• Renaming the Administrator account on the SQL Server computer
• Disabling the Guest account, enabling auditing using multiprotocol encryption,
•  Setting up SSL, setting up firewalls, isolating SQL Server from the web server

8. What action plan is prefered if SQL Server is not responding?

Connect using DAC via CMD or SSMS

• Connect via CMD
• SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster
•  Once you connect to the master database run the diagnostic quires to find the problem
• Correct the issue and restart the server

9. Which are third-party tools used in SQL Server and why would you use them?

Some of the third-party tools are:

• SQL CHECK – Idera – Monitoring server activities and memory levels
• SQL DOC 2 – RedGate – Documenting the databases
• SQL Backup 5 – RedGate – Automating the Backup Process
•  SQL Prompt – RedGate – Provides IntelliSense for SQL SERVER 2005/2000,
•  Lite Speed 5.0 – Quest Soft – Backup and Restore
  Benefits of using third-party tools:
•  Faster backups and restores
• Flexible backup and recovery options
•  Secure backups with encryption
•  Enterprise view of your backup and recovery environment
• Easily identify optimal backup settings
• Visibility into the transaction log and transaction log backups
•  Timeline view of backup history and schedules
•  Recover individual database objects
• Encapsulate a complete database restore into a single file to speed up restore time
•  When we need to improve upon the functionality that SQL Server offers natively
• Save time, better information or notification

10. What are Hotfixes and Patches?

Hotfixs are software patches that were applied to live systems (the ones still running.) A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product (i.e. a software bug).
In Microsoft SQL SERVER context, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes.
Ex: If a select query returns duplicate rows with aggregations, the result may be wrong….

Download SQL Interview Questions asked by top MNCs in 2018

GET PDF

11. Why Shrink file/ Shrink DB/ Auto Shrink is not preferable?

In the SHRINKFILE command, SQL Server isn’t careful about where it puts the pages that are moved from end of the file to open pages towards the beginning of the file.

• The data becomes fragmented – potentially up to 100% fragmentation, and hence, it is a performance killer for your database;
• The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)
  Recommendations:
• Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation
•  Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased.

12. What key provides the strongest encryption in SQL Server DBA?

•  AES (256 bit)
•  If we choose longer key, then encryption will be better, so choose longer keys for more encryption. However there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES.
•  AES: Advanced Encryption Standard
•  DES: Data Encryption Standard

13. What port do you need to open on your server firewall to enable named pipes connections?

Port 445. Named pipes communicate across TCP port 445.

14. We have 300 SSIS packages need to be deployed to production, how can we make it easier? What are the easy and short ways to deploy all SSIS packages at once?

• We store this data as XML based files, not in the MSDB database.
• With the configuration files, you can point the packages from prod to dev (and vice versa) in just few seconds.
• The packages and config files are stored in a directory of your choice.
• Resources permitting create a standalone SSIS server away from the primary SQL Server

15. How to decide the active and passive nodes?

Open Cluster Administrator checks the SQL Server group where you can see the current owner. So current owner is the active node and other nodes are passive

16. What is the common trace flags used with SQL Server?

Deadlock Information: 1204, 1205, 1222
Network Database files: 1807
Log Record for Connections: 4013
Skip Startup Stored Procedures: 4022
Disable Locking Hints: 8755
Forces uniform extent allocations instead of mixed page allocations 1118 – (SQL 2005 and 2008) To reduces TempDB contention.

17. Can we take backup for Resource DB?

No way. The only way if you want to get a backup by using windows backup for option resource mdf and ldf files.

18. Does upgrade advisor analyze the remote instances?

Upgrade Advisor can analyze remote instances of SQL Server, except for SQL Server Reporting Services. To analyze Reporting Services, Upgrade Advisor must be installed and executed on the report server.

19. How to upgrade SQL Server 2000 to SQL Server 2008?

The safest approach is the side-by-side upgrade. You can do this either by using backup and restore approach or detach/attach of the database files. I’d suggest using the backup & restore as the safer approach. The steps are:

•  Run Upgrade Analysis tool from Microsoft. Address any issues raised there, first.
• Identify DTS packages. These must be migrated by hand, unless you buy Pragmatic Works excellent software. Rebuild the DTS packages as SSIS.
•  Script out all SQL Agent jobs.
• Script out all security
•  Backup the systems and validate the backups (preferably by restoring them to another system)
•  Run the security script on the new system
• Run the restore on the new system.
•  Validate the databases by running DBCC
• Manually update all statistics
• Run the SQL Agent script

20. Can you detach SQL Server 2005 database and attach it to a SQL Server 2008 server?

Yes. SQL Server 2005 databases are compatible with SQL Server 2008. Attaching a SQL Server 2005 database to SQL Server 2008 automatically upgrades the SQL Server 2005 database to the latter and the database is then no longer usable by the SQL Server 2005 installation.

21. Can you detach a SQL Server 2008 database and attach it to a SQL Server 2005 server?

No. The only way to move a SQL Server 2008 database to a SQL Server 2005 server is by transferring the data using a method such as Data Transformation Services (Import/Export), SSIS, bcp, or use of a query between linked servers.

22. When you upgrade a SQL Server, the upgrade wizard seems to stop responding and fails. Why?

If applications or services have open ODBC connections to the SQL Server 2005 during the conversion process, they may not allow the SQL Server to shut down completely. The conversion process will not proceed to the next step if it does not receive verification that the SQL Server has been completely stopped.

23. How to rollback the upgrade?

If the legacy SQL Server instance is replaced by a new SQL Server 2008 instance, rolling back an in-place upgrade can be complex and time-consuming, whereas in a side-by-side upgrade the legacy instance remains available if a rollback is needed.

24. How to speed up the DBCC check db execution process?

The below command enforces an exclusive lock on database, which makes the process faster
DBCC CHECKDB (‘TestDB’) WITHNO_INFOMGS, TABLOCK

25. What is Phycial_Only option in DBCC CHECKDB?

This command limits checking the integrity of the physical structure of the page and record headers and can also detect torn pages, checksum failures, and common hardware failures.Using PHYSICAL_ONLY option may cause shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.DBCC CHECKDB (‘TestDB’) WITH NO_INFOMSGS, PHYSICAL_ONLY

26. How to check data purity using DBCC CHECKDB?

Below command causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range.DBCC CHECKDB (‘TestDB’) WITH NO_INFOMSGS, DATA_PURITYIn this command, DBCC CHECKDB detects columns with date and time values that are larger or less than the acceptable range for the datetime data type. It also limits the checking of integrity of the physical structure of the page and record.

Learn SQL from Experts! Enrol Today

27.How long are locks held/retained within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation and assuming row-level locking?

Within either of these isolation levels, locks are held for the duration of the transaction unlike within the READ_COMMITTED isolation level.

28. What 2 isolation levels support optimistic/row-versioned-based concurrency control?

• First is the READ COMMITTED isolation level. This is the only level that supports both a pessimistic (locking-based) and optimistic (version-based) concurrency control model.
• Second is SNAPSHOT isolation level that supports only an optimistic concurrency control model.

29. What database options must be set to allow the use of optimistic models?

READ_COMMITTED_SNAPSHOT option for the read committed optimistic model. ALLOW_SNAPSHOT_ISOLATION option for the snapshot isolation level.

30. Explain the purpose of INTENT locks?

The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource (lower in the lock hierarchy.) Intent locks are named because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level. Intent locks serve two purposes:

• To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
•  To improve the efficiency of the Database Engine in detecting lock conflicts at higher level of granularity.

31. How to read the graphical execution plan?

The Graphical Execution Plan should be read from Right to Left.

• Check the Graphical execution plan of a stored procedure / Query
•  Table Scan – Index is missing
• Index Scan – Proper indexes are not using
• BookMark Lookup – Limit the number of columns in the select list
•  Filter – Remove any functions from where clause; may require additional indexes
• Sort – Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?
•  DataFlow Arrow – High density: Sometimes you find few rows as outcome but the arrow line density indicates the query/proc processing huge number of rows
•  Cost – Can easily find out which table / operation taking much time
• From the execution plan, we can find out the bottlenecks and give the possible solution to avoid latency

32. What are the permissions required to view execution plans?

Either the user must be mapped to sysadmin, db_owner, db_creator or they will be granted the permission, “Show Plan”.GRANT SHOWPLAN TO [username]This blog will help you get a better understanding of SQL Optimization Techniques!