SQL Server Blogs - SQLTeam.com

Why to avoid SELECT * from tables in your Views

Jeff Smith — Fri, 11 May 2012 14:10:51 GMT

-- clean up any messes left over from before:
if OBJECT_ID('AllTeams') is not null
drop view AllTeams
go

if OBJECT_ID('Teams') is not null
drop table Teams
go

-- sample table:
create table Teams
(
id int primary key,
City varchar(20),
TeamName varchar(20)
)

-- sample data:
insert into Teams (id, City, TeamName )
select 1,'Boston','Red Sox' union all
select 2,'New York','Yankees'

create view AllTeams
as
select * from Teams

select * from AllTeams

--Results:
--
--id          City                 TeamName
------------- -------------------- --------------------
--1           Boston               Red Sox
--2           New York             Yankees

-- Now, add a new column to the Teams table:

alter table Teams
add League varchar(10)
go

-- put some data in there:
update Teams
set League='AL'

-- run it again

select * from AllTeams

--Results:
--
--id          City                 TeamName
------------- -------------------- --------------------
--1           Boston               Red Sox
--2           New York             Yankees

-- Notice that League is not displayed!

-- Here's an even worse scenario, when the table gets altered in ways beyond adding columns:
drop table Teams
go

-- recreate table putting the League column before the City:
-- (i.e., simulate re-ordering and/or inserting a column)
create table Teams
(
id int primary key,
League varchar(10),
City varchar(20),
TeamName varchar(20)
)
go

-- put in some data:
insert into Teams (id,League,City,TeamName)
select 1,'AL','Boston','Red Sox' union all
select 2,'AL','New York','Yankees'

-- Now, Select again for our view:
select * from AllTeams

--Results:
--
--id          City       TeamName
------------- ---------- --------------------
--1           AL         Boston
--2           AL         New York

-- The column labeled "City" in the View is actually the League, and the column labelled TeamName is actually the City!

go
-- clean up:
drop view AllTeams
drop table Teams

ClearTrace Performance on 170GB of Trace Files

Bill Graziano — Thu, 26 Apr 2012 12:22:29 GMT

I’ve always worked to make ClearTrace perform well. That’s probably because I spend so much time watching it work. I’m often going through two or three gigabytes of trace files but I rarely get the chance to run it on a really large set of files.

One of my clients wanted to run a full trace for a week and then analyze the results. At the end of that week we had 847 200MB trace files for a total of nearly 170GB.

I regularly use 200MB trace files when I monitor production systems. I usually get around 300,000 statements in a file that size if it’s mostly stored procedures. So those 847 trace files contained roughly 250 million statements. (That’s 730 bytes per statement if you’re keeping track. Newer trace files have some compression in them but I’m not exactly sure what they’re doing.) On a system running 1,000 statements per second I get a new file every five minutes or so.

It took 27 hours to process these files on an older development box. That works out to 1.77MB/second. That means ClearTrace processed about 2,654 statements per second. You can query the data while you’re loading it but I’ve found it works better to use a second instance of ClearTrace to do this. I’m not sure why yet but I think there’s still some dependency between the two processes.

ClearTrace is almost always CPU bound. It’s really just a huge, ugly collection of regular expressions. It only writes a summary to its database at the end of each trace file so that usually isn’t a bottleneck. At the end of this process, the executable was using roughly 435MB of RAM. Certainly more than when it started but I think that’s acceptable.

The database where all this is stored started out at 100MB. After processing 170GB of trace files the database had grown to 203MB. The space savings are due to the “datawarehouse-ish” design and only storing a summary of each trace file.

You can download ClearTrace for SQL Server 2008 or test out the beta version for SQL Server 2012. Happy Tuning!

ClearTrace Beta for SQL Server 2012

Bill Graziano — Wed, 25 Apr 2012 13:10:40 GMT

I have a beta version of ClearTrace that supports 2012. You can download it at http://www.scalesql.com/cleartrace/ClearTrace.2012.40.beta.zip. Please let me know if you find any issues.

The 5 stages reviewing bad TSQL

Mike Femenella — Mon, 16 Apr 2012 19:56:10 GMT

I'm working with an app team that is light on TSQL expertise this week and couldn't help but draw a parallel to the 5 stages of grieving.

Denial: There’s nothing wrong with the code SQL Server has a bug in it. There is a network problem.
Anger: You’re doing what in your code?! Why on earth are you doing that? That’s crazy.
Bargaining: Fine you can keep your cursor but let’s speed things up a bit.
Depression: Ugh, this is so horrible I’m never going to be able to fix all of it.
Acceptance: Ok, we’re screwed and we know we’re screwed. This is going to hurt…

Unhelpful Help

Geoff N. Hiten — Tue, 03 Apr 2012 17:42:22 GMT

Up until SQL 2012, I recommended installing Books On-Line (BOL) anywhere you installed SQL Server. It made looking up reference information simpler, especially when you were on a server that didn’t have direct Internet access. That all changed today. I started the new Help Viewer with a local copy of BOL. I actually found what I was looking for and closed the app. Or so I thought. Then I noticed something. A little parasite had attached itself to my system.

Yep, the “Help” system left an “agent” behind. Now I shouldn’t have to tell you that running application helper agents on server platforms is a bad idea. And it gets worse. There is no way to configure the app so that it does NOT start the ~~parasite~~ agent each time you restart help. So the solution becomes do not install help on production server platforms. Which is pretty unhelpful.

BIDS Helper 1.6 Beta released!

Luca Zavarella — Mon, 02 Apr 2012 12:42:00 GMT

A new version of this fantastic tool, used by Microsoft BI Stack developers, is out. Now it’s compatible with SQL Server 2012.

In this post, Teo Lachev gives us more details.

This is the project’s Home.

Enjoy!

SQLBits - Unicode Porn

