Maximum Recommended Databases

I've read MSDN that the theoretical maximum number of databases per
SQL server instance is 32,767. But I'd like to know if there is a
real world recommendation from Microsoft. I know this sounds like a
ridiculous question, but I've inherited an instance with approximately
1,000 databases (and growing) and I'm trying to determine when I
should expect major performance issues (don't laugh). I have a plan
for reducing the number of databases, but it won't happen over night.
I know a lot of this depends on database usage, but I'm just looking
for any information I can find. Thanks in advance!
I have a client with over 5000 databases and still growing. The usage
pattern is such that only a few dozen or even a few hundred are heavily used
at any one time. The system is performing quite well.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||I don't think I'm going to provide any new information that you don't
already have. But this is kind of like asking how big can a database be, or
how many rows can I get into a table? As the number of databases goes up,
the usage pattern really determines how detrimental each additional database
is to performance (let's leave administration costs out of this for now).
So many factors are involved that it's impossible for anyone to come up with
a real number that would apply to your situation and my situation
identically, even if you could feasibly perform all the real-world tests
required to come up with such a number...
When should you expect performance issues? When your usage patterns exceed
your hardware's capabilities. It is absolutely impossible to tell you that
it will happen when you add 5 or 500 more databases. In fact, it could
conceivably happen after you /*remove*/ databases ... if users of the
remaining databases coincidentally change their usage patterns.
I have relatively high-volume systems with over 500 databases, and they work
very well. Would I want to throw another 500 databases on there? Not in
one shot, I don't think. But it's not the number itself that scares me.
It's the fact that 10 or 15 of those (given my current scenario) would
likely end up high transaction hitters that *could* start to destabilize the
system. On the other hand, if they're all duds, then 500 could be added
easily without any impact to the system.
Aaron Bertrand
SQL Server MVP
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
wrote:

>I've read MSDN that the theoretical maximum number of databases per
>SQL server instance is 32,767. But I'd like to know if there is a
>real world recommendation from Microsoft. I know this sounds like a
>ridiculous question, but I've inherited an instance with approximately
>1,000 databases (and growing) and I'm trying to determine when I
>should expect major performance issues (don't laugh).
The critical problem would probably be that they will never share
pages in a working set, so caching will be less effective. But that's
if they're all active at once, which is unlikely ... isn't it?
A few more open file handles at the OS level, ... nah. Whatever
insight you can get on data locality and access patterns is probably
best, but hey, that's true even if it's all a single database, too!
Josh
|||> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective.
Hmm, can you elaborate on this? When you have several databases, the data for each customer (or
whatever differentiate these databases) obviously need to be in separate tables, per customer. So,
data for one customer is on a separate set of pages compared to some other customer meaning data
retrieved for one customer cannot be used when querying from some other customer. But the same thing
would happen if the same set of table were in one database (except for very small tables where mixed
extents come into play). But if this is re-modeled and all customers share the same table, we have a
different situation...

> A few more open file handles at the OS level
Interesting you mention this, since this can impact caching. With many databases, setting auto close
might be needed (especially in 32 bit due to OS limitation and open file handles). And when a
database is auto closed, data in cache is removed (I know for sure this applies to plans, I'd have
to do a test to verify it applies to data).
Perhaps plan caching is a bigger issue? Assuming best practices are followed and stored procedures
are used. Now, we would end up with duplicate procedures, one set for each database. Even if we
don't use stored procedures, we won't be able to re-use cached plans across databases...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:799fb3l6bsc2its7r7mfma3kjqr65gekh1@.4ax.com...
> On Mon, 06 Aug 2007 19:37:24 -0000, Chris Reeder <clreeder@.gmail.com>
> wrote:
>
> The critical problem would probably be that they will never share
> pages in a working set, so caching will be less effective. But that's
> if they're all active at once, which is unlikely ... isn't it?
> A few more open file handles at the OS level, ... nah. Whatever
> insight you can get on data locality and access patterns is probably
> best, but hey, that's true even if it's all a single database, too!
> Josh
>
|||I have a client with 6500+ databases. Biggest issues are rolling out
changes, size of msdb database (make it BIG to begin with to avoid
fragmentation from autogrowths), and most 3rd party tools break down when
you try to use them against the server due to massive amounts of system data
they typically try to read. I had a developer build a multi-threaded
"database shotgun" for doing maintenance stuff. HUGE help!
TheSQLGuru
President
Indicium Resources, Inc.
"Chris Reeder" <clreeder@.gmail.com> wrote in message
news:1186429044.319226.206990@.b79g2000hse.googlegr oups.com...
> I've read MSDN that the theoretical maximum number of databases per
> SQL server instance is 32,767. But I'd like to know if there is a
> real world recommendation from Microsoft. I know this sounds like a
> ridiculous question, but I've inherited an instance with approximately
> 1,000 databases (and growing) and I'm trying to determine when I
> should expect major performance issues (don't laugh). I have a plan
> for reducing the number of databases, but it won't happen over night.
> I know a lot of this depends on database usage, but I'm just looking
> for any information I can find. Thanks in advance!
>
|||> they typically try to read. I had a developer build a multi-threaded
> "database shotgun" for doing maintenance stuff. HUGE help!
I use SQL Farm Combine for this.
http://www.sqlfarms.com/
|||On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Hmm, can you elaborate on this?
Just speculating wildly.
If they are a single database, there is at least a *chance* customers,
users, sessions, etc will share pages!

>Perhaps plan caching is a bigger issue?
Ugh, you're right!
But then, wouldn't this be quickly obvious to anyone running
hundreds++ of databases? And the initial comments mostly seem to be
that it runs pretty well.
Josh
|||> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
Agreed. To give a more precise answer, we would have to know the data model after coalescing into
one database.

> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
True. But "run pretty well" doesn't mean that it cannot run better... :-)
Of course, this as well depends on the app. If stored procedures are used, then I expect to se some
benefit of having, for a particular proc, only one plan in cache instead of one per database. If
procedures are not used, then chances are that we mostly have exact text matching of the query
string in cache, and re-use would be minimal even if all is in the same database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9acib3h5ct5mkgtoectjg2s9aqe8k5633k@.4ax.com...
> On Tue, 7 Aug 2007 08:41:20 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Just speculating wildly.
> If they are a single database, there is at least a *chance* customers,
> users, sessions, etc will share pages!
>
> Ugh, you're right!
> But then, wouldn't this be quickly obvious to anyone running
> hundreds++ of databases? And the initial comments mostly seem to be
> that it runs pretty well.
> Josh
>
>

MAX, subquery, TOP or something else?

Ive tried many different ways to get this to work right and cant seem to get it. Should I use a Subquery, MAX, TOP or something else?!

I want the last note only for this field:("pc.Description AS Notes").

Any help is greatly appreciated!!!

CODE

SET NOCOUNT ON

SELECT
CONVERT(VARCHAR,pv.visit,101) AS DOS,
pv.TicketNumber,
ISNULL(pp.first,'')+ ' '+ ISNULL(pp.last,'')AS [Patient Name],
ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.Address2,'')+ ' '+ ISNULL(pp.City,'')+ ' '+ ISNULL(pp.State,'')+ ' '+ ISNULL(pp.Zip,'') AS [Patient Address],
IsNull(CONVERT(varchar,pp.Birthdate,101), '') AS Birthdate,
pp.PatientId,
ISNULL(ic.ListName,'') AS [Current Carrier],
ISNULL(pi.InsuredId,'') AS [Insured ID],
df.ListName AS Doctor,
df1.ListName AS Facility,
df2.ListName AS Company,
pva.PatBalance AS [Visit Pat Balance],
pva.InsBalance AS [Visit Ins Balance],
ppa.PatBalance AS [Total PatBalance],
ppa.InsBalance AS [Total InsBalance],
ISNULL(CONVERT(VARCHAR,g.LastStatement,101),'')AS [Last Statement]
-- pc.Description AS Notes,
-- MAX(pc.Created)

FROM PatientVisit pv
LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT OUTER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT JOIN PatientInsurance pi ON pv.CurrentPICarrierId = pi.PatientInsuranceId
LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId AND pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
LEFT OUTER JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId
LEFT JOIN [DoctorFacility] AS df2 ON pv.[CompanyId]= df2.[DoctorFacilityId]
LEFT OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
LEFT JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId AND pv.PatientVisitId = pc.PatientVisitId

WHERE pv.PatientVisitId = ('6')

-- GROUP BY pv.visit, pv.ticketnumber, pp.first, pp.last, pp.address1, pp.address2, pp.city, pp.state, pp.zip,
-- pp.birthdate, pp.patientid, ic.listname, PI.insuredid, df.listname, df1.listname, df2.listname, pva.patbalance,
-- pva.insbalance, ppa.patbalance, ppa.insbalance, g.laststatement, pc.description,pc.created

As you suggested there are a few ways you could do it, a subquery should be as good as any, and simpler than most others

SELECT ..., (SELECT TOP 1 description
FROM PatientCorrespondence
WHERE PatientProfileID = pp.PatientProfileID AND PatientVisitID = pv.PatientVisitID
ORDER BY Created DESC) AS Notes
FROM ...

|||SNMSDN - you are awesome! THANKS!

MAX() and DATEDIFF() question

I've written some software that listens to meters on a specified port and
writes XML files when any of those meters "speak." There are some 200 of
them, and each is programmed to "speak" at 10 second intervals, so there's a
fair amount of traffic.
Now what I've been asked to do is write a record to a database for each of
these meters at ONE MINUTE intervals. At first I simply set up a one minute
timer and pointed my software to the directory where the XML files live and
said "write a record for every file in there." But, alas, occasionally one
(or several) of the meters' files were being overwritten at that particular
time and thus weren't available.
So, I changed things so that each time a meter speaks, I check the database
to see if it has been a minute or more since the last database update for
that meter, and if it has I write a record out. Now this works fine, but it
uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
Pentium 3 system is hijacked.
So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
functions) the likely culprit for this inefficiency? My colleague believes
that it is, since (he says) calling MAX() forces a search through all the
records contained in the table (potentially a boatload). Here's the query:
SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "') from
" + MeterName
Thanks in advance for any advice on improving ANY of this!
Is there an index on the LastUpdateTime column? - since that might make a
significant difference.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"BCM" <BCM@.none.com> wrote in message news:4104f338$1@.news.unc.edu...
I've written some software that listens to meters on a specified port and
writes XML files when any of those meters "speak." There are some 200 of
them, and each is programmed to "speak" at 10 second intervals, so there's a
fair amount of traffic.
Now what I've been asked to do is write a record to a database for each of
these meters at ONE MINUTE intervals. At first I simply set up a one minute
timer and pointed my software to the directory where the XML files live and
said "write a record for every file in there." But, alas, occasionally one
(or several) of the meters' files were being overwritten at that particular
time and thus weren't available.
So, I changed things so that each time a meter speaks, I check the database
to see if it has been a minute or more since the last database update for
that meter, and if it has I write a record out. Now this works fine, but it
uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
Pentium 3 system is hijacked.
So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
functions) the likely culprit for this inefficiency? My colleague believes
that it is, since (he says) calling MAX() forces a search through all the
records contained in the table (potentially a boatload). Here's the query:
SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "') from
" + MeterName
Thanks in advance for any advice on improving ANY of this!
|||Hi,
Just a thought but the way I'd approach it is to have a 'feeder' table that
whenever one of your meters 'speaks' you update the table with the info
irrrespective of whether there is already a record in there. Don't have any
indexes on this table so writing is very quick. Also dont close / re-open
the connection for each write, keep it open and re-use. Then have a 'job' on
your database server run every minute that takes the latest reading for each
meter and updates the main table deleting the records in the feeder table
when it is finished. If you wrap this job in a transaction no writes can be
made whilst the update is progressing. This is nice as far as anatomic
transactions are concerned but could cause a block on your updater.
Therefore if your table has an AutoIncrementing field (Identity in TSQL)
then when the server job starts it reads the Max value and processes any
with the Autonumber <= to that value, thereby ignoring any new ones entered
whilst the job is running, finally deleting all records where the
Incrementing field is <= to the value read at the start
If you need any more info or explanations of my ramblings just shout.
Graham
"BCM" <BCM@.none.com> wrote in message news:4104f338$1@.news.unc.edu...
> I've written some software that listens to meters on a specified port and
> writes XML files when any of those meters "speak." There are some 200 of
> them, and each is programmed to "speak" at 10 second intervals, so there's
a
> fair amount of traffic.
> Now what I've been asked to do is write a record to a database for each of
> these meters at ONE MINUTE intervals. At first I simply set up a one
minute
> timer and pointed my software to the directory where the XML files live
and
> said "write a record for every file in there." But, alas, occasionally one
> (or several) of the meters' files were being overwritten at that
particular
> time and thus weren't available.
> So, I changed things so that each time a meter speaks, I check the
database
> to see if it has been a minute or more since the last database update for
> that meter, and if it has I write a record out. Now this works fine, but
it
> uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
> Pentium 3 system is hijacked.
> So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
> functions) the likely culprit for this inefficiency? My colleague believes
> that it is, since (he says) calling MAX() forces a search through all the
> records contained in the table (potentially a boatload). Here's the query:
> SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "')
from
> " + MeterName
> Thanks in advance for any advice on improving ANY of this!
>

