Fluctuate in DB Performance Statistics

Dear All,
I have noticed that the DB performance statistics is not
stable as it will rise at certain time dramatically.
I have checked that there isn't any job at that time.
E.g. Server\memory/pages , Current disk queue length,
processor time
Can any one suggest on any cause this?
Thanks.most database applications today consists of a mix of
simple transactions and complex queries that process a lot
of data. this includes reports or other sophisticated
features.
if you have for example 100 concurrent users running the
simple transactions, you might see a stable load on the
server,
however, anytime someone hits one of the complex queries,
you might see cpu spike.
this is why many people recommend separating OLTP and DSS
applications. there is no such thing as a machine powerful
enough to handle both functions on one server.
the OLTP server depends on fast response times, meaning it
should operate at low cpu loading.
the DSS app is supposed to run with cpu pegged, if not,
then you should be running more DSS queries to it, you
bought the hardware, you may as well use it.
>--Original Message--
>Dear All,
>I have noticed that the DB performance statistics is not
>stable as it will rise at certain time dramatically.
>I have checked that there isn't any job at that time.
>E.g. Server\memory/pages , Current disk queue length,
>processor time
>Can any one suggest on any cause this?
>Thanks.
>.
>|||Run profiler at that time to see if it is any TSQL commands causing this. Also, it can be the
checkpoint process.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jackty" <anonymous@.discussions.microsoft.com> wrote in message
news:022d01c3ad86$140683a0$a601280a@.phx.gbl...
> Dear All,
> I have noticed that the DB performance statistics is not
> stable as it will rise at certain time dramatically.
> I have checked that there isn't any job at that time.
> E.g. Server\memory/pages , Current disk queue length,
> processor time
> Can any one suggest on any cause this?
> Thanks.

Floating point fun

I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
migration. I've read certain floating-point values can't be represented
accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001? Converting the column to DECIMAL is probably not an
option.
Thanks
Damien
CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
FLOAT(2) )
-- Try and insert the value 4.1 into the float table
INSERT INTO #float_test ( ft_id, rate1 )
SELECT 1, 4.1 UNION
SELECT 2, 4 + .1 UNION
SELECT 3, '4.10' UNION
SELECT 4, 4.11 UNION
SELECT 5, 4.95 UNION
SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
SELECT 7, .1
GO
UPDATE #float_test
SET rate2 = rate1
SELECT * FROM #float_test
DROP TABLE #float_testHi
Read http://www.aspfaq.com/show.asp?id=2477
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:C4AF11D4-0AD6-4EE3-8453-FFF203829E25@.microsoft.com...
> I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
> migration. I've read certain floating-point values can't be represented
> accurately, however, .1 doesn't look like one of them as the FLOAT(2)
> field
> copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1
> round
> to .10000000000000001? Converting the column to DECIMAL is probably not
> an
> option.
> Thanks
> Damien
> CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
> FLOAT(2) )
> -- Try and insert the value 4.1 into the float table
> INSERT INTO #float_test ( ft_id, rate1 )
> SELECT 1, 4.1 UNION
> SELECT 2, 4 + .1 UNION
> SELECT 3, '4.10' UNION
> SELECT 4, 4.11 UNION
> SELECT 5, 4.95 UNION
> SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
> SELECT 7, .1
> GO
> UPDATE #float_test
> SET rate2 = rate1
> SELECT * FROM #float_test
> DROP TABLE #float_test
>|||On Tue, 23 Aug 2005 01:43:07 -0700, Damien
<Damien@.discussions.microsoft.com> wrote:

