SQLblog.com - The SQL Server blog spot on the web

MicroTraining: Executing SSIS 2012 Packages 22 May 10:00 AM EDT (Free!)

andyleonard — Wed, 16 May 2012 12:00:00 GMT

I am pleased to announce the latest (free!) Linchpin People microtraining event will be held Tuesday 22 May 2012 at 10:00 AM EDT. The topic will be Executing SSIS 2012 Packages. In this presentation, I will be demonstrating several ways to execute SSIS 2012 packages. Register here ! Interested in learning about more microtraining from Linchpin People – before anyone else? Sign up for our newsletter ! :{>...(read more)

Book Review (Book 11) - Applied Architecture Patterns on the Microsoft Platform

BuckWoody — Tue, 15 May 2012 16:50:34 GMT

This is a continuation of the books I challenged myself to read to help my career - one a month, for year. You can read my first book review here, and the entire list is here. The book I chose for April 2012 was: Applied Architecture Patterns on the Microsoft Platform. I was traveling at the end of last month so I’m a bit late posting this review here.

Why I chose this book:

I actually know a few of the authors on this book, so when they told me about it I wanted to check it out. The premise of the book is exactly as it states in the title - to learn how to solve a problem using products from Microsoft.

What I learned:

I liked the book - a lot. They've arranged the content in a "Solution Decision Framework", that presents a few elements to help you identify a need and then propose alternate solutions to solve them, and then the rationale for the choice. But the payoff is that the authors then walk through the solution they implement and what they ran into doing it.

I really liked this approach. It's not a huge book, but one I've referred to again since I've read it. It's fairly comprehensive, and includes server-oriented products, not things like Microsoft Office or other client-side tools. In fact, I would LOVE to have a work like this for Open Source and other vendors as well - would make for a great library for a Systems Architect. This one is unashamedly aimed at the Microsoft products, and even if I didn't work here, I'd be fine with that. As I said, it would be interesting to see some books on other platforms like this, but I haven't run across something that presents other systems in quite this way.

And that brings up an interesting point - This book is aimed at folks who create solutions within an organization. It's not aimed at Administrators, DBA's, Developers or the like, although I think all of those audiences could benefit from reading it. The solutions are made up, and not to a huge level of depth - nor should they be. It's a great exercise in thinking these kinds of things through in a structured way.

The information is a bit dated, especially for Windows and SQL Azure. While the general concepts hold, the cloud platform from Microsoft is evolving so quickly that any printed book finds it hard to keep up with the improvements.

I do have one quibble with the text - the chapters are a bit uneven. This is always a danger with multiple authors, but it shows up in a couple of chapters. I winced at one of the chapters that tried to take a more conversational, humorous style. This kind of academic work doesn't lend itself to that style.

I recommend you get the book - and use it. I hope they keep it updated - I'll be a frequent customer. :)

Messages do not always appear in [catalog].[event_messages] in the order that they occur [SSIS]

jamiet — Tue, 15 May 2012 15:07:00 GMT

This is a simple heads up for anyone doing SQL Server Integration Services (SSIS) development using SSIS 2012. Be aware that messages do not always appear in [catalog].[event_messages] in the order that they occur, observe…

In the following query I am looking at a subset of messages in [catalog].[event_messages] and ordering them by [event_message_id]:

SELECT [event_message_id],[event_name],[message_time],[message_source_name]
FROM   [catalog].[event_messages] em
WHERE  [event_message_id] BETWEEN 290972 AND 290982
ORDER  BY [event_message_id] ASC
--ORDER	BY [message_time] ASC

Take a look at the two rows that I have highlighted, note how the OnPostExecute event for “Utility GetTargetLoadDatesPerETLIfcName” appears after the OnPreExecute event for “FELC Loop over TargetLoadDates”, I happen to know that this is incorrect because “Utility GetTargetLoadDatesPerETLIfcName” is a package that gets executed by an Execute Package Task prior to the For Each Loop “FELC Loop over TargetLoadDates”:

If we order instead by [message_time] then we see something that makes more sense:

SELECT [event_message_id],[event_name],[message_time],[message_source_name]
FROM   [catalog].[event_messages] em
WHERE  [event_message_id] BETWEEN 290972 AND 290982
--ORDER	BY [event_message_id] ASC
ORDER  BY [message_time] ASC

We can see that the OnPostExecute for “Utility GetTargetLoadDatesPerETLIfcName” did indeed occur before the OnPreExecute event for “FELC Loop over TargetLoadDates”, they just did not get assigned an [event_message_id] in chronological order. We can speculate as to why that might be (I suspect the explanation is something to do with the two executables appearing in different packages) but the reason is not the important thing here, just be aware that you should be ordering by [message_time] rather than [event_message_id] if you want to get 100% accurate insights into your executions.

@Jamiet

How SQL Saturday could be better

AaronBertrand — Tue, 15 May 2012 12:58:00 GMT

I've been to a lot of SQL Saturdays. They are great events to attend - from a community standpoint, from a learning standpoint, and from a speaker growth standpoint. Who could ask for more, right? Great sessions, from passionate speakers willing to both teach and learn, fantastic networking opportunities and lunch. All for free, or at a very low cost - some events need to recover costs and charge $10 for lunch. Still a phenomenal bargain IMHO. But we all know that these events aren't perfect... there...(read more)

SQL Server 2008 R2 Service Pack 2 CTP is available

AaronBertrand — Mon, 14 May 2012 18:23:00 GMT

You can download the Service Pack 2 CTP from the following URL: http://www.microsoft.com/en-us/download/details.aspx?id=29848 The build # is 10.50.3720. This service pack contains all of the fixes from Service Pack 1 & Cumulative Updates 1 through 5, and a couple of other minor fixes (a couple of SSRS bugs and a bug about an ALTER TABLE batch not being cached correctly). It does not include fixes from Service Pack 1 Cumulative Update #6, which I mentioned recently . You should *NOT* install this...(read more)

SQL Rally Presentations

AllenMWhite — Fri, 11 May 2012 13:48:00 GMT

As I drove to Dallas for this year's SQL Rally conference (yes, I like to drive) I got a call asking if I could step in for another presenter who had to cancel at the last minute. Life happens, and it's best to be flexible, and I said sure, I can do that. Which presentation would you like me to do? (I'd submitted a few presentations, so it wasn't a problem.) So yesterday I presented "Gathering Performance Metrics With PowerShell" at 8:45AM, and my newest presentation, "Manage SQL Server 2012 on Windows...(read more)

SQLCMD Mode: give it one more chance

Maria Zakourdaev — Fri, 11 May 2012 13:26:00 GMT

- Click on me. Choose me. - asked one forgotten feature when some bored DBA was purposelessly wondering through the Management Studio menu at the end of her long and busy working day.

- Why would I use you? I have heard of no one who does. What are you for? - perplexedly wondered aged and wise DBA. At least that DBA thought she was aged and wise though each day tried to prove to her that she wasn't.

- I know you. You are quite lazy. Why would you do additional clicks to move from window to window? From Tool to tool ? This is irritating, isn't it? I can run windows system commands, sql statements and much more from the same script, from the same query window!

- I have all my tools that I‘m used to, I have Management Studio, Cmd, Powershell. They can do anything for me. I don’t need additional tools.

- I promise you, you will like me. – the thing continued to whine .

- All right, show me. – she gave up. It’s always this way, she thought sadly, - easier to agree than to explain why you don’t want.

- Enable me and then think about anything that you always couldn’t do through the management studio and had to use other tools.

- Ok. Google for me the list of greatest features of SQL SERVER 2012.

- Well... I’m not sure... Think about something else.

- Ok, here is something easy for you. I want to check if file folder exists or if file is there. Though, I can easily do this using xp_cmdshell …

- This is easy for me. – rejoiced the feature.

By the way, having the items of the menu talking to you usually means you should stop working and go home. Or drink coffee. Or both. Well, aged and wise dba wasn’t thinking about the weirdness of the situation at that moment.

- After enabling me, – said unfairly forgotten feature (it was thinking of itself in such manner) – after enabling me you can use all command line commands in the same management studio query window by adding two exclamation marks !! at the beginning of the script line to denote that you want to use cmd command:

-Just keep in mind that when using this feature, you are actually running the commands ON YOUR computer and not on SQL server that query window is connected to. This is main difference from using xp_cmdshell which is executing commands on sql server itself. Bottomline, use UNC path instead of local path.

- Look, there are much more than that. - The SQLCMD feature was getting exited.- You can get IP of your servers, create, rename and drop folders. You can see the contents of any file anywhere and even start different tools from the same query window:

Not so aged and wise DBA was getting interested: - I also want to run different scripts on different servers without changing connection of the query window.

- Sure, sure! Another great feature that CMDmode is providing us with and giving more power to querying. Use “:” to use additional features, like :connect that allows you to change connection:

- Now imagine, you have one script where you have all your changes, like creating staging table on the DWH staging server, adding fact table to DWH itself and updating stored procedures in the server where reporting database is located.

- Now, give me more challenges!

- Script out a list of stored procedures into the text files.

- You can do it easily by using command :out which will write the query results into the specified text file. The output can be the code of the stored procedure or any data. Actually this is the same as changing the query output into the file instead of the grid.

- Now, take all of the scripts and run all of them, one by one, on the different server.

- Easily

- Come on... I’m sure that you can not...

-Why not? Naturally, I can do it using :r commant which is opening a script and executing it. Look, I can also use :setvar command to define an environment variable in SQLCMD mode. Just note that you have to leave the empty string between :r commands, otherwise it’s not working although I have no idea why.

- Wow.- She was really impressed. - Ok, I’ll go to try all those…

-Wait, wait! I know how to google the SQL SERVER features for you! This example will open chrome explorer with search results for the “SQL server 2012 top features” ( change the path to suit your PC):

“Well, this can be probably useful stuff, maybe this feature is really unfairly forgotten”, thought the DBA while going through the dark empty parking lot to her lonely car. “As someone really wise once said: “It is what we think we know that keeps us from learning. Learn, unlearn and relearn”.

DATEFROMPARTS

jamiet — Thu, 10 May 2012 08:53:00 GMT

I recently overheard a remark by Greg Low in which he said something akin to "the most interesting parts of a new SQL Server release are the myriad of small things that are in there that make a developer's life easier" (I'm paraphrasing because I can't remember the actual quote but it was something like that).

The new DATEFROMPARTS function is a classic example of that . It simply takes three integer parameters and builds a date out of them (if you have used DateSerial in Reporting Services then you'll understand).

Take the following code which generates the first and last day of some given years:

SELECT 2008 AS Yr INTO #Years UNION ALL SELECT 2009 UNION ALL SELECT 2010 UNION ALL SELECT 2011 UNION ALL SELECT 2012

SELECT [FirstDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 101)))
,      [LastDayOfYear]  = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 1231)))
FROM   #Years y

