SQL Server DBA Interview question and answers Part 10

1. Can we hot add CPU to sql server?

1. Yes! Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning.
2. Starting with SQL Server 2008, SQL Server supports hot add CPU.
3. Requires hardware that supports hot add CPU.
4. Requires the 64-bit edition of Windows Server 2008 Data-center or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems operating system.
5. Requires SQL Server Enterprise.
6. SQL Server cannot be configured to use soft NUMA
7. Once the CPU is added just run RECONFIGURE then sql server recognizes the newly added CPU.

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

TELNET <HOSTNAME> PORTNUMBER

TELNET ADMIN 1433

TELNET ADMIN 1434

Common Ports:

MSSQL Server: 1433

HTTP TCP 80

HTTPS TCP 443

3. What is the port numbers used for SQL Server services?

1. The default SQL Server port is 1433 but only if it’s a default install. Named instances get a random port number.
2. The browser service runs on port UDP 1434.
3. Reporting services is a web service – so it’s port 80, or 443 if it’s SSL enabled.
4. Analysis service is on 2382 but only if it’s a default install. Named instances get a random port number.

4. Can we uninstall/rollback a service packs from SQL Server 2005?

No not possible for SQL Server 2005. To rollback a SP you have to uninstall entire product and reinstall it.

From SQL Server 2008 you can uninstall a SP from Add/Remove programs.

Some people are saying that we can do it by backup and replace the resource db. But I am not sure about it.

5. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.


6. SQL Server is not responding. What is action plan?

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

Find the errors from sql log using

SQLCMD –A –SmyServer –q”Exec xp_readerrorlog” –o”C:\logout.txt”

7. What are the Hotfixes and Patches?

Hotfixs are software patches that were applied to live i.e. still running systems. A hotfixis a single, cumulative package that includes one or more files that are used to address a problem in a software product (i.e. a software bug).

In a 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 returning duplicate rows with aggregations the result may be wrong….

8. Why Shrink file/ Shrink DB/ Auto Shrink is really bad?

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

The data becomes fragmented, potentially up to 100% fragmentation, this 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:

1. Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation.
2. Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased

9. Do you know about Resource Database?

All sys objects are physically stored in resource database and logically available on every database.

Resource database can faster the service packs or upgrades.

10. Really does resource faster the upgrades? Can you justify?

Yes, in earlier versions upgrades requires dropping and recreating system objects now an upgrade requires a copy of the resource file.

We are also capable of rollback the process, because it just needs to overwrite the existing with the older version resource copy.

11. I have my PROD sql server all system db’s are located on E drive and I need my resource db on H drive how can you move it?

No only resource db cannot be moved, Resource db location is always depends on Master database location, if u want to move resource db you should also move master db.

12. Can we take the backup for Resource DB?

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

13. Any idea what is the Resource db mdf and ldf file names?

mssqlsystemresource.mdf and mssqlsystemresource.ldf

14. How to install a SQL Server using configuration file?

From Command prompt locate the setup.exe file location and can install using config file.

Setup.exe /ConfigurationFile=MyConfigurationFile.INI

Instead of specifying passwords inside the config file specify them explicitly as below.

Setup.exe /SQLSVCPASSWORD=”************” /AGTSVCPASSWORD=”************” /ASSVCPASSWORD=”************” /ISSVCPASSWORD=”************” /RSSVCPASSWORD=”************” /ConfigurationFile=MyConfigurationFile.INI

15. What is the option ”Lock Pages in Memory”?

Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tells Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, but depends on various conditions this option needs to be turned on.

We must be very careful in dealing with this option. One can enable this after a detailed analysis of current environment.

Following issues may rise when “Lock Pages in Memory” is not turned on:

1. SQL Server performance suddenly decreases.
2. Application that connects to SQL Server may encounter timeouts.
3. The hardware running SQL Server may not respond for a short time periods.

The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. (rsReportServerDisabled) Get Online Help Keyset does not exist (Exception from HRESULT: 0x80090016)

