Posts Tagged ‘ Microsoft SQL Server ’

SQL The Memory Hog

I’ve worked with Microsoft SQL Server for a long time. In the context of people who study SQL to a degree I won’t ever find possible (or palatable), I would not refer to myself as an expert. I have had my fair share of issues I’ve had to find resolution for and this one comes up frequently since it’s how SQL installs by default.

You just get your new SQL Server up and running and within a short while of using it, your machine is running unfathomably slow. You manage to get Task Manager open and find that sqlservr.exe is pretty much chewing up all the RAM. “That makes no sense,” you lament. “I don’t even have that much running on the SQL Server yet.”

The reason: By default, SQL Server sets the “Maximum¬†server memory” option to just a shade over 2 billion megabytes (or the largest possible value a 32 bit integer can hold:¬†2,147,483,647). Likely, your server doesn’t have 2 billion gigs of RAM so SQL Server just creeps northward, holding all your RAM hostage, whether you’re using it or not.

Fortunately, this is an easy problem to fix.

Resolution:

  1. Login to SQL Server Management Studio and connect to your server (with high privileges, obviously).
  2. Right click on the server name itself.
  3. Select Properties.
  4. In the Server Dialog Properties window, click Memory.
  5. Change the “Maximum server memory (in MB)” value to an appropriate portion of the RAM on your server.

Before changes:

SQL before

After changes (in a server with 32GB of RAM):

SQL after

Now bear in mind, setting this value too low will naturally cause performance issues for your server. You want to set it lower than the full value of the RAM on the server, but not so low that you’re severely restricting SQL Server’s ability to perform.

This is a first line solution. There may be issues with how your SQL code is written, OS-level performance misconfiguration or more advanced SQL Server configuration issues with paging and so on. But 99 times out of 100, on an initial, default installation of SQL Server, this solved the problem. You may need to test max memory values for what works on your particular setup in order to find the sweet spot. Enjoy.

Advertisements

SQL Server – Last Update and Access Times

I periodically review the usage statistics on my databases to determine what is outdated and no longer used. Here’s the easiest way I’ve found to it:

You’re looking for the sys.db_db_index_usage_stats table. It contains a wealth of information and I recommend having a look at it when you get a chance. But for now, we just want to know when the table was last accessed or updated.

(CAVEAT: The counters/datetimes are reset when the SQL Server service is restarted so the information you get is since the last restart, not the last access/update time historically. This means that if you restarted the SQL Server service 10 minutes ago, it will probably look like none of your databases have ever been accessed.)

How to look at last access/updates for a specific table:

USE [MyDatabase]
GO

SELECT
OBJECT_NAME(object_id) as Table_Name,
last_user_update,
last_user_seek,
last_user_scan,
last_user_lookup, *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('MyDatabase')
AND OBJECT_NAME(object_id) = 'MySpecificTable'
GO

If you’re looking for the last access/update times for the whole database:

USE [MyDatabase]
GO

SELECT
OBJECT_NAME(object_id) as Table_Name,
last_user_update,
last_user_seek,
last_user_scan,
last_user_lookup, *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('MyDatabase')
ORDER BY Table_Name
GO

The system uses all kinds of index operations to keep a count of this information and it’s very handy when you need it. Enjoy.

Advertisements