24Oct 2008 by Grant Fritchey 4 Comments

Amazingly Stupid Query

T-SQL
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…
Read More
22Oct 2008 by Grant Fritchey No Comments

SQL Server 2008 PowerShell Mini-Shell

PowerShell
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.
Read More
22Oct 2008 by Grant Fritchey 5 Comments

Learning PowerShell in SQL Server 2008

PowerShell
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…
Read More
22Oct 2008 by Grant Fritchey No Comments

SQL Server 2005 SP3

SQL Server
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.
Read More
09Oct 2008 by Grant Fritchey 5 Comments

PASS Schedule Posted

PASS
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…
Read More
08Oct 2008 by Grant Fritchey No Comments

Database Engine Tuning Advisor

Uncategorized
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.
Read More
07Oct 2008 by Grant Fritchey 5 Comments

Composite Projects in DBPro

Visual Studio
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.…
Read More
07Oct 2008 by Grant Fritchey 2 Comments

SQL Server 2008: Database Engine Tuning Advisor

Tools
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:…
Read More
01Oct 2008 by Grant Fritchey 2 Comments

PASS Summit T-Shirt

PASS
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.
Read More
01Oct 2008 by Grant Fritchey No Comments

Simple Talk Editorial on PASS

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…
Read More