difference between SQL standard Edition and Enterprise Edition

Hi, there,

We are running SQL 2000 & SP4 with our ASP.NET application, now we plan to upgrade to Enterprise Edition due to the huge diffirence in price. Can any one of u give an brief introduction of the difference between these two, and what is the advantages of enterprise edition?

Any suggestion will greately appreciated.

Shermaine

There's a good comparison of the various versions of SQL Server here:-

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

shermaine wrote:

now we plan to upgrade to Enterprise Edition due to the huge diffirence in price.

That seems logical.

Difference between running MS SQL Server 2000 on a desktop PC and a Server

Hi Everyone,

Apparently, I was being asked on a question, "Why don't we procure a
desktop PC to run MS SQL Server 2000 rather than a buying a server?".
From a Management point-of-view, buying a desktop PC is much cheaper
than a server. However, I just wanted to understand that is it a
viable solution given the database size is something around 200 GB?
Equipping with more memory, more storage and a more powerful CPU on a
desktop PC could really taking up the role to support the DBMS?

Besides this "sensitive" costing concerns, what will be others
difference in running the SQL Server 2000 on the two different
hardware architecture? For example, IO rate, reliability, RAID-1
support, performance, etc.

(Note: The operating system is Microsoft Windows 2000 Enterprise
Edition)

Regards,
Ambrose"Ambrose" <achung@.hec.com.hk> wrote in message
news:6ec03d10.0410182005.618e1377@.posting.google.c om...
> Hi Everyone,
> Apparently, I was being asked on a question, "Why don't we procure a
> desktop PC to run MS SQL Server 2000 rather than a buying a server?".
> From a Management point-of-view, buying a desktop PC is much cheaper
> than a server. However, I just wanted to understand that is it a
> viable solution given the database size is something around 200 GB?
> Equipping with more memory, more storage and a more powerful CPU on a
> desktop PC could really taking up the role to support the DBMS?

Well, there's a lot of questions here.

How valuable is the data? I mean a 200GB SATA drive is cheap these days.
But if it fails, you're hosed.

I've run some small non-critical databases on workstations. Heck, if it was
non-critical, I might run a large (i.e. 200GB one) on a work station.

However, if it's critical, then I'm starting to look at things like ECC
memory, RAID, etc.

So, sure, the desktop is cheaper... but what if you lose your data? Or are
down for 10 hours restoring it from backup?

Also, if it's high volume, I'm looknig at RAID, multiple channels of RAID,
multiple NICs, multiple XEON CPUs. etc.

> Besides this "sensitive" costing concerns, what will be others
> difference in running the SQL Server 2000 on the two different
> hardware architecture? For example, IO rate, reliability, RAID-1
> support, performance, . etc.

MS Press has a book (don't recall the title) on this.

> (Note: The operating system is Microsoft Windows 2000 Enterprise
> Edition)
> Regards,
> Ambrosesql

Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference Between Physical IOs and Read-Ahead

Hello ...

I was running a table scan query on a 650MB table w/ 1 million rows. With statistics IO and time I noticed that I was able to get the following numbers on SQL Server 2005 (note this was the Sept CTP):

Logical IOs: 76,931

Physical IOs: 0

Read-Ahead: 58,321

CPU Time: 1212 ms

Clock Time: 42946 ms

So, in looking at the above I'm not seeing any physical IOs, but a lot of "read-ahead" IOs. If I understand it from the docs, a read-ahead essentially moves a data page into the cache. I understand this may mean getting a larger IO block size. Does this mean that I'm in fact doing physical IOs? This is a little confusing on the difference.

I noticed that when I ran a similar table scan query on a smaller table (say 266MB with 1 million rows) I had zero physical IOs and zero read-ahead hits. The query was significantly faster. The clock time was about 500 ms.

There were no indexes on either table.

Any advise here? Seems a little odd

Thanks!

DB

Read-ahead just means that the query processor asks for pages from a table to be pre-fetched into the cache. In this case, QP doesn't wait for the requests to complete - it is done asynchronously. The pages in the cache might be used later during the processing of the query at which point it may or may not be in the cache due to other activities on the server. So when the request for the page happens again it might incur in a physical IO (page was evicted from cache) or login IO (page is already in cache). It is possible that you see non-zero values for all the 3 counters. Physical IO is just that - fetching page from disk to memory. Logical IO is a page that is already in cache or memory (this can exceed the actual number of pages for a table if the same page is requested multiple times during query processing). You should think of read-ahead as an optimization mechanism that allows QP to request for pre-fetching pages that will be potentially used in the query.|||

Hi Umachandar ...

Sorry for the delay in responding to this...

From your message above and my real-life example above, since I have no physical IOs on the query using read aheads ... it sounds like the number of read ahead IOs is included in the total number of logical IOs. I think what you're saying is that in this situation w/ zero physical IOs, these are re-reads from cache of pages that had been previously cached ... is this the right way to look at it?

The example with the larger table using read aheads and the example with the smaller table and no read aheads were both just table scans. There were no indexes on either table, no where clause, and no physical IOs. I can see where scanning the larger table would take a bit longer than scanning the smaller table, but the clock time difference between the two samples seems really extreme. The larger table did require more CPU time (perhaps processing the read aheads ... ummm maybe re-reading previously cached pages?) but the overall picture doesn't make sense.

Are there cases where read aheads can perform poorly, and if so would one shut this feature off in terms of performance and tuning?

Thanks so much!

Doug

|||Yes, this is correct. The read-aheads are just requests to fetch pages from disk to cache and if they are already in memory then there is no additional work required. You should watch for cases where there was lot of read-ahead requests but the actual number of pages that were processed for the query is less in number. The difference in the CPU time might be due to the size of the larger table (i.e., more pages to read and process).

Difference between MSDE 7 and SQL 7

Is there a registry key that will tell me if am running MSDE 7 or MS
SQL 7?
Thanks
I guess MSDE 7 is just a typo of yours, but there does not have to be
only the one OR the other, there can also be the two of them installed
on the server, so it would be evenbetter for you to enumerate the
instances on the machine using DMO or SMO.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||This is the situation, I have a wise installation that needs to check
if the target pc is running either MSDE or full SQL. In our case the
target pc will only be running one or the other. Thats why I was
wondering if there is a registry key that will tell me which one is
running.
|||Mapping MSDE versions to their corresponding versions of SQL Server:
MSDE 1.0 used SQL Server 7.0 technology.
MSDE 2000 (also known as SQL Server 2000 Desktop Engine) used SQL Server
2000 technology.
SQL Server 2005 Express Edition is the MSDE replacement for SQL Server 2005.
You could only run one instance of either MSDE 1.0 or SQL Server 7.0 on a
computer, support for multiple instances of the Database Engine on one
computer was not introduced until SQL Server 2000.
I don't know of a registry key, but if you issue a SELECT @.@.VERSION
statement it will report the version of SQL Server 7.0 (MSDE or an edition
such as Standard or Enterprise).
Alan Brewer [MSFT]
SQL Server Documentation Team
Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Alan is right, but there are also .NET Framework factory classes that can
report all instances (2000 or 2005) on a network and on any specific
instance, the version etc. There is an example of this on my book's DVD.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Alan Brewer [MSFT]" <alanbr@.microsoft.com> wrote in message
news:OCdydtkRHHA.4060@.TK2MSFTNGP03.phx.gbl...
> Mapping MSDE versions to their corresponding versions of SQL Server:
> MSDE 1.0 used SQL Server 7.0 technology.
> MSDE 2000 (also known as SQL Server 2000 Desktop Engine) used SQL Server
> 2000 technology.
> SQL Server 2005 Express Edition is the MSDE replacement for SQL Server
> 2005.
> You could only run one instance of either MSDE 1.0 or SQL Server 7.0 on a
> computer, support for multiple instances of the Database Engine on one
> computer was not introduced until SQL Server 2000.
> I don't know of a registry key, but if you issue a SELECT @.@.VERSION
> statement it will report the version of SQL Server 7.0 (MSDE or an edition
> such as Standard or Enterprise).
> --
> Alan Brewer [MSFT]
> SQL Server Documentation Team
> Download the latest Books Online update:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Difference about running and runnable

Hi,
Anybody know the difference about running and runnable when I execute sp_who
?
Thanks in advance,
Vitor
From what I know the status column of sysprocesses table can have one of the
following values:
Status Meaning
------
Background SPID is performing a background task.
Sleeping SPID is not currently executing. This usually indicates
that the SPID is awaiting a command from the application.
Runnable SPID is currently executing.
Dormant Same as Sleeping, except Dormant also indicates that the
SPID has been reset after completing an RPC event. The reset cleans up
resources used during the RPC event. This is a normal state and the SPID is
available and waiting to execute further commands.
Rollback The SPID is in rollback of a transaction.
Defwakeup Indicates that a SPID is waiting on a resource that is in
the process of being freed. The waitresource field should indicate the
resource in question.
Spinloop Process is waiting while attempting to acquire a
spinlock used for concurrency control on SMP systems.
I found a description for "Running" status in a Sybase Manual:
"running:Actively running on one of the server engines" and in the same
manual "runnable: In the queue of runnable processes".
"Killing processes"
http://manuals.sybase.com/onlinebook...kTextView/5162
HTH,
Cristian Lefter, SQL Server MVP
"Vitor Mauricio de N. Silva" <vitor_mauricio@.terra.com.br> wrote in message
news:u3A6edKXFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anybody know the difference about running and runnable when I execute
> sp_who ?
> Thanks in advance,
> Vitor
>

Difference about running and runnable

Hi,
Anybody know the difference about running and runnable when I execute sp_who
?
Thanks in advance,
VitorFrom what I know the status column of sysprocesses table can have one of the
following values:
Status Meaning
----
----
Background SPID is performing a background task.
Sleeping SPID is not currently executing. This usually indicates
that the SPID is awaiting a command from the application.
Runnable SPID is currently executing.
Dormant Same as Sleeping, except Dormant also indicates that the
SPID has been reset after completing an RPC event. The reset cleans up
resources used during the RPC event. This is a normal state and the SPID is
available and waiting to execute further commands.
Rollback The SPID is in rollback of a transaction.
Defwakeup Indicates that a SPID is waiting on a resource that is in
the process of being freed. The waitresource field should indicate the
resource in question.
Spinloop Process is waiting while attempting to acquire a
spinlock used for concurrency control on SMP systems.
I found a description for "Running" status in a Sybase Manual:
"running:Actively running on one of the server engines" and in the same
manual "runnable: In the queue of runnable processes".
"Killing processes"
/5162" target="_blank">http://manuals.sybase.com/onlineboo...ew
/5162
HTH,
Cristian Lefter, SQL Server MVP
"Vitor Mauricio de N. Silva" <vitor_mauricio@.terra.com.br> wrote in message
news:u3A6edKXFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anybody know the difference about running and runnable when I execute
> sp_who ?
> Thanks in advance,
> Vitor
>sql

Difference about running and runnable

Hi,
Anybody know the difference about running and runnable when I execute sp_who
?
Thanks in advance,
VitorFrom what I know the status column of sysprocesses table can have one of the
following values:
Status Meaning
------
Background SPID is performing a background task.
Sleeping SPID is not currently executing. This usually indicates
that the SPID is awaiting a command from the application.
Runnable SPID is currently executing.
Dormant Same as Sleeping, except Dormant also indicates that the
SPID has been reset after completing an RPC event. The reset cleans up
resources used during the RPC event. This is a normal state and the SPID is
available and waiting to execute further commands.
Rollback The SPID is in rollback of a transaction.
Defwakeup Indicates that a SPID is waiting on a resource that is in
the process of being freed. The waitresource field should indicate the
resource in question.
Spinloop Process is waiting while attempting to acquire a
spinlock used for concurrency control on SMP systems.
I found a description for "Running" status in a Sybase Manual:
"running:Actively running on one of the server engines" and in the same
manual "runnable: In the queue of runnable processes".
"Killing processes"
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sag/@.Generic__BookTextView/5162
HTH,
Cristian Lefter, SQL Server MVP
"Vitor Mauricio de N. Silva" <vitor_mauricio@.terra.com.br> wrote in message
news:u3A6edKXFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Anybody know the difference about running and runnable when I execute
> sp_who ?
> Thanks in advance,
> Vitor
>

Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping b
y
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:[vbcol=seagreen]
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in
a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impac
t
> than trying to take seconds (or even minutes) off of long running procedur
es
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...

Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> > Hi everyone,
> >
> > We are running a SQL Server 2000 instance which is getting hit with
> > hundreds of queries a minute. The vast majority of these are very
> > short-lived, low overhead queries. Some of them are highly resouce
> > intensive (< 1%). My problem is that lately, our SQL Server CPU
> > utilization has climbed way up and I can't begin to figure out what is
> > causing it... the high volume of quick queries, or the low volume of
> > slow queries.
> >
> > I'm fairly skilled at SQL Server performance tuning, but this has got
> > me stumped. How can I isolate the category of queries are causing all
> > of the activity? I've tried using profiler, but it displays each of the
> > queries individually ... there's no way to group activity together by
> > meaningful categories (that I'm aware of).
> >
> > Can anyone point me in the right direction?
> >
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping by
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>> "Rich" <rich@.adgooroo.com> wrote in message
>> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
>> > Hi everyone,
>> >
>> > We are running a SQL Server 2000 instance which is getting hit with
>> > hundreds of queries a minute. The vast majority of these are very
>> > short-lived, low overhead queries. Some of them are highly resouce
>> > intensive (< 1%). My problem is that lately, our SQL Server CPU
>> > utilization has climbed way up and I can't begin to figure out what is
>> > causing it... the high volume of quick queries, or the low volume of
>> > slow queries.
>> >
>> > I'm fairly skilled at SQL Server performance tuning, but this has got
>> > me stumped. How can I isolate the category of queries are causing all
>> > of the activity? I've tried using profiler, but it displays each of the
>> > queries individually ... there's no way to group activity together by
>> > meaningful categories (that I'm aware of).
>> >
>> > Can anyone point me in the right direction?
>> >
>> The basic technique here is to use profiler or a server trace to gather
>> execution statistics for individual queries over a window of time. Then
>> load the results into a table and analyze them. For instance, grouping
>> by
>> query text (or truncated or scrubbed text) and then summing the IO and
>> CPU
>> statistics. This will isolate the queries driving the CPU use.
>> David
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impact
> than trying to take seconds (or even minutes) off of long running procedures
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> > Hi David,
> >
> > Great suggestion! I didn't know you could export this information from
> > Profiler into another format.
> >
> > One more question. Can you suggest which performance monitors I should
> > select in profiler to get just the query CPU and IO information for
> > completed queries? Or better yet, do you know of any online tutorials
> > with this information?
> >
> > Thanks!
> > -Rich
> >
> >
> > David Browne wrote:
> >> "Rich" <rich@.adgooroo.com> wrote in message
> >> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> >> > Hi everyone,
> >> >
> >> > We are running a SQL Server 2000 instance which is getting hit with
> >> > hundreds of queries a minute. The vast majority of these are very
> >> > short-lived, low overhead queries. Some of them are highly resouce
> >> > intensive (< 1%). My problem is that lately, our SQL Server CPU
> >> > utilization has climbed way up and I can't begin to figure out what is
> >> > causing it... the high volume of quick queries, or the low volume of
> >> > slow queries.
> >> >
> >> > I'm fairly skilled at SQL Server performance tuning, but this has got
> >> > me stumped. How can I isolate the category of queries are causing all
> >> > of the activity? I've tried using profiler, but it displays each of the
> >> > queries individually ... there's no way to group activity together by
> >> > meaningful categories (that I'm aware of).
> >> >
> >> > Can anyone point me in the right direction?
> >> >
> >>
> >> The basic technique here is to use profiler or a server trace to gather
> >> execution statistics for individual queries over a window of time. Then
> >> load the results into a table and analyze them. For instance, grouping
> >> by
> >> query text (or truncated or scrubbed text) and then summing the IO and
> >> CPU
> >> statistics. This will isolate the queries driving the CPU use.
> >>
> >> David
> >

Diagnosing Memory Problems?

I am consistantly getting Physical Memory Usage High errors. My Buffer Cache
is running at 99% and the Server Memory is at 87%. Any thoughts on what's c
ausing this?I am consistantly getting Physical Memory Usage High errors. My Buffer Cache
is running at 99% and the Server Memory is at 87%. Any thoughts on what's c
ausing this?

DHCP and SQL

Hello!!
I was wondering whether this was possible.
Running a DHCP on a 2003 Server platform I want to link the DHCP database to
a SQL database. The DHCP database is in windows/system32/dhcp I believe.
The idea is to have a web interface which access the SQL database with some
PHP programming. The SQL database gets the data from the DHCP server and all
the changes made, with the webinterface, on to the SQL database is updated
on the DHCP.
Has anyone tried this before and is it possible?
Thanks,
Fernando.
This is really a windows network programming question. You might have
better luck in a different newsgroup.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Fernando" <fernando@.microsoft.com> wrote in message
news:O7PatKe6FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Hello!!
> I was wondering whether this was possible.
> Running a DHCP on a 2003 Server platform I want to link the DHCP database
> to
> a SQL database. The DHCP database is in windows/system32/dhcp I believe.
> The idea is to have a web interface which access the SQL database with
> some
> PHP programming. The SQL database gets the data from the DHCP server and
> all
> the changes made, with the webinterface, on to the SQL database is updated
> on the DHCP.
> Has anyone tried this before and is it possible?
> Thanks,
> Fernando.
>
>
|||Thanks Jeff,
I will do that.
Regards,
Fernando.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OPPSw%23g6FHA.564@.TK2MSFTNGP10.phx.gbl...
> This is really a windows network programming question. You might have
> better luck in a different newsgroup.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Senior Consultant
> www.rapidigm.com
> "Fernando" <fernando@.microsoft.com> wrote in message
> news:O7PatKe6FHA.3136@.TK2MSFTNGP09.phx.gbl...
>

DHCP and SQL

Hello!!
I was wondering whether this was possible.
Running a DHCP on a 2003 Server platform I want to link the DHCP database to
a SQL database. The DHCP database is in windows/system32/dhcp I believe.
The idea is to have a web interface which access the SQL database with some
PHP programming. The SQL database gets the data from the DHCP server and all
the changes made, with the webinterface, on to the SQL database is updated
on the DHCP.
Has anyone tried this before and is it possible?
Thanks,
Fernando.This is really a windows network programming question. You might have
better luck in a different newsgroup.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Fernando" <fernando@.microsoft.com> wrote in message
news:O7PatKe6FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Hello!!
> I was wondering whether this was possible.
> Running a DHCP on a 2003 Server platform I want to link the DHCP database
> to
> a SQL database. The DHCP database is in windows/system32/dhcp I believe.
> The idea is to have a web interface which access the SQL database with
> some
> php programming. The SQL database gets the data from the DHCP server and
> all
> the changes made, with the webinterface, on to the SQL database is updated
> on the DHCP.
> Has anyone tried this before and is it possible?
> Thanks,
> Fernando.
>
>|||Thanks Jeff,
I will do that.
Regards,
Fernando.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OPPSw%23g6FHA.564@.TK2MSFTNGP10.phx.gbl...
> This is really a windows network programming question. You might have
> better luck in a different newsgroup.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Senior Consultant
> www.rapidigm.com
> "Fernando" <fernando@.microsoft.com> wrote in message
> news:O7PatKe6FHA.3136@.TK2MSFTNGP09.phx.gbl...
>

DHCP and SQL

Hello!!
I was wondering whether this was possible.
Running a DHCP on a 2003 Server platform I want to link the DHCP database to
a SQL database. The DHCP database is in windows/system32/dhcp I believe.
The idea is to have a web interface which access the SQL database with some
PHP programming. The SQL database gets the data from the DHCP server and all
the changes made, with the webinterface, on to the SQL database is updated
on the DHCP.
Has anyone tried this before and is it possible?
Thanks,
Fernando.This is really a windows network programming question. You might have
better luck in a different newsgroup.
--
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Fernando" <fernando@.microsoft.com> wrote in message
news:O7PatKe6FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Hello!!
> I was wondering whether this was possible.
> Running a DHCP on a 2003 Server platform I want to link the DHCP database
> to
> a SQL database. The DHCP database is in windows/system32/dhcp I believe.
> The idea is to have a web interface which access the SQL database with
> some
> PHP programming. The SQL database gets the data from the DHCP server and
> all
> the changes made, with the webinterface, on to the SQL database is updated
> on the DHCP.
> Has anyone tried this before and is it possible?
> Thanks,
> Fernando.
>
>|||Thanks Jeff,
I will do that.
Regards,
Fernando.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OPPSw%23g6FHA.564@.TK2MSFTNGP10.phx.gbl...
> This is really a windows network programming question. You might have
> better luck in a different newsgroup.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Senior Consultant
> www.rapidigm.com
> "Fernando" <fernando@.microsoft.com> wrote in message
> news:O7PatKe6FHA.3136@.TK2MSFTNGP09.phx.gbl...
>> Hello!!
>> I was wondering whether this was possible.
>> Running a DHCP on a 2003 Server platform I want to link the DHCP database
>> to
>> a SQL database. The DHCP database is in windows/system32/dhcp I believe.
>> The idea is to have a web interface which access the SQL database with
>> some
>> PHP programming. The SQL database gets the data from the DHCP server and
>> all
>> the changes made, with the webinterface, on to the SQL database is
>> updated
>> on the DHCP.
>> Has anyone tried this before and is it possible?
>> Thanks,
>> Fernando.
>>
>

Development Problems

Hi
Hope someone can help. I have a few legacy databases running on SQL Server
(developed before my time) which have a # in the name of the database.
I have been trying to write some reports that run off these databases
however whenever I attempt to preview or deploy the report I get an error
"Unable to access '/c'". I have narrowed it down to being something to do
with the # sign through moving things onto a test rig and removing the #
from the DB names and all is fine, however this is not really an option on
the live system.
My question is, does anyone know a way that I can get round this problem in
Reporting Services so I dont have to go through the process of modifying
about 20 different systems?
Thanks in advance.You could try placing the dbname within [], as in [pubs]
The brackets should take away any special meaning.
"Steven Clark" <sjc@.nospam.uk-support.net> wrote in message
news:hOOdnWLBBO7bvXHcRVnyrQ@.eclipse.net.uk...
> Hi
> Hope someone can help. I have a few legacy databases running on SQL
> Server (developed before my time) which have a # in the name of the
> database.
> I have been trying to write some reports that run off these databases
> however whenever I attempt to preview or deploy the report I get an error
> "Unable to access '/c'". I have narrowed it down to being something to do
> with the # sign through moving things onto a test rig and removing the #
> from the DB names and all is fine, however this is not really an option on
> the live system.
> My question is, does anyone know a way that I can get round this problem
> in Reporting Services so I dont have to go through the process of
> modifying about 20 different systems?
> Thanks in advance.
>

dev and production server - cache issue

i have two servers, both running SSAS, my production server recently became really slow performing lookups, just in one cube. I cant seem to find the root cause. I took a backup of the production and loaded it exactly as production but on a different server. I can query the cube in development fast. Looking at profiler, it seems the one cube with performance issues isnt looking up things in cache at first, it takes one try, then queries are fast. I have read some places online about cache warming, which is a great idea, but it doesnt make sense that the same cube in my dev environment is working just fine without cache warming. Anyone have any insights?

Are there any security roles applied in production that may not be applied in dev?

There are a couple of things around security roles that could result is slightly slower performance. If you are running as a server admin you sometimes get better performance as security roles are not applied at all. And the formula engine caches cannot be shared over security roles. So if you had mulitple different secuirty roles being used in production the amount of cache re-use would be reduced.

|||no, the security roles are the same. After investigating more, I found the Aggregation Manager util that comes with the samples and ran that against dev and prod, it looks like prod has 0 for aggregation counts where dev has counts..I wonder how that can be though, why one wouldn't be working and the other would when dev was just restored from prod's backup.

Is there anything in some settings or something where the aggregations are messed up or not working correctly for some reason? Maybe memory limits or disk, or something? I'm not having much luck finding out how they could be different. Even rebuilding the aggreations on prod through BIDS didnt change the count (i tested on a few partitions). Its like the prod server doesnt recognize the aggregations for some reason
|||let me rephrase that

the aggregation counts are there, like there are 25-30 per partition, but when i view the record count distirbution per aggreagation, all my records are in "No aggregation"
|||

Just to double check, are you using MOLAP storage?

How did you do your backup and restore? Did you do a full archive and restore or did you script the structures and re-process on dev?

Are you by any chance doing just a processData on your partitions? (this will not process aggregations)

Do you have any proactive caching on prod which might cause the cache to be flushed?

|||
yes using MOLAP

After more investigating, there was no full backup. We did try doing a full deployment from dev to production last night and it didnt change anything even after a full reprocess..

we dont have proactive caching set up

still stumped on this one..
|||we did another deploy and it seems to fix the issue, just a weird anamoly
|||Strange. All I can think if is that someone might have designed additional aggregations after the solution was deployed to production.

dev and production server - cache issue

i have two servers, both running SSAS, my production server recently became really slow performing lookups, just in one cube. I cant seem to find the root cause. I took a backup of the production and loaded it exactly as production but on a different server. I can query the cube in development fast. Looking at profiler, it seems the one cube with performance issues isnt looking up things in cache at first, it takes one try, then queries are fast. I have read some places online about cache warming, which is a great idea, but it doesnt make sense that the same cube in my dev environment is working just fine without cache warming. Anyone have any insights?

Are there any security roles applied in production that may not be applied in dev?

There are a couple of things around security roles that could result is slightly slower performance. If you are running as a server admin you sometimes get better performance as security roles are not applied at all. And the formula engine caches cannot be shared over security roles. So if you had mulitple different secuirty roles being used in production the amount of cache re-use would be reduced.

|||no, the security roles are the same. After investigating more, I found the Aggregation Manager util that comes with the samples and ran that against dev and prod, it looks like prod has 0 for aggregation counts where dev has counts..I wonder how that can be though, why one wouldn't be working and the other would when dev was just restored from prod's backup.

Is there anything in some settings or something where the aggregations are messed up or not working correctly for some reason? Maybe memory limits or disk, or something? I'm not having much luck finding out how they could be different. Even rebuilding the aggreations on prod through BIDS didnt change the count (i tested on a few partitions). Its like the prod server doesnt recognize the aggregations for some reason
|||let me rephrase that

the aggregation counts are there, like there are 25-30 per partition, but when i view the record count distirbution per aggreagation, all my records are in "No aggregation"
|||

Just to double check, are you using MOLAP storage?

How did you do your backup and restore? Did you do a full archive and restore or did you script the structures and re-process on dev?

Are you by any chance doing just a processData on your partitions? (this will not process aggregations)

Do you have any proactive caching on prod which might cause the cache to be flushed?

|||
yes using MOLAP

After more investigating, there was no full backup. We did try doing a full deployment from dev to production last night and it didnt change anything even after a full reprocess..

we dont have proactive caching set up

still stumped on this one..
|||we did another deploy and it seems to fix the issue, just a weird anamoly
|||Strange. All I can think if is that someone might have designed additional aggregations after the solution was deployed to production.

dev and production server - cache issue

i have two servers, both running SSAS, my production server recently became really slow performing lookups, just in one cube. I cant seem to find the root cause. I took a backup of the production and loaded it exactly as production but on a different server. I can query the cube in development fast. Looking at profiler, it seems the one cube with performance issues isnt looking up things in cache at first, it takes one try, then queries are fast. I have read some places online about cache warming, which is a great idea, but it doesnt make sense that the same cube in my dev environment is working just fine without cache warming. Anyone have any insights?

Are there any security roles applied in production that may not be applied in dev?

There are a couple of things around security roles that could result is slightly slower performance. If you are running as a server admin you sometimes get better performance as security roles are not applied at all. And the formula engine caches cannot be shared over security roles. So if you had mulitple different secuirty roles being used in production the amount of cache re-use would be reduced.

|||no, the security roles are the same. After investigating more, I found the Aggregation Manager util that comes with the samples and ran that against dev and prod, it looks like prod has 0 for aggregation counts where dev has counts..I wonder how that can be though, why one wouldn't be working and the other would when dev was just restored from prod's backup.

Is there anything in some settings or something where the aggregations are messed up or not working correctly for some reason? Maybe memory limits or disk, or something? I'm not having much luck finding out how they could be different. Even rebuilding the aggreations on prod through BIDS didnt change the count (i tested on a few partitions). Its like the prod server doesnt recognize the aggregations for some reason
|||let me rephrase that

the aggregation counts are there, like there are 25-30 per partition, but when i view the record count distirbution per aggreagation, all my records are in "No aggregation"
|||

Just to double check, are you using MOLAP storage?

How did you do your backup and restore? Did you do a full archive and restore or did you script the structures and re-process on dev?

Are you by any chance doing just a processData on your partitions? (this will not process aggregations)

Do you have any proactive caching on prod which might cause the cache to be flushed?

|||
yes using MOLAP

After more investigating, there was no full backup. We did try doing a full deployment from dev to production last night and it didnt change anything even after a full reprocess..

we dont have proactive caching set up

still stumped on this one..
|||we did another deploy and it seems to fix the issue, just a weird anamoly
|||Strange. All I can think if is that someone might have designed additional aggregations after the solution was deployed to production.

Determining which version of SQL is running

hi peter... i have a question about, how i can see if my sql server is the version 2005 sp2 and, what is the diference with server and server agent... i've checked the updates and the machine says i have up to date... but i dont know witch is.

thank you for your help

There are several ways to determine which version you are running. You can open a query window and type in "SELECT @.@.VERSION". This will return your version information. You can also go to Add/Remove Programs, click on your SQL 2005 entry, click on Remove and then Report. This will display a nice summary of what components/versions you are running on your machine.

Thanks,
Sam Lester (MSFT)

As for SQL Agent, it is a job scheduling service for SQL 2005. You can read about it here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx

Determining which version of SQL is running

hi peter... i have a question about, how i can see if my sql server is the version 2005 sp2 and, what is the diference with server and server agent... i've checked the updates and the machine says i have up to date... but i dont know witch is.

thank you for your help

There are several ways to determine which version you are running. You can open a query window and type in "SELECT @.@.VERSION". This will return your version information. You can also go to Add/Remove Programs, click on your SQL 2005 entry, click on Remove and then Report. This will display a nice summary of what components/versions you are running on your machine.

Thanks,
Sam Lester (MSFT)

As for SQL Agent, it is a job scheduling service for SQL 2005. You can read about it here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx