July 2011 - Posts
We are going on an research trip to the Great British Beer Festival.
If you fancy coming along then sign up here. The first 5 people will get free tickets (the rest will have to buy their own entry)
Its a great evening, especially if you like beer
Its also my Birthday so the more the merrier
I was in Denmark earlier this year speaking at Miracle Open World and I heard about a guy that had done a session the year before about the internals of PDF files. This year he was doing the one on MDF files. This was a must see, so I popped along.
Mark S. Rasmussen is the guy in question.
What he had done I found amazing. He wasn’t repeating the same old stuff from books online or from some blog. Mark had dug deep into the MDF file in such a way that he had mapped the structures to C# structures so that he could query the data for a table. For instance he wrote code to break up a row into its constituent parts i.e. the null bitmap, the fixed columns the variable columns etc.
The main reason I found this fascinating is that for a long time I’ve been speaking about SQL Server just being code and not “magic”. It has to read the data from a page, it has to find the row on the page, it has to read an index page to find out where to find the data page etc. It has to do all this with C code.
SQL Server is just code.
What Marks work highlights is how complex it is to get store data and get data back, and marks code doesn’t implement any consistency stuff like locks and latches.
If you’ve followed Mark on twitter http://twitter.com/improvedk or read his blog you may have seen him talking about how he has developed his code over the last few months. Just look at some recent posts
He has made the project available on Github https://github.com/improvedk/OrcaMDF
but even better he is doing a training day on this at SQLBits SQL_Server_Storage_Engine_and_MDF_File_Internals
This is not just about writing an MDF parser in C# this is more about understanding the internals of how data is stored.
If you understand the internals then it helps you to understand the significance of using (Nolock), how forward pointers in heaps affect performance, what happens when you add a column, what happens when you remove a column.
Once you know the internals then everything else makes much more sense and falls into place.
I reckon in doing this project Mark now knows more than anyone that hasn’t been on the product team.
So if you are serious about SQL Server then this really is a must.
So get registered and attend Mark’s session http://sqlbits.com/information/Registration.aspx
If you have ever tried installing a product 18 months after it was released you have probably found the base install very quick and then then it takes an age to apply all the hotfixes, service packs and QFEs. Service packs for visual studio have always been awful. Back in SQL 2000 days I think one of the scripts in the service pack took a very long time to run.
Well the good news is that with CTP3 of Denali automatic slipstreaming is here and they want feedback.
Whats more you can apparently include CUs, SPs, Hotfixes.
Even better is that you can download them to a file share and have setup point at the file share
And finally they are testing the process now and not when the first CU comes out.
What I can’t tell from the blog post is what happens with existing installations. What is the related mechanism for applying a set of fixes to an existing install.
The feature is called Product Updates and you can read more on MSDN here http://msdn.microsoft.com/en-us/library/hh231670(v=SQL.110).aspx
Other than bad code plan stability is one of the main reasons for performance problems I see in client systems. This is often due to bad estimates, bad stats etc you can watch my SQLbits session on when a query plan goes bad
for more information.
One of the solutions is the use of hints. I only use hints only in extreme cases when the data skew is such that the optimiser doesn’t have a chance or you can’t rely on stats being up to date.
Whats new with SQL Server 2008 R2 SP1 (http://support.microsoft.com/kb/2527041) (list of bug fixes here http://support.microsoft.com/kb/2528583) is an addition to the forceseek hint which allows you to force the index you wish to seek on, and the columns to use. I can see that to be of use if you have two access paths to the table and the stats and estimates reckon that you should use one path when you should really use the other path.
I’m not sure I’ve come across a scenario where I need this but clearly someone has.
In addition a FORCESCAN has been added, I wonder if thats been added to help support fasttrack type work configurations.
Here is the indepth section from BOL (http://msdn.microsoft.com/en-us/library/ms181714.aspx) about FORCESEEK.
FORCESEEK [ (index_value(index_column_name
Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view. Starting with SQL Server 2008 R2 SP1, index parameters can also be specified. Specifying FORCESEEK with index parameters is similar to using FORCESEEK with an INDEX hint. However, you can achieve greater control over the access path used by the query optimizer by specifying both the index to seek on and the index columns to consider in the seek operation. index_value
Is the index name or index ID value. The index ID 0 (heap) cannot be specified. To return the index name or ID, query the sys.indexes catalog view. index_column_name
Is the name of the index column to include in the seek operation. The query optimizer considers only index seek operations through the specified index using at least the specified index columns. The optimizer may consider additional columns if needed. For example, if a nonclustered index is specified, the optimizer may choose to use clustered index key columns in addition to the specified columns.
The FORCESEEK hint can be specified in the following ways.
Without an index or INDEX hint
FROM dbo.MyTable WITH (FORCESEEK)
The query optimizer considers only index seek operations to access the table or view through any relevant index.
Combined with an INDEX hint
FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))
The query optimizer considers only index seek operations to access the table or view through the specified index.
Parameterized by specifying an index and index columns
FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))
The query optimizer considers only index seek operations to access the table or view through the specified index using at least the specified index columns.
When using the FORCESEEK hint (with or without index parameters), consider the following guidelines.
The hint can be specified as a table hint or as a query hint. For more information about query hints, see Query Hints (Transact-SQL).
To apply FORCESEEK to an indexed view, the NOEXPAND hint must also be specified.
The hint can be applied at most once per table or view.
The hint cannot be specified for a remote data source. Error 7377 is returned when FORCESEEK is specified with an index hint and error 8180 is returned when FORCESEEK is used without an index hint.
If FORCESEEK causes no plan to be found, error 8622 is returned.
When FORCESEEK is specified with index parameters, the following guidelines and restrictions apply.
The hint cannot be specified in combination with either an INDEX hint or another FORCESEEK hint.
At least one column must be specified and it must be the leading key column.
Additional index columns can be specified, however, key columns cannot be skipped. For example, if the specified index contains the key columns a, b, and c, valid syntax includes FORCESEEK (MyIndex (a)) and FORCESEEK (MyIndex (a, b). Invalid syntax includes FORCESEEK (MyIndex (c)) and FORCESEEK (MyIndex (a, c).
The order of column names specified in the hint must match the order of the columns in the referenced index.
Columns that are not in the index key definition cannot be specified. For example, in a nonclustered index, only the defined index key columns can be specified. Clustered key columns that are automatically included in the index cannot be specified, but may be used by the optimizer.
Modifying the index definition (for example, by adding or removing columns) may require modifications to the queries that reference that index.
The hint prevents the optimizer from considering any spatial or XML indexes on the table.
The hint cannot be specified in combination with the FORCESCAN hint.
For partitioned indexes, the partitioning column implicitly added by SQL Server cannot be specified in the FORCESEEK hint. For more information, see Special Guidelines for Partitioned Indexes.
Specifying FORCESEEK with index parameters limits the number of plans that can be considered by the optimizer more than when specifying FORCESEEK without parameters. This may cause a "Plan cannot be generated" error to occur in more cases. In a future release, internal modifications to the optimizer may allow more plans to be considered. For more information, see Using the FORCESEEK Table Hint.
The SQLBits show is has started moving and you can now vote on the sessions you’d like to see at
Not only that you can also register for the conference here
Don’t forget that there is early bird pricing until 26th August so get you registration in soon. What’s more for Brighton we were full before the early bird period finished so don’t leave it until the last minute.
We’ve got lots of exciting things planned for SQLBits 9, its going to be the best event ever. Keep posted for details.
Today is your last day to submit sessions for SQLBits 9 in Liverpool on the 29th Sept – 1st October.
So far we have 93 sessions from 49 speakers. With 60 speaking slots plus lightening talks we have plenty of opportunities for people to speak.
To submit a session complete your profile and agree to the speaker terms for SQLBits 9
and then submit your sessions here