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

Eighteen Days Until TechEd Developers North America 2008!

Ward Pond — Fri, 16 May 2008 19:45:31 GMT

This afternoon, I attended a briefing for TechEd North America 2008 presenters and staff, and yesterday I got my booth schedule for the event. I've got my Hands-On Lab for SQL Tricks finished, and I was happily surprised to hear earlier this week...(read more)

Career News: Goin' Mobile

Ward Pond — Fri, 16 May 2008 18:00:00 GMT

I'm goin' home And when I wanna go home I'm goin' mobile Well, I'm gonna find a home And we'll see how it feels Goin' mobile Keep me movin' - Goin' Mobile , Pete Townsend ( The Who ) Frequent readers of this space will know that...(read more)

Log Buffer #97: a Carnival of the Vanities for DBAs

David Edwards — Fri, 16 May 2008 13:28:19 GMT

The 97th edition of Log Buffer, the weekly review of database blogs, has been published on Brian “Krow” Aker’s Idle Thoughts. We have Jeff Smith and Ward Pond standing by for two upcoming editions. And if you’d like to contribute, make yourself known in the DBA community-at-large (and have some fun in the process), you [...]

Log Buffer #97: Coming Soon

David Edwards — Fri, 16 May 2008 13:08:27 GMT

Log Buffer #97 is unaccountably delayed, but have no fear — it will appear before too long. Stay tuned. Bookmark online using:These icons link to social bookmarking sites where readers can share and discover new web pages.

SQL Server 2008 Change Tracking and Sync Services ARE made for each other... in VS2008 SP1

Fri, 16 May 2008 12:12:04 GMT