>I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
>migration. I've read certain floating-point values can't be represented
>accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
>copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
>to .10000000000000001? Converting the column to DECIMAL is probably not an
>option.
It's not clear to me what you expect to happen. Floating-point values
*are* precisely represented by floating-point values. (cough)
OTOH, certain fractional values are not in the domain of certain
floating-point data types. In those cases, software generally picks
the closest value available. (The result is "error of approximation",
not a rounding error.)
You seem to think that every number that ends in '.1' should have the
same behavior. That's simply not true of floating-point data types.
It might help to think of it this way. Neighboring, distinct values in
exact data types are the same distance apart on a number line. That
is, each value in a SQL INTEGER data type is plus or minus 1 from its
neighbor.
But neighboring, distinct values in a floating-point data type are not
the same distance apart on a number line. The closer you get to zero
(from either direction), the more closely spaced the neighboring
distinct values are.
Mike Sherrill|||> Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001?
because under the hood floats are stored as binaries. So, binary
numbers are represented accurately, up to some accuracy, of course.
Decimals are rounded to binaries. When you convert binaries back to
decimals, expect some mismatch|||On 24 Aug 2005 14:36:10 -0700, ford_desperado@.yahoo.com wrote:
[snip]
>Decimals are rounded to binaries.
Error of approximation, which seems to be what you're stumbling
toward, doesn't mean "fixed point numbers are rounded to binary". See
Knuth, vol 2.

Fireshose mode

About 2 times a week our SQL Server 2000 system will go into "firehose
mode" and we aren't able to run certain stored procedures. It seems
the ones that are affected are any procedures accessing linked
servers. The only way we can solve the problem is by restarting the
sql service on the server. This is starting to become a drag on our
business because all data processing stops when restart SQL. Has
anybody else encountered this issue with your systems?
Thanks,
JK
PS - We are not using Enterprise Manager so that is not the cause of
the problem. We are suspicous of the SQL Object Browser in Query
Analyzer and wonder if that uses the same "firehose" cursor as EM.Hello JK,
These links will help you to work on your problem.
PRB: SQL Enterprise Manager Returns "Cannot Start
Transaction While in Firehose Mode" Error
http://www.support.microsoft.com/?id=237398
FIX: Cursor Overhead Higher on SQL Server 7.0 for Small
Result Sets
http://support.microsoft.com/support/kb/articles/Q197/8/00.
ASP
Good Luck!
-SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>About 2 times a week our SQL Server 2000 system will go
into "firehose
>mode" and we aren't able to run certain stored
procedures. It seems
>the ones that are affected are any procedures accessing
linked
>servers. The only way we can solve the problem is by
restarting the
>sql service on the server. This is starting to become a
drag on our
>business because all data processing stops when restart
SQL. Has
>anybody else encountered this issue with your systems?
>Thanks,
>JK
>PS - We are not using Enterprise Manager so that is not
the cause of
>the problem. We are suspicous of the SQL Object Browser
in Query
>Analyzer and wonder if that uses the same "firehose"
cursor as EM.
>.
>|||One of those articles references Enterprise Manager, which we are not
using. The other one talks about SQL Server 7.0, which we are not
using.
Thanks for your help.
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message news:<074101c3ad67$6182baa0$a101280a@.phx.gbl>...
> Hello JK,
> These links will help you to work on your problem.
> PRB: SQL Enterprise Manager Returns "Cannot Start
> Transaction While in Firehose Mode" Error
> http://www.support.microsoft.com/?id=237398
> FIX: Cursor Overhead Higher on SQL Server 7.0 for Small
> Result Sets
> http://support.microsoft.com/support/kb/articles/Q197/8/00.
> ASP
> Good Luck!
> -SQLVarad (MCDBA-1999,MCSE-1999)
> >--Original Message--
> >About 2 times a week our SQL Server 2000 system will go
> into "firehose
> >mode" and we aren't able to run certain stored
> procedures. It seems
> >the ones that are affected are any procedures accessing
> linked
> >servers. The only way we can solve the problem is by
> restarting the
> >sql service on the server. This is starting to become a
> drag on our
> >business because all data processing stops when restart
> SQL. Has
> >anybody else encountered this issue with your systems?
> >
> >Thanks,
> >
> >JK
> >
> >PS - We are not using Enterprise Manager so that is not
> the cause of
> >the problem. We are suspicous of the SQL Object Browser
> in Query
> >Analyzer and wonder if that uses the same "firehose"
> cursor as EM.
> >.
> >