I have received the below error when I was tried to open web service URL.




The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. (rsReportServerDisabled) Get Online Help Keyset does not exist (Exception from HRESULT: 0x80090016)




Possible Reasons :


1. First time Report server was configured on instance and the databases were mapped to different server while configuring the report server.


Solution :


Go to the reporting service configuration manager --> Connect to the respective report server instance --> Encryption key page --> Delete .


Now open your Report URL in IE or any browser (run as administrator) .


Now your issue should get resolved. 

Script to find what is causing transaction log file fill in SQL Server

SELECT db.[name] AS [Database Name]
 ,db.recovery_model_desc AS [Recovery Model]
 ,db.log_reuse_wait_desc AS [Log Reuse Wait Description]
 ,ls.cntr_value AS [Log Size (KB)]
 ,lu.cntr_value AS [Log Used (KB)]
 ,CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %]
 ,db.[compatibility_level] AS [DB Compatibility Level]
 ,db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu ON db.NAME = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls ON db.NAME = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
 AND ls.counter_name LIKE 'Log File(s) Size (KB)%';

Script to find who did what in SQL Server ?

Below script is very use full to find out  who has performed what kind of action in SQL Server.

SELECT sys.dm_exec_sessions.session_id
 ,sys.dm_exec_sessions.host_name
 ,sys.dm_exec_sessions.program_name
 ,sys.dm_exec_sessions.client_interface_name
 ,sys.dm_exec_sessions.login_name
 ,sys.dm_exec_sessions.nt_domain
 ,sys.dm_exec_sessions.nt_user_name
 ,sys.dm_exec_connections.client_net_address
 ,sys.dm_exec_connections.local_net_address
 ,sys.dm_exec_connections.connection_id
 ,sys.dm_exec_connections.parent_connection_id
 ,sys.dm_exec_connections.most_recent_sql_handle
 ,(
  SELECT TEXT
  FROM master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle)
  ) AS sqlscript
 ,(
  SELECT db_name(dbid)
  FROM master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle)
  ) AS databasename
 ,(
  SELECT object_id(objectid)
  FROM master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle)
  ) AS objectname
FROM sys.dm_exec_sessions
INNER JOIN sys.dm_exec_connections ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id

How to rename user in MySQL

USE MYSQL;

RENAME USER 'CAMROON' @ 'LOCALHOST' TO 'CAMROON1' @ 'LOCALHOST'
 ,'USER1' @ 'LOCALHOST' TO 'USER2' @ 'LOCALHOST';

SELECT * FROM USER WHERE USER LIKE 'CAM%';

How to update the user or anyonymous password in MySQL

How to Change password with mysqladmin

mysqladmin - u root - p password "new password"

Change your password to pwd5 for the user account under which you're currently logged on to the system
SET PASSWORD = PASSWORD('pwd5'); 

Connect to the server at a specific IP address, with a username of myname and password of mypass:

shell> mysql host=192.168.1.33 user=myname password=mypass

shell> mysqladmin -u root password 'rootpass'

shell> mysqladmin -u root -h host_name password 'rootpass'

shell> mysql -u root mysql

If you want to assign passwords to the anonymous accounts

UPDATE user SET Password = PASSWORD('anonpass') WHERE User = '';

UPDATE user SET Password = PASSWORD('rootpass') WHERE User = '' AND Host = 'localhost';

How to update root password in MySQL

We can update the root password by using the below command.

UPDATE user
SET password = PASSWORD('secret')
WHERE user = 'root'

MySQL - How to drop user in My SQL

Dropping user in MySQL

We can drop user in MySQL in the below methods

1. Drop a User 

USE mysql;
SELECT * FROM user WHERE user = 'KRISH';

USE mysql;

DROP USER KRISH;

2.  Drop the user1@domain1.com user account

USE mysql;
DROP USER user1@domain1.com;

3. Delete anonymous users
USE mysql;

SELECT * FROM user WHERE user = '';
DELETE FROM user WHERE user = '';

MySQL : Grant privileges to the user

GRANT : GRANT statement grants privileges to a user account 



By default, MySQL creates the following grant tables:

SELECT * FROM columns_priv;

SELECT * FROM db;

SELECT * FROM tables_priv;

SELECT * FROM user;

The following grant command will provide Select,Insert access to the user SaiReddy on the desired database.


GRANT SELECT ,INSERT ON databaseName.tableName

 TO SaiReddy@localhost IDENTIFIED BY 'pw1';

Column-Level Privileges :

GRANT SELECT ,UPDATE (BookTitle ,Copyright ) ON test.Books
 TO 'user1' @ 'domain1.com' IDENTIFIED BY 'pw1';
Grant Create , alter,drop and create view permission for a database


GRANT CREATE ,ALTER ,DROP ,CREATE VIEW ON TENNIS.* TO USER1

Grant permission for a procedure
GRANT EXECUTE ON PROCEDURE PROCEDURE_NAME TO User1

GRANT ALL PRIVILEGES to a user from localhost
GRANT ALL PRIVILEGES ON *.* TO 'User1' @ 'localhost' WITH GRANT OPTION



GRANT Create,Alter,Drop privileges to ALL
GRANT CREATE ,ALTER ,DROP ON *.* TO MAX
GRANT Create,Alter,Drop view for a database
GRANT CREATE ,ALTER ,DROP ,CREATE VIEW  ON TENNIS.* TO KRISH



Give user permission for querying a view
CREATE USER 'TEST' @ 'localhost' IDENTIFIED BY 'TEST@123';

CREATE VIEW VW_EMP (DEPT ,NUMBER_OF) AS
SELECT DEPT ,COUNT(*) FROM EMP GROUP BY DEPT;

GRANT SELECT ON VW_EMP TO DEPT

MySQL Privileges

Privileges in MySQL.

MySQL - Verifying user permissions.

After creating the user and granting the permissions then you can use the below script to find out the granted privilege.

USE MYSQL;

SELECT host ,user,select_priv ,update_priv FROM user
WHERE user = 'KRISH';

SELECT host ,db ,user ,table_name ,table_priv ,column_priv FROM tables_priv
WHERE user = 'KRISH';

SELECT host ,db ,user ,table_name ,column_name ,column_priv FROM columns_priv
WHERE user = 'KRISH';

MySQL - Creating user in MySQL

Creating user

Creating a user is 4 step process i.e

1. Check the list of available databases in MySQL

show databases ;

2. Select the database on which you want to create the user.

use MySQL ;

3. Execute the below script to created the desired user.

CREATE USER 'KRISH' @ 'localhost' IDENTIFIED BY 'KRISH'
 ,'CAMROON' @ 'localhost' IDENTIFIED BY 'CAMROON';

4. Verify whether user has been created or not.

SELECT * FROM USER;

Script to find currently running or active queries in SQL Server

Some times we used to get calls or mails from the user and asking for what are currently running queries in a particular instance and also will get complaints like queries are running slow.

For this scenario, we have to run the below query on a requested instance to find out currently running queries.

SELECT r.start_time [Start Time]
 ,session_ID [SPID]
 ,DB_NAME(database_id) [Database]
 ,SUBSTRING(t.TEXT, (r.statement_start_offset / 2) + 1, CASE 
   WHEN statement_end_offset = - 1
    OR statement_end_offset = 0
    THEN (DATALENGTH(t.TEXT) - r.statement_start_offset / 2) + 1
   ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
   END) [Executing SQL]
 ,STATUS
 ,command
 ,wait_type
 ,wait_time
 ,wait_resource
 ,last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID
 AND session_id > 50 -- To avoid system queries
ORDER BY r.start_time

MySQL Strengths

MySQL Strengths

MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).

MySQL Introduction

MySQL Introduction