This item was mentioned in other blogs too, but just to mention it again (so my SQLCE and Sync Services friends won't think I overlooked it)...

Visual Studio 2008 SP1 Beta contains direct support for using SQL Server 2008 Change Tracking. When you use ADO.NET 1.0 Sync Services with a SQL Server 2008 database, the Sync Services designer (that's Add/New Item/Local Database Data Cache) adds a checkbox that allows you simply to "Use SQL Server Change Tracking". No extra triggers, no tombstone tables, change tracking does it all for you. Visual Studio generates some scripts to enable change tracking at a database level and at a table level for the tables you select.

There's some nice generated code that warns you if you need to resync because you haven't synchronized often enough. And you can tweak the SQL scripts for more control. You can specify a CHANGE_RETENTION (the default is 2 days) and whether or not AUTO_CLEANUP is ON (it's on by default) in your ALTER DATABASE statement. And if you want to write your own, more granular synchronization or conflict resolution code, you can specify WITH TRACK_COLUMNS_UPDATED = ON (default is OFF).

Something that puzzled me was, although the SQL Server Books Online states: "Using snapshot isolation is the easiest way for you to help ensure that all change tracking information is consistent. For this reason, we strongly recommend that snapshot isolation be set to ON for the database.", the ALTER DATABASE DDL generated by Visual Studio did not include the option to SET ALLOW_SNAPSHOT_ISOLATION ON. Enabling snapshot isolation in SQL Server does involve a non-trivial amount of overhead, so it looks like they set it to the least overhead option and let you decide if you need snapshot isolation after reading the arguments/scenarios where it's useful in Books Online. Probably a wise choice.

Synchronization Framework is an exciting technology, given that there's so many possible uses of synchronization with "Local Data Caches" and, with the support for Sync Services in mobile devices. The next release of the Synchronization Framework (2.0, there's a beta out already) even contains support for Peer-to-Peer synchronization through the model. Perhaps this will fit some additional "replication via services" in databases. We'll have to wait and see if the Peer-to-Peer sync provider even supports databases, I guess. Always something interesting going on...

This blog is sponsored by SQLSkills.

SQL Server 2008 data types in LINQ to SQL and EF, they (mostly) did it

Fri, 16 May 2008 11:43:03 GMT

Last fall I'd asked the folks on the data access team about the possibility of including the new SQL Server 2008 data types in the new data access object mapping layers. At that time they said "no" but they also asked "why do you want it?" and "are people expecting it?". Someone must have had some compelling arguments.

I'm sure you've read this in other blogs, but in Visual Studio 2008 SP1 Beta, which arrived Monday, there was...drumroll please...support for all four date and time types in not only LINQ to SQL, but in ADO.NET Entity Framework. Thanks! I'm still a bit surprised about the inclusion of DateTimeOffset in EF (its a SQL Server-specific data type and EF is more platform-neutral than LINQ to SQL), but it was a cool surprise to have them there.

Everyone's blog entry also points out there is some special support for Filestream storage (including a System.Data.Types.SqlFilestream class), but they identify Filestream as a data type. Filestream is a storage format rather than a data type, the actual data type is varbinary(max). Minor terminology nit. You can read and write FILESTREAM based types without resorting to PInvoke. Cool.

Of course, there were some SQL Server 2008 data types (the SQLCLR-based types) that aren't "in there". These are the spatial types (Geometry and Geography) and the HierarchyID data type. Perhaps for EF/LINQ to SQL version 2. These types are correctly identified in Server Explorer in a table but attempting to add a table containing them to an EF diagram fails (it actually leaves the type out of the diagram) and in LINQ to SQL throws an error "the data type is not supported".

That means if you have one of these types in your SQL Server 2008 database, you need to do something funky like treat it as a varbinary(max) and deserialize it (think IBinarySerialize) on the client yourself. Because the SQLCLR-based data types would have a one-to-one mapping to .NET classes (Geometry = Microsoft.SqlServer.Types.SqlGeometry, Geography = SqlGeography, HierarchyID = SqlHierarchyID) these would possibly be straightforward to implement in the next release. They WOULD be a potential model-breaker in that you would have "columns" (entity properties) that have their own properties and methods. Another whole level of indirection.

I've said before that IMHO, spatial support is the "killer developer feature" in SQL Server 2008. Let's hope for more API support in future. But THANKS for the inclusion of the date and time types.

This blog is sponsored by SQLSkills.

First blog entry in a month...what's up

Fri, 16 May 2008 11:20:03 GMT

After posting the last blog entry, I realized that I hadn't blogged in about a month. Hmmmm...

Well, since last month I posted the demos for my SQLConnections talks on the SQLskills website (under PastConferences). I really had a good time at SQLConnections, its been a while since I'd been invited to speak there. Thanks to those folks who showed up and said hi.

I've been updating my classes for SQL Server 2008 while waiting for the next CTP. Don't know when that's coming, but everyone's guess is "sometime in June". And I've found a few more neat things in SQL Server 2008 that I'll get around to writing about some day soon. And did some experimenting with the Visual Studio 2008 SP1 Beta (see previous post).

And I've done a lot of yardwork and gardening. I've been outside almost every day; might even lose that pastey-white "programmer who never sees the sun" look. But its 97 degrees in Portland today (yes, 97, really) and its a bit hot for gardening.

Next stop...TechEd Developer Week in June. I'm doing a precon there on SQL Server 2008 and a couple of other interesting talks. Wonder if it will be 97 degrees in Orlando... If you're in Orlando stop by and say hi.

This blog is sponsored by SQLSkills.

Decent Overview of Policy-Based Management

dtjones — Fri, 16 May 2008 11:09:39 GMT

This article is a decent overview of Policy-Based Management.

This blog will move

WesleyB — Fri, 16 May 2008 10:44:00 GMT

For reasons that may be clear from the address this blog will move to http://blogs.msdn.com/wesleyb/.
I hope you will all continue to read my blog on this new address!

Trace Events in ADO.NET Entity Framework

Fri, 16 May 2008 10:37:03 GMT

Every once in a while I'll get inquires on a paper I wrote in 2004 about the ETW trace providers for ADO.NET (named "Tracing Data Access"). I got an inquiry today, and since I'd recently installed Visual Studio 2008 SP1 Beta, I thought I'd try it out on a LINQ to SQL program and an Entity Framework program.

If you haven't read the paper, the original is still available on MSDN, but an embellished version (including SNAC tracing in addition to ADO.NET) was released in 2006. I'm also doing a talk at TechEd on "End-to-End tracing in ADO.NET and SQL Server 2008". If you're at all interested in client tracing or SQL Server 2008 Extended Events, it will be worth your while to attend.

Unless the LINQ to SQL program has its own ETW provider that I missed, I got no LINQ to SQL specific trace events. Since LINQ to SQL is a thin layer over System.Data.SqlClient, I do get the events for SqlClient however.

Entity Framework however, is another matter. I not only get EF-specific trace events, but there appears to be some new trace namespace abbreviations for EF as well. These are the ones I've seen while going through one trace of one simple EF program. I may have missed some.

dobj = System.Data.Objects
ec = System.Data.EntityClient
cqt = System.Data.Common.CommandTrees
pc = System.Data.Query.PlanCompiler

There's quite a bit of trace information for some parts of EF, like the CommandTree portion. But after a whole half-hour and a few traces, it appears that not all items in EF are as thoroughly instrumented with trace events as, say, SqlClient. On the other hand, EF provider for SQL Server will show lots of SqlClient-specific and System.Data.Common events, as does LINQ to SQL.

Another interesting thing I discovered from running the trace. It appears that EF is using the ADO.NET DataSet in some way, perhaps (wild guess) for entity tracking and change tracking. The way I came to this conclusion is that, I have a simple console app that produced no DataSets, but my trace shows lots of DataSet activity. Perhaps this is common knowledge, but it was a bit of a surprise to me.

This blog is sponsored by SQLSkills.

WinFS lives!

jamie.thomson — Fri, 16 May 2008 07:16:00 GMT

I listen to a lot of podcast series these days (a great way of filling up my one hour train commute) and I've recently subscribed to Perspectives with Jon Udell.

This morning I listened to his latest one Where is WinFS now? which featured an interview with Quentin Clark from the SQL Server team. Quentin was prominent on the supposedly ill-fated WinFS project but in actual fact he explains how a lot of the technologies developed as part of WinFS have manifested themselves in released technologies today. Entity Framework, HierarchyID data type & Filestream are all set to be released in the next three or four months and all were incubated in WinFS.

Well worth a listen if you like understanding how and where technologies originate.

-Jamie

Viable tools for Service Broker

remus — Fri, 16 May 2008 06:05:39 GMT

Technically Service Broker can offer a huge value over any other comparable technology, but every project faces in the early stages the same barrier: the extremely steep learning curve faced by developers and dba in order deploy a Service Broker solution. As the adopters try to set up a simple scenario to ‘play’ with the [...]

SQL Server Statistics

robcarrol — Fri, 16 May 2008 03:00:00 GMT

b Cross Posted from blogs.technet.com/rob...(read more)

Query Processing Presentation

craigfr — Thu, 15 May 2008 12:39:00 GMT

Last week, I had the opportunity to talk to the New England SQL Server Users Group. I would like to thank the group for inviting me, Adam Machanic for organizing the event, and Red Gate for sponsoring it. My talk was an introduction to query processing, query execution, and query plans in SQL Server. I've had a request for the slides, so here they are. Enjoy!

SQL Server and Windows Memory

robcarrol — Thu, 15 May 2008 10:02:00 GMT

SQL Server 2000 Enterprise Edition introduced support for the use of Windows 2000 Address Windowing Extensions (AWE) to address 8GB of memory on Windows 2000 Advanced Server and 32GB of memory on Windows 2000 Datacentre. With AWE, SQL Server can reserve memory not in use by other applications and the OS. Each instance must statically allocate the memory it needs. AWE memory can only be used for the data cache and not for executables, drivers, DLL's, etc.

Cross Posted from blogs.technet.com/rob

Windows Memory (PAE/AWE/3GB)

robcarrol — Thu, 15 May 2008 08:20:00 GMT

SQL Server 2000 Enterrise Edition introduced support for the use of Windows

Cross Posted from blogs.technet.com/rob

SQL Server multiple instances and resource utilization - best practices

ssqa.net — Thu, 15 May 2008 05:48:00 GMT

A newbie or FAQ within Forums and Newsgroups that having multiple instances and how best to set the resources on the server such as memory, CPU etc. Should you configure each of the instances to use specific portions of the available Operating System resources then best to leave the default settings, for instance dynamic memory settings on SQL Server. As per the configuration of SQL Server you can easily mix versions of SQL Server (both 2000 and 2005) on the same machine running Windows server, such...(read more)

SQL Server Performance issues with Fragmentation and heavy usage of TEMPDB?

SQL Master — Thu, 15 May 2008 04:59:00 GMT

Whenever a performance issue occurs on the SQL Server database best option for diagnosing and troubleshooting common problems by using publicly available tools such as Profiler, System Monitor (Perfmon), and Dynamic Management Views (DMVs) in SQL Server...(read more)

SQLTeach - Writing better queries presentation

Joe Webb — Thu, 15 May 2008 03:37:38 GMT

Here's a presentation that I gave yesterday afternoon at DevTeach/SQLTeach. It's called Tips & Tricks for Writing Better Queries. It's very similar to the presentation I gave a month or so ago at the Nashville SQL Server User Group meeting.

To everyone who attended the session - thanks for spending a part of your day with me. I hope it was worth your while. Please feel free to email me if you have any additional questions or comments. And please remember to login to your SQLTeach account and submit your session evals.

Cheers!

Joe

How hard is it to pick the right non-clustered indexes?

Wed, 14 May 2008 11:13:04 GMT

Kimberly and I were presenting at our local (Redmond) .Net Developers Association on Monday and the following question came up while Kimberly was talking about missing and extra indexes (paraphrasing):

What's the best non-clustered index to use for the query with a predicate WHERE lastname = 'Randal' AND firstname = 'Paul' AND middleinitial = 'S'?

Kimberly said that the order of the keys (e.g. lastname, firstname, middleinitial; or middleinitial, lastname, firstname; etc) doesn't matter for this case. I thought about it for a second and then argued, saying that the most selective column should come first. We agreed to discuss with the group at the end, but I thought about it some more and realized (and admitted to the group) that she's right - I should know better than to question Kimberly's knowledge of indexing... :-)

She's right because for a pure equality query using AND for multiple predicates, the Storage Engine will seek straight to the first exactly matching record in the index (and then scan for more matches if it's a non-unique index). It doesn't matter what order the index keys are defined because the Storage Engine is looking for an exact match.

When I started arguing, I was thinking about a phone book, which is ordered by lastname, firstname, middleinitial. You may think that a phone book is ordered that way because lastname is the most selective. Wrong. It's because the lastname is what most people know - it just happens to be the most selective of the three choices. Most SQL geeks should be able to find Kimberly in a phone book by looking for Tripp, Kimberly. But what if it was ordered by middleinital? I'd have no problem finding Kimberly, but how many of you would remember that her middleinitial is L? Probably a few as we both use our middle initials in our public names. What about if it was ordered by middleNAME? Again, no problem for me but who how many other people know her middle name is Lynn?

Then I started thinking about other queries and how they would play into the index choice to answer to the question above. If I also wanted to support a query with the predicate WHERE lastname = 'Randal', then having the left-most index key be anything other than lastname won't work so well. If the key order was firstname, middleinitial, lastname then all the distinct lastname values would be spread through the index rather than being together. The index might still be used to satisfy the query if it's the lowest cost index to use. However, having lastname be the leading key probably wouldn't work very well for a query with a predicate of WHERE firstname = 'Paul' - that argues for having firstname be the left-most index key.

Which should I choose? I probably I can't have both in the same index, so maybe I'd have TWO non-clustered indexes, to support both queries. The answer depends on how often the various queries are used and the trade-off between how much of a performance gain the non-clustered index would provide against the performance drop of having to maintain it during DML operations.

I hear time and again about people adding a non-clustered index for every column in the table, thinking that this will help - and my thinking is that this is wrong because these indexes can only satisfy a query where the only predicate is the column being indexed. I ran this argument past Kimberly and she added that these indexes could also be used if the column is chosen as the most selective in a multi-predicate query, and no other index has a lower cost than that one (a slim chance usually). Even what I though of as a simple case has caveats!

So what's the point of this post? Well, I wanted to show how indexing for one very simple query is pretty straightforward, but as soon as the number of different queries grows, and the query predicates get more complicated, indexing becomes more complex. You really have to know your workload and your data to know which columns are used, in what combinations, and how often - and then it helps to know how indexes are costed and used so that you can make intelligent choices about which indexes to define.

This thought-exercise has really shown me that I didn't know how much I don't know about indexes - I know precisely how they work at the Storage Engine level but not too much about how they're used by the Query Processor. I have new-found respect for Kimberly's indexing expertise. Luckily she's teaching a class at Microsoft called Indexing For Performance next week - I think I'll attend :-)

