Search results matching tags 'Documentation' and 'SQL Server 2008'

After the Upgrade, it runs differently…

BuckWoody — Tue, 01 Dec 2009 16:06:55 GMT

I got a question yesterday in the mail that I thought I would just answer here in a broad context. While I can’t troubleshoot or do performance tuning from a distance, there are some interesting concepts and suggestions this e-mail brings up:

“I have recently seen a change from SQL Server from 2005 to 2008 in where it handles CASE statements differently. Previously we saw a tremendous improvement in performance by using CASE statements instead of OR statements. However when one of our client upgraded to 2008 they began to notice unusually long runtimes with a few of these queries (orders of magnitude larger runtimes). Swapping it to an OR statement allows it to run in 0.075 seconds... so my question to you is do you know of any changes to the exectution engine that would account for this and what is your recommendation?”

It’s a great question. Basically it boils down to “I changed versions, now something acts differently.” Before we talk about what might be the issue, let’s talk about some things you should do after you upgrade from one version of SQL Server to another.

First, open the database properties and change the compatibility level to 10.0, unless you know you shouldn’t. Next, update your statistics – all of them. Third, ensure you have all of the proper service packs applied to the operating system and SQL Server. And finally, check the code you have for deprecated statements, or for places where you could optimize the code to use new statements or formats. There are other steps to follow, but these basics will help.

Now, with all of that done, let’s move on to things that work differently. First, you need to find out what the code is doing – and the primary way to do that is to examine the Query Execution Plan. There are a lot of resources to teach you how to do that, but the general idea is that you turn that plan on (in the Query menu), run the query, look at the graphical plan and check three items: the overall plan, the icons that show the highest percentage of use, and the thickest arrows. Evaluating this on the “before” system and the “after” system, and that will show you what changed.

Maybe.

The point is, the query might take exactly the same path, but a different component may show stress because you might have a different box or configuration. Perhaps the drive layouts changed (or should), you have more memory (or better access to it) and so on. In that case, you simply follow standard performance tuning methodologies to locate what’s waiting, and what is showing pressure.

Now to the question at hand – does SQL Server (any version) handle a CASE statement differently than an OR statement? Well, once again, the execution plan will show you that answer, but the CASE statement is used for a different purpose than an OR statement – without having all of the code it’s difficult to say which to use in a given situation. The best thing to do is to evaluate the documentation on each and decide which fits the situation best.

CASE: http://msdn.microsoft.com/en-us/library/ms181765.aspx

OR: http://msdn.microsoft.com/en-us/library/ms188361.aspx

Did we make changes to CASE or OR? Not directly – but each change to the product may have orthogonal implications, which is where I point you back to the steps I mentioned for the “after upgrade” process.

Some updates to SQL Server 2008 SP1 CU4 you may not have noticed

AaronBertrand — Sun, 08 Nov 2009 20:18:00 GMT

Back on September 21/22, Microsoft released its most recent cumulative updates for SQL Server 2008. For RTM, this was Cumulative Update #7 (10.00.1818), and for SP1, this was Cumulative Update #4 (10.00.2734).

However, if you have requested the SP1 CU #4 hotfix download since November 4th, the download page has some new files up there, with "_Updated_Ref_KB_976761" in the name:

When you download these CU4 files, you will end up with a build number (10.00.2740) that is not documented in either of the above KBs~~, and the referenced KB (967671 - currently yielding Bing's version of a 404) has not been published at the time of this writing~~. As Chris pointed out, KB 976761 appears to have been published today. Though, a separate download page has been available all along, in addition to being able to download the new files from the original CU #4 download page.

Notice that the original KB for CU #4 (973602) still says that the version is 10.00.2734:

But then when you download the file with the KB #976761 designation, you get this:

And as expected, when you apply the update to your SQL Server instance, you get @@VERSION = 10.0.2740.0.

My question is: what kind of fixes were so important to quietly sneak these updates onto the CU #4 download page mid-stream? My feeling is these should be separate. The next CU should be coming out within the next several weeks, if they are going to stick to the same ~60 day schedule. So I am curious what drives the priority to push the files to users before they push documentation explaining what the new files are.

I am sure this is just another case of the binary updates preceding the KB updates and other notifications (e.g. from the Release Services blog). Personally, I think that the documentation updates and announcements should come out *before* they post the files; that would cause far less confusion. What makes this scenario difficult is that the download page does not give users enough information about what these files are. So if the 2740 build includes fixes that I don't know how to test, how do I know whether or not that should be one of the files I download? What if I have tested CU #4 (10.00.2734) in my labs, and I tell the servicing team to apply CU #4 to production, and 10.00.2740 is the update they download? This can be quite problematic, I'm sure you'll agree.

This is a relatively isolated situation. But in general, I think the download pages need a lot more supporting documentation. I know the purpose was for self-service, and it is kind of a contradiction that we want more control over what fixes we get without having PSS hold our hands, yet we still want more guidance from PSS. But in my case, I still don't know what a "SQL_Server_2008_SP1_Cumlative_Update_4_RRB2ClickOn" file is, and whether or not I should download it and apply it to my servers. With such a long file name allowed, why make the important part so cryptic? (Naming of the file is obviously a choice made by a human; system-generated code would have spelled "Cumulative" correctly.) Users have had similar questions about files on the download page, such as SNAC and SharePoint... while their naming is a little more intuitive to those of us who have been applying updates to our environments for a while, this isn't true for everyone.

A user on Connect ("ManServ") noticed this yesterday, and filed an item on Connect (#508983). Hopefully I will have commented there by the time you get to it. I realize that if the situation were reversed, we would just be bitching about the opposite thing: "You're talking about build 2740; why can't I download it already!?" While both are equally annoying for PSS, I prefer the latter in terms of end users. At least in that case, they are not downloading the files before there is any documentation around them; maybe they could focus on reading that documentation while waiting for the files to be pushed to the download servers.

I really wish I had noticed this at PASS, instead of right after getting home. I actually had a conversation with Bob Ward during the conference, where he asked me how they can make this servicing model better. The only constructive criticism I could come up with on the spot was that, when they are supporting two branches, and a fix makes it into one CU and not the other, document which fixes are only in one branch, and why. This should be a companion KB article that is linked, in the case of SQL Server 2008, to both the RTM and the SP1 KB articles for the Cumulative Updates released at that time. The way I currently figure this out is to build a table of both sets of fixes, and then figure out where the rows are not equal (you've seen me do this before, here and here). It would be great if I could get that information from the source, however if I were to rank this, it would appear below the problem above, where files make it to end users' systems before there is even a document describing what those files are and what they fix.

Connect Digest : 2009-08-22

AaronBertrand — Sat, 22 Aug 2009 03:18:00 GMT

I rounded up a few interesting items this week.

Does "1/10/1900" mean January 10th, or October 1st?

This item demonstrates that Microsoft isn't all that concerned about publishing sample code that uses ambiguous or troublesome formats for date literals. Initially the Books Online topics mentioned in the item used "1/10/1900" as a date literal, and of course that would have a different meaning in a default US English locale compared with a British locale. The alleged 'fix' was to change the string to "January 1, 1900." Which will simply fail if the user has, for example, SET LANGUAGE FRENCH. It seems funny that they could make a change for the better within a few days, but then making another change (also for the better) cannot be done due to costs. Whereas if they had just changed "1/10/1900" to "19000101" in the first place, the problem would have been solved in one step. What a weird organization they have over there; I personally think that the people writing their documentation samples should be assigned random locales and SET LANGUAGE settings so that they aren't writing code in a nice little US English vacuum. I can imagine a scenario where you have a bank of servers to deploy to, and you have to test on one of them, but you never know which one you're going to get... when your code blows up because the German server with SET LANGUAGE DUTCH doesn't know what "February" means, you have to go back to the drawing board. And I think this would be beneficial for everyone.

#482186 : DOC : Add/Subtract topics should warn about DATE / TIME types

Why can't we have an easier way to get first day of month, of year, etc.?

We currently use clumsy methods to get the first day of the week, month or year (given a DATETIME/SMALLDATETIME value), or to strip the time. While the DATE data type in SQL Server 2008 will relieve some of this pain, it won't help in all cases. Meanwhile, other RDBMS have much more convenient ways to do this (e.g. Oracle's TRUNC()). I am hopeful that someday they will add a function like this to SQL Server; I call it "DATEROUND."

#483913 : Add a DATEROUND function similar to Oracle's TRUNC() for date handling

Why do we allow identifiers with trailing spaces?

While this may not be common practice (notice the trailing spaces in the table and column names)...

CREATE TABLE dbo.[foo ]
(
    [bar ] INT
);

...there are end users complaining about the fact that this works (see Connect #483389). I had a slightly different take than user "aitcha1" in that I don't understand why SQL Server allows trailing spaces in identifier names in the first place. It does ignore trailing spaces when preventing duplicates (for example, you can't have a column named [bar] and a column named [bar ]), but in spite of what the documentation says, it certainly keeps the space intact when storing the metadata in the catalog views. So I filed two suggestions: one to fix the documentation to be more explicit about how "SQL Server stores the name without the trailing spaces," and one to actually tighten up identifier rules.

#483553 : DOC : Delimited Identifiers topic lies about trailing spaces

#483527 : Tighten up identifier rules

Why does SSMS not like VARCHAR(MAX) parameters?

There was an avid discussion on the newsgroups this week about a long-acknowledged bug in SSMS. If you have a stored procedure that has a VARCHAR(MAX) parameter, and you open Object Explorer, right-click the procedure and choose Script As > Execute > New Query Window, the variable declaration for that parameter is simply VARCHAR, not VARCHAR(MAX). Obviously this isn't a huge show-stopper, and an easy workaround is to use NVARCHAR(MAX) parameters (if this is acceptable), but I also believe that this can't be a very difficult bug to fix. I tried to use Profiler to peek at what SSMS calls when you run this script, but they are only retrieving metadata and not revealing how they are actually building the output that you ultimately execute. My guess is that there is just a bug when handling VARCHAR() types where max_length = -1. Anyway the bug has been sitting around for almost three years and could possibly use a little traffic.

#241782 : create script for sp execute in parameter varchar(max) wrong

Who coded that infinite loop?

In several Windows collations (e.g. Latin1_General_CI_AS), when performing a REPLACE() of CHAR(0), the SQL engine can enter an infinite loop and consume multiple CPUs. A problem with CHAR(0) was first reported by Erland back in 2006, but the infinite loop was a much more recent revelation (it was added in a comment this week, and may appear in an independent Connect item in the future).

#125502 : Replace of char(0) does not work in DB with Windows collation

Can we have an option to turn off deferred name resolution?

Deferred name resolution is confusing, and has even created a market for 3rd party products that track dependencies better than SQL Server can do on its own (e.g. Red-Gate's SQL Dependency Tracker). We know that if we create a stored procedure that calls another stored procedure that doesn't yet exist, the parser still allows us to create the procedure, giving us the benefit of the doubt that we will create the other procedure later. So in this case, we get a warning message:

CREATE PROCEDURE dbo.test1
AS
BEGIN
   EXEC dbo.proc_does_not_exist;
END
GO

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends 
on the missing table 'dbo.proc_does_not_exist'. The stored procedure will still be 
created; however, it cannot be successfully executed until the table exists.

The warning message incorrectly says "table" when it should probably say "object." In SQL Server 2008, the error message is actually:

The module 'test1' depends on the missing object 'dbo.proc_does_not_exist'. The module 
will still be created; however, it cannot run successfully until the object exists.

Sadly, we don't get a warning or an error if we reference a table that doesn't yet exist:

CREATE PROCEDURE dbo.test2
AS
BEGIN
   SELECT * FROM dbo.table_does_not_exist;
END
GO

Command(s) completed successfully.

This was complained about in Connect #246014, which was closed as fixed, however the parser is still silent about the problem in SQL Server 2008 (where we are told it has been addressed). I guess the "fixed" part is just that the error message when the referenced object is NOT a table no longer incorrectly says "table." :-(

Deferred name resolution falls completely on its face if we reference a column that DOES NOT (yet) exist, in a table that DOES exist. Where is our benefit of the doubt now? This stored procedure won't compile, even if we intend to go create the column immediately:

CREATE TABLE dbo.foo
(
    bar INT
);
GO

CREATE PROCEDURE dbo.test3
AS
BEGIN
   SELECT column_does_not_exist FROM dbo.foo;
END
GO

Msg 207, Level 16, State 1, Procedure test3, Line 3
Invalid column name 'column_does_not_exist'.

As alluded to, dependency tracking gets better in SQL Server 2008, as *some* unresolved dependencies are stored in sys.dm_sql_referenced_entities. I wrote a lengthy blog post about this last year:

Keeping sysdepends up to date in SQL Server 2008

But I would still like to make it impossible to create anything (even a synonym) that references an object that doesn't yet exist. Erland goes into much more detail about this in his paper on SET STRICT_CHECKS ON. You can't vote on that, but you could vote on these:

#127152 : Ability to disable or workaround deferred name resolution

#287100 : Turn off deferred name resolution for CREATE SYNONYM
#260762 : Add optional checks for more robust development

SQL Server 2008 Books Online update is available

AaronBertrand — Tue, 07 Jul 2009 16:36:00 GMT

Well, let me be more precise: there is an updated version of Books Online online. As reported earlier by Buck Woody, the online version of SQL Server 2008 Books Online has been updated (we are seeing the updates now but the content was refreshed June 29th). The download has not been made available as of yet; this usually takes several days to get the packages together, get them up on the download servers, and then distribute the links throughout the download sites and other Microsoft properties.

When I was looking around to try and spot the downloadable version, I was quickly re-introduced to some peculiarities I've noticed before but didn't care enough to comment on. Most importantly:

the front page of the online version does not give any indication that there is a downloadable version, never mind provide a link to it; and,
the main SQL Server 2008 page (http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspx) points to a download page (http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx) which says you are downloading the January 2009 update. Of course the download page behind the links actually offers the May update.

I complained about this more formally here:

#472985 : DOC : Make Books Online downloads more discoverable and current

Anyway, you can watch this page to see if they update it when the new version becomes available for download (or, as Buck suggests, you can wait for Microsoft Update to download the new version for you).

More info on the May 2009 update to SQL Server 2008 Books Online

AaronBertrand — Fri, 22 May 2009 19:50:00 GMT

I finally got around to installing this update today, and thought I would mention a few things.

Don't be surprised that "Books Online (May 2009)" actually says "April 2009" in the application's title bar and on any page which states the version. (I already received one private message asking me why they got the April version instead of the May version.) This has happened for every Books Online update that I can remember -- it just takes time to publish and distribute; I don't know what else to tell you.
To make item 1. even more confusing, the "New and Updated Topics" topic is actually dated "March 30, 2009." Again, please don't nit-pick. While I am quite happy to use the easier-to-update web interface for most things, I am also happy that they are dedicated to pushing these releases out to us, even if it does take 45 days to actually go from "done" to "installed." (Now where is the mobile version for my iPhone?)
There is a new example in the topic "CREATE USER" that shows how to create and use a user without a login. I had no idea you could create a user without a login behind it. If it weren't for this update (and the fact that they have committed to documenting changes in a sensible way - just check the "Change History" section at the bottom of any page), I would never have been looking at this topic.
The sp_configure option 'user instance timeout' has been added to the list of deprecated instance options. Initially I thought this had to do with SQL Server Express Edition's user instances option, but apparently this specifically involves just the Compact Edition.

New version of SQL Server 2008 Books Online is available

AaronBertrand — Fri, 22 May 2009 02:45:00 GMT

Yesterday Microsoft released a new downloadable version of Books Online for SQL Server 2008:

http://www.microsoft.com/downloads/details.aspx?FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97

Did You Know? A New SQL Server 2008 Books Online Update is Available

Kalen Delaney — Tue, 03 Feb 2009 01:45:00 GMT

When I searched for "sql server 2008 books online update", the first non-advertising hit I got was labeled August 2008, and I already have a Books Online download for August.

However, when I clicked on the link I was taken to a page to download a brand new January 2009 Update::

So here's the link:

http://www.microsoft.com/downloads/details.aspx?FamilyId=765433F7-0983-4D7A-B628-0A98145BCB97&displaylang=en

Have fun!

~Kalen

Did You Know? You can search the web-only Books Online

Kalen Delaney — Wed, 12 Nov 2008 01:22:00 GMT

You might be aware (from other bloggers on this site) that there is a new web-only Books Online for SQL Server 2008 available at http://msdn.microsoft.com/en-us/library/dd239407.aspx.

Like Tibor, I prefer a downloadable BOL, partly because I do a lot of research and writing while traveling and I don't always have an internet connection, like at 42,000 ft (~14000m) above the Atlantic Ocean.

However, I need the latest documentation while I am finishing up my book, so I decided to start using it today, now that I am back from my travels to the UK. However, I discovered another reason while I like the desktop copy of BOL better and that is the searching functionality. The web-only BOL only has the search box to search the ENTIRE MSDN site, and it's not nearly as convenient as the search capabilities in the local documentation.

To my delight, I just found out from Alan Brewer, from the SQL Documentation Team, that Microsoft has a nice search macro to help limit your search on MSDN to just SQL Server 2008 topics:

http://search.live.com/macros/sql_server_user_education/sql2008bolsearch/

It's still not as nice as the local searching, but since web-only is all we have for now, for the most recent BOL, it's definitely a good thing.

There is also a 2005 online search macro at:

http://blogs.msdn.com/sqlserverue/archive/2007/10/01/eliminate-extranneous-search-results.aspx

Have fun!

~Kalen

New (web) update for SQL Server 2008 Books Online

TiborKaraszi — Wed, 05 Nov 2008 08:19:00 GMT

Fire up your SQL Server 2008 Books Online (BOL) and check the date. You should find this in any article (except for the very start page).

RTM of SQL Server 2008 shipped with a BOL which has ... hmm,... doesn't have a date (just checked in a virtual machine). OK, so if you don't find a date (year month) you have the BOL which shipped with RTM.

The first BOL update was released 2008-09-30. You will see this as (August 2008). This was made available on the web as well as a download (http://www.microsoft.com/downloads/details.aspx?FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97&DisplayLang=en). This BOL did not list what was changed from prior release since this was considered RTM of BOL and the BOL that shipped with RTM was considered RC0 (which explains why this BOL lists Aug 2008 when it was released late Sept 2008).

Today I learned that MS has released yet another release of Books Online. This is only available on the web, and you see this as (October 2008). Now we do have listing of what section are new and what were expended on. I admit that I prefer locally installed BOL, but I guess that MS has resource limitations the same was as all of us do. Check it out at http://msdn.microsoft.com/en-us/library/dd239407.aspx.