sqlmashup
PostgreSQL DBA

no more blogging at this site as I begin learning the ropes as a new postgresql dba. cheers!

Tammy
Today would have been my sister’s 43rd birthday. I love you Tammy!
Time
There is an illness in the family which has been the focus of my time.
Wednesday Weekly #sqlserver Links for 2011-40

Wednesday Weekly #sqlserver Links 

Grab a plate and load it up.

Current Feed Content

T-SQL Tuesday #23 – Joins #tsql2sday

T-SQL Tuesday

The host for this week early blog party is Stuart Ainsworth. You can click on the image above to read the invitation and about the topic of JOINS. You can also find there within the comments, links to the posts of all participants. You could also just wait for the round-up to be posted. For a round-up of round-ups please see this page by the blog party godfather Adam Machanic, A Year of Tuesdays: T-SQL Tuesday Meta-Roundup


Image002

Joining

If you have experience with joining the military then thank you sir or ma’am but as a SQL Server DBA you are sure to have experience with joining tables using Transact-SQL. My first blog post was on this exact same subject and this post will be a condensed version of it, SQL Server Joins using Playing Cards There are three primary JOIN operators the Query Optimizer will pick from: Nested Loops, Merge Join and Hash Match.

Image004
Nested-Loops

Imagine you were asked to find all the Aces in a deck of cards. How would you accomplish that? You would probably flip them all over and scan across them looking for the Aces. You would likely do the same if you were told that this would repeat for three, four or five different ranks. What if you were told it would be for eleven, twelve or even all thirteen different ranks?

Image006
Hash Match

If you were going to match many different ranks then you would probably decide scanning would be too repetitive. Instead you might break the deck into a pile for each rank. The time it took to reorganize the cards will be made-up by not having to look at every single card when another rank is asked for. What if you were told to match all fifty-two cards one-to-one between two decks?

Image008

Merge Join

We need to take a small leap of faith to continue using this card matching analogy. For now image that you have the ability to perform a perfect shuffle, where exactly one card from each the left and the right stacks fall together through-out the entire shuffle. If you had to find all fifty-two card matches you would likely go with a Hash Match but now that you are bestowed with the gift of Perfect Shuffle you have a better option. You quickly sort each deck then perform a perfect shuffle and BAM! The analogy is more appreciable when the cards are already sorted or the number of them is much higher than fifty-two.

Image010

See Also

Physical Join Operators in SQL Server – Nested Loops - SQL Server Performance

Physical Join Operators in SQL Server – Hash Operator - SQL Server Performance

Physical Join Operators in SQL Server – Merge Operator - SQL Server Performance

Nested Loops Join - Craig Freedman’s SQL Server Blog - Site Home - MSDN Blogs

Hash Join - Craig Freedman’s SQL Server Blog - Site Home - MSDN Blogs

Merge Join - Craig Freedman’s SQL Server Blog - Site Home - MSDN Blogs

Image012

People

Stuart Ainsworth

Adam Machanic

Ami Levin

Craig Freedman

Wednesday Weekly #sqlserver Links for 2011-39

Wednesday Weekly #sqlserver Links 

Help yourself to this link feast.

Current Feed Content

Wednesday Weekly #sqlserver Links for 2011-38

Wednesday Weekly #sqlserver Links 

Here are some yummy links from this week.

Current Feed Content

Wednesday Weekly #sqlserver Links for 2011-37

Wednesday Weekly #sqlserver Links 

Biggest SQL Server feast to date so be sure to come back for seconds.

Current Feed Content

T-SQL Tuesday #22 – Data Presentation #tsql2sday

T-SQL Tuesday

Robert Pearl has heeded the call of hosting the T-SQL Tuesday blog party this month. Please click on the #tsql2day image above to learn more about why the topic is “data-presentation”. If you want to read more party-style blog posts then also check out the summary by Adam Machanic called A Year of Tuesdays: T-SQL Tuesday Meta-Roundup.

Image002

Presents

Who presents the data to your users? There are occasions when the DBA will be asked to write T-SQL for Manipulating Result Sets. Mr. Pearl mentioned having experience with Using Common Table Expressions in the invitation. I had a similar experience with Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS. Who should present data to the users? Although this blog party has T-SQL in the title this post will be more theoretical than practical.

Image004

Tiers

There are always exceptions to any rule but as a general rule I subscribe to the N-Tier / Multi-tier architecting of applications. There are entire books about the benefits but for the sake of a short post I would like to share just two benefits of this architecture for a DBA. If you plan to stay for the soapboxing then please take a minute and look over this diagram by Damon Armstrong on Multi-tier architecture from his post .NET Application Architecture: the Data Access Layer.

Image006

Tools

Transact-SQL and SQL Server are used to scatter and gather data. CLR and Business Objects are used for control flow logic. Windows Presentation Foundation and Forms/Controls are used for user interface presentation. Each is a great tool for performing their intended task. You have NEVER used a crescent wrench as a hammer right? In the same tongue in cheek tone, you such NEVER use T-SQL to figure out the next user product suggestion and NEVER use T-SQL to display a dollar sign on the right side of a number.

Image008

Really?

Let us pretend that you inherited a stored procedure that does format the output so that there is a dollar sign displayed on the right side of a number. Who do you suppose would be tasked if the user wanted a different format?  (Euros, two decimal places, in millions, negatives in parenthesis)  Each of these has the potential to become a major time suck and also cause some wicked stored procedure sprawl. It has happened, it is still happening and will probably continue to happen but hopefully you are better informed for avoiding the problem.

Image009

People

Robert Pearl

Adam Machanic

Damon Armstrong

Welcome to the show

#powershell deepest folder top 1 alphabetical
Get-ChildItem -path “C:\getdeepestfolder\top1alphabetical" -recurse | Sort-Object -property @{Expression={[regex]::matches($_.FullName, “\”).count}; Ascending=$false} | Select-Object -property “FullName” -first 1
Wednesday Weekly #sqlserver Links for 2011-36

Wednesday Weekly #sqlserver Links 

Free buffet of some great content seen this week.

Current Feed Content

Wednesday Weekly #sqlserver Links for 2011-35

Wednesday Weekly #sqlserver Links 

Free buffet of some great content seen this week.

Current Feed Content

#sqlserver meta-URLs

Someone sent a request effectively asking for a whitelist of URLs for SQL Server DBAs.

Instead of a long list of all URLs here is a short list of meta-URLs.

SQL Server Blog listPASS - The Professional Association for SQL Server > Community > Blog Directory

SQL Server Community listSQL Server - Related Sites

SQL Server Vendor listSQL Server Product Categories

Q&A listList of question and answer websites - Wikipedia, the free encyclopedia

Do you see anything missing? Can you recommend other meta-URLs?

Wednesday Weekly #sqlserver Links for 2011-34

Wednesday Weekly #sqlserver Links 

Free buffet of some great content seen this week.

Current Feed Content

#sqlservermyth cost threshold for parallelism = seconds
134546163066liger

"It’s like a lion and a tiger mixed… bred for its skills in magic."

The definition of cost threshold for parallelism is similar to this definition of a Liger. It is based on fact but when retold it magically becomes inaccurate. “The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration." The key words are the last five. They are intended to tell you that the calculation for estimating seconds is based on the characteristics of some SQL Server which probably will not match your current, past or future SQL Servers.


Image003

So what time is it?

Try executing the script below on a SQL Server with its cost threshold for parallelism value set between 1 and 24. You should find that querying 33487 records (each requiring one logical read) will not parallelize but querying 33488 records will parallelize. It has already been executed on several different SQL Server versions with varying hardware characteristics and the same result occurred each time. Attempts were also made to try and cause parallelizing to occur with 33487 records but increased CPU due to calculations of math functions but the attempts were unsuccessful. Let us temporarily ass/u/me that IO is the only differentiator to cause parallelism even though that is not the case. If it were true then it would mean the time it takes your SQL Server to perform 33488 reads is what time a query would need to take before parallelism will be used. At threshold 25 the reads breakpoint was 33670 and at threshold 45 the reads breakpoint was 60608.