This weblog is sponsored by SQLskills.com. (c) Paul S. Randal. This feed is for personal, non-commercial use.

SQL Injection

Peter Larsson — Wed, 14 May 2008 05:14:47 GMT

Every now and then I see sites where commands are concatenated and sent to database server.
The author must really trust the user inputs!

For every system built this way, you can expect at least one attack with SQL injection. In some cases you might not be aware of the attack, and sometimes you are aware.

Here is an example of a "friendly" attack, that just promotes a site and when you click the link you execute a javascript who knows do what?

In this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102737
and this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101673

there are examples of SQL injection attacks.

As I wrote in the first topic, "What if the attack could have encrypted all columns!".
That would be easy spotted in front-end application.

But what if the attack had scrambled all date columns? How long time would it then take to discover the SQL Injection attack?

I hope this learns all newbies, noobs and beginners to NEVER EVER concatenate string to send to database.
Always use parametrized queries as a first line of defence.

Identifying Performance Bottlenecks

Joe Webb — Wed, 14 May 2008 04:47:51 GMT

Thanks to everyone who attended my "Identifying Performance Bottlenecks" session this morning at DevTeach/SQLTeach. As promised, here is the PowerPoint slide deck I used during the presentation.

Regarding the question of on the performance of LINQ, Bob Beauchemin recently posted a series of blogs entries on the topic. Here's a link.

