Operating Systems

This week we got an early alpha of Google Chrome OS, which is slated for full release sometime in Q4 of 2010. ZDNet blogs and Ars technica have three good first looks at Chrome. First up is Adrian Kingsley-Hughes article Chrome OS – The good, the bad and the ugly, and how it fits in with Windows, Mac and Linux.

For a more security related view, Ryan Naraine has an early look into Chrome OS security with Inside the Google Chrome OS security model.

Lastly, Jon Stokes at Ars Technica has his own first look with screenshots in Chrome OS: Internet failing at PC > PC failing at Internet.

There’s a new Fedora in town. The popular Linux desktop put out by Red Hat released its newest version. Bill has gone crazy for Fedora articles today, so here’s a point form list of what he found.

• Fedora 12 Release Announcement. The announcement has links to the feature list, release notes, download page, etc.
• The Red Hat press release about Fedora 12 called Fedora 12 Unites Latest Features and Usability Into Compelling Free Distribution.
• Tip of the hat: Fedora 12 a strong update by Ryan Paul goes through some initial impressions of Fedora 12 and what it brings to the table.
• Christopher Smart at Linux Magazine has his own first look in Fedora, Still Pushing The Envelope.
• And finally the first major bug has been reported and fixed in Fedora 12. Fedora 12 lets unprivileged users install packages has the initial information about the bug, and PackageKit change has information on the fix.

Have you ever wondered just how much you can take out of Windows and still have a usable system? The Minwin project set out to find that out for you. Warren Rumak discusses Minwin and what it’s all about in Inside “MinWin”: the Windows 7 kernel slims down.

Data Centers

Scuttlemonkey at SlashDot posted a question about how to evaluate a datacenter. This question has elicited a flurry of discussion on the topic with some good (and bad) stories, but buried inside are many good thoughts on criteria that would be useful in any evaluation. Read more in “How Do You Evaluate a Data Center?“.

Data Center Knowledge has an interesting article by Kevin Normandeau. It’s all about how a greener datacenter can pay off in the long run. “…Amazon, Toyota, and Nike, have realized that focusing on limiting energy calories in the datacenter and elsewhere pays profitability dividends on the financial side” says the article, which draws on a whitepaper from IDC on the subject.

Also at Data Center Knowledge, Rick Miller has a note about Rackspace expanding its headquarters with a new 120,000 square foot expansion. See Rackspace Expands Its Headquarters for more, and a video tour of one of their offices.

Hardware

Cnet news has an article about the recently released Square Trade survey of laptop reliability. Square Trade is a warranty provider who offer coverage for many brands of laptops so they should know. See Who makes the most reliable laptops for some excerpts from the report, and the full report can be found in PDF format from the Square Trade website.

Security

A follow up from last week regarding the Microsoft “sudo” patent. According to Ryan Paul this patent does not cover sudo at all. Read more about it in Microsoft’s pseudo sudo patent doesn’t really cover sudo.

A follow up to a post from a couple of weeks ago—there is now an exploit for the mentioned SSL/TLS attack vector. Dan Goodin at The Register has more in his article Researcher busts into Twitter via SSL reneg hole. More technical details on the exploit can be found in Understanding the TLS Renegotiation Attack.

Software

A follow up to last week’s article, Microsoft confirms the Windows 7 USB/DVD download tool contained GPLv2 code and has indicated that they will provide the source/binaries for the tool under the GPL. Peter Galli from Microsoft’s Open Source division has more in Update on the Windows 7 USB/DVD Tool.

Internet

The UN-backed Internet Governance Forum was on last week in Egypt. One of the hot topics was ownership of the DNS root domain, which is currently controlled by the US. This made sense when the internet was only in the US universities, but with the global nature of the ‘Net, there is no good reason for a single country to have control of something that controls the basic functionality of the internet. Janna Quitney Anderson has more in IGF attendees: America, surrender the root zone file!

Virtualization

A follow-up review on VMWare Fusion 3, running Windows 7 in OSX. Dave Girard has put Fusion through its paces; read about his results in Running Windows 7 under OS X: Ars reviews VMware Fusion 3.

That’ll do it for this week’s edition. As always feel free to add your own news or perspective in the comments. See y’all next week!

SQL Server