Most Valuable Yak (Rob Volk) — Sat, 31 Mar 2012 16:25:28 GMT

We've just finished up a fantastic event at SQLBits X in London! If you've never been to SQLBits and you can make it to the UK, I highly recommend it. If you didn't attend, here's what you missed.

Meanwhile, for those who attended the Lightning Talk sessions and were disappointed that I ran out of time, here's the last part that you would have seen:

/*    How to Lose Friends and Irritate People...With Unicode!
    Rob Volk
    SQLBits X - London - March 31, 2012
*/

-- some sexy SQL
DECLARE @oohbaby TABLE(i INT NOT NULL UNIQUE,
uni_char AS NCHAR(i),
hex AS CAST(i AS BINARY(2)))
INSERT @oohbaby VALUES(664),(1022),(1023),(1120),(1150),(8857),(11609),(42420),(42427)

-- change results font to larger size, some only work in grid font
SELECT * FROM @oohbaby

SELECT NCHAR(1022) + NCHAR(1023) AS Page3Girl

It's probably better that you run this yourself, in the privacy of your own home/office, you know *wink* *wink* *nudge* *nudge* *say no more*

Update to SQL Server Configuration Scripting Utility

Bill Graziano — Mon, 12 Mar 2012 00:49:20 GMT

Last spring I released a utility to script SQL Server configuration information on CodePlex. I’ve been making small changes in this application as my needs have changed. The application is a .NET 2.0 console application.

This utility serves two needs for me. First it helps with disaster recovery. All server level objects (logins, jobs, linked servers, audits) are scripted to a single file per object type. This enables the scripts to be easily run against a DR server. If these are checked into source control you can view the history of the script and find out what changed and when.

The second goal is to capture what changed inside a database. Objects inside a database (tables, stored procedures, views, etc.) are each scripted to their own file. This makes it easier to track the changes to an object over time. This does include permissions and role membership so you can capture security changes. My assumption is that a database backup is the primary method of disaster recovery for databases so this utility is designed to capture changes to objects.

You can find the full list of changes from the original on the Downloads page on CodePlex.

How to Manage Technical Employees

Ajarn Mark Caldwell — Fri, 02 Mar 2012 06:28:45 GMT

In my current position as Software Engineering Manager I have been through a lot of ups and downs with staffing, ranging from laying-off everyone who was on my team as we went through the great economic downturn in 2007-2008, to numerous rounds of interviewing and hiring contractors, full-time employees, and converting some contractors to employee status. I have not yet blogged much about my experiences, but I plan to do that more in the next few months. But before I do that, let me point you to a great article that somebody else wrote on The Unspoken Truth About Managing Geeks that really hits the target. If you are a non-technical person who manages technical employees, you definitely have to read that article. And if you are a technical person who has been promoted into management, this article can really help you do your job and communicate up the line of command about your team. When you move into management with all the new and different demands put on you, it is easy to forget how things work in the tech subculture, and to lose touch with your team. This article will help you remember what’s going on behind the scenes and perhaps explain why people who used to get along great no longer are, or why things seem to have changed since your promotion.

I have to give credit to Andy Leonard (blog | twitter) for helping me find that article. I have been reading his series of ramble-rants on managing tech teams, and the above article is linked in the first rant in the series, entitled Goodwill, Negative and Positive. I have read a handful of his entries in this series and so far I pretty much agree with everything he has said, so of course I would encourage you to read through that series, too.

New release of "OLAP PivotTable Extensions"

Luca Zavarella — Mon, 06 Feb 2012 11:20:16 GMT

For those who are not familiar with this add-in, the OLAP PivotTable Extensions add features of interest to Excel 2007 or 2010 PivotTables pointing to an OLAP cube in Analysis Services. One of these features I like very much, is to know the MDX query code associated with the pivot used at that time in Excel:

You can find all the details here:

http://olappivottableextend.codeplex.com/

It was recently released a new version of the add-in (version 0.7.4), which does not introduce any new features, but fixes a significant bug:

Release 0.7.4 now properly handles languages but introduces no new features. International users who run a different Windows language than their Excel UI language may be receiving an error message when they double click a cell and perform drillthrough which reads: "XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value". This error was caused by OLAP PivotTable Extensions in some situations, but release 0.7.4 fixes this problem.

Enjoy!

How to import in BIDS more than one SSIS package in one shot!

Luca Zavarella — Thu, 02 Feb 2012 20:27:14 GMT

Have you ever wanted to add more than one Integration Services existing package (e.g. 20 packages) in a SSIS project? Well, you may suppose that an Open Dialog supports multiple files selection to import more than one file at a time ...

BIDS Open Dialog doesn’t allow this, you can just select a single file! Hence the loss of valuable time spent to import the packages one at a time.

Few days ago I learned a trick that solves the problem, thanks to this post by Matt Masson.

Just copy all the packages to import from Windows Explorer (Ctrl + C):

Then just right click on the SSIS Packages folder of the Integration Services project and make a simple Past (CTRL + V):

So “auto-magically” you’ll have all those packages imported in your Integration Services project!!

What can I say... this feature was well hidden!

How to calculate the covariance in T-SQL

Peter Larsson — Wed, 18 Jan 2012 12:01:46 GMT

DECLARE @Sample TABLE
        (
            x INT NOT NULL,
            y INT NOT NULL

        )

INSERT  @Sample
VALUES  (3, 9),
        (2, 7),
        (4, 12),
        (5, 15),
        (6, 17)

;WITH cteSource(x, xAvg, y, yAvg, n)
AS (
        SELECT  1E * x,
                AVG(1E * x) OVER (PARTITION BY (SELECT NULL)),
                1E * y,
                AVG(1E * y) OVER (PARTITION BY (SELECT NULL)),
                COUNT(*) OVER (PARTITION BY (SELECT NULL))
        FROM    @Sample
)
SELECT  SUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)]
FROM    cteSource

