Question about reoccuring events

[phantom1000000]

Hi all,

I've written a piece of software that connects to a server's SQL database.

The software has a timer which fires every second, and the timer connects to the database every time and pulls out a list of current to do's from a table (CurrentToDoList). This database only has maximum of 100 rows in it, so not a huge demand on the server.

But now this software has been installed on several computers, each firing each second, etc.

And then there are the other events that connect to other databases on the server which are more taxing.

So far these are rhetorical questions, but I'm wondering how much of this can the server take? How many computers can run simultaneously before the server starts draining too many resources and not keeping up with the timer event.

So now the real question: What will happen if the server is too slow to deliver the information before another timer event fires?

[sknake]

You will start seeing timeouts and deadlocks. Deadlocks occur from poor database design and timeouts can be the same too, or it can be just too much load. As for what the server can take that depends a lot on the hardware and version(s) of software. The SQL Server Profiler will be your best bet to determine if you're hitting a bottleneck.

Some tips:
1) One second is too soon of an interval to produce any meaningful data. That would hammer the SQL Server and probably your user interface (i'm assuming you have a grid or such that is being updated).
2) Make sure your queries are well written and they are using the best indexes. You need to examine your query's execution plan and the table designs.

One thing you can do to alleviate query time on high I/O tables (which I think your to-do list qualifies) is using a read uncommitted transaction isolation level or the NOLOCK table hint, i.e.:

Select *
From ToDo (NOLOCK)
Where ...
Order By ...

Would changing your ToDo list checker to 10 seconds really hurt the user experience?

Also take a look at System.Data.SqlClient.SqlDependency . It will notify you when the table changes so you can probably bring down the query load on your server

[phantom1000000]

Thanks for that Sknake. That was really helpful.
Never heard of alot of those terms (Nolock, uncommitted transaction isolation level, SqlDependency), but sounds like they are very much worth looking into.

[sknake]

You're welcome

Please mark this thread as solved if I have answered your question and good luck! I look forward to seeing you post