MySQL is one of the most popular Open Source RDBMS ( Relational SQL database management system) because of its flexibility such as  fast, easy-to-use for many small and big businesses.
MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company.
MySQL is becoming so popular because of many good reasons:
MySQL is released under an open-source license. So you have nothing to pay to use it.
MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
MySQL uses a standard form of the well-known SQL data language.
MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
MySQL works very quickly and works well even with large data sets.
MySQL is very friendly to PHP, the most appreciated language for web development.

Sys.dm_exec_requests - What is use of this DMV ?

This DMV will provide the information about each request that is executing with in the SQL Server.
To work with this DMV , the user should have VIEW SERVER STATE permission.

SELECT SESSION_ID
 ,percent_complete
 ,estimated_completion_time
FROM SYS.dm_exec_requests

Percent_complete will provide the completion details of the below activities i.e

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

How to change all database comparability level from 90 to 100

During database migration ,I got the requirement to change the database recovery model from 90 to 100 i.e from SQL Server 2005 to SQL Server 2008.

You can also use the below script if database count is more.

Just execute the below script and paste the results appeared in message window to new query window and then press F5 to execute the scripts.

SELECT 'alter database ' + '' + NAME + ' ' + 'SET COMPATIBILITY_LEVEL = 100'

 ,recovery_model_desc
 ,compatibility_level
FROM sys.databases
WHERE compatibility_level = 90
 AND NAME NOT IN ('AdventureWorksDW')

Kindly note that you can also use the above script if you want to change the compatibility of the databases where you doesn't know the databases count or >100 databases at a time.

How to change all database comparability level from 90 to 100

During database migration ,I got the requirement to change the database recovery model from 90 to 100 i.e from SQL Server 2005 to SQL Server 2008.

You can also use the below script if database count is more.

Just execute the below script and paste the results appeared in message window to new query window and then press F5 to execute the scripts.

SELECT 'alter database ' + '' + NAME + ' ' + 'SET COMPATIBILITY_LEVEL = 100'

 ,recovery_model_desc
 ,compatibility_level
FROM sys.databases
WHERE compatibility_level = 90
 AND NAME NOT IN ('AdventureWorksDW')

Kindly note that you can also use the above script if you want to change the compatibility of the databases where you doesn't know the databases count or 100 databases at a time.

How to find when was SQL Server restarted ?

We can find the SQL Server restart time using various methods. Below are the methods which I used to find out when was the SQL Server restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

                          (or)

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1;

                          (or)
SELECT start_time FROM sys.traces WHERE is_default = 1

                          (or)
SELECT crdate FROM sysdatabases WHERE NAME = 'tempdb'

                           (or)

SELECT create_date FROM sys.databases WHERE NAME = 'tempdb'

How to find when was SQL Server restarted ?

We can find the SQL Server restart time using various methods. Below are the methods which I used to find out when was the SQL Server restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

                          (or)

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1;

                          (or)
SELECT start_time FROM sys.traces WHERE is_default = 1

                          (or)
SELECT crdate FROM sysdatabases WHERE NAME = 'tempdb'

                           (or)

SELECT create_date FROM sys.databases WHERE NAME = 'tempdb'

Script to estimate backup and restore completion time in SQL Server

I used to get frequent mails from the users stating about when would be the backup will complete etc.

For this I used the below script which will provide the details of backup with the estimated time.

SELECT r.session_id
 ,r.command
 ,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
 ,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20)

 AS [ETA Completion Time]
 ,CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
 ,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
 ,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
 ,CONVERT(VARCHAR(1000), (
   SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE 
      WHEN r.statement_end_offset = - 1
       THEN 1000
      ELSE (r.statement_end_offset - r.statement_start_offset) / 2
      END)
   FROM sys.dm_exec_sql_text(sql_handle)
   ))
FROM sys.dm_exec_requests r
WHERE command IN (
  'RESTORE DATABASE'
  ,'BACKUP DATABASE'
  )