So long and thanks for the fish…

Geoff N. Hiten — Wed, 28 Dec 2011 21:03:53 GMT

This marks my last post as a SQLPASS Board member. I learned a lot during my year of service and I thank everyone involved for this opportunity. I would especially like to thank the Chapter leaders and Regional Mentors for Virtual Chapters who (mostly) patiently taught me about Virtual Chapters. I hope the changes I put in place will help strengthen and grow VCs and PASS going forward. I would also like to thank every one who encouraged me to reach beyond my comfort zone and accept a leadership position within the PASS organization.

My overall principle was to be a good steward of the PASS community. Could I have done more? Always. Did I do enough? I hope so. But PASS is a volunteer organization and my time, like yours, is limited. I have other obligations in life that supersede PASS. Now I have more time for some of those. I won’t be going away or leaving the SQL Community. I will still contribute to the community and support PASS, just in a different role. Time to let somebody else enjoy the hot seat for a while.

Finally, everyone who voted (not just for me) deserves a thanks. More voters and more engaged voters, strong candidates, and a vigorous debate were all I wanted out of declaring as a candidate last year. This year the SQL community got exactly that.

Thank you..

Broken Views

Ajarn Mark Caldwell — Tue, 27 Dec 2011 07:16:30 GMT

“SELECT *” isn’t just hazardous to performance, it can actually return blatantly wrong information.

There are a number of blog posts and articles out there that actively discourage the use of the SELECT * FROM …syntax. The two most common explanations that I have seen are:

Performance: The SELECT * syntax will return every column in the table, but frequently you really only need a few of the columns, and so by using SELECT * your are retrieving large volumes of data that you don’t need, but the system has to process, marshal across tiers, and so on. It would be much more efficient to only select the specific columns that you need.
Future-proof: If you are taking other shortcuts in your code, along with using SELECT *, you are setting yourself up for trouble down the road when enhancements are made to the system. For example, if you use SELECT * to return results from a table into a DataTable in .NET, and then reference columns positionally (e.g. myDataRow[5]) you could end up with bad data if someone happens to add a column into position 3 and skewing all the remaining columns’ ordinal position. Or if you use INSERT…SELECT * then you will likely run into errors when a new column is added to the source table in any position.

And if you use SELECT * in the definition of a view, you will run into a variation of the future-proof problem mentioned above. One of the guys on my team, Mike Byther, ran across this in a project we were doing, but fortunately he caught it while we were still in development. I asked him to put together a test to prove that this was related to the use of SELECT * and not some other anomaly. I’ll walk you through the test script so you can see for yourself what happens.

We are going to create a table and two views that are based on that table, one of them uses SELECT * and the other explicitly lists the column names. The script to create these objects is listed below.

IF OBJECT_ID('testtab') IS NOT NULL
   DROP TABLE testtab
go
IF OBJECT_ID('testtab_vw') IS NOT NULL
   DROP VIEW testtab_vw
go   
IF OBJECT_ID('testtab_vw_named') IS NOT NULL
   DROP VIEW testtab_vw_named
go   
CREATE TABLE testtab (col1 NVARCHAR(5) null, col2 NVARCHAR(5) null)
INSERT INTO testtab(col1, col2)
VALUES ('A','B'), ('A','B')
GO
CREATE VIEW testtab_vw AS SELECT * FROM testtab
GO
CREATE VIEW testtab_vw_named AS SELECT col1, col2 FROM testtab
go

Now, to prove that the two views currently return equivalent results, select from them.

SELECT 'star', col1, col2 FROM testtab_vw
SELECT 'named', col1, col2 FROM testtab_vw_named

OK, so far, so good. Now, what happens if someone makes a change to the definition of the underlying table, and that change results in a new column being inserted between the two existing columns? (Side note, I normally prefer to append new columns to the end of the table definition, but some people like to keep their columns alphabetized, and for clarity for later people reviewing the schema, it may make sense to group certain columns together. Whatever the reason, it sometimes happens, and you need to protect yourself and your code from the repercussions.)

DROP TABLE testtab
go
CREATE TABLE testtab (col1 NVARCHAR(5) null, col3 NVARCHAR(5) NULL, col2 NVARCHAR(5) null)
INSERT INTO testtab(col1, col3, col2)
VALUES ('A','C','B'), ('A','C','B')
go
SELECT 'star', col1, col2 FROM testtab_vw
SELECT 'named', col1, col2 FROM testtab_vw_named

I would have expected that the view using SELECT * in its definition would essentially pass-through the column name and still retrieve the correct data, but that is not what happens. When you run our two select statements again, you see that the View that is based on SELECT * actually retrieves the data based on the ordinal position of the columns at the time that the view was created. Sure, one work-around is to recreate the View, but you can’t really count on other developers to know the dependencies you have built-in, and they won’t necessarily recreate the view when they refactor the table.

I am sure that there are reasons and justifications for why Views behave this way, but I find it particularly disturbing that you can have code asking for col2, but actually be receiving data from col3. By the way, for the record, this entire scenario and accompanying test script apply to SQL Server 2008 R2 with Service Pack 1.

So, let the developer beware…know what assumptions are in effect around your code, and keep on discouraging people from using SELECT * syntax in anything but the simplest of ad-hoc queries.

And of course, let’s clean up after ourselves. To eliminate the database objects created during this test, run the following commands.

DROP TABLE testtab
DROP VIEW testtab_vw
DROP VIEW testtab_vw_named

T-SQL Tuesday #025 – CHECK Constraint Tricks

Most Valuable Yak (Rob Volk) — Tue, 13 Dec 2011 19:24:28 GMT

Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks. And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them. You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted. Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway.

Some of you have seen or read about SQL Server constraints and have applied them to your table designs…unless you're a vendor ;)…and may even use CHECK constraints to limit numeric values, or length of strings, allowable characters and such. CHECK constraints can, however, do more than that, and can even provide enhanced security and other restrictions.