MAX() and DATEDIFF() question

I've written some software that listens to meters on a specified port and
writes XML files when any of those meters "speak." There are some 200 of
them, and each is programmed to "speak" at 10 second intervals, so there's a
fair amount of traffic.
Now what I've been asked to do is write a record to a database for each of
these meters at ONE MINUTE intervals. At first I simply set up a one minute
timer and pointed my software to the directory where the XML files live and
said "write a record for every file in there." But, alas, occasionally one
(or several) of the meters' files were being overwritten at that particular
time and thus weren't available.
So, I changed things so that each time a meter speaks, I check the database
to see if it has been a minute or more since the last database update for
that meter, and if it has I write a record out. Now this works fine, but it
uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
Pentium 3 system is hijacked.
So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
functions) the likely culprit for this inefficiency? My colleague believes
that it is, since (he says) calling MAX() forces a search through all the
records contained in the table (potentially a boatload). Here's the query:
SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "') from
" + MeterName
Thanks in advance for any advice on improving ANY of this!
Where there are a few things wrong with this query as far as efficiency is
concerned. First there is no WHERE clause so SQL Server has no choice but
to scan the entire table unless there is a proper index it can use on
LastUpdateTime. What you should do is use EXISTS to test for the existence
of a qualifying row. EXISTS will stop looking when it finds the first row
that matches the expression and if you have a proper index and WHERE clause
this should be a simple seek. Create a stored procedure that you can call
and have it return a 1 or 0. Make sure you have an index on LastUpdateTime
CREATE PROCEDURE YourProc
AS
SET NOCOUNT ON
DECLARE @.DateTime DATETIME, @.Return INT
SET @.DateTime = DATEADD(ss,-60,GEDTATE())
IF EXISTS (SELECT * FROM MeterName WHERE LastUpdateTime > @.DateTime)
SET @.Return = 0
ELSE
SET @.Return = 1
RETURN @.Return
Andrew J. Kelly SQL MVP
"BCM" <BCM@.none.com> wrote in message news:4104f360$1@.news.unc.edu...
> I've written some software that listens to meters on a specified port and
> writes XML files when any of those meters "speak." There are some 200 of
> them, and each is programmed to "speak" at 10 second intervals, so there's
a
> fair amount of traffic.
> Now what I've been asked to do is write a record to a database for each of
> these meters at ONE MINUTE intervals. At first I simply set up a one
minute
> timer and pointed my software to the directory where the XML files live
and
> said "write a record for every file in there." But, alas, occasionally one
> (or several) of the meters' files were being overwritten at that
particular
> time and thus weren't available.
> So, I changed things so that each time a meter speaks, I check the
database
> to see if it has been a minute or more since the last database update for
> that meter, and if it has I write a record out. Now this works fine, but
it
> uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
> Pentium 3 system is hijacked.
> So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
> functions) the likely culprit for this inefficiency? My colleague believes
> that it is, since (he says) calling MAX() forces a search through all the
> records contained in the table (potentially a boatload). Here's the query:
> SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "')
from
> " + MeterName
> Thanks in advance for any advice on improving ANY of this!
>