here are the results:

That code is pretty gnarly though with those CONVERTs in there and, worse, if the character string is constructed in a certain way then it could fail due to localisation, check this out:

SET LANGUAGE french;
SELECT dt,Month_Name=DATENAME(mm,dt)
FROM   (
       SELECT  dt = CONVERT(DATETIME,CONVERT(CHAR(4),y.[Yr]) + N'-01-02')
       FROM    #Years y
       )d;
SET LANGUAGE us_english;
SELECT dt,Month_Name=DATENAME(mm,dt)
FROM   (
       SELECT  dt = CONVERT(DATETIME,CONVERT(CHAR(4),y.[Yr]) + N'-01-02')
       FROM    #Years y
       )d;

Notice how the datetime has been converted differently based on the language setting. When French, the string "2012-01-02" gets interpreted as 1st February whereas when us_english the same string is interpreted as 2nd January.

Instead of all this CONVERTing nastiness we have DATEFROMPARTS:

SELECT [FirstDayOfYear] = DATEFROMPARTS(y.[Yr],1,1)
,      [LasttDayOfYear] = DATEFROMPARTS(y.[Yr],12,31)
FROM   #Years y

How much nicer is that? The bad news of course is that you have to upgrade to SQL Server 2012 or migrate to SQL Azure if you want to use it, as is the way of the world!