There are lots of good technical posts this week. The SSIS Junkie has some observations and a straw poll on sort transform arbitration. He writes, “This post was prompted by a thread on the MSDN SSIS forum today where the poster was asking how he could replicate the behaviour of SSIS’s Sort transform using T-SQL, specifically he wanted to know how the Sort transform chooses what data to pass through when the ‘Remove Duplicates’ option is checked.”

Another poll, courtesy of Tibor Karaszi: do you perform log backup for the model database?

Eric Johnson has a lesson in looping through rows in a table in SSIS 2008, which begins, “When writing code against a SQL Server, as we usually are doing in SSIS Packages, you often need to iterate over all the rows in a table. This can be done using an SSIS Foreach Loop Container, but the how is not obvious.”

Simple-Talk’s Tony Davis wonders, Do Scalar UDFs give SQL Server a Bad Name? “Many developers seem to regard SQL Server as if it were a science-fiction alien planet where unsuspecting crew-members in blue jumpers occasionally die horribly; everything is suddenly unsafe, and potentially malicious: nothing really works properly and so any serious code should be kept well away from it. Is this developer ignorance, or is their fear justified?”

From Merrill Aldrich comes a trick question — part quattro. Spoiler: TPH is an evil trap. As a commenter says: “Very interesting, I’ve never heard this vehement an argument against TPH before.”

Thinking outside the box lets us in on how to tell if you are running on a virtual environment, with a handy little bit of code.

Kalen Delaney elucidates UPDATE Locks, “ . . . a hybrid of SHARED and EXCLUSIVE locks. [Contrary] to what you might think, UPDATE locks are not just acquired for UPDATE operations.”

Stephen Forte shows how SQL Server R2 Does SQL Azure.

Now that PASS 2009 in Seattle, Washington has passed, it’s time to fondly look back on it. Kendal Van Dyke shares his experiences and some photos in Looking Back – PASS Summit 2009 Day 4.

The Rambling DBA, Jonathan Kehayias does so with the benefits of attending PASS realized: “ . . . as a testament to the value of attending this conference the very first session I attended, diagnosed and provided information for a problem that has existed in one of my servers for many weeks but was impossible to diagnose unless you knew what you were looking at.”

Thomas LaRock, SQL Rockstar concurs: “If you want to grow your skills, then you need to connect, learn, and share with others. And there is no better place to do that than at PASS.”

Not that PASS was the only game in town. Just down the coast a bit, Baron Schwartz gives his recap of Portland OpenSQL Camp 2009.

Selena Marie Deckelmann reports that OpenSQLCamp was awesome!

PostgreSQL

Pavel Stehule has some news of a longtime plpgsql misfeature removed. He writes, “plpgsql is good language – simple, robust with good error diagnostic. But it had one bizarre behave. plpgsql connects two worlds – procedural ADA like code and SQL statements. Usually there are not problems. But there are one exception – collision of identifiers.”

In the latest in his Waiting for 8.5 series, Hubert Lubacziewski introduces and tests something new: TRIGGERS on columns.

MySQL

On code.openark.org, Shlomi Noach is surprised by questions or queries. He writes, “I was used to checking for the ‘questions’ global status variables . . .  So, for example, I could run com_select/questions to learn the SELECT ratio out of all queries.  . . .  Apparently, as of 5.0.72-5.0.76 & 5.1.31 this has changed. A new status variable was introduced, called ‘queries’.” What’s the difference? Is this good or bad? Shlomi and his readers kick it around.

Venu Anuganti also was surprised, in his case by InnoDB Tablespace Corruption: “When . . . InnoDB crashes, it automatically recovers during the next start by rolling back/forward based on what was pending and un-flushed/un-committed changes at the time of crash.  . . .  [On] one of the servers; we ran out of disk space . . . on data directory . . . and server was running for few hours in this mode . . .  [It] became un-available and not responding after a while. Only option left was to kill the server process and its PID along with cleaning the stuff to get the space back. After I (re)started the server . . .  the tablespace is corrupted.”

From Arnold Daniels comes a version of versioning MySQL data, which Arnold introduces thus: “ . . . You’re probably using a versioning control system . . . to safeguard your data. Advantages of using a VCS are that you can walk to the individual changes for a document, see who made each change and revert back to specific revision if needed. These are features which would also be nice for data stored in a database. With the use of triggers we can implement versioning for data stored in a MySQL db.” Example code follows.

