Category: Performance Tuning
-
Delete vs Truncate
Its a common interview question which people like to ask to know your understanding on RDMS concepts. I too few times encountered the question in interviews. Pleases don’t ask me interview result. Haha ha… DELETE: 1. DELETE is a DML Command. 2. DELETE statement is executed using a row lock, each row in the table…
//
-
DBCC IND
The DBCC IND statement takes three parameters like this: DBCC IND(0,’dbo.SalesOrderDetailList’,1); First parameter is the database id. If you pass in 0 here, the current database is used. Second parameter is the table name in quotes. You can also pass in the object_id of the table instead of the quoted name like this: DBCC IND(0,1797581442,1); Third parameter is the index_id. There is an optional fourth parameter that…
//
-
SQL Server VSS Writer Service
Microsoft built the capability into windows allowing files to be backed up to disk while other programs are using those files. The VSS Servicer interacts with the Windows Volume Shadow Copy service (VSS), allowing files that are in use, such as SQL Server data files, to be copied (or backed up) without stopping all other…
//
-
SQL Server Architecture Diagram
Hey guys, In this blog I am going to explain you about SQL Server Architecture Diagram In almost every SQL DBA interview, this is the question which thrown as a hard ball from interviewer. Below is high level SQL Server Architecture Diagram which is hard to explain in telephonic interview as well as face to…
//
-
DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA Monitoring My SQL Performance Tuning PostgreSQL SQL Server
SQL Server Memory Architecture
There are four major components of SQL memory architecture… 1. Memory Broker2. Memory Object3. Memory Clerk4. Memory Node In short, BOCN. (For remember it “Boy Of Cartoon-Network”) 1. Memory Broker Purpose of Memory Broker is to provide a centralized mechanism to distribute/control memory the allocation made by each component in SQL Server. Note: Memory brokers do…
//
-
Dive into Buffer pool extension
The Buffer pool extension feature started from SQL server 2014. This is a nice addon to SQL server to extend the memory paging to SSD. This is not an enterprise only feature, this can be enabled on standard edition. The database size has grown huge now the Db’s having size more than 1 TB is…
//
-
DBA Worries Continued Installation and Configuration L1 DBA Monitoring Performance Tuning SQL Scripting SQL Server
Let’s talk about In memory OLTP
In-memory OLTP frequently referred to by its code name “Hekaton”, was introduced in SQL Server 2014 but it got more popular from SQL Server 2016. This feature was being developed by Microsoft to stay in the game for high speed OLTP transaction speed and recent hardware changes in terms of the cost and throughput. Many…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL Performance Tuning PostgreSQL SQL Server
Ghost Records
When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records. SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically…
//
-
Moving A Table Data Into New Location Within Database
Problem I often getting low disk space alert from one of my data drive. Adding space on the disk was not possible because of hard drive size limitations and barriers at OS max disk space size. So I started looking what can I do for database end and I found there is only one table…
//
-
DBCC (Database Console Command) Commands
It’s a common question of DBA interview. I already have faced millions of time. HaHa Ha. DBCC is a series of statements in Transact-SQL programming language to check the physical and logical consistency of SQL Server database. We can divide in 4 major category or type… DBCC command type… 1. Informational DBCC command 2. Validational DBCC command 3. Maintenance…
//
-
Azure Azure SQL Database Azure SQL Database Backup and Recovery Backup Tools for SQL Server DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL Performance Tuning PostgreSQL Security SQL Scripting SQL Server
Important SQL Server Scripts…
Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1 @DBName AS [DBName] ,[LogDate] ,CASE WHEN…
//