SQL Server 2005 SP3
It looks like it’s on schedule for an RSN release (Real Soon Now).
For those who can’t wait, the latest cumulative update, #10, is available for SQL Server 2005. If you don’t know, the cumulative updates are a collection of all the small hot fixes and patches that have been issued by Microsoft over a period of time. Instead of tracking them all down individually, you can get the cumulative update and install it.
PASS Schedule Posted
Finally, the PASS Summit schedule has been posted. I’m presenting on Wednesday afternoon from 3pm to 4:15pm. Unfortunately, Gert Drapers is presenting at the same time. So my one hope/fear that he would be in the audience is gone.
I did a dry-run on the presentation last night at the SNESSUG meeting. It went over well, but it was a bit short at 53 minutes. I realized after I was done that there was a bit structure missing from the presentation, so I was able to add a couple of slides that allows me to describe the methods I’m going to employ and why and then summarize at the end of each section. It seemed to be well received. No one fell asleep or walked out. I even saw a couple of people taking notes. The one piece of feedback I received was to focus a bit on the “why’s” of what I’m doing so that people better understand the all the “how’s” that I’m describing.
The good news is, that I’ve been able to get the compound project structure within the GDR of VSDB to work well. Using this mechanism makes incremental deployments possible as well as being able to deploy to different environments.
Database Engine Tuning Advisor
I did receive a response to my post on the Microsoft forum. Unfortunately it was just a suggestion to post the problem on Connect since it might be a bug. Good, but not helpful information.
If anyone can confirm the issue, please follow the link to Connect and vote for validation. If you can show a query that gets a recommendation, I’m excited to hear about it.
Composite Projects in DBPro
I know the name is supposed to be Visual Studio Team System Database Edition, but DBPro, like Data Dude, slides off the tongue and the other doesn’t. Anyway…
I’ve been working with CTP 17 since it came out this week. So far, knock wood, no issues. I was trying to work out how to get multi-platform security working in some manner other than post-deployment scripts. Everything I tried failed. I finally posted a question on the MS forum. It was suggested that I use composite projects… What?
A little research later and I’m off and running. Now, with the ability to chain projects together, you can provide two projects, one with all the structures, and the other with only specialized differences, such as you might find in a development server.
I built a copy of Adventureworks (I love having a free test database to play with) in one database project. I created a server project and added a couple of logins to it. I then created a second database project. To this new project I added references back to the other two. I added a few security changes, to reflect, for example, a dev deployment to a server that isn’t going to have the same settings on security as the production server.
Now when I run build, all three projects get built, if needed. Deploy launched all three and the final result showed the aggregation of the projects.
I’m loving this. The improvements this creates in terms of process for tear-down and rebuild of the database, as well as enabling a decent process around incremental builds too. Now, if they would just release it already.
SQL Server 2008: Database Engine Tuning Advisor
I don’t think this thing works. I posted a bug report in Connect. If you can replicate my problem, described below, please go here and confirm the issue.
Get a copy of AdventureWorks2008. Pick any of the bigger tables. In my case I went with the Sales.SalesOrderHeader table. Write a query, a simple one or a complex one, that has performance problems that can be easily fixed by the right query. Here’s a simple example:
SELECT soh.Freight, soh.SalesOrderNumber
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderNumber LIKE ‘SO’ + CAST(6 AS VARCHAR) + ‘%’
AND soh.Freight > 50
If you run this query and get an execution plan, it immediately flags a missing index. The plan itself is a clustered index scan and a filter operation. Take the index it suggests and build it:
CREATE NONCLUSTERED INDEX [IX_Test]
ON [Sales].[SalesOrderHeader] ([SalesOrderNumber],[Freight])
Run the query again. This time, because this is a covering index, it has a single index seek operation and it runs in one second instead of three. Now drop the index. Right click on the query and select “Analyze query in Database Engine Tuning Advisor.” Start the tuning session. After a few seconds, it returns with no recommendations. None.
I tried other queries. None of them are getting flagged. Here are a few examples
select CarrierTrackingNumber, ProductID, OrderQty, UnitPrice from Sales.SalesOrderDetail where CarrierTrackingNumber = ‘8639-4639-AA’
SELECT Production.Product.Name,
Production.Product.ProductNumber,
Production.ProductModel.Name AS ModelName,
Production.ProductInventory.LocationID,
Production.ProductInventory.Shelf,
Production.ProductInventory.Bin,
Production.ProductCostHistory.StartDate,
Production.ProductCostHistory.EndDate,
Production.ProductCostHistory.StandardCost
FROM Production.Product
INNER JOIN Production.ProductCostHistory ON
Production.Product.ProductID = Production.ProductCostHistory.ProductID
INNER JOIN Production.ProductInventory ON
Production.Product.ProductID = Production.ProductInventory.ProductID
INNER JOIN Production.ProductModel ON
Production.Product.ProductModelID = Production.ProductModel.ProductModelID
WHERE Production.ProductInventory.LocationID IN (60)
ORDER BY Production.Product.NAME
If anyone can make it work, please post the sample code in the comments.
UPDATE:
I forgot to mention that I’ve posed the problem on a couple of different discussion lists. SQL Server Central (home away from home) provided a pretty interesting discussion, including the fact that my sample query, when run against the last CTP and not the RTM actually did provide a recommendation. It was the wrong one, but it provided something instead of just sitting there.
I also posted it to the Microsoft forum, SQL Server Katmai Manageability and Tools. No responses there as of this posting.
PASS Summit T-Shirt
Apparently, if you write a friends name in who is also registered for the PASS Summit, you can both get nifty t-shirts. Well, doggone it! I want a nifty t-shirt. So someone, please, click this link and include my name in your registration in the Friend-To-Friend box. Assuming you know my name.
Simple Talk Editorial on PASS
If you don’t subscribe to the Simple-Talk newsletter, why not? This month’s newsletter is chock full of interesting stuff, a DBA checklist from Brad McGehee, a workbench on keys, an article on how covering indexes are faster than clustered indexes (they are too), and an article on why one DBA is learning PowerShell (I want to, but my spare time is taken up with writing a new book). All great stuff and worth reading.
I’m going to be reading all these articles, but the most fascinating piece of the current newsletter is the editorial by Tony Davis. I was actually a little shocked by it. I’m a PASS volunteer for the Special Interest Groups and the Editorial Committee. I’ve attended the last three PASS events. I’m the current president and one of the founding officers of a PASS chapter (Southern New England SQL Server Users Group, come visit us). This year, for the first time, I’m presenting at PASS. So, while I’m not on the board, I kind of feel like I’ve got some vested interest in PASS. When I first read Tony’s editorial, I was shocked and a bit defensive. I’ve really enjoyed my involvement with PASS and I know that the volunteers that help run the thing put in a lot of their own time to keep it going. But after reading it a second time, I can’t disagree with Tony. As a matter of fact, I’ve said most of his criticisms myself. The organization does need to improve in general and, I think, better define it’s mission. The Summit itself is wonderful, as Tony outlines, but PASS, the organization, really does need to improve itself.
Go to the Summit. See for yourself the terrific program. Better still, volunteer to help improve PASS or to help out with your own local users group.
Home of the Scary DBA 