If you come here regularly, you have probably noticed that my web site is not one that is updated frequently. Essentially, I make changes when I have a change to make: an update to an article, a new version of a tool or somesuch. If you want to see if I have any updates, this is the page you should bookmark. If I make additions to the site, or changes to existing items you will see it here. (Unless it's just a trivial fix like a correction of a spelling or a grammar error.)
Added a new item to the SQL Server wishlist: Add a Virtual Errors Table.
Updated the article Slow in the Application, Fast in SSMS to reflect an import change in SQL 2012 SP1 with regards to the issue with linked servers. Also added a note to the section The Story so Far.
Added a good suggestion from Wayne Bloss about sharing temp tables in the article How to Share Data Between Stored Procedures.
Added a caveat to the documentation of the column tempdb in the output of beta_lockinfo. The procedure itself is unchanged.
A small update to the SQL Server Wishlist about the old Connect items with numbers in the 12xxxx range. They are visible, but you can currently not vote for these items.
Uploaded two suits of pictures: one from my vacation in Romania,where the top draw was the Danbue delta. Then there is a smaller suite with the regular pictures from summer in the area Stockholm, this time also augmented by pictures from Kalmar and Portland and a little more.
Added one more item to my Strict Checks proposal: columns must be prefixed by an alias or the table source when there are more than one table source visible, to avoid that adding a column in the future results in an ambiguity error.
There was a problem with some very old Connect items mentioned in my SQL Server wishlist. The Connect team have fixed this problem, and these Connect items should now be visible. (At least I can see them when I'm signed in, and I couldn't in the past.)
Version 15 of beta_lockinfo. Added display of non-default ANSI and locking settings as well a progress column (which applies to only some commands). beta_lockinfo now displays tasks without session ids that have interesting wait types. This can help you to see THREADPOOL waits that occurs when you run out of worker threads and your users cannot log in.
There is a new version of Lee Tudor's sp_sqltrace available. Lee have made a major overhaul and the procedure is now more powerful than ever. You can now snoop another spid instead of analysing your own process. The procedure now collects and presents wait-stats information per statement on SQL 2008 and up. There are several new parameters to control the procedure, and you can reanalyse a trace if you find that wanted to see the data in a different way. Finally, it has a new name, so you have it in master and run it from anywhere.
Released version 2.008 of Win32::SqlServer, an API to SQL Server for Perl. See the History file for changes and new features.
Corrected a few errors and mumblings in the paragraph about using the CLR for linked servers in the article How to Share Data Between Stored Procedures.
Rewrote most parts of the article Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters. I found that was extremely easy to write a class that parses a comma-delimited string and feed the constructor of that class as the parameter value to a TVP, why I have replaced the previous examples with this one. I have also added two examples on how to load a file through a TVP, one non-streaming and one streaming example. I've also added information on how you can specify that the data you pass to the TVP is already sorted. As a consequenc, I have also updated the section on TVPs in the performance appendix, but I have not run any new performance tests.
Added a note about SQL Server Data Tools and sharing temp tables to the article How to Share Data Between Stored Procedures.
Updated my SQL Server Wishlist after the release of SQL 2012. I've rearranged the list, and I put focus even more robustness, devoting an entire section to wishes aimed to make development in SQL Server more robust. All new wishes on the wish list are in that area. I have also updated my document Ideas for SET STRICT_CHECKS ON with some interesting ideas for strict checks, for instance I suggest checks for cardinality errors at compile-time. I've withdrawn some wishes from the wishlist – those that were fulfilled and a few more. If you wonder what happened the old wishes, there is an annonated version of the old wishlist.
The eighth public release of AbaPerls. Big news! There is now support for Team Foundation Server. AbaPerls users can see the list of recent changes here.
Triple release:
Refreshed the article Connecting to MS SQL Server from Unix to reflect that Microsoft has released a CTP of an ODBC driver for Linux.
Several of the links in the section Further Reading in the article Slow in the Application, Fast in SSMS? were broken. This has been fixed.
Uploaded pictures from my vacation in Spain earlier this year, as well as the usual set of summer pictures from the Stockholm area (although you will need sturdy clothing to look at some of the pictures at the end, as they are from elsewhere.)
When I updated of the article article on Dynamic Search Conditions for SQL 2008 to reflect the release of SP1 of SQL 2008 R2, I missed in one place. This has now been corrected.
Released version 2.007 of Win32::SqlServer.
Updated the article on Dynamic Search Conditions for SQL 2008 to reflect the release of SP1 for SQL 2008 R2. I've also updated the script for Northgale to avoid use of some deprecated syntax and data types.
I've made a thorough revision of the article Granting Permissions through Stored Procedures. Partly this is due to that the article was originally written with SQL 2005 being a brand-new release which it no longer is. But I also cover some new ground with counter-signatures and show how you can use counter-signing to make it possible for plain users to start a certain job. The article now also includes a script to easily grant server-level permissions to a user-database procedure, using throw-away passwords for the certitficate. And the most important of all: the article includes a detailed discussion of what it means to make a database trustworthy, and how this can be a security vulnerability if done causually.
There is a now a Russian translation available of Slow in the Application, Fast in SSMS. Thanks to Dima Piliugin for this work!
Added section An Issue with Linked Servers to the article Slow in the Application, Fast in SSMS that covers one more reason why may see different performance in the application and from SSMS. Also, got a whim and clean up the article list one the home page to only include the date for the most recent revision of each article.
A minor update in the article on Dynamic SQL to reflect that the bug with OPTION(RECOMPILE) has been fixed. (It was over a year ago, but I had failed to update this article.)
Made an addition to the article Arrays and Lists in SQL Server 2008, Using Tabled-Valued Parameters: you need EXECUTE permission to use a table type.
Published a brand-new article: Slow in the Application, Fast in SSMS? Understanding Performance Mysteries, which takes its starting point in the confusing situation when a query is slow in your application, yet runs quickly when you investigate it in SQL Server Management Studio. In many of these situations parameter sniffing is part of the equation, and I explain what parameter sniffing is, and what measures you can take to avoid to be tripped by what in most situations is a good feature, but which sometimes backfires.
Uploaded a new version of beta_lockinfo. The new version displays temp tables with their logical name only. Moreover, if there is more than one temp table with the same name, they aggreagated into a single line per lock.
On a cold dark winter's day, what could be better than to dream back on the summer that was? Pictures from the summer of 2010 uploaded.
Imran Mohamed pointed out that there was an error in the article Granting Permission through Stored Procedures, the expression to decode context_info() would include trailing NUL characters in the character data. The expression has been improved.
I used to host a tool call Query Plan Tree, developed by Ivan Arjetinski. However, I have dropped this, since SQL Sentry have made their SQL Sentry Plan Explorer available for free. It has all the functionality of Ivan's tool and a lot more bells and whistles.
Released version 13 of beta_lockinfo. Bugfixes, and added a column to show tempdb usage.
Released version 2.006 of Win32::SqlServer.
The seventh public release of AbaPerls. AbaPerls users can see the list of recent changes here.
Updated the article Dynamic Search Conditions in SQL 2008 to reflect the release of Serivce Pack 2 for SQL 2008. Also, I have corrected a couple of grammar and spelling errors in the same articleDuff Browne was kind to point out.
I have rewritten the XML section in the article Arrays and Lists in SQL Server 2005. During the summer I became aware of several things about best practice and performance for XML, which I have addressed. I have performed a rerun of the performance tests for SQL 2008, since they were flawed in several aspects for XML. I have also uptaded the article for the original performance tests in 2006 to point out these flaws. I've also made some smaller updates to the section about fn_nums and added note that perforamance of INSERT VALUES with many rows may improve in SQL 11.
Updated the section on the OVER clause in the SQL Server Wishlist, with a new Connect item from Iztik Ben-Gan. Votes are encouraged!
Fixed an issue in sqltrace. Behzad Sadeghi pointed out that plans were missing for dynamic SQL invoked through sp_executesql. due to an inconsistency in SQL Server, which I have reported on Connect.With help of Behzad I have implemented a workaround for the issue.
Uploaded pictures from my spring vacation in the south of France and Paris. Also uploaded very old (and generally very poor) pictures from my vacation in 2000.
A new update for Dynamic Search Conditions in SQL 2008: There is now a Cumultative Update for SQL 2008 R2 which includes the fix for OPTION (RECOMPILE).
An update on the article for Dynamic Search Conditions in SQL 2008: the RTM version of SQL 2008 R2 does not include the fix to make OPTION (RECOMPILE) behave as desired.
Added a request for scoped variables to my SQL Server Wishlist.
Added a request for QUALIFY to permit for more concise queries to my SQL Server Wishlist.
Uploaded various pictures from 2009, mainly from summer in Stockholm and around.
Further changes to Arrays and Lists in SQL Server 2005 and Beyond, When TVPs Don't Cut it: some variations on fn_nums and some performance observations had I failed to include originally.
I've extended the section XML in the article How to Share Data Between Stored Procedures and made some other minor revisions to that article.
I've reworked the article on Arrays and Lists to cover a couple of new methods that people has to be kind to suggest to me. I've also run a new suite of performance tests on SQL 2008 to test the new methods together with the old ones. This time I have also tested call overhead, and made an attempt to test multi-thread performance. I've also added a new article for SQL 2008 that is devoted entirely to table-valued parameters. In total there are now three articles and two appendixes:
Two new items:
Started this web log, and added items back to 2009-04-01.
Updated the articles on Dynamic Search Conditions. With the release of SQL 2008 SP1 CU5, OPTION (RECOMPILE) now works like it did in RTM, but without the serious bug that caused Microsoft to revert to the old behaviour in SP1. Therefore I've restored the new article for SQL 2008, and the old one is again labelled to be for SQL 2005 and earlier.
In my SQL Server Wishlist, added request for getting the position in an XML document.
In the article How to Share Data Between Stored Procedures, added a brief discussion on performance about INSERT-EXEC with dynamic SQL, and a reference to a blog post from SQL Server MVP Adam Machanic.
Released version 12 of beta_lockinfo. I've added three columns to show information about the current transaction of the process. Also added one more spid column. I've also fixed textmode that was broken, and added better checks to the creation script to alert you when you don't meet the prerequisites.
Released version 2.005 of Win32::SqlServer.
Uploaded pictures from my trip to Japan.
The section on INSERT-EXEC in How to Share Data... said that it does not work with table variables, which is right on SQL 2000 only.
In the SQL Server wishlist, added an additional request with regards to domains.