Field Order

I am new to SQL so keep asking questions. My apologies if they are dumb.
Does it make any difference to the performance or otherwise of SQL if I
arrange the fields in one particular order in my table, and then input them
in another?
For example, my table is Field 1, Field 2, Field 3 etc., but I might want to
input Field 3 first, then Field 1, then Field 2.
Thanks
On Mon, 5 Apr 2004 11:55:48 +0100, "Keith" <@..> wrote:

>I am new to SQL so keep asking questions. My apologies if they are dumb.
There are no dumb questions. Not asking - that is dumb.

>Does it make any difference to the performance or otherwise of SQL if I
>arrange the fields in one particular order in my table, and then input them
>in another?
No, not at all.
(Sole exception - if you write an insert statement supplying data for
ALL columns in the same order as they are defined, you are allowed to
leave out the column list in the insert statement - but that is
definitely not recommended practice, since it will produce errors as
soon as someone or something changes the columns' order)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Field Order

I am new to SQL so keep asking questions. My apologies if they are dumb.
Does it make any difference to the performance or otherwise of SQL if I
arrange the fields in one particular order in my table, and then input them
in another?
For example, my table is Field 1, Field 2, Field 3 etc., but I might want to
input Field 3 first, then Field 1, then Field 2.
ThanksOn Mon, 5 Apr 2004 11:55:48 +0100, "Keith" <@..> wrote:

>I am new to SQL so keep asking questions. My apologies if they are dumb.
There are no dumb questions. Not asking - that is dumb.

>Does it make any difference to the performance or otherwise of SQL if I
>arrange the fields in one particular order in my table, and then input them
>in another?
No, not at all.
(Sole exception - if you write an insert statement supplying data for
ALL columns in the same order as they are defined, you are allowed to
leave out the column list in the insert statement - but that is
definitely not recommended practice, since it will produce errors as
soon as someone or something changes the columns' order)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:78g270t8umrbpp1in6r6ihk04ebichk8ng@.
4ax.com...
> On Mon, 5 Apr 2004 11:55:48 +0100, "Keith" <@..> wrote:
>
> There are no dumb questions. Not asking - that is dumb.
>
them
> No, not at all.
> (Sole exception - if you write an insert statement supplying data for
> ALL columns in the same order as they are defined, you are allowed to
> leave out the column list in the insert statement - but that is
> definitely not recommended practice, since it will produce errors as
> soon as someone or something changes the columns' order)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Favorite Monitoring (Performance) Tools?

I'm fairly new to the DBA thing.
I've been doing quite a bit of research on monitoring MSSQL performance.

What tools are you guys using?
PerfMon, Dbartisan, Spotlight, or what?

I'd like to find some free or affordable way to watch our Servers to check health/performance/etc.If you're using SQL 2005 (SP2), you can use a combination of SQL Health and History (aka SQL H2) and the new SQL Dashboard. Both require SQL Reporting services.

I'm using a set of vb scripts that use WMI queries to gather basic information: storage, network config, os config, installed apps, memory and cpu utilization. These are stored in a database. You can do a google on "wmi computer inventory vb script" and I think it will come up with a variety of examples.

Regards,

hmscott|||"whatsup" has a lot of robust automation for wmi if you're looking for a prepackaged solution. We've had reasonable luck with it so far.

FAST_FORWARD Cursor

Hi Experts,
(1) Will there be any considerable performance gain in
using the default cursor type (FORWARD_ONLY) over
FAST_FORWARD cursors. Cursors are used in my application
as READ ONLY source of data.
(2) Will the use of GOTO inside a SP degrade performance?
Kindly suggest.
TIA,
Hari
On Wed, 4 Aug 2004 05:02:01 -0700, Hari wrote:

>Hi Experts,
>(1) Will there be any considerable performance gain in
>using the default cursor type (FORWARD_ONLY) over
>FAST_FORWARD cursors. Cursors are used in my application
>as READ ONLY source of data.
>(2) Will the use of GOTO inside a SP degrade performance?
>Kindly suggest.
>TIA,
>Hari
Hi Hari,
(1) No. FORWARD_ONLY is implied by FAST_FORWARD. There will be a
performance gain in using a set-based solution over a cursor based
solution, if possible.
(2) No.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Could you please tell me what is the SET-based solution?
Thanks,
Hari

>--Original Message--
>On Wed, 4 Aug 2004 05:02:01 -0700, Hari wrote:
>
>Hi Hari,
>(1) No. FORWARD_ONLY is implied by FAST_FORWARD. There
will be a
>performance gain in using a set-based solution over a
cursor based
>solution, if possible.
>(2) No.
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>
|||On Wed, 4 Aug 2004 05:31:30 -0700, Hari wrote:

>Hugo,
>Could you please tell me what is the SET-based solution?
>Thanks,
>Hari
Hi Hari,
Without knowing what your current cursor-based solution actually does?
No.
But if you post DDL (create table statements, including all constraints
but excluding irrelevant columns), sample data (as INSERT statements),
expected output, your current cursor-based SQL and a description of the
business problem you're trying to solve, I could give it a shot.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

FAST_FORWARD Cursor

Hi Experts,
(1) Will there be any considerable performance gain in
using the default cursor type (FORWARD_ONLY) over
FAST_FORWARD cursors. Cursors are used in my application
as READ ONLY source of data.
(2) Will the use of GOTO inside a SP degrade performance?
Kindly suggest.
TIA,
HariOn Wed, 4 Aug 2004 05:02:01 -0700, Hari wrote:
>Hi Experts,
>(1) Will there be any considerable performance gain in
>using the default cursor type (FORWARD_ONLY) over
>FAST_FORWARD cursors. Cursors are used in my application
>as READ ONLY source of data.
>(2) Will the use of GOTO inside a SP degrade performance?
>Kindly suggest.
>TIA,
>Hari
Hi Hari,
(1) No. FORWARD_ONLY is implied by FAST_FORWARD. There will be a
performance gain in using a set-based solution over a cursor based
solution, if possible.
(2) No.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Could you please tell me what is the SET-based solution?
Thanks,
Hari
>--Original Message--
>On Wed, 4 Aug 2004 05:02:01 -0700, Hari wrote:
>>Hi Experts,
>>(1) Will there be any considerable performance gain in
>>using the default cursor type (FORWARD_ONLY) over
>>FAST_FORWARD cursors. Cursors are used in my application
>>as READ ONLY source of data.
>>(2) Will the use of GOTO inside a SP degrade performance?
>>Kindly suggest.
>>TIA,
>>Hari
>Hi Hari,
>(1) No. FORWARD_ONLY is implied by FAST_FORWARD. There
will be a
>performance gain in using a set-based solution over a
cursor based
>solution, if possible.
>(2) No.
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||On Wed, 4 Aug 2004 05:31:30 -0700, Hari wrote:
>Hugo,
>Could you please tell me what is the SET-based solution?
>Thanks,
>Hari
Hi Hari,
Without knowing what your current cursor-based solution actually does?
No.
But if you post DDL (create table statements, including all constraints
but excluding irrelevant columns), sample data (as INSERT statements),
expected output, your current cursor-based SQL and a description of the
business problem you're trying to solve, I could give it a shot.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

FAST_FORWARD Cursor

Hi Experts,
(1) Will there be any considerable performance gain in
using the default cursor type (FORWARD_ONLY) over
FAST_FORWARD cursors. Cursors are used in my application
as READ ONLY source of data.
(2) Will the use of GOTO inside a SP degrade performance?
Kindly suggest.
TIA,
HariOn Wed, 4 Aug 2004 05:02:01 -0700, Hari wrote:

>Hi Experts,
>(1) Will there be any considerable performance gain in
>using the default cursor type (FORWARD_ONLY) over
>FAST_FORWARD cursors. Cursors are used in my application
>as READ ONLY source of data.
>(2) Will the use of GOTO inside a SP degrade performance?
>Kindly suggest.
>TIA,
>Hari
Hi Hari,
(1) No. FORWARD_ONLY is implied by FAST_FORWARD. There will be a
performance gain in using a set-based solution over a cursor based
solution, if possible.
(2) No.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Could you please tell me what is the SET-based solution?
Thanks,
Hari

>--Original Message--
>On Wed, 4 Aug 2004 05:02:01 -0700, Hari wrote:
>
>Hi Hari,
>(1) No. FORWARD_ONLY is implied by FAST_FORWARD. There
will be a
>performance gain in using a set-based solution over a
cursor based
>solution, if possible.
>(2) No.
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||On Wed, 4 Aug 2004 05:31:30 -0700, Hari wrote:

>Hugo,
>Could you please tell me what is the SET-based solution?
>Thanks,
>Hari
Hi Hari,
Without knowing what your current cursor-based solution actually does?
No.
But if you post DDL (create table statements, including all constraints
but excluding irrelevant columns), sample data (as INSERT statements),
expected output, your current cursor-based SQL and a description of the
business problem you're trying to solve, I could give it a shot.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Fast way to get values from previous record

Hello,

I know that I've seen this question asked on here before, but I can't
find an answer that gives me the performance that I need.

I have a table that stores events for users:

CREATE TABLE Lead_Action_History (
lead_action_seq INT IDENTITY NOT NULL,
lead_action_date DATETIME NOT NULL,
lead_seq INT NULL,
operator_id VARCHAR(20) NOT NULL,
call_time INT NOT NULL,
CONSTRAINT PK_Lead_Action_History PRIMARY KEY (lead_action_seq) )
GO

The table has a foreign key to another table through the lead_seq
column:

CREATE TABLE Lead_Master (
lead_seq INT IDENTITY NOT NULL,
state CHAR(2) NOT NULL,
CONSTRAINT PK_Lead_Master PRIMARY KEY (lead_seq) )
GO

I need to write a query that will give me a sum of call_time broken
down by a column that is in the table joined through the lead_seq.
However, if the lead_seq for a row is NULL then I need to use the
lead_seq for the previous row (based on lead_action_date) for the same
operator.

This is what I came up with:

SELECT LM.state, SUM(call_time)
FROM Lead_Action_History LAH
INNER JOIN Lead_Master LM ON (LM.lead_seq = LAH.lead_seq)
OR (LAH.lead_seq IS NULL
AND LM.lead_seq = (SELECT TOP 1
LAH2.lead_seq
FROM
Lead_Action_History LAH2
WHERE
LAH2.operator_id = LAH.operator_id
AND LAH2.lead_seq
IS NOT NULL
ORDER BY
LAH2.lead_action_date DESC))
GROUP BY LM.state

The problem is that Lead_Action_History has millions of records and
any solution that I've found involves one or more subqueries on it
which kills performance. I am going to look at using a covering index
with the solution above, but I thought that someone here might have
another way of doing this.

I can't really change the structure, but I can play with the indexing.
I would still be curious though how other people model this type of
temporal data in a way that makes it easy to work with.

Thanks!
-Tom.tom_hummel@.hotmail.com (Thomas R. Hummel) wrote in message news:<a2c0eeb8.0401140902.723b743e@.posting.google.com>...
> Hello,
> I know that I've seen this question asked on here before, but I can't
> find an answer that gives me the performance that I need.

<snip
Could you post some sample data (INSERT statements) and the expected
result set? It's not totally clear (at least to me) what you require
when the Lead_Seq is NULL - your code suggests you want to count the
previous call_time twice?

Also, if you're having performance issues, you may want to post the
execution plan for your current query.

Simon|||Hi Tom,

Try this <disclaimer> completely untested </disclaimer>...

SELECT STATE
, SUM(CALL_SUM)
FROM (SELECT LM.state
, SUM(LAH.call_time) AS CALL_SUM
FROM Lead_Master LM
INNER JOIN
(SELECT ,L1.lead_action_seq
,L1.lead_action_date
,L2.lead_seq
,L1.operator_id
,L1.call_time
FROM Lead_Action_History L1
INNER JOIN
(SELECT operator_id
, MAX(lead_seq) as max_opr_ls
FROM lead_action_history
WHERE lead_seq IS NOT NULL
GROUP BY
operator_id) L2
ON L1.operator_id = L2.operator_id
AND L1.lead_seq IS NULL) LAH
ON LM.lead_seq = LAH.lead_seq
GROUP BY LM.state
UNION ALL
SELECT LM.state
, SUM(call_time) AS CALL_SUM
FROM Lead_Action_History LAH
INNER JOIN
Lead_Master LM
ON (LM.lead_seq = LAH.lead_seq)
GROUP BY LM.state) AS T
GROUP BY STATE;

Christian.|||>> I can't really change the structure, but I can play with the
indexing. I would still be curious though how other people model this
type of temporal data in a way that makes it easy to work with. <<

Then you're probably dead, because the problem is in the temporal
model. The idea of a time is that it is a contimuum and not a set of
discrete points. This goes back to Zeno, then to Einstein. You can
get some good SQL temporal programming tricks in "Developing
Time-Oriented Database Applications in SQL" by Rick Snodgrass.

The principle is that you need to model events as durations. So we
might re-write your table like this:

>I can't really change the structure, but I can play with the
indexing. I would still be curious though how other people model this
type of temporal data in a way that makes it easy to work with. <<

Then you're probably dead, because the problem is in the temporal
model. The idea of a time is that it is a contimuum and not a set of
discrete points. This goes back to Zeno, then to Einstein. You can get
some good SQL temporal programming tricks in "Developing Time-Oriented
Database Applications in SQL" by Rick Snodgrass.

The principle is that you need to model events as durations. So we
might re-write your table like this:

tom_hummel@.hotmail.com (Thomas R. Hummel) wrote in message news:<a2c0eeb8.0401140902.723b743e@.posting.google.com>...
Hello,

I know that I've seen this question asked on here before, but I can't
find an answer that gives me the performance that I need.

I have a table that stores events for users:

CREATE TABLE Lead_Action_History
(operator_id VARCHAR(20) NOT NULL
REFERENCES Personnel (operator_id)
ON UPDATE CASCADE,
action_start_date DATETIME NOT NULL,
action_finish_date DATETIME, null means ongoing
action_code CHAR(5) NOT NULL, -- needs constraints
CHECK (action_start_date <= action_finish_date)
state CHAR(2) DEFAULT '??' NOT NULL
CHECK (state IN ('Al', 'AK', )),
PRIMARY KEY (operator_id, action_start_date));

By definition, IDENTITY can never be a key. Your data integrity is
probably a mess because of that design flaw. Why does this table not
have an action code of some kind? The length of a call can be
computed from its start and finish times, so that column is redundant
and subject to errors imprecision.

The join to the LeadMAster table seems redundant since it is just a
list of two letter codes. Why not have a '??' token for the unknown
and avoid NULLs?

>> I need to write a query that will give me a sum of call_time broken
down by state <<

That is now very easy:

SELECT state, SUM(DATEDIFF(SS, action_start_date,
COALESCE(action_finish_date, CURRENT_TIMESTAMP))
AS total_time
FROM Lead_Action_History
GROUP BY state;

If you do most of the reports by dates, then use PRIMARY KEY
(action_start_date, operator_id) so the hidden index will be built in
that order. You can also use UNIQUE (action_start_date,
action_finish_date, operator_id) to get a covering index.|||And after proof reading...

SELECT STATE
, SUM(CALL_SUM)
FROM (SELECT LM.state
, SUM(LAH.call_time) AS CALL_SUM
FROM Lead_Master LM
INNER JOIN
(SELECT L1.lead_action_seq
,L1.lead_action_date
,max_opr_ls AS lead_seq
,L1.operator_id
,L1.call_time
FROM Lead_Action_History L1
INNER JOIN
(SELECT operator_id
, MAX(lead_seq) as max_opr_ls
FROM lead_action_history
WHERE lead_seq IS NOT NULL
GROUP BY
operator_id) L2
ON L1.operator_id = L2.operator_id
AND L1.lead_seq IS NULL) LAH
ON LM.lead_seq = LAH.lead_seq
GROUP BY LM.state
UNION ALL
SELECT LM.state
, SUM(call_time) AS CALL_SUM
FROM Lead_Action_History LAH
INNER JOIN
Lead_Master LM
ON (LM.lead_seq = LAH.lead_seq)
GROUP BY LM.state) AS T
GROUP BY STATE;

Christian.

Fast updates in SQL Server

Hi
In Oracle there is a concept that allows one to perform an update without
using the rollback logs so you can try to improve the performance of an
update. Does such functionality exist in SQL Server 2000? I cannot seem to
find anything on it in BOL
Thanks
NHi
In SQL Server every DML operations are logged. If you explain us a little
bit more about your requiremnts we will be able to help you .
How much data are you going to update?
Do you have any indexes defined on the table?
"Nesaar" <nesaarATprescientdotcodotza> wrote in message
news:%23GNz2I$HGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi
> In Oracle there is a concept that allows one to perform an update without
> using the rollback logs so you can try to improve the performance of an
> update. Does such functionality exist in SQL Server 2000? I cannot seem to
> find anything on it in BOL
> Thanks
> N
>
>|||Look at this thread there, for some operations there is a minimized
transaction protocol, like TRUNCATE. Other details were discussed in
here:
http://www.mcse.ms/archive89-2005-2-1441624.html
HTH, jens Suessmeyer.|||He has a temp table that has about 25 million rows in it. This is joined to
a transaction table with about 110 million rows on it. The transaction table
has a clustered index and a few other indexes as well.
I have just told the developer he should create an index on his temporary
table for the columns he is joining on as a first step to try and increase
performance.
Thanks
N
I've created a temp table to store transactions (+-25mill rows)
then it joins from there onto our transaction table (110 mill rows; lots of
indexes) to update.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uhRnAM$HGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi
> In SQL Server every DML operations are logged. If you explain us a little
> bit more about your requiremnts we will be able to help you .
> How much data are you going to update?
> Do you have any indexes defined on the table?
>
>
> "Nesaar" <nesaarATprescientdotcodotza> wrote in message
> news:%23GNz2I$HGHA.3936@.TK2MSFTNGP12.phx.gbl...
without
to
>

Fast Hardware - Slow SQL

Hey All,
We've got a new 8-way SQL 2000 Server that pretty much
follows all of the Microsoft guidelines for performance
(i.e. the right kind of RAID arrays for both the
transaction and data volumes, etc). It was
configured/setup with Microsoft's sales support folks.
Although SQL runs okay, it doesn't run as fast as we'd
expect. One thing we notice is that processor/memory
utilization is almost always nothing.
As an example, if we're creating a blank database and
specify large file sizes, it could literally take
something like 6 hours to create a "blank" 100gb database
with a 20gb log file (on separate volumes).
We're also running a data conversion process as we're
upgrading our Microsoft Navision SQL server database and
although it runs faster than our older environment, the
system is still running overall at less than 1% CPU
utilization and maybe using 2gb of the available 8gb of
RAM.
I know my disk subsystem is fine as SQL can backup a 120gb
database to disk in less than 25 minutes. I've also
watched counters (like avg. disk. length queue) and they
are in the expected ranges.
How can I "force" SQL to run faster and utilize all the
hardware that it has available?
It's hard to believe that on such an expensive piece of
hardware it could still take 6 hours to create a blank
database!!!
Any help would be appreciated...
-daveThat does seem like a long time. What OS are you running? What kind of
SAN? Are you sure the drivers are the latest for that SAN / HBA?
Andrew J. Kelly
SQL Server MVP
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:1171c01c3f4ec$553e47b0$a401280a@.phx
.gbl...
> Hey All,
> We've got a new 8-way SQL 2000 Server that pretty much
> follows all of the Microsoft guidelines for performance
> (i.e. the right kind of RAID arrays for both the
> transaction and data volumes, etc). It was
> configured/setup with Microsoft's sales support folks.
> Although SQL runs okay, it doesn't run as fast as we'd
> expect. One thing we notice is that processor/memory
> utilization is almost always nothing.
> As an example, if we're creating a blank database and
> specify large file sizes, it could literally take
> something like 6 hours to create a "blank" 100gb database
> with a 20gb log file (on separate volumes).
> We're also running a data conversion process as we're
> upgrading our Microsoft Navision SQL server database and
> although it runs faster than our older environment, the
> system is still running overall at less than 1% CPU
> utilization and maybe using 2gb of the available 8gb of
> RAM.
> I know my disk subsystem is fine as SQL can backup a 120gb
> database to disk in less than 25 minutes. I've also
> watched counters (like avg. disk. length queue) and they
> are in the expected ranges.
> How can I "force" SQL to run faster and utilize all the
> hardware that it has available?
> It's hard to believe that on such an expensive piece of
> hardware it could still take 6 hours to create a blank
> database!!!
> Any help would be appreciated...
> -dave

Fast Hardware - Slow SQL

Hey All,
We've got a new 8-way SQL 2000 Server that pretty much
follows all of the Microsoft guidelines for performance
(i.e. the right kind of RAID arrays for both the
transaction and data volumes, etc). It was
configured/setup with Microsoft's sales support folks.
Although SQL runs okay, it doesn't run as fast as we'd
expect. One thing we notice is that processor/memory
utilization is almost always nothing.
As an example, if we're creating a blank database and
specify large file sizes, it could literally take
something like 6 hours to create a "blank" 100gb database
with a 20gb log file (on separate volumes).
We're also running a data conversion process as we're
upgrading our Microsoft Navision SQL server database and
although it runs faster than our older environment, the
system is still running overall at less than 1% CPU
utilization and maybe using 2gb of the available 8gb of
RAM.
I know my disk subsystem is fine as SQL can backup a 120gb
database to disk in less than 25 minutes. I've also
watched counters (like avg. disk. length queue) and they
are in the expected ranges.
How can I "force" SQL to run faster and utilize all the
hardware that it has available?
It's hard to believe that on such an expensive piece of
hardware it could still take 6 hours to create a blank
database!!!
Any help would be appreciated...
-davewhat aspect of performance are you interested in?
the blank db init is seriously off.
you should be able to init at a rate of ~50MB/sec per
file, on a big disk array with the db partitioned into
filegroups or files, you should be at a combined rate of
>200MB/sec, so for 120GB, 40min with single file and 10min
with 4 files.
8-way scalability is a tricky item, some SQL operations
scale very well (hash & merge joins), other ops scale very
poorly (RPC, etc)
i will be have article detailing some of these issues on
www.sql-server-performance.com in a few of weeks
also, are you large row count data modifications?
(inserts, updates & delete) check the above web site later
this week, there will be some very interesting items on
this matter
>--Original Message--
>Hey All,
>We've got a new 8-way SQL 2000 Server that pretty much
>follows all of the Microsoft guidelines for performance
>(i.e. the right kind of RAID arrays for both the
>transaction and data volumes, etc). It was
>configured/setup with Microsoft's sales support folks.
>Although SQL runs okay, it doesn't run as fast as we'd
>expect. One thing we notice is that processor/memory
>utilization is almost always nothing.
>As an example, if we're creating a blank database and
>specify large file sizes, it could literally take
>something like 6 hours to create a "blank" 100gb database
>with a 20gb log file (on separate volumes).
>We're also running a data conversion process as we're
>upgrading our Microsoft Navision SQL server database and
>although it runs faster than our older environment, the
>system is still running overall at less than 1% CPU
>utilization and maybe using 2gb of the available 8gb of
>RAM.
>I know my disk subsystem is fine as SQL can backup a
120gb
>database to disk in less than 25 minutes. I've also
>watched counters (like avg. disk. length queue) and they
>are in the expected ranges.
>How can I "force" SQL to run faster and utilize all the
>hardware that it has available?
>It's hard to believe that on such an expensive piece of
>hardware it could still take 6 hours to create a blank
>database!!!
>Any help would be appreciated...
>-dave
>.
>|||That does seem like a long time. What OS are you running? What kind of
SAN? Are you sure the drivers are the latest for that SAN / HBA?
--
Andrew J. Kelly
SQL Server MVP
"Dave Rose" <anonymous@.discussions.microsoft.com> wrote in message
news:1171c01c3f4ec$553e47b0$a401280a@.phx.gbl...
> Hey All,
> We've got a new 8-way SQL 2000 Server that pretty much
> follows all of the Microsoft guidelines for performance
> (i.e. the right kind of RAID arrays for both the
> transaction and data volumes, etc). It was
> configured/setup with Microsoft's sales support folks.
> Although SQL runs okay, it doesn't run as fast as we'd
> expect. One thing we notice is that processor/memory
> utilization is almost always nothing.
> As an example, if we're creating a blank database and
> specify large file sizes, it could literally take
> something like 6 hours to create a "blank" 100gb database
> with a 20gb log file (on separate volumes).
> We're also running a data conversion process as we're
> upgrading our Microsoft Navision SQL server database and
> although it runs faster than our older environment, the
> system is still running overall at less than 1% CPU
> utilization and maybe using 2gb of the available 8gb of
> RAM.
> I know my disk subsystem is fine as SQL can backup a 120gb
> database to disk in less than 25 minutes. I've also
> watched counters (like avg. disk. length queue) and they
> are in the expected ranges.
> How can I "force" SQL to run faster and utilize all the
> hardware that it has available?
> It's hard to believe that on such an expensive piece of
> hardware it could still take 6 hours to create a blank
> database!!!
> Any help would be appreciated...
> -dave

Farms

I'm new to the architect world and I'm trying to solve a performance problem with a SQL Server cluster. But, in talking to some folks they were recommending that I write a design based on a SQL Server "farm". However, when I ask them for more information about what a "farm" is, they start to shuffle their feat!

So, what is a farm? Is it better than a cluster? Where can I find information on creating a SQL Server farm?

Many thanks!? I haven't heard the term "farm" used too much in conjunction with SQL Server installations. But there is some info in this doc (found via Google): http://www.dell.com/downloads/global/power/4q03-mis.pdf ... looks like a pretty good article, either way. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Laurence Hunter@.discussions.microsoft.com> wrote in message news:44b215d6-a25e-44ac-83d8-c18517f64798@.discussions.microsoft.com...I'm new to the architect world and I'm trying to solve a performance problem with a SQL Server cluster. But, in talking to some folks they were recommending that I write a design based on a SQL Server "farm". However, when I ask them for more information about what a "farm" is, they start to shuffle their feat!So, what is a farm? Is it better than a cluster? Where can I find information on creating a SQL Server farm?Many thanks!

Failure in performance dashboard

Hello,
i have a problem when i click on any blue region in the chart for the SQL CPU utilization on the performance dashboard where you normally would get further infos (reports for the last 15 minutes).
There comes no report but a side with "Error: Arithmetic overflow error converting expression to data type datetime."
This happens on two different servers with SQL Server 2005 Enterprise Editions, but all other drill through is functioning. What' s the problem, is it a problem in recent_cpu.rdl or performance_dashboard_main.rdl or in the called stored procedures. Please help, it is a really good tool, but i cannot use it.
Thanks for help
HeinrichSee this http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx?CommentPosted=true blog.|||Hi,
i know the link, i have posted the problem there also (see Heinrich).
But the changing of the setup.sql as described there doesn't help.
I get the failure as before. No SQL Server is functioning at the described point when i want to get detail information on pointing on the blue sql parts in the System CPU Utilization.
I am not a sql programmer, so i am not able to find the probleme, so i cannot use this Performance Dashboard.
Thanks for further help.
Heinrich