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

Getting off to a good start

John Paul Cook — Fri, 01 Jan 2010 22:20:45 GMT

There are several things I do to get each New Year’s Day off to a good start. All of my machines have been to Windows Update today and are updated and defragmented. My DBA toolkit on a stick is completely new for 2010. When I went to http://portableapps.com and downloaded the latest full suite , it was a pleasant surprise to find out that the portable version of Open Office finally works on 64-bit systems. These are my recommendations for portable applications you might want to add: 7-Zip portable...(read more)

2010 Themeword and Goals

andyleonard — Fri, 01 Jan 2010 21:00:00 GMT

Introduction Tim Ford ( Blog / Twitter ) tagged me in a post about 2010 Resolutions and Themeword . Resolutions I've not had good luck with resolutions in the past and I think I figured out why: My life doesn't revolve around a calendar year. This sounds like a cheesy excuse (it is an excuse and it may very well be cheesy), but I tend to get things done in 17-week, 15-month, or 4.5 year increments. This probably corresponds with the calendar of my home planet (wherever that is) but I digress... That...(read more)

Quote of the Day: On writing the new year

BuckWoody — Fri, 01 Jan 2010 14:47:00 GMT

"We will open the book. Its pages are blank. We are going to put words on them ourselves. The book is called Opportunity and its first chapter is New Year's Day." Edith Lovejoy Pierce

Closing out 2009 – Goals in Review

Jonathan Kehayias — Fri, 01 Jan 2010 04:53:01 GMT

This year I set quite a few goals for myself both personally and professionally, and I blogged about them on my blog post Goals for 2009. Looking back at them, I did really good accomplishing my primary goals for the year, dedicating a block of time to my family each day and graduating from the US Army Drill Sergeant Academy to further my military career. I accomplished both of these goals, and my relationship with my family is stronger than I ever thought was possible, something that...(read more)

Year in Review: My Series on Building Developer Communities

andyleonard — Fri, 01 Jan 2010 00:00:00 GMT