Don't forget that if you want to try this code out on SQL Azure right this second, for free, you can do so by connecting up to AdventureWorks On Azure. You don't even need to have SSMS handy - a browser that runs Silverlight will do just fine. Simply head to https://mhknbn2kdz.database.windows.net/ and use the following credentials:

Database AdventureWorks2012

User sqlfamily

Password sqlf@m1ly

One caveat, SELECT INTO doesn't work on SQL Azure so you'll have to use this instead:

DECLARE @y TABLE (
 [Yr] INT
);
INSERT @y([Yr])
SELECT 2008 AS Yr UNION ALL SELECT 2009 UNION ALL SELECT 2010 UNION ALL SELECT 2011 UNION ALL SELECT 2012;

SELECT [FirstDayOfYear] = DATEFROMPARTS(y.[Yr],1,1)
,      [LastDayOfYear]  = DATEFROMPARTS(y.[Yr],12,31)
FROM @y y;
SELECT [FirstDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 101)))
,      [LastDayOfYear]  = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 1231)))
FROM @y y;

@Jamiet

Speaking at SQLRelay. Will you be there?

jamiet — Wed, 09 May 2012 12:26:00 GMT

SQL Relay (#sqlrelay) is fast approaching and I wanted to take this opportunity to tell you a little about it.

SQL Relay is a 5-day tour around the UK that is taking in five Server Server user groups, each one comprising a full day of SQL Server related learnings. The dates and venues are:

Click on the appropriate link to see the full agenda and to book your spot.

SQL Relay features some of this country's most prominent SQL Server speakers including Chris Webb, Tony Rogerson, Andrew Fryer, Martin Bell, Allan Mitchell, Steve Shaw, Gordon Meyer, Satya Jayanty, Chris Testa O'Neill, Duncan Sutcliffe, Rob Carrol, me and SQL Server UK Product Manager Morris Novello so I really encourage you to go - you have my word it'll be an informative and, more importantly, enjoyable day out from your regular 9-to-5.

I am presenting my session "A Lap Around the SSIS Catalog" at Edinburgh and Manchester so if you're going, I hope to see you there.

@Jamiet

Publish Profile Files in SQL Server Data Tools (SSDT)

jamiet — Tue, 08 May 2012 22:07:36 GMT

I have been using SQL Server Data Tools (SSDT) both at work and on some hobby projects for quite a few weeks now and of all the new features I have to say the one that I am appreciating the most is Publish Profile files. I have been searching around on MSDN for an article that explains Publish Profile files but it seems no such article exists so I’ll attempt to surmise here.

Publish Profile files are, essentially, a collection of all the property key-value pairs that are needed to deploy a database model (i.e. a .dacpac) to some target database. Those properties include (but are not limited to):

database name
connection string
whether or not to publish SSDT projects on which the current project has a dependency
Recreate the database from scratch (or not)
Backup before deploy
Drop unknown objects in target
SQLCMD Variables

Those that have used the predecessor to SSDT, Visual Studio Database Projects, will be familiar with a lot of those properties however in that case the properties had to be specified on a case-by-case basis; typically in an msbuild script or similar. Often those scripts are not maintained by a developer – rather they are maintained by a DevOps team that are not familiar with the code being deployed and as a developer myself that’s not a situation that I’m at all comfortable with. On my most recent project where Visual Studio Database Projects were being used I faced the maddening situation where every new SQLCMD variable added to a project required an email to be sent to the DevOps team to ask them to update their scripts accordingly. As you can imagine human errors crept in (on our side more than the DevOps side) and we ended up deploying projects with the wrong SQLCMD values. Moreover, we had to deal with different DevOps folks and often they would store the values in different places; totally infuriating, believe me.

Publish Profile files make this process much easier because we can define all those properties on a per-environment basis and keep them in a dedicated Publish Profile file. The obvious benefit then is that a Publish Profile file abstracts all of the environment-specific information into a single file so deploying an SSDT project now requires only two things; the build output (i.e. a .dacpac file) and a Publish Profile file:

>sqlpackage.exe /sf:MyDB.dacpac /pr:DEV.publish.xml

The implied benefit (and this is what I really like about them) is that Publish Profile files are a source code artefact that a developer maintains the same as they would any other source code artefact, all that the DevOps people need are the build output (i.e. a .dacpac file) and an appropriately named Publish Profile file. Developers are now wholly in charge of how their code gets deployed which keeps them happy and the DevOps people have less work to do – so they’re a happy bunch too! I’m not saying that the wrong values won’t ever be supplied but at least now we know exactly where to go to fix those errors.

SSDT also provides a friendly UI for maintaining these Publish Profile files. Double-click on one and this UI appears:

Its fairly self-explanatory to fill these things out. A connection string, a database name and values for each SQLCMD variable defined within the project and you’re pretty much there. (I have written previously about one other benefit of Publish Profile files - that they can be used to make SQLCMD variables mandatory. Read more at SQL Server Data Tools does support required variables.)

One minor downside of Publish Profile files is that they get created in the root of your SSDT project (I have griped about this previously at Folders in SQL Server Data Tools) so the convention that I have been using is to create a folder called Publish in each SSDT project and move all Publish Profile files into there.

Hopefully this has given you a small taster of what Publish Profile files are all about. I’ll probably share some msbuild scripts that we’re using to deploy these things in a later blog post.

@Jamiet

P.S. As an aside, I have requested that the SSIS/SSAS/SSRS teams adopt the Publish Profile file approach in future iterations of their products. If you think that that is something you would like to see happen then click through, vote and leave a comment.

The ethical question

Rob Farley — Tue, 08 May 2012 19:56:33 GMT

Some people question the ethics of writing about my company on my blog. But I don’t have a problem with it. Is it ethical to ignore the concerns of others and to just do what I want?

Anyway – it’s relevant for this month’s T-SQL Tuesday, which Chris Shaw is hosting on the topic of ethics, so I’m going to write a few things about integrity and ethics.

When I hire people to work at LobsterPot Solutions, the main thing I look for is a high level of integrity. I can handle a certain level of technical unfamiliarity – that’s something that can be learned. But I can’t teach integrity in the same way. I can foster it, and I can ensure that it’s an established ‘value’ in the company. It’s the number one thing.

I know a sysadmin company (it wasn’t LobsterPot) that recently lost a bunch of client data – they asked us to help them recover some of the lost data. It was a bad technical mistake they made, but that’s forgivable. They tried really hard to get the data back, and managed to salvage a lot of it, which demonstrated a better level of integrity. When I look at ethics and integrity, it’s not about mistakes, it’s more about the response to mistakes. Their integrity was brought into question over putting the client into a situation where they could’ve lost data – but this wasn’t a question of integrity, it was an honest mistake. The integrity question comes about their response. I like to look at the knee-jerk reaction. If you make a mistake, do you cover it up, or do you try to resolve it? Unfortunately, I think there’s often an element of both, but if the motivation is to protect the other people involved, then I would expect that the integrity level is okay.

Ethics is about good and bad behaviour, and of course, once that definition has been made, choosing good. I’m not going to try to do the definition thing – I think people generally have a feel for what is good and bad (even with data it should be clear – you don’t get to steal client data, etc). The application of ethics comes down to integrity.

At LobsterPot, we try to do the right thing all the time. This doesn’t mean that we don’t make mistakes – but our first reaction is always to do the right thing by the other parties involved, even if it costs us money.

It has to. We don’t get a choice on this. And if I don’t think that integrity is your driving influence for every decision you make, then I’m sorry, but I’m just not going to hire you.

BlobShare: Corporate Filesharing with Security and Control

BuckWoody — Tue, 08 May 2012 12:49:21 GMT

We’ve all done it. We have a file we need to transfer to someone - but it’s big. Really big. “Big”, in this case, is defined as “bigger than my corporate e-mail will let me send.” So of course we scout around, and find a free file share location. We plop it in, and send a link to the other person. Problem solved!

No, problem created. You see, many of these file-sharing sites have some…interesting…language in their terms. Sure, for a picture of aunt Sally riding that llama on the vacation, who cares if someone else steals it and puts it on an advertisement for a shipping company from Elbonia? But most of these offerings were not designed for a corporation with private or secure information - sometimes your private or secure information.

So what’s a company to do? You have to let people send files - they are just going to work around IT if you don’t - but you need a way to keep it secure, and maintain a chain of custody in some cases. There is a solution.

There’s a free Codeplex project called “BlobShare” that we have for you here. You pull it down, follow the instructions, and deploy it to Windows and SQL Azure. From there, you have a clean, safe, secure, controlled interface to let your folks share files.

There’s a full post on the inner-workings and the design of this code - and it’s just code. You can change it, use your own logo, restrict it further, loosen it up, whatever you like. That’s the point of Platform as a Service - control and ease.

http://blogs.msdn.com/b/vbertocci/archive/2011/10/31/blobshare-sample-acs-protected-file-sharing.aspx

SQLIO analysis on Excel charts

Marco Russo (SQLBI) — Tue, 08 May 2012 10:20:00 GMT

I tend to use twitter when I have to notify an interesting link or tool. But sometimes a blog post (even if short) is better, because allows me to easily find it on search engines (Bing/Google/whatever) months or years later. I know PInterest is growing these days, but the blog indexed on search engines is still a reliable resource to me.

Davide Mauri tweeted this Pual Randal’s blog that mention the SQLIO Result Parser tool from David Klee. In short: you run SQLIO, save result in a CSV file, upload these results on a web page and obtain an Excel file as a result, including charts. Simple and useful. I’ll use it!

Did You Know? Turning Off Locking

Kalen Delaney — Mon, 07 May 2012 23:35:00 GMT

Back when the lock manager was rewritten for SQL Server 7, and row level locking was added to the product, the procedure sp_indexoption gave us the ability to turn OFF either row or page locks or both, for one particular index, or for a table and all the indexes on it. I must admit, I rarely found this option to be the solution to any sticky problems, although while doing some quick research prior to writing this post, I did find that Microsoft recommended turning off locking options on a couple...(read more)

PHP Setup for IIS and SQL Server

Davide Mauri — Mon, 07 May 2012 15:20:50 GMT

Making PHP running on IIS and configuring it to be able to connect and query a SQL Server database is quite easy. If just need to get pointed to the correct direction, since information are spread all across the web but sometimes is not easy to understand to which version they apply.

So, since our primary goal is to have Wordpress 3.3.2 (the latest version as of 07 May 2012) and PHPBB 3.0.12 (the latest version as of 07 May 2012) up and running, we’ll download the latest PHP 5.3 version (PHP 5.4 has been reported to have some problems with the current version of Wordpress so we won’t go for the latest one).

So the first step is to download PHP 5.3.12 version from PHP.NET web site:

http://windows.php.net/download/

As you may notice there are tow flavor of the PHP distribution: Thread Safe and Non Thread Safe (NTS).

I’ve googled/binged a bit to understand what’s the advised flavor and as you can read here

http://learn.iis.net/page.aspx/246/using-fastcgi-to-host-php-applications-on-iis/

the best practice is to use the NTS flavor.

I downloaded the Zip package, and unzipped the files in a newly created PHP directory under C: drive (C:\PHP)

The above link also pointed me to a resource page very helpful for everyone trying to install PHP and configure on IIS:

http://learn.iis.net/page.aspx/24/running-php-applications-on-iis/

The following link give also additional information

http://php.net/manual/en/install.windows.iis7.php

I also found a very nice IIS Add-on that allows you to manage PHP directly from IIS:

http://phpmanager.codeplex.com/

One installed the PHP Manager, you can check that everything is working simply checking that PHPINFO() works:

To be able to use SQL Server from PHP you need the PHP SQL Server Drivers provided by Microsoft:

http://blogs.iis.net/sqlphp/archive/2012/03/22/microsoft-drivers-3-0-1-for-php-for-sql-server-with-php-5-4-support-released.aspx

http://sqlsrvphp.codeplex.com/

Once the file has ben downloaded I extracted it to a C:\PHPSQLSRVfolder.

Now you must acknowledge PHP that they exists and should be used. You can do it directly modifying your <php install folder>\php.ini file, adding the reference to the extension,

begin sure to have copied the correct .dll file into the <php install folder>\ext folder.

if you have installed the PHP Manager, you can also enable/disable extension from here (.dll file must be manually copied into the /ext directory anyway)

As you may have noticed, after having unzipped the PHP SQL Server Drivers you have a bunch of files to choose from. Which is the one right for you? For the configuration we’ve chosen we have to go for the “standard” (which means not the “pdo” driver), non-thread safe PHP 5.3 driver. As you can guess the file we need is

php_sqlsrv_53_nts.dll

After having restarted your website, you can check in the phpinfo() page if the extension was loaded correcty:

I strongly suggest to take a look at the help file that comes with the PHP SQL Server Drivers, so that you can start to get used to access SQL Server from PHP. In particular use the code shown in the

“How to: Connect Using SQL Server Authentication”

help article in order to create a .php page to test that your environment can correctly connect to SQL Server.

Of course be sure to have at least the SQL Server Native Client installed on the web server, otherwise you won’t be able to connect to SQL Server from PHP.

http://www.microsoft.com/en-us/download/details.aspx?id=29065

That’s it! You now have a PHP environment on you IIS 7 / 7.5 capable of using SQL Server as RDBMS.

Wordpress and PHPBB on SQL Server

Davide Mauri — Mon, 07 May 2012 14:12:18 GMT

In the last months, in the spare time, I started to study PHP in order to use it on a Windows + SQL Server box. Why you would do such thing you may be wondering. The point is that Wordpress is actually, IMHO, the state of the art of a free CMS that must be used as the backend for a community site: it’s feature-rich, it has a *lot* of plugins and themes, it can be used to host blogs and to empower a “thematic” website. In my case I’d like to refresh the engine used to publish the Italian SQL Server User Group website.

I’ve looked for a lot of alternatives in the .NET World, and I evaluated in the last year

Umbraco
DotNetNuke
Community Server
Orchard
ScrewturnWiki

For one reason or another, none of the mentioned platforms, which are great platforms BTW, was the right for us. We needed something

Capable of managing a community portal with news, articles, events, calendars and so on
Capable of managing the blogs of members, allowing the generation of new blog sub site on the fly
Fully customizable with a minimum effort for the end user
Enabled to use HTML5 and CSS3
Stable and Mature, with a good documentation and/or forum support
Easy to be extended/modified adapted to our needs
Compatible with MSN Live Writer
Compatible with SQL Server
Capable of hosting forums or capable of be integrated with a 3rd party forum platform

And at the end the platform that suits all our need is…Wordpress!

Of course this decision bring some challenges in the game:

I need to be sure that Wordpress can work *well* with SQL Server
I need to integrate Wordpress with a forum software.

Luckily Microsoft has written a cool abstraction layer for Wordpress, that make it compatible with SQL Server. And, even more luckily, there is a mainstream forum solution, PHPBB, natively compatible with SQL Server.

Of course not everything is as smooth as one would like it to be, so there are some “attention point” that one need to take into account when going in this way. And since there isn’t a lot of documentation available on running Wordpress together with PHPBB on SQL Server, I though that writing some post can be helpful to the community. After all Wordpress and PHPBB are two *great* solution and having them available on SQL Server is something desirable in my opinion.

So, in the next months, I’ll write a series of four (maybe five) posts to describe how to have a Wordpress + PHPBB on IIS + SQL Server solution up and running.

Here’s the agenda of the next posts:

PHP Setup for IIS and SQL Server
Wordpress Installation
PHPBB Installation
Wordpress & PHPBB Login Integration

I’ll hope you’ll enjoy the topics!

Presenting at Seacoast SQL Server Users Group in New Hampshire 17 May 2012!

andyleonard — Mon, 07 May 2012 11:00:00 GMT

I am honored to present Using BIML as an SSIS Design Patterns Engine to the Seacoast SQL Server Users Group 17 May 2012! This presentation is based on one of my favorite chapters in the upcoming book: SSIS Design Patterns scheduled for release this summer. If you find yourself in the Portsmouth New Hampshire area on the evening of 17 May, please drop by and introduce yourself. I am the fat guy with a fu. :{>...(read more)

Registrations Open for SSAS Maestro in July 2012 #ssasmaestro #sass

Marco Russo (SQLBI) — Mon, 07 May 2012 08:24:00 GMT

Registrations are now open for the SSAS Maestro course in July 2012, which will take place in Milan (Italy).

I introduced this course in a previous blog post and I’m not going to repeat all the details here. I just want to highlight a few updates, the biggest one is related to the cost of the course. We received several feedback and cut some of the costs for reducing the fee. One of the consequences is that we will not include Tabular as part of the SSAS Maestro course, which will be dedicated only to Multidimensional. We’ll evaluate how to include Tabular in the future. As a result, the price is now 4500€ (reduced to 3500€ for students of a previous SSAS Maestro edition). This is not going to lower the overall value of the course and the price includes the complete evaluation process after the training.

Another very good news is that Thomas Kejser (from the SQLCAT team) will join me and Chris Webb teaching the class. This is another important contribution that adds value to the course: Thomas has been involved in development and deployment of TBs-sized cubes and discussions about scalability of SSAS cubes are an important part of the SSAS Maestro training.

Please read my previous post to get other details about the SSAS Maestro certification process. If you want to get more information and/or ask if you are a good candidate for SSAS Maestro certification, write to ssasmaestro@sqlbi.com sending your CV/resume and a short description of your level of SSAS knowledge and experience. The seats are limited and it is important that students have the right prerequisites in terms of SSAS experience. When you are ready, you can register here.

Principles of Modeling: Avoid Redundancy

Hugo Kornelis — Sat, 05 May 2012 19:31:12 GMT

In 1994, I learned a method for data modeling that is based on three principles. I immediately knew that these principles should embraced by anyone who does any data modeling or process modeling. Or almost any other job, for that matter. I have described these principles in three previous blog posts: the Jargon Principle , the Concreteness Principle , and the Reproducibility Principle . But I have later found that there are more principles and guidelines that are important to keep in mind when modeling....(read more)

Decoding STATS_STREAM

jchang — Sat, 05 May 2012 10:16:00 GMT

Data distribution statistics is one of the foundations of the cost-based query optimizer in all modern database engines including SQL Server. From SQL Server 2005 on, most of the information displayed by DBCC SHOW_STATISTICS is kept in a binary field accessible with the STATS_STREAM clause. Back in SQL Server 2000, it was possible to modify system tables directly, including the sysindexes stat_blob field. At the time, I described a decode of the stat_blob field with the purpose of influence the execution...(read more)

Execute T-SQL Across Listed Servers/Databases

merrillaldrich — Fri, 04 May 2012 22:50:00 GMT

Here’s a handy trick – if you have a SQL script, stored as a text file, and need to run it against an arbitrary list of different databases on different SQL Server instances, try a little PowerShell. There are a lot of ways to accomplish this task, but personally I like this one for its flexibility. First, make sure the SQL script does NOT include the common USE <database>. Generally that statement is your friend, but not in cases where a single script should work against multiple databases...(read more)

The Curious Case of the Optimizer that doesn’t

Hugo Kornelis — Thu, 03 May 2012 22:13:00 GMT

The optimizer is the part of SQL Server that takes your query and reorders and rearranges your query to find the optimal execution plan. In theory. In practice, that doesn’t always work out well. Often, the optimizer manages to come up with brilliant ways to execute a complex query very efficiently – but sometimes, it misses an option that appears to be so simple that you can only stare in utter amazement at the execution plan before going to the Connect site. Here is an example I recently ran into....(read more)

New version of SSDT Power Tools are available – but with a caveat

jamiet — Thu, 03 May 2012 20:12:07 GMT

A little over a month ago the first version of the SSDT Power Tools were made available bringing with them some much needed functionality – a replacement for Schema View called SQL Server Object Explorer.

Along with the first release of the power tools came a promise that they would be updated on a regular cadence and, true to their word, an updated version (v1.1) is already available.

If you have got v1.0 installed then you should automatically be notified about the update. However, an attempt to apply the update results in this little nasty:

Here’s the full text of that error in case anyone happens to Bingle* for it:

Digital signature mismatch

The signature on the update version of 'SSDT Power Tools' does not match the signature on the installed version. Therefore, extension manager cannot install the update.
If you trust the update, try the installation again after you install the installed version.

Now, according to this KB article this isn’t an issue with the extension, it is actually an issue with the Extension Manager in Visual Studio 2010 Service Pack 1 hence the recommended course of action is to uninstall and then reinstall from the gallery

I did this and it worked just fine.

Of course, the only missing information is what is new in this update and a cursory search hasn’t uncovered anything. I suggest keeping an eye on the SSDT blog to see if any such news transpires.

@Jamiet

*Bingle – to search using Google or Bing. I first saw “Bingle” in an email a few weeks ago from Glenn Berry (blog |twitter) and it rather amused me so I’m adopting it for the foreseeable future

Build Your Own Microsoft Operations Manager Pack

KKline — Thu, 03 May 2012 15:25:00 GMT

Want to develop your own Operations Manager Management Pack? Here's how....(read more)

Parallel Execution Plans Suck

Paul White — Wed, 02 May 2012 17:57:00 GMT

Summary: A deep dive into SQL Server parallelism, and a potential performance problem with parallel plans that use TOP.

There was an interesting question asked by Mark Storey-Smith on dba.stackexchange.com back in October 2011. He was looking at the execution plan for a query that counts a million rows from a virtual auxiliary table of numbers. The query below is a slightly-modified version of the one in the original post:

     WITH

        N1 (n) AS

        (

            -- 10 rows

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1

        ),

        N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R), -- 100 rows

        N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R), -- 10,000 rows

        N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R), -- 100,000,000 rows

        N5 AS (SELECT L.n FROM N4 AS L CROSS JOIN N1 AS R), -- 1,000,000,000 rows

        N6 AS 

        (

            SELECT TOP (1000000) n

            FROM N5

        )

    SELECT 

        COUNT_BIG(*)

    FROM N6

    OPTION (RECOMPILE, MAXDOP 1);

This particular virtual numbers table is capable of producing up to a (short-scale) billion rows, via a number of cross joins, but the final common table expression N6 limits it to one million with the TOP clause. The query plan is just a sequence of cross joins of the ten in-memory rows defined by the first common table expression, N1 (click to enlarge):

As usual, the Constant Scan operator is used to generate rows in memory without accessing a physical table, but this one has an interesting property: the ‘virtual table’ contains ten rows, but no columns. The query itself only counts rows, it does nothing at all with any column in those rows, and the optimizer contains logic to only project columns that are needed later in the query plan. If you look at the query plan in SSMS or SQL Sentry Plan Explorer, you will see that the Constant Scans have a blank output columns list; they project no columns at all.

Execution Plans Suck

The plan above does illustrate an important concept in plan-reading: execution plans start executing at the left-most node. People are often told to read execution plans from the top-right, and that is fine if you just want to follow the flow of data – so long as you bear in mind that the flow of program control starts at the root (far left).

Rob Farley (blog | twitter) sums this up by saying “execution plans suck”; a reference to the fact that rows are pulled up the tree by parent operators requesting rows, one at a time, from the its child operator(s). Query execution follows a demand-based pipeline model (except in batch mode in parallel 2012 column-store plans, but that is a tangent we will not be pursuing today). I’m not going to labour the point, but if you are interested to understand this better, take a look at my previous post on the topic, or Brad Schulz’s entertaining overview.

