Wednesday, September 30, 2009

On vs. Where

Does it matter if you put your criteria in the ON clause or the WHERE clause?  Well, as with most things SQL the answer is, “It depends”.  If you are dealing with INNER JOIN’s then it really doesn’t matter because the query optimizer is smart enough to come up with the same execution plan for both.  For example these 2 queries evaluate to the same execution plan:

SELECT
SOD.SalesOrderDetailID
,
SOH.SalesOrderID
FROM
Sales.SalesOrderHeader AS SOH
JOIN
Sales.SalesOrderDetail AS SOD
ON
SOH.SalesOrderID = SOD.SalesOrderID
WHERE
SOH.OrderDate >= '7/1/2004'
AND
SOH.OrderDate <
'8/1/2004'

SELECT
SOD.SalesOrderDetailID
,
SOH.SalesOrderID
FROM
Sales.SalesOrderHeader AS SOH
,
Sales.SalesOrderDetail AS SOD
WHERE
SOH.SalesOrderID = SOD.SalesOrderID
AND
SOH.OrderDate >= '7/1/2004'
AND
SOH.OrderDate < '8/1/2004'



Execution Plan



The old SQL 6.5 OUTER JOIN syntax (*= and =*) has been discontinued beginning with SQL Server 2005, so you have to do the JOIN for OUTER JOIN’s in the ON as demonstrated in this code:



SELECT

  
SOD.SalesOrderDetailID
,

  
SOH.SalesOrderID


FROM

  
Sales.SalesOrderHeader AS SOH
LEFT JOIN

  
Sales.SalesOrderDetail AS SOD


        ON SOH.SalesOrderID = SOD.SalesOrderID


WHERE

  
SOH.OrderDate >='7/1/2004'
AND

  
SOH.OrderDate <'8/1/2004'





Now let’s create a sandbox to play in.



If OBJECT_ID('sales.orders', 'U') Is Not Null
Begin
Drop Table
sales.orders;
End;

If OBJECT_ID('sales.order_items', 'U') Is Not Null
Begin
Drop Table
sales.order_items;
End;

If SCHEMA_ID('sales') Is Not Null
Begin
Drop Schema
sales;
End;

Go

Create Schema
sales;

Go
/*
Tables to hold sample data
*/
Create Table sales.orders
(
order_id INT IDENTITY(1,1)PRIMARY KEY,
customer_id INT
);

Create Table sales.order_items
(
order_detail_id INT IDENTITY(1, 1)PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT
)

/*
Load Sample data
*/
INSERT INTO sales.orders (customer_id)
SELECT TOP 5
AO.[object_id]
FROM
sys.all_objects AS AO;

INSERT INTO sales.order_items
(
order_id,
product_id,
quantity
)
SELECT
1,
1,
7
Union ALL
Select
2,
1,
4
Union ALL
Select
3,
2,
6
Union ALL
Select
4,
2,
11
Union ALL
Select
5,
3,
1;



Now we want to return all the customers who have placed an order, but we only want to return the items where the quantity is greater than 5.  Here is method 1:


Select
O.customer_id,
OI.order_id,
OI.product_id,
OI.quantity
From
sales.orders AS O LEFT OUTER JOIN
sales.order_items AS OI ON
O.order_id = OI.order_id
Where
OI.quantity > 5;



This returns:



customer_id order_id    product_id  quantity
----------- ----------- ----------- -----------
3 1 1 7
7 3 2 6
8 4 2 11


Hmmm, we know we have orders from five customers, but this only returns the three rows.  Let’s look at the execution plan:



image



What’s that nest loops (inner join) operator?  Well, by putting the criteria for the RIGHT (second) table in the WHERE clause we essentially converted our LEFT OUTER JOIN to an INNER JOIN.  The correct way to get the data we want would be this way:



SELECT

   
O.customer_id,

   
OI.order_id,

   
OI.product_id,

   
OI.quantity

FROM

   
sales.orders AS O LEFT OUTER JOIN

   
sales.order_items AS OI ON

       
O.order_id = OI.order_id AND

       
OI.quantity > 5;



This returns what we would expect to see:





customer_id order_id    product_id  quantity
----------- ----------- ----------- -----------
3 1 1 7
5 NULL NULL NULL
7 3 2 6
8 4 2 11
17 NULL NULL NULL



And here is the execution plan:




image



Where you can see the Nested Loops (left outer join) operator.




So yes, it does matter where you put your criteria when dealing with OUTER JOINS.

Wednesday, September 23, 2009

Venturing into Powershell

