SQL Server Memory Usage - Task Manager

Another question I have answered 100s of times to Non SQL folks. "SQLServr.Exe is consuming 80% of memory. Can you please help to reduce the memory utilization to less than 50% and check whats the problem with SQL ? "

If you are a DBA, I guess you would have faced the question many times. Let me explain the response to such a question in the post below.

              Usually, SQL Server utilizes as much as RAM that is available on the server. This behavior is not a bad thing and has been specifically designed to be so. Initially, when a SQLServr.EXE started ( ie., when SQL Service is started ) , SQL Server consumes just a few MB of RAM. As the SQL Server starts getting utilized or when queries are getting fired and result sets are returned, the memory utilization starts increasing. When a query is executed, SQL Server retrieves data pages from the disk, loads them into memory ( RAM ) , performs the processing and returns the result set. As the data pages are getting loaded to the memory, the memory utilization increases. After the query execution completes, SQL Server doesn't release the memory by flushing data pages loaded. SQL Server retains them on the memory so that the next time when the same or similar query is executed, SQL Server need not fetch the data pages from the disk and instead directly fetch it from RAM, which would make the query execution much faster.

           So a Production Server in use for few days or weeks ( depending upon its usage or RAM available), is expected to use most of the memory available in the server. On 64 BIT database server, SQL Server is free to use the entire memory available while on a 32 bit database server, SQL server can use only up to 50% of RAM if AWE is not enabled. Usually, the windows operating system consumes 1 GB of RAM at least. So normally on 64 BIT server, one can allocate, 75 to 80% of RAM to SQL Server, 1 GB for the operating system and the rest for other process on the server. Even if there are no other process on the server, it is better to have a few GB of RAM left so that there is no tussle for memory between SQL Server and Windows.

        One can allocate memory to SQL Server from Management Studio. Right click on the registered server on the management studio, pick Memory tab. Set the amount of memory in KB you would like to allocate to SQL Server Process. Click OK and the maximum limit for memory utilization is set. The above operation doesn't require a service restart.

             Coming back to question, the above details explain why SQLServr.exe on the task manager is having a high memory utilization and why it absolutely normal to have that. If SQL Server's memory allocation is reduced to below 75%, then it can hurt SQL Servers performance as queries will more frequently require to fetch data from disk instead of memory. So it is good to have 75 - 80% allocated to SQL Server and normal to have a 85 to 90% overall memory utilization on DB Server.

Sysprocesses CPU and CPU Percentage - utilization

I am not sure how many of you have faced this situation? I have faced it many many times. Somebody asks you to provide currently running expensive queries or processes. You quickly look up to sys.dm_exec_requests or sys.processes and export the result in a excel and send it to requestor.Mostly you would have included the columns like CPU,Memory,IO to indicate the utilization. Immediately the question people shoot at you is "Can you convert the CPU column to CPU %?. How much CPU% my query consumed?"

Eh..I have always found it bit hard to make people understand it doesnt indicate CPU%. So what does it indicate?

CPU_time column on Sys.Exec_requests and Sys.processes indicates amount of time in milliseconds the process was utilizing the CPU. CPU time is similar to "man hours" calculation used in Corporate world. If two men work for 1 hour each then the man hours spent is 2 hours. Similarly CPU time is amount of time all the processors spent on executing your query.

So does that show Query running time? No. It is "related" to query running time. Why it doesnt reflect exact running time? Because of the following factors.

* A query can be waiting for some other resource as well. The wait, if it doesnt involve any CPU processing then the CPU_time will be zero though the query may be running for long time. For example, if the query is waiting for a lock to be released, it doesnt consume any CPU_time. Other such examples can be latch wait,network wait etc.

* CPU_time is the time spend by all processes just like "Man hours". If the query uses parallel processing or consumes multiple processors, the CPU time returned by sys.processes/exec_requests will be much higher than actual running time of the query. For ex: if the query runs on both the processors p1 and p2, then CPU_time is equal to the sum of time consumed on all p1 and p2. So we can say CPU_time relates to actual execution time but not exactly the same.

So what can we infer from the CPU_time field? We understand that the queries which had the highest CPU_time have made the processor work harder and are likely to have caused a stress on the server.

Calling Function / UDF using linked server

If one needs to call a UDF or System function ( ex: Serverproperty, Getdate(), etc ) on a remote server, then one cannot use the usual 4 part linked server type query.ie., Assume you want to find the Servername, instance name or the current time on a remote sql server then, querying [linked_server].master.dbo.getdate() or [linked_server].master.dbo.Servername('Servername') doesn't work.  
      In such a case OPENQUERY function comes to our rescue. How does that help? Assume we want to get the Servername,Instance name, Version, Service Pack levels of a remote server. Then use the following query

SELECT *

FROM   Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
') 




As you can see, the first parameter is the linked server name. The second paramater is the actual query we want to execute.Within the quotation we can run any query and obtain the results. Openquery sends the query to the remote server, executes it in the remote server and presents the result for processing on the local server. Note that this allows one to perform joins / filters with the local table after the results have been returned by the remote server using OpenQuery function. Ex :


SELECT *
FROM   Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
')
WHERE  remote_server_edition = Serverproperty('Edition') 


The above query prints the result if and only if the local and remote server's SQL Editions were the same. On the queries above we have used additional quotations on serverproperty function as we have to specify quotation with in a quotation.

Also,one can also store the result into a temp/physical table or in a variable. Sample query for storing in temp table

INSERT INTO #temp
SELECT *
FROM   Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
') 


Sample query for storing the remote server's date in a variable