One tip or trick that I didn't cover very well in the presentation is using constraints to do unusual things; specifically, limiting or preventing inserts into tables. The idea was to use a CHECK constraint in a way that didn't depend on the actual data:

-- create a table that cannot accept data
CREATE TABLE dbo.JustTryIt(a BIT NOT NULL PRIMARY KEY,
CONSTRAINT chk_no_insert CHECK (GETDATE()=GETDATE()+1))

INSERT dbo.JustTryIt VALUES(1)

I'll let you run that yourself, but I'm sure you'll see that this is a pretty stupid table to have, since the CHECK condition will always be false, and therefore will prevent any data from ever being inserted. I can't remember why I used this example but it was for some vague and esoteric purpose that applies to about, maybe, zero people. I come up with a lot of examples like that.

However, if you realize that these CHECKs are not limited to column references, and if you explore the SQL Server function list, you could come up with a few that might be useful. I'll let the names describe what they do instead of explaining them all:

CREATE TABLE NoSA(a int not null, 
    CONSTRAINT CHK_No_sa CHECK (SUSER_SNAME()<>'sa'))

CREATE TABLE NoSysAdmin(a int not null, 
    CONSTRAINT CHK_No_sysadmin CHECK (IS_SRVROLEMEMBER('sysadmin')=0))

CREATE TABLE NoAdHoc(a int not null, 
    CONSTRAINT CHK_No_AdHoc CHECK (OBJECT_NAME(@@PROCID) IS NOT NULL))

CREATE TABLE NoAdHoc2(a int not null, 
    CONSTRAINT CHK_No_AdHoc2 CHECK (@@NESTLEVEL>0))

CREATE TABLE NoCursors(a int not null, 
    CONSTRAINT CHK_No_Cursors CHECK (@@CURSOR_ROWS=0))

CREATE TABLE ANSI_PADDING_ON(a int not null, 
    CONSTRAINT CHK_ANSI_PADDING_ON CHECK (@@OPTIONS & 16=16))

CREATE TABLE TimeOfDay(a int not null, 
    CONSTRAINT CHK_TimeOfDay CHECK (DATEPART(hour,GETDATE()) BETWEEN 0 AND 1))
GO

-- log in as sa or a sysadmin server role member, and try this:
INSERT NoSA VALUES(1)
INSERT NoSysAdmin VALUES(1)
-- note the difference when using sa vs. non-sa
-- then try it again with a non-sysadmin login

-- see if this works:
INSERT NoAdHoc VALUES(1)
INSERT NoAdHoc2 VALUES(1)
GO

-- then try this:
CREATE PROCEDURE NotAdHoc @val1 int, @val2 int AS
SET NOCOUNT ON;
INSERT NoAdHoc VALUES(@val1)
INSERT NoAdHoc2 VALUES(@val2)
GO

EXEC NotAdHoc 2,2

-- which values got inserted?
SELECT * FROM NoAdHoc
SELECT * FROM NoAdHoc2

-- and this one just makes me happy :)
INSERT NoCursors VALUES(1)
DECLARE curs CURSOR FOR SELECT 1
OPEN curs
INSERT NoCursors VALUES(2)
CLOSE curs
DEALLOCATE curs
INSERT NoCursors VALUES(3)

SELECT * FROM NoCursors

I'll leave the ANSI_PADDING_ON and TimeOfDay tables for you to test on your own, I think you get the idea. (Also take a look at the NoCursors example, notice anything interesting?)

The real eye-opener, for me anyway, is the ability to limit bad coding practices like cursors, ad-hoc SQL, and sa use/abuse by using declarative SQL objects. I'm sure you can see how and why this would come up when discussing Revenge: The SQL.;) And the best part IMHO is that these work on pretty much any version of SQL Server, without needing Policy Based Management, DDL/login triggers, or similar tools to enforce best practices.

All seriousness aside, I highly recommend that you spend some time letting your mind go wild with the possibilities and see how far you can take things. There are no rules!

(Hmmmm, what can I do with rules?)

#TSQL2sDay

Let’s keep informed with “Data Explorer”

Luca Zavarella — Sat, 10 Dec 2011 12:39:20 GMT

At Pass Summit 2011 a new project was announced. It’s a Microsoft SQL Azure Lab and its codename is Microsoft “Data Explorer”. According to the official blog (http://blogs.msdn.com/b/dataexplorer/), this new tool provides an innovative way to acquire new knowledge from the data that interest you. In a nutshell, Data Explorer allows you to combine data from multiple sources, to publish and share the result. In addition, you can generate data streams in the RESTful open format (Open Data Protocol), and they can then be used by other applications. Nonetheless we can still use Excel or PowerPivot to analyze the results.

Sources can be varied: Excel spreadsheets, text files, databases, Windows Azure Marketplace, etc.. For those who are not familiar with this resource, I strongly suggest you to keep an eye on the data services available to the Marketplace:

https://datamarket.azure.com/browse/Data

To tell the truth, as I read the above blog post, I was tempted to think of the Data Explorer as a "SSIS on Azure" addressed to the Power User. In fact, reading the response from Tim Mallalieu (Group Program Manager of Data Explorer) to the comment made to his post, I had a positive response to my first impression:

“…we originally thinking of ourselves as Self-Service ETL. As we talked to more folks and started partnering with other teams we realized that would be an area that we can add value but that there were more opportunities emerging.”

The typical operations of the ETL phase ( processing and organization of data in different formats) can be obtained thanks to Data Explorer Mashup. This is an image of the tool:

The flexibility in the manipulation of information is given by Data Explorer Formula Language. This is a formula-based Excel-style specific language:

Anyone wishing to know more can check the project page in addition to aforementioned blog:

http://www.microsoft.com/en-us/sqlazurelabs/labs/dataexplorer.aspx

In light of this new project, there is no doubt about the intention of Microsoft to get closer and closer to the Power User, providing him flexible and very easy to use tools for data analysis. The prime example of this is PowerPivot.

The question that remains is always the same: having in a company more Power User will implicitly mean having different data models representing the same reality. But this would inevitably lead to anarchical data management... What do you think about that?

SSMS Tools Pack 2.1.0 is out. Added support for SQL Server 2012 RC0.

Mladen Prajdić — Thu, 01 Dec 2011 19:34:18 GMT

This version adds support for SQL Server 2012 RC0 and fixes a few bugs with SQL History.

Because of the support for regions in SSMS 2012 the regions and debug sections feature has been removed from SSMS Tools Pack for SQL Server 2012.

The feature is still available for previous SSMS versions.

In other news SSMS Tools Pack has won the SQL Magazine bronze award for best free tool of 2011. You can view all the details at the SQL Server Magazine Award page.

Thanx to all the people who voted for it. I'm glad you all like it and use it with great success.

Also I've added a possibility for you to subscribe to email notifications in case the auto-updater doesn't work for you for some reason like being behind a proxy.

Enjoy it!

Speaking in Omaha: December 7, 2011

Bill Graziano — Thu, 01 Dec 2011 16:30:45 GMT

I’m presenting in Omaha on Writing Faster SQL at 6PM on December 7th. You can find meeting details on the Omaha SQL Server User Group page. The meeting location requires an RSVP so building security has a list of attendees.

The presentation is a series of suggestions on improving performance. It ranges from simple things like comparing indexed columns to scalar values up to tips for reducing query compiles and asynchronous processing patterns. Nearly all of these come from specific issues I’ve encountered working on poorly performing SQL Servers.

A Rose by Any Other Name..

Geoff N. Hiten — Tue, 29 Nov 2011 14:45:36 GMT

It is always a good start when you can steal a title line from one of the best writers in the English language. Let’s hope I can make the rest of this post live up to the opening.

One recurring problem with SQL server is moving databases to new servers. Client applications use a variety of ways to resolve SQL Server names, some of which are not changed easily <cough SharePoint /cough>. If you happen to be using default instances on both the source and target SQL Server, then the solution is pretty simple. You create (or bug the network admin until she creates) two DNS “A” records. One points the old name to the new IP address. The other creates a new alias for the old server, since the original system name is now redirected. Note this will redirect ALL traffic from the old server to the new server, including RDP and file share connection attempts.

Figure 1 – Microsoft DNS MMC Snap-In

Figure 2 – DNS New Host Dialog Box

Both records are necessary so you can still access the old server via an alternate name.

Server Role	IP Address	Name	Alias
Source	10.97.230.60	SQL01	SQL01_Old
Target	10.97.230.80	SQL02	SQL01

Table 1 – Alias List

If you or somebody set up connections via IP address, you deserve to have to go to each app and fix it by hand. That is the only way to fix that particular foul-up.

If have to deal with Named Instances either as a source or a target, then it gets more complicated. The standard fix is to use the SQL Server Configuration Manager (or one of its earlier incarnations) to create a SQL client alias to redirect the connection. This can be a pain installing and configuring the app on multiple client servers. The good news is that SQL Server Configuration Manager AND all of its earlier versions simply write a few registry keys. Extracting the keys into a .reg file makes centralized automated deployment a snap.

If the client is a 32-bit system, you have to extract the native key. If it is a 64-bit, you have to extract the native key and the WoW (32 bit on 64 bit host) key.

First, pick a development system to create the actual registry key. If you do this repeatedly, you can simply edit an existing registry file. Create the entry using the SQL Configuration Manager. You must use a 64-bit system to create the WoW key. The following example redirects from a named instance “SQL01\SQLUtiluty” to a default instance on “SQL02”.

Figure 3 – SQL Server Configuration Manager - Native

Figure 3 shows the native key listing.

Figure 4 – SQL Server Configuration Manager – WoW

If you think you don’t need the WoW key because your app is 64 it, think again. SQL Server Management Server is a 32-bit app, as are most SQL test utilities. Always create both keys for 64-bit target systems.

Now that the keys exist, we can extract them into a .reg file. Fire up REGEDIT and browse to the following location: HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo. You can also search the registry for the string value of one of the server names (old or new).

Right click on the “ConnectTo” label and choose “Export”. Save with an appropriate name and location. The resulting file should look something like this:

Figure 5 – SQL01_Alias.reg

Repeat the process with the location: HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

Note that if you have multiple alias entries, ALL of the entries will be exported. In that case, you can edit the file and remove the extra aliases.

You can edit the files together into a single file. Just leave a blank line between new keys like this:

Figure 6 – SQL01_Alias_All.reg

Of course if you have an automatic way to deploy, it makes sense to have an automatic way to Un-deploy. To delete a registry key, simply edit the .reg file and replace the target with a “-“ sign like so.

Figure 7 – SQL01_Alias_UNDO.reg

Now we have the ability to move any database to any server without having to install or change any applications on any client server. The whole process should be transparent to the applications, which makes planning and coordinating database moves a far simpler task.

PASS Summit 2011 – Part IV

Tara Kizer — Fri, 11 Nov 2011 22:04:26 GMT

This is the final blog for my PASS Summit 2011 series. Well okay, a mini-series, I guess.

On the last day of the conference, I attended Keith Elmore’ and Boris Baryshnikov’s (both from Microsoft) “Introducing the Microsoft SQL Server Code Named “Denali” Performance Dashboard Reports, Jeremiah Peschka’s (blog|twitter) “Rewrite your T-SQL for Great Good!”, and Kimberly Tripp’s (blog|twitter) “Isolated Disasters in VLDBs”.

Keith and Boris talked about the lifecycle of a session, figuring out the running time and the waiting time. They pointed out the transient nature of the reports. You could be drilling into it to uncover a problem, but the session may have ended by the time you’ve drilled all of the way down. Also, the reports are for troubleshooting live problems and not historical ones. You can use Management Data Warehouse for historical troubleshooting. The reports provide similar benefits to the Activity Monitor, however Activity Monitor doesn’t provide context sensitive drill through.

One thing I learned in Keith’s and Boris’ session was that the buffer cache hit ratio should really never be below 87% due to the read-ahead mechanism in SQL Server. When a page is read, it will read the entire extent. So for every page read, you get 7 more read. If you need any of those 7 extra pages, well they are already in cache.

I had a lot of fun in Jeremiah’s session about refactoring code plus I learned a lot. His slides were visually presented in a fun way, which just made for a more upbeat presentation. Jeremiah says that before you start refactoring, you should look at your system. Investigate missing or too many indexes, out-of-date statistics, and other areas that could be leading to your code running slow. He talked about code standards. He suggested using common abbreviations for aliases instead of one-letter aliases. I’m a big offender of one-letter aliases, but he makes a good point. He said that join order does not matter to the optimizer, but it does matter to those who have to read your code. Now let’s get into refactoring!

Eliminate useless things – useless/unneeded joins and columns. If you don’t need it, get rid of it!
Instead of using DISTINCT/JOIN, replace with EXISTS
Simplify your conditions; use UNION or better yet UNION ALL instead of OR to avoid a scan and use indexes for each union query
Branching logic – instead of IF this, IF that, and on and on…use dynamic SQL (sp_executesql, please!) or use a parameterized query in the application
Correlated subqueries – YUCK! Replace with a join
Eliminate repeated patterns

Last, but certainly not least, was Kimberly’s session. Kimberly is my favorite speaker. I attended her two-day pre-conference seminar at PASS Summit 2005 as well as a SQL Immersion Event last December. Did I mention she’s my favorite speaker? Okay, enough of that.

Kimberly’s session was packed with demos. I had seen some of it in the SQL Immersion Event, but it was very nice to get a refresher on these, especially since I’ve got a VLDB with some growing pains. One key takeaway from her session is the idea to use a log shipping solution with a load delay, such as 6, 8, or 24 hours behind the primary. In the case of say an accidentally dropped table in a VLDB, we could retrieve it from the secondary database rather than waiting an eternity for a restore to complete. Kimberly let us know that in SQL Server 2012 (it finally has a name!), online rebuilds are supported even if there are LOB columns in your table. This will simplify custom code that intelligently figures out if an online rebuild is possible.

There was actually one last time slot for sessions that day, but I had an airplane to catch and my kids to see!

PASS Summit 2011 – Part III

Tara Kizer — Fri, 11 Nov 2011 20:22:14 GMT

Well we’re about a month past PASS Summit 2011, and yet I haven’t finished blogging my notes! Between work and home life, I haven’t been able to come up for air in a bit. Now on to my notes…

On Thursday of the PASS Summit 2011, I attended Klaus Aschenbrenner’s (blog|twitter) “Advanced SQL Server 2008 Troubleshooting”, Joe Webb’s (blog|twitter) “SQL Server Locking & Blocking Made Simple”, Kalen Delaney’s (blog|twitter) “What Happened? Exploring the Plan Cache”, and Paul Randal’s (blog|twitter) “More DBA Mythbusters”. I think my head grew two times in size from the Thursday sessions. Just WOW!

I took a ton of notes in Klaus' session. He took a deep dive into how to troubleshoot performance problems. Here is how he goes about solving a performance problem:

Start by checking the wait stats DMV
System health
Memory issues
I/O issues

I normally start with blocking and then hit the wait stats. Here’s the wait stat query (Paul Randal’s) that I use when working on a performance problem. He highlighted a few waits to be aware of such as WRITELOG (indicates IO subsystem problem), SOS_SCHEDULER_YIELD (indicates CPU problem), and PAGEIOLATCH_XX (indicates an IO subsystem problem or a buffer pool problem).

Regarding memory issues, Klaus recommended that as a bare minimum, one should set the “max server memory (MB)” in sp_configure to 2GB or 10% reserved for the OS (whichever comes first). This is just a starting point though!

Regarding I/O issues, Klaus talked about disk partition alignment, which can improve SQL I/O performance by up to 100%. You should use 64kb for NTFS cluster, and it’s automatic in Windows 2008 R2.

Joe’s locking and blocking presentation was a good session to really clear up the fog in my mind about locking. One takeaway that I had no idea could be done was that you can set a timeout in T-SQL code view LOCK_TIMEOUT. If you do this via the application, you should trap error 1222.

Kalen’s session went into execution plans. The minimum size of a plan is 24k. This adds up fast especially if you have a lot of plans that don’t get reused much. You can use sys.dm_exec_cached_plans to check how often a plan is being reused by checking the usecounts column. She said that we can use DBCC FLUSHPROCINDB to clear out the stored procedure cache for a specific database. I didn’t know we had this available, so this was great to hear. This will be less intrusive when an emergency comes up where I’ve needed to run DBCC FREEPROCCACHE.

Kalen said one should enable “optimize for ad hoc workloads” if you have an adhoc loc. This stores only a 300-byte stub of the first plan, and if it gets run again, it’ll store the whole thing. This helps with plan cache bloat.

I have a lot of systems that use prepared statements, and Kalen says we simulate those calls by using sp_executesql. Cool!

Paul did a series of posts last year to debunk various myths and misconceptions around SQL Server. He continues to debunk things via “DBA Mythbusters”. You can get a PDF of a bunch of these here. One of the myths he went over is the number of tempdb data files that you should have. Back in 2000, the recommendation was to have as many tempdb data files as there are CPU cores on your server. This no longer holds true due to the numerous cores we have on our servers. Paul says you should start out with 1/4 to 1/2 the number of cores and work your way up from there. BUT! Paul likes what Bob Ward (twitter) says on this topic:

8 or less cores –> set number of files equal to the number of cores
Greater than 8 cores –> start with 8 files and increase in blocks of 4

One common myth out there is to set your MAXDOP to 1 for an OLTP workload with high CXPACKET waits. Instead of that, dig deeper first. Look for missing indexes, out-of-date statistics, increase the “cost threshold for parallelism” setting, and perhaps set MAXDOP at the query level.

Paul stressed that you should not plan a backup strategy but instead plan a restore strategy. What are your recoverability requirements? Once you know that, now plan out your backups.

As Paul always does, he talked about DBCC CHECKDB. He said how fabulous it is. I didn’t want to interrupt the presentation, so after his session had ended, I asked Paul about the need to run DBCC CHECKDB on your mirror systems. You could have data corruption occur at the mirror and not at the principal server. If you aren’t checking for data corruption on your mirror systems, you could be failing over to a corrupt database in the case of a disaster or even a planned failover. You can’t run DBCC CHECKDB against the mirrored database, but you can run it against a snapshot off the mirrored database.

A new version of SQL Treeo released

Luca Zavarella — Wed, 09 Nov 2011 08:34:40 GMT

A new SQL Treeo update is available at http://www.sqltreeo.com.

Lot of bugs fixed so now it seems to be a stable add-on for SSMS.

The full change log is available here:

http://www.sqltreeo.com/wp/new-version-of-sql-treeo-ssms-productivity-add-in-was-released/

So, update your SSMS clients :)

