Entries tagged 'sqlserver' ↓

Improvements to the Execution Plan UI in SQL Server Management Studio 2008 sure do make it a doddle to improve query execution time.

A client just reported that a particular stored procedure kept timing out.

Viewing the Estimated Execution Plan for this query displayed a nice green message advising of a potentially missing index which could improve performance by >95%:

Right-clicking and selecting “Missing Index Details” then brings up a new query window containing the index creation DDL (I had changed the index name by the time I grabbed this capture):

Dead easy. Repeating the process identified a second index which could further improve performance by >80%.

Of course, you still need to bear in mind other queries which access the table, and the effects on inserts/updates of creating additional indexes, but even so, this really helps the workflow.

The suggested indexes functionality was actually available in SSMS 2005 too, but the results were buried away in the XML version of the execution plan, and obscure properties windows, rather than being so in your face.

SQL Server 2008 SP1 has been released! The big news as far as I can tell is that SSRS Report Builder 2.0 can now be deployed via ClickOnce, which should make it easier to provide this application to end users. Apart from that, it’s mostly tweaks and fixes which have already been made available in previous cumulative updates.

That said, reaching the first service pack is often a helpful milestone for persuading clients that a product is at a suitable level of maturity for deployment across their business. Think it’s tough persuading your bosses to upgrade to VS 2008 and start targetting .NET 3.5 SP1? It’s a doddle compared to getting them to upgrade operating systems or databases.

One of the multiple hats that I’m currently wearing for a client is that of SQL Server DBA. In this guise, I have recently fielded a couple of email queries from users concerned about the data recovery options available to them, should application data be inadvertently deleted:

“What kind of database backup and restore mechanism is in place for [application]? If someone deletes any data from [application] front end is it possible to restore it? Generally the way to recover data would be to restore the database from a database backup file, however this would restore all data held in the backup and would overwrite any changes made since the time of the backup.”

and

“If user A corrupts data entry in some way, and needs to go back to a version X of the database before the corruption, will user B also have to go back to version X and potentially lose any 'good' they themselves might have added since version X?”

I replied explaining that this is one of the many reasons for choosing SQL Server to provide the database back-end for critical applications, rather than, say, Access (don’t get me started on the prevalence of Access in mission-critical applications, it’s a bit of a bugbear).

If you have your SQL database recovery model set to Full or Bulk-Logged (and if not, why not?) then it is possible to restore from transaction log backups to a point in time. So, in a data loss scenario like those outlined in the emails above, you would take a backup of the transaction log, and restore a new copy of the database to a point in time just before the deletion. The missing data could then be copied from the restored copy across to the live database. Easy.

Even so, just because this functionality exists doesn’t mean you should plan to use it. Flipping back to my system developer persona makes me question why the application allows users to perform non-logged hard deletes? Soft deletes would be far preferable (i.e. setting a “deleted” bit on the relevant rows). Better still are ledgering systems (like the bank statement paradigm), which handle updates and deletions by making a change entry, or entering an offsetting transaction.

My name is Ian Nelson. I'm a recovering SQL addict. It's been four weeks since my last stored procedure.

One of my reasons for leaving my cosy permanent job and entering the scary cut-throat world of freelancing was to get a broader exposure to different tools, technologies and methodologies, while remaining with my feet planted firmly in the .NET universe. So, when I got offered a position in a team who were using Subversion, Monorail, Ext.JS, NUnit, Oracle and NHibernate, I jumped at the opportunity.

Of those technologies, it's definitely NHibernate which has most dramatically changed my perspective on how enterprise solutions can (and should) be developed. For many years now, I've considered deep database (particularly SQL Server) skills to be one of my core proficiencies, and have merrily spent (wasted?) countless man hours writing dreary "plumbing" code, by the way of CRUD stored procedures and repetitive Data Access Layers in order to persist my CLR entities to the RDBMS.

Well, no longer, those days are over. I've seen the light and embraced the wonders of NHibernate. For those who are unfamiliar with it, NHibernate is an Object-Relational Mapper, and is a port of a Java utility, Hibernate. The premise is beautifully simple - by decorating your objects with attributes or, more neatly, by creating XML mapping files, you define the mappings between your .NET domain objects and their persisted representation on the database. Then NHibernate handles all the grunt work of saving your objects to and from the database.

Digging deeper, I discovered that NHibernate has been developed in such a way as to make it easy to embrace best practices in database access techniques. Several years ago I read through Fowler's seminal Patterns of Enterprise Application Architecture, and wondered how I would ever find time to develop solutions which made use of the cunning O-R behavioural patterns he described - Lazy Load, Unit of Work, Identity Map, Query Object, Repository, etc. I utterly failed to pick up on this paragraph on page 171:

"Remember that you don't have to build a full-featured database-mapping layer. It's a complicated beast to build, and there are products available that do this for you."

Products such as, yes, NHibernate. Or Wilson ORM, or more recently, the LINQ to SQL offering introduced by Microsoft in the .NET 3.5 Framework.

I'm glad that I have a solid grounding in SQL, and I still believe that other skills related to RDBMSs are useful in the enterprise (i.e. database design, normalisation and index tuning), but I'm pleased that I can now bring myself to relax, stop worrying, and leave an ORM to take care of generating the repetitive CRUD statements required for persisting my domain objects.

So, what have I been doing with all this "free time" that I now have available to me during a hard day at the code face? Well, I've been trying to create systems which are more robust and well-designed by making a concerted effort to embrace Test-Driven Development (for real, this time!) But that's a good topic for a future post...

 

A few useful NHibernate / ORM Resources:

• NHibernate
• Wilson ORM
• LINQ to SQL
• NHibernate Best Practices with ASP.NET - article by Billy McCafferty
• My del.icio.us links tagged with NHibernate / ORM / LINQ
• Dot Net Rocks - the ORM Smackdown Show
• NHibernate In Action
• LINQ In Action
• Ayende's Blog
• The NHibernate FAQ
• Wikipedia - Object-Relational Mapping

During 2007, I unexpectedly achieved a long-held ambition by increasing the number of books I regularly read (as opposed to merely buying, then leaving them to languish on the bookcase!). I read plenty of techie books of course, but also made use of local libraries and devoured plenty of books on other subjects, and even found time for some fiction. I think having a young baby in the house was partially responsible for this achievement - not much else to do of an evening any more!

Some books were great and will remain on my shelves for years to come. Plenty others were quickly returned to the libraries, or resold on Amazon Marketplace. Either way, I figure that it might be a nice addition to the old blog if I take the time to say a few words about some of them, so let's start with the book I've finished most recently.

Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney is one in a series of four books from MS Press, the other three having subtitles of T-SQL Programming, T-SQL Querying, and Query Tuning and Optimization. I suspect that The Storage Engine is the most deep and esoteric of the four volumes, offering an insight into the architecture and inner workings of SQL Server that isn't matched even by my old favourite SQL Server 2005 Unleashed.

By no stretch of the imagination could this be considered an introduction to SQL Server, and frequently I found myself skipping forward a few pages at a time, reasoning to myself that "there is no reason why I will ever need to know this!" - indeed, plenty of the first half of the book on topics such as architecture, configuration, files, logging, and recovery, would be of more use to DBAs and sysadmins rather than developers such as myself - not that it isn't nice to have an appreciation of these topics though.

From a developer perspective, it is the second half where this book really springs to life, with three great chapters on Tables, Indexes, and Locking & Concurrency. These are 235 pages of pure gold, offering to the willing reader a really deep understanding of what is really going on under the hood, and how exactly MSSQL goes about implementing your requests. It certainly blew apart some of my incorrect assumptions about internal table structures, and has given me a better understanding of the internal structures of clustered and nonclustered indexes.

SQL Server is one of the most widely used products in Microsoft's server range, and in my opinion also one of the most widely abused. Anyone who has worked in this industry for any length of time has inevitably witnessed some awful database designs (no primary keys? no foreign keys? no indexes? every column a varchar(50)? you know the kind of thing!), but we can all learn to improve our database designs, and the level of understanding offered by Kalen's book should help in the quest for perfection.

As long as my career keeps its technical focus, and I'm using SQL Server 2005, then this one is a keeper, and I've already ordered Kalen's other book in the series, Query Tuning and Optimization.

Many moons (and about four jobs) ago, a wise DBA by the name of David Hanson once recommended to me a book called Microsoft SQL Server 2000 Unleashed.  I took his advice, bought a copy, and it rapidly became one of the most dog-eared and oft-thumbed computing books I have ever owned.  It is comprehensive, deep, broad, well-written and covers everything one might ever wish to know about SQL Server 2000. With its help and guidance I came to love and respect Microsoft's premier database product, passing the 70-229 exam by a comfortable margin.

So, following the release of SQL Server 2005, I was hoping that SAMS would publish a similar tome about that new platform.  Once said book was announced, I pre-ordered* a copy, and I'm delighted to report that after many months of patient waiting, the sequel (sorry) landed on my desk last week - and for a computing book, it's simply awesome.

Microsoft SQL Server 2005 Unleashed is even bigger than it's predecessor.  In fact, it's so large that eight chapters have been left out of the physical book, and are included only in a PDF version supplied on CD-ROM.  These aren't just padding or filler, either - they're solid chapters on interesting topics as Notification Services, Service Broker, Recovery Planning, and T-SQL Tips n Tricks.  Including the bonus chapters, the whole package weighs in at over 2,000 pages - no wonder I had to wait a while for its publication!