MAX() and DATEDIFF() question

I've written some software that listens to meters on a specified port and
writes XML files when any of those meters "speak." There are some 200 of
them, and each is programmed to "speak" at 10 second intervals, so there's a
fair amount of traffic.
Now what I've been asked to do is write a record to a database for each of
these meters at ONE MINUTE intervals. At first I simply set up a one minute
timer and pointed my software to the directory where the XML files live and
said "write a record for every file in there." But, alas, occasionally one
(or several) of the meters' files were being overwritten at that particular
time and thus weren't available.
So, I changed things so that each time a meter speaks, I check the database
to see if it has been a minute or more since the last database update for
that meter, and if it has I write a record out. Now this works fine, but it
uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
Pentium 3 system is hijacked.
So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
functions) the likely culprit for this inefficiency? My colleague believes
that it is, since (he says) calling MAX() forces a search through all the
records contained in the table (potentially a boatload). Here's the query:
SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "') from
" + MeterName
Thanks in advance for any advice on improving ANY of this!
..org
BCM,
Do you have an index on LastUpdateTime? If not, that should help quite a
bit since the query engine can seek to the MAX value rather than scanning
for it.
Alternatively, you could keep a LastMeterUpdateTime table which holds only
an ID for each meter and the last time you updated it. But then it is up to
you to make sure that this table is updated.
Russell Fields
"BCM" <BCM@.none.com> wrote in message news:4104f321$1@.news.unc.edu...
> I've written some software that listens to meters on a specified port and
> writes XML files when any of those meters "speak." There are some 200 of
> them, and each is programmed to "speak" at 10 second intervals, so there's
a
> fair amount of traffic.
> Now what I've been asked to do is write a record to a database for each of
> these meters at ONE MINUTE intervals. At first I simply set up a one
minute
> timer and pointed my software to the directory where the XML files live
and
> said "write a record for every file in there." But, alas, occasionally one
> (or several) of the meters' files were being overwritten at that
particular
> time and thus weren't available.
> So, I changed things so that each time a meter speaks, I check the
database
> to see if it has been a minute or more since the last database update for
> that meter, and if it has I write a record out. Now this works fine, but
it
> uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
> Pentium 3 system is hijacked.
> So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
> functions) the likely culprit for this inefficiency? My colleague believes
> that it is, since (he says) calling MAX() forces a search through all the
> records contained in the table (potentially a boatload). Here's the query:
> SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "')
from
> " + MeterName
> Thanks in advance for any advice on improving ANY of this!
> .org
>

MAX() and DATEDIFF() question

I've written some software that listens to meters on a specified port and
writes XML files when any of those meters "speak." There are some 200 of
them, and each is programmed to "speak" at 10 second intervals, so there's a
fair amount of traffic.
Now what I've been asked to do is write a record to a database for each of
these meters at ONE MINUTE intervals. At first I simply set up a one minute
timer and pointed my software to the directory where the XML files live and
said "write a record for every file in there." But, alas, occasionally one
(or several) of the meters' files were being overwritten at that particular
time and thus weren't available.
So, I changed things so that each time a meter speaks, I check the database
to see if it has been a minute or more since the last database update for
that meter, and if it has I write a record out. Now this works fine, but it
uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
Pentium 3 system is hijacked.
So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
functions) the likely culprit for this inefficiency? My colleague believes
that it is, since (he says) calling MAX() forces a search through all the
records contained in the table (potentially a boatload). Here's the query:
SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "') from
" + MeterName
Thanks in advance for any advice on improving ANY of this!
..org
Try creating a non-clustered index on LastUpdateTime. This should optimize
your MAX query.
Hope this helps.
Dan Guzman
SQL Server MVP
"BCM" <BCM@.none.com> wrote in message news:4104f30d$1@.news.unc.edu...
> I've written some software that listens to meters on a specified port and
> writes XML files when any of those meters "speak." There are some 200 of
> them, and each is programmed to "speak" at 10 second intervals, so there's
a
> fair amount of traffic.
> Now what I've been asked to do is write a record to a database for each of
> these meters at ONE MINUTE intervals. At first I simply set up a one
minute
> timer and pointed my software to the directory where the XML files live
and
> said "write a record for every file in there." But, alas, occasionally one
> (or several) of the meters' files were being overwritten at that
particular
> time and thus weren't available.
> So, I changed things so that each time a meter speaks, I check the
database
> to see if it has been a minute or more since the last database update for
> that meter, and if it has I write a record out. Now this works fine, but
it
> uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
> Pentium 3 system is hijacked.
> So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
> functions) the likely culprit for this inefficiency? My colleague believes
> that it is, since (he says) calling MAX() forces a search through all the
> records contained in the table (potentially a boatload). Here's the query:
> SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "')
from
> " + MeterName
> Thanks in advance for any advice on improving ANY of this!
> .org
>

MAX() and DATEDIFF() question

I've written some software that listens to meters on a specified port and
writes XML files when any of those meters "speak." There are some 200 of
them, and each is programmed to "speak" at 10 second intervals, so there's a
fair amount of traffic.
Now what I've been asked to do is write a record to a database for each of
these meters at ONE MINUTE intervals. At first I simply set up a one minute
timer and pointed my software to the directory where the XML files live and
said "write a record for every file in there." But, alas, occasionally one
(or several) of the meters' files were being overwritten at that particular
time and thus weren't available.
So, I changed things so that each time a meter speaks, I check the database
to see if it has been a minute or more since the last database update for
that meter, and if it has I write a record out. Now this works fine, but it
uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
Pentium 3 system is hijacked.
So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
functions) the likely culprit for this inefficiency? My colleague believes
that it is, since (he says) calling MAX() forces a search through all the
records contained in the table (potentially a boatload). Here's the query:
SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "') from
" + MeterName
Thanks in advance for any advice on improving ANY of this!
..org
PLEASE DON'T MULTI-POST!!!
http://www.aspfaq.com/
(Reverse address to reply.)
"BCM" <BCM@.none.com> wrote in message news:4104f2c3$1@.news.unc.edu...
> I've written some software that listens to meters on a specified port and
> writes XML files when any of those meters "speak." There are some 200 of
> them, and each is programmed to "speak" at 10 second intervals, so there's
a
> fair amount of traffic.
> Now what I've been asked to do is write a record to a database for each of
> these meters at ONE MINUTE intervals. At first I simply set up a one
minute
> timer and pointed my software to the directory where the XML files live
and
> said "write a record for every file in there." But, alas, occasionally one
> (or several) of the meters' files were being overwritten at that
particular
> time and thus weren't available.
> So, I changed things so that each time a meter speaks, I check the
database
> to see if it has been a minute or more since the last database update for
> that meter, and if it has I write a record out. Now this works fine, but
it
> uses an enormous amount of CPU time: at times nearly 50% of a dual 2 gig
> Pentium 3 system is hijacked.
> So, my question is: is my SQL (which calls the DATEDIFF() and MAX()
> functions) the likely culprit for this inefficiency? My colleague believes
> that it is, since (he says) calling MAX() forces a search through all the
> records contained in the table (potentially a boatload). Here's the query:
> SELECT DATEDIFF(second, MAX([LastUpdateTime]), '" + [CurrentTime] + "')
from
> " + MeterName
> Thanks in advance for any advice on improving ANY of this!
> .org
>