The series of posts that generated the most email for me was the series entitled On Developer Communities. I've included links to the posts, in order, here: First , you need a team builder . You can run a company like a user group, but the inverse is not always true. Quality always works. People are not resources or assets . Don't go away. Have a (Sponsorship) Plan . The Sponsorship Plan May everyone have a Happy New Year! :{> Andy Share this post: email it! | bookmark it! | digg it! | reddit!...(read more)

[OT] Auto-Responders: Making E-Mail Suck, One Reply at a Time

Adam Machanic — Thu, 31 Dec 2009 20:12:00 GMT

Do either of these look familiar: "Suzanne is out of the office and will return December 12." "Hi! I'm on vacation, and may not have access to e-mail. If you need immediate assistance, please contact ..." Or how about this one (seen in December): "I'm on vacation from July 3-7 ..." Or the worst one I've seen recently, just three letters long: "OOF" Auto-responders are a nuisance, an annoyance, and destroy the asynchronous nature of e-mail that makes it such a useful communication mechanism. Here's...(read more)

[OT] Happy New Year

AllenMWhite — Thu, 31 Dec 2009 19:43:00 GMT

Well, it's been an interesting year. I got to teach four different brand new SQL Server courses, and teach classes in four different countries on two continents. Two books were published with chapters I wrote. Microsoft published a whitepaper I wrote. And finally I've started a new business and it's time consuming but so far successful. I'd like to thank everyone who helped make this all possible, specifically Kieth Nicholson, Paul Nielsen, Kalen Delaney, Adam Machanic, Dan Jones, Don Vilen, Tony...(read more)

Got a slow holiday seaons? Why not spend it learning?

KKline — Thu, 31 Dec 2009 18:40:00 GMT

Traditionally, the time between Christmas and New Years is a bit slow. Many people take time off from work and, for those of us still working, it's a good time to catch up on all of those low priority projects that have been on the back burner for the last several months. If you find yourself with a few extra hours, why not engage in some first-hand learning with the new features of SQL Server 2008 R2? Here are two great resources to get you started. First, take a look at betaforsqlserver.com , a...(read more)

The Beauty/Danger of Checklists

BuckWoody — Thu, 31 Dec 2009 14:10:00 GMT

I love checklists. They give you a set of steps to take and when to take them for your systems, and help you standardize processes and procedures. They help you stay consistent, and let you know when you should do something, even when you might not be aware that a particular step is required. They are a way of transmitting knowledge from those who understand to those who don’t.

Interestingly, we don’t have a lot of them in Books Online. In fact, I don’t think we have any. The reason is pretty simple – most tasks can’t safely be reduced to a checklist. You see, if you make the checklist have all of the “but if you have X, then you have to do Y” would make them almost unusable. And since Microsoft is always a big target, it’s safer not to put them in than to be blamed for missing a step or having something that isn’t suited for every circumstance.

I think if they are used properly, checklists are awesome. I also think that the benefits outweigh the trouble they can cause. In the next few weeks, I’ll be working with some friends to develop a few, with, of course, the caveat that you read and understand them – not just implement them blindly.

Connect digest : 2009-12-31

AaronBertrand — Thu, 31 Dec 2009 13:43:00 GMT

Like Hugo's immensely popular item pushing for SQL Server 2005 Service Pack 3 , Steve Jones has created suggestions prompting new service packs for both 2005 and 2008: #522122 : Service Pack 4 for SQL Server 2005 #522123 : SQL Server 2008 Service Pack 2 In a predictability area, I suggested that online index rebuilds should populate percent_complete in sys.dm_exec_requests (just like index reorganization does today): #520072 : Online index operations should populate percent_complete in sys.dm_exec_requests...(read more)

Dimension cubes

jamiet — Wed, 30 Dec 2009 23:14:08 GMT

This is a quickfire blog post about a feature in Analysis Services that I have learnt about recently – Dimension Cubes. As of Analysis Services 2005 every single dimension in an Analysis Server database implicitly has a dimension cube created for it. Dimension cubes enable you to get around the limitation that every MDX query must have a FROM clause that references a cube and thus enable you to query over a single dimension without worrying what cube it is in. The name of a dimension cube is the same as that of the dimension itself but with a dollar sign pre-pended to it.

Did that make any sense? Perhaps rather than try and explain what a dimension cube is I could probably just show you instead, I’ll use the [Adventure Works DW Standard Edition] sample database in SQL Server Analysis Services 2008 as an example. The following query returns all members of the [Product] level in the [Product Categories] hierarchy in the [Product] dimension:

select [Product].[Product Categories].[Product].members on columns
from [$Product]

And here’s the result:

Notice how we are not querying over a regular cube but we are still able to interrogate the members of a dimension; I find this to be a really useful feature, especially when building reports. Still, I would rather the members were arranged in a list vertically rather than horizontally but that doesn’t seem possible because a dimension cube by definition only has one dimension and the first axis referenced in an MDX query MUST be ‘columns’. Well, whoever came up with the notion of dimension cubes obviously thought of that because every dimension cube also contains a single measure that has the same name as the dimension itself. Hence we can write this:

select   [Product].[Product Categories].[Product].members on rows
,        [Measures].[Product] on columns
from    [$Product]

and see our dimension members in a much more readable format:

It transpires that we can also use an empty set on the ‘columns’ axis in order to achieve the same thing:

select   [Product].[Product Categories].[Product].members on rows
,        {} on columns
from    [$Product]

I’m sure many people reading this already knew about dimension cubes but until recently I did not and hence I assume others who might find them useful might not know about them either – hence this blog post. Did I miss anything?

@Jamiet

Improving Your Public Speaking: "Confessions of a Public Speaker"

Adam Machanic — Wed, 30 Dec 2009 17:43:00 GMT

Speaking is one of those activities where there is always something to tweak or improve. Whether you've just finished your first talk or your thousandth, after you're done I guarantee you'll look back and find at least a couple of things that you'll wish went better. Changes you can make for the next time will always be on your mind. As a speaker, part of your job is to realize that no talk is going to be perfect, but with work you can make your talks better and better, with fewer modifications required...(read more)

New website online and sys2 scripts updated

Davide Mauri — Wed, 30 Dec 2009 14:33:08 GMT

Thanks to these Holydays I’ve been able to update my website which now uses the phenomenal Umbraco CMS.

Umbraco is a open-source, free (if you don’t need support or don’t want to re-brand it) Content Management System, completely built with ASP.NET. It simply rocks. After an intense but short learning curve, you can create a website without having to take care of anything else but its content. Just the look & feel and the content. No other problems or things to deal with. All you need is a little knowledge of XML/XSLT and HTML/CSS. What a dream! :-)

