TV Server - possible speed improvement? (1 Viewer)

[jmbillings]

Hi,

Just to let you know, i've made a change to my SQLExpress config - namely to disable the "AUTO_CLOSE" parameter on the TV Server db.
I'm curious to see if this makes channel changes and other stuff more speedy by stopping sql having to re-open the DB after inactivity.

I'm not sure yet on any outcome but thought i'd mention it in case anyone else wanted to try it (or has a good reason for NOT trying it of course!)

 

[Cybertex]

Hi,

Just to let you know, i've made a change to my SQLExpress config - namely to disable the "AUTO_CLOSE" parameter on the TV Server db.
I'm curious to see if this makes channel changes and other stuff more speedy by stopping sql having to re-open the DB after inactivity.

I'm not sure yet on any outcome but thought i'd mention it in case anyone else wanted to try it (or has a good reason for NOT trying it of course!)

If I'm not wrong (should check this evening at home) tvservice never disconnects from SQLServer; so the DB is never closed as long TVserver service is running.

 

[jmbillings]

Yes, I figured the DB Connection probably doesn't get closed off, however the auto_close sounds more like that isn't the case - it's more simply being idle. The following text (from linky ) explains it - although I could be reading it wrong

Another minor change in SQL Express that has a lesser impact on perceived performance is the way the AUTO_CLOSE property of a database is handled. The AUTO_CLOSE property allows a database to be managed more like any other file in Windows by releasing the lock that SQL Server would normally hold on the file and allowing it to be copied, backed up, etc. This type of behavior is very important to supporting XCopy type deployment, which is a core piece of functionality needed to support Visual Studio ClickOnce™ Deployment. Supporting ClickOnce is something we wanted to do with SQL Express, so we modified the behavior of the CREATE DATABASE functionality in SQL Express in order to set AUTO_CLOSE to True for all databases. The results of this is that after 300 ms of inactivity for a database, SQL Express will close the database and release the lock on the file. It's important to recognize that Close and Detach are two different things. SQL Express still maintains metadata information about the closed databases, we just don't lock the file. When a request comes in that requires the use of a Closed database, we Open the database using the information stored in metadata. Opening a database that has been closed does not have a significant performance impact, but it can have some interesting side effects (and by interesting I mean negative) for certain kinds of operations:
# If you are running an iterative process that includes a cross-database query and the period of the iteration exceeds 300 ms, the external database your process access would go through a Close/Open cycle during every iteration. This can add up over a large number of iterations and become a large component of the process.
#
If you have a process that polls a database at intervals, and that interval is larger than 300 ms, the database will go through a Close/Open cycle with every poll. In this case, the direct performance impact is negligible, but the fact that SQL Server writes an entry to the Windows Application Log every time a database is opened can result in your log filling up unexpectedly. (One example of this is if you have installed Reporting Services Express; it polls one of it's databases every couple of minutes.)

 

[Cybertex]

"Cool" (awful, in fact.)
Didn't know this "feature" of SQL Server Express; the info in SQL Server Books online is -of course- missing.

It could worth a try, just to see if there is something noticeable in terms of performance.

As a DBA, I've a lot of other doubts about this modified behaviour, just (quoting from blog) "allowing it to be copied, backed up, etc." I guess if in this "etc." there is also "deleted, moved" and other catastrophics actions

 

[jmbillings]

Well, I only found this today at work trying to track down a problem one of our customers was experiencing - our main app re-connects itself if it encounters an error, but one part in an separate module doesn't and it was that part that was having trouble... so we'll have to see what it does.
There was another issue on 2003 server SP1/2 where MS introduced a security "fix" to try and stop Syn attacks I think, but apparently that can sometimes see heavy genuine traffic as an attack and thus block it. The exact error we were getting was in the technet article. Anyway, all a bit off topic now, will let you know what I find!

(And yes - I don't like the idea of faffing around with the mdf + ldf while the service is running either!)