SQL Server Memory Frequently Asked Question and Answers (FAQ) in Interviews Part 1

Memory in SQL Server is one of the most interesting concept. SQL Server implements its own memory architecture and management. If you are attending an interview for a senior DBA role or attending an interview with a very reputed organization who are expecting a very talented resource, then most often or not you will be asked lot of questions about the SQL Server internals and it definitely includes Memory related question. Understanding SQL Server Memory architecture is simple, yet very interesting and challenging as we dig deeper.

Below could be some of the Frequently Asked Question and Answers (FAQ) in Interviews on SQL Server Memory.

Is SQL Server Memory architecture different in x86, x64 and WoW modes?
Yes, SQL Server has different architecture for servers with x86, x64 and WoW modes of SQL Server instances, primarily because of how Virtual Address Space (VAS) is allocated by the Operating System. On x86 system, VAS is limited to 4 GB and out of this 4 GB, SQL Server can only use VAS up to 2 GB by default or 3 GB if PAE or /3 GB switch is enabled, where as in x64 systems, it is as high as 16 TB and SQL Server can use up to 8 TB of VAS.

What is Virtual Address Space (VAS)?
Virtual Address Space (VAS) is a Operating System concept, which is nothing but set of addresses which are allocated to each process running on the system. The idea behind VAS is, If processes running on system are only allowed to access the physical RAM, then very quickly the system will run out of memory, so VAS concept was employed, where each process is allocated some virtual addresses and when required, the processes can request the OS to map their virtual addresses with the physical addresses where the data is to be loaded. This way all processes get their own private virtual addresses and can get physical memory allocated as and when required. This way Operating System can manage the available physical memory efficiently across all the processes.

What are the available Virtual Address Space (VAS) on x86 and x64 systems for SQL Server?
SQL Server is also a user processes, so it gets its own private Virtual Address Space (VAS) from the Operating System. On x86 system, 4 GB of VAS is available in a system, which is divided into two regions, User mode VAS and Kernal mode VAS. Each process running on the server gets its own 2 GB of VAS and remaining 2 GB is available for the OS for its own use. We can increase the User mode VAS of a process from 2 GB to 3 GB using /3GB switch or /USERVA. so, SQL Server on x86 system is a user process, so it gets 2 GB or 3 GB or VAS depending upon the /3 GB switch. On a x64 system, VAS available 16 TB of which 8 TB is available for each process and 8 TB is available for the OS. SQL Server On x64 system, can use up to 8 TB of VAS.

What is SQL Server Memory Architecture or How is SQL Server Memory Divided?
SQL Server user VAS is divided into two regions, which are BufferPool and Memory-To-Leave (MTL).

BufferPool – BufferPool is the region of memory which is used for storing the in memory data and index pages which are <= 8 KB of size. MemToLeave (MTL) – MTL is contiguous memory which is allocated during the startup of SQL Server, which is used for SQL Server thread stacks, third party DLL’s, Extended Stored Procedures, COM objects, Memory used by linked servers, CLR, any memory allocated for requests greater than 8 KB of contiguous memory.

How are the memory sizes for BufferPool and MemToLeave (MTL)?
Size of BufferPool and MTL differs from x86 and x64 systems. On x86 system, during startup of SQL Server MTL is reserved, which is contiguous memory and by default is 384 MB on systems with less than 4 processors. More accurate way of calculating the size of MTL is as below.

MTL = (Size of Stack Size * Number of SQL Server Worked Threads) + Additional space reserved, by default 256 MB and the value specified in -g startup parameter).

– Size of stack on x86 system is 512 KB
– Number of worked threads on a server can be different based on available processors. On x86 system with <= 4 processors, there will be 256 worker threads available, but can be changed using sp_configure options as well. Refer here for more information about worked threads.

So, on x86 system with <= 4 processors and -g option not used, MTL = ((256 * 512 KB)/1025) MB + 256 MB = 384 MB. Once this 384 MB contiguous memory is allocated out of available 2 GB VAS, there will be 1.6 GB of memory remaining which will be used as BufferPool memory.

On a x64 system, SQL Server Stack Size is 2048 KB. So a x64 system with <=4 processors will have MTL = ((512 * 2048)/1024) MB + 256 MB = 1280 MB. This is contiguous memory allocated during the startup of SQL Server and then BufferPool is allocated and finally remaining VAS is left to be used for MTL.