On the MySQL Perfomance Blog, Morgan Tocker qualifies his earlier piece on why you don’t want to shard: “What I didn’t mention was that if you’ve established that you will need to eventually shard, is it better to just get it out of the way early? My answer is almost always no. That is to say I disagree with a statement I’ve been hearing recently; ’shard early, shard often’”

Morgan also mentions that interviews for InfiniDB and TokuDB are next: “I’d like to announce that Robert Dempsey (InfiniDB storage engine) and Bradley C. Kuszmaul (TokuDB storage engine) have also accepted an interview. If you have any questions about either storage engine, please post them here by Friday 20th November.”

Here’s a new blog to watch, particularly if you’re new to MySQL or Drizzle: Kent Bozlinski’s Learning Drizzle. Kent says, “I’m not really scared of rain after living in Seattle for seven years. I am a little scared of sticking my neck out and writing about something which (for the moment) I know almost nothing about.” The post is Drizzle is Scary (A Little).

Oracle

Maybe you’ve already heard about the fabulous unpopularity of the new My Oracle Support. Daniel Fink comes back with some data and commentary on just that, with his My Oracle Support Survey Results.

Richard Foote asks, An index only performs how much work???, the result of looking into exactly why index rebuilds can improve performance so significantly.

Kerry Osborne gives a lesson on fixing bad index hints in SQL Profiles (automatically). He says, “With 10g and 11g, it appears the goal [or Outlines] has swung away from the “locking” concept and towards allowing the optimizer more flexibility.  . . . I must say that I find this decision to be irritating at best.  . . .  One of the main offenders in this regard is the use of a new format available for index hints as of 10g.”

Inside the Oracle Optimizer covers similar turf in answering to the question, What should I do with old hints in my workload?, or more specifically, “When moving from 10g to 11g, should hints in existing SQL be removed?”

Tom Kyte wants your opinions on comparative window functions: “ . . . they could be getting better in the near future.  . . . analytics [could be allowed] to access the current row value to be compared against any other row value in a defined window.  . . .  I’ve already supplied them with my feedback (which started with “this is an awesome idea”) – and you can too – by posting it here. They’ll be checking back to see what you say.”

If you like staying on top of fresh things, perhaps John Piwowar’s method of retrieving Oracle patches with wget would also appeal to you.

And that is all for now. If you think I’ve missed a worthwhile DB blog from this week, please mention it in a comment.

Log Buffer will be back in a week’s time. See you then!

Following the Fedora documentation, I decided to use the “preupgrade” tool. Everything was going smoothly until the machine restarted to begin installation of the new packages. I got a message that there wasn’t enough space in my /boot partition. Specifically, the message claimed that there was insufficient disk space in /mnt/sysimage/boot. I found this rather odd and troubling, since I had let the Fedora installer determine the /boot partition size when I originally installed Fedora 11.

Turns out that this is a known problem with the preupgrade tool. A kind soul in #fedora on IRC directed me to the list of common Fedora 12 bugs, in particular the preupgrade free space check. I installed the updated preupgrade package as directed, but again got the error. That’s when I followed the next link for additional tips to free up space in /boot. The first was to remove obsolete kernels, which I had already done. The next was to run tune2fs on /boot filesystem to free up reserved blocks, which aren’t needed for /boot. I strongly suggest you visit the links provided for helpful screenshots and commands to follow.

After making these changes, the upgrade worked and am I’m the proud owner of a Fedora 12 Constantine laptop, with a slightly brighter shade of blue desktop than that crusty old Fedora 11. ;)

Christo has been an active blogger on our blog and has made numerous top-class presentations at Oracle Conference world-wide. He has also been an active contributor on the Oracle-L list.

Christo has been always representing an independent voice in the community and it’s a pleasure to have him on board. I’m looking forward to his contributions to the Oracle ACE program and the Oracle community at large.

Welcome Christo!

Here’s a serious performance bug that’s been in Oracle for a while, and finally there are hints of it been fixed, but only partially.

The bug happens when performing sorting operations in parallel, and the source data is already well sorted. The “ranger” doesn’t do a good job of properly assigning row ranges to sorter processes, and ~90% of the rows end up being sent to the same parallel process, regardless of the level of parallelism. So even if you have 256 CPUs, only about 10% performance improvement is achieve, instead of a factor of your parallelism when running the query in parallel.

For example, if the non parallel sort/index creation took 45 minutes, running with parallel 32 will take 41 minutes, instead of the possible 1.4 minutes (assuming you have sufficient horsepower).

