Windows 7 BSOD's - SOLVED!!!

Ok,


it has been a while since my last BSOD's. I blamed everything, really everything. Memory, Power Supply, CPU, Mainboard, Chipset drivers, i even blamed my dog for sniffing at my computer right before it got a BSOD. And then i found something funny.

I was copying some things to and from 1 particular disk. BSOD.
Tried it again. BSOD.
Again. BSOD.

Strange! I did some S.M.A.R.T. tests, and nothing was wrong. I used the drive tests from the speedfan tool (http://www.almico.com/speedfan.php), and nothing was wrong it said. However, i still couldn't copy from and to the disk at the same time.

And then eventually i fixed my problems. And guess what, there is one thing missing in the list above. Cables. Plain simple cables. SATA Cables are probably known for the defects, however, that didn't occured to me. So i bought 4 new SATA cables, this time short ones that even fitted better in my mainboard, et voila. No more BSOD's.

So next time you go into a deepdive of windows debugging, make sure your checklist is complete with even the trivial things ;-)

Windows 7 BSOD's

Ok, this one really got on my nerves. And it still does to be honest.
At home on my computer i get these strange random BSOD's. One time it says it has something to do with my disk, next one was about power, and i even had several about memory. Since the random factor of the BSOD's looks like a hardware conflict of any kind i tried to do the following :


- change disks
- change disk settings in the BIOS
- change voltage settings in the BIOS
- overclocked/underclocked several settings in the BIOS
- Changed the PSU (from 350w to 550w)
- Did several disk checks
- Did several memory checks
- Changed SATA cables
- Installed new/different drivers
- Reinstalled Windows 7 about 10 times
- Installed Windows Server 2008 r2




And nothing helped. The times between BSOD's got random, where an overclock from the BIOS seems to help the most. But it only helped to push the times of random BSOD's a little further.