BufferPool is also calculated, where BufferPool = Minimum(Physical memory, User address space – MTL) – BUF structures

On x86 system with <= 4 processors, 4 GB RAM and -g option not used, BPool = Min(4 GB, 2 GB – 384 MB) – 32 MB = 1632 MB ~ 1.59 GB

On x64 system with <= 4 processors, 4 GB RAM and -g option not used, BPool = Min(4 GB, 8 TB – 1280 MB) – 32 MB = 4064 MB ~ 3.96 GB

What can SQL Server MTL memory region contain?
MTL regios is used for thread stacks, third party DLL’s, COM components, Extended Stored Procedures, CLR objects loaded in SQL Server, linked server objects memory, any contiguous memory greater than 8 KB.

What can SQL Server BufferPool memory region?
SQL Server BufferPool memory region contains data or index pages which are less than or equal to 8 KB size.

What is Address Windowing Extensions (AWE) and how is it useful and how it can be used with SQL Server?
Address Windowing Extensions aka AWE is nothing but set of API’s which is used for addressing more than 4 GB of physical memory. On x86 system, SQL Server by default can only use 4 GB physical memory, so to allow SQL Server to use more than 4 GB of memory, AWE needs to be enabled. AWE for 32 bit SQL Server instance can be enabled either from Sp_Configure or from SQL Instance Properties from SSMS.

What happens on x86 system with 64 GB RAM and /3 GB switch enabled?
On 32 bit system with /3 GB switch enabled, SQL Server instance can only use a maximum of 16 GB physical memory. This is because, when /3 GB switch is enabled SQL Server process gets 3 GB of VAS, but Kernal mode VAS is reduced to 1 GB, with this 1 GB of kernal VAS, OS can only use up to 16 GB physical memory, thus SQL Server also cannot see beyond this 16 GB, so be mindful with enabling /3 GB switch.

Should we enabled AWE in x64 systems?
No, although we have the option to enable AWE on x64 systems, it has no affect. By default x64 systems can use large amounts of physical memory as it has 8 TB if user VAS, so no requirement of using AWE.

What is Lock Pages in Memory?
“Lock Pages in Memory” is Operating System policy, which allows a process to lock its data in memory and does not trim/release that memory working set under memory pressure. This is not completely true, as Operating System can trim working set of any process if required, but it will only trim the process workingset with lock pages in memory enabled at the last. Enabling this for SQL Server service account, will lock bufferPool pages in memory and will not trim the BPool unless OS is completely out of memory and all other processes memory has been already trimmed.

Should we enabled Lock Pages in Memory for SQL Server instance?
On x86 system, to use AWE, lock pages needs to be enabled.
On x64 system, if the server is dedicated SQL Server system, then it is good to enable lock pages in memory.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Migration of SQL Server Instance from One Server to Another Server with Minimum Downtime

As newer versions of SQL Server are released, we plan implement to upgrade to higher versions and during this process, we may also upgrade to new and powerful servers with better hardware and latest Operating System. Also, as part of data center relocation, we migrate the SQL Server instance from one server to another server in new data center. The process of migration involves lot of steps and need to plan and implement the steps so that, at end of migration, we have SQL Server instance running without any problems.

This article covers a scenario where we want to migrate SQL Server instance from one server to another server. This is applicable for scenarios where we are migrating between servers which have same versions of SQL Server installed or new servers having higher version of SQL Server. Goal of the migration is that there is minimal downtime when we switch the applications from old server to new server.

Below are the high level steps involved in migration.

– First, we need to perform these steps on test server, so that we can find the issues that can arise during the migration before hand and also to test if the applications work without any problems after migration.

– If we are migrating to a higher version of SQL Server, then need to document the issues that can arise with the migration by running Upgrade Adviser and prepare documentation with remediation steps to fix those issues as part of the migration. If we are migrating to new server with same version of SQL Server, then no requirement of upgrade adviser.