And here's a link to series of posts on the Dynamic Management Views (DMVs) that Louis Davidson authored.

Cheers!

Joe

Full-time MCTs...Less Value?

Derek Comingore — Tue, 13 May 2008 22:51:24 GMT

Do you find that fulltime MCTs (and other teachers) are somewhat limited in their value to students? To me...all teachers should be practicing in the REAL WORLD what they are educating others on! Now before I get any further I can already hear the comments..."what about demos/labs"...Demos & Labs are HARDLY REAL WORLD! In college, I had much the same thoughts..."why is this guy teaching me if he’s so good and not out there DOING IT"?

Personally, I enjoy teaching and even more so I enjoy seeing others learn, but if I only lived in academia my value to any potential students/attendees would be more limited! Why?

1. I could not relate the technology as well to real world uses and labs/demos

2. I could not relate to students/attendees when discussing certain pros/cons of architectures or given technologies/platforms.

3. The bulk (if not all) of my material would be based on others writings and/or probably a limited amount of my own research.

I am by no means saying fulltime MCTs and other teachers do not provide value but when compared to someone who does the actual job in the real world (assuming the real world teacher also has comparable teaching skills) there is NO COMPARISON! And I suppose that is the pro of being a fulltime teacher, your presentation etc. skills should be much better honed but look at the cost!

