Where did my SQL Server Memory go?

In managing one of my servers I had to make sure everything was running the way it should. I am working on fairly big databases (in the order of 10s of gigabytes) and I wanted to make sure SQL is using all the memory it has available.

The server I am working on is an x64 windows 2003 enterprise running SQL 2005 enterprise 32 bit. The server is also equipped with 12GB of ram.

Whenever I would look into the task manager or the performance monitor the only memory allocation I see for SQL server is around 202 MB which makes no sense.

If you look at the screen capture enclosed you can see the memory allocation for SQL server.

I did some research on the subject and found the answer. SQL has a dynamic management view called sys.dm_os_memory_clerks. This view described in http://technet.microsoft.com/en-us/library/ms175019.aspx has detailed memory allocation information for SQL server. When you examine this view you can see a column that details AWE memory allocations that are detailed in the awe_allocated_kb column, in my case this was summed to the 10GB I assigned to the server. There are other useful rows columns in the view and they are documented in the technet article mentioned here.

One useful query i run constantly:

SELECT
SUM(awe_allocated_kb)/1024 AS [AWE_MEM_MB],
SUM(virtual_memory_reserved_kb)/1024 AS [VM_RES_MB],
SUM(virtual_memory_committed_kb)/1024 AS [VM_COM_MB],
SUM(shared_memory_reserved_kb)/1024 AS [SM_RES_MB],
SUM(single_pages_kb )/1024 [SNG_PAGE_MB],
SUM(multi_pages_kb )/1024 [MLT_PAGE_MB]
FROM sys.dm_os_memory_clerks

This returns the memory allocation totals in megabytes.

Running C# and VB.Net Code on Microsoft SQL Server - Slides code and database

Thanks for coming to the November PSSUG Meeting at Microsoft.
All and all it went pretty well with a couple of Visual Studio crashes, SQL server acting funky but that comes with the territory.
As promised the code is available by clicking below:
Code, database and slides from Running C# and VB.Net Code on Microsoft SQL Server talk
I had to remove the ANTLR part of the demo since there are IP issues involved.
To get this running extract the zip file to drive c:\
it will create a folder c:\SQL_CLR
Attach the database in c:\SQL_CLR\db to sql server
You can look at a word document in c:\SQL_CLR\powerpoint\ for instructions to run the demo.
The slides are in c:\SQL_CLR\powerpoint\ also.
To run the solution open:
C:\SQL_CLR\SimpleFunctions\SimpleFunctions.sln
Good luck and let me know if you have any questions!
Sagi

Updated Code and Slides for the SQL CLR Integration Talk

I updated the code and the slide for the SQL Integration talk that I gave in Code Camp 2007.2 in Philadelphia.
The slides code and slides are available here:
http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip
I added a readme file that explains how to run these samples.
There are samples for:
-SQL Stored Procedure written in C#, it uses return parameters, the message channel, internal connections to the database.
-User defined Aggregate function in C#
-Table valued function in VB.Net
-Scalar Values functions in C# and VB.Net
And a couple more

Thanks for coming to my talk in code camp!

Thanks for coming to my talk in code camp.
I will post the revised code on the blog tomorrow - need to run to a wedding right now.
There were serveral examples I didn't have a chance to go through but they will be in my code and slided that will be posted - tomorrow.
Enjoy the rest of the weekend!

Code for Philly Code Camp 2007.2

The code for my session in Code Camp is available here:
http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip
If you want to run the samples you will need SQL server 2005 and Visual Studio.Net 2005.
The slides are not yet ready but they will be posted here before the talk.

Code Camp 2007.2 Running C# and VB.Net Code on Microsoft SQL Server

I will be speaking at the Philly Code Camp 2007.2 on the subect of integrating C# and VB.Net Code on MS-SQL server 2005.
I will post my slides and code files here in a couple of days.
This is a link to the event on the PhillyDotNet website:
http://www.phillydotnet.org/Default.aspx?tabid=598
Abstract for the talk
SQL server 2005 allows .NET code to be run as functions, stored procedures, aggregate functions and other objects. In this session we will explore the integration of the .Net CLR with SQL server. We will go over the implications of running managed and unmanaged .Net code within SQL server 2005. We will talk about some useful applications for this type of integration and then we will get our hands dirty. We will create several objects in Visual Studio and deploy them in SQL server. Time permitting we will either go into debugging or informal performance testing by comparing .Net functions to native T-SQL code. This talk is geared towards DBAs and programmers alike. The code and slides will be posted ahead of time at http://blog.shkedy.com (check the night before the talk). If you are interested in running the code you will need SQL server 2005 and Visual Studio 2005 although it is not required in order to benefit from the session.