In addition, if you’re as good as website design as I am, you can find some valuable help on www.styleshout.com where in just a few hours you can apply the web site design you choose to your Umbraco powered web pages.

And this is what I’ve been able to do:

http://www.davidemauri.it

Now, beside that (I hope these information helps everyone who want or needs to have and self-manage their own web presence), I’ve also had the time to update my “SQL Scripts” page:

http://www.davidemauri.it/resources/sql-scripts.aspx

Here you’ll find all my “system” scripts that helps me to save time and keystrokes while using DMVs. In particular, for now, index-related DMVs.

In these last years I’ve written and gathered a lot of useful scripts like those and now that I have a rockin’ CMS I should be able to keep the online version much more updated so, stay tuned!

Any feedback on scripts is more than welcome! If you find something that can be improved or fixed just let me know.

Ah, yeah, the script should work with SQL 2005 and 2008 (but I've tested it only on 2008 for now).

Measure Statement Performance with PowerShell

BuckWoody — Wed, 30 Dec 2009 14:31:37 GMT

When you monitor for performance, you might start in the database server itself. But a true test really has more to do with the “round trip” of a data request from a client to a server and then the return of data back to the client.

So I wired up this little test to simulate that process, and now I can run that from anywhere. The trick is the measure-Command { } part, which brings back the time display. In my example, I’m testing the backup process, but your command can be anything you like. I’m also using the ExecuteNonQuery call, since I don’t want any data back. You might want to use the ExecuteReader call if you do want data.

If you want to try this (and remember, test system only, read and understand, etc.), you need to change the SERVERNAME\INSTANCENAME to your system and instance names, and change the:

$sqlCommand.CommandText="BACKUP DATABASE TESTDATABASE TO DISK='c:\TEMP\TESTDATABASE.bak' WITH INIT" line to some command you want to run.

I’ve alternated the colors – keep all of one color on one line:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "server=SERVERNAME\INSTANCENAME;integrated security=true;database=master"

$sqlConnection.Open()

$sqlCommand = new-object System.Data.SqlClient.SqlCommand

$sqlCommand.CommandText="BACKUP DATABASE TESTDATABASE TO DISK='c:\TEMP\TESTDATABASE.bak' WITH INIT"

$sqlCommand.Connection=$sqlConnection

Measure-command {$sqlCommand.ExecuteNonQuery()}

$sqlConnection.Close()

Goodwill, Negative and Positive

andyleonard — Wed, 30 Dec 2009 12:00:00 GMT

Introduction A friend and I shared a recent instant-message conversation and she told me one of the two netbooks she ordered had been stolen in transit. They are gifts for her children and one gift is gone, within two weeks of Christmas. Tracking numbers show the netbook was stolen while in possession of the shipping company. Most likely, an inside job. It got me to thinking about the mindset of an individual who would risk their job, reputation, career, and potentially their freedom for the promise...(read more)

Using sa as owner for jobs and databases

TiborKaraszi — Wed, 30 Dec 2009 07:19:00 GMT

This blog is not about avoiding logging in using the sa login. Hopefully we all know about this, and work towards avoidning this practice. Instead I want to talk about using sa, but not to login (authenticate), but as owner for jobs and databases. I want keep these thing de-individualized - so we avoid things like person A leaving the company and we don't dare to remove that login/Windows account. We can of course create some SQL login or Windows login especially for this purpose and use that. But...(read more)

You're a mean one...

Mike C — Wed, 30 Dec 2009 05:42:00 GMT

Vince threw out a question - how did I create my geeky Christmas card at http://sqlblog.com/blogs/michael_coles/archive/2009/12/22/merry-christmas.aspx ? For those who haven't seen it yet -- this is your spoiler alert -- go check it out before you read on. The script in the blog draws a picture of the Grinch with the SQL 2008 Geometry data type. Unfortunately the way I wanted to create it is a much better story than how I ended up making it. Originally I wanted to take a Grinch image, write a little...(read more)

Good Bye BI

mosha — Wed, 30 Dec 2009 00:23:00 GMT

"Every new beginning comes from some other beginning's end" ( Seneca ) I spent big chunk of my professional career working on what we call today BI. From Panorama, coding some of the innovative cutting-edge (back then) algorithms, to Microsoft with mission "Bring BI to the masses", inventing MDX and developing what became the best and most widely used OLAP server in the industry, to working on the huge scale distributed BI systems for Microsoft online division. And then a year ago, I decided to do...(read more)

Diskeeper 2010 Update

Jonathan Kehayias — Tue, 29 Dec 2009 18:32:15 GMT

A few weeks ago I blogged about A tale of CHECKDB failures caused by 3rd party file-system drivers that was related to a bug in the latest version of Diskeeper 2010. I received an email from the company that they have released an update that resolves the problem. http://www.diskeeper.com/blog/post/2009/12/28/New-Diskeeper-2010-update-%28140898%29.aspx The fix was identified and blogged about on the Diskeeper blog within hours of my posting the original blog post: http://www.diskeeper.com/blog/post/2009/12/10/IntelliWrite-and-SQL-DBCC-false-positives-Technical-Bulletin.aspx...(read more)

SQL Server Chargeback Strategies

BuckWoody — Tue, 29 Dec 2009 14:47:37 GMT

It seems the more things change the more they stay the same. One of the things I used to create on the mainframe system when I started years ago was a “charge-back” system. It tracked the time and resources used by the employees so that we could charge their department money for the time they spent on the (very expensive) mainframe.

When LAN systems came out, IT departments were just charged as a general expense. But these days, with shrinking budgets and deeper scrutiny on how resources are used, this idea has come back around. IT and Data Systems managers are keen to show the organization that it isn’t free to operate a database system.

From time to time I get asked if it is possible to do a chargeback system for SQL Server. And it is! In fact, I wrote a couple of articles on how to do that right here:

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=311

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=312

Mixing OLTP and reporting using indexed views

AaronBertrand — Tue, 29 Dec 2009 14:40:00 GMT

I'm not a big fan of denormalizing, nor of repeating redundant redundant information in a database when those facts can already be derived from other information. A classic example of the latter is when I see questions on newsgroups, forums or StackOverflow that ask how they can update a table's rank column to reflect the current rank based on some other criteria in the table. The problem with this is that you have to run the update every time any DML operation touches any row in the table, and if...(read more)

Util procedure to show database size

TiborKaraszi — Tue, 29 Dec 2009 12:17:00 GMT

One of the first things I want to do when I look at a new SQL Server is to get an idea of space usage details for each database, including total space usage. For this I have been using my own sp_db_space_usage for a while now, so I decided to add it to my website ( www.karaszi.com ). Check it out here . Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

Article: Scripting in SSIS - Part 1

andyleonard — Tue, 29 Dec 2009 12:00:00 GMT

My latest article - Scripting in SSIS-Part 1 - is available today at SQLServerCentral.com ! :{> Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

Microsoft BI Conference 2010 joins TechEd 2010 North America

Marco Russo (SQLBI) — Tue, 29 Dec 2009 10:12:07 GMT

Even if the site still is not updated, the Microsoft BI Conference 2010 will be joined with TechEd 2010 in New Orleans, LA. The conference will start on June 7, 2010, with a pre-conference day on June 6 (there are still no updates about BI Conference on the TechEd site too). I think this is a good way to optimize content delivery. Several (technical) sessions can really be shared across TechEd and BI Conference, and there is the opportunity to add sessions less technical and more business-oriented...(read more)

Playing with page compression - for real

AaronBertrand — Tue, 29 Dec 2009 04:46:00 GMT

During maintenance windows and against test databases, I have been applying page compression to some largish tables (~200+ million rows, ~50GB). These tables are heavy write and medium read, but all writes are inserts (no updates). Clustered indexes are on a DATETIME column, and then on INT columns. Since there will be very repetitive data within each page, they seemed like decent candidates for compression. The actual rebuild tends to take about 3.5 hours on average, sometimes a little more, for...(read more)