When running a “Sort” operation in parallel, there are 2 sets of parallel processes. Producers and consumers. The number of producer/consumer pairs depends on your parallelism settings. So in the case of parallel 32 there are 32 producers and 32 consumers. This is well illustrated in the Oracle documentation HERE (Oracle web based documentation down, will update later). As each ‘producer’ is reading data it is sending it to the appropriate consumer for that “range”. For example, consumer 1 takes A-B, consumer 2 takes C-F, consumer 3 G-L and etc. The exact split is dynamically calculated by the “Ranger” process. Unfortunately it doesn’t work so good with sorted data.

The same applies to index creation. Index creation is basically a big sort, followed by writing out the result set into a B-Tree structure. Index creation suffers from the exact same ranging issues, at least until 11.2.

Here’s an example:

-- Create mini-sample table
create table mytest_s as select rownum r from dual connect by level <=400000;

-- Fetch only 1 row, no need to fetch all
begin
  for c in ( select /*+PARALLEL(t,4)*/ * from mytest_s t order by 1) loop
    exit;
  end loop;
end;
/
select dfo_number "d", tq_id as "t", server_type, num_rows,rpad('x',round(num_rows*10/nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type),0)),'x') as "pr", round(bytes/1024/1024) mb,  process, instance i,round(ratio_to_report (num_rows) over (partition by dfo_number, tq_id, server_type)*100) as "%", open_time, avg_latency, waits, timeouts,round(bytes/nullif(num_rows,0)) as "b/r"
from v$pq_tqstat order by dfo_number, tq_id, server_type desc, process;

d t SERVER_TYPE   NUM_ROWS pr           MB PROCESS I    %  OPEN_TIME AVG_LATENCY   WAITS   TIMEOUTS  b/r
- - ----------- ---------- ----------- --- ------- - ---- ---------- ----------- ------- ---------- ----
1 0 Ranger             372 xxxxxxxxxx    0 QC      1  100          0           0       0          0   11

1 0 Producer        126144 xxxxxxxxxx    1 P012    1   32          0           0      19          2    6
1 0 Producer         47304 xxxx          0 P013    1   12          0           0       8          1    6
1 0 Producer        110376 xxxxxxxxx     1 P014    1   28          0           0      17          2    6
1 0 Producer        116176 xxxxxxxxx     1 P015    1   29          0           0      16          0    6

1 0 Consumer          7885               0 P008    1    2          0           0       6          1    5
1 0 Consumer          7884               0 P009    1    2          0           0       6          1    6
1 0 Consumer          7884               0 P010    1    2          0           0       7          2    6
1 0 Consumer        376347 xxxxxxxxxx    2 P011    1   94          0           0      16          4    6

1 1 Producer          5536 xxxxxxxxxx    0 P008    1   55          0           0     577        568    3
1 1 Producer          4508 xxxxxxxx      0 P011    1   45          0           0     587        571    4

1 1 Consumer           100 xxxxxxxxxx    0 QC      1  100          0           0       1          0  161

As you can see from this test case, the sorter processes (4 consumers) had a very uneven split, with 94% of the rows been sent to only one consumer. I tested this case with parallel 64, and in that case 90% gets sent to 1 consumer, with the other 10% evenly distributed on the remaining ones.

This essentially reduces your execution time by at most 10%.

A very similar thing happens if you create an index. I did, however, catch an anomaly in the test case. With parallel 4, the distribution is 66/34/0/0, while with parallel 8, it’s 100/0/0/0/0/0/0/0. I.e., terrible as all the work will be performed by 1 process, absolutely the same as Serial, only a little worse as there will be inter-process communication.

create index on mytest_s (r) parallel 4;
select dfo_number "d", tq_id as "t", server_type, num_rows,rpad('x',round(num_rows*10/nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type),0)),'x') as "pr", round(bytes/1024/1024) mb,  process, instance i,round(ratio_to_report (num_rows) over (partition by dfo_number, tq_id, server_type)*100) as "%", open_time, avg_latency, waits, timeouts,round(bytes/nullif(num_rows,0)) as "b/r"
from v$pq_tqstat order by dfo_number, tq_id, server_type desc, process;

