Here’s the command to issue at a command prompt to enable database caching for a MS SQL 2005 server:
aspnet_regsql -S [servername] -U [username] -d [databasename] -ed -et -t [tablename]
Here’s some variables to throw into your Page Directives:
<%@ OutputCache Duration=”20″ VaryByParam=”none” VaryByCustom=”browser” SqlDependency=”CommandNotification” %>
Around 4:45pm, I was able to witness the most beautiful sight! Something that I’ve been wanting to see. Scratch that, something I needed to see for over 2 weeks now.
For over 2 weeks straight. Literally, all day, all night, and sometimes overnight all-nighters, I spent trying to figure out the database issue that has been causing so many problems with BodyMod.org.
For all this time, my DB server would run at 100% CPU utilization, and with throwing up errors that only returned 3 sites to troubleshoot from in Google. I tried everything. I rewrote my code (a whole hell of a lot), converted a lot of my sql statements in code to stored procedures, added more ram to the server, changed pagefile settings, messed with IIS settings, modified database connecting code, added / messed with connection pooling though .NET to my DB server, created new indexes, reindexed the old ones, changed network configurations, changed database security privileges, installed countless patches, defragged the drive, and tried every server hack under the sun to get it to work. I ran the SQL Analyzer, profiler, tuning wizard. Nothing.
My web server would run nicely for about 1.5 - 2.5 hours and then shit the bed with constant ‘Insufficient memory to run that query’ statements to a point where the database services would just seize and die. This of course required me to login and start it manually (the automatic “fail-safes” never kicked in). On top of that, my error logs would have this message 20-30 times per second(!) for an entire day. We’re talking log files in the GB range:
2008-01-17 03:41:48.43 spid The query notification dialog on conversation handle '{GUID}.' closed due to the following error: '-8470Remote service has been dropped.
I am posting this so that if anyone else has this problem, you’ll know what to do. The fix? Reinstall SQL server. Yep. I backed it all up, wiped it out, and reinstalled it from scratch. Don’t just unmount and then attach the DB’s either. Backup, then Restore.
What did I finally get after all this BS? The most beautiful site…
I guess the good that came of this is that I now have an extremely fine tuned website, I know more about SQL memory management than I would ever need to know, I can trace a SQL server like a champ, and I learned how to create stored procedures. So I guess it’s not all doom and gloom, even though there was a point where I was just going to flip the switch and say “fuck it” (after I’d been up about 48+ hours straight staring at SQL Logs and a green wavey line about a centimeter higher than would have preferred.)
I’m also hoping that it stays this way. I’m just going to have to keep my fingers crossed for the next few days.
I’ll go into the details and some sample code later, I have a bit of a crisis on my hands right now, but in short the ROWCOUNT() feature in SQL 2005 ROCKS!
I just got my queries down from 40-50 seconds (i think that’s the prob I’ve been having) to 183 milliseconds! Hells Yeah!
More on this awesomeness later =)
I had added a bunch more RAM to my database server (MS SQL 2005) and it eatted it all up! This isn’t an exaggeration either. It consumed every little byte! So my question… why?
After a lot of hair pulling and then a little googling, I found my answer. By default SQL 2005 consumes all available memory. Not cool. To set a limit, just open up the SQL Design Console and Right click on the server instance, then choose Properties. You’ll see a Memory tab on the left. In there you can set it to use min / max MB of RAM.
After I updated it to 512 less than what I had, my server ran much smoother. The problem was that SQL would eat up all the available ram and then not give any back to other apps and services that needed it to run properly.
Fook!
What I learned today:
1. .net tracing is freaking awesome. Talk about being able to pinpoint your problems! Sheesh! I wish I had this a looooong time ago.
2. My db server needs more ram. I guess 512 isn’t enough 
All this time, I figured it was my code, or possibly something with the web server that was causing my site (BodyMod.org) to run so slow. Nope… DB server. I ran some trace events tight around all the DB calls, and low and behold, that’s where all the time was coming from. Up to 15+ seconds for a DB call!
So I head on over to my db server to check out whats up. I pull up my task manager and see cpu at 60-70% ( not great, but not bad ), and my ram usage at about 615 out of 1024. Not bad either. So wtf?
Oops… only 512 of that is physical. the other 100 is virtual. Crap.
So, I sent an IM to my server guy (it’s 5am, so I doubt he’s up) that I wanted to buy some more ram. I figure with prices the way they are, I’ll bump it up to 2gb =)
Better be the miracle fix I was looking for.
So to all you bmod members out there, hold tight, it’s about to get crazy fast! (probably not, but I dream big =)
A while ago I migrated everything from mssql 2000 sp4 to mssql 2005. For the most part, everything went ok. Then it hit me, my site halted because my alloted space for my transaction logs was reached! to make things worse, the way to fix this in sql2k didn’t exist in 2k5. So, it was on to Google. After hours of research and testing, more testing, and then more testing, here’s a solution that I got to work.
1. Open MS SQL Server Management Studio
2. Expand Management > Maintenance Plans
3. Create a new plan called ‘Shrink Logs’
4. Create a new Subplan that occurs every day that Executes a T-SQL Statement task.
5. Enter in the following:
USE [DATABASENAME]
GO
DBCC SHRINKFILE ('LOGFILENAME' , 0)
GO
That’ll wipe out your transaction logs every night.
If you don’t want to wipe out your logs, DO NOT RUN THIS. You’ll have to figure something else out =)
Hope this saves you some time!
Make sure SQLGrinder App is not open when you do this.
1. Download the Microsoft JDBC Driver
Go to the Microsoft JDBC Driver download page:
Follow the link at the bottom of the page to download the Unix version of the JDBC driver (.tar.gz file).
2. Extract the downloaded Archive
You can safely extract the downloaded file with StuffIt Expander. The extracted files are in the folder “sqljdbc_1.0″.
3. Install the JDBC Driver
To install the Microsoft JDBC Driver you have to copy the actual driver file (sqljdbc.jar) from the sqljdbc_1.0/enu folder to your Java classpath (/Library/Java/Extensions).
Don’t create sub-directories in /Library/Java/Extensions (like lib/). The .jar file has to be in the /Library/Java/Extensions folder.
4. Open SQLGrinder
You will now have a Drive labeled ‘MS SQL Server 2005′.
You’re done!