Top posts
-
example of need of resource governor
What will be the real world example of need of resource governor? Scenario 1: A server which is running OLTP workload and various resource intensive reports on the same server. The ideal situation is where there are two servers which are data synced with...
-
What database objects are stored in tempdb
What database objects are stored in tempdb? Ans: There are three different types of objects stored in tempdb. Internal Objects: Intermediate runs for sort. Intermediate results for hash join and hash aggregates. XML variables or other large object (LOB)...
-
Performance Tuning
Performance Tuning – SQL Server Part – 1 Q. What are the bottlenecks that effects the performance of a Database / Application Ans: The top performance bottlenecks for OLTP applications are outlined as: Database Design \ Database Code Application Design...
-
What are the CheckPoints in SQL Server database?
What are the CheckPoints in SQL Server database? Ans: Checkpoints flush dirty data pages from the buffer cache of the current database to disk. This minimizes the active portion of the log that must be processed during a full recovery of a database. During...
-
Microsoft R&D SQL Server Oops Lead
1. What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value” Ans: It issues an update lock and upgrades it to Exclusive Lock The corresponding page would be captured from...
-
Genpact Lead SQL DBA
1. Difference between 32 bit and 64 bit 2. How B-Tree formed for Clustered and non clustered indexes? 3. How B-Tree forms for indexes with included column? 4. Does alzebrizer tree stores in memory for stored procedures, views and constraints? 5. What...
-
VIRTUSA SQL DBA
1. On which basis merge replication works on? 2. How to give a user to run a sql agent job? 3. Can we install SQL Server using a configure file? 4. What are the top performance counters to be monitor in Performance Monitor? 5. How do you know how...
-
sql server innRoad INterview Questions
1. What is index reorganization? 2. How sql engine knows which index has to be used while dealing with indexed views? 3. How to prevent bad parameter sniffing? What exactly it means? 4. What dll file that handle the transaction logs in logshipping? 5....
-
Replication Commands:
Replication Commands: Publisher.SP_ReplTran: Checks the pending transactions at p Distributor.MSReplCommands and MSReplTransactions: Gives the transactions and commands details. Actual T_SQL data is in binary format. From the entry time we can estimate...
-
Can we perform a tail log backup if .mdf file is corrupted?
Can we perform a tail log backup if .mdf file is corrupted? Ans: Yes we can perform a tail log as long as the ldf if not corrupted and no bulk logged changes. A typical tail log backup is having two options, 1. WITH NORECOVERY 2.Continue After Error....
-
How MAXDOP impacts SQL Server?
How MAXDOP impacts SQL Server? Ans: The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing...
-
How do you troubleshoot a Full transaction log issue?
How do you troubleshoot a Full transaction log issue? Ans: Columns log_reuse_wait and log_reuse_wait_desc of the sys.databases catalog view describes what is the actual problem that causes log full / delay truncation. Backing up the log. Freeing disk...
-
Can you describe factors that causes the logfile grow?
Can you describe factors that causes the logfile grow? Ans: CHECKPOINT has not occurred since last log truncation No log backup happens since last full backup when database is in full recovery An active BACKUP or RESTORE operation is running from long...
-
What is the option ”Lock Pages in Memory”? in sql server
What is the option ”Lock Pages in Memory”? Ans: Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems,...
-
What are the Hotfixes and Patches?
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....
-
Why Shrink file/ Shrink DB/ Auto Shrink is really bad?
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...
-
What is the cluster node limitation?
What is the cluster node limitation? The number of allowable nodes in the SQL Server cluster depends on your SQL Server version and your Windows Server version. For SQL Server 2008 Standard edition, you can only have two cluster nodes. If you have SQL...
-
SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days
SQL SERVER – 2005 – List All Stored Procedure Modified in Last N I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable...
-
SQL SERVER – Improvements in TempDB
SQL SERVER – 2005 Improvements in TempDB Following are some important improvements in tempdb in SQL Server 2005 over SQL Server 2000 Input/Output traffic to TempDB is reduced as logging is improved. In SQL Server 2005 TempDB does not log “after value”...
-
How to Identify Locked Table in SQL Server?
SQL SERVER – How to Identify Locked Table in SQL Server? Here is a quick script which will help users to identify locked tables in the SQL Server. SELECT OBJECT_NAME(p.OBJECT_ID) AS TableName, resource_type, resource_description FROM sys.dm_tran_locks...
-
SQL DBA Log Shipping PROBLEMS
Troubleshooting Log Shipping: • 1) Jobs disabled can be a cause for LS failure. • 2) Backup Share permission issues. • 3) Space issues in the backup share/local copy location. 4) SQL Server Agents stopped at Primary/Standy/Monitor. 5) Manual log backup...
-
SQL DBA LOG SHIPPING
Log Shipping Introduction Shipping of Transaction Log files from Primary server database to secondary database to get both databases in continuous Synchronization. It is a continuous process in the form of batches. Log Shipping requires 3 Servers: · Primary...
-
Sql server TATA technology Questions
1. EXPLAIN CONSTRAINTS? 2.WHAT IS CHECK CONSTRAINT? WHEN YOU USE THAT? 3.DEFINE VIEW? DIFFERENCE BETWEEN WHERE CLAUSE AND HAVING CLAUSE? 5.WHAT IS INDEX USE OF INDEXES? 6.WHAT IS TOP CLAUSE? 5.WHAT IS WILD CARD EXPLAIN? 7.GOALS OF NORMALIZATION? DIFFERENCE...
-
Used UNIX / Linux Commands (With Examples)
Used UNIX / Linux Commands (With Examples) 1. tar command examples Create a new tar archive. $ tar cvf archive_name.tar dirname/ Extract from an existing tar archive. $ tar xvf archive_name.tar View an existing tar archive. $ tar tvf archive_name.tar...
-
DAC IN SQL SEREVR
SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked...