2

Im getting nasty performance on my server.

It happens radmonly while accessing a website, but more often, right on the first time I load it. The server/webapp are still in dev, so I'm the only one accessing it.

Specifically, timeout expired error. Can only post one link, so screenshot follows...

I started running perfmon on the Database VM and after a couple page loads, got it up to 100! Here's the screenshot.

When it went to 100, it still loaded the page, there was just a delay. On the other times, I can imagine it went higher.

And, apparently it cuts off at 100, so it could have gone even higher, but not showing it.

This question seems to come close in describing the issue, but Im not sure what to do about it: SQL Server Slow but CPU Normal

As far as db access, my queries are all automatically optimized, via using linq-to-sql. I dont do any indexing on the db...but the load right now is just me hitting it.

so, so-far my gut is telling me its some kind of hyper-v configuration issue.

Specs:

Single Server: Xeon E5520 @2.27GHz (2 processors). Windows 2008 Ent. 6gb of rame, broken down: 2GB - Parent 2Gb - DB server running SQL 2008 Web Edition 1.25Gb - Web server running IIS7 .75GB - DNS, Misc

Hyper-V was setup by hosting company, probably just double clicked and clicked next, next, next until it was done, so I'm thinking there's got to be some best practices for configuration that's going to make all the difference.

Help! Thanks!

Shane.

Community
  • 1

3 Answers3

1

First Linq-To-SQL isn't optimized unless you have indexes on the tables. Anyone who says otherwise is wrong.

Windows 2008 with SQL 2008 running 1.25 Gigs of RAM is nothing. Windows alone needs about that. You have almost no RAM for the database. It needs about 70 Megs just to start the SQL Server. Must less start loading data into memory, then there's room needed for the procedure cache. As everything is dynamic SQL (via Linq To SQL) you'll need lots of room for procedure cache as odds are every command you run against the database will need to be recompiled.

Can you post some perfmon data from the SQL Server when the problem is happening?

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • "As everything is dynamic SQL (via Linq To SQL) you'll need lots of room for procedure cache as odds are every command you run against the database will need to be recompiled." -> queries are parameterized so this is wrong – Mark Sowul Nov 24 '13 at 14:35
  • Depends on how the queries are written in Linq. I've seen some truly horrible stuff out there with different columns in the where clause for different situations bloating the hell out of the plan cache. Also Linq tends to make the parameters which it uses the same size as the value, so when passing in "Bob" and "Fred" you get two plans because the parameters are different sizes. – mrdenny Dec 05 '13 at 19:15
0

In case there was any doubt Hyper-V is completely new to me and my hosting co.

http://technet.microsoft.com/en-us/library/dd440865(WS.10).aspx

Im running my DB VHD as dynamic, so one I remove the snapshots ive got an wait for it to merge, I'll conver to Fixed.

Pass-through would be best, but would take more time, so try this for now.

Probably will do this on all three of my VHD's.

How big a difference do you all think this will make?

0

Just as well this isnt in production. yikes.

So, in the process of converting the VHD to Fixed, it's now corrupted somehow and I cant get it to load the VM. Cant even go back to the old one and reload.

Im getting the error:

Stop c000021a the session manager failed to create protected prefixes system process terminated unexpectedly

whenever I try to start that VM back up.

thinking of just wiping the server off and going without HyperV. (oh, by the way, 2gb for SQL... not that it's much diff from 1.25).

Since the machine has got 6gb and I only need to run one website with one database on sql2008,maybe that's the way to go.