SQLDBA- Get the table sizes in MB

Below query  will help to fetch the tables sizes in a data base, 

For the below query  you have to  pass the DB name from which DB table size we want know. 

USE <DB Name>   -- Pass here you DB name 

GO

SELECT 

    t.name AS TableName,

    s.name AS SchemaName,

    p.rows,

    --SUM(a.total_pages) * 8 AS TotalSpaceKB, 

    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,

    --SUM(a.used_pages) * 8 AS UsedSpaceKB, 

    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 

    --(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

FROM 

    sys.tables t

INNER JOIN      

    sys.indexes i ON t.object_id = i.object_id

INNER JOIN 

    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN 

    sys.schemas s ON t.schema_id = s.schema_id

WHERE 

    t.name NOT LIKE 'dt%' 

    AND t.is_ms_shipped = 0

    AND i.object_id > 255 

GROUP BY 

    t.name, s.name, p.rows

ORDER BY 

    TotalSpaceMB DESC, t.name 

-- Get database size (replace 'YourDatabaseName' with the actual database name)
EXEC sp_spaceused;

-- Get table size
EXEC sp_spaceused 'YourTableName';

-- Assume the output from sp_spaceused is in KB for simplicity
-- Calculate the percentage (replace with actual values from the output)
DECLARE @databaseSizeKB DECIMAL(18, 2) = 1024000; -- Example: 1000 MB = 1024000 KB
DECLARE @tableSizeKB DECIMAL(18, 2) = 102400; -- Example: 100 MB = 102400 KB

SELECT (@tableSizeKB / @databaseSizeKB) * 100 AS TableSizePercentage;

Important Notes:

• The sp_spaceused procedure returns data in kilobytes (KB) by default.
• You can adjust the calculation based on the actual units returned by sp_spaceused.
• For more precise calculations, you might need to consider the data and index sizes separately, as they contribute differently to the overall table size. 

SQLDBA - Lock Pages In memory (LPIM) on windows server.

Lock Pages In memory:

Ø  Lock Pages in Memory (LPIM) is a Windows policy that prevents the system from not performing data paging to virtual memory on disk during memory pressure.

Ø  LPIM locks your data in physical memory to improve the system performance and can be very helpful for SQL Server.

Ø  To use this for SQL Server, you must enable this setting for your SQL Server instances.

Ø  If you haven’t enabled it and you are seeing performance degradation or memory paging on your server then you should enable this setting by adding the SQL Server service account to this Windows policy.

Ø  You will see error 17890 in the SQL Server error log file if your system is facing memory paging issues and this setting may help you reduce these errors in a significant manner.

 

Understanding Lock Pages in Memory Configuration

Ø  Lock Pages in Memory is a Windows policy that determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk.

Ø  Locking pages in memory may keep the server responsive when paging memory to disk occurs. It means SQL Server will use memory as much as it requires, and it will not be released to the OS in normal circumstances.

Ø  This behavior may increase the system and SQL Server performance if there is any memory pressure on the system.

 

Check Lock Pages in Memory Configuration

There are multiple ways to check whether this setting is enabled or not for your SQL Server instance running on a Windows server.

The first way to check this setting is by executing the below T-SQL statement. If output of “locked_page_allocations_kb” shows 0 it means this setting is disabled and not being used for SQL Server.

--Check Lock Pages in Memory

SELECT a.memory_node_id, node_state_desc, a.locked_page_allocations_kb

FROM sys.dm_os_memory_nodes a

INNER JOIN sys.dm_os_nodes b ON a.memory_node_id = b.memory_node_id

 

We can see the output shows 0 which means this setting is not enabled for SQL Server.

The second option to check and verify whether LPIM is enabled or not for a SQL Server instance is by looking at the SQL Server error log file. As we know, when SQL Server starts it logs system related information in the error log file as informational messages. 

I checked my SQL Server instance and saw the below information that shows lock pages in memory is not enabled because the error log shows that SQL Server is “using conventional memory in the memory manager”.

The third option is by looking at the property window for the Lock Pages in Memory Windows policy. You can see there are no accounts added here. Ideally if this setting is enabled, then the SQL Server service account must be added to this Windows policy for SQL Server to use this feature.

 

The fourth option to check lock pages in memory by using the DMV sys.dm_os_sys_info. This DMV shows information about the memory model configuration of SQL Server which lets us know about lock pages in memory for the instance of SQL Server. Run the below command to get the output of the memory model.

--Check Lock Pages in Memory

SELECT sql_memory_model, sql_memory_model_desc

FROM sys.dm_os_sys_info

The below output shows that the memory model being used is conventional and not locked pages in memory.

You can see there are two columns in the above output.

• The first column “sql_memory_model” specifies the memory model used by SQL Server to allocate memory. There are 3 values of this column.

• 1 means “Conventional Memory Model” is being used

• 2 means “Lock Pages in Memory” is being used and enabled

• 3 means “Large Pages in Memory” is configured

• Similarly, the column “sql_memory_model_desc” specifies the description of the first column that I just explained above. You can see, I got a value of 1 which is “CONVENTIONAL”.  Conventional is the default when the SQL Server service account is not configured for Lock Pages in Memory

 

Enable or Disable Lock Pages in Memory Configuration

We have checked for locked pages in memory for a SQL Server instance running on Windows server and we can see it’s not enabled in the above sections. Here, I will show you how to enable lock pages in memory for a SQL Server instance on a Windows server.

Note: You must have system administrator privilege. Also get the SQL Server service account details before going ahead with the steps. You can get it from the SQL Server service logon tab in SQL Server Configuration Manager.

Go to start menu on the Windows server,

 click Run and type gpedit.msc and press enter to launch the Windows local group policy window.

The below screen will appear after you press enter.

You need to expand Computer Configuration node and then expand Windows Settings. Then expand Security Settings, here you can see the Local Policies folder. Expand Local Policies and click User Rights Assignment as shown below.

Once you click on the User Rights Assignment folder, you can see all the Windows policies on the right. Next, search for the Lock pages in memory policy on the right. You can see this setting is not enabled because there is no account added to this policy as shown in the below screenshot.

Double click the Lock pages in memory policy to open its properties window. There are two tabs in this window. Click the first tab Local Security Setting and then click Add User or Group to add the SQL Server service account.

Once you click the button, you will get the below screen to select the SQL Server service account. In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server service account.  I entered the SQL Server service account which is recognized by Windows and then clicked OK.

Once you will click OK, the account will display in Local Security Setting tab as shown below. Click Apply and OK to save and close this window.

Now, you can see the SQL Server service account has been added to the Windows policy for Lock pages in memory as shown below.

The final step is to restart the SQL Server service for this setting to take effect. Launch SQL Server Configuration Manager and restart the SQL Server service as shown below. Lock Pages in Memory should now be enabled after the SQL Server service is restarted.

Validate Lock Pages in Memory Enablement

Now we will validate this setting by doing the same steps we did above.

Below we can see the T-SQL script now shows a memory value for locked page allocation.

In the SQL Server error log, it now shows “Using locked pages in memory manager”.

I also executed the below T-SQL script and it now shows the sql_memory_model value as 2 which is “LOCK_PAGES”.