So, time to take it to the testtable.... if i had one ;-)
The following hardware specifications i am using :
- ASRock 890GX Motherboard
- AMD Phenom x4 925
- Recom 550watt PSU
- ATI Radeon HD4850 pci-e video card
- 2x2gb Mushkin memory
- 3 sata disks, 2x80gb and 1x640gb (in IDE mode in BIOS, because AHCI or RAID won't recognize them properly, maybe this is due to the fact that i am using port 4,5 and 6 from my SATA controller instead of 1,2 and 3, have to look into this)


That's it, that is the whole setup i am using. Can be several things of course. One thing it certainly is not the issue, temperature. Why? I have monitored my temperature sensors, since i thought this was the issue. I came to that from a co-worker who putted a vacuum cleaner on his laptop's tempfan while having several power downs. It worked for him, so maybe this was my problem to. I cleaned my PSU (which was DIRTY!), my CPU Fan and my video card fan. It looked like it worked, but it didn't. Temperatures got down immediately, so if temperature is high on your system without any noticeable reason, maybe it is a dirty fan ;-)


So, i have some BSOD dumps, why not get into that?


Ok.. so you have to do some things to read a BSOD memorydump. I use some tooling, like Windows SDK as a start, so we have all debugging components. You can find them here : http://www.microsoft.com/whdc/devtools/debugging/default.mspx
When you have installed the debugging tools, you will have a "c:\Program Files\Debugging Tools for Windows (x64)" directory where you can find the file "cdb.exe". That is the only thing we need.


I used it with the following options :


-logo c:debuglog.txt
-c "!analyze -v;r;kv;lmtn;.logclose;q"
-y SRV*c:\symbols*http://msdl.microsoft.com/download/symbols
-i C:\Windows;C:\Windows\system32;C:\Windows\system32\drivers
-z "C:\Windows\Minidump\122910-17409-01.dmp"


When you run "cdb.exe" with these options, you'll get a "c:\debuglog.txt" (the -logo parameter), where the -c parameter gives a command to the debugger (to automate things). For a closer look into this command look into the included helpfile, it will sort things out!
The symbols flag (-y) is pretty self explaining, as the same goes for the -i flag, where you give a reference or references to the location(s) where the images are that we have to use.
The next one, -z, is the minidump we got from the BSOD itself.


So the complete command will be :
"C:\Program Files\Debugging Tools for Windows (x64)\cdb.exe  -logo c:debuglog.txt -c "!analyze -v;r;kv;lmtn;.logclose;q" -y SRV*c:\symbols*http://msdl.microsoft.com/download/symbols -i C:\Windows;C:\Windows\system32;C:\Windows\system32\drivers -z "C:\Windows\Minidump\122910-17409-01.dmp" "


Make a debuglog for every minidump you have and start comparing it. When you open these log's you'll see a lot, and i mean a lot, of information. The first part is about your OS. Buildnumbers, some symbol information and after that you'll get the in depth analysis.


This analysis learned me really nothing. How sad is that :( Well.. it learned me that anything can be the problem. from disk reads/write to memory reads/write (any buffers in the motherboard maybe??). Heating is not the issue, low wattage, corrupt disks, you name, all not the issue. And then we had a real bright moment.. a REAL bright moment. BIOS Updates. And yeah, 2 were available. I installed them both, and WOW. It solved everything.


So next time you go into windows debugging to solve a really annoying problem, make sure you have all updates, windows AND bios, installed, and then look further. Thank god for release notes in BIOS updates (are they even existing??)

datetime conversions

Today i received a question about datetime conversions, because twitter is only allowing 160 characters in a reply i deceided to put it here.


The question was quite simple, what is the best way in SQL Server to receive only the datepart from a datetime variable. I got into it, and this is what it can be :

declare @dt1 datetime

      , @dt2 datetime

      , @dt3 date

     

set @dt1 = GETDATE()

set @dt2 = CONVERT(datetime, CONVERT(varchar(10), @dt1, 101))

set @dt3 = cast(@dt1 as date)

select @dt1, @dt2, @dt3

the @dt3 variable is a with a new type, date. Date is available in SQL Server 2008 and higher. If you can't use that.. use the @dt2 variable.

 

String Concatenation in SQL Server. Concat vs REPLACE

Every now and then i get to see some T-SQL code where you get that feeling that something could get a performance upgrade, while not being sure if it would benefit in a positive way. I was going through a lot of code today and saw some lines which made me think.


In pseudo code i had the following line :

set @variable_a = 'First part of a string, with ''' + @variable_b + ''' concatenated to it'

Straightforward, it concatenates a string to another string. How many times you that? The code i went through was full with these kind of concatenations. 

Every time i see a string concatenation, i am thinking about the String vs StringBuilder example in .Net code, where the StringBuilder is much faster in concatenations. So, maybe there is a same general rule for this in SQL Server.

With that in mind, what about the REPLACE function. I use it very often, and to be honest, i used it mainly to make my code more readable. If you think about dynamically build up sql, a placeholder to be replaced reads better then a string concatenation.

So, time to put this to a test and see who is the David that brings down Goliath.  And i made the following script to see which one is faster, and which is slower.

------------------------------------------

-- String CONCATENATION

------------------------------------------

declare @totrow int

      , @teststring varchar(max)

      , @startdate datetime

      , @enddate datetime

set @startdate = getdate()

set @teststring = ''

set @totrow = 1000

while @totrow > 0

begin

 

  set @teststring = @teststring + 'aa'

 

  set @totrow = @totrow - 1

end

set @enddate = getdate()

print 'String CONCATENATION : ' +

        cast(datediff(ms, @startdate, @enddate) as varchar(10)) +

        ' ms'

------------------------------------------

-- String REPLACE

------------------------------------------

set @startdate = getdate()

set @teststring = '[A]'

set @totrow = 1000

while @totrow > 0

begin

  set @teststring = replace(@teststring, '[A]', 'aa[A]')

  set @totrow = @totrow - 1

end

set @teststring = replace(@teststring, '[A]', '')

set @enddate = getdate()

print 'String REPLACE       : ' +
        cast(datediff(ms, @startdate, @enddate) as varchar(10)) +

        ' ms'

The changing part in the 2 blocks of code is the REPLACE functionality. When i run this code, i see that the first part is taking up approximately 0 ms and approximately 70ms for the replace part.And that is with ONLY 1000 replacements. Try to guess what happens when we give the total rows 

variable a value of 10000.

The concatenation part then is using 230 ms and the REPLACE is taking up to 5100 ms to process.

So, next time you use a REPLACE function, use is wisely. On lower rates it can be used, 

higher numbers could end up in useless performance losses.

ISNULL and Operand type clash: int is incompatible with date.

So, just ran into this problem. After running a query with some checks in it i got this strange error

"Operand type clash: int is incompatible with date". Strange? Not really! An integer isn't compatible with date. But why? And why do i not get the same error when i use a datetime in stead of the date?

I analysed my problem and came with this :




use tempdb

go

declare @temp_aId int

if OBJECT_ID('temp_a') is not null

  drop table temp_a

if OBJECT_ID('temp_b') is not null

  drop table temp_b 

 

create table temp_a (

  Id int identity(1, 1) not null primary key

, ColDate date

, ColDateTime datetime

)

create table temp_b (

  Id int identity(1, 1) not null primary key

, temp_aId int not null references temp_a(Id)

, ColDate date

, ColDateTime datetime

)

insert

into    temp_a

select  GETDATE(), GETDATE()

set @temp_aId = @@IDENTITY

insert

into    temp_b

select  @temp_aId, GETDATE(), GETDATE()

select  *

from    temp_a a

        inner join temp_b b

          on  b.temp_aId = a.Id

where  (case when isnull(a.ColDate, -1) = isnull(b.ColDate, -1) 

             then 1 else 0 end *

        case when isnull(a.ColDateTime, -1) = isnull(b.ColDateTime, -1) 

             then 1 else 0 end) = 1

select isnull(ColDate, -1) from temp_a

select isnull(ColDateTime, -1) from temp_a

You can run it as it is here and it is pretty straightforward, this is what it does: It creates two tables with a date and a datetime, just for proof of concept ;-) In my original query i join these tables and perform a check if the 2 tables are equal. I do this with the "case when isnull(a.column, -1) = isnull(b.column, -1) then 1 else 0 end" statement. If a column isn't equal then a zero is returned, and we all know that "0 = 1" is false! So if a column isn't equal it should return false and start to insert things in the original query.

But all i got was the "Operand type clash: int is incompatible with date" error.

It looks like it is the ISNULL function. When i look into BOL (Books OnLine) i see this :

"

ISNULL ( check_expression , replacement_value )

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion. "

Ok. So the check_expression can be of any type, and the replacement_value has to be of a type that is convertible to the check_expression type. Makes sense.According to BOL a conversion between INT  and DATE is not allowed, and only an explicit conversion is allowed between an INT and a DATETIME.

Why does it say IMPLICITLY? I don't get it. It can only do implicit conversions, but a DATETIME to -1 is allowed, but according to BOL it is a EXPLICIT conversion.

Then why isn't a convert from DATE to -1 not valid?

Anyone has a clue?

Fragment an Index?

Some time ago i wrote a script for defraging indexes on sql server, this one will do the trick to! The old script looked at the sys.indexes table, where the information about fragmentation levels was not being used. The script below does.


I have set the maximum percentage of fragmentation to 30%, which is low enough. Getting a lower fragmentation percentage could be used maybe once of twice on a certain index, but for the most part it would only cost time and wouldn't give you that much of a boost on your indexes. 


I suggest to run this script and use outcome in the generated SQL columns at least once a weak. The first time it might hurt a bit, especially on long running tables. I had to use this one today on a 10.000.000 records counting table and it helped a lot in performance gains and only cost about 30 minutes or so. 


When you don't want to run ALL the generated SQL statements, but only the ones that have a high level of fragmentation look at the columns 'page_count' and 'avg_fragmentation_in_percent'. The higher both are, the more you want to fragment these. On the indexes i had to fragment today some where 50.000 pages with a 98% fragmentation (!?).


Like i said, you have to run the outcome of this query, preferable the 'statement_of_choice' column should be used. The fragmentation levels below 30% will result automatically into a reorganize in stead of a rebuild. That column could be used in my script from a while ago, where the statements were executed. 




declare @database_id int


select @database_id = database_id
from sys.databases
where name = db_name()


select distinct
o.name
, i.name
, s.avg_fragmentation_in_percent
, s.avg_fragment_size_in_pages
, s.page_count
, case
when avg_fragmentation_in_percent < 31 then 'reorganize'
else 'rebuild'
end as choise
, case
when avg_fragmentation_in_percent < 31 then replace(replace('alter index [[INDEXNAME]] on [OBJECTNAME] reorganize', '[INDEXNAME]', i.name), '[OBJECTNAME]', o.name)
else replace(replace('alter index [[INDEXNAME]] on [OBJECTNAME] rebuild', '[INDEXNAME]', i.name), '[OBJECTNAME]', o.name)
end as choise
, replace(replace('alter index [[INDEXNAME]] on [OBJECTNAME] rebuild', '[INDEXNAME]', i.name), '[OBJECTNAME]', o.name) as statement_rebuild
, replace(replace('alter index [[INDEXNAME]] on [OBJECTNAME] reorganize', '[INDEXNAME]', i.name), '[OBJECTNAME]', o.name) as statement_reorganize
, replace(replace(replace('dbcc indexdefrag([DATABASE_ID], ''[OBJECTNAME]'', ''[[INDEXNAME]]'')'
, '[DATABASE_ID]', @database_id)
, '[INDEXNAME]', i.name)
, '[OBJECTNAME]', o.name) as statement_defrag
from sys.dm_db_index_physical_stats(@database_id, null, null, null, null) s
inner join sys.tables o
on o.object_id = s.object_id
inner join sys.indexes i
on i.object_id = o.object_id
inner join sys.index_columns c
on c.object_id = o.object_id
and c.index_id = i.index_id
inner join sys.columns u
on u.column_id = c.column_id
and u.object_id = o.object_id
--order by 5 desc, s.avg_fragmentation_in_percent desc
order by 3, 1


Impersonation in VB.Net

For a client i had to get some files from a fileshare to process these programmaticly. The language of choice was in this case VB.Net, not my favorite, but the clients wish is the clients wish. The fileshare was located on a machine that was accessible from the normal networkdomain, but the webserver that hosted the web application wasn't part of that domain, so no standard fileshare permissions were available.
We came up with the idea to impersonate the process to the administrator, where we had all the permissions we needed (and more... i know). In the end we wanted to have things configurable, so that we can use another user with the appropriate permissions.

The impersonation uses advapi32.dll's UserLogonA function, you can add it like this into your code:


Declare Function LogonUserA Lib "advapi32.dll" (ByVal lpszUsername As String, _
                                                    ByVal lpszDomain As String, _
                                                    ByVal lpszPassword As String, _
                                                    ByVal dwLogonType As Integer, _
                                                    ByVal dwLogonProvider As Integer, _
                                                    ByRef phToken As IntPtr) As Integer

To call this function we need to do the folowing :


Dim token As IntPtr = IntPtr.Zero
Dim loggedOn As Boolean = False
If (LogonUserA(My.Settings.ImpersonateUser, My.Settings.ImpersonateDomain,                         My.Settings.ImpersonatePassword, 9, 0, token) <> 0) Then
                loggedOn = True
End If
If (loggedOn) Then
    Dim newIdentity As WindowsIdentity = New WindowsIdentity(token)
    Dim impersonatedUser As WindowsImpersonationContext = newIdentity.Impersonate()

    << code for impersonated processes goes here >>


    impersonatedUser.Undo()
end if

Pretty straightforward huh! Hence the My.Settings.ImpersonateXXX setting variables, configurable throught the project settings.
It is also important to call the Undo() method of the impersonatedUser variable. When a process throws an exception and this Undo() function isn't called, the process will stay running with the impersonated user permissions, not a good idea!!!

Have fun!