Skip to main content
gamedev.net
🔒 Locked

[web] Two SQL Server Questions

Started by CyberSlag5k 5 replies 1k views
Original Post
CyberSlag5k
CyberSlag5k
1. I'm running SQL Server 2005 Express, and I've noticed sometimes (not often) it'll just be extremely slow to do anything. It'll take like 30 seconds to open up a table or run a simple query that normally takes less than a second. Usually when this happens, I just reboot, but one day this database is going to support my game server. When that happens, this will be a significant problem. Does anyone know what might cause this? 2. This database is going to be handling lots of, often small, simple, LINQ queries. Would a SQL Server 2008 Express database be a better choice for performance, or some other reason? I've read that on the whole 2008 is a little slower than 2005, and that it's only worth switching to if you plan to use the new 2008 features, which I won't (I'm a very basic database user, I'm barely using 2005). Are there any differing opinions on this, or perhaps a reason why my particular needs would be better met by 2008?
Without order nothing can exist - without chaos nothing can evolve.
leandro81
leandro81
Hi,

I don't know if it will help you, but I had the same problem with my SQL server. Every other day it would become slow and unresponsive, and I had to manually restart the SQL Server service.

Eventually, when we upgraded the server memory, the problem was solved.
Nik02
Nik02
I remember there was a known issue with the initial database engine loading delay in SQLS2005 prior to some service pack (I don't remember which SP).

If you determine that this is not your problem, there is support available for SQL Server performance issues here.

I've had some "unexplained" delay problems with SQL Server in the past, and on closer inspection they have invariably been related to low availability of physical memory.

Anyway, 2008 is actually slightly faster in some operations like inserting to large tables, judging by my experience. You can also increase I/O performance significantly by using compressed paging in 2008, a feature that 2005 does not support at all.

As a side note, LINQ is not actually evaluated as-is on the SQL Server - instead, the data user tier (software connected to the DB) generates SQL statements corresponding to the LINQ statements and sends them to the server to execute them.
Niko Suni
cyansoft
cyansoft
More importantly, according to Tim Mallalieu, the program manager of the LINQ to SQL and Entity Framework projects, Microsoft no longer recommends using LINQ to manipulate SQL server databases:

Quote:

We're making significant investments in the Entity Framework such that as of .NET 4.0 the Entity Framework will be our recommended data access solution for LINQ to relational scenarios.


If you haven't written any code yet for your database access layer, you may want to use the Entity Framework or T-SQL commands and/or stored procedures instead.

Sander
Sander
At a previous company I worked for it was standard practice to automatically reboot SQL server daily just because of this sort of things.
evolutional
evolutional
It sounds like the database might be automatically closing down (which is the default). Right click the DB in SSMMS and click Options - from there change the "Auto Close" value to false.
CyberSlag5k
CyberSlag5k
Quote:
Original post by evolutional
It sounds like the database might be automatically closing down (which is the default). Right click the DB in SSMMS and click Options - from there change the "Auto Close" value to false.


Done (it was set to True). I'll see if that helps. Thanks!
Without order nothing can exist - without chaos nothing can evolve.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.