SERVER_TYPE   NUM_ROWS pr          MB PROCESS  I   %  OPEN_TIME AVG_LATENCY      WAITS   TIMEOUTS        b/r
----------- ---------- ----------- -- -------- - --- ---------- ----------- ---------- ---------- ----------
Ranger              12 xxxxxxxxxx   0 QC       1 100          0           0          1          0       3974
Producer        132601 xxxxxxxxxx   2 P004     1  33          0           0         19          1         18
Producer         95265 xxxxxxx      2 P005     1  24          0           0         14          2         18
Producer         95265 xxxxxxx      2 P006     1  24          0           0         14          1         18
Producer         79497 xxxxxx       1 P007     1  20          0           0         11          0         18

Consumer        262308 xxxxxxxxxx   4 P000     1  66          0           0         76         73         18
Consumer           164              0 P001     1   0          0           0         77         74         19
Consumer           164              0 P002     1   0          0           0         76         73         19
Consumer        137364 xxxxx        2 P003     1  34          0           0         76         73         18

Producer             1 xxxxxxxxxx   0 P000     1  25          0           0          0          0        322
Producer             1 xxxxxxxxxx   0 P001     1  25          0           0          0          0        322
Producer             1 xxxxxxxxxx   0 P002     1  25          0           0          0          0        322
Producer             1 xxxxxxxxxx   0 P003     1  25          0           0          0          0        322
Consumer             4 xxxxxxxxxx   0 QC       1 100          0           0          1          0        322

create index on mytest_s (r) parallel 8;

SERVER_TYPE   NUM_ROWS pr            MB PROCESS  I   %  OPEN_TIME AVG_LATENCY      WAITS   TIMEOUTS        b/r
----------- ---------- ------------- -- -------- - --- ---------- ----------- ---------- ---------- ----------
Ranger               0                0 QC       1              0           0          6          2 

Producer         47304 xxxxxxxx       1 P008     1  12          0           0         14          2         18
Producer         51246 xxxxxxxxx      1 P009     1  13          0           0         15          1         18
Producer         47304 xxxxxxxx       1 P010     1  12          0           0         15          2         18
Producer         45220 xxxxxxxx       1 P011     1  11          0           0         15          2         18
Producer         59130 xxxxxxxxxx     1 P012     1  15          0           0         17          3         18
Producer         35478 xxxxxx         1 P013     1   9          0           0         10          3         18
Producer         55188 xxxxxxxxx      1 P014     1  14          0           0         16          1         18
Producer         59130 xxxxxxxxxx     1 P015     1  15          0           0         17          1         18

Consumer        400000 xxxxxxxxxx     7 P000     1 100          0           0         52         49         18
Consumer             0                0 P001     1   0          0           0         52         49
Consumer             0                0 P002     1   0          0           0         52         49
Consumer             0                0 P003     1   0          0           0         52         49
Consumer             0                0 P004     1   0          0           0         52         49
Consumer             0                0 P005     1   0          0           0         52         49
Consumer             0                0 P006     1   0          0           0         52         49
Consumer             0                0 P007     1   0          0           0         52         49 

Producer             1 xxxxxxxxxx     0 P000     1  13          0           0          0          0        322
Producer             1 xxxxxxxxxx     0 P001     1  13          0           0          0          0        322
Producer             1 xxxxxxxxxx     0 P002     1  13          0           0          0          0        322
Producer             1 xxxxxxxxxx     0 P003     1  13          0           0          0          0        322
Producer             1 xxxxxxxxxx     0 P004     1  13          0           0          0          0        322
Producer             1 xxxxxxxxxx     0 P005     1  13          0           0          0          0        322
Producer             1 xxxxxxxxxx     0 P006     1  13          0           0          0          0        322
Producer             1 xxxxxxxxxx     0 P007     1  13          0           0          0          0        322

Consumer             8 xxxxxxxxxx     0 QC       1 100          0           0          2          1        322

To further explore the implications of this bug, I created a more elaborate test case. I created several types of data, and tested ordering against each “class.”

create table mytest as
select rownum pk, trunc(dbms_random.value(0,400000)) rnd,
floor(rownum/1000) type_1000, floor(rownum/5) type_5,
mod(rownum,1000) type_1000mod,mod(rownum,5) type_5mod,mod(rownum,10009) type_10000mod,
sysdate-rownum/100 as dt,
rpad('x',10,'x') pad from dual connect by level <=400000;

To spare you some of the testing, here are my results. Query ran:
select /*+PARALLEL(t,[DEGREE])*/* from mytest order by [ORDER BY];

