Thursday, May 15, 2008
Here's a presentation that I gave yesterday afternoon at
DevTeach/SQLTeach. It's called Tips & Tricks for Writing Better Queries. It's very similar to the presentation I gave a month or so ago at the
Nashville SQL Server User Group meeting.
To everyone who attended the session - thanks for spending a part of your day with me. I hope it was worth your while. Please feel free to email me if you have any additional questions or comments. And please remember to login to your SQLTeach account and submit your session evals.
Cheers!
Joe
Wednesday, May 14, 2008
Thanks to everyone who attended my "Identifying Performance Bottlenecks" session this morning at DevTeach/SQLTeach. As promised, here is the PowerPoint slide deck I used during the presentation.
Regarding the question of on the performance of LINQ, Bob Beauchemin recently posted a series of blogs entries on the topic. Here's a link.
And here's a link to series of posts on the Dynamic Management Views (DMVs) that Louis Davidson authored.
Cheers!
Joe
Wednesday, May 07, 2008
The nomenclature used for referencing software releases and updates can be quite confusing. Let's consider an example to illustrate what the various terms mean.
As a new version of a product is being developed, it may be made available to select customers and community members for early testing. This is sometimes called alpha builds of the product.
As development progresses and the product becomes more and more polished, it's provided to a wider audience. This used to be called beta releases; for example beta 1, beta 2, etc. However a few years ago Microsoft changed the terminology for SQL Server pre-releases. They are now referred to as CTPs (Community Technology Previews). You can download the November CTP, for example.
As the product enters it's final stages before release, the feature set is complete and the product is undergoing final testing, it's called an RC (Release Candidate).
After a product has undergone significant testing and it's determined that no more changes will be made to the product before release, it's sometimes said that the product has gone golden. It's also called a GA (General Availability) release.
Once the bits been turned over to a company to mass produce the media (CDs, DVDs, etc), it's RTM'd (Released To Manufacturing).
Usually sometime around the RTM, the product version is "launched". The timing of the launch may or may not have any correlation with the time the product is actually available for purchase. The launch has more to do with marketing and product feature education than availability.
Finally the product is released! It's available for purchase from the normal distribution channels.
Over time, Hot Fixes are created by the dev team to address specific product issues affecting certain customers. Sometimes the issue is so wide spread, a GDR (General Distribution Release) is issued so that all customers will receive the updates.
Since hot fixes and GDRs are designed to quickly address specific problems encountered by specific customers, they can be issued rather often. The rapidity of the hot fixes and GDR's makes it impractical for many IT shops to keep up with the pace of the releases. So, a CU (Cumulative Update) is created that contains all of the applicable hot fixes. This makes it easier for customers who haven't been directly affected by the issues that sparked the hot fixes to remain current.
Once a large enough collection of changes have been gathered, an SP (Service Pack) will be issued. Historically, SPs have also been the release vehicle used to deliver new features that were not ready at the time of GA. For example, Database Mirroring was made available in SP1. SP2 brought us the custom reports as in the Performance Dashboard. Microsoft has since indicated that SPs will not be used as a release vehicle for new features.
And then the whole cycle starts overs. Feel like you're on a treadmill yet?
For more information, check out the following links:
Cheers!
Joe
Tuesday, May 06, 2008
As most you already know, Notification Services is not part of Microsoft SQL Server 2008. I think this is a shame since SSNS is really a great product. It may be rather complex at first glance, but it's a great product nonetheless. I'll probably devote a blog to it's abrupt deprecation at some point in the future.
In the meantime, if you're preparing to upgrade to SQL Server 2008, you should verify that you don't have any rogue SSNS instances running on any of your servers. I hope your environment is more controlled than that, but I've seen more than one shop where change control was, well in a word, uncontrolled.
So, how can you tell if a server has a SSNS installed on it? Run the following query in the msdb database. It'll list all SSNS instances installed on that SQL Server instance.
SELECT
InstanceName
,DatabaseName
FROM msdb.NS90.NSInstances
Cheers!
Joe
Sunday, April 27, 2008
Ocassionally, I see a question in the MSDN Forums where someone wants to know how to retrieve a list of column information for a table using only T-SQL.
Although there are several ways in which to approach this, I like the following method.
SELECT
ORDINAL_POSITION
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,IS_NULLABLE
,COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Product'
ORDER BY
ORDINAL_POSITION ASC;
Do you have a different way of doing this that you'd like to share? If so, feel free to leave a comment, sharing your way and why you like it.
Cheers!
Joe
Wednesday, April 23, 2008
I hope that everyone that works with Microsoft SQL Server is aware of the
MSDN SQL Server Forums. If not, you should really go check them out. They're a great resource when you're staring at a tough SQL Server-related problem.
Now, some of the Moderators, Answerers, and MVPs have gotten together to create a new resource to supplement the Forums; it's called
SQLExamples. We just started it a few weeks ago so it's still very much in its infancy and somewhat sparsely populated. But it's growing quickly. I think it'll be a boon for SQL Server professionals the world over before long.
Check it out and let me know what you think.
Cheers!
Joe
Wednesday, April 16, 2008
The most common way to create a SQL Server Notification Services instance is to craft a valid and well-formed XML document for the Instance Configuration File and the Application Definition File. These two files are know as the ICF and ADF respectively and they essentially form the source code for the notification instance and application. As such, you should store them securely in the versioning repository of your choice - Visual Source Safe, Subversion, what have you.
If however, you find that you've lost the XML source files, or perhaps you've altered them without first making a backup copy to which you can revert back, all is not lost. You can have SSNS recreate the configuration files for you.
Recreating the configuration files
Simply select the Notification Services instance in SQL Server Management Studio, right click to show the context-sensitive menu, select Tasks, and choose the Export to Editor option as shown in the following screenshot.
This option combs through the SSNS metadata stored within the SSNS databases to reconstruct the XML configuration documents. Two new XML documents are opened within Management Studio as shown below.
Thought you may find this interesting.
Cheers!
Joe
Tuesday, April 15, 2008
At the 2007
PASS Community Summit in Denver, a keynote speaker made a passing comment about how there has not been a security bulletin released for SQL Server in over three years! I forget which speaker made the statement, but I found it utterly amazing. Not a single security bulletin released in over three years! Could this be true?
If you've worked with SQL Server for a while, you'll undoubtedly remember
SQL Slammer, the worm that hit
thousands of SQL Servers around the world in 2003. It's effects were nothing short of devastating for many companies.
I made a mental note to do my own research into what the speaker stated as fact; but promptly forgot about it while sitting in session after session, soaking in as much good technical content as my brain could absorb.
In a recent blog posting however,
Jeff Jones did the research that I forgot to do. His posting, entitled
SQL Server - Fact Checking Recent Vulnerability History, details the most recent security bulletins released for Microsoft SQL Server.
Jeff found that SQL Server 2000 hasn't had a security bulletin released since January of 2004, and even more amazingly, SQL Server 2005 has never had a security bulletin released! He goes further in his research, though, and compares these figures to the numerous security-related critical patch updates for Oracle.
It's an interesting read that I thought you may like to see. Check it out.
Cheers!
Joe
Friday, April 04, 2008
Thanks to all 60+ people who braved the flood warnings in Nashville to attend the SQL Server User Group meeting earlier today. I hope you found it to be worth your while. Some of your questions and comments at the end gave some some great ideas for future presentations. Thanks!
And thanks to Quest and RHT for sponsoring our meeting meeting today. If your in the Nashville areas, I'd encourage you to come on out for our next meeting, most likely sometime in May.
As promised,
here's a link to the presentation materials, both the PowerPoint presentation and the demonstration code.
Cheers!
Joe
Tuesday, April 01, 2008
If you've used SQL Server for a while, you're probably familiar with the @@version function. Selecting the function in a query window produces the following results on my system.
From this, you can quickly determine that I'm running SQL Server 2005 Developer Edition version 9.00.3054.00 on an Intel X86 processor. It may also appear that I've installed SQL Server SP2, however that is rather misleading. The "Service Pack 2" text in the above image refers to the operating system, not the SQL Server system.
To determine SQL Server's service pack level, use the built-in SERVERPROPERTY function with appropriate parameters - EDITION, PRODUCTLEVEL, and PRODUCTVERSION.
For example the following queries produce the results shown in the image below.
From this, you can see that I am indeed running SQL Server 2005 Developer Edition updated with Service Pack 2.
The SERVERPROPERTY function is part of SQL Server 2000 and greater. Prior to that, we had to resort to equating the product version to a service pack level. Here's a link to a KB article with more details for v7.0 and earlier.
Cheers!
Joe