That’s my 2

Derek

The Architecture Layer

Sheeri Cabral — Tue, 13 May 2008 10:46:03 GMT

Contemporary software engineering models include many loosely-defined layers. Database developers might help with other layers, but for the most part a database administrator’s domain is the persistence layer. Presentation Application Business Logic Persistence (also called Storage) The Daily WTF has an article on The Mythical Business Layer makes the case for not separating the business layer and [...]

Lightning fast collapsed date ranges and missing date ranges

Peter Larsson — Tue, 13 May 2008 10:16:41 GMT

The last two days I have been involved in a rather interesting discussion.

The original poster wanted a fast way to get missing date ranges in a series of date pairs.
Naturally I posted the link to the Script Library topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88422
Traditional T-SQL proved to be very inefficient. Even when using the CTE approch which proved to be second fastest and still 100 to 1,600 times slower!

I started out with creating 1,000 date pairs with following code

-- Prepare sample data
CREATE TABLE #ProcessCellAllocation
              (
                     AllocationID INT IDENTITY(1, 1) NOT NULL,
                     ProcessCell VARCHAR(50) NOT NULL,
                     DateFrom DATETIME NOT NULL,
                     DateTo DATETIME,
                     Seq INT
              )

INSERT        #ProcessCellAllocation
                (
                     ProcessCell,
                     DateFrom
                )