As you probably know if you read my blog regularly, I recently put together a presentation called, Dive into the Default Trace, and as part of that presentation I wanted to provide examples of different ways to archive the Default Trace files.  Here are the option I considered:

  1. fn_trace_gettable to load the data into a table.
  2. SSIS to archive the files.
  3. PowerShell to archive the files.
  4. xp_cmdshell and the copy command.

I immediately eliminated #4 because I don’t have xp_cmdshell enabled and because Chad Miller (@cmille19) had said in his Powershell talk that anything that requires xp_cmdshell is a good candidate for Powershell.  So I sent Chad an email asking him where to start and he was kind enough to help out.  I got a “working” script together and sent it off to Chad for review and he kindly told me my errors (there were several) and included a modified script that cleaned up my code.  I did make a couple of minor modifications after receiving the script from Chad.  The idea and starting point were mine, even if much of the finished product is Chad’s.  So here’s the code (please comment if you have any improvements):

# Written with much Assistance from Chad Miller 
# (http://chadwickmiller.spaces.live.com/Blog)
param ($serverName
)

#######################
function
Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'
),
[
string]$databaseName=$(throw 'databaseName is required.'
),
[
string]$query=$(throw 'query is required.'
))

Write-Verbose
"Get-SqlData serverName:$serverName
databaseName:$databaseName query:$query"

$connString =
"Server=$serverName;Database=$databaseName;
Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString
)
$dt = New-Object
"System.Data.DataTable"
[void]$da.fill($dt
)
$dt

}
#Get-SqlData