Configuring ASP.Net 1.1 on IIS 6.0 in x64 versions of windows Server

32 vs 64 bit versions of windows server
The 32Bit version of windows 2003 does not take full advantage of 64 bit hardware. We got a double Xeon machine with windows 2003 server R2 x64 which is the 64 bit version of the OS. The advantages of the OS is the increased memory allowance (32 bit only allows 4GB form which .Net can only use 2GB, with the 64bit you can go up to 32GB of memory which makes a lot of sense for a web server.
The problem
By default iis 6.0 is running as a 64 bit host which means it will not run 32 bit applications.
ASP.Net 1.1 was never ported as a 64 bit app (2.0 was).
There is a way to configure iis to run 32 apps like ASP.Net 1.1: http://support.microsoft.com:80/?id=894435
A couple of things to know - if you allow iis to run 32 bit processes this will affect all applications. In the ASP.Net tab in IIS MMC console will disappear - making it impossible to switch between versions 1.1 and 2.0 for a specific website - this can be done only from the command prompt.
The solution
Since I have a busy web server to manage with many sites I automated the process by writing a batch file that takes 2 arguments; the .Net framework version and the target site id. The scripts Changes the registration for that site.
To make this work - open notepad and paste the following:

@echo off

IF(%1) == () GOTO :MissingArgs

IF(%2) == () GOTO :MissingArgs

IF (%1) == (1.1) GOTO :VER11

IF (%1) == (2.0) GOTO :VER20





GOTO :END



:VER11

ECHO 1.1



@echo on

c:\windows\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis -s /w3svc/%2/root

@echo off

GOTO :END



:VER20

ECHO 2.0

@echo on

c:\windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -s /w3svc/%2/root

@echo off

GOTO :END





:MissingArgs

ECHO Usage change_asp_ver version sitenumber



:END

ECHO QUITTING

PAUSE

Save the file as change_asp_ver.bat in a directory you can recall like c:\scripts\
To run the script you will need to find out the site id that you wish to change. Open the IIS MMC console click websites and you will see the site identifier on the right.

If you want to change site id 1234 to version 1.1 and you saved the file in the scripts directory you will type:
Start->run->cmd (enter)
cd c:\scripts\ (enter)
change_asp_ver 1.1 1234 (enter)

You might need to change some of the paths in your script if you used non standard directories to install the frameworks.

This is based on a post response to microsoft.public.interserver.iis

ClickOnce Button for ASP.Net 2.0

We have a couple of forms from which we get double submissions by people hitting the submit button again and again. We decided to go with a ClickOnce button that disables itself when the form submits. I saw a couple of solutions that implement it but nothing for ASP.Net 2.0.
I ended up implementing it in myself.

About 5 lines of code placed in the Page_Load event handler:


// Define the name and type of the client script on the page.
String csname = "OnSubmitScript";
Type cstype = this.GetType();
// Get a ClientScriptManager reference from the Page class.
ClientScriptManager cs = Page.ClientScript;
// Check to see if the OnSubmit statement is already registered.
if (!cs.IsOnSubmitStatementRegistered(cstype, csname))
{
String cstext = "if (typeof(ValidatorOnSubmit) == 'function' && ValidatorOnSubmit() == false)return false; else { var myCtl = document.getElementById('" + this.SubmitButton.ClientID + "'); myCtl.value = 'Please wait...'; myCtl.disabled = true;}";
cs.RegisterOnSubmitStatement(cstype, csname, cstext);
}

On the aspx page we have:

<asp:button id="SubmitButton" onclick="SubmitButton_Click" usesubmitbehavior="False" runat="server">


There are 2 gotchas here:
1. Make sure validation takes place and the page validates prior to disabling the button. This is done with JavaScript
2. Make sure the button is not rendered as input type submit otherwise when it is disabled it will not trigger the correct event handler on postback - for that we use: UseSubmitBehavior="False".

Temporary Tables and Dynamic SQL

When programming in SQL on occasion there is a need to generate temporaty tables inside a sproc without knowing the colums or type when the sproc is written. One such situation would be when you want to pivot a table. You can create a cursor over a column result set and create a new column for each value.

When you try to create temporary tables with dynamic SQL you will run into a scoping problem.
for example:

DECLARE @SQL nvarchar(4000)
SELECT @SQL = 'CREATE TABLE #Temp (col1 int)'
EXEC (@SQL)
SELECT * FROM #Temp

This will cause an error:

Msg 208, Level 16, State 0, Line 4
Invalid object name '#Temp'.

The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed.

One solution I have found for this problem is creating the table in the "parent" scope and then just using dynamic sql to modify the table. For this to work a table is created with a minimum set of colums. And then we use the ALTER TABLE statement with dynamic SQL. The Child session has access to the objects created in the parent session so the table can be modified with dynamic sql:

DECLARE @SQL NVARCHAR(4000)
CREATE TABLE #Temp ( id int null)
SELECT @SQL = 'ALTER #Temp ADD Col1 int null'
EXEC (@SQL)
SELECT * FROM #Temp
DROP TABLE #Temp

This table is visible and both columns will show up.

Improving on the ASP.Net Regular expression validator for phone numbers

When I am working on web development I often use the ASP.Net regular expression validator in order to validate phone numbers.
The validation expression for US phone numbers that is built in to Visual Studio expects phone numbers in xxx-xxx-xxxx format.
The problem with that is that you alwas have to preface the prompt for the phone number with a sentence explaining the format and even then people often mess it up and do not understand why their form does not submit.
A good solution for the problem is to wire the onkeyup event that will add the dashes in all the right places.
For example this is in ASP.Net 2.0:

<asp:textbox id="homePhoneTextBox"
runat="server" maxlength="12" onkeyup="phoneFormat(this, event)"></asp:textbox>
In asp.net 1.1 you can do it on the code behind:

in c# this whould be something like:

this.homePhoneTextBox.Attributes.Add("onkeyup", ="phoneFormat(this, event)");

Now all you need to do is handle this event with a JavaScript function that will format the phone number for the user:

function phoneFormat(sender, ev)
{
var theNumber;
var i;
theNumber = '';
for(i = 0; i < thenumber =" theNumber" keycode ="="> 0)
{
theNumber = theNumber.substring(0, theNumber.length -1);
}
if (theNumber.length > 6)
{ sender.value = theNumber.substring(0,3) + '-' + theNumber.substring(3,6) + '-' + theNumber.slice(6);
return(true);
}
if (theNumber.length > 5)
{
sender.value = theNumber.substring(0,3) + '-' + theNumber.substring(3,6)+ '-';
return(true);
}
if(theNumber.length > 3)
{
sender.value = theNumber.substring(0,3) + '-' + theNumber.slice(3)
return(true);
}
if (theNumber.length > 2)
{
sender.value = theNumber.substring(0,3)+ '-';
return(true);
}
sender.value = theNumber;
return(true);
}

Not the shortest function ever but it does the trick.
If you want to grab the file you can do it here:
http://www.shkedy.com/blogFiles/01182007/CustomValidation.js

Alternating HTML table Row Styles with javascript and CSS

In a website that we built for a clients http://www.isoabroad.com/ we had to use alternating row styles for the tables. When I generate tables dynamically using server side code this is pretty easy. I can just specify a different class for each . If the tables are static this becomes a mess. For http://www.isoabroad.com/ one of our web designers had to hand code the classes for each of the rows and each time we had to add or remove a row he had to edit all consecuitive rows.
For another website we were working on I had used a different approach. Using JavaScript and CSS I set a class for the table (altRows). When the page load I look for all the table elements and traverse the rows, setting the different classes for alternating rows. If a row already has a class defined the script will not change it which makes it perfect for header rows etc. I tested the solution in IE, Firefox and Opera and it works :)
The code is listed below:

function alternateRowStyles()
{
var i, j;
var table;
for( i =0; i < document.getElementsByTagName("table").length; i++) { if( document.getElementsByTagName("table")[i].className == "altRows") { table = document.getElementsByTagName("table")[i]; for ( j = 0; j < table.rows.length; j++) { if (table.rows[j].className == "") { if (j%2 == 0 ) { table.rows[j].className = "oddClass"; } else { table.rows[j].className ="evenClass"; } } } } } }
You can see it in action at http://www.usnetcare.com

If you want to download the code including a function that will wire the function to the onLoad event of the page you can do so at:
http://www.shkedy.com/blogfiles/01172007/formatting.js

Creating GUIDs with Client Side JavaScript

I needed to generate GUIDs (globally unique identifiers) for a web project I was working on.
The only solutions I could see for that task involved evoking an ActiveX control which makes it really useless for general purpose. I ended up writing a short javascript that will achieve this task:

function generateGuid()
{
var result, i, j;
result = '';
for(j=0; j<32; j++)
{
if( j == 8 || j == 12|| j == 16|| j == 20)
result = result + '-';
i = Math.floor(Math.random()*16).toString(16).toUpperCase();
result = result + i;
}
return result
}