– Install the required version of SQL Server instance on the new server.
– Apply latest Service Pack or Cumulative Updates.
– Script logins from existing server and Create those logins on the new server.
– Script all the jobs on existing server and create them on the new server, keep them disabled. Create maintenance plans on new server similar to existing server.
– Configure Database Mail if used.
– Create any linked servers if required.
– Configure SQL Server settings like Max Server Memory, MaxDoP, backup compression settings, etc on the new server.
– Configure OS or Network settings on new server like Lock Pages In Memory, Perform Volume Maintenance Tasks, etc.
– Backup all User databases on existing server, copy the backups to new server and restore them. Document the time it took for performing backups, copying backups to new server and for restoring of databases.
– Fix Orphan Users for all user databases.
– Enable backup and maintenance jobs like rebuild index, Update stats, etc on new server.
– Change the compatibility level of user databases to latest version(If migrating to higher version).
– Perform Application testing to make sure the application is working without any problems and performance is acceptable.
– Once, everything looks fine and got permission to move ahead, plan for the Go-Live of new server.

Go-Live Planning
– Our goal here is to have minimum downtime during the Go-Live process, so we need to either setup Logshipping or Database Mirroring between existing and new servers.

– Configure Logshipping from existing server to new server for all user databases and make sure backup, copy and restore jobs are running without any issue.
– Before Go-Live, make sure Logins and jobs are same on both the existing and new servers.
Start of Down Time
– Disable all jobs on existing server.
– Run logshipping backup jobs on existing server, once it completes successfully, disabled the job.
– Run logshipping copy jobs on new server, once it completes successfully, disabled the job.
– Run logshipping restore jobs on new server, once it completes successfully, disabled the job.
– Remove the logshipping configuration between existing and new server.
– Bring the all user databases online on new server by running “restore database databasename with recovery”.
– Enable all required SQL Server Agent jobs on new server and disable the same on old server
– Fix the orphan users.
– Change the compatibility level of all user databases to latest version(If migrating to higher version).
– Point the application to use the new server.
End of Down Time
– Test the Application to make sure it is working as expected.
– Later run rebuild indexes and Update Statistics on all user databases.
– If there are any issues, try possibilities of fixing those issue, if it is taking time and decided to rollback, refer below RollBack plan.
– If no rollabck required, take SQL Services offline on old server and decommission the old server.

RollBack Plan
– Verify all the user Databases are online on old server.
– Point the application back to old server.
– Enable all required jobs on old server and disable the same on new server.
– Test the application.

You can also use database mirroring instead of logshipping, just replace the steps related to logshipping with database mirroring related steps.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server 2008 R2 Service Pack 3 (SP3) has Been Released

SQL Server 2008 R2 SP3 has been released on 26 Sep 2014. You can download here the SQL Server 2008 R2 Service Pack 3 (SP3)

SQL Server 2008 R2 Service Pack 3 (SP3) is now available for download. SQL Server 2008 R2 service packs are cumulative and can be used to upgrade all releases of SQL Server 2008 R2 to Service Pack 3. SQL Server 2008 R2 Service Pack 3 contains Cumulative Update 1 to 13 from SQL Server 2008 R2 SP2. The package can be used to upgrade the following SQL Server 2008 R2 editions:

SQL Server 2008 R2 Parallel Computing Edition
SQL Server 2008 R2 Datacenter Edition
SQL Server 2008 R2 Enterprise Edition and Developer Edition
SQL Server 2008 R2 Standard Edition
SQL Server 2008 R2 Web Edition
SQL Server 2008 R2 Workgroup Edition

You may right away download the install the SP3 on a test server or wait for the release of CU1 for SP3 and install both of them together. It is normal good practice to first install the newly released service pack and CU1 together. Important thing is to first install the SP3 on test server to make sure that the service pack does not break the application after its installation.

Some of the common terminology of various SQL Server releases or patches.

Community Technology Preview (CTP) – Community Technology Previews are beta releases. Before release of actual RTM version, multiple CTM versions are released for testing purposes and fix any issues reported before releasing the official product.

Release Candidate (RC) – Release candidate is a version of a program or product that is functional, but not quite ready to be released to the consumer market.

Released To Manufacturing (RTM) – It is the official and full product, and is a released build version of the product, what we get on the DVD or when you download the ISO file from MSDN. This is the product which we install on production and non-production servers and is fully supported by Microsoft in case of any issues.

Cumulative Update (CU) – Cumulative updates contain the bug fixes and enhancements–up to that point in time–that have been added since the previous Service Pack release and will be contained in the next service pack release. Installation of the Cumulative Update is similar to the installation of a Service Pack. Cumulative Updates are not fully regression tested.

