THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

A funny thing happened on the way to the database

A funny thing happened the other day. I was running 64-bit Dev edition on my 4Gb Vista 64-bit notebook. SQL Server was set to 1Gb RAM and I was running 10 scripts each continually inserting a new randomly generated person into a table. The goal was to generate 500 million rows. Meanwhile, I was checking email, doing some browsing, doing some writing – normal things. I couldn’t even feel a difference with SQL Server running smoothly in the background.  I was feeling rather smug about my 64-bit machine.

A couple hours later.. 

the machine started slowing down just a wee bit, and watching the Vista widget reporting memory the used memory started crawling upward – 75%, 83%, eventually it stopped crawling at 97% memory used. At this point the machine was taking minutes to respond to mouse clicks. I was up to about 300 million rows in the Person table when I stopped the scripts.

I checked SQL Server memory and it was still right at 1Gb target and 1Gb used. What was going on? Looking at Task Manager, SQLWB.exe was at 1.7Gb. What?!?! How did that happen?






Baffled, I mentioned it to Sue Hoegemeier, another Colorado based SQL Server MVP who in a rather scary way can quote obscure knowledgebase articles. She laughed and with a wry grin said “Set NoCount On.” Of course I do that religiously in every proc “AS SET NoCount ON …”. But in these quick scripts I had overlooked the nocount. SQLWB (Management Studio was originally going to be named SQL WorkBench hence the name) was filling up with millions and millions of "1 row affected" messages. 

Published Friday, December 14, 2007 11:20 AM by Paul Nielsen
Filed under: , ,



Denis Gobo said:

At least you didn't script out a table to create a history table. You change the name in the create but forget to do it in the drop part on top (if exists drop table..) times


December 14, 2007 12:33 PM

Joe Webb said:

LOL!!! Thanks for sharing that, Paul!



December 14, 2007 3:19 PM
New Comments to this post are disabled

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog



news item test
Privacy Statement