A few quick conclusions:

• The first column of order matters for distribution.
• If the Order by column has repeated values, PQ sort will be limited by number of distinct values, but only if they are not grouped together.
• Index creation on time series (log table, stock table) is slow, “type” indexes are slow.
• If you reorder the keys of an index, you may affect the time it takes to create it.
• Follow up on the preceding, especially true if you try to put low cardinality columns first to improve compression.

One could dispute in which cases data in the table is ordered, and it’s amazing how many cases this is:

• Time series data — ever-growing data. The PK is ordered; the “insert date” is somewhat ordered.
• Data warehouses — bulk load files are often ordered via some conditions.
• Sometimes it is good to reorder a table, to improve data locality and compression in data warehouses. This can, however, have negative effects on index build time.
• Sometimes one of the intermediate steps will return an ordered set for the final processing.

One example of the last type, is analytics. But that’s for a separate blog post.

And finally, to end on an optimistic note, it appears that 11.2 has the index creation issue resolved, but the order by in queries is still bad.

Internet

Happy Birthday FireFox! The browser turns 5 years old today. Ryan Paul has more in his article
Firefox turns five: half a decade of Web liberation.

The Google Chromium blog has an interesting post called A 2x Faster Web about their attempts to speed up your surfing. “The initial results are very encouraging: when we download the top 25 websites over simulated home network connections, we see a significant improvement in performance – pages loaded up to 55% faster”, says the blog. This is still in early testing phases but sounds very promising.

Operating Systems

Steven J. Vaughan-Nichols at Computerworld reflects on his time using linux on the desktop in Opinion: Linux desktop turns 10; world yawns. The article needs to be taken with a grain of salt. He states in the preamble that “The credit for creating and marketing the first Linux desktop designed for ordinary users goes to Corel Corp., which launched Corel Linux OS 10 years ago, in November 1999″. I take umbrage with this statement because by 1999 Caldera and Mandrake were already making great strides with linux on the desktop, Caldera even had a pretty boxed edition out in stores 2 years ahead of Corel, and Mandrake had a box out in 1998 (if memory serves). Sure Corel had a big name and some marketing power, but they were not the first and certainly not the best (Corel Linux sucked IMHO).

Security

This next article is a combination of software, security, legal, patents and what the hell. Microsoft Patents Sudo?!! is a story with it all. Microsoft has taken something that has been in use in the unix community since the 80s (and on Mac since OSX) and patented it. I wonder if RBAC is also covered by this? The link to the patent and information on sudo are included in the article.

Help Net Security has a post about a recent Cenzic report on web server and browser vulnerabilities. Read all about it in Firefox most vulnerable browser, Safari close second.

Software

According to Ars Technica, Novell is “offering a new commercial add-in for Visual Studio that will allow software developers to test and package .NET applications for Linux without having to leave their Windows development environment”. The new add-in, called MonoVS is available now for a free 30 day trial.

According to the WithinWindows blog Microsoft may have used GPL code in it’s released Windows 7 USB/DVD Download tool without contacting the author or releasing the source code back to the community. The article has some evidence to support this claim. Microsoft has pulled the tool pending further investigation.

Virtualization

Slashdot has a note about The Remus project. This project adds live migration/failover functionality to the Xen hypervisor allowing virtual machines to (reportedly) seamlessly move to another host without losing data or active connections. This was one of the last things that VMWare ESX offered that I could not find in the open source (read free) world so I’ll be sure to check it out myself.

Business

HP to Acquire 3Com for $2.7 Billion is a new release at the HP site. There’s a lot in there, read it for yourself. Hopefully this will be a good boost for 3Com which has not made anything outstanding since the 3c501.

A couple of items on the AMD front. Firstly AMD and Intel Announce Settlement of All Antitrust and IP Disputes which I hope will bury the hatchet once and for all and allow both companies to get back to innovation. Secondly, AMD has release some more news about their future roadmap and the “fusion” of CPU with GPU in AMD avoiding Larrabee route on road to CPU/GPU “Fusion” by Jon Stokes.

Well there was a couple more items on the list but I am out of time. Have a great weekend!

So like get lost eh! Hoser!

Brad

This is the first Log Buffer published outside Pythian in quite a while, and we’d love to have more. Log Buffer has a regular readership, and so makes a great way to present yourself and your blog to the DBA community at large. To get started, just send an email to the Log Buffer coordinator.