Virtual Lab part 2–Templates, Patterns, Baselines

Geoff N. Hiten — Tue, 01 Nov 2011 16:42:45 GMT

Once you have a good virtualization platform chosen, whether it is a desktop, server or laptop environment, the temptation is to build “X”. “X” may be a SharePoint lab, a Virtual Cluster, an AD test environment or some other cool project that you really need RIGHT NOW. That would be doing it wrong.

My grandfather taught woodworking and cabinetmaking for twenty-seven years at a trade school in Alabama. He was the first instructor hired at that school and the only teacher for the first two years. His students built tables, chairs, and workbenches so the school could start its HVAC courses. Visiting as a child, I also noticed many extra “helper” stands, benches, holders, and gadgets all built from wood.

What does that have to do with a virtual lab, you ask? Well, that is the same approach you should take. Build stuff that you will use. Not for solving a particular problem, but to let the Virtual Lab be part of your normal troubleshooting toolkit.

Start with basic copies of various Operating Systems. Load and patch server and desktop OS environments. This also helps build your collection of ISO files, another essential element of a virtual Lab. Once you have these “baseline” images, you can use your Virtualization software’s snapshot capability to freeze the image. Clone the snapshot and you have a brand new fully patched machine in mere moments. You may have to sysprep some of the Microsoft OS environments if you are going to create a domain environment or experiment with clustering. That is still much faster than loading and patching from scratch.

So once you have a stock of raw materials (baseline images in this case) where should you start. Again, my grandfather’s workshop gives us the answer. In the shop it was workbenches and tables to hold large workpieces that made the equipment more useful. In a Windows environment the same role falls to the fundamental network services: DHCP, DNS, Active Directory, Routing, File Services, and Storage services. Plan your internal network setup. Build out an AD controller with all the features listed. Make the actual domain an isolated domain so it will not care about where you take it. Add the Microsoft iSCSI target. Once you have this single system, you can leverage it for almost any network environment beyond a simple stand-alone system.

Having these templates and fundamental infrastructure elements ready to run means I can build a quick lab in minutes instead of hours. My solutions are well-tested, my processes fully documented with screenshots, and my plans validated well before I have to make any changes to client systems. the work I put in is easily returned in increased value and client satisfaction.

SQL 2012 Licensing Thoughts

Geoff N. Hiten — Thu, 03 Nov 2011 16:30:23 GMT

The only thing more controversial than new Federal Tax plans is new Licensing plans from Microsoft. In both cases, everyone calculates several numbers.

First, will I pay more or less under this plan?
Second, will my competition pay more or less than now?
Third, will <insert interesting person/company here> pay more or less?

Not that items 2 and 3 are meaningful, that is just how people think.

Much like tax plans, the devil is in the details, so lets see how this looks. Microsoft shows it here: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx

First up is a switch from per-socket to per-core licensing. Anyone who didn’t see something like this coming should rapidly search for a new line of work because you are not paying attention. The explosion of multi-core processors has made SQL Server a bargain. Microsoft is in business to make money and the old per-socket model was not going to do that going forward.

Per-core licensing also simplifies virtualization licensing. Physical Core = Virtual Core, at least for licensing. Oversubscribe your processors, that’s your lookout. You still pay for what is exposed to the VM. The cool part is you can seamlessly move physical and virtual workloads around and the licenses follow. The catch is you have to have Software Assurance to make the licenses mobile. Nice touch there.

Let’s have a moment of silence for the late, unlamented, largely ignored Workgroup Edition. To quote the Microsoft FAQ: “Standard becomes our sole edition for basic database needs”. Considering I haven’t encountered a singe instance of SQL Server Workgroup Edition in the wild, I don’t think this will be all that controversial.

As for pricing, it looks like a wash with current per-socket pricing based on four core sockets. Interestingly, that is the minimum core count Microsoft proposes to swap to transition per-socket to per-core if you are on Software Assurance. Reading the fine print shows that if you are using more, you will get more core licenses:

From the licensing FAQ.

15. How do I migrate from processor licenses to core licenses? What is the migration path?

Licenses purchased with Software Assurance (SA) will upgrade to SQL Server 2012 at no additional cost. EA/EAP customers can continue buying processor licenses until your next renewal after June 30, 2012. At that time, processor licenses will be exchanged for core-based licenses sufficient to cover the cores in use by processor-licensed databases (minimum of 4 cores per processor for Standard and Enterprise, and minimum of 8 EE cores per processor for Datacenter).

Looks like the folks who invested in the AMD 12-core chips will make out like bandits.

Now, on to something new: SQL Server Business Intelligence Edition. Yep, finally a BI-specific SKU licensed for server+CAL configurations only. Note that Enterprise Edition still supports the complete feature set; the BI Edition is intended for smaller shops who want to use the full BI feature set but without needing Enterprise Edition scale (or costs). No, you don’t get ColumnStore, Compression, or Partitioning in the BI Edition. Those are Enterprise scale features, ThankYouVeryMuch. Then again, your starting licensing costs are about one sixth of an Enterprise Edition system (based on an 8 core server).

The only part of the message I am missing is if the current Failover Licensing Policy will change. Do we need to fully or partially license failover servers? That is a detail I definitely want to know.

SQL Server Developer Tools – Codename Juneau vs. Red-Gate SQL Source Control

Ajarn Mark Caldwell — Wed, 26 Oct 2011 06:14:11 GMT

So how do the new SQL Server Developer Tools (previously code-named Juneau) stack up against SQL Source Control? Read on to find out.

At the PASS Community Summit a couple of weeks ago, it was announced that the previously code-named Juneau software would be released under the name of SQL Server Developer Tools with the release of SQL Server 2012. This replacement for Database Projects in Visual Studio (also known in a former life as Data Dude) has some great new features. I won’t attempt to describe them all here, but I will applaud Microsoft for making major improvements. One of my favorite changes is the way database elements are broken down. Previously every little thing was in its own file. For example, indexes were each in their own file. I always hated that. Now, SSDT uses a pattern similar to Red-Gate’s and puts the indexes and keys into the same file as the overall table definition.

Of course there are really cool features to keep your database model in sync with the actual source scripts, and the rename refactoring feature is now touted as being more than just a search and replace, but rather a “semantic-aware” search and replace. Funny, it reminds me of SQL Prompt’s Smart Rename feature. But I’m not writing this just to criticize Microsoft and argue that they are late to the party with this feature set. Instead, I do see it as a viable alternative for folks who want all of their source code to be version controlled, but there are a couple of key trade-offs that you need to know about when you choose which tool set to use.

First, the basics

Both tool sets integrate with a wide variety of source control systems including the most popular: Subversion, GIT, Vault, and Team Foundation Server. Both tools have integrated functionality to produce objects to upgrade your target database when you are ready (DACPACs in SSDT, integration with SQL Compare for SQL Source Control). If you regularly live in Visual Studio or the Business Intelligence Development Studio (BIDS) then SSDT will likely be comfortable for you. Like BIDS, SSDT is a Visual Studio Project Type that comes with SQL Server, and if you don’t already have Visual Studio installed, it will install the shell for you. If you already have Visual Studio 2010 installed, then it will just add this as an available project type. On the other hand, if you regularly live in SQL Server Management Studio (SSMS) then you will really enjoy the SQL Source Control integration from within SSMS. Both tool sets store their database model in script files. In SSDT, these are on your file system like other source files; in SQL Source Control, these are stored in the folder structure in your source control system, and you can always GET them to your file system if you want to browse them directly.

For me, the key differentiating factors are 1) a single, unified check-in, and 2) migration scripts. How you value those two features will likely make your decision for you.

Unified Check-In

If you do a continuous-integration (CI) style of development that triggers an automated build with unit testing on every check-in of source code, and you use Visual Studio for the rest of your development, then you will want to really consider SSDT. Because it is just another project in Visual Studio, it can be added to your existing Solution, and you can then do a complete, or unified single check-in of all changes whether they are application or database changes. This is simply not possible with SQL Source Control because it is in a different development tool (SSMS instead of Visual Studio) and there is no way to do one unified check-in between the two. You CAN do really fast back-to-back check-ins, but there is the possibility that the automated build that is triggered from the first check-in will cause your unit tests to fail and the CI tool to report that you broke the build. Of course, the automated build that is triggered from the second check-in which contains the “other half” of your changes should pass and so the amount of time that the build was broken may be very, very short, but if that is very, very important to you, then SQL Source Control just won’t work; you’ll have to use SSDT.

Refactoring and Migrations

If you work on a mature system, or on a not-so-mature but also not-so-well-designed system, where you want to refactor the database schema as you go along, but you can’t have data suddenly disappearing from your target system, then you’ll probably want to go with SQL Source Control. As I wrote previously, there are a number of changes which you can make to your database that the comparison tools (both from Microsoft and Red Gate) simply cannot handle without the possibility (or probability) of data loss. Currently, SSDT only offers you the ability to inject PRE and POST custom deployment scripts. There is no way to insert your own script in the middle to override the default behavior of the tool. In version 3.0 of SQL Source Control (Early Access version now available) you have that ability to create your own custom migration script to take the place of the commands that the tool would have done, and ensure the preservation of your data. Or, even if the default tool behavior would have worked, but you simply know a better way then you can take control and do things your way instead of theirs.

You Decide

In the environment I work in, our automated builds are not triggered off of check-ins, but off of the clock (currently once per night) and so there is no point at which the automated build and unit tests will be triggered without having both sides of the development effort already checked-in. Therefore having a unified check-in, while handy, is not critical for us. As for migration scripts, these are critically important to us. We do a lot of new development on systems that have already been in production for years, and it is not uncommon for us to need to do a refactoring of the database. Because of the maturity of the existing system, that often involves data migrations or other additional SQL tasks that the comparison tools just can’t detect on their own. Therefore, the ability to create a custom migration script to override the tool’s default behavior is very important to us. And so, you can see why we will continue to use Red Gate SQL Source Control for the foreseeable future.