The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog



No Ads ever, except search!
Monday, May 27, 2024 Login

Blog posts for the month of October,2008.
merry-go-round scans10/28/2008 3:34:34 PM

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.


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.


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 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.

4) Large Page Extensions

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

5) Parallel index Operations

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

Buttoning up the wall10/26/2008 7:17:40 PM

Today was the day to button up the wall.


Door Installation10/24/2008 7:50:05 AM

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.


Job listing with steps and tsql10/22/2008 1:05:02 PM

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....

 from sysjobs sj
 inner join sysjobsteps sjs on sj.job_id = sjs.job_id
-- where like 'cy%'
 order by sj.job_id,sjs.step_id


Hash Join vs. Merge Join10/21/2008 12:23:07 PM

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
AND ( a.status_id in (4, 5, 6, 7)
OR type_id = 3
OR a.match_id =
AND = @p_in_thread_no

New Query

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

Roof Day 110/19/2008 12:00:29 PM

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.


Roof Done10/20/2008 12:05:13 PM

The roof is done.

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


Buick drops the ball, er...muffler10/18/2008 11:55:28 AM

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


Skylights installed.10/18/2008 7:50:11 AM

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.


Useable Space10/14/2008 9:52:07 AM

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.


MySQL Links for the SQL DBA10/8/2008 10:45:55 AM

I have done some recent work on MySQL. Most of my work on the database side pertains to MS SQL Server, Oracle and DB2 but at times, I have done some MySQL related work as well. Here are some good links on MySQL:

Check the Uptime of a Windows Server10/6/2008 11:08:30 AM

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

Quickly found a command line:

There are two options

1 - net statistics server

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.

Giving up on the ASP.NET Treeview10/3/2008 1:38:48 PM

The controls with are great, some of us can remember programing in asp and request.response, but has been great....with the exception of the 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:

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 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.

Blog Home