SQL Server Thread

Quirky update in sql server

2009-06-13T08:52:00.001-07:00

When you use variables in update statement and update the columns with the values of variables which keep on changing for each row , you can simulate many things that are done using Loop or a Cursor declare @temp table ( product_name varchar ( 100 ),...( read more)

Upcoming BI User Group Events

2009-06-01T11:03:00.001-07:00

Two UK SQL Server user group dates to flag up for anyone with an interest in BI:

Integrating Data Mining into your BI Solution, with Allan Mitchell, on June 4th via Live Meeting
http://www.sqlserverfaq.com/events/177/Inte...Full Article.

Last Backup/Restore date of database

2009-05-18T11:10:00.002-07:00

If you want to know the last Backup/Restore date of database, use the following --Last Backup date of database --Method 1 In Management Studio,Expand database,Right Click on the database and click properties Refer Last Batabase Backup which is availalbe...( read more)

Table level Backup/Restore

2009-05-18T11:10:00.001-07:00

It may be very useful if we have feature to take backup or restore a table I have posted my suggestion here Table level Backup/Restore What do you think of it?...( read more)

Import from Text file - Single column to multiple columns

2009-05-18T11:09:00.005-07:00

One of the users asked this question in the SQL forum I have data in the text as follows AccountNo: 00234543 AccountName: Kickser City: Chicago Country:USA AccountNo: 00234543 AccountName: Annis City: Seatle Country:USA AccountNo: 12234456 AccountName...( read more)

Speaking next week in Zurich

2009-05-18T11:09:00.004-07:00

I do quite a lot of work in Switzerland: I lived in Basel for three years and still have a lot of friends and business contacts there as a result. I'll be there next week, in fact, and while I'm there I'll be speaking at the May meeting of the Swiss PASS chapter in Zurich. I'll be doing the same session I did at PASS Europe last week (so no prep time needed, luckily) on 'Designing Effective Aggregations in SSAS 2008'. All the details are here:

How to find size of backup files in a folder?

2009-05-18T11:09:00.003-07:00

Tables with no Primary and Foreign keys

2009-05-18T11:09:00.002-07:00

John Paul Cook has posted in his blog to show the list of tables that have no Referential Integrity. What if you want to know list of tables that have no primary and foreign keys? Here is the query select table_name from INFORMATION_SCHEMA.TABLES where...( read more)

Removing unwanted characters

2009-05-18T11:09:00.001-07:00

Sometimes when you import data from other system , you may want to clean up data by removing unwanted characters You can use Replace function . Suppose you want to remove the character ^ from the string declare @str varchar ( 100 ) set @str = 'test...( read more)

Filling random passwords - Set based approach

2009-03-31T09:00:00.005-07:00

In this post , I showed a way to generate 8 characters random password If you have table with data and add a column for password and want to fill that column with random passwords , you can use this approach. I have seen the usage of a function that generates...(read more)

...Full Article.

SQLBits IV

2009-03-31T09:00:00.003-07:00

So, yet another SQLBits finished, and judging by the feedback we got it was the best yet. Certainly we had our highest ever attendance and the sponsors all seemed very happy (which is important because, after all, they're the ones paying for it); we've also had a number of very kind emails thanking us too. Now for a few months of peace and quiet before we start planning for the next one, which I suppose will be sometime in the autumn. Any suggestions on w...Full Article.

SQL Solutions OLAP Heartbeat and OLAP Performance Advisor

2009-03-31T09:00:00.001-07:00

Hmm, you wait for years for commercial tools for monitoring Analysis Services (the only one I'd ever seen before was Companion for Analysis Services from SQLMinds) and then two come out at once. One of these tools I'll be blogging about towards the end of this week, hopefully - I've had a sneak preview and it looks very cool - but today I found out the following from SQL Solutions:

OLAP...Full Article.

Splitting delimited data to columns - Set based approach

2009-03-30T09:54:00.005-07:00

In this post splitting-csv-to-columns I showed how to split delimited data into multiple columns

Simon in his post

Return TOP N rows

2009-03-30T09:54:00.003-07:00

The TOP Clause returns top rows from the table based on the number or percentage value
What if you want to have TOP N rows for each group?
The following explains it

(
The purpose is to return top 3 orders for each customer based on the
most recent orderdate from the table Orders in Northwind database
)

1 Use IN ...Full Article.

Export to EXCEL with column names

2009-03-30T09:54:00.001-07:00

In the post Import/Export to Excel, I showed how to export data to EXCEL
The problem that most users find it is it wont include column names in the file and file should exists already with headings

This procedure would solve that problem

...Full Article.

SQL_VARIANT_PROPERTY function

2009-03-30T09:53:00.014-07:00

If you want to know the datatype or length of a column , you can query on the system table syscolumns or system view INFORMATION_SCHEMA.COLUMNS But you dont need to query on system objects if you use SQL_VARIANT_PROPERTY function Example SELECT TOP 1...(read more)

Time Calculation on Numbers

2009-03-30T09:53:00.013-07:00

In Forums sometimes I read questions on how to find out the difference between two times which are stored as characters Here are some possible answers Subtraction declare @t1 v archar ( 5 ), @t2 varchar ( 5 ), @sum float select @t1 = '12.56' ...(read more)

Row_number() Function with no specific order

2009-03-30T09:53:00.011-07:00

Row_number() function is used to generate row number to rows based on the column which is ordered What if you want to generate row number without ordering any column Here is the method (Consider Suppliers table from northwind database) 1 Assign dummy...(read more)

Full Article.

Script of DDL triggers

2009-03-30T09:53:00.009-07:00

How do I view the script of the DDL triggers? The answer is not sp_helptext 'DDL trigger' Because DDL triggers are defined at database / server level and not at any table level So they are not stored in a system objects like sys.objects and the...(read more)

NULL on joined columns

2009-03-30T09:53:00.007-07:00

You know that NULL values on joined columns are omitted from comparision when tables are joined Consider this example Declare @t1 table ( col1 int , col2 varchar ( 10 )) insert into @t1 select 1 , 'test1' union all select 2 , 'test2' union...(read more)

Happy New Year

2009-03-30T09:53:00.005-07:00

set nocount on select space ( 7 - len ( replicate ( char ( 42 ), no )))+ replicate ( char ( 42 ), no * 2 - 1 ) from ( select top 5 row_number () over ( order by name ) as no from sysobjects ) as t union all select space ( 5 )+ replicate ( char ( 124 ...(read more)

Compatibility Level of a database

2009-03-30T09:53:00.003-07:00

Find Alphanumerals only

2009-03-30T09:53:00.001-07:00

Sometimes when you import data from other system, you may need to clean the data by removing unwanted data. Consider you import data where you want to keep only those which are purely alphanumerals. Here is a way to find out declare @table table ( data...(read more)

SQL_VARIANT datatype and behaviour changes

2009-03-30T09:52:00.003-07:00

When you use sql_variant datatype in sql server 2000 , and add data to a table using union all, it will work though datatype of data differs from each other declare @test table ( variant_column sql_variant ) insert into @test select 'test' union...(read more)

Full Article.

Beware of the Usage of SET ROWCOUNT

2009-03-30T09:52:00.001-07:00

This is used to set the number of rows affected for DML Declare @test table ( number int ) set rowcount 1 insert into @test select 11 union all select 10 set rowcount 0 select number from @test The above inserts only one row to the table variable @test...(read more)

Which columns uniquely identify a row?

2009-03-30T09:51:00.007-07:00

Without seeing the table structure,If you are interested in knowing which columns uniquely identify a row in a table, you can use this system procedure EXEC sp_special_columns table_name...(read more)

Behavior Changes to Database Engine Features in SQL Server 2008

2009-03-30T09:51:00.005-07:00

Updated Point on BOL is available at http://msdn.microsoft.com/en-us/library/ms143359.aspx...(read more)

Full Article.

BI Survey 8

2009-03-30T09:51:00.003-07:00

As I think I've probably said before, I don't get many freebies as a blogger but one that I do get and I really appreciate is my review copy of the BI Survey. It being that time of year, I got the latest edition - the BI Survey 8 - last week along with an email encouraging me to blog about it, and who am I to refuse a request to blog about something as fascinating as this? It's a whopping 489 pages long so I can't even begin to summarise it, but there a...Full Article.

Query behaviour when you cross the LowMemoryLimit

2009-03-30T09:51:00.001-07:00

Here's something that had me stumped for quite a long time earlier this week.

I'm doing some performance tuning work for a customer at the moment, and following my usual approach I took a test query, cleared the cache, ran it and noted how long it ran for and what I saw in Profiler and MDX Studio. However this time I saw some strange things happening: I'd run the same query on a cold cache more than once and the query times would differ by anything up to a minute; even more confusing, the ...Full Article.

Analysis Services and the System File Cache

2009-03-09T11:10:00.005-07:00

Earlier this week Greg Galloway sent me an email about some new code he'd added to the Analysis Services Stored Procedure Project to clear the Windows system file cache:
http://asstoredprocedures.codeplex.com/Wiki/View.aspx?title=FileSystemCacheFull Article.

Updated Wiki: Home

2009-03-09T11:10:00.003-07:00

Microsoft SQL Server Community Projects & Samples

SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects.

Updated Wiki: Home

2009-03-09T11:10:00.001-07:00

Microsoft SQL Server Community Projects & Samples

SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects.

SQL_VARIANT datatype and behaviour changes

2009-02-26T09:23:00.007-08:00

Full Article.

SQL Server Conference in Italy

2009-02-26T09:23:00.005-08:00

I'm doing some work with Marco Russo and Alberto Ferrari at the moment, and for the benefit of any Italian (or Italian-speaking) readers of this blog I thought I'd mention that they are involved in organising a SQL Server conference near Milan. You can find out more and register here:
http://www.s...Full Article.

Madison and SSAS?

2009-02-26T09:23:00.003-08:00

On Monday Microsoft announced "SQL Server Fast Track", a set of reference architectures for data warehousing with SQL Server. This has all been blogged very well by others, so if you're interested in finding out more I suggest you read Peter Koller:
htt...Full Article.

Updated Wiki: HowToUseCodePlex

2009-02-26T09:23:00.001-08:00

How do I use CodePlex to download samples and sample databases?

To download the entire set of SQL Server product samples, click on the releases tab for this portal project, then click on the appropriate installer for your computer.
To focus on samples for a particular technology on the appropriate Product or Community link in the sample table on the home portal page. After clicking on that link you can:

Down...Full Article.

New Comment on "HowToUseCodePlex"

2009-02-19T07:44:00.000-08:00

I do not understand where the AdventureWorks2008 database has gone, last time when i installed the codeplex adventureworks database, a database named "AdventureWorks2008" was made. When i reinstalled the sample databases, it is gone! I'm reading a sql tutorial that uses the AdventureWorks2008 database extensively, so please anybody tell me where to get it? Full text search and filestream are enabled....Full Article.

Find Alphanumerals only

2009-02-17T06:43:00.001-08:00

Implementing Real Analysis Services DrillDown in a Reporting Services Report

2009-02-17T06:42:00.001-08:00

Sean Boon recently blogged about an approach to implement drilldown on charts with Reporting Services when Analysis Services is used as the data source, and it got me thinking about ways to implement drilldown in Reporting Services in general. There are two standard methods used to do this that are widely known about:

The first can be described as "fetc...Full Article.

SQLBits IV Registration Open!

2009-02-11T08:15:00.001-08:00

Registration for SQLBits IV (the UK's - and perhaps the world's - largest free SQL Server tech conference), which will be taking place on March 28th in Manchester is now open:
http://www.sqlbits.com/

We've got four tracks of top-notch presentations including some very strong BI sessions. I'll be speaking, and among other speakers we've got SSIS-superstar Jamie 'twoFull Article.

PASS European Conference 2009 and Analysis Services Monitoring

2009-02-09T07:27:00.007-08:00

Last November, at the PASS Summit in Seattle, I presented a session on building a monitoring solution for Analysis Services, Integration Services and Reporting Services which seemed to go down pretty well. I was lucky in that the SQLCat team presented a very similar session, although just covering Analysis Services, the next day - so at least I got to present first! Anyway, I see that they've just got round to publishing their material on this subject here:

SQLBits IV voting now open

2009-02-09T07:27:00.005-08:00

We've now closed session submission for SQLBits IV, which will be taking place on March 28th in Manchester. Once again we've had a great response and it's now time to vote for which of the 83 submitted sessions you'd like to see; to do this you need to register on the site and then go to
http://www.sqlbits.com/information/PublicSessions.aspx
and choose the sessions you'd like to see.

Full Article.

Speeding up the Query Parameters Dialog in the SSRS Query Designer

2009-02-09T07:27:00.003-08:00

Boyan Penev recently blogged about the virtues of setting default parameters and he's right: it's a good idea to set parameter defaults. However I was with a customer yesterday who were waiting for a really long time to even open the Query Parameters dialog in the SSRS query designer. I was a bit curious as to why this was, so I ran a Profiler trace. What I found was then when I opened t...Full Article.

Fact dimension relationships

2009-02-09T07:27:00.001-08:00

If you build a dimension directly off a fact table (a fact dimension), when you add it to your cube and set the relationship between that dimension and the fact table you have a choice about the type of relationship you can use: you can use either a Fact relationship type or a Regular relationship type. I've always used Fact relationships but never known whether there was anything special about them - there was in some of the early Yukon betas but the functionality got dropped before RTM.

...Full Article.

Updated Wiki: Home

2009-02-09T07:26:00.005-08:00

Microsoft SQL Server Community Projects & Samples

SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects.

Updated Wiki: Home

2009-02-09T07:26:00.003-08:00

Microsoft SQL Server Community Projects & Samples

SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects.

Updated Wiki: Home

2009-02-09T07:26:00.001-08:00

Microsoft SQL Server Community Projects & Samples

SQL Server 2008 product samples are available for each project below or you can download all the samples together (except AdventureWorks), or select from the menu below for specific projects.

A SysAdmin's Guide to Change Management

2009-01-08T10:40:00.005-08:00

In the first in a series of monthly articles, 'Confessions of a Sys Admin', Matt describes the issues involved in Change Management, and gives a simple guide.

Source Click Here.

Exchange: Recovery Storage Groups

2009-01-08T10:40:00.003-08:00

It can happen at any time: You get a request, as Admin, from your company, to provide the contents of somebody's mailbox from a backup set as part of an investigation. The Recovery Storage Group is usually the easiest way to do this. It may either mean using the Exchange Management Console or the Exchange Management Shell. Jaap explains all.

Source Click Here.

Rendering Hierarchical Data with the Treeview

2009-01-08T10:40:00.001-08:00

It sometimes happens that Web Server controls that visualize data don't quite fit with the way that the data is actually held in the application. This shouldn't be an obstacle, as Ion Freeman points out. To prove his point, he shows how to use Nested Sets with Treeview.

Source Click Here.

Build Your Own Virtualized Test Lab

2009-01-08T10:39:00.009-08:00

Desmon explains the fundamentals of building a test lab for Windows servers and Enterprise applications from scratch using Hyper-V on a supported x64 machine. He even suggests tips and tricks to help make the journey smoother.

Source Click Here.

UK SQL Server User Group (Reading, 15th Jan) - Business Intelligence; Chris Webb (Creating a SSRS, SSAS & SSIS monitoring solution with SSIS, SSAS & SSIS) and Allan Mitchell (Data Mining - time prediction)

2009-01-08T10:39:00.007-08:00

Business Intelligence - Creating an SSIS, SSAS & SSRS monitoring solution with SSIS, SSAS and SSRS; Data Mining - Marking Predictions over time Cost Free Registration Via UK SQL Server User Group Address Building 3, Microsoft Campus, Thames Valley Park, Reading, RG6 1WG Directions to Event Join us for another UK SQL Server User Group meeting. 5.30pm - 6:00 Registration and networking + Tea/Coffee with biscuits. Meet and greet. 6:00pm - 6:30pm Round Table discussion and Nuggets - ALL Take stock...(read more)

Source Click Here.

UK SQL Server User Group (Leeds, 28th Jan) - Martin Bell (What's new for DBA's in SQL Server 2008?)

2009-01-08T10:39:00.005-08:00

What's new for a DBA in SQL Server 2008; Nuggets and networking Cost Free Register Via UK SQL Server User Group Address Leeds Metropolitan University Directions to Event What's new for a DBA in SQL Server 2008? There are many great new features in SQL Server 2008. In this presentation Martin will demonstrate some of his favourite features that will be of interest to DBA including the new Auditing feature, Transparent Data Encryption, Change Data Capture, Resource Governor, Compression features, Performance...(read more)

Source Click Here.

UK SQL Server User Group (Edinburgh, 29th Jan) - Rob Carrol Microsoft PFE (Upgrading to SQL 2008) and Martin Bell (Resource Governor demo)

2009-01-08T10:39:00.003-08:00

Upgrading to SQL Server 2008 - Rob Carrol PFE; Resource Governor demo; SQL Nuggets and networking. Cost Free Register Via UK SQL Server User Group Address Microsoft Office at Waverley Gate, Edinburgh Directions to Event Scottish Area SQL Server User Group Meeting, Edinburgh - Thursday 29th January Upgrading to SQL Server 2008 - Rob Carrol As a Premier Field Support Engineer for Microsoft, Rob is heavily involved with clients who are in the process or looking to upgrade to SQL Server 2008. In this...(read more)

Source Click Here.

Testing Times - mdf fragmentation

2009-01-08T10:39:00.001-08:00

NTFS fragmentation degrades SAN performance? Well it does for insane fragmentation....(read more)

Source Click Here.

Start / Stop SQL Server, SSIS, SSAS, SSRS and SQL Server Agent at once with a batch file

2009-01-08T10:38:00.015-08:00

A lot of developers have SQL Server and its different services running on their PC or notebook to develop or test BI solutions. Unfortunately this slows down your system quite a lot. To speed things up when not using SQL Server, I used to stop and start each service manual quite often.

Recently I found out that it's possible to start and stop all services at once with a simple batch file. It now only takes a couple of seconds instead of a few minutes and some annoying steps.

Copy/paste the following in a .txt file and rename it to .bat to make it a batch file, execute it by double clicking the file.

START SCRIPT:

NET START "SQL Server Agent (MsSqlServer)"
NET START "MsSqlServer"
NET START "MsSqlServerOlapService"
NET START "ReportServer"
NET START "SQL Server Integration Services"

STOP SCRIPT:

NET STOP "SQL Server Agent (MsSqlServer)"
NET STOP "MsSqlServer"
NET STOP "MsSqlServerOlapService"
NET STOP "ReportServer"
NET STOP "SQL Server Integration Services"

Source Click Here.

FullText Search fails to start on Windows 2008 Cluster

2009-01-08T10:38:00.013-08:00

I'd recently built a two-node failover cluster for a customer using Windows Server 2008 and it's working really well. The customer wanted to extend the cluster by adding a third node and I came across a problem with the Full Text Search service following the SQL Server installation.

The new node was introduced to the failover cluster - no problem. I'd re-run SQL Server setup.exe on the new node for each of the three instances, then run Service Pack 2, plus CU7 to synchronise the SQL binaries on the new node with the existing nodes. I'd adjusted the Possible Owners and Preferred Owners to reflect the intended failover actions. Next I wanted to test failover of each instance to the new node to validate SQL Server started and there were no problems.

Everything worked fine, except FullText Search - which wouldn't start, the following appears in the Application Log:

The SQL Server FullText Search (SQLInst2) service depends the following service: NTLMSSP. This service might not be installed.

There is a mention of this problem in the following KB article, although the resolution is to install SP2 for SQL 2005 - which had already succeeded.

List of known issues when you install SQL Server 2005 on Windows Server 2008

After some further research and testing - I changed the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\msftesql$SQLInst2\DependOnService

The DependOnService showed two values RPCSS, NTMLSSP on the new node, but the same key only showed RPCSS on the existing 2 nodes (where the instances failover without problem). Removing NTLMLSSP from this registry key for all three FullText Search instances on the new node and restarting the server resolved the problem - and the instances now failover.

Regards,

Justin Langford

Coeo - SQL Server Consultants | SQL Server Remote DBA

Source Click Here.

SQL Server 2008 Information

2009-01-08T10:38:00.011-08:00

I was searching for a particular document for SQL 2008 and thought I'd list the links to what I found, there's some especially good white papers in Technet, I can recommend the T-SQL enhancements and the indexed view white papers especially: SQL Server 2008 White Papers http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx Technet SQL 2008 White Papers http://technet.microsoft.com/en-us/library/bb418496.aspx A list of blogs and other sites, sadly no Grumpy Old DBA http://msdn.microsoft...(read more)

Source Click Here.

Presenting New Year's resolutions

2009-01-08T10:38:00.009-08:00

Donald(Farmer) has posted his new years resolutions for his speaking engagements. http://www.beyeblogs.com/donaldfarmer/archive/2009/01/you_say_you_wan.php They're great.

I totally agree with the reduction of slides. My presentations last year had few slides and is something I'm going to commit to more in 2009. I remember seeing Mike Taulty presenting at a usergroup meeting and he had slides that moved rather than being static. I'm going to commit to moving the slides I do use, from text ones, to graphical ones.

Donald says he's going to stop using analogies. I love analogies so not sure I'll do this but ensuring they are relevant is something that one needs to do.

-

Source Click Here.

Compatibility Level of a database

2009-01-08T10:38:00.007-08:00

Source Click Here.

UK BI User Group meeting next week

2009-01-08T10:38:00.005-08:00

OK, this is rather short notice I know but the UK SQL Server User Group is hosting a BI evening event next week, on January 15th at Microsoft's HQ in Reading:
http://sqlserverfaq.com/default.aspx?item=event&itemid=139

Since this is the rumoured date for Microsoft laying off 15% of its staff, you might be advised to wear some wellies in case you step in a puddle of blood on the floor. Hopefully all the BI folks will be ok though...

Anyway if you follow the link you'll see that I'm one of the speakers: I'll be repeating the session I did at PASS last year on building a monitoring solution for Integration Services, Analysis Services and Reporting Services. Also speaking will be Allan Mitchell, on his new pet hobby of data mining. Hope to see you there!

Source Click Here.

Check out another Erland Sommarskog's utility: ABAPERLS

2009-01-08T10:38:00.003-08:00

Will we need many DBAs or no DBAs at all in the future?

2009-01-08T10:38:00.001-08:00

This is a very exciting/confusing/glooming period for SQLServer Developers/DBAs

On one hand there is the 'cloud' which will take our precious boxes away from us and we won't be needed to manage it anymore.

On the other hand is an avalanche of technologies bundled with every release of SQL Server.

Back in the 2000 days all you really needed to know was

VB/VBA/VBS

DTS

T-SQL

MDX

SSAS

DOS

WSH

Profiler/QA and other tools

I can just see the skills required for a SQL Server DBA these days

VB/VBA/VBS

SSIS

DTS

Powershell

T-SQL

MDX

SSAS

SSRS

SSNS

LINQ

Entity Framework

SSDS

SSMS/ Profiler/QA/BI Studio and other tools

With Kilimanjaro this list will only get longer.

Soon your IT shop will look like a hospital.

You need some SSIS with C#? Talk to DBA A

You need some Powershell automation with SMO? Talk to DBA B since DBA A doesn't know Powershell

You need some DTS? Talk to DBA C because DBA A and DBA B started to use SQL Server with version 2005 (9) and don't know anything about DTS

Is there really one super DBA who knows all these technologies? I don't think so; it is time to start specializing. Besides the stuff you absolutely need to know like T-SQL, the internals and maintenance, you will have to pick your direction.

So I ask you the reader: what brings the future for the SQL Server DBA?

Source Click Here.

[New England] New England Data Camp v1.0 - January 24

2009-01-08T10:37:00.004-08:00

If you live in the greater Boston area you might be interested in knowing that the New England SQL Server Users Group and Southern New England SQL Server Users Group have been quietly working for the past several weeks to create a SQL Server oriented Code Camp, using the PASS Community Connection framework. Following is the blurb we came up with to describe the event:

It's all about the data! Today's applications are increasing data-dependent and whether you are a developer, DBA, or BI professional, you need to know how to work a variety of data across a range of products. This free, full-day Code Camp-style event will feature three concurrent tracks throughout the day to satisfy any tech professional who is interested in the latest and greatest tips and techniques for working with SQL Server 2005 and 2008, the Microsoft BI toolset, and all of the new .NET data access technologies, including LINQ and the Entity Framework.

Interested in attending? Click here to join us! Register soon; space is limited and the event is starting to fill up. You can find a current list of the sessions that are planned for the day by clicking here. I'll post again once we have a full time table worked out.

Like all Code Camp style events, this one has an open call for speakers, which we'll leave open until early next week. If you're interested in presenting, please submit an abstract or two! The more speakers the better for this kind of community event. For instructions on how to do that, please see Chris Bowen's blog post announcing the Data Camp.

Please let me know if you have any questions. We're looking forward to a great event, and hope to see you there!

Source Click Here.

Book Reviews.

2009-01-08T10:37:00.003-08:00

When I wrote the 2005 book, I promised that I would take the time to respond to reviews as much as possible. From the good ones if I didn't really believe that the person read the book (there were a few I have seen, but not too many people tend to write good reviews that clearly haven't read the book unless they are paid to.) In fact, I am constantly surprised with good reviews, mostly because it is so difficult to write a book. I just spent an hour yesterday tracking down 20+ typos that the Chinese translator found.

I will also comment on the less good ones that are posted by people who clearly don't like the material. You aren't required to like the book, and when I am writing it I purposefully will ruffle the feathers of certain groups of people (particularly the ones who don't believe normalization matters!) In fact, I think that the 2005 book was significantly better than the first primarily because of a few reviews that smacked me around and made me realize some of the errors in judgment that I made. My favorite review of the 2005 book was a private review that was scathing in a few important points that I applied to the 2008 book.

What burns my feathers is bad reviews that have no information whatsoever. I mean, take this review of the book on Amazon (http://www.amazon.com/review/R29BAGXMZMIC7Y/ref=cm_cr_rdp_perm):

Waste time to reading this book.,
"Since nobody publish a review for this book, I would like to say something. I have to say: Don't read it. It just waste your time."

Now, I won't make any kind of fun of the reviewers grammar, as it is very likely that he is not a native English speaker. But if I were to review a remake of Bio-Dome where Pauly Shore spoke with an English accent in Shakespearean-style rhyme and stopped watching the film 5 minutes inI could say more than this about than "it will waste your time." I will also admit that there are probably a million people that would plunk down a tenner and happily watch this movie (and not just because movie theater popcorn is so much better than you can make at home.) Part of a good book review is to identify the type of reader. If this person is a DBA, this might be that he read the first chapter and said "bleh," then fair enough. If he read the words "database" and thought that there would be dating tips, well, yeah, it would be a waste of time.

I might not be able to speak for every author out there, but I am sure that almost any author loves to get honest feedback on their work, particularly us who write trade literature. I want to target what people want to read, and with new editions likely for new versions of SQL Server, it is important to provide new/fresh/useful upgrades to the material. The best place to determine what to add are reviews: public, private , whatever, as long as they are from people who have read the material and know what they were reading.

As always, if you want to contact me, go to the contact form on my website: http://drsql.org/contactus.aspx (you can contact me anoymously there too) or email me at louis@drsql.org. Just please give honest feedback about what you have seen, not just post a review because no one has published a review yet.

Source Click Here.

Welcome to 2009 and 4 Years of Blogging

2009-01-08T10:37:00.001-08:00

It's hard for me to believe it, but I've been blogging about SQL Server for over four years now. For those of you who've been reading my blog all of these years, thank you. And if you've commented or added a few thoughts over the years, I thank you even more! It's hard to get up the courage and carve out the time to post in the first place, so adding a comment is always a nice validation that the blog is both being read and offering something of value.

I also want to say a special word of thanks to my friend Michele Crocket and the team at SQL Server Magazine for giving me the opportunity to blog there starting way back in December of 2004. If you're curious, take a look at my first (rather uneventful) blog post here. Over the years, I've written an average of two blog posts per week. But, for some reason, my favorite blog posts have little to do with a specific SQL Server tip or piece of practical advice. My favorite blog posts usually tend to be off-topic articles like the gathering of the first PASS board of directors, my series of posts about getting into the book writing business, and the shower that tried to kill me.

As we begin the new year, there's a lot to look forward to with many new innovations in the technology, SQL Server, that is the hub of our professional life. Drop me a note if you think of something you'd like to see get special attention as I blog. And, once again, thanks for reading my blog as it now enters its fifth year.

-Kev

Source Click Here.

Protection for the Data Journey

2009-01-05T08:16:00.003-08:00

The DBA cannot be sure that the sensitive data that is flowing to the database has been sufficiently protected. If sensitive data is compromised while it is in storage there is no doubt that the DBA will be the first questioned.

Source Click Here.

Session submission is now open for SQLBits IV - March 2009 - Manchester

2009-01-05T08:16:00.001-08:00

Session submission is now open for SQLBits IV.

It will be in Manchester on the Saturday 28^th March 2009.

We are planning on having more sessions than before so if you submit a session you will have more chance of being chosen.

To submit a session go to http://www.sqlbits.com/information/SessionSubmission.aspx ( you will need to register on the site to be able to submit a session)

Source Click Here.

Do you have a backup?

2009-01-05T08:15:00.015-08:00

Its shocking how many people don't even do the basics of ensuring your data is protected.

I've just read this article http://itknowledgeexchange.techtarget.com/sql-server/mirroring-isnt-a-backup-solution/ about a company that ran JournalSpace.com that relied on RAID to protect itself. NO BACKUPS.

Shocking. I was asking thother day about spares for raid arrays, if you are bothered about spares I would hope you have all your backups sorted as well.

I remember I was told that when the World Trade Centers were bombed in 1993 many companies went out of business because they didn't have backups of their data.

Just imagine if your company didn't have any of its data, it would be crippled, as happened with JournalSpace.com

So remember take backups and put them offsite.

-

Source Click Here.

Starting to get into Twitter

2009-01-05T08:15:00.013-08:00

If you want to follow you can hear http://twitter.com/simon_sabin

-

Source Click Here.

Demo tips

2009-01-05T08:15:00.011-08:00

This is a great list of demo tips from Buck . I would agree with Bill that you need to change the foreground hihghlighting to change

I would also add that I find I often end up in Notepad and the command prompt so you need to set the font size in those as well.

Fortunately for the command prompt you can have a Demo Command prompt that is configured with big text.

Then there is the use of zoomit. I haven't actually used it but I know I should, especially for those areas that you can't increase the font size. Although changing your font resolution to large sccale fonts 120 dpi can help, although I've seen dialogs look odd because of this.

Finally I would add the use of a multi desktop tool that means you only have one app on one desktop. This makes things look a lot smarter and provides an easy way to flip between the different applications.

I'm working on a process for saving settings and being able to easily restore them, because changing the font sizes can be a pain.

-

Source Click Here.

Rotating text in SQL Server Reporting Services

2009-01-05T08:15:00.009-08:00

I was reminded this morning of how to rotate text in a report.

I've also developed some code to do it by use of graphics if you prefer the text to go in different directions. More on that later

-

Source Click Here.

New MVP on the block

2009-01-05T08:15:00.007-08:00

Sean McCown has been given the MVP award in January this year. I did a google to find out somethings about him and came across a great article on SQL Server Central about great responses to interview questions from experienced DBAs it is so true though. What I find is people can have years of experience working with SQL Server but no knowldge of SQL because they've been doing the same thing for 10 years. They have other people that do design and development and performance tuning, so all they know is how to check the status of agent jobs.

Sean's got a few blogs http://weblog.infoworld.com/dbunderground, http://dbarant.blogspot.com and a tutorial site http://midnightdba.itbookworm.com/

Contgratulations Sean

-

Source Click Here.

Biggest secret feature of Management Studio

2009-01-05T08:15:00.005-08:00

The biggest feature of management studio that most people don't know about is the keyboard shortcuts for stored procedures.

You can configure a shortcut i.e. CTRL+1 to execute a stored procedure. Whats more if you have some text highlighted it can be passed to that SP as a parameter.

To configure go to Tools | Options | Keyboard.

You will see a set of shortcuts defined by default i.e. ALT + F1 for sp_help.

I set CTRL + F1 to sp_spaceused and CTRL + 3 to sp_helptext.

Whilst these are great they can be better. For instance if you have execution plans selected and you run sp_help you will have a huge number of execution plans generated. This causes a huge problem.

I've created a connect item to get the keyboard shortcut functionality extended and to allow selected text to be passed as a string. (Currently if you select Sales.Order and do ALT+F1 it will fail because of the . (period) in the selected text)

The connect item is here https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=390612

-

Source Click Here.

Compatibility Level of a database

2009-01-05T08:15:00.003-08:00

When users migrate from SQL Server 2000 to SQL Server 2005, not all databases are set to Compatibility Level 90 which is required to make use of the newly available functions There are many methods to know the Compatibility Level of a database using queries --Method 1 EXEC sp_dbcmptlevel your_database_name --Method 2 EXEC sp_helpdb your_database_name --Method 3 SELECT name , cmptlevel FROM master .. sysdatabases WHERE name = 'your_database_name' --Method 4 SELECT name , compatibility_level...(read more)

Source Click Here.

What hierarchies are actually being used in my cube?

2009-01-05T08:15:00.001-08:00

Working out how to monitor Analysis Services usage is something I've spent a fair bit of time doing over the last few months; I'll get around to writing up my SQLBits presentation here soon, I promise, but in the meantime here's a quick blog entry on a related topic: how can you work out which dimension hierarchies your users are actually using in their queries? It's useful to know this for a number of reasons, for example if you're thinking of changing the AggregationUsage, AttributeHierarchyEnabled or AttributeHierarchyOptimizedState properties. Or if you're just plain curious.

The first step towards answering this question is to capture all the MDX queries that are being run, and you can do this by running a Profiler trace on the Query Begin event and saving it to a table in SQL Server in the way I described here. Once you've got the queries you then need to work out which queries mention which hierarchies and you can do this in SSIS using the Term Lookup transform in the data flow:

Unfortunately the Term Lookup transform is a bit picky as far as the input it can use. You can get a list of all the hierarchies in every dimension in every database in Analysis Services by running the following DMV query:
select * from $system.mdschema_hierarchies

To be able to use this in a Term Lookup I had to create a linked server in SQL Server pointing to Analysis Services, then building a view that queried the Analysis Services DMV using the OpenQuery function so:
select cast(hierarchy_unique_name as varchar(100)) as HIERARCHY_UNIQUENAME from openquery([AS], 'select * from $system.mdschema_hierarchies')

Having done this, and having run the SSIS package, you then get a table containing one row for each query/hierarchy combination:

Running a select distinct on this table of course gives you a list of all the hierarchies mentioned in your queries:

Using different DMVs would allow you to analyse different aspects of the queries - for example, you could work out which MDX functions were being used in the queries with the mdschema_functions DMV (similar to what Mosha was doing here, but maybe with a view to seeing whether your client tool was using 'bad' MDX functions).

Source Click Here.

5 Reasons why I think MDX is the Devil

2009-01-05T08:14:00.003-08:00

First, an up front apology to Mosha - this is half in jest, half real. MDX is a powerful and useful language, but I find that its very existence has made my life very difficult when it comes to developing a solid Analysis Services solution. That's not to say I don't use it, or endorse it, but I worry greatly about its overall usage.

1) It tempts you by looking like SQL, but once your brought into it you realize how complex it is (if you don't have Mosha's MDX Studio, get it).

2) MDX allows you to do TOO much when it comes to a multi-dimensional structure, without first understanding multi-dimensional concepts. I've seen code that creates filters or subset cubes that overlap or end up cutting out measures implicitly. You have no warning about possibly undermining previous dimensional modeling - that would be a nice addition to see.

3) Along those lines, it enables sloppy dimensional modeling in the first place. "Throw it together, will add in things later". With a multi-dimensional database, it is very important to model it correctly, just like you would a relational structure. Unfortunately there are no Codd/Date rules of normalization, and often very little work done up front understanding the actually relationships and what the data is meant to represent. For example, instead of using a many-to-many cube dimension (what I call the dimesion to measure tie) to represent something like a bill of materials, I often see this done afterwards somehow via MDX calculations and such. Instead of going back to the dimensional modeling, the answer is usually "we can do this in MDX later".

4) Yet another language with its own syntactical oddities - damn you syntax, you will be the death of me!

5) I can't count the number of calculations I've seen in a cube that could have been done before the cube is loaded in the first place. An MDX calculation that does something like PRICE * QUANTITY = TOTAL SALES is just a waste of processing on the cube side. Remember, MDX is post aggregation - so the large the cube, the more dimensions it has and the level of drill down it allows all play a roll in the speed of things.

Those are 5 to start - anyone have anymore?

Source Click Here.

2009 SQL Resolutions

2009-01-05T08:14:00.001-08:00

Every year I put out resolutions, but this year is a little different. I will post my SQL only blog resolutions here, and my personal ones here. Every year I find my ability to put out stuff is limited by my desire for sleep and fun, and this year is no different. So I am going to be realistic this time.I think.

1. Only collaborative works this year. I am collaborating with Tim Ford (http://ford-it.com/) on the oft mentioned DMV book for red-gate. I feel it will get done with or without me this time, but I am committed to making it happen. The MVP book work is mostly done, but there is a good bit more work to do before it goes to print for sure.

2. At least one SQL blog on this site every two weeks or more often. I have my pillar posts that are in progress, (one is ready for posting in a day or two), and I am going to post some of the DMV stuff along too, most likely.

3. Back on the forums more steady now that the holidays have passed. I try to stick to 3-5 posts a day, even when I am writing, but it can be laborious. Luckily the talent that is working the forums has greatly improved over the past three years.

4. Bring my back catalog of posts from drsql.spaces.live.com over to sqlblog in cleaned up/updated form, as well as posting them to my website drsql.org for future reference as well.

5. Keeping drsql.org more up to date with stuff I do.

6. Speak at least 3 times this year (user group (nashville.sqlpass.org), Devlink (devlink.net), and PASS, hopefully).

7. Work with PASS on social media to help make them a solid place to go to meet other SQL nerds, even those on other communities.

8. Work on my responsibilities with the Nashville SQL User's group to help us turn a corner to a thriving group. Right now we have a lot of potential, but need to do a bit more.

9. Read more. One thing about producing content is that it prohibits you from ingesting content from others.

See, realistic.

Source Click Here.

Session submission is now open for SQLBits IV - March 2009 - Manchester

2009-01-02T09:14:00.007-08:00

Session submission is now open for SQLBits IV.

It will be in Manchester on the Saturday 28^th March 2009.

We are planning on having more sessions than before so if you submit a session you will have more chance of being chosen.

To submit a session go to http://www.sqlbits.com/information/SessionSubmission.aspx ( you will need to register on the site to be able to submit a session)

Source Click Here.

SQLBits - Introduction to XML, Performance Tuning SQL and SQL Injection

2009-01-02T09:14:00.005-08:00

I've submitted a couple of sessions for SQLBits IV

You can see the sessions that have been submitted so far by going here -Submitted Sessions

Source Click Here.

Disk rotation and usage of spares

2009-01-02T09:14:00.003-08:00

When I was a lad and starting out in work an learning about servers and disks I was told of large corporations that regularly replaced storage drives to mitigate against failures.

They would start after x years of buying new storage and start replacing drives. They would, by the time they reached 3 or 4 years have replaced all the drives and be looking to cycle through again.

Things have moved on greatly since I heard this story and wondered if anyone does this, given the proliferation of SANs and often the complete replacement culture I've seen in many places.

The other thing I often find in small places is the lack of spare drives being used in RAID arrays. Whilst I can understand that if, you have spare drives somewhere close, you actively monitor the drives. However in the event of a failure over the weekend a spare can save you in the even you are running a less resilient RAID array or are unlucky.

Do people think that with RAID 10 that the likelihood of getting 2 drive failures in the same mirrored pair is really rare and so don't need spares.

I've created a quick survey if you would like to participate. Its only got two questions so should take you less time that it has for you to read this post.

If the survey doesn't appear above Click Here to take survey Cheers

Source Click Here.

Point in time restore in Bulked logged mode

2009-01-02T09:14:00.001-08:00

Pop quiz.

Your database is in bulk logged mode. You do a transaction log backup.

Can you restore the log to a point in time between the main backup and the log backup?

Scroll down for the answer

Answer: It depends

If you don't have any bulk logged operations then you can however the second you have a bulk logged operation you are stuffed, and you have to restore the whole log. So if someone has put a little SELECT INTO statement of a SSIS package that does BULK insert you won't be able to do point in time recovery.

So the lesson learnt is, only switch to BULK logged mode when you don't want to do point in time recovery.

Source Click Here.

SQLBits - Introduction to XML, Performance Tuning SQL and SQL Injection

2009-01-02T09:13:00.014-08:00

I've submitted a couple of sessions for SQLBits IV

You can see the sessions that have been submitted so far by going here -Submitted Sessions

-

Source Click Here.

New version, Decode and Find, and documentation

2009-01-02T09:13:00.013-08:00

I've just release a new version of Internals Viewer available here.

This release has a few bug fixes in it and also the Decode and Find tool window has been added, which I'll explain below.

I've also created some documentation on the CodePlex site. It covers the basic functionality of the add-in as is available here.

The Decode and Find window was available in Version 1 of Internals Viewer. It allows a value to be encoded to a particular type and then searched for in the Page Viewer.

To open it click the icon on the Page Viewer toolbar button:

It will also give a breakdown of how certain types are structured, for example smalldatetime:

In this version it's an SSMS tool window, so it can be docked or tabbed, just like the Find and Replace window.

In the latest release the Decode and Find window will automatically link to the active Page Viewer window, and can also be used if no Page Viewer is active.

Source Click Here.

Disk rotation and usage of spares

2009-01-02T09:13:00.011-08:00

When I was a lad and starting out in work an learning about servers and disks I was told of large corporations that regularly replaced storage drives to mitigate against failures.

They would start after x years of buying new storage and start replacing drives. They would, by the time they reached 3 or 4 years have replaced all the drives and be looking to cycle through again.

Things have moved on greatly since I heard this story and wondered if anyone does this, given the proliferation of SANs and often the complete replacement culture I've seen in many places.

Do people think that with RAID 10 that the likelihood of getting 2 drive failures in the same mirrored pair is really rare and so don't need spares.

I've created a quick survey if you would like to participate. Its only got two questions so should take you less time that it has for you to read this post.

If the survey doesn't appear above Click Here to take survey Cheers

-

Source Click Here.

Point in time restore in Bulked logged mode

2009-01-02T09:13:00.009-08:00

Pop quiz.

Your database is in bulk logged mode. You do a transaction log backup.

Can you restore the log to a point in time between the main backup and the log backup?

Scroll down for the answer

Answer: It depends

So the lesson learnt is, only switch to BULK logged mode when you don't want to do point in time recovery.

-

Source Click Here.

Happy New Year

2009-01-02T09:13:00.007-08:00

Testing Times 4 - 4 semiT gnitseT

2009-01-02T09:13:00.005-08:00

Yeah I know it's a silly title but I decided to up my testing by mirroring the database that I populate, that way I can create havoc on two servers at the same time and get a feel for how mirroring might be of use to me in the future, or not. One slight struggle was that I didn't really have enough SQL 2005 enterprise class servers to use, in this instance I couldn't see any value in using a collection of PC's and/or laptops, so all the Servers are pretty hefty x64 boxes. What did...(read more)

Source Click Here.

CompareStorage@GrumpyOldDBA

2009-01-02T09:13:00.003-08:00

As a slightly seasonal touch of lightness I though I'd just do a comparison of DAS and SAN storage Item DAS SAN Basic Storage Cost Good Hideously expensive Ease of Use Simple How many storage engineers and system administrators does it take to change a lun ? Performance Monitoring Easy Almost Impossible Ability to modify configuration Easy Most " automatic/dynamic " so largely not. Actual Performance Predictable Who knows Expandability Pretty simple Expensive e.g. how much for a tray of disks ??...(read more)

Source Click Here.

Pointless challenge for 2009

2009-01-02T09:13:00.001-08:00

So you've gone back to work and can't really get back into it after all that chocolate and booze. Here's a little challenge.

Phil Brammer wondered what would happen to the usecounts column in the sys.dm_exec_cached_plans dmv when you hit the limit of an integer.

The problem is that the limit of an integer is 2147483647.

So the challenge is to write code that will result in the limit being hit as quickly as possible.

Also take into consideration use of multi core machines.

With the code I tried I was shocked at the rate of executions that can be achieved, on sqlexpress on a 8 core machine (note express is limited to 4 cores) I got 1/2 million requests/second.

-

Source Click Here.

Reproducing deadlocks involving only one table

2009-01-02T09:12:00.021-08:00

Although the best known deadlock scenario involves two connections modifying two tables in different order, there are also other deadlock scenarios involving only one table. Besides, in some scenarios each connection needs to issue only one statement, and it is enough to get a deadlock. Also in some scenarios only one connection needs to modify or acquire exclusive locks - the other one may only read data and only acquire shared locks and still embrace in a deadlock. I will provide repro scripts...(read more)

Source Click Here.

NULL on joined columns

2009-01-02T09:12:00.020-08:00

Source Click Here.

Happy New Year

2009-01-02T09:12:00.019-08:00

Source Click Here.

Performance impact: file fragmentation and SAN - Part VI

2009-01-02T09:12:00.017-08:00

A script to fragment a test file

Well, this post won't cover any actual test results and is not specific to SAN. But in the spirit of full disclosure, I've attached the script I used to control the fragmentation level of the test files mentioned in this series of posts. The script is written in Perl, but can be easily done in any language.

The script does not itself fragment any file. Rather, it fragments the entire disk drive into non-contiguous blocks, or fragments, of a given size. A file (such as a database file) created afterwards is then necessarily fragmented into these fragments.

It takes three parameters at the beginning of the script: (1) $DRIVE to specify which drive the test file will be placed, (2) $FRAGMENT_SIZE to specify the size of the fragments in bytes, and (3) $FREE_SIZE to specify how much free space you want to have after the script completes. So if you want to examine the behavior of a 10GB test file that is fragmented into 128KB fragments, you may want to specify $FREE_SIZE to be slightly bigger than 10GB.

The script begins by creating an empty directory tree with enough directories so that each directory will hold 200 files of the specified size. It then proceeds to create 200 files in each of these directories until the drive is completely filled up. Finally, it randomly deletes some of these files until the free space on the drive reaches the value specified by $FREE_SIZE.

Hope you find this script useful in case you'd like to check out the impact of file fragmentation yourself.

Source Click Here.

Did You Know? You, too, can be a Geek!

2009-01-02T09:12:00.015-08:00

Danny Gould has a new (beta) version of his Internals Viewer, that you can download from Codeplex at http://www.codeplex.com/InternalsViewer

I've just started playing around with it, and the coolest thing is that it integrates right with your Management Studio!

The codeplex site include a Troubleshooting Guide and a User Guide.

It does look like it can display SQL Server 2008 data pages that have been compressed. For pages with ROW compression you'll be able to see the data, but for PAGE compression, no translation is done.

You can also request to have information from the transaction log displayed, but it's not the same as the 3rd party log reader tools, in that it doesn't give you the complete set of values that were inserted or deleted.

I'm still exploring its nuances, and now you can too!

Have fun!

~Kalen

Source Click Here.

A Gift of Script for 2009: Who is Active, Redux

2009-01-02T09:12:00.013-08:00

Last year on December 31 I posted part of a larger monitoring script that I had been working on for a few months. I received lots of great feedback on the little script in both the comments and from people I was working with, and over the course of the last year I estimate that I have invested at least a couple of hundred hours in the script. The result of all of this time is attached, and I hope that this script is as useful for others as it has been for me; I've been using it pretty much nonstop while modifying it and it has become an indispensable part of my toolkit.

Alas, while I had planned to write a comprehensive blog post on the script for publication today, I simply don't have time to do it correctly at the moment--but I wanted to get the script out on the 31st in order to create a kind of tradition. So instead of telling you everything it can do, I will ask that you instead take a look inside the script, where I've done a fairly good job documenting both the parameters and the outputs. I'll do a followup post soon (I hope) and do a better job of telling you about the various options. In the meantime, I'll leave you with a list of the script's major features:

Wait stats collection, if you enable the GET_WAITS switch. This mode aggregates the various resources that the tasks associated with the session are waiting on, and lets you know how long the tasks have been waiting.
Collection of lock information, if you enable the GET_LOCKS switch. A lot of effort went into this particular feature--all of the locks for each session are aggregated in an XML format, and work is done to map the various identifiers associated with each lock to actual object names so that you don't have to do the footwork yourself.
Information about sleeping SPIDs holding open transactions, if you enable the GET_TRANSACTION_INFO switch. If you're being blocked by a SPID that is not active, you should be able to find out about it. This option lets you do it. As a bonus, it also pulls information about transaction log writes in any database in which the transaction has done a write.
Collection of the "outer" command/batch, if you enable the GET_OUTER_COMMAND switch. One of the comments on the script I posted last year asked why, if you issue SQL like "ALTER TABLE dbo.xx ALTER COLUMN yyy FLOAT NULL", you'll end up with statement text that looks like "UPDATE [zzz].[dbo].[xxx] SET [yyy] = [yyy]". This is an internal representation of what's actually happening, but in many cases is not esepecially useful for end-users. So if you want to see what was actually run, now you can.
Every expensive or potentially expensive option is now parameterized. I spent a lot of time tweaking performance and with no options running the thing is pretty fast. Enable some options--especially lock collection--and it will slow down considerably, so use these advanced features with care.

Enjoy, and please keep sending feedback! Let me know what else you would like to see, if you find bugs, etc. I'll post updated versions as major changes are made.

To finish I would like to thank a few of the people who had an impact on the evolution of this script over the past 12 months: Michael Condon, Erland Sommarskog, Aaron Bertrand, Louis Davidson, and Jonathan Kehayias.

Have a great 2009, everyone!

Source Click Here.

A year in review, The 31 best blog posts on SQLBlog for 2008

2009-01-02T09:12:00.011-08:00

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog last year

Here we are with another fascinating collection of links, some of them are chosen for their content and some of them for the great comments.

Which was your favorite post of the past year?

Source Click Here.

Deep dive to MDX presentation - slides

2009-01-02T09:12:00.009-08:00

During SQL PASS 2008 summit, I gave full day preconference seminar "Deep Dive to MDX". The evaluation forms now have arrived, and the scores for the content, relevance and depth portion of the seminar are very good. The scores are not as high, however, for the time allocated (people argued that it should've been 2 days instead of one), and for the materials (since nobody received any materials). Both points are valid, one day turned out to be not enough, and perhaps two days wouldn't have been enough either, since I got into less than a half of material that I had prepared. Also, due to some issues, I couldn't hand down the presentation material - slides and MDX queries/calculations. These issues are cleared now, and I will start uploading the slides from the presentation. I don't suppose they will be very useful for people who didn't attend the session, since slides really only contain major talking points, and the rest was delivered verbally, but they probably will be very useful for people who did attend.

The time allowed to cover the following subjects:

Overall Architecture
Subcubes
Sonar
Storage Engine query plan
Storage Engine cache
Formula Engine query plan

Block mode

Formula Engine cache
Sets

More detailed Table of Content can be found here.

I will do series of posts uploading one subject at a time. The first subject is "Overall Architecture", and it gives high-level picture of the components discussed further in the presentation. The slides are here.

Slightly unrelated announcement: MDX Studio 0.4.11 was also released today, with multiple UI enhancements, docked windows (Visual Studio style) etc. Versions for SSAS 2005 and SSAS 2008 are available, and can be downloaded from here.

Source Click Here.

Season's Greetings!

2009-01-02T09:12:00.007-08:00

I was catching up on my blog reading and came across a very nice post on Ward Pond's blog; he gives us some TSQL code that produces a holiday message. It's a few days late, but it's the thought that counts, right?

I did have a few problems with the code on Ward's site... when I copied and pasted to SSMS, there were no line breaks at all, so everything appeared as one huge long line. That made it hard to interpret, and hard to troubleshoot when a syntax error was reported. It turned out that not only did no line breaks appear, but whatever was in the code to break the lines before the word 'cross' got swallowed up, so the last part of the code was:

from firstTablecross join SecondTablecross join ThirdTablecross join ForthTablewhere therow%2!=0

So I fixed up the code to add line breaks, to separate the 'cross' from the preceding table names, and to fix a spelling error.

Enjoy... and thanks, Ward!

with FirstTable as (select top 14 row_number()
          over (order by name) therow
     from master.sys.objects)
   , SecondTable as (select replicate(char(32),15) theLine)
   , ThirdTable as (Select replicate(char(124),3) theOtherLine)
   , FourthTable as (
      select (select left(db_name(4),1) ) + (
      select substring(db_name(2),2,1) ) + (
      select replicate(substring(db_name(1),6,1),2) ) + (
      select replace(schema_name(4),'s','') ) + (
      select char(max_length * 2)
         from master.sys.types
         where system_type_id = 36) + (
      select top 1 substring(wait_type,10,2)
         from master.sys.dm_os_wait_stats
         where wait_type like 'PageIO%' ) + (
      select substring(@@version,4,1) ) + (
      select substring(object_name(55),4,2) ) + (
      select convert(char(1),(reverse(convert(char(7),name))))
         from sys.configurations
         where configuration_id = 124 ) + (
      select left(db_name(1),3) ) theEnd
)
select case therow
      when 11 then
           stuff( theLine,(datalength(theLine)/2)
              - 1,3,TheOtherLine)
      when 13 then upper(theEnd )
      else stuff( theLine,(datalength(theLine)/2) -
              (theRow/2),therow,
                  replicate(char(42),therow)) end ' '
from firstTable
      cross join SecondTable
      cross join ThirdTable
      cross join FourthTable
where therow%2!=0;

Happy New Year!

~Kalen

Source Click Here.

Performance impact: file fragmentation and SAN - Part V

2009-01-02T09:12:00.005-08:00

SQL Server workloads

So far, the discussions in all the previous posts (1, 2, 3, and 4) on the performance impact of file fragmentation on a drive presented from a high-end enterprise-class disk array are related to disk I/O workloads. Ultimately, you want to know how file fragmentation may impact your SQL Server workloads.

In this post, I share with you some results from running three SQL Server workloads: (1) database backups, (2) checkpoints, and (3) table scans. These SQL Server workloads were run in the same three test scenarios as used in all the previous four posts:

· Non-fragmented. The database files were created on an empty drive,

· 2MB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 2MB in size,

· 128KB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 128KB in size,

The same database was used in all the tests. The database was about 9.5GB in size, and the table on which table scan was performed had 10 million rows and was about 4GB in size. DBCC DROPCLEANBUFFERS was run prior to each table scan.

The following chart is a summary of the SQL Server workload test results:

Clearly, file fragmentation did not have any significant performance impact on these SQL Server workloads. Because the performance of these workloads is dependent on the disk I/O throughput, we could have predicted this from the results of our disk I/O tests as reported in the previous four posts. However, it's still comforting to see the prediction validated with real data points.

This concludes this series of posts on the performance impact of file fragmentation on a drive that is presented from a high-end enterprise class disk array. Overall, the impact was significantly less than what we have seen on a traditional directly attached disk drive. For I/O throughput, there was little to no impact. For I/O latency (or response time), file fragmentation can cause some I/O request to take much longer to complete.

In a future post(s), I'll explore whether the same holds true with a drive presented from a lower end disk array.

Source Click Here.