SQL Server 2008 – Resource Governor Questions

Published On: 2008-01-12By:

The engineers from Microsoft PSS has posted some excellent information on thier blog about SQL Server 2008’s Resource Governor.

It’s some pretty hard core information but when it comes to SQL 2008 it’s going to be some useful information to have.

Denny

 Update 2008.01.14: Now with correct URL.  Sorry about that.

Contact the Author | Contact DCAC

New Article: Determining SQL Server database storage requirements

Published On: 2008-01-11By:

SQL CLR: The What, When, Why and How.

Published On: 2008-01-10By:

There are two camps when it comes to SQL CLR.  The DBA camp, which says don’t use it, it’ll kill your SQL Server and the Developer camp which says that it will save you loads of time and that you should use it for everything.  These two different camps also represent the different messages which Microsoft is giving out as well.  I highly recommend that DBAs sit in on a Microsoft developer track session and that developers sit in on a DBA track session some time.  You’ll see a wide difference in the message about CLR in the sessions.

SQL CLR while powerful can not save the world as we know it from T/SQL.  SQL CLR has it’s uses, and it has times when it is the wrong option.  It can most definitely not do everything.  First of all, you’ll notice that I call it SQL CLR not just CLR.  This is because only a subset of the .NET CLR is supported by SQL Server.  This subset it called the SQL CLR.  The official list of supported libraries can be found here.  If it isn’t on that list it’s not officially supported by Microsoft.  What do I mean by supported?  Well in basic terms if you are using a .NET CLR library which isn’t on that list and you start having performance issues, Microsoft can require that you remove the CLR code from the SQL Server before troubleshooting the issue.  The official support policy from Microsoft can be found in MSKB article 922672.

Now with all that said what exactly can SQL CLR functions and procedures be used for?

SQL CLR code is extremely powerful, and when it comes to some kinds of work can be much faster than T/SQL code.  Some examples are advanced math, string manipulation, string searching, pattern matching, etc.  Places where SQL CLR will be slower than native T/SQL will be when general data manipulation and data searching.  When writing SQL CLR functions and procedures do remember that if the .NET code has to go back to the database for any reason that data access will not be slower than doing the data access natively within T/SQL.  If nothing else you have to account for the additional time to connect to the SQL Server, check credentials and move into the correct database.  All of which takes time.  When updating records from .NET code you have to process the records one at a time.  While .NET is great at processing records row by row, SQL isn’t.  SQL will be very slow when it comes to actually processing the updates as it is optimized for record set processing not row by row processing.

Now that I know what I can do with it, when should I?

When deciding to write a .NET SQL CLR function or procedure you first need to decide; Is the SQL Server the right place to do this?  Often times the front end, or middle end (if you have an N tier application) may be a better place to do the work.  For example formatting phone numbers via a .NET function should be done on the front end, not in the database.  Encrypting a credit card number should be done in the middleware or the SQL Server (if you have a middleware layer do it there).  You probably don’t want to install the encryption method and certificate that you use to encrypt data on the end users computer.  The general rule that I like to live by is to put as much .NET code outside of the SQL Server as possible, either in the middleware or in the front end.  Now there are also times when putting the code within the SQL Server is the right place to put it.  Say that you are doing a data warehouse load and you need to parse the text of a NVARCHAR(MAX) field looking for specific key words or phrases, and if these key words or phrases exist you need to add rows somewhere else.  T/SQL may not be the best option for this.  A Full Text search will be inefficient as you will have to search the entire table every time.  A regular T/SQL LIKE command will be very slow as indexes can’t be used, and searching through large pieces of text takes SQL a lot of time.  But if you use a SQL CLR function this can be done with very little CPU time using what are called Regular Expressions.  Regular Expressions are a basic .NET function which allows you to quickly and easily search a block of text.  (Since I’m not a .NET programmer I’m not going to dive into using regular expressions, however here is an MSDN article on the topic.)