Let’s look at the execution plan (with runtime statistics) changing the TOP specification from one million to one hundred to make it easier to see what’s going on:

I’m just showing part of the plan for clarity. There are another four Constant Scans off to the right that all produce one row. If query plans really did start executing at the top right, we would expect one row from the right-most Constant Scan, ten rows from its parent, and one hundred at the next operator up the tree (working right to left). As it is, the expected pattern (one, then ten, then one hundred) appears closest to the Top operator. This only makes sense if a row at a time is sucked up the plan from the root. The pipelined (row-by-row) model also explains why execution stops after 100 rows; the Top operator in the plan simply stops requesting a new row from its immediate child at that point.

The Question

Back to the main thrust of today’s post. The question arose when Mark ran his query with parallelism enabled:

    WITH

        N1 (n) AS

        (

            -- 10 rows

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1

        ),

        N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R), -- 100 rows

        N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R), -- 10,000 rows

        N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R), -- 100,000,000 rows

        N5 AS (SELECT L.n FROM N4 AS L CROSS JOIN N1 AS R), -- 1,000,000,000 rows

        N6 AS 

        (

            SELECT TOP (1000000) n

            FROM N5

        )

    SELECT 

        COUNT_BIG(*)

    FROM N6

    OPTION (RECOMPILE, MAXDOP 2);

That produces an actual execution plan like this (click to enlarge):

The question relates to the actual number of rows shown entering and leaving the Gather Streams exchange operator:

As expected, one million rows leave the exchange operator, but the plan shows 1,004,588 rows entering it. So the question is, are the row counts wrong, or if not, where did the rows go?

The Answer

As you may know, the answer lies in the fact that exchanges contain buffers. For efficiency reasons, rows are not passed across threads one by one as in the normal model discussed above. Instead, producer threads (on the right side of the exchange operator) pack rows one at a time into packets, and push completed packets across the exchange. Consumer thread(s) on the left side of the exchange retrieve rows one at a time from the current packet on demand, re-establishing the demand-based pipeline model. The internal class name for the packets, by the way, is Class eXchange PACKET – which gives us the familiar CXPACKET moniker.

Exploring the detail at little more, we can see from the execution plan that the two independent producer threads to the right of the exchange pack a total of 499,225 + 505,363 = 1,004,588 rows into packets, and the single consumer thread (thread zero) retrieves just the one million rows needed by the Top operator:

So, the actual row counts shown on the execution plan are correct, and the ‘missing rows’ are accounted for by rows that were added to packets but not ultimately needed by the consumer. After the one millionth row is passed by the Top to the Stream Aggregate (and remember all these things happen one row at a time), the next time the Top gets control, it starts the process of shutting down the pipeline below it, rather than asking the Gather Streams exchange for another row. Glossing over the finer details a little, instead of the Top calling a GetRow() method on the Gather Streams iterator, it calls a Close() method instead.