Service Pack (SP) – Larger collection of CU’s, hotfixes and additional fixes that have been fully regression tested. In some cases also has additional product enhancements.

General Distribution Release (GDR) – GDR packages contain only security and critical stability issue fixes. GDR fixes does not contain any of the CU updates.

Quick Fix Engineering (QFE) – Quick Fix Engineering (QFE) are now referred as Limited Distribution Release (LDR). QFE and LDR may be used synonymously. LDR/QFE updates include CU fixes. LDR packages contain “other” fixes that have not undergone as extensive testing, and resolve issues that only a fraction of the millions of Windows users might ever encounter.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Script to Gather Disk Space Details and Database File Free Space Information

It is a common requirement to find the details about the total free disk space, database files total size, free space available in database files, name of the database files, type of database file and the physical location they are located at.

Below is the code which returns Free disk space on a drive, name of database, logical and physical file names of all databases, total size, free space available inside the database files and the physical location where they are stored.

USE tempdb
    GO
      CREATE TABLE #TMPFIXEDDRIVES (
    DRIVE CHAR(1),
    MBFREE INT)

    INSERT INTO #TMPFIXEDDRIVES
    EXEC xp_FIXEDDRIVES

    CREATE TABLE #TMPSPACEUSED (
    DBNAME VARCHAR(1500),
    FILENME VARCHAR(500),
    SPACEUSED FLOAT,
    growth int)

