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.
|
 |
Monday, November 02, 2009
Due to a bug i haven’t anticipated, a whole load of users with non English SSMS couldn’t install the latest SSMS Tools Pack 1.7.
The SSMS Tools Pack 1.7.5.1 fixes this as well as 1 other major bug that was reported and 3 smaller ones.
The feature list hasn’t changed.
You can simply reinstall 1.7.5.1 over the 1.7.0.0.
If you’re still on 1.5 or less then you’ll have to manually uninstall it and install 1.7.5.1.
You can download the new version here.
Hopefully this will bring back joy to everyone :)
Monday, October 26, 2009
Ladies and gentlemen, boys and girls, the STP is back to rock your world!
The new feature is
SQL Snippets to speed up your development.
This new version brings one completely new option, full GUI redesign, completely rewritten installer, improved error handling and reporting, bug fixes and old features improvements.
Type in the assigned shortcut, press enter or tab and get the snippet you need.
It's quick, clean and saves time!
Some of more visible improved features include:
SSMS 2008 R2 is supported but expect quirks in it due to the CTP status.
Due to a folder naming installer bug in version 1.5 please uninstall any and all previous versions before installing the 1.7 version.
Thursday, October 15, 2009
We all know how to find only duplicated rows in a table.
Since SQL Server 2005 this became really simple using a ROW_NUMBER() window function like this:
USE AdventureWorks
GO
;WITH cteDupes AS
(
-- find all rows that have the same AddressLine1 and City.
-- we consider those rows are duplicates so we partition on them
SELECT ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID) AS RN,
*
FROM Person.Address
)
SELECT *
FROM cteDupes
WHERE RN > 1
ORDER BY AddressLine1, City, AddressID
The IO for this method is great. We get only one pass through the table:
Table 'Address'. Scan count 1, logical reads 280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
However the problem with this code is that it returns only the duplicated rows and not the original row the duplicates originated from.
Good way
We want a fast way of getting the whole duplicated group. My first attempt was to use the Count(*) with OVER to get the group count in each row like this:
USE AdventureWorks
GO
;WITH cteDupes AS
(
-- find all rows that have the same AddressLine1 and City.
-- we consider those rows are duplicates so we partition on them
SELECT COUNT(*) OVER(PARTITION BY AddressLine1, City ) AS CNT,
*
FROM Person.Address
)
SELECT *
FROM cteDupes
WHERE CNT > 1
ORDER BY AddressLine1, City, AddressID
But looking at the IO this caused made my head explode and i wanted a better way.
Table 'Worktable'. Scan count 3, logical reads 117473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 1, logical reads 280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Better way
We could get the same thing by using 2 ROW_NUMBER()-ed column, one in ascending and the other on descending order like this:
USE AdventureWorks
GO
;WITH cteDupes AS
(
-- find all rows that have the same AddressLine1 and City.
-- we consider those rows are duplicates so we partition on them
SELECT ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID DESC) AS RND,
ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID) AS RNA,
*
FROM Person.Address
)
SELECT *
FROM cteDupes
-- this condition removes the rows that don’t have duplicates
-- RNA-RND = 0 gets all odd rows in the group: 1st, 3rd, etc...
-- RNA = 1 AND RND = 1 limit those rows to those groups that don’t have duplicates
-- because if there’s only one row both RNA and RND will be 1
-- by negating the condition we return the whole duplicated group
WHERE NOT (RNA-RND = 0 AND RNA = 1 AND RND = 1)ORDER BY AddressLine1, City, AddressID
This made the IO a normal single pass at the table.
Table 'Address'. Scan count 1, logical reads 280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If anyone has a better way do let me know.
Tuesday, October 06, 2009
Simply put:
I prefer using surrogate keys because natural keys are by default a subject to change which is a bad behavior for a row identifier.
But let’s dig a bit deeper into each key type to see why this is. Here’s a little table with column names that tell us what kind of a key each column is.
Surrogate keys
A surrogate key is a row identifier that has no connection to the data attributes in the row but simply makes the whole row unique. And that property is also the downside of it. Because it has no connection to the data attributes we can have two rows with the exact same data in all columns except the key column. This is usually handled at the application side and is an acceptable downside.
An example of a surrogate key is an integer identity or a GIUD unique identifier. I’ve never seen another data type being used as a surrogate key successfully. Both have their pros and cons though.
GUID unique identifier
GUID is globally unique 16 byte long data type that can have 2128 different values. This makes it ideal for scenarios with multiple server moving data from one to another like replication.
However for a key 16 bytes is really a lot. This causes less data to be available on a single data page which in turn causes extra IO activity because it has to retrieve more data pages. Another issue about it is that is causes perfect page splits in a clustered index because it has random 100% selectivity in it’s entire data type range.
Integer identity
Integer identity is either 4 byte INT with range from -2,147,483,648 to 2,147,483,647 or 8 byte BIGINT with range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. In 99.9% of cases this range is split in half because the default setting in SQL Server is to start any integer data type from 0. As this is a surrogate key this makes no sense and there’s no reason it shouldn’t start from the min value.
It is a small data type which gives it the advantage of having more data in the data pages thus needing less IO for the same amount of data. Unlike the GIUD unique identifier the integer identity has ever increasing 100% selectivity in it’s entire data type range. This makes it a perfect candidate for a clustered because it doesn’t cause page splits. If it actually is an appropriate candidate for a clustered index is a different matter.
Its downside is that it is not ideal for multi server scenarios although it can be done by using another tinyint column identifying a location and making it a covering row identifier over ID and LocationId columns.
And remember: Never tie any business logic to the surrogate key other than simple CRUD operations.
Natural Keys
A natural key is a row identifier composed of data that uniquely describes data using its own attributes. An example of a natural key is social security number or other government issued number.
However this presents a huge problem from the physical database implementation point of view. In most databases a row identifier is usually also the basis for the clustered index and non-clustered indexes. But natural keys are by definition a subject to change. When the clustered index key is changed ALL indexes have to be rebuilt because non-clustered indexes contain the full key of the clustered index. So every time the natural key, which is also a clustered index changes, all indexes have to be rebuilt. And this is not including changing the actual data type or it’s size, jut the key value.
At this point someone might say: Yes Mladen you’re right about the theory of this but how many times have you seen the Natural key really change? Well so far I’ve seen it 2 times both with heavy consequences. It was 2 times too many.
Natural Key Fail Case 1:
It was a standard customer, product, order type of application. The key in this case was the 7 char long customer ID. It was a mix of first 3 letters of the customer name plus 4 numbers that also had some business meaning. The company got acquired by another company and a new customer numbering was introduced. Every key in that database had to be changed. Due to fully breaking changes to the database the whole application had to be modified and the store went offline for 3 months loosing the company a lot of profit. All this wouldn’t have happened if they had used surrogate keys.
Natural Key Fail Case 2:
This one was even more far reaching. In Slovenia (my home country) we have something called a Tax ID. This is an ID that is unique for companies and individuals so every person and every company has one for tax purposes. Many systems in Slovenia used it as the natural never changing key which sounded like a reasonable thing at the time. And it was so for over 30 years. Applications came and went. But in 2004 Slovenia entered into the European Union. So we had to modify the TaxId to European standards which means that every application using it had to be changed. I know of at least one company that went out of business because of this change. Again had they used a surrogate key the only change would be the length of the TaxId column.
Because of all this I’ve come to the prefer the surrogate keys in majority of cases.
Hopefully this gives you some insight why surrogates are in my opinion better suited as row identifiers. Although whichever you choose is still a matter of common sense and your business problem. The answer is always “It depends”.
Monday, August 24, 2009
Today I had an interesting problem.
If I rebuilt my solution every thing would succeed but after that if i pressed F5 (build) the whole solution would get built again but it would error out. Now this doesn’t make any sense because the difference between Build and Rebuild is that Rebuild always compiles and links all files + all dependencies, while Build only compiles and links files that have changed since the last build. So everything should be fine. The thing about our solution is that the we have some post build events that do some IL merge, etc… the problem was that rebuild ran those events while just build didn’t. So on build IL merge didn’t happen and I got the error when I pressed F5.
It turned out that one dependant DLL that was included everywhere had a create date set to year 2049 and because of that the build process wanted to build everything every time since it thought that it had changed since it was in the future. Talk about going back to the future :)
Moral of the story: If your build always builds your whole solution check for future dated files.
Wednesday, August 05, 2009
On September 2nd this years biggest online SQL Server related event will begin.
It’s the 24 hours of PASS.
The 24 one-hour presentations will begin at 00:00 GMT (UTC) on September 2, 2009 and it will last full 24 hours.
The speaker list is impressive and topics are something to be desired.
So on September 2nd prepare to have a sleepless day.
Gentlemen, start your SQL engines!
Tuesday, July 28, 2009
The Running Totals problem is as old as accounting. In SQL Server there are different ways of calculating it and the general consensus is that it is one of the few problems best handled with a cursor. I still say it’s best handled in the presentation layer though. Being the SQL geek I am I can’t accept a problem in SQL Server which has a cursor for a solution (just kidding).
Note that I didn’t put any indexes on the tables so we can’t rely on them for any kind of ordering. The base table has 10 million rows and we’re going to aggregate on a 1 million row subset. For shortness sake the test table consists of a single column that we will aggregate on. And if you really need to output running totals of 1 million rows on the fly you’re doing something wrong. :)
IF OBJECT_ID('tempdb..#baseTable') IS NOT NULL
DROP TABLE #baseTable
-- create a 10 million row base table
-- we need running totals for 1 million rows out of 10 million
SELECT TOP 10000000 -- 10 million row table
ROW_NUMBER() OVER (ORDER BY t1.Number) AS Number
INTO #baseTable
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
The ordered CTE Update solution
In SQL Server 2005 Common Table Expressions (CTE) were introduced. They work like a view inside your select statement. What is cool and not well known about them is that you can update, insert and delete their results without joining them to other tables. I used a TOP (Int Max value) trick because the TOP 100 Percent syntax is considered harmful. Because of this I didn’t want to rely on it.
The next gem is the OUTPUT clause which was also introduced in SQL Server 2005. It outputs changed data from an update, delete or an insert statement into a result set so you don’t need to do another select.
First we populate the temp table with the 1 million rows subset we wish and add an extra RT column which will hold the Running total value. Next we create an ordered CTE, update it with “the direct variable and column update at the same time” trick and directly output the updated rows into the returning result set.
IF OBJECT_ID('tempdb..#testCTE') IS NOT NULL
DROP TABLE #testCTE
DECLARE @dtStart DATETIME
SELECT @dtStart = GETDATE()
-- create our temp table to return data from
SELECT TOP 1000000 Number, CAST(0 AS BIGINT) RT
INTO #testCTE
FROM #baseTable
ORDER BY Number
-- ORDER BY NEWID() -- also works just fine
-- declare helper variable
DECLARE @RT BIGINT
SELECT @RT = 0
;WITH RunningTotals AS
(
SELECT TOP (2147483647) -- put a int max value here to get all rows
Number, RT
FROM #testCTE
ORDER BY Number
-- both of there work correctly
-- ORDER BY Number DESC
-- ORDER BY NEWID()
)
UPDATE RunningTotals
SET @RT = RT = @RT + Number
OUTPUT inserted.*
SELECT DATEDIFF(s, @dtStart, GETDATE()) AS DurationInSeconds
The query ran in 8 seconds which blew my mind. I tried various orderings of the column, putting clustered and nonclustered indexes, ordering insert into #testCTE table by newid() but I couldn’t find any example where the ordering would not be honored. Even fragmenting the index didn’t break it.
If anyone could provide an example when the order by isn’t honored I’d be very pleased. Being proved wrong is kind of fun :)
UPDATE: Problems with clustered index
After playing some more and discussing this with Jeff Moden (hoped he would drop in :)) the problem with adding a clustered index is that the clustered index is always updated in the order of the clustering key. It doesn’t matter on which column it is. We can verify this easily by doing this:
SELECT TOP 1000000 Number, CAST(0 AS BIGINT) RT
INTO #testCTE
FROM #baseTable
ORDER BY Number
CREATE CLUSTERED INDEX CI_testCTE_Number ON #testCTE(Number)
If we run the upper CTE after adding a clustered index we can see that different ordering inside the CTE makes no difference since the update runs in the order of the clustering key. For this to work the #testCTE should NOT have a clustered index on it. Non clustered indexes are ok and they make no difference since the ordering inside the CTE is respected.
And please leave the running totals for the client to do. It’s its job. :)
The Cursor and Correlated subquery solutions
For completeness sake here are the cursor and correlated subquery solutions. Cursor solution took 48 seconds and I just killed the correlated subquery after 5 minutes.
-- Cursor solution
IF OBJECT_ID('tempdb..#testCursor') IS NOT NULL
DROP TABLE #testCursor
-- create our empty temp table to return data from
DECLARE @dtStart DATETIME
SELECT @dtStart = GETDATE()
SELECT TOP 1 Number, CAST(0 AS BIGINT) RT
INTO #testCursor
FROM #baseTable
WHERE 1=0
DECLARE RTCursor CURSOR FOR
SELECT TOP 1000000 Number FROM #baseTable ORDER BY Number
DECLARE @Number INT, @RT BIGINT
SELECT @RT = 0
OPEN RTCursor
WHILE (0=0) BEGIN
FETCH NEXT FROM RTCursor INTO @Number
IF @@FETCH_STATUS <> 0 BREAK
SET @RT = @RT + @Number
INSERT INTO #testCursor(Number, RT)
VALUES(@Number, @RT )
END
CLOSE RTCursor
DEALLOCATE RTCursor
SELECT *
FROM #testCursor
ORDER BY Number
SELECT DATEDIFF(s, @dtStart, GETDATE()) AS DurationInSeconds
GO
-- Correlated subquery method
SELECT TOP 1000 Number, (SELECT SUM(Number) FROM #baseTable WHERE Number <= t1.Number) AS RT
FROM #baseTable t1
ORDER BY Number
I haven’t tried the SQL CLR solution for this so if anyone has any benchmarks I’d be glad to hear them.
Thursday, July 16, 2009
Today on twitter Lori Edwards (@loriedwards) asked how can you check when was SQL Server installed with a T-SQL query. Otherwise this is pretty simple by looking at the creation time of master database (provided you never had to restore it).
But i wanted to find a nice way of doing this without resorting to any xp_ stored procedures. Of course this is possible by looking into the sys.syslogins compatibility view:
SELECT createdate as Sql_Server_Install_Date
FROM sys.syslogins
where sid = 0x010100000000000512000000 -- language neutral
-- loginname = 'NT AUTHORITY\SYSTEM' -- only English language installations
This query actually returns the creation date and time of the NT AUTHORITY\SYSTEM login which gets created when you install SQL Server. This of course won’t work if you had to restore the master database.
Pretty simple if you know where to look :))
Monday, June 15, 2009
I’ll be talking about the following topics:
What should developers know about database design so they don't have performance and logical problems? What's new in SQL Server 2008 that helps solve some business problems that sometime required "hacking" before. Concurrency design models and isolation levels.
The presentation will start on Tuesday June 16th at 2:00 PM EST / 6:00 PM UTC / 8:00 PM CET
More info at PASS Application Development SIG
UPDATE: you can view the presentation here.
Friday, June 05, 2009
There are times when you want to have one .cs file in multiple projects. However if you do Add existing item you’ll notice that the file is copied to each project’s folder. This is not what we want.
The solutions is of course pretty simple once you know where to look. In the Add existing item dialog you have to add the file as a Link as is shown on the picture:
And there you go. you can have one file in multiple projects.
Friday, May 29, 2009
I’ve written two articles on SQLTeam.com about a great new feature in SQL Server 2008 called Extended Events. They are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events.
1. Introduction to SQL Server 2008 Extended Events
This is an introductory article where we take a look at performance troubleshooting and system monitoring and what they lack in previous SQL Server versions. After that we get to know the Extended Events architecture, new terminology and we try them out with a simple example.
2. Advanced SQL Server 2008 Extended Events with Examples
Part 2 of the series takes a look at some performance considerations we must be aware of when using Extended Events like synchronous or asynchronous target target, predicate short circuiting and event action size. After we take a look at 7 examples that show a different way we can use them to troubleshoot our system. Examples are meant to show Extended Events power and give some ideas on how to use them for more advanced monitoring.
