December 2009 Blog Posts
I created a small table with about 150,000 records in order to do some aggregations. There are some prerequisites to comply for, but that is easily done with replace function.
With Azure, it took almost 4 times as long time as SQL Server Express on my old development machine to complete the query.
With this simple test, I find the use for Azure very limited, as of today. It is excellent for small companies who is not hosting their own environment themself. For companies on a web hosting company which doesn't support Microsoft SQL Server it will do just fine.
But today, almost...
The few last days, our hosting company have updated their VMware environment a number of times and thus have forced equal number of restarts for our database servers.
The problem with this is that one of the databases, Yoda, needed 1.5-2.0 hours to start up due to "In Recovery" status.
I asked around what could be the cause of this and also read some excellent posts by Kimberley Tripp
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
I saw the undocumented DBCC LogInfo command and decided to give it a go. To my surprise, there were 33,636 records returned. As a general rule of thumb, you should have about 8-16 VLF...
This is currently for ISO weeks only, but if you have the week number, the weekday and year and you want that date in return, you can use this function below. And according to ISO, Monday is 1 and Sunday is 7.
CREATE FUNCTION dbo.fnGetDateFromYearWeekWeekday
(
@Year INT,
@Week INT,
@Weekday INT
)
RETURNS DATETIME
AS
BEGIN
RETURN CASE
WHEN @Year < 1900 OR @Year > 9999 THEN NULL
WHEN @Week < 1 OR @Week > 53 THEN NULL
WHEN @Weekday < 1 OR @Weekday > 7 THEN NULL
WHEN @Year = 9999 AND @Week = 52 And @Weekday > 5 THEN NULL
WHEN DATEPART(YEAR, DATEADD(DAY, 7 * @Week + DATEDIFF(DAY, 4, DATEADD(YEAR, @Year - 1900, 7)) / 7...