Happy halloween, 1 day, 2 windows installed and 1 door, not bad for one days work.

An often overlooked feature of SQL Server Enterprise Edition is support for advanced scanning, referred to as merry-go-round scans.  Often I'm asked about the differences between enteprise edition and standard, this is one that I often forget about this one.  Recently I was researching some information on read-consistency problems ( nolock etc) and came across this type of scan.  There are many resources about the differences, i've quoted some here below, and also list the reference.

From:

http://blogs.msdn.com/boduff/archive/2008/01/24/why-should-i-use-sql-enterprise-edition.aspx

http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=49285

There are some key enterprise edition only performance benefits across RAM, Parallelism, Query Plans and DISK I/O that will lead to better performance on high end systems, which I will try to list here.

1) Lock Pages in Memory

Lock Pages In Memory" allows SQL Server 2005 to manage its own memory (as opposed to having the operating system do it). It is extremely important to give this right to the SQL Server Service account, especially on 64-bit SQL Server 2005 systems with lots of RAM. It also is required on 32-bit systems to enable AWE.

See http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

2) Advanced Scanning (aka Merry-go-round scan)

In SQL Server Enterprise Edition, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.

See http://msdn2.microsoft.com/en-us/library/ms191475.aspx and  Merry-Go-Round Culprits for performance variances

3) Larger Read Ahead Buffering on Storage Devices

Determining I/O section mentions that EE does up to 1024k read ahead buffering on a Storage Area Network (std only does 64k). This indicates that EE is more suitable to SAN’s which need more buffering due to increased latency.

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

4) Large Page Extensions

SQL Enterprise Edition retrieves pages up to eight at a go.

http://msdn2.microsoft.com/en-us/library/aa337525.aspx

5) Parallel index Operations

This is particularly useful in data warehouses where indexes may be frequently dropped and re-created.

http://msdn2.microsoft.com/en-us/library/ms189329.aspx

Today was the day to button up the wall.

Finally the one set of doors and one window are installed.

Dealing with an unlevel floor and walls that are not plumb are a joy.

SQL Agent job listing, jobs, steps and tsql / commands executed.

Not really useful, but helpful when someone wants to see all the jobs and the steps they are executing.

Save as an excel file, or publish to rdl and push to a reporting site so they can always see it themselves....

select sj.name,sj.description,sjs.step_name,
 sjs.subsystem,sjs.command,sjs.database_name,
 sjs.output_file_name
 from sysjobs sj
 inner join sysjobsteps sjs on sj.job_id = sjs.job_id
-- where sj.name like 'cy%'
 order by sj.job_id,sjs.step_id

Error: 8646, Severity: 21, State: 1.
Unable to find index entry in index ID 1, of table 1877581727, in database 'db name here'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

Recently ran into a serious issue with SQL Server issueing the above message under load.  After shutting things down and running check db there was no corruption, but every time a particular stored procedure was executed under load, stack dumps would occur with the above message in the Error Log.

With the help of Microsoft support we isolate the issue to a particular delete statement that was being executed with a join to another table and a where clause.  The query was performing a hash join and there is some bug that was causing a problem.  Microsoft continues to research the issue, but recommended we use a query hint to force a merge join to resolve the problem.  Testing indicates that the problem has gone away.

Old Query:

DELETE FROM {child table}
FROM {child table} aq, {parent table} a
WHERE aq.id = a.id
AND ( a.status_id in (4, 5, 6, 7)
OR type_id = 3
OR a.match_id =2 
AND a.id%@p_in_threads = @p_in_thread_no

New Query

DELETE FROM {child table}
FROM {child table} aq, {parent table} a
WHERE aq.id = a.id
AND ( a.status_id in (4, 5, 6, 7)
OR type_id = 3
OR a.match_id =2 
AND a.id%@p_in_threads = @p_in_thread_no
option (merge join)

I still have some caulking and and gutter work, but for the most part it is leak proof.

Roof day 1.

Now that the skylights are installed, time to get working, putting down the snow and ice wrap and tar paper and managed to get the first row of shingles installed.  The real difficult part is all the cutting of the shingles and properly aligning them to seal against the skylight flashing.  This simple pitched roof should be shingled in probably 6 hours without the skylights, but because of the skylights and low pitch it will take me two days and some hours.

She's running good, park in the driveway.  Standing out front, enjoying a cold beer, thud.  Muffler down.

Saturday was skylight day, i tried to start friday, but rain and late delivery caused most of the day to be a wash.  Before really starting, had to remove and rebuild one wall which was supporting the roof, as the wood had started to deteriorate through moisture and the use of non-pressure treated wood on the footings.

Tired of the screened in porch being a dirty space, with a leaking roof, that's always dirty....time to turn it into a room off the back of the house with heat, air and skylights to open it up.

Initial pictures, concept, roof opened up and all old material removed.

Recently needed to check the uptime of some windows server, mostly to verify that we had rebooted them all.

Quickly found a command line:

2 - SystemInfo |FIND /I "System Up Time"

Be nice to include this in my monitoring program, so it could be quickly queried from a centralized place, or embed this in some windows script that could take a list of them and run it remotely. 

Thanks to Rick Mcintosh.

The controls with asp.net are great, some of us can remember programing in asp and request.response, but asp.net has been great....with the exception of the asp.net treeview.

I've tried using this thing off and on for months, really digging in recently and have come to the conclusion, that I am better off creating my own treeview control.

I think for simple requirements the treeview control works great, but my requirements were to create a dynamically driven treeview loaded from a database as each node is clicked.  Somehow this just causes the treeview control to loose it's brain, viewstate and postbacks didn't work, slowly I began building up so many hacks to make it work, that I just couldn't believe it.  You have to know when to give up, and I was there.

I created my own tree view for my knowledge base, and it's located here:  http://www.lifeasbob.com/code/kb_articles.aspx.

It uses a combination of post backs and query strings to manipulate and display articles, search them too (though I need to work on that some more), also for me I have the ability to add, edit and delete them off the treeview.

I feel very satisfied with my own version of the treeview, as I understand everything about it and don't have to worry about the voodoo asp.net treeview control loosing state and the myriad of other issues I ran into.  I tried many of the websites below for help, and they were great, but ultimately the damn thing still didn't work, mine does, code done.

http://www.mredkj.com/vbnet/scriptCallback.html

http://aspalliance.com/732 

http://www.dotnetjunkies.com/Article/E80EC96F-1C32-4855-85AE-9E30EECF13D7.dcik 

http://www.bulahema.com/en/aspnet20treeviewwithoutpostbacksolved