Other excellent uses of SQL CLR would include procedures or functions which involve advanced mathematics functions which SQL Server does not include.  These functions could be easily put into a SQL CLR procedure or function and executed within the .NET environment with the result then being used within the SQL Server.  While normally I would recommend putting this in the client tier or middle tier if the function or procedure was needed for in row processing of a query then the SQL Server may be the right place for the CLR code.

While the SQL CLR isn’t the end all solution that some people were looking for, and want it so desperately to be, when used correctly it can be an extremely powerful tool.  But it must be used carefully as when used incorrectly it can hamper performance of your SQL Server.

Denny

Contact the Author | Contact DCAC

XML in the database. What’s the big deal any way?

Published On: 2008-01-07By:

So, we’ve had the ability to use XML within the SQL Server databases since SQL 2000 came out.  What’s the big deal you ask?  How can I use it to make my life easier you also ask?

When I first started looking into this whole XML thing I was thinking the same thing.  How is this blob of data going to make my life easier?  Well when used correctly it most certenly can.  In SQL Server (up through SQL 2005 at least) we don’t have a way to pass more than one record into a stored procedure.  If you have an order entry system and the custom wants to order 3 items you have to run three commands on the SQL Server, doing three inserts one at a time.  If you instead passed those three items from the UI to the database as an XML document you could then process all three items in a single command thereby improving performance.

declare @XML XMLSET @XML = '<ROOT><Customer ID="12">
<Order ID="7498">
<Item ID="415" Qty="1" />
<Item ID="87547" Qty="2" />
<Item ID="4125" Qty="1" />
</Order>
</Customer>
</ROOT>'
DECLARE @hDoc INT
exec sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML (@hDoc, '//Customer/Order/Item')
WITH (CustomerId INT '../../@ID',
OrderId INT '../@ID',
ItemId INT '@ID',
Qty INT '@Qty')
exec sp_xml_removedocument @hDoc

As you can see by the 0utput we have a table which we can process in bulk.  Instead of running a sample piece of code if we were to build this into a stored procedure we could do something like this.

Here is our LineItems table.

CREATE TABLE LineItems
(OrderId INT,

ItemId INT,
Qty INT,
ItemPrice NUMERIC(6,2))

And here is our procedure which adds the XML data to the LineItems table.  In a production system business logic would need to be added to ensure stock on hand, and to return estimated ship dates to the client.
CREATE PROCEDURE usp_AddLineItems
@Items XML
AS
DECLARE @hDoc INT

exec sp_xml_preparedocument @hDoc OUTPUT, @Items


INSERT INTO LineItems
SELECT OrderId, ItemId, Qty, ItemPrice
FROM OPENXML (@hDoc, '//Customer/Order/Item')
WITH (CustomerId INT '../../@ID',
OrderId INT '../@ID',
ItemId INT '@ID',
Qty INT '@Qty',
ItemPrice NUMERIC(6,2)
'@ItemPrice')
exec sp_xml_removedocument @hDocGO

Since we don’t want to create a web based order system for this little demo here we can run the procedure and verify the output.

declare @XML XMLSET @XML = '<ROOT><Customer ID="12">
<Order ID="7498">
<Item ID="415" Qty="1" ItemPrice="12.95"/>
<Item ID="87547" Qty="2" ItemPrice="16.25"/>
<Item ID="4125" Qty="1" ItemPrice="8.25"/>
</Order>
</Customer>
</ROOT>'
EXEC usp_AddLineItems @XMLGO
SELECT *
FROM LineItemsGO

As we can see this is much more efficient than having to run three insert statements as they would require at least three rights to the disk, plus how ever many reads are needed to do the business logic. By using the XML we can do it all with a single write to the disk, and a single set of reads for the business logic. In this example we have reduced our disk activity by 33%. If we were processing 10 line items we would have reduced our disk activity 90%. And since disk performance is usually the bottleneck of any database reducing the disk IO any way we can is a good thing.

Denny

Contact the Author | Contact DCAC
1 408 409 410 411 412 418

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP