Code Camp 2008.1 - t-Sql to CLR performance comparison testing and Cursor Avoidance Techniques

We had a great session at Code Camp. Thanks to Sebastian Meine who did most of the work for the session. I contributed by brainstorming with Sebastian, coding the CLR components of the project and walking through the code.
We concluded that there is no conclusion...CLR is faster for some things and T-SQL is faster for others. There are some very big limitations to the CLR integration in SQL server - the main one is the 8000 byte limit when serializing custom aggregates and the indifference to order in those functions.
We also played with some neat SQL-2005 enhancements like common table expressions and Cross apply which was new for some of the users.
I will try to get the code from Sebastian and post it here.

Troubleshooting SQL server database mail

I am putting database mail on SQL server 2005 into use more and more these days with my consutling projects and it is making things much easier on me.

It is very easy to trigger emails based on transactions that happen on the database - like payments posted, account goes below a certain balance etc.

It is also nice to use some of the included views to see what is the status of your email messages.

If a message that I send did not reach the recipient I can check:
SELECT * FROM msdb.dbo.sysmail_faileditems
to look for that message.

Once I find it there I can can look at the mailitem_id in the msdb.dbo.sysmail_event_log view.

Happy mailing!

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.