SQL Server 2008+

On SQL Server 2005 that is the end of the story. On SQL Server 2008 and later (including 2012), there is more. Let’s run the query one more time, but this time with a degree of parallelism of three, rather than two. Sometimes, we will get a plan that contains this sort of row count arrangement at the Gather Streams:

Much the same as before, a few thousand extra rows are processed by the producer threads than are ultimately needed by the consumer. That’s fine, of course, the threads in a parallel plan execute independently, so there are bound to be small timing differences that lead to this sort of effect. Every so often, however, executing this query on SQL Server 2008 or above will produce a result like this:

Whoa. Nearly 335 million rows entering the exchange – and the query now runs for 50 seconds or so, having run consistently for around 200ms previously. Looking at the per-thread actual row counts is revealing:

If everything were perfectly balanced, we might expect each of the three producer threads to process around 333,000 rows. Indeed, thread 1 and thread 2 are in this ballpark area, but thread 3 ground through 334 million rows on its own! I should mention that there is nothing special about thread 3 (you are as likely to find the huge row count on thread 1 or thread 2, the labels are arbitrary). Indeed, the problem can occur on any or all threads, as a second example run at DOP 3 shows below:

This time two threads went rogue, resulting in over 667 million rows being processed in a total execution time of 67 seconds.

Parallelism Problems?

There are other parallelism (exchange) operators in the plan, though we have concentrated only on the final Gather Streams operator so far. By the way, Gather Streams is also known as a Start Parallelism operator – a name that might surprise you since it seems to mark the end of parallelism in the plan, rather than the start. Remember that execution plans suck. The ‘final’ Gather Streams is in fact the first operator to start executing, and it is responsible for starting the parallel workers and attaching them to the threads that were reserved for this plan at the very start of execution. Anyway, I digress. Let’s look at the next exchange in the plan – reading left to right of course. This is a Repartition Streams exchange operating in Round Robin distribution mode:

This shows that 1,000 rows arriving on the producer side of the exchange, and a total of 668 on the consumer side. Not shown in the diagram above, the producer side has 340 rows on thread 1, and 330 rows on each of thread 2 and thread 3. Note that these are not the same threads as the ones we saw numbered the same way before. The numbering scheme is restarted for each independent parallel zone in the plan (and zones are bounded by a parallelism operator of one sort or another). Anyway, the point is that the rows are pretty evenly distributed on the producer side of the exchange.

On the consumer side (row counts illustrated above), things are rather different. Thread 1 (in this zone) processes 334 rows, thread 2 gets 333, and thread 3 gets only one. Now these are the same threads as shown in the 667 million row diagram. I mentioned that parallel zones are bordered by parallelism operators; the current zone is bordered by the Repartition Streams on its right side, and by the Gather Streams on its left. The same three threads are consumers at the Repartition Streams, and producers at the Gather Streams operator:

There is a clear relationship between the thread row counts at the consumer side of the Repartition Streams (334, 333, and 1 row) and the row counts at the producer side of the Gather Streams (334 million, 333 million, 338,614 rows). The two problematic threads have multiplied their row counts by a factor of one million – precisely the effect of the six cross joins in this parallelism zone. The Constant Scan virtual tables contain ten rows each, and multiplying by ten, six times over, gives a factor of one million.

Rogue Row Goals

Thread 3 in the example above ends up processing 338,614 rows. This number has no special significance, except it shows that this thread did not run this portion of the plan to completion. If it had, the single row it started with would have ended up as one million rows by the time it had been cross-joined six times with the ten-row Constant Scan table.

