May 2006 - Posts
Microsoft are releasing a new edition to visual studio for database developers
Thanks to Patrick for the info Visual Studio Team Edition for Database Professionals. Gert Drapers talks about the formation of the team as well. Euans take on Data Dude
You can access the information about the product here http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/
So what does it include
I can't wait to get my hands on it.
Fast parse is hidden in the depths of the advanced settings of a flat file, it can be used to improve the performance of parsing text files for date and time and integers
Jamie has looked into and the performance improvements you can achieve SSIS: Fast Parse
Thanks to Euan for pointing out that Sunil from the Storage Engine team has started a blog. This continues the fantastic set of blogs coming out of the SQL Server team
Sunil has started with three greate posts
Bulk Importing data with OPENROWSET in SQL2005
Jim Gray was recently interviewed by The Register about the use of GPUs for processing data and the challenges for coding parallel systems.
Every interview, whether written or video (http://channel9.msdn.com/ShowPost.aspx?PostID=49891, http://channel9.msdn.com/Showpost.aspx?postid=50428) Jim comes across as being immensley intelligient as well being clear and concise.
Being called a Distinguished Engineer just about sums it up.
If your are lucky enough to be going to the SQL Server 2005 upgrade event on Friday, then enjoy Jim's keynote
Frans Bouma has brought together a set of posts about stored proc v ad hoc SQL usage. Yay! A new Stored Proc vs. Dyn. Sql battle!
On reading the posts it is clear that each has its place, if only everyone would agree.
I have to comment on the fact that many people who make views about bad this and poor written that are generally experiencing them in an enterprise environment. More and more systems nowdays aren't such systems, don't process 100's of transactions per second so why do they need to eek out that nth degree of performance. Most servers these days are hugely powerful compared to previous version, whereas in the client world the applciations have absorbed this increase, the database hasn't so as servers get faster so do the database servers.
For these reasons it is a fair argument to say "we are develop code in a way that means we can develop systems faster", if this means using adhoc sql then why not, i know form biter experience that managing 10 sps that do the same thing but with a slightly different where clause isn't great for supportability.
Which ever option you choose make sure you have a DBA that can review any adhoc sql your system produces to make sure you haven't got some nice cross joins occuring.
Bob Beauchemin's has blogged about the lack of support for versioning CLR assemblies in SQL Server 2005. Although some have said its is possible Bob has clarified the siuations where apparent versioning is possible
SQLCLR supports assembly versioning...NOT
I blogged last July about the ability to select column headers when copying from a grid in Management Studio into excel or notepad, and that it left something to be desired when copying one value.
Well for the RTM they change the behaviour so that if you select more than one column the headers are include otherwise they are not.
I have turned the option on and have yet to find an instance when the results have been undesirable.
Give it ago the option is in Tools|Options|Query Results|SQL Server|Results To Grid -> include column headers when copying or saving results
Well even if your not, one of my real bug bears is fat tables. These aren't large tables they are fat tables, wide ones with lots of columns or large data types.
The reason this gets is is people develop databases with 10s of rows in them. Then end up with millions of rows in them in a live environment and find this things just don't perform.
Then managing those tables is a real chore.
Just because you have a table called person doesn't mean all the information relating to a person has to be in that table. Especially when you most of the time all you want is there name. Ok so you can create covering indexes, but then you hurt your insert performance and you can't create a covering index for every situation.
Imageine the situation you have a table that links people to stadium. you can have millions of people visit your stadium but you've only got a few stadium,
Do you have a table that is like this
create table StadiumVisit (personId uniqueidentifier not null
, visitDate datetime not null
, stadium int not null
, comments varchar(200))
create table StadiumVisit2 (personId int not null
, visitDate smalldatetime not null
, stadium tinyint not null)
Whats the different well the first would be ~a minium of 28 bytes per row and the other would be 9 bytes per row (ignoring overhead). With 10 million rows the second takes up 90Mb. Assuming the average comment was 100 bytes the first would take 1280Mb ~1.2Gb
How much memory you got in your box? 1Gb which one is going to perform better?
Even ignoring the comments field thats 3 times more data to read to process a query, the impact is that you put more pages into memory that you don't need, you push pages out of memory that are needed by other processes, you query needs to process more data and thus use more cpu. In a scalable system make sure you do as little as possible, this means it has less impact on anything else and so you server can do more.
For a great book on this look at Pro SQL Server 2005 Database Design and Optimization by Louis Davidson.
But be careful of fat tables, we all no how they get fat but I will leave that to another time.
Euanga is leaving the SQL team and joining the Visual Studio Team on Team Test. I relaly hope Euan can take some of his immense SQL knowledge and push for some great SQL testing tools.
Euan your shirts have always been memorable, you must be glad with a new team you can now get a new one. Good luck with your new team.
Fantastic insight into the size of the SQL teams by the number of signatures on the boxes right from version 1 up to 2005 (much bigger box)
Mythbusters, SQL Server History follow up
More Posts Next page »