SELECT        TOP 10000
              CHAR(65 + ABS(CHECKSUM(NEWID())) % 26),
              25000 + ABS(CHECKSUM(NEWID())) % 25000
FROM          syscolumns AS c1
CROSS JOIN    syscolumns AS c2

UPDATE #ProcessCellAllocation
SET    DateTo = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 30, DateFrom)

CREATE CLUSTERED INDEX IX_DateFrom ON #ProcessCellAllocation (ProcessCell, DateFrom, DateTo) WITH FILLFACTOR = 95

The various techniques used to produce the wanted results were very inefficient.
Ultimately I come up with this idea, which produced both collapsed date ranges and missing date ranges.

I started out with clearing a preexisting Seq columns. I can clear all records or only a certain range of ProcessCells.

UPDATE #ProcessCellAllocation

SET Seq = NULL

WHERE ProcessCell BETWEEN 'A' AND 'F'

I then initialize some variables to speed up counting

DECLARE @Seq INT,

@ProcessCell VARCHAR(50),

@DateFrom DATETIME,

@DateTo DATETIME

SELECT TOP 1

@Seq = 0,

@ProcessCell = ProcessCell,

@DateFrom = DateFrom,

@DateTo = DateTo

FROM #ProcessCellAllocation

ORDER BY ProcessCell,

DateFrom

This is only for initializing the documented trick I use, the Clustered Index Update.
The code used for the actual update looks like this

UPDATE #ProcessCellAllocation

SET @Seq = CASE

WHEN DateFrom > @DateTo THEN @Seq + 1

WHEN ProcessCell > @ProcessCell THEN @Seq + 1

ELSE @Seq

END,

@DateFrom = CASE

WHEN DateTo > @DateTo THEN DateFrom

WHEN ProcessCell > @ProcessCell THEN DateFrom

ELSE @DateFrom

END,

@DateTo = CASE

WHEN DateTo > @DateTo THEN DateTo

WHEN ProcessCell > @ProcessCell THEN DateTo

ELSE @DateTo

END,

Seq = @Seq,

@ProcessCell = ProcessCell

Now all work is done, and we can either show the collapgsed date ranges with this

-- Get the collapsed date ranges

SELECT ProcessCell,

MIN(DateFrom) AS DateFrom,

MAX(DateTo) AS DateTo

FROM #ProcessCellAllocation

GROUP BY ProcessCell,

Seq

ORDER BY Seq

Or we can get the missing date ranges with this

-- Get the missing date ranges

SELECT a.ProcessCell,

a.DateFrom,

b.DateTo

FROM (

SELECT ProcessCell,

Seq,

MAX(DateTo) AS DateFrom

FROM #ProcessCellAllocation

GROUP BY ProcessCell,

Seq

) AS a

INNER JOIN (

SELECT ProcessCell,

Seq,

MIN(DateFrom) AS DateTo

FROM #ProcessCellAllocation

GROUP BY ProcessCell,

Seq

) AS b ON b.ProcessCell = a.ProcessCell

WHERE a.Seq = b.Seq - 1

ORDER BY a.Seq

This technique is very fast!

For 1,000 date pairs the algorithm runs in 80 ms.
For 10,000 date pairs the algorithm runs in 360 ms.
For 100,000 date pairs the algorithm runs in 900 ms.
For 1,000,000 date pairs the algorithm runs in 2250 ms.