Tuesday, August 31, 2010
Having the SSMS Tools Pack out in the wild enables me to get much “joy” from different bug reports people send me. And let me tell you, people send me back some seriously weird errors.
But the most unexpected error message I’ve seen so far was the OverflowException when calling System.Data.SqlClient.SqlDataReader.GetDecimal(Int32 i).
It turns out that SQL Server decimal data type is not mapped to the .Net decimal in it’s whole range. .Net decimal type only maps to SQL Server one in the decimal(29, X) range.
That means that if you have a decimal column that has the precision higher than 29 and no matter the scale, you won’t be able to use the native .Net data type.
So what to do? Let’s take a look with an example. The comments provide additional info.
SQL Code:
-- create a test table in tempdb with one valid and one invalid decimal mapping column.
CREATE TABLE TestTable
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[ValidDecVal] [DECIMAL](29, 2) NOT NULL,
[InvalidDecVal] [DECIMAL](30, 30) NOT NULL
)
GO
-- insert some data
INSERT INTO TestTable(ValidDecVal, InvalidDecVal)
-- both values are in correct range
SELECT 123456789012345678901234567.56, 0.123456789012345678901234567890
C# Code:
private void GetData()
{
using (SqlConnection conn = new SqlConnection(@"server=TestServer; database=tempdb; Integrated Security=SSPI;"))
{
using (SqlCommand cmd = new SqlCommand("SELECT ID, ValidDecVal, InvalidDecVal FROM TestTable", conn))
{
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
rdr.Read();
// get the id
int id = rdr.GetInt32(0);
// get the 29 precision value just fine
decimal validDecimal = rdr.GetDecimal(1);
// this errors out ...
decimal invalidDecimal = rdr.GetDecimal(2);
// so does this ...
decimal invalidDecimal = rdr.GetSqlDecimal(2).Value;
// the only thing to do is to either pass around SqlDecimal class
SqlDecimal invalidDecimalAsSqlDecimal = rdr.GetSqlDecimal(2);
// or to cast it to string
string invalidDecimalAsString = invalidDecimalAsSqlDecimal.ToString();
// ... do something with upper values
Console.WriteLine(invalidDecimalAsSqlDecimal);
Console.WriteLine(invalidDecimalAsString);
}
}
}
If you’re working with large precision types I’d like to hear how you overcome this problem in .Net.
The only way I’ve found to deal with this is to either pass around the raw SqlDecimal data type or its string value.
A quick search revealed this Connect item that showed the problem with Linq2SQL. I don’t know why this isn’t fixed yet but I hope it will be soon. If you encounter this problem, vote it up.
Tuesday, August 24, 2010
This is a release that fixes all known bugs. If you encounter any new ones don’t hesitate to report them. :)
The main feature list hasn’t changed.
A few improvements have been made though:
Save SQL Snippets to HTML and/or print them directly from SSMS.
In Window Connection Coloring the server names can now be regular expressions giving you the ability to color multiple servers with the same color.
Here’s a blog from David Levy (Blog|Twitter) about the new window connection coloring using regular expression and why is it really useful to him. Thanx for kind words Dave.
You can download the new version 1.9 here.
Enjoy it!
Thursday, July 01, 2010
As of today I’m the third Slovenian SQL Server MVP.
Thanx to all who nominated me!
Let’s see how this year goes and i’m sure it’ll be a blast, but most importantly:
See you all at the MVP Summit next year! :))
Friday, June 04, 2010
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover:
SQLU part 1 - What and why of database testing
SQLU part 2 - What and why of database refactoring
SQLU part 3 - Database testing and refactoring tools and examples
This is the third and last part of the series and in it we’ll take a look at tools we can test and refactor with plus some an example of the both.
Tools of the trade
First a few thoughts about how to go about testing a database. I'm firmily against any testing tools that go into the database itself or need an extra database. Unit tests for the database and applications using the database should all be in one place using the same technology. By using database specific frameworks we fragment our tests into many places and increase test system complexity. Let’s take a look at some testing tools.
1. NUnit, xUnit, MbUnit
All three are .Net testing frameworks meant to unit test .Net application. But we can test databases with them just fine. I use NUnit because I’ve always used it for work and personal projects. One day this might change. So the thing to remember is to be flexible if something better comes along. All three are quite similar and you should be able to switch between them without much problem.
2. TSQLUnit
As much as this framework is helpful for the non-C# savvy folks I don’t like it for the reason I stated above. It lives in the database and thus fragments the testing infrastructure. Also it appears that it’s not being actively developed anymore.
3. DbFit
I haven’t had the pleasure of trying this tool just yet but it’s on my to-do list. From what I’ve read and heard Gojko Adzic (@gojkoadzic on Twitter) has done a remarkable job with it.
4. Redgate SQL Refactor and Apex SQL Refactor
Neither of these refactoring tools are free, however if you have hardcore refactoring planned they are worth while looking into. I’ve only used the Red Gate’s Refactor and was quite impressed with it.
5. Reverting the database state
I’ve talked before about ways to revert a database to pre-test state after unit testing. This still holds and I haven’t changed my mind. Also make sure to read the comments as they are quite informative. I especially like the idea of setting up and tearing down the schema for each test group with NHibernate.
Testing and refactoring example
We’ll take a look at the simple schema and data test for a view and refactoring the SELECT * in that view. We’ll use a single table PhoneNumbers with ID and Phone columns. Then we’ll refactor the Phone column into 3 columns Prefix, Number and Suffix. Lastly we’ll remove the original Phone column. Then we’ll check how the view behaves with tests in NUnit. The comments in code explain the problem so be sure to read them. I’m assuming you know NUnit and C#.
| T-SQL Code | C# test code |
USE tempdb
GO
CREATE TABLE PhoneNumbers
(
ID INT IDENTITY(1,1),
Phone VARCHAR(20)
)
GO
INSERT INTO PhoneNumbers(Phone)
SELECT '111 222333 444' UNION ALL
SELECT '555 666777 888'
GO
-- notice we don't have WITH SCHEMABINDING
CREATE VIEW vPhoneNumbers
AS
SELECT *
FROM PhoneNumbers
GO
-- Let's take a look at what the view returns
-- If we add a new columns and rows both tests will fail
SELECT *
FROM vPhoneNumbers
GO
-- DoesViewReturnCorrectColumns test will SUCCEED
-- DoesViewReturnCorrectData test will SUCCEED
-- refactor to split Phone column into 3 parts
ALTER TABLE PhoneNumbers ADD Prefix VARCHAR(3)
ALTER TABLE PhoneNumbers ADD Number VARCHAR(6)
ALTER TABLE PhoneNumbers ADD Suffix VARCHAR(3)
GO
-- update the new columns
UPDATE PhoneNumbers
SET Prefix = LEFT(Phone, 3),
Number = SUBSTRING(Phone, 5, 6),
Suffix = RIGHT(Phone, 3)
GO
-- remove the old column
ALTER TABLE PhoneNumbers DROP COLUMN Phone
GO
-- This returns unexpected results!
-- it returns 2 columns ID and Phone even though
-- we don't have a Phone column anymore.
-- Notice that the data is from the Prefix column
-- This is a danger of SELECT *
SELECT *
FROM vPhoneNumbers
-- DoesViewReturnCorrectColumns test will SUCCEED
-- DoesViewReturnCorrectData test will FAIL
-- for a fix we have to call sp_refreshview
-- to refresh the view definition
EXEC sp_refreshview 'vPhoneNumbers'
-- after the refresh the view returns 4 columns
-- this breaks the input/output behavior of the database
-- which refactoring MUST NOT do
SELECT *
FROM vPhoneNumbers
-- DoesViewReturnCorrectColumns test will FAIL
-- DoesViewReturnCorrectData test will FAIL
-- to fix the input/output behavior change problem
-- we have to concat the 3 columns into one named Phone
ALTER VIEW vPhoneNumbers
AS
SELECT ID, Prefix + ' ' + Number + ' ' + Suffix AS Phone
FROM PhoneNumbers
GO
-- now it works as expected
SELECT *
FROM vPhoneNumbers
-- DoesViewReturnCorrectColumns test will SUCCEED
-- DoesViewReturnCorrectData test will SUCCEED
-- clean up
DROP VIEW vPhoneNumbers
DROP TABLE PhoneNumbers
|
[Test]
public void DoesViewReturnCorrectColumns()
{
// conn is a valid SqlConnection to the server's tempdb
// note the SET FMTONLY ON with which we return only schema and no data
using (SqlCommand cmd = new SqlCommand("SET FMTONLY ON;
SELECT * FROM vPhoneNumbers", conn))
{
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
// test returned schema: number of columns, column names and data types
Assert.AreEqual(dt.Columns.Count, 2);
Assert.AreEqual(dt.Columns[0].Caption, "ID");
Assert.AreEqual(dt.Columns[0].DataType, typeof(int));
Assert.AreEqual(dt.Columns[1].Caption, "Phone");
Assert.AreEqual(dt.Columns[1].DataType, typeof(string));
}
}
[Test]
public void DoesViewReturnCorrectData()
{
// conn is a valid SqlConnection to the server's tempdb
using (SqlCommand cmd = new SqlCommand("SELECT * FROM vPhoneNumbers", conn))
{
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
// test returned data: number of rows and their values
Assert.AreEqual(dt.Rows.Count, 2);
Assert.AreEqual(dt.Rows[0]["ID"], 1);
Assert.AreEqual(dt.Rows[0]["Phone"], "111 222333 444");
Assert.AreEqual(dt.Rows[1]["ID"], 2);
Assert.AreEqual(dt.Rows[1]["Phone"], "555 666777 888");
}
}
|
With this simple example we’ve seen how a very simple schema can cause a lot of problems in the whole application/database system if it doesn’t have tests. Imagine what would happen if some outside process would depend on that view. It would get wrong data and propagate it silently throughout the system. And that is not good. So have tests at least for the crucial parts of your systems.
And with that we conclude the Database Testing and Refactoring week at SQL University. Hope you learned something new and enjoy the learning weeks to come. Have fun!
Wednesday, June 02, 2010
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover:
SQLU part 1 - What and why of database testing
SQLU part 2 - What and why of database refactoring
SQLU part 3 - Database testing and refactoring tools and examples
This is a second part of the series and in it we’ll take a look at what database refactoring is and why do it.
Why refactor a database
To know why refactor we first have to know what refactoring actually is.
Code refactoring is a process where we change module internals in a way that does not change that module’s input/output behavior.
For successful refactoring there is one crucial thing we absolutely must have: Tests. Automated unit tests are the only guarantee we have that we haven’t broken the input/output behavior before refactoring. If you haven’t go back ad read my post on the matter. Then start writing them. Next thing you need is a code module. Those are views, UDFs and stored procedures. By having direct table access we can kiss fast and sweet refactoring good bye. One more point to have a database abstraction layer. And no, ORM’s don’t fall into that category.
But also know that refactoring is NOT adding new functionality to your code. Many have fallen into this trap. Don’t be one of them and resist the lure of the dark side. And it’s a strong lure. We developers in general love to add new stuff to our code, but hate fixing our own mistakes or changing existing code for no apparent reason. To be a good refactorer one needs discipline and focus.
Now we know that refactoring is all about changing inner workings of existing code. This can be due to performance optimizations, changing internal code workflows or some other reason. This is a typical black box scenario to the outside world. If we upgrade the car engine it still has to drive on the road (preferably faster) and not fly (no matter how cool that would be). Also be aware that white box tests will break when we refactor.
What to refactor in a database
Refactoring databases doesn’t happen that often but when it does it can include a lot of stuff. Let us look at a few common cases.
Adding or removing database schema objects
Adding, removing or changing table columns in any way, adding constraints, keys, etc… All of these can be counted as internal changes not visible to the data consumer. But each of these carries a potential input/output behavior change. Dropping a column can result in views not working anymore or stored procedure logic crashing. Adding a unique constraint shows duplicated data that shouldn’t exist. Foreign keys break a truncate table command executed from an application that runs once a month. All these scenarios are very real and can happen. With the proper database abstraction layer fully covered with black box tests we can make sure something like that does not happen (hopefully at all).
Changing physical structures
Physical structures include heaps, indexes and partitions. We can pretty much add or remove those without changing the data returned by the database. But the performance can be affected. So here we use our performance tests. We do have them, right? Just by adding a single index we can achieve orders of magnitude performance improvement. Won’t that make users happy? But what if that index causes our write operations to crawl to a stop. again we have to test this. There are a lot of things to think about and have tests for. Without tests we can’t do successful refactoring!
Fixing bad code
We all have some bad code in our systems. We usually refer to that code as code smell as they violate good coding practices. Examples of such code smells are SQL injection, use of SELECT *, scalar UDFs or cursors, etc… Each of those is huge code smell and can result in major code changes. Take SELECT * from example. If we remove a column from a table the client using that SELECT * statement won’t have a clue about that until it runs. Then it will gracefully crash and burn. Not to mention the widely unknown SELECT * view refresh problem that Tomas LaRock (@SQLRockstar on Twitter) and Colin Stasiuk (@BenchmarkIT on Twitter) talk about in detail. Go read about it, it’s informative.
Refactoring this includes replacing the * with column names and most likely change to application using the database.
Breaking apart huge stored procedures
Have you ever seen seen a stored procedure that was 2000 lines long? I have. It’s not pretty. It hurts the eyes and sucks the will to live the next 10 minutes. They are a maintenance nightmare and turn into things no one dares to touch. I’m willing to bet that 100% of time they don’t have a single test on them.
Large stored procedures (and functions) are a clear sign that they contain business logic. General opinion on good database coding practices says that business logic has no business in the database. That’s the applications part. Refactoring such behemoths requires writing lots of edge case tests for the stored procedure input/output behavior and then start to refactor it. First we split the logic inside into smaller parts like new stored procedures and UDFs. Those then get called from the master stored procedure. Once we’ve successfully modularized the database code it’s best to transfer that logic into the applications consuming it. This only leaves the stored procedure with common data manipulation logic. Of course this isn’t always possible so having a plethora of performance and behavior unit tests is absolutely necessary to confirm we’ve actually improved the codebase in some way.
Refactoring is not a popular chore amongst developers or managers. The former don’t like fixing old code, the latter can’t see the financial benefit. Remember how we talked about being lousy at estimating future costs in the previous post? But there comes a time when it must be done. Hopefully I’ve given you some ideas how to get started. In the last post of the series we’ll take a look at the tools to use and an example of testing and refactoring.
Monday, May 31, 2010
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover:
SQLU part 1 - What and why of database testing
SQLU part 2 - What and why of database refactoring
SQLU part 3 - Database testing and refactoring tools and examples
With that out of the way let us sharpen our pencils and get going.
Why test a database
The sad state of the industry today is that there is very little emphasis on testing in general. Test driven development is still a small niche of the programming world while refactoring is even smaller. The cause of this is the inability of developers to convince themselves and their managers that writing tests is beneficial. At the moment they are mostly viewed as waste of time. This is because the average person (let’s not fool ourselves, we’re all average) is unable to think about lower future costs in relation to little more current work. It’s orders of magnitude easier to know about the current costs in relation to current amount of work. That’s why programmers convince themselves testing is a waste of time.
However we have to ask ourselves what tests are really about? Maybe finding bugs? No, not really. If we introduce bugs, we’re likely to write test around those bugs too. But yes we can find some bugs with tests. The main point of tests is to have reproducible repeatability in our systems. By having a code base largely covered by tests we can know with better certainty what a small code change can break in other parts of the system. By having repeatability we can make code changes with confidence, since we know we’ll see what breaks in other tests. And here comes the inability to estimate future costs. By spending just a few more hours writing those tests we’d know instantly what broke where.
Imagine we fix a reported bug. We check-in the code, deploy it and the users are happy. Until we get a call 2 weeks later about a certain monthly process has stopped working. What we don’t know is that this process was developed by a long gone coworker and for some reason it relied on that same bug we’ve happily fixed. There’s no way we could’ve known that. We say OK and go in and fix the monthly process. But what we have no clue about is that there’s this ETL job that relied on data from that monthly process. Now that we’ve fixed the process it’s giving unexpected (yet correct since we fixed it) data to the ETL job. So we have to fix that too. But there’s this part of the app we coded that relies on data from that exact ETL job. And just like that we enter the “Loop of maintenance horror”. With the loop eventually comes blame. Here’s a nice tip for all developers and DBAs out there: If you make a mistake man up and admit to it.
All of the above is valid for any kind of software development. Keeping this in mind the database is nothing other than just a part of the application. But a big part! One reason why testing a database is even more important than testing an application is that one database is usually accessed from multiple applications and processes. This makes it the central and vital part of the enterprise software infrastructure.
Knowing all this can we really afford not to have tests?
What to test in a database
Now that we’ve decided we’ll dive into this testing thing we have to ask ourselves what needs to be tested? The short answer is: everything. The long answer is: read on! There are 2 main ways of doing tests: Black box and White box testing.
Black box testing means we have no idea how the system internals are built and we only have access to it’s inputs and outputs. With it we test that the internal changes to the system haven’t caused the input/output behavior of the system to change. The most important thing to test here are the edge conditions. It’s where most programs break. Having good edge condition tests we can be more confident that the systems changes won’t break.
White box testing has the full knowledge of the system internals. With it we test the internal system changes, different states of the application, etc… White and Black box tests should be complementary to each other as they are very much interconnected.
Testing database routines includes testing stored procedures, views, user defined functions and anything you use to access the data with. Database routines are your input/output interface to the database system. They count as black box testing. We test then for 2 things: Data and schema. When testing schema we only care about the columns and the data types they’re returning. After all the schema is the contract to the out side systems. If it changes we usually have to change the applications accessing it. One helpful T-SQL command when doing schema tests is SET FMTONLY ON. It tells the SQL Server to return only empty results sets. This speeds up tests because it doesn’t return any data to the client. After we’ve validated the schema we have to test the returned data. There no other way to do this but to have expected data known before the tests executes and comparing that data to the database routine output.
Testing Authentication and Authorization helps us validate who has access to the SQL Server box (Authentication) and who has access to certain database objects (Authorization). For desktop applications and windows authentication this works well. But the biggest problem here are web apps. They usually connect to the database as a single user. Please ensure that that user is not SA or an account with admin privileges. That is just bad.
Load testing ensures us that our database can handle peak loads. One often overlooked tool for load testing is Microsoft’s OSTRESS tool. It’s part of RML utilities (x86, x64) for SQL Server and can help determine if our database server can handle loads like 100 simultaneous users each doing 10 requests per second. SQL Profiler can also help us here by looking at why certain queries are slow and what to do to fix them.
One particular problem to think about is how to begin testing existing databases. First thing we have to do is to get to know those databases. We can’t test something when we don’t know how it works. To do this we have to talk to the users of the applications accessing the database, run SQL Profiler to see what queries are being run, use existing documentation to decipher all the object relationships, etc… The way to approach this is to choose one part of the database (say a logical grouping of tables that go together) and filter our traces accordingly. Once we’ve done that we move on to the next grouping and so on until we’ve covered the whole database. Then we move on to the next one.
Database Testing is a topic that we can spent many hours discussing but let this be a nice intro to the world of database testing. See you in the next post.
Monday, May 03, 2010
This is a release that fixes all known major bugs and most of the minor ones.
The main feature list hasn’t changed.
The only addition is the ability to export and import only SQL snippets.
Before you could only export/import all settings which included the snippets.
You can download the new version here.
Enjoy it!
Thursday, April 15, 2010
In this part of the ongoing Wix tutorial series we’ll take a look at how to localize your MSI into different languages. We’re still the mighty SuperForm: Program that takes care of all your label color needs. :)
Localizing the MSI
With WiX 3.0 localizing an MSI is pretty much a simple and straightforward process. First let look at the WiX project Properties->Build. There you can see "Cultures to build" textbox. Put specific cultures to build into the testbox or leave it empty to build all of them. Cultures have to be in correct culture format like en-US, en-GB or de-DE.
Next we have to tell WiX which cultures we actually have in our project. Take a look at the first post in the series about Solution/Project structure and look at the Lang directory in the project structure picture. There we have de-de and en-us subfolders each with its own localized stuff. In the subfolders pay attention to the WXL files Loc_de-de.wxl and Loc_en-us.wxl. Each one has a <String Id="LANG"> under the WixLocalization root node. By including the string with id LANG we tell WiX we want that culture built. For English we have <String Id="LANG">1033</String>, for German <String Id="LANG">1031</String> in Loc_de-de.wxl and for French we’d have to create another file Loc_fr-FR.wxl and put <String Id="LANG">1036</String>. WXL files are localization files. Any string we want to localize we have to put in there. To reference it we use loc keyword like this: !(loc.IdOfTheVariable) => !(loc.MustCloseSuperForm)
This is our Loc_en-us.wxl. Note that German wxl has an identical structure but values are in German.
<?xml version="1.0" encoding="utf-8"?>
<WixLocalization Culture="en-us" xmlns="http://schemas.microsoft.com/wix/2006/localization" Codepage="1252">
<String Id="LANG">1033</String>
<String Id="ProductName">SuperForm</String>
<String Id="LicenseRtf" Overridable="yes">\Lang\en-us\EULA_en-us.rtf</String>
<String Id="ManufacturerName">My Company Name</String>
<String Id="AppNotSupported">This application is is not supported on your current OS. Minimal OS supported is Windows XP SP2</String>
<String Id="DotNetFrameworkNeeded">.NET Framework 3.5 is required. Please install the .NET Framework then run this installer again.</String>
<String Id="MustCloseSuperForm">Must close SuperForm!</String>
<String Id="SuperFormNewerVersionInstalled">A newer version of !(loc.ProductName) is already installed.</String>
<String Id="ProductKeyCheckDialog_Title">!(loc.ProductName) setup</String>
<String Id="ProductKeyCheckDialogControls_Title">!(loc.ProductName) Product check</String>
<String Id="ProductKeyCheckDialogControls_Description">Plese Enter following information to perform the licence check.</String>
<String Id="ProductKeyCheckDialogControls_FullName">Full Name:</String>
<String Id="ProductKeyCheckDialogControls_Organization">Organization:</String>
<String Id="ProductKeyCheckDialogControls_ProductKey">Product Key:</String>
<String Id="ProductKeyCheckDialogControls_InvalidProductKey">The product key you entered is invalid. Please call user support.</String>
</WixLocalization>
As you can see from the file we can use localization variables in other variables like we do for SuperFormNewerVersionInstalled string. ProductKeyCheckDialog* strings are to localize a custom dialog for Product key check which we’ll look at in the next post.
Built in dialog text localization
Under the de-de folder there’s also the WixUI_de-de.wxl file. This files contains German translations of all texts that are in WiX built in dialogs. It can be downloaded from WiX 3.0.5419.0 Source Forge site. Download the wix3-sources.zip and go to \src\ext\UIExtension\wixlib. There you’ll find already translated all WiX texts in 12 Languages.
Localizing the custom EULA license
Here it gets ugly. We can override the default EULA license easily by overriding WixUILicenseRtf WiX variable like this: <WixVariable Id="WixUILicenseRtf" Value="License.rtf" /> where License.rtf is the name of your custom EULA license file. The downside of this method is that you can only have one license file which means no localization for it. That’s why we need to make a workaround. License is checked on a dialog name LicenseAgreementDialog. What we have to do is overwrite that dialog and insert the functionality for localization.
This is a code for LicenseAgreementDialogOverwritten.wxs, an overwritten LicenseAgreementDialog that supports localization. LicenseAcceptedOverwritten replaces the LicenseAccepted built in variable.
<?xml version="1.0" encoding="UTF-8" ?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
<Fragment>
<UI>
<Dialog Id="LicenseAgreementDialogOverwritten" Width="370" Height="270" Title="!(loc.LicenseAgreementDlg_Title)">
<Control Id="LicenseAcceptedOverwrittenCheckBox" Type="CheckBox" X="20" Y="207" Width="330" Height="18" CheckBoxValue="1" Property="LicenseAcceptedOverwritten" Text="!(loc.LicenseAgreementDlgLicenseAcceptedCheckBox)" />
<Control Id="Back" Type="PushButton" X="180" Y="243" Width="56" Height="17" Text="!(loc.WixUIBack)" />
<Control Id="Next" Type="PushButton" X="236" Y="243" Width="56" Height="17" Default="yes" Text="!(loc.WixUINext)">
<Publish Event="SpawnWaitDialog" Value="WaitForCostingDlg">CostingComplete = 1</Publish>
<Condition Action="disable">
<![CDATA[ LicenseAcceptedOverwritten <> "1" ]]>
</Condition>
<Condition Action="enable">LicenseAcceptedOverwritten = "1"</Condition>
</Control>
<Control Id="Cancel" Type="PushButton" X="304" Y="243" Width="56" Height="17" Cancel="yes" Text="!(loc.WixUICancel)">
<Publish Event="SpawnDialog" Value="CancelDlg">1</Publish>
</Control>
<Control Id="BannerBitmap" Type="Bitmap" X="0" Y="0" Width="370" Height="44" TabSkip="no" Text="!(loc.LicenseAgreementDlgBannerBitmap)" />
<Control Id="LicenseText" Type="ScrollableText" X="20" Y="60" Width="330" Height="140" Sunken="yes" TabSkip="no">
<!-- This is original line -->
<!--<Text SourceFile="!(wix.WixUILicenseRtf=$(var.LicenseRtf))" />-->
<!-- To enable EULA localization we change it to this -->
<Text SourceFile="$(var.ProjectDir)\!(loc.LicenseRtf)" />
<!-- In each of localization files (wxl) put line like this:
<String Id="LicenseRtf" Overridable="yes">\Lang\en-us\EULA_en-us.rtf</String>-->
</Control>
<Control Id="Print" Type="PushButton" X="112" Y="243" Width="56" Height="17" Text="!(loc.WixUIPrint)">
<Publish Event="DoAction" Value="WixUIPrintEula">1</Publish>
</Control>
<Control Id="BannerLine" Type="Line" X="0" Y="44" Width="370" Height="0" />
<Control Id="BottomLine" Type="Line" X="0" Y="234" Width="370" Height="0" />
<Control Id="Description" Type="Text" X="25" Y="23" Width="340" Height="15" Transparent="yes" NoPrefix="yes" Text="!(loc.LicenseAgreementDlgDescription)" />
<Control Id="Title" Type="Text" X="15" Y="6" Width="200" Height="15" Transparent="yes" NoPrefix="yes" Text="!(loc.LicenseAgreementDlgTitle)" />
</Dialog>
</UI>
</Fragment>
</Wix>
Look at the Control with Id "LicenseText” and read the comments. We’ve changed the original license text source to "$(var.ProjectDir)\!(loc.LicenseRtf)". var.ProjectDir is the directory of the project file. The !(loc.LicenseRtf) is where the magic
happens. Scroll up and take a look at the wxl localization file example. We have the LicenseRtf declared there and it’s been made overridable so developers can change it if they want. The value of the LicenseRtf is the path to our localized EULA relative to the WiX project directory. With little hacking we’ve achieved a fully localizable installer package.
The final step is to insert the extended LicenseAgreementDialogOverwritten license dialog into the installer GUI chain. This is how it’s done under the <UI> node of course.
<UI>
<!-- code to be discussed in later posts –>
<!-- BEGIN UI LOGIC FOR CLEAN INSTALLER -->
<Publish Dialog="WelcomeDlg" Control="Next" Event="NewDialog" Value="LicenseAgreementDialogOverwritten">1</Publish>
<Publish Dialog="LicenseAgreementDialogOverwritten" Control="Back" Event="NewDialog" Value="WelcomeDlg">1</Publish>
<Publish Dialog="LicenseAgreementDialogOverwritten" Control="Next" Event="NewDialog" Value="ProductKeyCheckDialog">LicenseAcceptedOverwritten = "1" AND NOT OLDER_VERSION_FOUND</Publish>
<Publish Dialog="InstallDirDlg" Control="Back" Event="NewDialog" Value="ProductKeyCheckDialog">1</Publish>
<!-- END UI LOGIC FOR CLEAN INSTALLER –>
<!-- code to be discussed in later posts -->
</UI>
For a thing that should be simple for the end developer to do, localization can be a bit advanced for the novice WiXer. Hope this post makes the journey easier and that next versions of WiX improve this process.
WiX 3 tutorial by Mladen Prajdić navigation
Tuesday, February 23, 2010
In previous posts I’ve shown you our SuperForm test application solution structure and how the main wxs and wxi include file look like. In this post I’ll show you how to automate inclusion of files to install into your build process. For our SuperForm application we have a single exe to install. But in the real world we have 10s or 100s of different files from dll’s to resource files like pictures. It all depends on what kind of application you’re building. Writing a directory structure for so many files by hand is out of the question. What we need is an automated way to create this structure. Enter Heat.exe.
Heat is a command line utility to harvest a file, directory, Visual Studio project, IIS website or performance counters. You might ask what harvesting means? Harvesting is converting a source (file, directory, …) into a component structure saved in a WiX fragment (a wxs) file.
There are 2 options you can use:
- Create a static wxs fragment with Heat and include it in your project. The pro of this is that you can add or remove components by hand. The con is that you have to do the pro part by hand. Automation always beats manual labor.
- Run heat command line utility in a pre-build event of your WiX project. I prefer this way. By always recreating the whole fragment you don’t have to worry about missing any new files you add. The con of this is that you’ll include files that you otherwise might not want to.
There is no perfect solution so pick one and deal with it. I prefer using the second way. A neat way of overcoming the con of the second option is to have a post-build event on your main application project (SuperForm.MainApp in our case) to copy the files needed to be installed in a special location and have the Heat.exe read them from there. I haven’t set this up for this tutorial and I’m simply including all files from the default SuperForm.MainApp \bin directory.
Remember how we created a System Environment variable called SuperFormFilesDir? This is where we’ll use it for the first time. The command line text that you have to put into the pre-build event of your WiX project looks like this:
"$(WIX)bin\heat.exe" dir "$(SuperFormFilesDir)" -cg SuperFormFiles -gg -scom -sreg -sfrag -srd -dr INSTALLLOCATION -var env.SuperFormFilesDir -out "$(ProjectDir)Fragments\FilesFragment.wxs"
After you install WiX you’ll get the WIX environment variable. In the pre/post-build events environment variables are referenced like this: $(WIX). By using this you don’t have to think about the installation path of the WiX. Remember: for 32 bit applications Program files folder is named differently between 32 and 64 bit systems. $(ProjectDir) is obviously the path to your project and is a Visual Studio built in variable.
You can view all Heat.exe options by running it without parameters but I’ll explain some that stick out the most.
- dir "$(SuperFormFilesDir)": tell Heat to harvest the whole directory at the set location. That is the location we’ve set in our System Environment variable.
- –cg SuperFormFiles: the name of the Component group that will be created. This name is included in out Feature tag as is seen in the previous post.
- -dr INSTALLLOCATION: the directory reference this fragment will fall under. You can see the top level directory structure in the previous post.
- -var env.SuperFormFilesDir: the name of the variable that will replace the SourceDir text that would otherwise appear in the fragment file.
- -out "$(ProjectDir)Fragments\FilesFragment.wxs": the full path and name under which the fragment file will be saved.
If you have source control you have to include the FilesFragment.wxs into your project but remove its source control binding. The auto generated FilesFragment.wxs for our test app looks like this:
<?xml version="1.0" encoding="utf-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
<Fragment>
<ComponentGroup Id="SuperFormFiles">
<ComponentRef Id="cmp5BB40DB822CAA7C5295227894A07502E" />
<ComponentRef Id="cmpCFD331F5E0E471FC42A1334A1098E144" />
<ComponentRef Id="cmp4614DD03D8974B7C1FC39E7B82F19574" />
<ComponentRef Id="cmpDF166522884E2454382277128BD866EC" />
</ComponentGroup>
</Fragment>
<Fragment>
<DirectoryRef Id="INSTALLLOCATION">
<Component Id="cmp5BB40DB822CAA7C5295227894A07502E" Guid="{117E3352-2F0C-4E19-AD96-03D354751B8D}">
<File Id="filDCA561ABF8964292B6BC0D0726E8EFAD" KeyPath="yes" Source="$(env.SuperFormFilesDir)\SuperForm.MainApp.exe" />
</Component>
<Component Id="cmpCFD331F5E0E471FC42A1334A1098E144" Guid="{369A2347-97DD-45CA-A4D1-62BB706EA329}">
<File Id="filA9BE65B2AB60F3CE41105364EDE33D27" KeyPath="yes" Source="$(env.SuperFormFilesDir)\SuperForm.MainApp.pdb" />
</Component>
<Component Id="cmp4614DD03D8974B7C1FC39E7B82F19574" Guid="{3443EBE2-168F-4380-BC41-26D71A0DB1C7}">
<File Id="fil5102E75B91F3DAFA6F70DA57F4C126ED" KeyPath="yes" Source="$(env.SuperFormFilesDir)\SuperForm.MainApp.vshost.exe" />
</Component>
<Component Id="cmpDF166522884E2454382277128BD866EC" Guid="{0C0F3D18-56EB-41FE-B0BD-FD2C131572DB}">
<File Id="filF7CA5083B4997E1DEC435554423E675C" KeyPath="yes" Source="$(env.SuperFormFilesDir)\SuperForm.MainApp.vshost.exe.manifest" />
</Component>
</DirectoryRef>
</Fragment>
</Wix>
The $(env.SuperFormFilesDir) will be replaced at build time with the directory where the files to be installed are located. There is nothing too complicated about this. In the end it turns out that this sort of automation is great!
There are a few other ways that Heat.exe can compose the wxs file but this is the one I prefer. It just seems the clearest. Play with its options to see what can it do. It’s one awesome little tool.
WiX 3 tutorial by Mladen Prajdić navigation
Wednesday, February 17, 2010
In the previous post we’ve taken a look at the WiX solution/project structure and project properties. We’re still playing with our super SuperForm application and today we’ll take a look at the general parts of the main wxs file, SuperForm.wxs, and the wxi include file. For wxs file we’ll just go over the general description of what each part does in the code comments. The more detailed descriptions will be in future posts about features themselves.
WXI include file
Include files are exactly what their name implies. To include a wxi file into the wxs file you have to put the wxi at the beginning of each .wxs file you wish to include it in. If you’ve ever worked with C++ you can think of the include files as .h files. For example if you include SuperFormVariables.wxi into the SuperForm.wxs, the variables in the wxi won’t be seen in FilesFragment.wxs or RegistryFragment.wxs. You’d have to include it manually into those two wxs files too.
For preprocessor variable $(var.VariableName) to be seen by every file in the project you have to include them in the WiX project properties->Build->“Define preprocessor variables” textbox.
This is why I’ve chosen not to go this route because in multi developer teams not everyone has the same directory structure and having a single variable would mean each developer would have to checkout the wixproj file to edit the variable. This is pretty much unacceptable by my standards. This is why we’ve added a System Environment variable named SuperFormFilesDir as is shown in the previous Wix Tutorial post. Because the FilesFragment.wxs is autogenerated on every project build we don’t want to change it manually each time by adding the include wxi at the beginning of the file. This way we couldn’t recreate it in each pre-build event.
<?xml version="1.0" encoding="utf-8"?>
<Include>
<!--
Versioning. These have to be changed for upgrades.
It's not enough to just include newer files.
-->
<?define MajorVersion="1" ?>
<?define MinorVersion="0" ?>
<?define BuildVersion="0" ?>
<!-- Revision is NOT used by WiX in the upgrade procedure -->
<?define Revision="0" ?>
<!-- Full version number to display -->
<?define VersionNumber="$(var.MajorVersion).$(var.MinorVersion).$(var.BuildVersion).$(var.Revision)" ?>
<!--
Upgrade code HAS to be the same for all updates.
Once you've chosen it don't change it.
-->
<?define UpgradeCode="YOUR-GUID-HERE" ?>
<!--
Path to the resources directory. resources don't really need to be included
in the project structure but I like to include them for for clarity
-->
<?define ResourcesDir="$(var.ProjectDir)\Resources" ?>
<!--
The name of your application exe file. This will be used to kill the process when updating
and creating the desktop shortcut
-->
<?define ExeProcessName="SuperForm.MainApp.exe" ?>
</Include>
For now there’s no way to tell WiX in Visual Studio to have a wxi include file available to the whole project, so you have to include it in each file separately.
Only variables set in “Define preprocessor variables” or System Environment variables are accessible to the whole project for now.
The main WXS file: SuperForm.wxs
We’ll only take a look at the general structure of the main SuperForm.wxs and not its the details. We’ll cover the details in future posts. The code comments should provide plenty info about what each part does in general.
Basically there are 5 major parts. The update part, the conditions and actions part, the UI install sequence, the directory structure and the features we want to include.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Add xmlns:util namespace definition to be able to use stuff from WixUtilExtension dll-->
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi" xmlns:util="http://schemas.microsoft.com/wix/UtilExtension">
<!-- This is how we include wxi files -->
<?include $(sys.CURRENTDIR)Includes\SuperFormVariables.wxi ?>
<!--
Id="*" is to enable upgrading. * means that the product ID will be autogenerated on each build.
Name is made of localized product name and version number.
-->
<Product Id="*" Name="!(loc.ProductName) $(var.VersionNumber)" Language="!(loc.LANG)" Version="$(var.VersionNumber)" Manufacturer="!(loc.ManufacturerName)" UpgradeCode="$(var.UpgradeCode)">
<!-- Define the minimum supported installer version (3.0) and that the install should be done for the whole machine not just the current user -->
<Package InstallerVersion="300" Compressed="yes" InstallScope="perMachine"/>
<Media Id="1" Cabinet="media1.cab" EmbedCab="yes" />
<!-- Upgrade settings. This will be explained in more detail in a future post -->
<Upgrade Id="$(var.UpgradeCode)">
<UpgradeVersion OnlyDetect="yes" Minimum="$(var.VersionNumber)" IncludeMinimum="no" Property="NEWER_VERSION_FOUND" />
<UpgradeVersion Minimum="0.0.0.0" IncludeMinimum="yes" Maximum="$(var.VersionNumber)" IncludeMaximum="no" Property="OLDER_VERSION_FOUND" />
</Upgrade>
<!-- Reference the global NETFRAMEWORK35 property to check if it exists -->
<PropertyRef Id="NETFRAMEWORK35"/>
<!--
Startup conditions that checks if .Net Framework 3.5 is installed or if
we're running the OS higher than Windows XP SP2.
If not the installation is aborted.
By doing the (Installed OR ...) property means that this condition will only
be evaluated if the app is being installed and not on uninstall or changing
-->
<Condition Message="!(loc.DotNetFrameworkNeeded)">
<![CDATA[Installed OR NETFRAMEWORK35]]>
</Condition>
<Condition Message="!(loc.AppNotSupported)">
<![CDATA[Installed OR ((VersionNT >= 501 AND ServicePackLevel >= 2) OR (VersionNT >= 502))]]>
</Condition>
<!--
This custom action in the InstallExecuteSequence is needed to
stop silent install (passing /qb to msiexec) from going around it.
-->
<CustomAction Id="NewerVersionFound" Error="!(loc.SuperFormNewerVersionInstalled)" />
<InstallExecuteSequence>
<!-- Check for newer versions with FindRelatedProducts and execute the custom action after it -->
<Custom Action="NewerVersionFound" After="FindRelatedProducts">
<![CDATA[NEWER_VERSION_FOUND]]>
</Custom>
<!-- Remove the previous versions of the product -->
<RemoveExistingProducts After="InstallInitialize"/>
<!-- WixCloseApplications is a built in custom action that uses util:CloseApplication below -->
<Custom Action="WixCloseApplications" Before="InstallInitialize" />
</InstallExecuteSequence>
<!-- This will ask the user to close the SuperForm app if it's running while upgrading -->
<util:CloseApplication Id="CloseSuperForm" CloseMessage="no" Description="!(loc.MustCloseSuperForm)"
ElevatedCloseMessage="no" RebootPrompt="no" Target="$(var.ExeProcessName)" />
<!-- Use the built in WixUI_InstallDir GUI -->
<UIRef Id="WixUI_InstallDir" />
<UI>
<!-- These dialog references are needed for CloseApplication above to work correctly -->
<DialogRef Id="FilesInUse" />
<DialogRef Id="MsiRMFilesInUse" />
<!-- Here we'll add the GUI logic for installation and updating in a future post-->
</UI>
<!-- Set the icon to show next to the program name in Add/Remove programs -->
<Icon Id="SuperFormIcon.ico" SourceFile="$(var.ResourcesDir)\Exclam.ico" />
<Property Id="ARPPRODUCTICON" Value="SuperFormIcon.ico" />
<!-- Installer UI custom pictures. File names are made up. Add path to your pics. –>
<!--
<WixVariable Id="WixUIDialogBmp" Value="MyAppLogo.jpg" />
<WixVariable Id="WixUIBannerBmp" Value="installBanner.jpg" />
-->
<!-- the default directory structure -->
<Directory Id="TARGETDIR" Name="SourceDir">
<Directory Id="ProgramFilesFolder">
<Directory Id="INSTALLLOCATION" Name="!(loc.ProductName)" />
</Directory>
</Directory>
<!--
Set the default install location to the value of
INSTALLLOCATION (usually c:\Program Files\YourProductName)
-->
<Property Id="WIXUI_INSTALLDIR" Value="INSTALLLOCATION" />
<!-- Set the components defined in our fragment files that will be used for our feature -->
<Feature Id="SuperFormFeature" Title="!(loc.ProductName)" Level="1">
<ComponentGroupRef Id="SuperFormFiles" />
<ComponentRef Id="cmpVersionInRegistry" />
<ComponentRef Id="cmpIsThisUpdateInRegistry" />
</Feature>
</Product>
</Wix>
For more info on what certain attributes mean you should look into the WiX Documentation.
WiX 3 tutorial by Mladen Prajdić navigation
Thursday, February 11, 2010
This is the first post about building a WiX 3 installer from zero. The reason I’ve decided to write this WiX series is that the good sources are quite hard to find and examples even harder.
The finished installer (at the end of the series) will be localized in 2 languages, have localized EULA check, product key validation via a custom action, updating (and killing the running app when updating) and wrapping both localized MSI’s into a bootstrapper that will enable you to choose the installation language and install prerequisites like .Net framework 3.5. Those are the general features and we’ll well dive into each feature in a separate post.
We’ll be using WiX 3.0.5419.0 in Visual Studio 2008. You can download WiX at the SourceForge Wix Site. I’m expecting that you’re familiar with basic WiX operations and how to make the WiX project.
To help you with your WiX development be sure to use the ORCA tool (download via Win SDK or directly from here) for viewing/editing MSI tables and default MSI logging capabilities like “msiexec /i SuperForm.Installer.msi /l*v SuperFormLog.txt” which will write a very detailed log of what it’s doing at each step.
The application we’ll be installing is called SuperForm which solves all your label’s text color needs by using the awesome action of pressing a button.
The first thing we have to do is to create a new System Environment variable SuperFormFilesDir containing the path to the directory where you keep the files you’ll install on the users computer. We’ll be using this variable in the automated building of wxs fragment file that holds the correct directory/files structure. This is my preference in environments with multiple developers. It is highly unlikely that all developers have the same directory structure. If you work alone you can also take the “preprocessor variables” route described below in Project Properties –> Build. I’ll be using the System Environment variable approach.
Solution/Project structure
We have three projects of which the most important for us is the WiX project. Logically the project is divided into 7 folders and 1 main file.
- CustomActions: stores WiX fragments that define different custom actions (CA’s). You can have 1 fragment per CA or all CA’s under 1 fragment. I prefer fragment per CA.
- CustomDialogs: stores WiX fragments that define our custom built dialogs. there are 2 we’ll have: The overwritten existing EULA dialog and fully custom product key check dialog.
- Fragments: stores WiX fragments that are either auto generated by Heat.exe directory harvesting or manually built like inserting some values into the registry.
- Includes: stores WiX include files that hold pre processor variables. Wxi files must be included at the top of each wxs file you’re using variables in.
- Lang: stores localization stuff
- de-de: German installer. Stores German EULA, custom localization file and official German WiX translations.
- en-us: US English installer. Stores English EULA and custom localization file.
- Packages: output for the actual MSI’s. This replaces the bin folder.
- de-de: stores the German MSI installer.
- en-us: stores the English MSI installer.
- Resources: stores different resources like icons, jpg’s, etc… used in the installer.
- SuperForm.wxs: master installer files where all the magic happens.
We also need to include some references:
- SuperForm.CustomAction: needed to run our custom actions stored in the SuperForm.CustomAction.dll project
- WixNetFxExtension: needed for conditional check id .Net 3.5 is installed and abort if not.
- WixUIExtension: needed to include and modify any UI elements.
- WixUtilExtension: needed to run CloseApplication built in custom action.
All non WiX files (others than wsx, wxi, wxl) don’t really need to be included in the project but i like to include them for clarity. This way we see exactly what is in the installer from the project itself.
SuperForm.Install WiX project properties:
- Installer: No changes
- Build:
- Change “Output Path” to “Packages\”. This is needed to respect the project structure. You could use the default ($Configuration)\bin but I prefer not to. The ($Configuration) part is only useful if you’re building both 32 and 64 bit installers.
- If you don’t like adding a new System Environment variable you can do this instead. To “Define preprocessor variables” add the SuperFormFilesDir=pathToTheFolderWhereTheFilesToBeInstalledAre;”. Variables are delimited by semicolons (;).
- Build Events:
- To “Pre-build event” add "$(WIX)bin\heat.exe" dir "$(SuperFormFilesDir)" -cg SuperFormFiles -gg -scom -sreg -sfrag -srd -dr INSTALLLOCATION -var env.SuperFormFilesDir -out "$(ProjectDir)Fragments\FilesFragment.wxs". Heat.exe makes a wxs file with proper directory/file structure from the specified directory. We’ll take a detailed look into this in a future post.
- You can skip this for now but remember to add it later! To “Post-build event” add “$(SolutionDir)BootstrapperBuild.bat $(TargetDir) $(TargetFileName) $(SolutionDir)”. This will be used at the end to build the bootstrapper.
- Paths: No changes
- Tool settings: No changes
Resources for WiX development
- WiX Documentation – official docs, what else.
- Rob Mensching – creator and main contributor of WiX.
- WiX tutorial by Gábor DEÁK JAHN – this is an excellent tutorial that covers all aspects of WiX!
- From MSI to WiX by Alex Shevchuk – a Technet series on how to build an installer with WiX.
- WiX Mindcapers wiki – great wiki with lots of great stuff
- Joy of Setup by Bob Arnson – a setup blog with lots of good info.
- WiX tricks and best practices – StackOverflow wiki with some more links to best practices with WiX.
WiX 3 tutorial by Mladen Prajdić navigation
Thursday, January 21, 2010
Execution plans! Don’t you just love them? They’re the first thing you look at when tuning a query or a stored procedure. But what do you do if you have a gigantic query play with 10’s of nodes? how do you find the most complex one? Where do you start?
What I’ve usually done in situations like that is to first find the node/statement with the highest cost and work from there. Now the highest cost can be IO, CPU, Row number or the good old SubTree cost which gives us a number based on all those counters. Let’s see how.
With SQL Server 2005 we got the option to show our execution plans in XML in two different ways: using the SET SHOWPLAN_XML ON option or choosing the Show Execution Plan XML option in the execution plan context menu.
These are the two queries we’ll work with:
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader SOH
SELECT *
FROM Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD on SOH.SalesOrderID = SOD.SalesOrderID
They generate pretty simple plans that are perfect for our demo.
This is a heavily trimmed part of the upper queries execution plan XML:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.2531.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="..." StatementId="1" StatementCompId="1" ... >
<StatementSetOptions ... />
<QueryPlan CachedPlanSize="8" CompileTime="1" CompileCPU="1" CompileMemory="152">
<RelOp ... > ... </RelOp>
</QueryPlan>
</StmtSimple>
<StmtSimple StatementText="..." StatementId="2" StatementCompId="2" ... >
<StatementSetOptions ... />
<QueryPlan CachedPlanSize="40" CompileTime="6" CompileCPU="6" CompileMemory="512">
<RelOp ... > ... </RelOp>
</Merge>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
First thing to do is to remove the xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan attribute part from the ShowPlanXML node. This is needed so that we don’t have problems with namespaces further on which simplifies the querying. You can leave the xmlns attribute but then you’ll have to use WITH XMLNAMESPACES to query it properly. From this XML we can extract the costliest nodes and statements with the following code:
-- notice the removed
-- xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan
-- attribute
declare @xml xml = '
<ShowPlanXML Version="1.1" Build="10.0.2531.0">
... Shortened XML data...
</ShowPlanXML>'
-- go through all the execution plan nodes, get the attributes and sort on them
SELECT c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') as EstimatedTotalSubtreeCost,
c.value('.[1]/@EstimateRows', 'nvarchar(max)') as EstimateRows,
c.value('.[1]/@EstimateIO', 'nvarchar(max)') as EstimateIO,
c.value('.[1]/@EstimateCPU', 'nvarchar(max)') as EstimateCPU,
-- this returns just the node xml for easier inspection
c.query('.') as ExecPlanNode
FROM -- this returns only nodes with the name RelOp even if they are children of children
@xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC
-- go through all the SQL Statements, get the attributes and sort on them
SELECT c.value('.[1]/@StatementText', 'nvarchar(max)') as StatementText,
c.value('.[1]/@StatementSubTreeCost', 'nvarchar(max)') as StatementSubTreeCost,
c.value('.[1]/@StatementEstRows', 'nvarchar(max)') as StatementEstimateRows,
c.value('.[1]/@StatementOptmLevel', 'nvarchar(max)') as StatementOptimizationLevel,
-- this returns just the statement xml for easier inspection
c.query('.') as ExecPlanNode
FROM -- this returns only nodes with the name StmtSimple
@xml.nodes('//child::StmtSimple') T(c)
ORDER BY StatementSubTreeCost DESC
In the results from the upper queries we can see that the ExecPlanNode is the XML of the processed node. By clicking on it we can inspect just that single node for further details.
We can see that the results are nicely sorted by cost and finding most expensive parts of your plan is easy. This may not look like a big deal but imagine having a stored procedure that generates 50 complex execution plans. Try going through that by hand and see how long you last.
Tuesday, January 12, 2010
Adam Machanic launced his second TSQL tuesday: Invitation for T-SQL Tuesday #002: A Puzzling Situation. The theme is to show something interesting and not expected by the first look. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.
Ok here we go. Let me ask you this: How many times will the subquery in the below SQL statement be executed?
SELECT (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM Sales.SalesOrderHeader SOH
ORDER BY c DESC
How about the subquery in this in the below SQL statement be executed? It’s the same query only we’re repeating the subquery in the order by
SELECT (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM Sales.SalesOrderHeader SOH
ORDER by (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) DESC
If you said once you’d be wrong. At first look I also thought it would run once because SQL server query optimizer would be smart enough to realize we’re ordering by the same values as we’re selecting. However this isn’t a case. Let’s take a look at the IO cost of both queries:
Query #1:
-- Table 'SalesOrderHeader'. Scan count 1, logical reads 703
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 1238
Query #2:
We can see the table SalesOrderDetail being scanned twice. Once for select part and once for order by part.
-- Table 'SalesOrderHeader'. Scan count 1, logical reads 703
-- Table 'SalesOrderDetail'. Scan count 2, logical reads 2476 <- READ TWICE! ONCE FROM SELECT AND ONCE FROM ORDER BY!
Of course the second example isn’t a way to write SQL but it sure is interesting how stuff works.
Tuesday, December 08, 2009
A few days ago Adam Machanic proposed a great idea about a T-SQL Tuesday. Every Tuesday there would be a topic bloggers all around would post about. Chosen as the first topic was the date and time stuff in SQL Server.
Because there’s already all this great content out there I’m not going to repeat it but I’m going to put together a ton of datetime resources from SQLTeam forums and blogs as a resource you can use in the future.
Michael Valentine Jones
MVJ as we like to call him in the forums has a thing for datetime manipulation. He has some really good helper functions he shared with the world.
Peter Larsson
Peter is an MVP who has the most amazing ideas for writing SQL. If you want performance talk to him.
Jeff Smith
Sometimes I think that Jeff is datetime machine. His posts are always high quality and simple to understand. He’s also an MVP who I hope will start blogging more again soon.
K H Tan
K H Tan is a smart fellow from Singapore who’s always glad to help. Shame he doesn’t blog.
- fn_next_business_day - Calculates the next x business day (excluding Sat, Sun). Basically a businessdateadd() for dateadd()
Mladen Prajdić
In each post I learned something new or got a nice idea so it’s also worth reading the comments. On some issues my views have changed while on others they stayed the same.
Tuesday, December 01, 2009
A while back I was thinking about SQL Server’s capabilities of returning data as XML with FOR XML and it occurred to me that maybe returning 10.000 results as xml would be faster that the way SQL server returns them now in Tabular Data Stream (TDS). After 10 seconds I dismissed it as nonsense because if that were so the xml would be used by default. But it piqued my curiosity about what would be the actual size overhead of returning XML compared to TDS. I knew there was overhead but i didn’t think it would be so high.
So i decided to test it. I tried 2 queries which got same data from AdventureWorks.Person.Address table and for each I just changed the TOP N value to: 1, 10, 100, 1000, 10000 and no TOP N to get all rows.
Query 1:
SELECT * FROM Person.Address
Query 2:
Here I used alias A and root P to minimize the XML size. The AUTO option creates an XML item for each row and columns are treated as attributes the PATH wraps the row items with the root P item. This appears to be the smallest XML you can generate with unchanged column names.
SELECT * FROM Person.Address as A FOR XML AUTO, ROOT('P')
These are the results in table and graph form.
| Rows |
TDS size (Kb) |
XML size (Kb) |
| 1 |
0.36 |
0.53 |
| 10 |
1.23 |
3.82 |
| 100 |
10.18 |
37.59 |
| 1000 |
104.14 |
382.3 |
| 10000 |
1024.71 |
3839.93 |
| 19614 (All) |
2008.28 |
7526.28 |
We can see that compared to TDS the XML rises much more exponentially with increasing row numbers.
|
 |