INSERT INTO #TMPSPACEUSED
    EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed,growth from sysfiles''')

SELECT C.DRIVE,
    CASE 
    WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
    ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
    END AS DISKSPACEFREE,
    A.NAME AS DATABASENAME,
    B.NAME AS FILENAME,
    CASE B.TYPE 
    WHEN 0 THEN 'DATA'
    ELSE TYPE_DESC
    END AS FILETYPE,
    CASE 
    WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
    ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
    END AS FILESIZE,
    CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE_MB,
    (d.growth*8)/1024 as AutoGrowth_MB,
    B.PHYSICAL_NAME
    FROM SYS.DATABASES A
    JOIN SYS.MASTER_FILES B
    ON A.DATABASE_ID = B.DATABASE_ID
    JOIN #TMPFIXEDDRIVES C
    ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
    JOIN #TMPSPACEUSED D
    ON A.NAME = D.DBNAME
    AND B.NAME = D.FILENME
    ORDER BY databasename

/*<b>*/DROP/*</b>*/ TABLE #TMPFIXEDDRIVES
/*<b>*/DROP/*</b>*/ TABLE #TMPSPACEUSED

Output returned will be in below format

DRIVE

DISKSPACEFREE

DATABASENAME

FILENAME

FILETYPE

FILESIZE

SPACEFREE_MB

AutoGrowth_MB

PHYSICAL_NAME

S

923.48 GB

DBName

LogicalFileName

DATA

218.16 GB

8500.25

200

S:\DataFile.mdf

DRIVE – Drive where the database file resides.
DISKSPACEFREE – Free disk space available in the drive.
DATABASENAME – Name of the database.
FILENAME – Logical database File Name.
FILETYPE – What type of file it is, Data File, Log File.
FILESIZE – Total size of the database file.
SPACEFREE_MB – Free space available inside the database file.
AutoGrowth_MB – Auto_Growth size of the database file.
PHYSICAL_NAME – Physical location and physical database file name.

Below script will provide with space details of a specific database and its files. This information will be useful to find which database files are large, how much free space is still available in the database files, so that we can shrink the file to release the available free space.

select
       name
     , filename
     , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
     , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
     , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
     from dbo.sysfiles a 

name

filename

FileSizeMB

SpaceUsedMB

FreeSpaceMB

tempdev

T:\Data\tempdb.mdf

40960.00

4.75

40955.25

templog

T:\Log\templog.mdf

2048.00

365.86

1682.14

tempdata

T:\Data\tempdata.mdf

40960.00

4.56

40955.44

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Configuring SQL Server 2012 Logshipping on AlwaysON Availability Group Databases with Successful Failover of AG and Logshipping

Configuring SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG) is supported in SQL Server 2012 or higher. We can failover AlwaysON Availability Groups between Primary and Secondary Server and also move or enable the Logshipping from new Primary Server to DR server. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02.

Now we can setup Logshipping from Server01 to Server03 just the same way how we setup logshipping between any two server databases as mentioned below.

How to Setup Logshipping between two servers?
– Connect to SQL Server instance from SSMS using instance name as Server01.
– On Server01, right click the database you want to use as your primary database in the log shipping configuration, and go to its Properties and go to last tab “Transaction Log Shipping”.
– Select the “Enable this as a primary database in a log shipping configuration” check box, which will now enable the logshipping configuration options to choose backup, copy and restore settings.
– Under “Transaction log backups”, click on Backup Settings.
– In the Network path to the backup folder box, type the network path to the backup share to use it for placing the transaction log backups.
– If this backup share is located on the primary server itself then type the local path to the backup folder in the “If the backup folder is located on the primary server” text box. If the backup folder is not on the primary server, you can leave this box empty.
– Make sure that the SQL Server service account on the primary server has full permissions on the backup share.
– Configure the Delete files older than and Alert, if no backup occurs within parameters.
– You can choose to customize the schedule for log backup and alerts.
– Starting SQL Server 2008, backup compression is supported, so you may choose to enabled it or set it to use Instance wide backup compression configuration.
– Now, under Secondary server instances and databases, click Add.
– Click on Connect and then connect to the SQL Server instance Server03 that we want to use as the secondary server for logshipping.
– In the Secondary Database box, either choose a database from the list or just type the name of the database you want to create.
– On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.
– Make sure that you choose correct option above and appropriately prepare the database on secondary server.
– Now go to Copy Files tab, and in the “Destination folder for copied files” text box, put the path of the folder to which the transaction logs backups should get copied. This folder is often located on the secondary server or common share. Make sure that SQL Server service account on secondary has full permissions to this path.
– You may optionally choose to change the copy and alert thresholds as per your SLA’s
– Now move to Restore tab, under “Database state when restoring backups”, choose the No recovery mode or Standby mode option. If you are choosing Standby mode option, then also select that you want to disconnect users from the secondary database while the restore operation is performed.
– If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
– You may optionally alter the restore and alert thresholds.
– If you want, you may also add a third server as Monitor server instance to monitor this log shipping configuration. You need to decide whether you want to use monitor server or not, because, if you want to add the monitor server later, we need to remove logshipping entirely and reconfigure it with monitor server.
– If you choose to configure monitor server as well, then click on Connect and connect to the SQL Server instance to use as the logshipping monitor server.
– Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server. Under History retention, choose the length of time you want to retain a history of log shipping.
– On the Database Properties dialog box, logshipping tab, click OK to begin the configuration process.
– Verify and make sure that logshipping log backup job is running successfully on Primary server Server01 and is creating the backup file on the backup share we provided to place the log backup files.
– Verify and make sure that logshipping copy job is running successfully on Secondary Server Server03 and is copying the backup file from the backup share to path specified during copy settings for secondary server.
– Verify and make sure that logshipping restore job is running successfully on Secondary Server Server03 and is restoring the backup files.
– Check Logshipping report from SSMS on Server01 and Server03, by right clicking on SQL Server Instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

Now we have successfully setup logshipping from Server01 to Server03, Next thing is to failover AlwaysON Availability Group “PROD_LSTNR” from Server01 to Server02, after which we also want to move the logshipping to happen from Server02 to Server03 and disable logshipping from Server01 to Server03.

Disable the logshipping from Server01 to Server03 by following below steps
Connect to SQL Server instance using SSMS with instance name as Server01 and Server03
Disable logshipping log backup job on Server01.
Disable logshipping copy job on Server03 related to Server01 as primary.
Disable logshipping restore job on Server03 related to Server01 as primary.

Perform AlwaysON Availability Group “PROD_LSTNR” failover from Server01 to Server02. Verify to make sure databases are synchronized and no errors related to AlwaysON.

Now configure logshipping from Server02 to Server03
– Connect to SSMS using SQL Server instance name as Server02.
– Configure logshipping from Server02 to Server03 using steps mentioned above under section “How to Setup Logshipping between two servers?”.
– Make sure logshipping is working fine by checking new backup job on Server02 and new copy and restore jobs on Server03 which are related to Server02 as primary.

From now onwards, you can failover AlwaysON Availability Group from Server01 to Server02 or vice-versa and at same time switch the logshipping to be performed from Primary AlwaysON AG database to Server03, by disabling the logshipping jobs related to AlwaysON AG secondary server.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)

 
1 2