DECLARE @dt DATETIME

SELECT @dt = dt
FROM   Openquery([10.13.221.55], 'Select getdate() as dt ')

PRINT @dt 

So,Openquery provides easy way to access functions esp the system ones like getdate(),db_name(),Serverproperty,DMFs which can't be accessed directly using linked servers.

Inserting UniCode / Special characters in tables

Consider the situation where one needs to store multilingual data / Special characters into a table. For example Chinese characters or Tamil characters. Most of the folks would be aware that one should use NVarchar column instead of Varchar column as Nvarchar column can store unicode characters. This post explains the problem one faces while inserting special characters from a query. Consider the following script to insert some special character data into database


CREATE TABLE #sample
  (
     id       INT,
     spl_char NVARCHAR(500)
  )
GO
INSERT INTO #sample
SELECT 1,
       '我的妻子塞尔伽'
GO
INSERT INTO #sample
SELECT 2,
       'மறத்தமிழன் ' 


The script executes successfully.Let us see the results. Refer to picture below.



We are surprised to see that the special characters are not inserted correctly. We have set the column as Nvarchar but still the special characters appear corrupted. Why?

The reason is when one is expilictly specifying the special character within quotation, one needs to prefix it with the letter N. For ex, while specifying 'மறத்தமிழன்', one needs to specify it as N'மறத்தமிழன்'. The reason is when a string is enclosed with single quotes, its automatically converted to Non Unicode data type or Varchar/char data type. Specifying the letter N before the quotes informs SQL Server that the next string contains unique code character and should be treated as Nvarchar.

Let us modify the script and try using inserting special / Unicode characters.


CREATE TABLE #sample
  (
     id       INT,
     spl_char NVARCHAR(500)
  )
GO
INSERT INTO #sample
SELECT 1,
       N'我的妻子塞尔伽'
GO
INSERT INTO #sample
SELECT 2,
       N'மறத்தமிழன் '
GO
SELECT *
FROM   #sample; 


The result shows that the multilingual characters are now correctly displayed.



So one shouldn't forget to include the letter N while specifying NVarchar or special characters explicitly.

File Group Backups - Intro

What is File Group backup?

Backing up a portion of a database, say a File Group is termed as Filegroup backup.
If you are wondering what are filegroups, then in short a Database's data files can be made of multiple files or groups of files. For more info on File Groups read here

When File Group backups are useful ?
Assume you have very large database with few hundred GBs or a few Terabytes. The database is divided into multiple filegroups, with recently loaded data in one file group and older data in other filegroups. For example, you have a database which maintains a shop's order/transaction details. Assume that the database is designed to have each year's transaction at one filegroup. Then, instead of backing up the entire database, it would save lot of disk space, if one backup's up the current year's file group alone.

How to take file group backups ?

The Screenshot shows how to take file group backup. Fairly straight forward.


What are the advantages of file group backups ?
1) Saves lot of space as you backup only a portion of the backup.
2) Can bring the database online partially and at a faster pace. You can restore only your highest priority filegroup first, bring it online while filegroups havent been restored.
3) If a table or particular filegroup is corrupted then One can restore the filegroup seperately.

Requirements
Any backup strategy is said to work only when one can successfully recover the database. With filegroup backups, there is one basic principle. Each filegroups that is online should be consistent with the rest of the filegroups in the database. Also, the primary filegroup should be restored first for the database to be partially online.

To explain bit more, assume you have a database 'DB' with filegroups FG1,FG2,FG3. All are read write file groups.FG1 is the primary file group.You can bring the database online partially with either of these

* File group backups of FG1 alone
* File group backups of FG1 + FG2
* File group backups of FG1 + FG3
* File group backup of FG1 + FG2 + FG3 ( this becomes completely online )

However one should note that FG2/FG3 backup set should have the same Restoration point as FG1. Restoration point is the time upto which backups where taken for a file/filegroup.

Assume one has taken full backup of a database at 1 PM and transaction log backups at 2PM and 3PM. After that there were no backups taken for the database. Then the restoration point is termed to be 3PM. In other words, the time upto which you are restoring a backup is termed as restoration point.

So in our case, one CANT bring the database partially ( excluding FG1 alone ) online with

* FG1 file group backup taken on 10th Jan 9 PM
* FG2 file group backup taken on 9th Jan 9 PM
* FG3 file group backup taken on 8th Jan 9 PM

Attempts to restore with these 3 backups alone will fail as FG3 contains transactions upto 8th Jan night, FG2 upto 9th night and FG1 upto 10th night.

What CAN work is

* FG1 file group backup taken on 10th Jan 9 PM
* FG2 file group backup taken on 9th Jan 9 PM
* FG3 file group backup taken on 8th Jan 9 PM
* Additional T-Log backups from 8th Jan 9 PM to 10th Jan 9 PM.

T-Log backups from 8th Jan 9 PM to 10th Jan 9 PM contain all the transactions till
10th Jan 9 PM and upon restoration we can bring FG1,FG2,FG3 to the same restoration point.

In short the two most important principles for filegroup backups are

1) Primary Filegroup should be restored first.
2) All the Filegroups should have the same restoration point.

The table below shows the recovery models and Modes at which filegroup backups are useful.

Recover model	Read only	Strategy
Full	No	Full FG backups + Differential + T-log backups
Simple	No	Doesn't work
Full	Yes	Full FG backups for Read write + T-Log backups
Simple	Yes	Full FG backups

On the upcoming posts, I will be explaining various backup strategies and restoration scenarios in detail.