I have been testing SQL Server 2005 SP3 CTP this week. This Beta version of SP3, which is due out for official release before the end of the year, includes all of the previous cumulative updates for SQL Server 2005 since its release in Nov 2005. It's been almost a year since SP2 so this is welcome news. In addition to the previously issued updates, there are some enhancements in SP3 which are outlined in the associated What's New document. These are classified as "new or improved features". Let's take a look...

Read more

In my previous blog entry, I talked about index fragmentation and some of the tools that could be used to monitor and remove the fragmentation. The general rule to follow, according to Microsoft best practices, is to rebuild an index with more than 30% fragmentation.  In a recent class I was teaching, we used the SSMS built-in reports to find out which indexes to rebuild. Using SQL Server Management Studio 2008, right-click on your database, select Reports then Standard Reports then choose Index Physical Statistics.

Read more

When I was young we used to go to see our local football, sorry, soccer team and cheer them on rain or shine on a Saturday afternoon in England. In those days the cheapest way to watch the game was "standing on the terraces" - no seats for the weary, but we would always stand in the same place so friends knew where we would be in the crowd. Yes, this was long before the cell phone revolution. I remember there was always an old man there that would criticize the team week in week out. No cheers from him when they scored a goal, but plenty of abuse when they didn't.

Read more

As I mentioned in my previous blog, I attended the SSWUG Virtual Conference last week. I am really getting into it.

I enjoyed the session on "Intro to Scripting with SSIS" with Matthew Roche in Room A. It didn't seem to matter that I missed the earlier "101" sessions. It was good to see the demos done with SQL Server 2008 although there were some performance problems with his machine. We all get the demo gremlins from time to time. It was good to see the new support for C#.NET in SSIS 2008.

Read more

I attended the first day of the SSWUG Virtual Conference this week and I was pleasantly surprised. In a previous blog entry I had mentioned that I would be attending both this virtual conference on the web and the SQL PASS conference in Seattle in November. Here I will review the online version and will report on Seattle later. Let's take a look...

First of all, signing in was easy. An email was sent the day before and the morning of the conference with links to test your system and to attend the conference itself.

Read more

As of 27th Oct 2008, the latest service pack for SQL Server 2005 has been made available for Beta testing with the CTP (Community Technology Preview) program. This is good news as most customers are waiting on an upgrade decision for SQL Server 2008 but still want the latest updates for 2005. It's almost been a year since SP2 so this is well overdue. It's a great part of the process that Microsoft releases the Service Pack as a CTP. That way, users can report issues straight away that may need to be fixed before official release.

Read more

One of the restrictions of SQL Server 2005 (and now in 2008) is that you cannot include an ORDER BY within a view. This presumably is to remove the possibility of a "double sort" where the view defines a sorting order then the application requests a different sort order when the view is accessed. If you try to create a view with an ORDER BY it will now produce an error. This seems fine except that when you upgrade from SQL Server 2000 with an ORDER BY in a view already, it will allow the view to upgrade unchanged but the query optimizer will actually ignore the ORDER BY at runtime.

Read more

A nice new feature in SQL Server 2008 Reporting Services is the Scale Break property for a Report Chart axis. Just by checking one box, the report server application will take over when generating a histogram and will adjust the scale automatically so that you can view bars that may have a great variance in values. This is a run-time feature in Reporting Services. Let's take a look...

When defining a Chart Data Region in Report Designer, each axis on the chart has a set of properties. One of them is the Scale Break property.

Read more

Recently, I was teaching a class to "Upgrade your skills to Microsoft SQL Server 2008". This course walks through many of the great new features available in the new release. At the end of the three days of hands-on exercises and lecture, I asked the question: "So, what's your favorite feature?". One student came back with a very piquant observation: "There's many features I like, but what concerns me is that they are all Enterprise Edition features...".  I hadn't thought about it before but he is absolutely right.

Read more

Data Compression is available at last in SQL Server 2008. Even Backup Compression is available. This will help us save space and time. So maybe my comment about comparing storage and the financial credit market was a little hasty. Looks like Microsoft is taking some proactive steps to bail us out of this storage crisis before it happens. We have two types of compression available in SQL Server 2008: Data Compression and Backup Compression. Let's take a look...

Data Compression can be performed on tables, indexes and partitions.

Read more

In my previous blog entry I talked about the new Change Data Capture (CDC) feature. This allows us to capture data changes to another relational table for later use. An example may be in order to apply incremental updates to a Data Warehouse. There are many strategies for this process that can be employed but Microsoft has given us the new MERGE statement which helps simplify things. Let's take a look...

One way of applying incremental updates is by use of a Timestamp column to identify what has changed since the last incremental load.

Read more

Data Partitioning in SQL Server was introduced in the 2005 release. This was a feature that had been in Oracle and IBM's DB2 for a while, but Microsoft is positioning to compete in the VLDB market place.  What constitutes a VLDB? Well, that's a "moving window" - most people now talk about terabyte size as being large. Once Microsoft match the big guys for functionality, it can then turn around and say SQL Server has a cheaper TCO and is easier to use. That's the theory anyway.

Read more

One of the interesting new features in SQL Server 2008 is Change Data Capture. I wrote about Change Tracking in a previous blog entry. That will track the what, when and who but will not tell you exactly what the data values were before and after an update. If you need to know the column data involved in an insert, update or delete statement then CDC is for you. For instance, you can use CDC in your incremental load package that copies changes to your Data Warehouse every night.  Let's look at this feature...

Read more

One of the dreaded error codes we get in the SQL log is the "1205". It represents the infamous deadlock where two transactions are locking each other out. No way out. (Wasn't that a Kevin Costner movie? He turned out to be a Russian spy, didn't he? Whoops...gave that one away...).When there's no way out, we have to take drastic action. And SQL Server does just that. One of the transactions is "killed" and becomes the "victim". Sounds like an episode of "CSI:NY" but it's true.

Read more

In a previous blog entry, I talked about the Virtual Conference being planned for the SQL Server Web Users Group ( http://www.sswug.org/ ). Well, the dates have been switched but I am still looking forward to the online conference. Now it is going to be on Nov 5th through 7th so if can recover quickly from the US Presidential election, you can attend the conference the day after.

Read more

One of the requirements for SQL Server Reporting Services (SSRS) in the 2000 and 2005 releases was that Microsoft's Internet Information Services (IIS) was required as the Web Services product for running the Report Manager and Report Server applications. The good news was that, at least, we could run these applications on a separate Web Server machine allowing top security for the SQL Server. But Microsoft would charge us extra to do this. Running IIS on your database server was never a good idea for security - locked down or not.

Read more

SQL Server 2008 has some great new features as I have outlined in numerous previous blog entries. One of them is the new auditing capability. Now we can audit at the server, database and table level according to specifications provided by the DBA. We could audit such things as successful and failed logins in previous releases but now we have much more granular capability. Let's take a look at the new functionality...

First thing to do is create a server audit object. This defines where the log entry will go.

Read more

When I worked for Sybase, much of the advertising we published was based on the TPC performance benchmark figures of Sybase SQL Server compared to the competition. The Transaction Processing Performance Council (http://www.tpc.org/) according to its web site, "is a non-profit corporation founded to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry". Very noble. This attracts both hardware and software vendors to prove that their products are the best on the market. Of course, the reality is that you are only as good as your last benchmark, as the sports cliché goes...

Read more

I have been testing SQL Server 2008 and I must admit, I've been impressed with the new features. I've written about some of the new functionality in past blog entries. One feature that I had not tested before was the Resource Governor. This allows us to control which applications use which resources on the Windows Server and in what proportion. Let's see how we can use it.

With Resource Governor we can limit the resources an application uses, while allowing other more important applications more of the resources such as CPU and Memory usage. This can help minimize the effects of runaway queries or memory intensive code in a low priority application. Higher priority applications will be able to get the resources they need when they need it. The neat thing about the Resource Governor is that it will only kick in if there is contention to deal with. If only a single application is running there is no need to limit resources. As soon as multiple applications are running and competing for resources, it will do its job.

To set up the Resource Governor, we need to enable it and define Resource Pools , Workload Groups and a Classifier Function. A Resource Pool defines the physical resources available on the server. You can specify minimum and maximum CPU and Memory usage. A Workload Group allows you to assign certain criteria to an application, for instance,  a priority or a limit to simultaneous requests. Workload Groups are assigned to a single Resource Pool. We then need to create a Classifier Function to assign applications to Workload Groups. There can be only one Classifier Function in use at a time. If an application is not identified by the Classifier Function, it will use the default Resource Pool.

Read more

In a previous blog entry I talked about the new feature of Intellisense in SSMS 2008. This uses the intelligence of the system to help you with scripting syntax (and semantics) as you type. Very productive. It is a great feature and I have been using it in my testing of SQL Server 2008. But how good is it really?

Well, I must say, having the system prompt me for column and table names as I type is a definite advantage. It saves me typing and the more typing I do the more typos I make. It also will check things like objects that don't exist, invalid syntax and other errors. Hovering the cursor over the error will give an explanation of what is wrong. Very nice.

Recently, however, I have been spotting some vulnerabilities with Intellisense. For instance, I wanted to enter the following script as part of a training scenario in a lab I was testing:

USE AdventureWorks2008

GO

UPDATE Person.Person

SET LastName = 'Davolio'

WHERE LastName = 'Duffy'

GO

This script will update the LastName column in the Person table in the Person schema. As I type the "USE" then the space then the "A", Intellisense brings up a list of databases beginning with "A". I see AdventureWorks2008 so I hit Enter and it brings that database name into my script. Great. I hit Enter again for a carriage-return and type in the "GO" delimiter followed by another carriage-return. All is going well. Now I type  "UPDATE", space, "Pe" and the list of objects appears automatically with the Person schema selected so I press Enter again to confirm and it copies it to my script. Good. I type the period and decide to just keep on typing while looking at the lab instructions since it contains a couple literals which Intellisense is not going to help me with.  This is what I ended up with:

Read more