Troubleshooting CXPACKET wait type in SQL Server

The CXPACKET term came from Class Exchange Packet, This can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using parallel plan.

You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

For more details please refer.

SQL Server Agent failing to start with the error “StartServiceCtrlDispatcher failed (error 6)”

SQL Server Agent failing tostart with the error “StartServiceCtrlDispatcher failed (error 6)” 

Log reader fails with “The process could not execute ‘sp_replcmds’ ” error

Log reader fails with “The process could not execute‘sp_replcmds’ ” error

How to export the Event Logs without opening MMC

Export the Event Logs without opening MMC

Application timing out due to excessive blocking on tempdb PFS

     Trouble shooting :  Application timing out due to excessive blocking on PFS

Common Significant Wait types with BOL explanations

                                                          WaitTypes 

Network Related Waits

       ASYNC_NETWORK_IO :Occurs on network writes when the task is blocked behind the network

 Locking Waits

1. LCK_M_IX: Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.

2. LCK_M_IU: Occurs when a task is waiting to acquire an Intent Update (IU) lock.

3. LCK_M_S: Occurs when a task is waiting to acquire a Shared lock.

I/O Related Waits

1.ASYNC_IO_COMPLETION: Occurs when a task is waiting for I/Os to finish.

2. IO_COMPLETION: Occurs while waiting for I/O operations to complete.              This wait type generally represents non-data page I/Os. Data page I/O completion waits appear  as

 PAGEIOLATCH waits

3. PAGEIOLATCH_SH:Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

4. PAGEIOLATCH_EX:Occurs when a task is waiting on a latch for a buffer that is in an I/O request.  The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

5. WRITELOG : Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.

6. PAGELATCH_EX: Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.

7. BACKUPIO:Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

CPU Related Waits

1. SOS_SCHEDULER_YIELD:  Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

2. THREAD POOL : Occurs when a task is waiting for a worker to run on.

  This can indicate that the maximum worker setting is too low, or that batch executions are taking  unusually long, thus reducing the number of workers available to satisfy other batches.

3. CX_PACKET: Occurs when trying to synchronize the query processor exchange iterator .You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

SQL Server : Slow running query resons

                                                    Query Performing Slowly

1) Identify the SPID of the query from Sys.sysprocesses

2) Find any blockings exists or not and if it exists find the LEAD Blocker.

3) Identify how many connections are there, Benchmark if exceeds is an issue

4) CPU Utilization, Memory Utilization, System Memory Information and how much SQL Server is consuming. If AWE is enabled or not.

5) Any jobs are running both at OS level and SQL Server level.

6) Affinity Mask for Processor and IO should be checked.

7) Query observation, Coding standards have been followed or not. Commit issued at regular intervals in the code.

8) Table statistics are outdated. Need to update stats.

9) Indexes are created on correct columns are not. Verify the query and check the columns listed under WHERE condition and ensure that there are indexes created.

10) Even if indexes are created, they are fragmented or not.

11) Run server side trace to track what other activities are currently being rolled out to find the cause of the issue. If approved Profiler also can be used.

12) Network Latency between Client and the Server can cause performance issue.

13) MAXDOP feature can be utilized to improve the parallel execution of the query.

14) If clustered instance and Active-Active (N+1/N+M) Multiple Instance. If all nodes fail the last surviving node has all instances running on it causing performance issue.

15 )  Check disk space availability for that database and also for system databases.

16 ) Verify if any Application or Database specific jobs are running or not.

17 ) Network bandwidth to be verified and also Storage SAN bandwidth when accessing data. Contact Network/Storage team.

18) Identify top 10 long running queries and see if they are causing the performance lag.

19) Ask requester for the Query. Verify the Estimated Execution Plan of the Query.

20) Check if any Table Scans are present in the Plan. Table Scans are very expensive from resource perspective.

Also verify the Load on the system, Example average load is 2500 but we could see 10,000 connections.