And now, here is Log Buffer #169.

The “non-yielding scheduler” message comes with warnings like the following:

Process %ld:%ld:%ld (0x%lx) Worker 0x%p appears to be non-yielding on Scheduler %ld. Thread creation time: %time. Approx Thread CPU Used: kernel 0x ms, user 0x ms. Process Utilization 0x%. System Idle 0x%. Interval: 0x ms.

This means that there is a background thread that keeps running and making other worker threads yielding back, in time.

There are many causes of the “non-yielding scheduler” error. Memory dump analysis is necessary to determine the cause. Look up the error in MS Support Advanced Search and you will find plenty of KBs dedicated to it.

You may also find (not all cases) errors 17883, 17884 logged in the event viewer, containing information like this:

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17884
Date:
Time:
User:
Computer:
Description:
New queries assigned to process on Node 0x have not been picked
up by a worker thread in the last 0x seconds.
Blocking or long-running queries can contribute to this condition,
and may degrade client response time. Use the “max worker threads” configuration option to increase number
of allowable threads, or optimize current running queries. SQL Process Utilization: 0x%. System Idle: ox%%.

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17883
Date:
Time:
User:
Computer:
Description:
Process Worker appears to be non-yielding on Scheduler .
Thread creation time: . Approx Thread CPU Used: kernel ms, user ms.
Process Utilization %. System Idle %. Interval: ms.

There is a must-read whitepaper describing SQL Server Scheduling and how to interpret and diagnose Errors 17883, 17884, 17887, and 17888; please look here How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888

Recently, we had a client having same issue. The client runs a busy online business with thousands of connected users; databases sometimes include more than half a million tables.

We had a healthy counters and server didn’t seem to suffer from memory or I/O bottlenecks. We did suspect, however, that it is something to do with SQL Server internals, as we also received fatal exceptions pertaining to SPIDs <50 (system processes).

We looked at any Hardware Fault, outdated drivers, tempdb contention (there were nearly 0.5 datafiles/core).

We opened a case with MS support after the dumps continued to happen and their reply was:

After analyzing the dumps, we see that the thread has non-yielded while updating index usage stats. These stats are returned through DMV sys.dm_db_index_usage_stats.

According to MS, the issue is a known bug that has been corrected in SQL Server 2008 but isn’t expected to be resolved in SQL Server 2005.

Workaround

Enable trace flag 2330 to prevent the update of data in DMV in sys.dm_db_index_usage_stats.

This DMV retrieves information that about index usage; it does not, however, generate this information itself, but rather just returns this info from a cache inside SQL Server. This cache is empty when the server instance beside it doesn’t persist between instance restarts.

The impact of disabling this job was nothing to the server operations. There was a selective indexes maintenance job that depended completely on sys.dm_db_index_physical_stats, which is a different DMV.

Also, the query optimizer should be only affected if we stopped updating statistics, or are no longer optimizing the indexes which we were not doing here.

The affected DMV helps with auditing the indexes usage when we opt to diagnose instance performance, as it helps tracking the number of scan operations, the number of seek operations, the number of lookup operations, and some more.

It is worthwhile to note that sys.dm_db_index_operational_stats can report similar statistics (there are some differences though) as sys.dm_db_index_physical_stats.

Craig Freedman wrote a good blog: What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

Two days ago—and that’s the reason I’m writing this blog—Microsoft released a “FAST PUBLISH” KB: (Query performance decreases when sys.dm_db_index_usage_stats has large number of rows) about this issue and it mentions the following scenario:

• In Microsoft SQL Server 2005, you frequently perform DDL operations that involve dropping and recreating of lot of tables (especially temporary tables in tempdb database).
• You have a large number of entries (100,000 or more) in the sys.dm_db_index_usage_stats dynamic management view (DMV).

This aligns with what we had with the affected server (more than half a million tables can easily generate that). The KB mentions these workarounds:

1. Reduce the rate at which you create or drop temporary tables. (This may need extensive code change.)
2. Upgrade to SQL Server 2008. (Unfortunately, not everyone can afford that.)
3. Configure your SQL Server to use trace flag T2330 as a startup parameter. (This is what we have done and adieu la problème.)

Note: make sure that the trace is enabled by running DBCC TRACESTATUS(-1)

You can also look in the SQL Server error log and note whether the trace flags are mentioned, something like this:

Time Server -T1222
Time Server -T845
Time Server -T2330

One more KB that was released recently: (FIX: Error message in the SQL Server 2005 Errorlog file after the SQL Server service stops responding: “Timeout occurred while waiting for latch”); mentions similar symptoms that are related to SQL Server processing missing index information for a dynamic management view (DMV). This was fixed in SQL server 2005 Sp3 CU6

If you face similar issues, you should install the latest service pack and cumulative update, and check if you have any resource bottlenecks. You can try trace T2330 as well, and if you still get errors or dumps, then you had better open a case with Microsoft Support to analyze the dumps and provide a resolution.

Hope this helps!

http://bit.ly/37E9ld

But the basics are: No access to Rough Cuts or Downloads, for new subscribers only. It’s one of those “sign up and if you do not cancel after 10 days, we bill you” — and at $42.99 a month, that’s not a mistake you want to make. Must sign up by Nov. 24th.

To sign up now: https://ssl.safaribooksonline.com/tryitfree

I was asked to send this information along, so I am…Now’s your chance to skim High Performance MySQL, among other high quality books!

In SQL Server Integration services, User namespace variables are assigned values that are used across the package.

When developing, testing or debugging packages, we assign multiple values to those variables to test different scenarios. This is done using the variables window.

However, SSIS Script tasks can allow us to key in values for selected variables in run time. This looks more fun and keeps us from taking chances when we forget assigning variables’ values.

A tiny example:

• Return list of processes running on SQL server instance from SYS.SYSPROCESSES (I know it is legacy) where process ID (Spid) is larger than or equal to a value ( >50 for example to return user processes).
• Dump the returned list of SPIDs and associated info (cmd, waittype, loginame,…etc) into a flat file.

  I’m using a script task and a Data flow task. The Data Flow task contains an OLE DB Source and a flat file destination.

  

  Package

  The script task idea is just simple :

  • Pass a variable to the script indicating that we need to set some variables(s) value(s) interactively (Interactive mode).
  • In the script body, we will check for interactive mode and if it is true then we will use an InputBox to receive variables values.
  • Set the package variable(s) value(s) to the value(s) returned from inputbox(s).

  I have attached two DSTX files, one for SS2005 and one for SS2008; the 2005 file can be easily upgraded with a tiny change in the function declaration, but I opted to attach a 2008 file for simplicity.

  Interactive_Variables_value_2005.dtsx

  Interactive_Variables_value_2008.dtsx

  Here is also the code for the script task (2008). I hope this helps.

  ' Microsoft SQL Server Integration Services Script Task
  ' Write scripts using Microsoft Visual Basic
  ' The ScriptMain class is the entry point of the Script Task.
  
  Imports System
  Imports System.Data
  Imports System.Math
  Imports Microsoft.SqlServer.Dts.Runtime
  Imports System.Text
  Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel
  
    Partial Public Class ScriptMain
      Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  
      Enum ScriptResults
          Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
          Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
      End Enum
  
      Dim PackageName As String
  
      Public Sub Main()
          PackageName = Dts.Variables("PackageName").Value.ToString()
  
          If Dts.Variables("interactive_values").Value.Equals(True) Then
              Dts.TaskResult = PromptForParams()
          Else
              Dts.TaskResult = ScriptResults.Success
          End If
      End Sub
  
      Public Function PromptForParams() As ScriptResults
  
          Dim Response As String
          Dim spidint As Integer
  
          ' Default Task output to failure
          Dts.TaskResult = ScriptResults.Failure
  
          Response = InputBox("Get a list of SQl server processes that begin with SPID X " " Enter SQL server SPID, must be integer and > 0:", PackageName)
  
          If Response = String.Empty Then
              MsgBox("Cancelled! ", MsgBoxStyle.OkOnly, PackageName)
              Return ScriptResults.Failure
          End If
  
          If Integer.TryParse(Response, spidint) And spidint > 0 Then
              Dts.Variables("spid").Value = spidint
          Else
              MsgBox("Invalid value spid, must be integer and larger than Zero ", MsgBoxStyle.Critical, PackageName)
              Return ScriptResults.Failure
          End If
          Return ScriptResults.Success
      End Function
  End Class
  

  ]]> http://www.pythian.com/news/4812/ssis-tip-set-variables-values-interactively-in-ssis-packages/feed/ 0 http://www.pythian.com/news/4812/ssis-tip-set-variables-values-interactively-in-ssis-packages/