This is the row goal in action (if you need a details on exactly what a row goal is, please see my previous post). Essentially, though, things like a TOP operator or a FAST n hint set a row goal. Plans normally run to completion, but row goals modify this, producing a plan that tries to optimize for a certain number of rows rather than the full potential set. The TOP operator is even more special. As discussed briefly before, TOP can bring execution to an early end – instead of continuing to ask for rows from its child iterator, it calls the Close() method instead. This call filters down the tree, and execution comes to an early end. (Strictly, row goals affect optimizer choices and plan costing rather than being associated with the early end of execution, but I like the phrase ‘rogue row goal’).

So, thread 3 did not run to completion – it responded to the early Close() call and only processed 338 thousand of the one million rows it could have produced if left alone. Threads 1 and 2 never received the Close() call, or chose to ignore it. These two rogues went on to process their full potential row set – 667 million rows – despite the fact that the TOP had seen the million rows it needed and was waiting for operations elsewhere in the parallel plan to stop. We can see this by looking at the sys.dm_os_waiting_tasks DMV while the long-running query is executing:

The output is split across two lines for readability, and shows execution context zero blocked on a CXPACKET wait by both execution context 1 and execution context 3. Execution context zero is always thread zero – the so-called coordinator thread that runs the serial part of the execution plan to the left of the left-most Gather Streams operator. Put another way, context zero always runs the part of the plan before (reading left to right) the Start Parallelism operator (and therefore always runs a serial plan).

OK, so CXPACKET means the thread is involved in a parallelism-related wait. The extra detail in the resource_description column tells us that the wait is occurring at the node id 2. Checking the execution plan, we see that node 2 is indeed the Gather Streams exchange:

The wait type of e_waitPortClose means the consumer is waiting for the port to close. If you are wondering what a port is in this context, I will just say the parallelism architecture is more complex than just CXPACKET – the wiring includes a CXPort class, a CXPipe class, a CXTransLocal (local transport) class and a CXTransRemote class (for the Parallel Data Warehouse edition). There is also a linked map structure that shows how the various pipes, ports, and transports connect together. Closing the port is one step in shutting down part of a parallel plan which is running on the other side of an exchange (via a transport, pipe, and port). The stack trace below shows thread zero waiting for the port to close:

The important things are that (a) this problem does not occur in SQL Server 2005; and (b) a number of changes were made to the internal parallelism implementation in SQL Server 2008. These changes seem to have introduced a bug, where the consumer can wait for the port to close, but one or more producers either ignore the request, or fail to check for it, and go on to process the whole potential result set instead of stopping early.

Not Just Nested Loops

Fellow SQLblogger Joe Chang (blog) suggested in the comments that this problem may only occur with parallel nested loops joins. The script below reproduces the problem with parallel hash joins:

    WITH

        N1 (n) AS

        (

            -- 10 rows

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1

        ),

        N2 AS (SELECT L.n FROM N1 AS L JOIN N1 AS R ON L.n % 1 = R.n % 1), -- 100 rows

        N3 AS (SELECT L.n FROM N2 AS L JOIN N2 AS R ON L.n % 1 = R.n % 1), -- 10,000 rows

        N4 AS (SELECT L.n FROM N3 AS L JOIN N3 AS R ON L.n % 1 = R.n % 1), -- 100,000,000 rows

        N5 AS (SELECT L.n FROM N4 AS L JOIN N1 AS R ON L.n % 1 = R.n % 1), -- 1,000,000,000 rows

        N6 AS 

        (

            SELECT TOP (1000000) n

            FROM N5

        )

    SELECT 

        COUNT_BIG(*)

    FROM N6

    OPTION (RECOMPILE, MAXDOP 4, QUERYTRACEON 8649);

The full execution plan is rather large, but the key part is shown below (click to enlarge):

Yes, that is 700 million rows entering the Gather Streams exchange (click to enlarge):

The bug does not occur in every query plan with Top and parallelism, but the choice of nested loops join is not the cause.

Final Thoughts

The potential for poor performance and excessive processor usage here is obvious; and the chance of hitting the race condition gets worse at higher DOP. With eight threads per parallel zone (DOP 8), I hit this issue almost every time on SQL Server 2008, 2008 R2, and 2012. Because this behaviour does not occur on SQL Server 2005, but does on 2008 and later, I have filed this as a bug on Connect:

https://connect.microsoft.com/SQLServer/feedback/details/740234/poor-performance-with-parallelism-and-top

SQLblog.com - The SQL Server blog spot on the web

MicroTraining: Executing SSIS 2012 Packages 22 May 10:00 AM EDT (Free!)

Book Review (Book 11) - Applied Architecture Patterns on the Microsoft Platform

Messages do not always appear in [catalog].[event_messages] in the order that they occur [SSIS]

How SQL Saturday could be better

SQL Server 2008 R2 Service Pack 2 CTP is available

SQL Rally Presentations

SQLCMD Mode: give it one more chance

DATEFROMPARTS

Speaking at SQLRelay. Will you be there?

Publish Profile Files in SQL Server Data Tools (SSDT)

The ethical question

BlobShare: Corporate Filesharing with Security and Control

SQLIO analysis on Excel charts

Did You Know? Turning Off Locking

PHP Setup for IIS and SQL Server

Wordpress and PHPBB on SQL Server

Presenting at Seacoast SQL Server Users Group in New Hampshire 17 May 2012!

Registrations Open for SSAS Maestro in July 2012 #ssasmaestro #sass

Principles of Modeling: Avoid Redundancy

Decoding STATS_STREAM

Execute T-SQL Across Listed Servers/Databases

The Curious Case of the Optimizer that doesn’t

New version of SSDT Power Tools are available – but with a caveat

Build Your Own Microsoft Operations Manager Pack

Parallel Execution Plans Suck

Execution Plans Suck

The Question

The Answer

SQL Server 2008+

Parallelism Problems?

Rogue Row Goals

Not Just Nested Loops

Final Thoughts

Further Reading: