More database performance tuning.

Cleaning up extra indexes, and implementing parameterized queries.

I don’t know how many times I have had to explain the difference between a clustered index and a non-clustered index.  In the end it is all good. Better performance, faster software, and happier customers.

Query SQL Server 2008 Total Memory Usage

The quick and easy way to determine how much memory your SQL Server is using is to look at the Task Manager on the server and find “sqlservr.exe” in the process list, and look at the “Mem Usage” column.

But what if you are not on the server, or if you want to grab this information programmatically from an application, it is not so obvious.  After 15 different Google queries, and many articles saying to just use DBCC MEMORYSTATUS, I finally tracked it down.

The problem with DBCC MEMORYSTATUS, is that it returns nearly 500 values about every aspect of memory possible on the SQL Server.

I finally discovered that if I take all of the output from DBCC MEMORYSTATUS, and insert it into a temporary table, I can then query for the one line of information that I need.  The total memory used, that matches what the task manager shows is called “Working Set”.   The following query returns the same memory usage numbers as the task manager shows.

CREATE TABLE #tmp
( row_id INT IDENTITY PRIMARY KEY,
name VARCHAR(100),
VALUE BIGINT );

INSERT INTO #tmp EXEC ( 'DBCC memorystatus' );

SELECT TOP 1 name, VALUE / 1024 'KBUsed'
FROM #tmp
WHERE Rtrim(Ltrim(name)) = 'Working Set';

DROP TABLE #tmp;

 

From here I was able to call this from my C# code as follows:  (conn is of type SqlConnection and was already open and connected).

// get the total memory used by the SQL Server
SQLStatement = @"CREATE TABLE #tmp
( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT );
INSERT INTO #tmp EXEC ( 'DBCC MEMORYSTATUS' );
SELECT top 1 value / 1024 'KBUsed'
FROM #tmp
WHERE RTRIM(LTRIM(name)) = 'Working Set';
DROP TABLE #tmp;";

SQLDataAdapter = new SqlDataAdapter(SQLStatement, conn);
dtResult = new DataTable();

SQLDataAdapter.Fill(dtResult);

foreach (DataRow drRow in dtResult.Rows)
{
tbServerMemory.Text = drRow["KBUsed"].ToString();
}
SQLDataAdapter.Dispose();

This gives you a quick way to check the amount of memory being used by the SQL Server without being on the server or using the Task Manager.

How much Procedure Cache memory does one SQL Statement take up?

I recently have had the opportunity to work on performance tuning on Microsoft SQL Server 2005. Recently I have been doing more with performance tuning on Oracle. The following is something that I have discovered to be very similar between Oracle and Microsoft SQL Server, but the names are different between the two systems.

So this brings me back to the question of how much procedure cache memory does one SQL Statement take up? The answer is either very little memory, or all of the memory available to the cache, depending on how you do it.

Take the following query as an example.

SELECT column1
FROM table1
WHERE id = ???;

 

Where ??? represents the actual id number being passed into a query. For instance:

SELECT column1FROM table1
WHERE id = 7;

SELECT column1
FROM table1
WHERE id = 928;

SELECT column1
FROM table1
WHERE id = 12341;

Now compare the above queries to the following parameterized query:

SELECT column1
FROM table1
WHERE id = @ID_TO_FIND;

Where @ID_TO_FIND is a parameter that gets set at runtime.

To start with we need to understand how SQL Server handles the processing of queries. There are 2 steps to running a query, the first is to compile the query (also known as creating the plan), and the second is to run the query. The process of creating the plan (compiling) is very expensive, so SQL Server keeps a cache of these compiled queries around. If SQL Server finds an already compiled query, it uses that, otherwise it is forced to recompile the query.

The difference between these two calling conventions is astounding. In the first calling convention where you pass hard coded strings in each time, the SQL procedure cache will have n unique copies of the first query format cached, where n is the number of unique id parameters that are passed in.
In the second calling convention where the SQL statement is parameterized and is not changing each time, then we will have 1 copy of the SQL statement in the procedure cache, and this one cached copy is reused over and over again.

Given that the amount of memory for the procedure cache is limited (which it is), and that you could have hundreds or more queries running on your SQL server, and for each of those queries was not parameterized, you would multiply it by the number of unique parameters passed into it, you could easily in a day have hundreds of thousands of un-necessary plan compiling being done on your SQL Server.

Without using parameterized queries, you are just wasting the Procedure Cache. If you are using parameterized queries, then your system will run faster, and scale better.

If you are using Oracle, you have the same concept, but it is called Bind Variables instead of Parameterized Queries.

Database Tuning at Emergency Reporting

This week I started working at Emergency Reporting doing database performance tuning and scalability work.

Currently this is a half time gig focused on SQL Server database performance.  Fun stuff.