(NOTE: this was an overly simplistic test for a feature as complex as parallel execution plans but hopefully it still proves the intended point)

Image005

Myth Busting

Tony Davis : SQL Server Myths

Demystifying SQL Server: SQL Server Myths Debunked (Part 1)

Demystifying SQL Server: SQL Server Myths Debunked (Part 2)

CommonSQLServerMyths.pdf  <a pox on your house! if you do not read all 59 pages>

T-SQL Tuesday #11 Round up, Misconceptions in SQL Server « « Sankar ReddySankar Reddy

T-SQL Tuesday: Common SQL Server myths – Series I–Setup and Services « TroubleshootingSQL

Common SQL Server myths – Series II – SQL Memory « TroubleshootingSQL

My Favourite SQL Server Myths and Realities - LivingForSqlServer - SQLServerCentral.com

Image007

People

Tony Davis

Adam Haines

Paul Randal

Sankar Reddy

Amit Banerjee

Ramkumar Gopal

Image009
Script

SET NOCOUNT ON;

GO

SET ROWCOUNT 0;

GO

CREATE DATABASE [DELETEME] ON

                PRIMARY (NAME = N’DELETEME_data’, FILENAME =N’C:\Temp\DELETEME_data.mdf’, SIZE = 16384KB , FILEGROWTH =16384KB)

                LOG ON (NAME = N’DELETEME_log’, FILENAME =N’C:\Temp\DELETEME_log.ldf’, SIZE = 16384KB , FILEGROWTH =16384KB);

GO

ALTER DATABASE [DELETEME] SET AUTO_CREATE_STATISTICS OFF;

GO

ALTER DATABASE [DELETEME] SET AUTO_UPDATE_STATISTICS OFF;

GO

ALTER DATABASE [DELETEME] SET RECOVERY FULL;

GO

ALTER DATABASE [DELETEME] SET MULTI_USER;

GO

USE [DELETEME];

GO

CREATE TABLE [dbo].[JUNK] (TRASH CHAR(8000) NOT NULL DEFAULT SPACE(8000));

GO

INSERT INTO [dbo].[JUNK] VALUES (DEFAULT);

GO

INSERT INTO [dbo].[JUNK] SELECT * FROM [dbo].[JUNK];

GO 15

DECLARE @MORE_ROWS INT;

SELECT @MORE_ROWS = 33487 - COUNT(*) FROM [dbo].[JUNK];

SET ROWCOUNT @MORE_ROWS

INSERT INTO [dbo].[JUNK] SELECT * FROM [dbo].[JUNK];

GO

CHECKPOINT;

GO

SET ROWCOUNT 0;

GO

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

GO

PRINT 'PREDICTING WITHOUT PARALLELISM';

GO

SET SHOWPLAN_ALL ON;

GO

SELECT DISTINCT 0 FROM [dbo].[JUNK];

GO

SET SHOWPLAN_ALL OFF;

GO

SET STATISTICS IO ON;

GO

SET STATISTICS TIME ON;

GO

SELECT DISTINCT 0 FROM [dbo].[JUNK];

GO

SET STATISTICS IO OFF;

GO

SET STATISTICS TIME OFF;

GO

PRINT 'ADDING JUST 1 MORE ROW';

GO

INSERT INTO [dbo].[JUNK] VALUES (DEFAULT);

GO

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

GO

PRINT 'PREDICTING WITH PARALLELISM';

GO

SET SHOWPLAN_ALL ON;

GO

SELECT DISTINCT 0 FROM [dbo].[JUNK];

GO

SET SHOWPLAN_ALL OFF;

GO

SET STATISTICS IO ON;

GO

SET STATISTICS TIME ON;

GO

SELECT DISTINCT 0 FROM [dbo].[JUNK];

GO

SET STATISTICS IO OFF;

GO

SET STATISTICS TIME OFF;

GO

DROP TABLE [dbo].[JUNK];

GO

USE [master];

GO

DROP DATABASE [DELETEME];

GO

Bustin’ makes me feel good