I think you would be hard-pressed to find any other single SQL 2005 book which covers quite so much material to such depth. While perhaps not being suitable for beginners, for those with some experience of SQL Server or other relational database products, this book works on many levels - reference, tutorial, guidance, or just as an interesting read.  If you work with, or anticipate working with, SQL 2005, then you owe it to yourself to get a copy now!

---

* I hate this term, but that's what Amazon call it - "pre-order".  Whilst I understand the implication, I don't think there's any real linguistic difference between "ordering" and "pre-ordering".  Even less so the distinction between to "book" and to "pre-book", a service offered by some cinemas... 

Permit me a quick whinge:

Three basic features of NUnit that aren't replicated in VSTS unit testing:
link [blogs.msdn.com]

SQL2005 backup maintenance plans don't support auto-cleanup from subdirectories (as in SQL2000):
link [blogs.msdn.com]

SQL2005 scripting options are (frankly) a pain in the arse, and I'm hopeful that a third-party will soon release a tool  to fill this gap:
link [lab.msdn.microsoft.com]


Don't get me wrong, I'd rather be using VS2005 and SQL2005 than their predecessors, but it's bloody annoying that after such a long wait for these tools they are lacking in some fairly obvious features.

The imminent end (for this year, at least) of British Summer Time gives me the luxury of an extra hour to spend as I see fit this weekend.  So, permit me to use some of it to tell you that I've had a great week.  Leaving Avanade and returning to my old role at Marshalls was definitely the right decision.  I feel stress-free for the first time in many months. 

It's not just that I've realised my mistake and gone back to a place where I was happier - the situation actually feels better than it was before.  We have some new blood in the team, bringing a new set of skills and ideas that could prove to be a shot in the arm.  We have a new set of challenging projects facing us in the upcoming months and years.  We have new hardware, and of course with the release of VS2005 and SQL2005 this week we now have a new set of technologies that we're aiming to target soon.  It's an exciting time.

In the short-term, I'm focussing on some R&D tasks surrounding the use of ASP.NET 2.0 and SQL Server 2005 on some potentially large development / integration projects.  Great fun, and if we pull it off it could be quite unique in the market sector.  I shouldn't really say more at a high-level, but I'll be sure to blog about any technical stuff I stumble across that may be of use to others.

For instance, did you know that it's perfectly possible to share an ASP.NET FormsAuthentication cookie across multiple websites, running on totally different machines? So, for example, you can log into app1.foo.com and share this with app2.foo.com. Just be sure to set up identical settings in the machineKey and forms configuration settings in web.config (see this MSDN article for more info), and to set the Domain property to the top-level domain when storing the HttpCookie. Bob's your uncle. Sweet.

Bin your betas, cast aside your CTPs!  RTM versions of SQL Server 2005 and Visual Studio 2005 are now available on MSDN Subscriber Downloads.  W00t!

Fill yer boots!

Last week I had the pleasure of attending a two-day MSDN event in Harrogate focusing on the upcoming Visual Studio 2005 and SQL Server 2005 releases.  It was one of the better and more enjoyable MSDN roadshows I've been on, partly because there's so much cool stuff due to be released at the end of this year, but also because this stuff is no longer so much of a mystery to me!  I remember attending the .NET 1.0 release event in London in December 2001 and practically every session blew my mind, as everything I was hearing about seemed so different to the relatively simplistic ASP / VB paradigms with which I'd been working up to that point.  .NET was a wonderful and empowering toolset, but for me it came with a steep learning curve which made my head hurt for much of 2002 and 2003.  But now I've put in that hard work back then I find myself in a much better position for understanding the 2.0 enhancements, most of which are things I'm keen to get using straight away.  I can't wait.

Eager for more of the same, Eric Nelson drew my attention to another MSDN event next week in London - a technical briefing on Extreme programming in .NET.  Having read a couple of books on XP / TDD in the last year but failed to really put in into practice in the working environment, this sounds like a potentially interesting and useful session, so I've registered to attend.  Maybe I'll see you there..

Edgar Sanchez is at TechEd and is reporting that Paul Flessner has announced the release date of Visual Studio 2005, SQL Server 2005 and BizTalk Server 2006 as being the week of November 7th.  Five months to wait, then...

Update: the full text of Flessner's speech is now online at http://www.microsoft.com/presspass/exec/flessner/06-07Tech-Ed05.mspx

But at least the SQL Server 2005 June CTP is with us today!  Yay, (slightly) new toys to play with :-)  It's at times like this (i.e. when faced with a 765Mb download), that I'm grateful to BT for upgrading my ADSL to 2Mbps.

There seems to have been a lack of .NET-related stuff on this blog recently, so let me show you something I cobbled together for work purposes last Wednesday:

A common requirement we have in our ASP.NET applications is how to reliably persist object instances for a lengthy period of time.  End users shouldn't have to concern themselves with the concept of Session expiry, and it's unacceptable for them to return to their PC after nipping off for a coffee to be confronted with the dreaded "Object reference not set to an instance of an object" when they try to continue editing their basket / sales order / timesheet / whatever.

One pattern to solve this, detailed by Martin Fowler in Patterns of Enterprise Application Architecture, is the Serialized LOB (Large Object), which works by serializing the object in question into binary or text which is then stored in a single database field.

An excellent example of how to achieve this using SQL Server and .NET is given by Peter A Bromberg in this Egghead Cafe article.

Building on this example, I've put together a generic assembly for persisting and retrieving serializable classes into SQL Server, referenced by a combination of two strings (a username and a key).  It is essentially an alternative to storing objects in Session or ViewState.  As an additional benefit, it allows you to "lock" objects based on the key, so that only one user can store the same object at a given point in time.

The zip file below includes the source and a SQL script for setting up the required database table and four stored procedures, as well as some unit tests (yes, I am sticking to my New Year's Resolution!).

The DataStore class contains the various static methods necessary to add, retrieve and delete objects from the store.  For example, to save an object you can simply make a call along the lines of:

 DataStore.Add("joebloggs","order1234",objOrder);

 DataStore.Add("joebloggs","order1234",objOrder,true);

 SalesOrder objOrder = DataStore.Get("joebloggs","order1234").StoredObject as SalesOrder;

---

Updated 30 October 2005:

I've reworked this code for .NET 2.0, and removed the dependency on the Microsoft Data Access Application Block. Also, the NUnit tests have been replaced with VS2005 unit tests.

Can anyone give me some pointers as to what business reporting / analytics tools and packages should be used when Reporting Services and Excel are insufficient?

Following the successful deployment of a new application at work in August, we (that is, the IS dev team) have been occasionally requested to provide various reports on the underlying data.  So far these have generally been provided using SQL Reporting Services (available online and sometimes emailed to a distribution list as Excel export), and it's been working really well for most requirements.

But as ever, the more you provide, the more the business asks for, and now I find myself faced with a request to produce a huge (in terms of number of rows) Excel report containing, effectively, a few key tables from the database joined together.  I contend that this isn't, strictly speaking, a report request - it's a desire to be able to query and filter the data using a familiar tool, but the sheer size will make this infeasible in Excel, and there must be better ways to achieve this goal.

I hear the term ProClarity banded around, and Jez told me about Cognos Reporting - anyone got any experience of using these?  Or recommendations for other useful tools?  Ideally I'd like to be able to give the relevant folk in the business direct read access to our SQL database (or a replica - don't really want people querying the same db used for OLTP), but I'm unsure if this is feasible.

Via Eric G. Harrison - MS have developed a SQL Server Best Practices Analyser Tool.  It's like a cross between FxCop and Baseline Security Analyser, for SQL Server.  Very useful.  I just downloaded it, ran it against some of my databases, and it made some useful suggestions.  This could quickly become indispensable.

Is this site for real?  I'm not sure.  Either way, I'm sticking to SQL Server!

Ooh, there's more - read the dress code!

It never fails to amaze me how many .NET/COM charting components are available - it must be a very crowded, or very lucrative, marketplace.  Possibly both, given the prices at which these things sell.  I had half-expected VS2005 to include a built-in charting component - after all, it includes Treeview, SiteMapPath and Menu controls, similar ideas to which have all been available from third parties in the past. 

Anyway, just when I thought I had used or trialled every charting component in the known universe, Scoble brought my attention to yet another - Graphics Server .NET.  These guys aren't marketing themselves aggressively enough, which is unfortunate as this is by far the most pleasant component I've used for a long time - dragging the individual elements of a chart into place is so much better than having to rely on trial and error as you set and reset layout properties until they're pixel perfect.  I just wish a license wasn't $899 - that might be perfectly reasonable for the enterprise, but I'm on the lookout for a cheap charting component for a personal project, and as yet have been unable to find one.

Also today I've been experimenting with SQL Server Reporting Services, which itself allows for the creation of charts within its reports.  Since an RS report can easily be embedded in a webpage (using the supplied ReportViewer server control), what is to stop someone using an RS report containing a single chart where previously they would have required a third-party .NET charting component?  OK, the appearance might not be quite the same as using a charting component which outputs a PNG or GIF direct onto a webpage, but for a large proportion of business reporting solutions I suspect the end result would be more or less the same - the business get to see a graphical representation of their data.  It will be interesting to see whether the number of .NET charting components on the market shrinks in the coming years as RS takes a hold in the enterprise.