THE SQL Server Blog Spot on the Web

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

Paul Nielsen

www.SQLServerBible.com

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: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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..) ...fun times

Denis

December 14, 2007 12:33 PM
 

Joe Webb said:

LOL!!! Thanks for sharing that, Paul!

Cheers!

Joe

December 14, 2007 3:19 PM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

News

news item test
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement