GET YER DEAD HANDS OFF MY DATABASE

101 LINQ TO SQL Code Samples

2011-01-31T15:39:00.000-08:00

The following page has been heaven sent for quick access to LINQ TO SQL sample code.

If you have trouble like I do memorizing the exact LINQ to SQL Syntax (despite the fact that accomplishing the same thing in t-sql would be second nature) then you should appreciate the link as well.

http://msdn.microsoft.com/en-us/vbasic/bb688085

I stumbled onto this link via this months SqlServerCentral "database weekly" newsletter. The SSC article takes you to a C# version. If you prefer the C# version then you should check here instead of the VB link above.

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Crystal Reports for Visual Studio 2010 Deployment

2010-10-14T11:17:00.000-07:00

There is currently no runtime for Crystal Report for Visual Studio 2010. A quick Google search reveals this work around. In short, go ahead and use the 2008 SP 2 runtime - it should work fine.

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/d085f103-ff2d-2d10-2f9e-ffb14d10ef33?QuickLink=index&overridelayout=true

The one thing this workaround doesn't tell you is that you still have to go into your project references and set "most" of the CrystalDecisions assemblies to the 2008 sp 2 (12.2) version.

If you don't do that your deployed application will give you the following error message:

Could not load file or assembly CrystalDecisions.Windows.Forms with a reference to version 14 .####.##

Here's a quick snapshot of what my VS2010 applications that are using Crystal Reports references look like:

Notice that the "Enterprise" assemblies remain version 14. There is no corresponding 12.2 version and this didn't seem to keep my applications from working. Also, as mentioned in a previous post - don't forget to set cpu for x86 if you are developing on a 64 bit machine.

Installing Crystal Runtime With Your .Net Deployment

2010-08-12T08:32:00.000-07:00

If any of your .Net projects contain Crystal Reports you’ve probably contemplated the confusing reality of deployment. Getting those embedded reports to function on client computers like they do on your development box never seems as easy as it should be. You have options including merge modules, separate runtime installs, and so on – but if your installs are going to be done by folks who are not familiar with your project or by end users whose technical skill is questionable you want your install to deploy as easy as possible.

That’s why for me… , my favorite solution for deployment projects that contain Crystal Reports is Visual Studio’s Custom Actions feature.

Custom Action’s allow you to run a separate executable as part of your install. So if you have the appropriate run time installation from Crystal Reports in hand, you can include it in your Deployment Project. If you don’t have the appropriate runtime installation for Crystal, chances are you can download it here. (At least you could as of this post)

It’s pretty much as easy as this:

1. After you have set up your basic deployment project either from scratch or using the wizard right click on your deployment project and select ‘View File System’. From here you need to add the Crystal Runtime package to the Application Folder (or somewhere else that makes sense to you). Basically we just need to ensure the installer can find it later (as you'll see below).

It's optional, but I would also suggest you set 'Exclude' to true under the file's properties in your deployment project (as shown below). This will keep the runtime package from being copied to the end users app folder after the install is complete - which reduces the total footprint of your application and leaves less executables laying around for no reason. Don't worry, the install package can still access the file even with 'exclude' marked true.

2. Right click on your project again and select ‘View Custom Actions’. You’ll notice that you can add a custom action as part of the “Install” or as part of another process associated with the project such as Un-Install. For now, right click on Install and add it there.

3. After selecting Add Custom Action you will be prompted to select an executable (or script) from files that are already included in your project. Open the file system folder where you placed the runtime (in step 1), highlight the runtime and click ‘Add’.

4. If necessary you can now customize the Crystal Runtime installation with any available command line arguments (under properties). My current version of Crystal (Version 12 SP 2) has various command line options (which you can discover by running the install from the command line with the /? Switch).

Note: For some reason my version of Visual Studio adds the switch "/install" into my argument box. This doesn't do anything (in fact, it keeps it from running properly) so either leave it blank or add a known argument that you've tested.

Two examples of common options you might pass as arguments.

/quiet (Quiet mode, no user interaction)

/passive (Unattended mode - progress bar only)

It’s always a good idea to test your deployment but really, that should pretty much cover it. When the end user installs your package, it will automatically install the Crystal Runtime as well without any additional user effort.

SQL Server 2008 Sparse Column Generalities

2010-06-08T10:30:00.001-07:00

The following is a great guide on using the new Sparse Column feature of SQL Server 2008.

http://msdn.microsoft.com/en-us/library/cc280604.aspx

It seems to me however that this entire article can be boiled down to a much simpler generality.

1. Using sparse columns on bit, tinyint is pointless - smallint is borderline.

2. On the rest of the columns that support sparse your only going to get decent space savings if more than 60% of your values are null.

3. If your getting fancy with the columns, (filestream, replication, indexes, default values, identity etc.) then Sparse is most likely not going to work, or if it does - it probably isn't worth the administrative effort to maintain.

Like all generalities, there will be edge cases that wont apply to my generality. I can only hold so much information in my head however, so it's the general rule I use for deciding whether or not to look deeper into using a Sparse column.

Where Sparse columns really shine is when you run into those non-normal legacy type tables that are as wide as they are long. Tables that we lovingly describe as random in content. If this describes a table you know - that you work with regularly - then you should also look into Column Sets.

In a nutshell, Column Sets return all your Sparse Columns in a well formed XML block. If you have a lot of matrix type queries that need to be performed - it can really be useful. Especially if you can consume it's XML output for displaying query results in an ASP.Net page or if your preparing a JSON/AJAX solution.

Read up on column sets here http://msdn.microsoft.com/en-us/library/cc280521.aspx .

Crystal Reports Error in VS 2008 on Windows 7 64

2010-06-04T14:02:00.000-07:00

Ok, so my last post was way off my usual topics. Probably not too many more posts on here about RV faucets and camping.

This one is more typical of the stuff I run into on a regular basis. Crap I can't explain for a few hours and then finally lightning strikes, there is a eureka moment and then I move on.

A few months ago I upgraded my development machine to Win 7 64. I ran into a couple gotcha's moving my visual studio projects off my old computer but for the most part it was a nice clean transition. That was until this morning when I opened an old project that needed a couple features added. Long story short, the project contains some crystal reports and when I compiled the project I suddenly started getting an error when attempting to view the report.

An error has occurred while attempting to load the Crystal Reports runtime. Either the Crystal Reports registry key permissions are insufficient or the Crystal Reports runtime is not installed correctly.

Well, that's just plain silly - I own the full blown Crystal Developer product and have it installed on my box. What's more is my other projects use Crystal Reports embedded and they run no problem.

So I checked in my list of references to make sure the dll's that were being referenced were the same. They were. I deleted those references anyway and recreated them from scratch. No luck. I excluded the report files from the project and then reattached them using 'Add Existing'. It no worky either. I deleted the VB files related to the Crystal so the engine would regenerate them. That didn't help. I even tried actually installing the runtime package on my machine to see if that would fix anything and all it did was break my Crystal Developer install - (re-installing that was fun). Well, my last hope was that after having to reinstall the full developer product the problem would surely go away. ... well, .... it didn't.

Oh, I almost forgot ... I also discovered during this little adventure. Crystal Reports 2008 Service Pack 3 is out. And no, that didn't fix my error either.

After a few choice words with the cubicle air around me such as Crystal Reports is a flying piece of steamy crap - I finally realized what the issue was. It all comes down to doing a thorough comparison between the ones that work and the ones that didn't. This is what I found.

I had forgotten that a lot of third party tools don't really want to run in 64 bit. Crystal Reports is apparently one of these. Under configuration manager when doing your project build you need to specify X86 as the platform instead of the default 'Any CPU'. My other projects were already set this way because I was pushing the finished product to clients who were all on 32 bit Win XP machines. Boom bam fixed it.

Magento Customization Tricks and RV Faucets

2010-05-27T20:05:00.000-07:00

I've pretty much been a career Microsoft guy - SQL Server, .Net developer,... yep that's me - Mr corporate sell-out. Last year however I was presented with an interesting problem. I was asked to set up a shopping cart system but do it for free - or as close to free as possible. My time isn't free so building one from scratch definitely wasn't the best solution. The client also had an existing web hosting account that he hoped to be able to use. So, now my requirements were free and based on linux, mysql, php.

Now, anyone else and I would have said, "go find some php hippie that'll do it for a Costco pallet

of Doritos and let me get back to my corporate strength big-boy tools". But, the client is a friend of mine. One I would rather keep - so he was warned that his request would require some research on my part and possible an unhappy ending.

Enter Magento.

I don't remember exactly how I stumbled onto it but I started tinkering with Magento. I was encouraged that a lot of big name companies were happy to lend their logos and claim they liked it. There were a few free templates floating around and we found one that fit the bill nicely. It wasn't long before we had the site up and running and I have to admit. I was pretty dang impressed.

Enter Scope Creep

It wasn't long before the client started asking questions like... "can you make it so that the product image changes to match the user option selected?" - and other seemingly simple requests.

If I had one true complaint about Magento it would be there is precious little developer documentation. It's too new to simply pick up a cookbook at the local tech friendly book store.

The core Magento product has a pretty incredible collection of source files. Add any template files on top of that and the file count gets a little staggering. My install had over 11,000 files associated with it. That's a whole lot of javascript, php, classes, objects, etc etc etc that I didn't write and have no intimate knowledge of.

My other major problem with PHP is that I am used to Visual Studio intellisense - you know.... a tool that tells me all the methods, properties etc. of every object at my fingertips. My PHP development is all in notepad - no intellisense.

Enter the two most useful tricks I have found for customizing Magento.

First - a way to discover the location of the code that needs fixin'. Also known as Template Path Hints. Describing it would take too long, so just watch the video in the link. It's the same video I stumbled onto while pondering the problem. Absolutely priceless trick.

Second - a way to discover the methods at your disposal within the source code that Template Path Hints helped you locate. This is probably elementary level knowledge for you PHP / MySql hippies out there. For me, it was a real life saver.

In my case I needed to locate the SKU of a configurable product so I could access the images associated with the simple child product. I saw in the php code a variable called $this. It was obvious from the way it was being used that it probably held the information I needed. While trolling the magento forums for anyone else who had faced this same scenario I found this little block of code.

In short, this prints to screen all the methods within the $this object. I used it as instructed, and sure enough - right at the top of the list was a method called $this->getChildProduct().

So I set a variable equal to $this->getChildProduct() and ran the above code on that variable. Again, right at the top of the list was another method. $child->getThumbnailUrl(). Ok, it might be a simple block of code... but again, priceless.

Oh, and by the way. If you need an RV faucet for your motor home, or fifth wheel - you should really check out Dura Faucet. They'll hook you up.

Cursors and the super hero community

2010-03-11T13:41:00.000-08:00

A few years ago I was interviewing for a position and the question came up... "What is a cursor and does it have value in SQL Server databases?" I gave my definition and then regarding the second half of the question answered... "Every now and then a problem comes up where a cursor is literally the only possible solution. If that's really the case, hey - it's a tool available to you. If you can come up with ANY other way to address the problem however - it's probably a better way". The interviewer smiled and said, "yep, that's how I'd answer too".

To a SQL pro using a cursor is like Superman taking the stairs. It's an affront to the whole super hero community. Who does he think he is taking the stairs like some procedural based dBase language? This is TSQL not FoxPro.

So what are some good techniques to avoiding cursors? Here are a few I've picked up along the way. I'm sure countless others exist and I'd love to get comments with other techniques or approaches.

1. Table Variable, Identity(1,1), and a little creativity

I would say that most cursors I have seen could be replaced with a creative mixture of WHILE loops, temp tables, table variables, and/or user defined functions/sprocs. SQL might be set based but the query language gives us a lot of tools to find and work with individual rows or fields.

Consider the following example. I create a table variable with an identity key that starts at one. This key allows me to then isolate each row one by one in a while loop.

DECLARE @pImportantKey int

DECLARE @pNotACursor TABLE
(
AutoKey int IDENTITY(1,1), -- this is important
ImportantKey int,
EmpName varchar(50)
)

INSERT INTO @pNotACursor (ImportantKey, EmpName)
SELECT ImportantKey, EmpName
FROM Employees WHERE Salary > 250000

DECLARE @pCount int
SET @pCount = 1

DECLARE @pRows int
SELECT @pRows = Count(ImportantKey) FROM @pNotACursor

WHILE @pCount <= @pRows
BEGIN
SELECT @pImportantKey = ImportantKey
FROM @pNotACursor WHERE AutoKey = @pCount

EXEC DoSomethingFancy @pImportantKey

SET @pCount = @pCount + 1
END

2. COALESCE() and Concatenation

Most DB guys have run into a scenario where they have had to express columnar data in a single row. Also known as making long data wide or pivoting. A few of us may have thrown a cursor at the problem.

Most database developers came from programming backgrounds of some kind. We brought foundational knowledge of loops and concatenation with us. So, the cursor may be the first thing we'd think of to solve what comes to mind as a procedural problem. At least that was my excuse when I wrote my first cursor. It's also how I first discovered Coalesce for uses other than finding a non-null value. I then hunted down and killed that first cursor. In the words of Cpl Pearson, Generation Kill... "Look, I'm a man now".

COALESCE(expression [ ,...n ]) takes any number of arguments and simply returns the first non null, so how in the world does it help us here. Let's look at the following code.

Let's say that

Select [Region]
FROM SalesRegion
WHERE EmployeeKey = 12345

produces the following:

Delaware
Iowa
Ohio

Let's run it again using Coalesce()

DECLARE @pResult varchar(max)

SELECT @pResult = COALESCE(@pResult + ', ', '') + [Region]
FROM SalesRegion
WHERE EmployeeKey = 12345

SELECT @pResult

This time around we get the same data only it's been pivoted.

This is how it works... The first time @pResult is null so the second argument (the empty string) is returned by the function. That's what Coalesce does. The empty string is then concatenated with [Region] of the first rowset. ''+'Delaware' results in 'Delaware'.

The second time Coalesce() is called @pResult is not null (it's equal to 'Delaware') so the first argument @pResult is returned (and concatenated with ','). This creates the result 'Delaware,' which is concatenated again with [Region] resulting in 'Delaware,Iowa'

Without using a cursor we end up with the result set:

'Delaware, Iowa, Ohio'

3. RECURSION and Common Table Expressions

Another place you might be able to clean cursors out of your sprocs is by using recursion and the CTE structure. I could eat up a bunch of space providing an example but unlike above where I'm using COALESCE() in a way that may be outside what the engineers originally intended, the CTE structure can clean up cursors when used exactly as it was intended. So, I'll just refer you to the MSDN article Recursive Queries using Common Table Expressions

Step through the sample provided at the bottom and I think you'll get the picture. This could be a great tool for fighting the urge to use a cursor.

Why I always start with a temp table (vs table variable)

2010-03-04T12:28:00.000-08:00

Most people who are serious enough about SQL Server Administration already understand the pro/con matrix of table variable vs temp tables. A decent thread outlining the matrix can be found on MSDN and any number of SQL blogs.

I often find myself leaning towards temp tables for the simple fact that they are easier to script out. I build my select statement as the logic pours for my brain and then simply add a " INTO #tblDestination " before the FROM clause and boom-bam a local temp table ready to be worked with.

Inside I know that in most cases I should be using a table variable for performance and IO reasons but I still prefer to code it out as a temp table just because it's so much easier to write. I don't have to pre-think the table design and struggle to declare it when I haven't fully explored the logic in the queries below that will create it.

Before you judge me as reckless and lazy however... there is still method to my madness.

After having written the procedure and tested its results as sound I go back and THEN give it some thought from a performance (temp vs variable) standpoint.

If I decide a table variable would have been a better solution - no problem. I now have all my fields and datatypes defined in my query logic making it easy to declare the needed table variables using the logic below as a reference. I am careful to name all the tables the same just using a @tblDestination where before I used a #tblDestination.

As a coding rule my " INTO #tblDestination " statements from the first go around are already on their own line (for readability and because I knew they might go away soon) so those are easy to delete. Also, with my table declarations now written it is also much easier for me to add a " INSERT INTO @tblDestination (values)" above each statement that used to contain a " SELECT INTO " statement.

As a final step a simple find and replace completes the conversion from #tblDestination to @tblDestination.

Some people might find this method is not right for them. My high school geometry teacher used to go nuts following my proofs (that were always right, mind you, they just contained a few logic paths that some might consider atypical). Either way, best practice has been met and for me... starting with a temp table works.

SQL Server Migration Assistant 2008 for MySQL

2010-01-29T10:22:00.001-08:00

A couple weeks ago I downloaded the Microsoft Sql Server Migration Assistant for MySQL V1.0. We have a handful of MySQL databases we've been putting off converting for the simple fact that it's always such a pain. This morning however I was going over my zombie plan and realized a database containing the location of my tactical bacon was in MySQL. I need it in SQL Server. I can't put this off any longer, time to try the new tool.

Listen in on my brain patterns as I re-live the experience:

Start by installing SSMA on the same box that was hosting the MySQL tables. Installed fine, add the license file, ok, no problem, (just follow the on-screen prompts).

Alright, this looks very empty, nothing here. Let's click "start a new project"... seems logical.

Really only presented with 2 toolbar buttons, 'Connect to MySQl' and 'Connect to SQL Server' ok let's start with MySQL. Enter port, root, password, all familiar... wait an error message. 'error occurred while collecting data.'

Ok... not very informative. Google search, not much out there... hey, a readme file that came with the SSMA download... maybe that will have something helpful. Documentation says...

----------------------------------------

3.0 Documentation Notes

There are currently no documentation notes

----------------------------------------

Hmmmm, well CTP release... I guess I can't really complain. You gotta admire the Microsoft guys who took the time to enter this in the read-me file though.

I have another MySQL install I guess I'll move the files to it and try again. Back up files, move them to MySQL on another box. (first install was version 5.05.1, the second is 5.1). This time it seemed to connect fine. I wish I knew something more than "error occured" with regards to it not wanting to run on the 5.05.1 box. Possibly an issue with the ODBC driver being 5.1 ???

Ok, now let's connect to SQL Server... fiddled around a little before figuring out that the connect button will not 'enable' until you've put something in the database field. (even though I have no intention of actually using that database - after all, I'm connecting to migrate a database that doesn't exist in SQL Server yet) Sure enough, once in - I can select any database on the server - not really sure why they want me to enter a database before letting me connect. It doesn't even have to be a legitimate database.

So, both servers are now connected, I can browse schema's.... great, now what... I don't really see anything obvious. I've clicked the database I want to migrate. I've added and clicked the database on the SQL side that I want to migrate to. Well, Maybe it's voice activated... move, migrate, shoot, bust a cap, teine (stole that from GI Joe). Ok, no voice activation.

Let's just start randomly right clicking things and see what options appear. Oh, hey 'Migrate Data'... that's exactly what I want. In fact, I just realized if I have the source table selected 'Migrate Data' also appears as a button on the toolbar. Click it, get a message, table XYZ does not exist. Uhm, yes I know, that's why I want to migrate from MySQL.

Hmmm, what else is on the MySQL database right click menu. Hey, 'Convert Schema', ok sure, *click*.

Well, something happened. What happened? Oh, the SQL Server schema in SSMA now has the tables on the metadata explorer. But wait, the SQL Server Management Studio doesn't show them. Refresh, still no, run a query, nope no tables.

Gotta be a way to make that happen. Right click again on the SSMA explorer. Let's try, 'Save to script', sure. That should work..., then I can open the script in the SQL Server Management Studio and run it on the empty Database. That must mean that when I do stuff to the meta-data explorer it doesn't actually change the SQL database. Safe feature but what if I WANT to push my changes to the SQL database.

Wait..., I see another option I didn't notice before... 'Synchronize Database'. Let's try that instead of saving to script. *click*. Something is happening, .... Hey cool, I like how that works - that actually pushed my changes to the SQL database.

Ok, so the empty schema is now on the SQL Server... back to migrating data. Let's right click the MySQL Schema again and try that 'Migrate Data' option again. Lot's of stuff seems to be happening. do dee do de dum do de do .... ok, it's done ... it say's it was successful !! Ok, run some queries via SSMS just to make sure...

IT WORKED ! MySQL database has been migrated to SQL Server ... HOORAY !

Despite no documentation, a couple errors, and having to kind of just figure stuff out on my own... that actually wasn't too bad. I look forward to exploring all the options available under the tools menu.

SQL Query Optimizer

2010-01-11T08:43:00.001-08:00

Quite possibly the best explanation of the SQL Query Optimizer I have ever read. Though I have to disagree with the selectivity of "pretty girls" where city = "Buenos Aires". I've never been to Rio but I've been a lot of other places and BA stays pretty darn high on the list.

http://www.simple-talk.com/sql/t-sql-programming/13-things-you-should-know-about-statistics-and-the-query-optimizer/

Good job Fabiano for writing this in such a way to keep my interest.

PLINQO - Converting a project from LINQ to SQL

2009-10-22T14:24:00.000-07:00

I am still testing PLINQO and getting ready to start publishing my findings in an in depth post but for now I at least wanted to post some gotcha's and "best practice" I discovered while converting my LINQ to SQL project to a PLINQO project.

If you are converting an existing project be aware that PLINQO gives you much more control over the naming conventions used and the default settings do not match how LINQ behaves. This isn't a problem if your building a project from scratch but if you are converting a project like I did it will matter.

The 'table' and 'entity' names are easy enough to fix in code if they change during generation but if the association names do not match, your controls will go crazy when you replace the LINQ dbml with the PLINQO dbml.

LINQ names it associations using a plural format, if you are converting a project I would configure PLINQO to do the same in the DBML template as shown here at the left. (As you'll note I also chose to name my tables and entities as 'Preserve' and 'Mixed' for personal preference.)

My approach when generating the PLINQO files was to leave the existing LINQ context and files in their own folder while I created the PLINQO contexts and files in another. I figured this would allow me to test individual forms one at a time and move DataSources manually from one to the other. Once it was done and PLINQO outputs were generated I could compare the results and make sure the entities etc. in the DBML files were the same.

The data context namespace generated by PLINQO is also customizable in the template. In my approach I purposely gave it a different namespace than the original LINQ context. This made generating the PLINQO files easy and non intrusive and didn't break my project with the simple act of generating files.

With both contexts now available I simply went through the DataSources one by one and replaced the LINQ DataSource with the PLINQO DataSource and then checked for errors.

Using a different namespace did cause me to have to change the context declaration in code as well as the BindingSource.DataSource property on a couple top level tables. Because I was careful to keep the association relationship names the same however everything else switched automatically as soon as the top level BindingSource and datacontext was happy.

The only real show-stopping issue I ran into during conversion was that PLINQO does not generate code for User Defined Scalar Functions. To accomodate the switch to PLINQO I had to write some custom code to take the place of the missing UDFs but it was simple code and easily replaced. (I checked and this was already submitted as a feature/bug request on the PLINQO code site. I added my name to the list of "me toos". I expect it won't be long before PLINQO generates code for functions as LINQ does.)

So, there you go... these are the steps I used and it worked out great.

I will also mention that I did some preliminary SQL Profiling sessions against my new project and the original LINQ to SQL project and the profiles were virutally identical. Same number of batch requests, same query text etc. Performance was basically the same. Now, I was not surprised by any of this since I haven't really used any of the enhanced PLINQO features which would be the true purpose of switching a project over. The good news is that the baseline is the same which is what I was hoping for.

Stay tuned as I dig a little deeper.

PLINQO

2009-10-19T10:14:00.000-07:00

While DBA by title and trade I do end up writing code from time to time.

Last week I was working with a Linq to SQL project I had developed over the summer to address a small business need. I had made some table changes and needed to refresh a couple tables/views. As I began testing the new tables I started getting foreign key violation errors. I knew immediately what was wrong. Linq to SQL had replaced my deleteonnull settings when I refreshed the context… time to open the context with the XML editor and put all my settings back by hand (sigh).

This got me thinking critically about Linq to SQL. I have long since resigned myself to the fact that Microsoft is moving on to other pursuits and leaving Linq to SQL behind. I have accepted the fact that whatever strengths and weaknesses it has, I will have to deal with them as is if I continued to use L-T-S.

It HAS been awhile though, I decided to check the blogosphere and Microsoft websites to see if any recent news had appeared to give me renewed hope in further development. Maybe Microsoft was changing their tune. I knew the “entities” effort so far wasn’t winning Microsoft any friends. Maybe they’ll develop LINQ further after all… hey, one can hope.

While I didn’t find any information that Microsoft is going to put focus back on LINQ I did stumble onto a third party approach called PLINQO (from the same folks who bring you CODESMITH). Now, I was always a big “Price is Right" fan so naming a product PLINQO is a marketing gem (if you’re trying to catch my eye at least). More importantly however I was excited to see someone out there liked LINQ enough to put effort into furthering its development even though Microsoft is abandoning the effort.

I immediately downloaded the trial version and started playing around. I have never used CODESMITH but getting up to speed on the product in order to use PLINQO turned out to be no big deal.

PLINQO UNBOXED – FIRST IMPRESSION:

The biggest project I have used LINQ to SQL on so far is an insurance claim database used by a local insurance CO-OP of about 30 school districts in the Pacific Northwest. I decided I might as well go big on my first test of PLINQO and see how that LINQ project would behave as a PLINQO project.

In under 15 minutes (45 minutes if you include the time it took to watch the instructional video on implementing PLINQO) I had replaced the data context, entities, and code behinds generated by LINQ with those generated by PLINQO.

More important than the time used implementing PLINQO, an afternoon of bounds testing and walking through the original user stories and scripts produced no issues whatsoever. In short, the move from LINQ to PLINQO came off without a hitch.

With that done, I’ll start testing the “enhanced” features of PLINQO. While the feature list is impressive these are the PLINQO claims that I am most interested in.

Preservation of context changes across data table refresh statements (I hate doing work over, and this frustration led to me originally finding PLINQO)
Batch deletes and updates (batch anything get’s me excited)
Reduction of SQL round trips (I am an SQL Profiler Addict, PLINQO will be watched and my results will be posted)
Change Auditing (if this solves a feature request on my insurance database the folks at PLINQO may have a big ‘ol wet kiss from me in their future)

If you are interested in taking a look at PLINQO you can find it here: http://plinqo.com

Documentation here: http://plinqo.com/Documentation.ashx

Feature requests here: http://plinqo.com/Feedback.ashx

My next post will report the final findings of my test. If PLINQO does what it claims it will, the PRICE WILL BE RIGHT.

Sorry, I couldn't help myself. The product is called PLINQO for heaven's sake. Cut me some slack... PLINQO / PLINKO ... they are phonetically identical.

REORGANIZE - WHAT IT DOESN'T DO

2009-09-08T10:39:00.000-07:00

Many DBAs use a level of fragmentation as their guide to deciding when to ‘rebuild’ an index or ‘reorganize’ it. For example, they will have on their cheat sheet, “25/75 meaning… up to 25% fragmentation (reorganize), any higher (rebuild)”. The numbers vary from pro to pro, some as wide as 80/20 others as close as 60/40. They all agree on one thing however. There are circumstances when ‘reorganize’ won’t reduce fragmentation levels sufficient to their expectation (or at all). Why doesn’t it? While most DBAs seem to understand that REORGANIZE won’t always do the trick, there seems to be precious little information floating the web as to why?

Obviously, for the seasoned DBA there are several factors at play in the decision to reorganize an index as opposed to other options for tuning index performance. Not the least of which is how busy the table in question may be and what the related service level agreements (SLA) are. But, for the sake of this discussion we’ll just stick to why ALTER INDEX REORGANIZE doesn’t fix fragmentation as well as we sometimes wish it would.

Physical Structure of an Index and FILLFACTOR

If you are not familiar with the physical makeup of a b-tree index, clustered index, heap etc, a great primer can be found in books online. Thinking in terms of the leaf nodes let’s address the concept of FILLFACTOR.

The FILLFACTOR setting tells SQL Server the amount of free space to leave in each leaf level node of the index at the time that it’s built. If your table is going to be highly read intensive you may not want or need much free space. The benefit is that each read can get as much data as possible without having the overhead of moving from page to page to get it. If your database is more read/write balanced you may want a little more free space in each leaf so that there is room for new data without the overhead of splitting pages to make room. That’s why the server engineers let us modify FILLFACTOR because every database is different. (If the table is purely write intensive it probably shouldn’t be indexed at all. Why waste the resources to index a table that rarely gets read.) While FILLFACTOR should be customized to each scenario, I will go ahead and state that a typical FILLFACTOR is around 90.

Fragmentation

There are two types of fragmentation in SQL Server. The first is internal fragmentation, or the empty space that is spread across all the pages in the index. The more space in each individual page the more heavily fragmented (internally that is) the index is said to be. Keep in mind that some amount of space may be desirable. Is your database read intensive or more read/write balanced? Where was the fill factor set when the index was first built? The “fragmentation” in this case may be just where it should be. Above I said 90 is typical but keep in mind that number varies. In fact, Mike Hotek states in the Microsoft MCTS 70-431 test prep book that he doesn't consider an index internally fragmented until if falls below 75%.

The other type of fragmentation is external. An index has a logical order, that’s what makes an index useful after all. But, the physical order of the pages on the disk may not match the logical order after a lot of normal inserts, updates, deletes, etc. That’s because the disk is going to write new pages wherever it first finds room on the media. External fragmentation in SQL Server is more like the fragmentation most computer users experience with their personal operating system/file systems.

Unlike internal fragmentation, external fragmentation is never good. From a performance standpoint less is always better. When the physical order of your pages does not match the logical order of the index you’ll be incurring more overhead than what is theoretically possible in any predictable I/O scenario.

Under the Hood

With the foundation out of the way, let’s find out what’s really going on during an ALTER INDEX REORGANIZE?

The behavior of ALTER INDEX REORGANIZE is nearly identical to the old DBCC INDEXDEFRAG from the pre SQL 2005 days. Microsoft chose wisely in using the term REORGANIZE in the newer statement as opposed to “INDEXDEFRAG” as was used with DBCC.

Reorganize does the best it can with internal fragmentation by compressing available space at the page level and getting rid of unnecessary pages. User locks may keep it from getting rid of all possible space however. Remember also that REORGANIZE will not compress page space beyond the current FILLFACTOR setting.

The first way in which you may be able to increase the effectiveness of your REORGANIZE is by taking a closer look at your FILLFACTOR strategy. This strategy will probably change as a table matures (whether over time or overnight… i.e. think bulk load/ETL scenarios).

To address external fragmentation REORGANIZE attempts to put the pages in a physical order as closely matched to the logical order as possible. This works best under a scenario of light usage and low initial fragmentation. The reason is that it will skip any page locked by another process. That’s any lock mind you, including a shared lock. Also, contrary to the popular concept of "defrag" reorganize will not create new pages for swapping purposes as a disk defrag might do (just a page for page swap).

As fragmentation grows, so does the number of index pages holding the same amount of actual data. Single locks may start affecting more pages in turn causing more pages to be skipped by the process. In cases of widespread fragmentation and heavy usage REORGANIZE's ability to help becomes more and more powerless.

Another thing that may keep REORGANIZE from having any apparent effect is if you are dealing with very small indexes with only a few pages. As fragmentation percentage is reported as a ratio to total data pages it is not uncommon for small indexes to report very large amounts of fragmentation. You’ll find that even REBUILD doesn’t fix the index in these instances. Not to worry, the performance affect on these small indexes is nil. Kalen Delaney, who knows her stuff, says any index with less than 100 pages not worth worrying about. She further states even a 1000 pages may not be worth the effort.

Once again, every scenario is different and only you can figure out what a small or large index is for your organization. A reoccurring topic you will find in my posts is make sure your getting good baseline info so you know when performance truly is degrading or improving. Reducing fragmentation statistics is great, but only if it actually improves the actual performance and reliability of your database. The better solution of course is to utilize methods to reduce the causes of fragmentation in the first place. That may be my next post.

If anyone else has insight into the limitations of REORGANIZE by all means post them below.

What's good depends on what's normal; normal isn't always good

2009-08-28T15:49:00.001-07:00

Do you ever wonder why an actual number is rarely mentioned with regards to batch requests/sec and articles on performance best practices. That’s because perception is everything, or at least the baseline of that perception is.

In my last post I related how an article by Brad McGehee had made me question my batch requests/sec count. He stated, “Generally speaking, over 1000 batch requests per second indicates a very busy SQL Server, and could mean that if you are not already experiencing a CPU bottleneck, that you may very well soon.”

My perception of that number was that it seemed low compared to the baseline I had recently taken off a new server. A second look proved that my initial baseline (~3900 batches per second) was still accurate and that all other numbers still appeared healthy. Still, McGehee’s comment reminded me that I was a little surprised at that number initially. But, as the days went by my perception became desensitized and pretty soon that counter statistic was perfectly normal.

Before I go further, let’s define the counter stat ( batch requests/sec ). A batch is one or more individual SQL statements issued to the server in a…. uummm, well, batch. ( more on batches here ) In SQL 7 and 2000 this counter did not actually count batches, it counted remote procedure calls. A batch however may contain one or more RPC. With the release of SQL 2005 the behavior was changed to more closely reflect the name of the statistic. It started counting actual batches. If a batch contained more than one RPC it would get counted accordingly (only once). Microsoft undid the change in 2005 SP2 stating simply that the change was less useful then they had anticipated. As of this writing we’re back to the statistic counting RPCs, not batches. KB article here.

Obviously, each RPC may have infinite variations of complexity. A single complex RPC may tax the server more than 1000 trivial ones. Batch requests/sec on its own then, isn’t that useful. It only becomes meaningful when applied in one of two ways. The first, as a ratio to another statistic such as recompiles per second, CPU usage, etc. Or, when compared to a known good baseline. It’s a nice way of identifying that a server is a lot more (or less) busy than usual. I suppose it also indicates good network throughput if you have really high numbers with no other side effects but there is probably better ways to identify network throughput. Batch requests would have to be pretty astronomical to come up against the limit of today’s 1Gbs network cards.

But still McGehee had me intrigued so I decided to investigate further. What are all these requests? A few seconds of profiling created a trace that identified 90% of those requests coming from a laserfiche audit service. A couple log files to the vendor and we were informed that it had gone rogue. We killed off the process and suddenly my new baseline is only a couple hundred requests per second with the occasional spike to 3000 (that we identified as a legitimate process related to a different application).

My baseline has just changed drastically; my perception of the count has changed drastically. So McGehee’s number was a decent catalyst for investigation after all. But the fact remains, this information really only does me any good on this one server. When it comes to batch requests/sec what’s “good” depends too much on what’s “normal” for your server. And what’s normal may not necessarily be what’s good.

batch requests/sec... que tal?

2009-08-27T13:19:00.001-07:00

Day one:

It is my diligent quest to prepare myself and those I love in everyway possible for the imminent zombie apocalypse. My food storage is secure. My defense has been carefully selected for efficiency, longevity, and portability. My family has been trained. The only thing left to address at this point is the proper preparation of my SQL Servers. The return of living breathing humanity will have nothing if it doesn't have it's data!

But zombies are not the only thing my servers have to worry about, there are more immediate concerns such as poor application development, reckless ad-hoc queries, unwieldy business intelligence reports to be compiled, SQL injection on our web servers, and will someone please think of the clients. Mine aren't the only DBMS I've sworn to defend.

This blog will serve as a place to document my issues. Primarily for my own purposes mind you. I tend to remember things better when I've written them down and analyzed them in an after action report. Call it a mental process I picked up all those years I was knuckle draggin’ with the deputies of Clark County. There is a second reason however. I happen to network with a few other DBAs and from time to time I may experience something that may help someone else... it could happen. Heaven knows I've been saved a time or two by an obscure blog. Lastly, I spend a lot more time on the computer than I used to, it's time I do something more career-promoting then adjusting my fantasy baseball roster and studying the draft for the upcoming football season.

So, put your trays upright and fasten those seatbelts because reading my blog about SQL Server will most likely put you right to sleep. I would hate for you to fall out on the floor and bruise something.

Now, on to my first topic...

I was reading an article this morning by Brad McGehee regarding performance tuning and it made reference to the PerfMon statistic ( batch requests/sec ). It stated ... and I quote,

"To get a feel of how busy SQL Server is, monitor the SQLServer: SQL Statistics: Batch Requests/Sec counter. This counter measures the number of batch requests that SQL Server receives per second, and generally follows in step to how busy your server's CPUs are. Generally speaking, over 1000 batch requests per second indicates a very busy SQL Server, and could mean that if you are not already experiencing a CPU bottleneck, that you may very well soon. Of course, this is a relative number, and the bigger your hardware, the more batch requests per second SQL Server can handle."

1000 seemed low to me based on my recent baseline review of my own server.... I took a second and pulled up my standby PerfMon counter template, and sure enough. There my server is... running along with an average of 3900 batch requests per second. Should I worry? CPU usage is hovering around 7%, buffer cache hit ratio is pegged at 100%, recompiles are less than 1% of total batch requests, page life expectancy is good. It seems everything else I'm looking at is healthy. What gives ?

The climactic answer will follow in my next post. But I'll give away a little bit of the plot, batch requests per second on it's own really doesn't mean much. This is even more true when you are comparing the stats from a SQL 2008 instance vs a 2005, or 2000 install (or even different service pack installs for that matter), further it has a lot to do with what your server's focus is and what it's running on.

Still...., turns out 3900 is still crazy high.

Zombieware Consulting

2009-04-01T09:12:00.000-07:00

In 1999 I met Sgt Randon Walker of the Clark County Sheriff's Office. He was the CSI detective with the Major Crimes unit and was in need of a database that would do a better job of tracking evidence. We hit it off immediately and a lifelong friendship quickly developed as we worked together to create a series of solutions to meet his business needs.

Aside from being the newly hired database guy for the department I had also just graduated the academy and had received a commission as a reserve deputy with the same agency. In addition to being a friend Sergeant Walker now became my mentor and guide in achieving everything I aspired to be as a law enforcement officer.

For the next 10 years we worked together in various roles. The most important lesson he imparted through this time however was preparation for the imminent zombie apocalypse. If you're ready for that, he advised, you're ready for anything. This might be an appropriate time to mention that in addition to being the crime scene detective with major crimes he was also a sniper on the SWAT team.

In 2008 when I left the Sheriff's Office to advance my career in new directions I took the lessons that Sergeant Walker had imparted with me. Starting my own database consulting service left me with no doubts what it should be called.