VSTSDBE RC0 Install
I’ve downloaded and started the install. The first screen I’m seeing is not filling me with confidence. The executable that is currently running is called “DBProRepair.exe” However, the installed completed without any issues.
Just for giggles, I tried opening my PASS presentation (created on CTP 17). If it worked, I wouldn’t have to recode. Ah, well, no joy. Not that I expected any. Oh, but the Conversion Report that comes up afterwards is very informative.
Reverse engineer against the AdventureWorks2008 database went off without a hitch. All the objects imported into the database with no issues too. So far, so good. Created all the projects. No issues. I’m configuring for a deployment now. We’ll see how that goes. Builds worked fine. I forgot to change the deploy action the first time I did a deployment. Second time… Frell!
The compound deployment is failing. I had something configured wrong… I’ll post when I get it figured out.
Visual Studio Team System Database Edition GDR RC
I’m a bit late to notice this, but Gert Drapers announced on Monday that the release candidate for VSTSDBE, or good ole VSDB as I’ve decided to call it, is now available. I figured they’d do something during the Professional Developers Conference (where supposedly a new printing of my book was being given out). Now I need to get on the stick & upgrade my virtual… maybe. According to the post, functionality hasn’t changed. Maybe I should just leave everything alone on my machine until after PASS… Nah!
I’ll post progress for the uninstall, the install and the recreation of the compound project.
Procedure Cache and Dynamic Management Views
I’m just going through the chapter on the procedure cache in the new book and I’m having a blast playing with the dynamic management views and functions that let you access the procedure cache. It’s just too much fun and way too easy to get detailed information about the queries in the system, not like the old days. First, you can access the cache itself with the DMV, sys.dm_exec_cached_plans. This shows some of the data describing the plan in cache, but most importantly it provides the plan_handle. You need this for other joins later. You can also use sys.dm_exec_query_stats to get aggregated performance statistics about the plan. It also has the plan_handle and two things new to SQL Server 2008, the query_hash and the query_plan_hash, also known as query fingerprints. I’ll get into those things another day, but they’re pretty exciting all by themselves. Finally, you can also get information about currently executing queries, which may or may not be in the cache, through sys.dm_exec_requests. These are all views, so you can just query them directly. Unfortunately, the first two don’t offer any chance to filter the information returned by database or spid, etc., but sys.dm_exec_requests does.
So, getting basic information about the size of a plan or how long it’s been cache or the accumulated time that it’s run is all interesting and everything, but what about getting a peek at the execution plan itself. Enter the dynamic management function sys.dm_exec_query_plan( plan_handle ). Just join it with one of the other views and suddenly you’ve got some real information:
SELECT *
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) p
You’ll get an XML plan, which, when you click on it in 2008 opens up as a graphical plan since, to Management Studio in 2008, they’re the same thing. Easy-peasy.
But what about that query that’s hanging up and blocking all the othe query’s? What the heck did the developers do this time? Let’s try out the DMF sys.dm_exec_sql_text ( plan_handle ).
SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) t
You’ll see the query, as it was passed, in the text field. Great stuff. Really handy.
There is a ton to explore here and it’s all very useful stuff. These are available in SQL Server 2005 in addition to SQL Server 2008.
Twitter?
After much discussion in a set of emails between some of the more dangerous people that attend the PASS conference, I’ve decided to create a Twitter account. I created it under my name for the time being. I may blow it up later & go with something else. I’m not sure. I still don’t quite get the point of this one, but I’m hooked in now. If you’re interested in “following” me (like I could lead you anywhere), click here.
Custom Monitor for SQL Agent in Operations Manager
Tom Larock has a new article on how to create a custom monitor for SQL Agent. It’s worth a read. He walks you through a series of concepts that are pretty important if you want to track individual Agent jobs in custom fashion.
Andy Warren for PASS Board?
Andy posted the question more than a week ago. I need to pay more attention to his blog. In my opinion, the answer isn’t yes and it isn’t hell yes. It’s **** YES! For cryin’ out loud!
I totally respect his feelings of being overwhelmed and busy. I’m there too. I’ve completely overcomitted, so I understand. But Andy Warren just strikes me as one of those really smart, capable guiys. You know who they are. They walk in to situations and things just improve. I think he can do two things, only one of which I could do. I think he’ll help PASS deliver on it’s promise, working with the other board members. I also think he’s likely to have a few good ideas (something I’m extremely short on) that will make a difference for the PASS organization and the community as a whole. I say this because Andy has already so profoundly affected the SQL Server community with his invovement in so many different undertakings.
That’s my 1.5 cents. Follow the link above to Andy’s blog and give him some encouragement.
Amazingly Stupid Query
I have to share this one.
I’m working on a set of queries, tuning them. They’re all following a similar pattern. They’re passing in XML data sets which are then shredded into a temporary table. Once that’s done, the temp table is used in a series of joins to other tables to return the data. Some of the queries were missing indexes, some were structured a bit poorly, but overall, it was pretty standard stuff. Until…
I ran a query that looked, at first glance, the same as the rest. When I looked at the execution plan, I saw a warning symbol, one of those little exclamation points on an operator. I figured, based on the other issues with this database, that it was just some out of date or missing statistics. When I looked at the tooltip it said, “NO JOIN PREDICATE.” Huh? What the…
So I looked at the query. I’m changing what I saw, just a bit, so I don’t violate any of my business data:
SELECT DISTINCT x.MyValue
FROM #Temp t
INNER JOIN (SELECT a.MyValue, b.xvalue, c.yvalue
FROM Atable a, Btable
INNER JOIN Ctable….) AS x
ON x.xvalue = t.xvalue
AND x.yvalue = t.yvalue
Clearly Atable needs some join criteria. So I look through the hole query. It’s joining about 8 different tables and none of them refer to Atable. None of them have referential constraints with Atable. Atable is just glommed on in a cartesian join, everything with everything. Clearly this is messed up, but, as you can see, the whole point of the excercise is to get a value out of Atable. I look at the data in Atable. There are over 100 rows. In all those rows, the interesting value from Atable is identical, on each and every row. Suddenly, the DISTINCT makes sense. The rest of the query doesn’t. They joined all kinds of tables together and passed in parameters all to return a single value that can never be anything else, regardless of the parameters passed in. Further, regardless of the value of the column in ATable, they’re only ever going to get all the rows of ATable, again without regard to parameters.
I’m awaiting word from the development team to find out just what the **** they were thinking.
SQL Server 2008 PowerShell Mini-Shell
I went right back to working on learning PowerShell after the last blog post. I no more than get started when I stumble across the explanation for the mini-shell used by SQL Server 2008. It makes sense and should be read. I still don’t know enough about how PowerShell works to get all miffed over Microsofts (minor?) error, but I’m glad that they’re aware of the dirt they kicked up.
Learning PowerShell in SQL Server 2008
This blog post is pretty interesting.
http://concentratedtech.com/content/index.php/2008/06/sql-server-2008-powershell-no-no-no-no-no/
SQL Server 2008 comes with sqlps.exe, which is what this guy is ranting about.
To get around it, you can do this. Open the regular powershell command prompt. Run this:
notepad $profile
This opens up the profile used by PowerShell under your login. Now you can add these lines and save the file:
add-pssnapin SqlServerProviderSnapin100
add-pssnapin SqlServerCmdletSnapin100
You should now be able to run SQL Server commands through the regular PowerShell prompt. Depending on how you configure and run Powershell, you may also have to add those lines to scripts you create.
I may not have this quite right. I’m still learning. Here’s a suggestion that I should be updating the console file? He’s solving the same issue though.
More as I learn more.
Home of the Scary DBA 