#######################
function
Get-TraceInfo
{
param($serverName
)

$qry =
@"
SELECT
RIGHT(T.path, CharIndex('\',
Reverse(T.path))-1) as current_file,
Substring(T.path, 1, Len(T.Path) -
CharIndex('\', Reverse(T.path))+ 1) as trace_path
FROM
sys.traces T
WHERE
T.is_default = 1
"@

Get-SqlData $serverName 'master'
$qry

}
#Get-TraceInfo

#######################
# MAIN
#######################

if ($serverName -eq $null
)
{
$serverName =
$env:COMPUTERNAME
}

$result = Get-TraceInfo
$serverName
$copy_path
= $result.trace_path +
'Archive'

if ((Test-Path -Path $copy_path) -eq $false
)
{
New-Item -Path $result.trace_path -Name 'Archive' -ItemType
directory
}

Get-ChildItem -Path $result.trace_path -Exclude $result
.current_file |
where {$_.name -like "*.trc"} | Move-Item -destination
$copy_path


Once I knew what cmdlet’s I needed the PowerShell was probably the second easiest one to implement.  I know SSIS fairly well and it got a bit complex because of having to exclude the file currently in use.  Using fn_trace_gettable is simple because it allows me to use T-SQL which I have many years experience in.



Using PowerShell is really a matter of knowing what cmdlet’s you need, once you have that, the built in help (get-help) is very good, so you can just about do anything.  PowerShell is very powerful and flexible.  It can actually do so much that I think you need to be careful.  For example, the New-Item cmdlet can create just about any item depending on the ItemType you use.  '



So while I definitely recommend that any DBA learn some PowerShell I also recommend being very careful and Test, Test, Test!

Tuesday, September 22, 2009

September OPASS Meeting Recap

OPASS met last Tuesday, September 14th, with a mini (15 minute) presentation by Todd Holmes on Backup Basics and Jorge Segarra (@SQLChicken) speaking on Policy Based Management in the main presentation.  We had  good crowd of about 20-25.

Meeting Introduction

We started the meeting with some announcements and discussion of who was going to the PASS SUMMIT.  I was officially presented as co-President of OPASS and took some time to discuss the upcoming SQLSaturday 21 – Orlando schedule as I was the one responsible for putting the schedule together.

We then had about 10 minutes of networking time.  I spent it talking with Mark, who had come to Orlando on a contract job that had been ended and was looking for work and guidance as to where to go to get solid SQL Server training.

Mini Presentation – Backup Basics

Todd did an okay job on his mini presentation on Backup Basics.  He is a first time speaker and you could tell he was a little nervous about being in front of a group.  He covered recovery models, backup types, and backup and restore command syntax.  I thought he did well to cover that much information in 15 minutes.  I don’t think I could have covered as much, especially with the number of questions that were asked.

Feature Presentation – Policy Based Management

Jorge did a good job presenting on PBM.  This was my first exposure to it and I was impressed by what you can do with it.  He went over the architecture and examples of how to use it.  I came away convinced that any DBA in a shop with more than a couple of SQL Servers should become familiar with it and use it.  We did learn one thing that is a little disturbing about having an active policy in place, like requiring stored procedure names to fit a standard.  If you have an existing procedure that does not meet the standard and you, for whatever reason, make changes to that procedure and choose to deploy those changes using a DROP and CREATE the policy will block the rollback the CREATE, but not the DROP.  Here’s an example:

/*
Existing Procedure
*/
CREATE PROCEDURE sp_getorder
(
@order_id INT
)

AS

SET NOCOUNT ON

SELECT
*
FROM
dbo.orders
WHERE
order_id = @order_id;

RETURN;

GO
/*
Now you create a policy that says that
stored procedures must begin with usp_%
*/

/*
You realize that SELECT * is a bad practice
so you want to change the procedure to only
return the required columns and you do a Drop
and Create
*/

IF OBJECT_ID(N'dbo.sp_getorder', N'P') IS NOT NULL
BEGIN
DROP PROCEDURE
dbo.sp_getorder ;
END

GO

CREATE PROCEDURE
sp_getorder
(
@order_id INT
)

AS

SET NOCOUNT ON

SELECT
order_no,
customer_id,
salesperson_id,
purchase_order_no
FROM
dbo.orders
WHERE
order_id = @order_id;

RETURN;

GO


The PBM violation will not allow the CREATE to take place, but will allow the DROP.  There may be ways to work around this, but Jorge didn’t know any off the top of his head.  This discussion was driven by a question from Kendal Van Dyke (@SQLDDBA).



Post Meeting Discussion


As usual we had a good post meeting discussion as Kendal, Jorge, Andy, and I stuck around for about an hour and others for about 30 minutes.  We talked about networking, PASS, PASS Summit, and keeping up.



As always some good value in the meeting and the post meeting discussion.

Tuesday, September 15, 2009

Orlando PASS Meeting

Tonight is the OPASS bi-monthly meeting.  Todd Holmes will be giving a mini presentation on SQL Server Backups and out main speaker will be Jorge Segarra (@SQLChicken) speaking on Policy Based Management.  Jorge is very involved in the community as a blogger, twitterer, and speaking at local user groups and SQLSaturday’s.  This will be my first time hearing Jorge speak, but I am sure it will go well as Jorge is knowledgeable and engaging.

Come on out.  Meeting starts at 6 and there is free pizza and always some type of SWAG.  We meet at End to End Training’s office in Altamonte Springs, FL, sorry that would be SQLShare’s offices (Map).

Friday, September 11, 2009

Thanks Space Coast SQL User Group

I want to thank the Space Coast User Group for having me over to speak on the Default Trace last night.  I had a great time meeting everyone and hopefully I presented some information that they all can take back to the office and use.

Space Coast is a relatively new user group but they have good core and are an enthusiastic group.  They asked good questions and 7 out of 9 (I think there were 9 I didn’t take attendance) attendees (not counting me) went to the after meeting get together at Holiday Inn.

The meeting started with some announcements and then I got to jump in and start my presentation.  I started by doing some marketing for SQLSaturday #21 – Orlando and the great seminar series scheduled the week before.  I then asked, “Before tonight, how many people knew that there is a trace running in SQL Server 2005/2008?”  Once again the majority were not even aware it existed.  We discussed what the Default Trace is, what it traces, where it is used, how to query it, and how to archive the data.  I went a little longer than an hour so I’ll have to trim it a little for SQLSaturday.  I’d probably grade myself a B-/B as I stumbled around as I changed applications to show code and do demos and had a couple of brain cramps.  I need to practice this one a few more times.  My slide deck and demo scripts are available here on SkyDrive and I have sent them to Bonnie Allard to post on the Space Coast SQL User Group web site so watch there as well.

We had some great discussions after the event about Powershell, hurricanes, software vendors, and the differences in diets around the world.

Thursday, September 10, 2009

What happened to that email?

This question:

Created script to send mails using sp_send_dbmail- working like a charm.
Now searching for a way to get result code of sent mail (like Success = Recipient got it,
Failure = Did not get regardless of the reason).
I mean SP return codes 0 (success) or 1 (failure) refer to correct mail Profile, not missing Recipient, etc.
Frankly not sure this is possible as it looks like outside Sql Server authority/responsibility?!

asked in this thread on SQLServerCentral prompted me to do some research into Database Mail.  The result of the research is that there is no way to get this information from SQL Server.

Basically the way Database Mail/sp_send_dbmail works is that the message is placed in a Service Broker queue (sp_send_dbmail returns success), the external Database Mail executable reads the queue and sends the message to the designated SMTP mail server.  If the mail server accepts the message then Database Mail is done and the status is set to sent.  So, if you have an incorrect email address or the receiving server refuses it, SQL Server has no way to know.  In order to find this out you would need to use a valid Reply To or From email address and monitor that mailbox.

Here’s the query I use for checking Database Mail:

SELECT
SEL.event_type,
SEL.log_date,
SEL.description,
SF.mailitem_id,
SF.recipients,
SF.copy_recipients,
SF.blind_copy_recipients,
SF.subject,
SF.body,
SF.sent_status,
SF.sent_date
FROM
msdb.dbo.sysmail_faileditems AS SF JOIN
msdb.dbo.sysmail_event_log AS SEL
ON SF.mailitem_id = SEL.mailitem_id

Let me know if you have any better ways to find errors for Database Mail.

Wednesday, September 9, 2009

Speaking at Space Coast User Group

I have the privilege of presenting, Dive into the Default Trace, at the Space Coast User Group, tomorrow evening (Sept. 10). 

We’ll be discussing what the default trace is, what it collects, where' it is used, how to find it, and how to query it.  I have what I think are some interesting demos and hopefully information that will help developers and DBA’s better manage and audit their SQL Servers.

I’m really looking forward to meeting Bonnie Allard and the rest of the group.

Tuesday, September 8, 2009

Windows 7, UAC, and SQL Server

This is just a quick note, almost a continuation of my Access Denied, Not Possible post.  I have been working on some queries for a Default Trace presentation that I am preparing for the Space Coast User Group and SQLSaturday #21 – Orlando, and one of the queries has to do with trying to find logins that have gained access through a Windows Group.  Since I am working on my laptop (no domain), I decided to add the Builtin\Administrators group, delete my explicit login, and get access via the group.  Interestingly enough, in order to get access to SQL Server via Builtin\Administrators you need to run SSMS as Administrator.  Here’s the error I get when not running SSMS as administrator:

SSMSLoginFailWhen I did run SSMS as administrator, I was able to successfully login to my local SQL Server.

No, I do not leave Builtin\Adminstrators as sysadmin on my servers and with SQL Server 2008, I do not have it at all.

Saturday, September 5, 2009

Networking Successes

Over the last few weeks I’ve had several instances where I’ve had to learn new things and, in my struggles, have had the opportunity to get help from people I have met recently (both in person and on-line).  Notice I said “opportunity”.  One thing I’ve learned recently is that people like to help other people!   As part of my professional development I’ve been attempting to work on my networking skills, and, in my opinion, networking is more than meeting people, it is interacting with them to help and to be helped.

What the heck is jQuery?

The current project I am working on is using ASP.NET MVC and AJAX for the web site and my HTML and javascript skills are not strong so I was reading and workring with Professional ASP.NET MVC 1.0.  As I went through the examples I encountered a jQuery script that was not working.  I posted a question on Twitter which was answered by Jeremiah Peschka (Blog|Twitter).  He sent me his email address and offered to look at the script for me.  He also forwarded on the problem to a jQuery guru he knows.  All that effort and we’ve never met!  See people DO like to help!

How does this work in Powershell?

A few months ago I began interacting with Chad Miller (Blog|Twitter) on Twitter and was able to set him up to speak at my local user group (OPASS).  Chad is a Powershell guru and presented on T-SQL vs. Powershell back in July.  I’m working on a presentation about the Default Trace and I wanted to provide some examples of how to archive the Default Trace files/data.  This seemed like a good opportunity to learn some Powershell, so I sent Chad an email asking him to point me in the right direction, which he did.  I completed a “working” Powershell script and sent it to him for review.  He responded with explanations of what I had done wrong and a corrected script.

Why can’t I get this file processed?

Again as part of the Default Trace presentation I wanted to present a solution using SSIS.  Now I have some experience with SSIS and consider myself to be at an intermediate level so I figured I could get it done without trouble.  Well, I was wrong.  I had what I thought was a working solution, until I got Log_10.trc at the same time as Log_9.trc.  The ForEach File Enumerator orders files by name so the active Log_10.trc file was the first file the File System Task attempted to move and it is locked, thus the task failed.  So once I again I used Twitter to ask an SSIS guru, Andy Leonard (Blog|Twitter), if there was a way to change the sort order on the ForEach File Enumerator.  He said that you needed to script it, unfortunately.  He also emailed me an example script.

Those are just 3 instances where I’ve had the opportunity to truly practice networking (I blogged about another here).  Interacting with people and using those interactions to learn new skills and share your skills.  In my mind this is real networking.  Sure these are examples where I got something from my network, but there have been times where I’ve been on the other side, and you’d better believe if I can help out any of these guys I’ll do it!

Thursday, September 3, 2009

24 Hours of PASS

From 7:45 pm (Eastern DST) on Tuesday, September 1st until 8:00 pm on Wednesday, September 2 PASS provided free online seminars each hour.  It was a veritable who’s who in SQL Server and a great preview of what’s to come at the PASS Summit in November.  Unlike Tom LaRock (aka SQLRockstar)  and Jonathan Kehayias I did not try to stay up and attend every session, I chose to cherry pick the sessions I would attend, none of which were in the middle of the night.  The sessions I did attend went really well with only 1 minor technical glitch during a session, which is very impressive when you think that every session I was in had at least 250 attendees.  There were some issues with errors in the links to the sessions on the 24 hours of PASS website, but Twitter definitely helped there.  Here are the sessions I attended with a few notes on what I picked up:

Session 1 – 10 Big Ideas in Database Design - Louis Davidson and Paul Nielsen

A big one for me here was that Classes <> Tables.  While ORM tools want to create a class for each table, this does not really work with a good relational design there really is not a one to one relationship there.  With a truly normalized database you will probably need to have a class that spans multiple tables. 

Session 3 - Team Management Fundamentals – Kevin Kline 

This was probably my favorite session.  I am not a manager and I really don’t want to be a manager, but I do want to understand how to manage and especially how to run meetings.  Kevin offered lots of great advice, but my one takeaway was that every meeting should end with an ACTION PLAN.  You should know what is going to happen because of this meeting and what tasks you are responsible for.  I think I heard this phrase at least 4 times in the hour.

Session 11 – Effective Indexing – Gail Shaw

This was at 6:00 am my time, and I’m not a morning person, but as a DBA/Developer I don’t think you can ever know enough about Indexing so I made a point of being up for this session.  Gail is also a friend on SQLServerCentral that I have learned a ton from there and from her blog so I knew it would be a good session.  Gail did a great job explaining how indexes work with equality and inequality operators, and how they work from left to right so you want your most selective column used in an equality operation first in your key list.  I used to make the mistake of putting bit columns, like an active flag, first because they are typically used in every query.  This is a bad choice because they are typically not very selective. 

Session 13 – Query Performance Tuning 101 – Grant Fritchey

Wow! If this was a 101 session I’d hate to be in 401 session with Grant!  Tons of good information about creating a baseline so you KNOW if you are having performance problems, what to look for, where to look, and the tools to use to look (PerfMon, Profiler, oops, sorry Grant, SQLTrace, DMV’s).  One thing that Grant mentioned as did Paul and Louis, “normalization is not evil”.  Meaning that a properly normalized database (~3rd normal form) usually does not need to be denormalized for performance reasons, if you have proper indexes.

Session 17 – Building a Better Blog – Steve Jones

Another very popular session, I guess because so many of us have blogs now.  Steve had some great tips about keeping your blog technical/professional and if you want to blog about personal things start another blog.  He did hit one hot button issue when he recommended hotlinking images instead of downloading and embedding in your blog.  He believes you should hotlink because that can protect you better from copyright violations, while others considering hotlinking to be bandwidth stealing from the hosting site.  I don’t use many images, although it is recommended so maybe I’ll start. 

A main point he made was to “Praise Publically, Criticize Privately”.  Basically don’t call someone out in your blog.  If you have an issue with someone keep it private.  Remember that your blog is public so current and prospective employers may see it.  This is really just a good piece of advice for every situation.  I did disagree a little when he said he does not comment on blog posts where he thinks there is an error, but rather contacts the author privately. I do tend to comment on blog posts where I think there is an error, but I try to do it constructively and provide solid reasons and examples for my opinion.

Session 21 – What’s Simple about Simple Recovery Model – Kalen Delaney

I can’t say that I’ve read all of Kalen’s books, but I have read a couple so I knew there’d be good information in this session and there was.  She really covered much more than the title implies.  She discussed how the transaction log works and how the different recovery models affect the transaction log.  Between sessions like this and Paul Randal’s blog I think I may eventually understand the transaction log.  The main point is that you need to carefully choose your recovery model and understand that the Simple Recovery model does NOT mean that the transaction log won’t grow, but it does mean that you do not (cannot) back up the transaction and CANNOT restore to a point time.

Overall, it was a great event (series of events?).  As I mentioned in my post, No Training Budget Still No Excuse, with events like these there really is no excuse for not taking time for professional development.  It’s YOUR career and YOU need to manage it.  Even if you had to choose 1 or 2 sessions that’s better than doing nothing.  It was also a great preview of the PASS Summit as all the speakers will be speaking there as well.

2009PASS_Signature01