Individual Process CPU utilization

MS SQL Server 2000 Enterprise with SP3a
Is there a way to known which process is causing 100% cpu.
Is cpu column in sysprocesses gives that.
How can we see the plan of current running process. Sybase has sp_showplan.
Is there any equivalent stored proc in sql server.
Thanks.
Satwinder..Hi
The CPU column is the cumulative CPU usage, therefore you should be looking
at the rate of change for this value. You may want to look at SET STATISTICS
TIME. Also check out SQL Profiler which will show what statements (including
a duration and I/O details) are being run on the server.
John
"Satwinder" wrote:
> MS SQL Server 2000 Enterprise with SP3a
> Is there a way to known which process is causing 100% cpu.
> Is cpu column in sysprocesses gives that.
> How can we see the plan of current running process. Sybase has sp_showplan.
> Is there any equivalent stored proc in sql server.
> Thanks.
> Satwinder..|||yesterday my server had cpu of 100% for an hour. During that time i did not
want to run profiler and put more load on server.
By looking at processes can we tell which process is utilising max. cpu.
cheers
Satwinder
"John Bell" wrote:
> Hi
> The CPU column is the cumulative CPU usage, therefore you should be looking
> at the rate of change for this value. You may want to look at SET STATISTICS
> TIME. Also check out SQL Profiler which will show what statements (including
> a duration and I/O details) are being run on the server.
> John
> "Satwinder" wrote:
> > MS SQL Server 2000 Enterprise with SP3a
> > Is there a way to known which process is causing 100% cpu.
> >
> > Is cpu column in sysprocesses gives that.
> >
> > How can we see the plan of current running process. Sybase has sp_showplan.
> > Is there any equivalent stored proc in sql server.
> >
> > Thanks.
> > Satwinder..|||Hi,
run the following querry
select spid,hostname,program_name, cpu from master..sysprocesses order by
cpu desc
Amo Lembhe
"Satwinder" wrote:
> yesterday my server had cpu of 100% for an hour. During that time i did not
> want to run profiler and put more load on server.
> By looking at processes can we tell which process is utilising max. cpu.
> cheers
> Satwinder
> "John Bell" wrote:
> > Hi
> >
> > The CPU column is the cumulative CPU usage, therefore you should be looking
> > at the rate of change for this value. You may want to look at SET STATISTICS
> > TIME. Also check out SQL Profiler which will show what statements (including
> > a duration and I/O details) are being run on the server.
> >
> > John
> >
> > "Satwinder" wrote:
> >
> > > MS SQL Server 2000 Enterprise with SP3a
> > > Is there a way to known which process is causing 100% cpu.
> > >
> > > Is cpu column in sysprocesses gives that.
> > >
> > > How can we see the plan of current running process. Sybase has sp_showplan.
> > > Is there any equivalent stored proc in sql server.
> > >
> > > Thanks.
> > > Satwinder..|||CPU in sysprocesses does not indicate currently process comsuning high cpu.
cheers
"Amol Lembhe" wrote:
> Hi,
> run the following querry
> select spid,hostname,program_name, cpu from master..sysprocesses order by
> cpu desc
> Amo Lembhe
> "Satwinder" wrote:
> > yesterday my server had cpu of 100% for an hour. During that time i did not
> > want to run profiler and put more load on server.
> > By looking at processes can we tell which process is utilising max. cpu.
> >
> > cheers
> >
> > Satwinder
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > The CPU column is the cumulative CPU usage, therefore you should be looking
> > > at the rate of change for this value. You may want to look at SET STATISTICS
> > > TIME. Also check out SQL Profiler which will show what statements (including
> > > a duration and I/O details) are being run on the server.
> > >
> > > John
> > >
> > > "Satwinder" wrote:
> > >
> > > > MS SQL Server 2000 Enterprise with SP3a
> > > > Is there a way to known which process is causing 100% cpu.
> > > >
> > > > Is cpu column in sysprocesses gives that.
> > > >
> > > > How can we see the plan of current running process. Sybase has sp_showplan.
> > > > Is there any equivalent stored proc in sql server.
> > > >
> > > > Thanks.
> > > > Satwinder..|||Anyone in the world who can help me on this. We have 500 process and finding
which one is causing cpu to go 100.
Is this at all possible in SQL server.
Help...
"Satwinder" wrote:
> CPU in sysprocesses does not indicate currently process comsuning high cpu.
> cheers
> "Amol Lembhe" wrote:
> > Hi,
> > run the following querry
> > select spid,hostname,program_name, cpu from master..sysprocesses order by
> > cpu desc
> >
> > Amo Lembhe
> >
> > "Satwinder" wrote:
> >
> > > yesterday my server had cpu of 100% for an hour. During that time i did not
> > > want to run profiler and put more load on server.
> > > By looking at processes can we tell which process is utilising max. cpu.
> > >
> > > cheers
> > >
> > > Satwinder
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > The CPU column is the cumulative CPU usage, therefore you should be looking
> > > > at the rate of change for this value. You may want to look at SET STATISTICS
> > > > TIME. Also check out SQL Profiler which will show what statements (including
> > > > a duration and I/O details) are being run on the server.
> > > >
> > > > John
> > > >
> > > > "Satwinder" wrote:
> > > >
> > > > > MS SQL Server 2000 Enterprise with SP3a
> > > > > Is there a way to known which process is causing 100% cpu.
> > > > >
> > > > > Is cpu column in sysprocesses gives that.
> > > > >
> > > > > How can we see the plan of current running process. Sybase has sp_showplan.
> > > > > Is there any equivalent stored proc in sql server.
> > > > >
> > > > > Thanks.
> > > > > Satwinder..|||Hi
If you are running at 100% for that length of time it sounds like you are
already in trouble, therefore the faster you fix it the better regardless of
short term inconvenience. A server side trace will use less resources than
using the GUI and using a disc not used by SQL Server for the output will
reduce any resource conflicts further. It would not require a great deal of
profiling to identify what is wrong especially if you already have a baseline
for the performance, and you will know exactly what piece of code the problem
is occuring. You could even automate the collection of a trace using a
perfmon alert.
John
"Satwinder" wrote:
> yesterday my server had cpu of 100% for an hour. During that time i did not
> want to run profiler and put more load on server.
> By looking at processes can we tell which process is utilising max. cpu.
> cheers
> Satwinder
> "John Bell" wrote:
> > Hi
> >
> > The CPU column is the cumulative CPU usage, therefore you should be looking
> > at the rate of change for this value. You may want to look at SET STATISTICS
> > TIME. Also check out SQL Profiler which will show what statements (including
> > a duration and I/O details) are being run on the server.
> >
> > John
> >
> > "Satwinder" wrote:
> >
> > > MS SQL Server 2000 Enterprise with SP3a
> > > Is there a way to known which process is causing 100% cpu.
> > >
> > > Is cpu column in sysprocesses gives that.
> > >
> > > How can we see the plan of current running process. Sybase has sp_showplan.
> > > Is there any equivalent stored proc in sql server.
> > >
> > > Thanks.
> > > Satwinder..|||On Tue, 1 Aug 2006 04:56:01 -0700, Satwinder
<Satwinder@.discussions.microsoft.com> wrote:
>MS SQL Server 2000 Enterprise with SP3a
>Is there a way to known which process is causing 100% cpu.
>Is cpu column in sysprocesses gives that.
>How can we see the plan of current running process. Sybase has sp_showplan.
>Is there any equivalent stored proc in sql server.
exec sp_who2
>Thanks.
>Satwinder..|||Hi,
get cpu consume for each program
select program_name, sum(cpu) from master..sysprocesses
group by program_name
u can querry system tables to get required info.
"Satwinder" wrote:
> CPU in sysprocesses does not indicate currently process comsuning high cpu.
> cheers
> "Amol Lembhe" wrote:
> > Hi,
> > run the following querry
> > select spid,hostname,program_name, cpu from master..sysprocesses order by
> > cpu desc
> >
> > Amo Lembhe
> >
> > "Satwinder" wrote:
> >
> > > yesterday my server had cpu of 100% for an hour. During that time i did not
> > > want to run profiler and put more load on server.
> > > By looking at processes can we tell which process is utilising max. cpu.
> > >
> > > cheers
> > >
> > > Satwinder
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > The CPU column is the cumulative CPU usage, therefore you should be looking
> > > > at the rate of change for this value. You may want to look at SET STATISTICS
> > > > TIME. Also check out SQL Profiler which will show what statements (including
> > > > a duration and I/O details) are being run on the server.
> > > >
> > > > John
> > > >
> > > > "Satwinder" wrote:
> > > >
> > > > > MS SQL Server 2000 Enterprise with SP3a
> > > > > Is there a way to known which process is causing 100% cpu.
> > > > >
> > > > > Is cpu column in sysprocesses gives that.
> > > > >
> > > > > How can we see the plan of current running process. Sybase has sp_showplan.
> > > > > Is there any equivalent stored proc in sql server.
> > > > >
> > > > > Thanks.
> > > > > Satwinder..|||My question is whenever cpu is 100%, then i start the sql profiler, will it
capture the query causing high cpu. Profiler does not capture already running
queries.
cheers, satwinder
"Satwinder" wrote:
> MS SQL Server 2000 Enterprise with SP3a
> Is there a way to known which process is causing 100% cpu.
> Is cpu column in sysprocesses gives that.
> How can we see the plan of current running process. Sybase has sp_showplan.
> Is there any equivalent stored proc in sql server.
> Thanks.
> Satwinder..|||On Wed, 2 Aug 2006 02:26:01 -0700, Satwinder
<Satwinder@.discussions.microsoft.com> wrote:
>My question is whenever cpu is 100%, then i start the sql profiler, will it
>capture the query causing high cpu. Profiler does not capture already running
>queries.
Yes, it will capture that when complete, even if it was started before
the profiler. I'm pretty certain of that, because I've done traces
catching both begins and ends, and had orphans!
J.sql

Individual Process CPU utilization

MS SQL Server 2000 Enterprise with SP3a
Is there a way to known which process is causing 100% cpu.
Is cpu column in sysprocesses gives that.
How can we see the plan of current running process. Sybase has sp_showplan.
Is there any equivalent stored proc in sql server.
Thanks.
Satwinder..Hi
The CPU column is the cumulative CPU usage, therefore you should be looking
at the rate of change for this value. You may want to look at SET STATISTICS
TIME. Also check out SQL Profiler which will show what statements (including
a duration and I/O details) are being run on the server.
John
"Satwinder" wrote:

> MS SQL Server 2000 Enterprise with SP3a
> Is there a way to known which process is causing 100% cpu.
> Is cpu column in sysprocesses gives that.
> How can we see the plan of current running process. Sybase has sp_showplan
.
> Is there any equivalent stored proc in sql server.
> Thanks.
> Satwinder..|||yesterday my server had cpu of 100% for an hour. During that time i did not
want to run profiler and put more load on server.
By looking at processes can we tell which process is utilising max. cpu.
cheers
Satwinder
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The CPU column is the cumulative CPU usage, therefore you should be lookin
g
> at the rate of change for this value. You may want to look at SET STATISTI
CS
> TIME. Also check out SQL Profiler which will show what statements (includi
ng
> a duration and I/O details) are being run on the server.
> John
> "Satwinder" wrote:
>|||Hi,
run the following querry
select spid,hostname,program_name, cpu from master..sysprocesses order by
cpu desc
Amo Lembhe
"Satwinder" wrote:
[vbcol=seagreen]
> yesterday my server had cpu of 100% for an hour. During that time i did no
t
> want to run profiler and put more load on server.
> By looking at processes can we tell which process is utilising max. cpu.
> cheers
> Satwinder
> "John Bell" wrote:
>|||CPU in sysprocesses does not indicate currently process comsuning high cpu.
cheers
"Amol Lembhe" wrote:
[vbcol=seagreen]
> Hi,
> run the following querry
> select spid,hostname,program_name, cpu from master..sysprocesses order by
> cpu desc
> Amo Lembhe
> "Satwinder" wrote:
>|||Anyone in the world who can help me on this. We have 500 process and finding
which one is causing cpu to go 100.
Is this at all possible in SQL server.
Help...
"Satwinder" wrote:
[vbcol=seagreen]
> CPU in sysprocesses does not indicate currently process comsuning high cpu
.
> cheers
> "Amol Lembhe" wrote:
>|||Hi
If you are running at 100% for that length of time it sounds like you are
already in trouble, therefore the faster you fix it the better regardless of
short term inconvenience. A server side trace will use less resources than
using the GUI and using a disc not used by SQL Server for the output will
reduce any resource conflicts further. It would not require a great deal of
profiling to identify what is wrong especially if you already have a baselin
e
for the performance, and you will know exactly what piece of code the proble
m
is occuring. You could even automate the collection of a trace using a
perfmon alert.
John
"Satwinder" wrote:
[vbcol=seagreen]
> yesterday my server had cpu of 100% for an hour. During that time i did no
t
> want to run profiler and put more load on server.
> By looking at processes can we tell which process is utilising max. cpu.
> cheers
> Satwinder
> "John Bell" wrote:
>|||On Tue, 1 Aug 2006 04:56:01 -0700, Satwinder
<Satwinder@.discussions.microsoft.com> wrote:

>MS SQL Server 2000 Enterprise with SP3a
>Is there a way to known which process is causing 100% cpu.
>Is cpu column in sysprocesses gives that.
>How can we see the plan of current running process. Sybase has sp_showplan.
>Is there any equivalent stored proc in sql server.
exec sp_who2

>Thanks.
>Satwinder..|||Hi,
get cpu consume for each program
select program_name, sum(cpu) from master..sysprocesses
group by program_name
u can querry system tables to get required info.
"Satwinder" wrote:
[vbcol=seagreen]
> CPU in sysprocesses does not indicate currently process comsuning high cpu
.
> cheers
> "Amol Lembhe" wrote:
>|||My question is whenever cpu is 100%, then i start the sql profiler, will it
capture the query causing high cpu. Profiler does not capture already runnin
g
queries.
cheers, satwinder
"Satwinder" wrote:

> MS SQL Server 2000 Enterprise with SP3a
> Is there a way to known which process is causing 100% cpu.
> Is cpu column in sysprocesses gives that.
> How can we see the plan of current running process. Sybase has sp_showplan
.
> Is there any equivalent stored proc in sql server.
> Thanks.
> Satwinder..

Indexing on large table kills Transactional Replication

Hi,
SQL Server 2K Enterprise Ed, running Transactional replication, four
articles being replicated, 1 article containing over 15 million rows.
That table has 4 indexes on it. I created a job that executes a DBCC
INDEXDEFRAG statement against each of the indecies, I run the job at
the weekend, the job never errors, however it kills the Trans
replication.
What am I doing wrong? I used INDEXDEFRAG because of its online
capabilities, but the replication still fails.
Cheers
Scott
Are you having a problem with the Log Reader agent?
If so the problem is that Index Defragging is a logged operation and your
Tlog will balloon. This puts stress on your log reader agent and you will
see that it will experience time outs. The best way to fix this is to change
your Log Reader Agent's PollingInterval - set it to 1, and change the
ReadBatchSize - probably to 50 when you are doing the defragging. When you
not, use the defaults. Using profiles is an excellend way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1108472684.342558.177210@.z14g2000cwz.googlegr oups.com...
> Hi,
> SQL Server 2K Enterprise Ed, running Transactional replication, four
> articles being replicated, 1 article containing over 15 million rows.
> That table has 4 indexes on it. I created a job that executes a DBCC
> INDEXDEFRAG statement against each of the indecies, I run the job at
> the weekend, the job never errors, however it kills the Trans
> replication.
> What am I doing wrong? I used INDEXDEFRAG because of its online
> capabilities, but the replication still fails.
> Cheers
> Scott
>

INDEXES ON A TABLE

Hi,
How can I find out the indexes on a particular table. which data dictionary table I nshould query?
In enterprise manger there is no object called indexes.
pls help.
Thanks,
Retna
Hi,
sp_helpindex <table_name>
sp_help <table_name> (This will list Indexes and all the other information
related to that table)
Indexes will be stored in a system table : sysindexes
Thanks
Hari
MCDBA
"RETNA" <anonymous@.discussions.microsoft.com> wrote in message
news:454888B6-A08B-4930-A3D1-83431F1CE5B1@.microsoft.com...
> Hi,
> How can I find out the indexes on a particular table. which data
dictionary table I nshould query?
> In enterprise manger there is no object called indexes.
> pls help.
> Thanks,
> Retna
|||Is Enterprise Manager - tell "Design..." on table and then look onto toolbar
buttons - one of them is responsible for showing you indexes list (actually
it's not only SHOW you index list but allow you to add new indexes and
modify'n'delete existing ones)
"RETNA" <anonymous@.discussions.microsoft.com> wrote in message
news:454888B6-A08B-4930-A3D1-83431F1CE5B1@.microsoft.com...
> Hi,
> How can I find out the indexes on a particular table. which data
dictionary table I nshould query?
> In enterprise manger there is no object called indexes.
> pls help.
> Thanks,
> Retna
|||Thanks for your help
|||Thank you Alex for your help.

INDEXES ON A TABLE

Hi,
How can I find out the indexes on a particular table. which data dictionary
table I nshould query?
In enterprise manger there is no object called indexes.
pls help.
Thanks,
RetnaHi,
sp_helpindex <table_name>
sp_help <table_name> (This will list Indexes and all the other information
related to that table)
Indexes will be stored in a system table : sysindexes
Thanks
Hari
MCDBA
"RETNA" <anonymous@.discussions.microsoft.com> wrote in message
news:454888B6-A08B-4930-A3D1-83431F1CE5B1@.microsoft.com...
> Hi,
> How can I find out the indexes on a particular table. which data
dictionary table I nshould query?
> In enterprise manger there is no object called indexes.
> pls help.
> Thanks,
> Retna|||Is Enterprise Manager - tell "Design..." on table and then look onto toolbar
buttons - one of them is responsible for showing you indexes list (actually
it's not only SHOW you index list but allow you to add new indexes and
modify'n'delete existing ones)
"RETNA" <anonymous@.discussions.microsoft.com> wrote in message
news:454888B6-A08B-4930-A3D1-83431F1CE5B1@.microsoft.com...
> Hi,
> How can I find out the indexes on a particular table. which data
dictionary table I nshould query?
> In enterprise manger there is no object called indexes.
> pls help.
> Thanks,
> Retna|||Thanks for your help|||Thank you Alex for your help.

INDEXES ON A TABLE

Hi
How can I find out the indexes on a particular table. which data dictionary table I nshould query
In enterprise manger there is no object called indexes
pls help
Thanks
RetnaHi,
sp_helpindex <table_name>
sp_help <table_name> (This will list Indexes and all the other information
related to that table)
Indexes will be stored in a system table : sysindexes
Thanks
Hari
MCDBA
"RETNA" <anonymous@.discussions.microsoft.com> wrote in message
news:454888B6-A08B-4930-A3D1-83431F1CE5B1@.microsoft.com...
> Hi,
> How can I find out the indexes on a particular table. which data
dictionary table I nshould query?
> In enterprise manger there is no object called indexes.
> pls help.
> Thanks,
> Retna|||Is Enterprise Manager - tell "Design..." on table and then look onto toolbar
buttons - one of them is responsible for showing you indexes list (actually
it's not only SHOW you index list but allow you to add new indexes and
modify'n'delete existing ones)
"RETNA" <anonymous@.discussions.microsoft.com> wrote in message
news:454888B6-A08B-4930-A3D1-83431F1CE5B1@.microsoft.com...
> Hi,
> How can I find out the indexes on a particular table. which data
dictionary table I nshould query?
> In enterprise manger there is no object called indexes.
> pls help.
> Thanks,
> Retna|||Thanks for your help|||Thank you Alex for your help.

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?It is my understanding that _wa objects are system created statistics.
--
Thomas
"Sal Young" wrote:
> Does anyone knows what kind of indexes are the ones showing up under certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?It is my understanding that _wa objects are system created statistics.
--
Thomas
"Sal Young" wrote:

> Does anyone knows what kind of indexes are the ones showing up under certa
in
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?
It is my understanding that _wa objects are system created statistics.
Thomas
"Sal Young" wrote:

> Does anyone knows what kind of indexes are the ones showing up under certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?
|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexed Views on SQL Server 2005 Non Enterprise

The feature matrix for SQL Server 2005 states:
"Indexed view matching by the query processor is only supported in
Enterprise Edition."
What does this mean in English? We use indexed views *a lot*. What is
"Indexed view matching by query processor" ?!?
Thanks
--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.comBecause SQL is on-disk compatible throughout all editions, indexed views
exist in all editions. The query processor will only use them as a means to
resolve a query in Enterprise Edition. In short, they exist but serve no
useful function except in Enterprise Edition.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:uktHeu56FHA.1484@.tk2msftngp13.phx.gbl...
> The feature matrix for SQL Server 2005 states:
> "Indexed view matching by the query processor is only supported in
> Enterprise Edition."
> What does this mean in English? We use indexed views *a lot*. What is
> "Indexed view matching by query processor" ?!?
> Thanks
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com|||"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eW2DUX66FHA.2176@.TK2MSFTNGP14.phx.gbl...
> Because SQL is on-disk compatible throughout all editions, indexed views
> exist in all editions. The query processor will only use them as a means
> to resolve a query in Enterprise Edition. In short, they exist but serve
> no useful function except in Enterprise Edition.
> --
Not true. Indexed views are quite usefull in all editions. You must
explicitly query them in other editions, however. To use the indexed view
you need to reference the view directly and use the NOEXPAND hint. In
Enterprise Edition the Query engine will consider rewriting queries against
the base table(s) to go against the indexed view.
"Indexed Views" are in all editions; "Indexed View Query Rewrite" is an EE
feature.
Here's an example:
drop table t
create table t(id int primary key, name varchar(50) null, status int)
insert into t(id,name,status) values (1,'joe',1)
insert into t(id,name,status) values (2,'fred',0)
insert into t(id,name,status) values (2,'alex',0)
go
create view vt
with schemabinding
as
select id,name from dbo.t where status = 1
go
create unique clustered index ix_vt
on vt(id)
go
set showplan_text on
go
select id,name from vt (noexpand)
outputs
StmtText
--
select id,name from vt (noexpand)
(1 row(s) affected)
StmtText
---
|--Clustered Index Scan(OBJECT:([test].[dbo].[vt].[ix_vt]))
(1 row(s) affected)
David|||Geoff N. Hiten wrote:
> Because SQL is on-disk compatible throughout all editions, indexed
> views exist in all editions. The query processor will only use them
> as a means to resolve a query in Enterprise Edition. In short, they
> exist but serve no useful function except in Enterprise Edition.
I apologize for beating a dead horse, but this information is so
shocking that I want to make sure I really understand.
With SQL Server 2005 Standard, there is absolutely zero benefit by
adding an index to a view?
If so, I expect this is going to cause a major performance impact to
our customers. And paying the extra $$$ just for indexed views will
not be an option for them.
I can't believe Microsoft took a feature available in SQL Server 2000
and promoted it to Enterprise only.
--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com|||> "Indexed Views" are in all editions; "Indexed View Query Rewrite" is
> an EE feature.
Thank you for the clarification!
If anyone from Microsoft marketing is listening, the product matrix at
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
should really be clarified.
--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com|||As David pointed out, you can create them and use them, but not
transparently. In EE, if you query a base table and you would have been
better off querying the indexed view, the optimizer uses the indexed view.
In other editions, you have to explicitly use the indexed view rather than
the base table. Sorry if that wasn't clear from my earlier post.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>> Because SQL is on-disk compatible throughout all editions, indexed
>> views exist in all editions. The query processor will only use them
>> as a means to resolve a query in Enterprise Edition. In short, they
>> exist but serve no useful function except in Enterprise Edition.
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>|||The feature usage is exactly the same as SQL Server 2000. Direct use of
Indexed Views is supported in all editions. Transparent use of them when
querying a base table is an EE-only feature.
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>> Because SQL is on-disk compatible throughout all editions, indexed
>> views exist in all editions. The query processor will only use them
>> as a means to resolve a query in Enterprise Edition. In short, they
>> exist but serve no useful function except in Enterprise Edition.
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>|||Hi Jon
Microsoft did not do that. This is exactly the same behavior as in SQL 2000.
You can only use indexed views in Standard edition of SQL 2000 if you
reference them directly:
SELECT ... FROM my_indexed_view
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>> Because SQL is on-disk compatible throughout all editions, indexed
>> views exist in all editions. The query processor will only use them
>> as a means to resolve a query in Enterprise Edition. In short, they
>> exist but serve no useful function except in Enterprise Edition.
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23%23Mo$V86FHA.268@.TK2MSFTNGP10.phx.gbl...
> Hi Jon
> Microsoft did not do that. This is exactly the same behavior as in SQL
> 2000. You can only use indexed views in Standard edition of SQL 2000 if
> you reference them directly:
> SELECT ... FROM my_indexed_view
>
Should be:
SELECT ... FROM my_indexed_view (NOEXPAND)
BOL:
Indexed views can be created in any edition of SQL Server 2005. In SQL
Server 2005 Enterprise Edition, the query optimizer automatically considers
the indexed view. To use an indexed view in all other editions, the NOEXPAND
table hint must be used.
David|||Kalen Delaney wrote:
> Microsoft did not do that. This is exactly the same behavior as in
> SQL 2000. You can only use indexed views in Standard edition of SQL
> 2000 if you reference them directly:
> SELECT ... FROM my_indexed_view
Thanks Kalen. By the way, Inside SQL Server 2000 is one of the best
SQL books ever published. Any chance of a 2005 edition?
--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com|||And, in SQL Server 2005, you have to use the WITH keyword, which is optional
in 2000
SELECT ... FROM my_indexed_view WITH (NOEXPAND)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23$J9r%2396FHA.1184@.TK2MSFTNGP12.phx.gbl...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23%23Mo$V86FHA.268@.TK2MSFTNGP10.phx.gbl...
>> Hi Jon
>> Microsoft did not do that. This is exactly the same behavior as in SQL
>> 2000. You can only use indexed views in Standard edition of SQL 2000 if
>> you reference them directly:
>> SELECT ... FROM my_indexed_view
> Should be:
> SELECT ... FROM my_indexed_view (NOEXPAND)
> BOL:
> Indexed views can be created in any edition of SQL Server 2005. In SQL
> Server 2005 Enterprise Edition, the query optimizer automatically
> considers the indexed view. To use an indexed view in all other editions,
> the NOEXPAND table hint must be used.
>
> David
>
>|||Thanks Jon ...
I'm working on the next one...
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:uJfXwg$6FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Kalen Delaney wrote:
>> Microsoft did not do that. This is exactly the same behavior as in
>> SQL 2000. You can only use indexed views in Standard edition of SQL
>> 2000 if you reference them directly:
>> SELECT ... FROM my_indexed_view
> Thanks Kalen. By the way, Inside SQL Server 2000 is one of the best
> SQL books ever published. Any chance of a 2005 edition?
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uaLyYwD7FHA.736@.TK2MSFTNGP09.phx.gbl...
> And, in SQL Server 2005, you have to use the WITH keyword, which is
> optional in 2000
> SELECT ... FROM my_indexed_view WITH (NOEXPAND)
>
Well, you don't really have to use WITH here.
BOL:
In SQL Server 2005, with some exceptions, table hints are supported in the
FROM clause only when the hints are specified with the WITH keyword. Table
hints also must be specified by using parentheses.
The table hints allowed with and without the WITH keyword are the following:
NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE,
READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT,
READPAST, XLOCK, and NOEXPAND. When these table hints are specified without
the WITH keyword, the hints must be specified alone. For example: FROM t
(fastfirstrow).
David

Indexed Views on SQL Server 2005 Non Enterprise

The feature matrix for SQL Server 2005 states:
"Indexed view matching by the query processor is only supported in
Enterprise Edition."
What does this mean in English? We use indexed views *a lot*. What is
"Indexed view matching by query processor" ?!?
Thanks
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com
Because SQL is on-disk compatible throughout all editions, indexed views
exist in all editions. The query processor will only use them as a means to
resolve a query in Enterprise Edition. In short, they exist but serve no
useful function except in Enterprise Edition.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:uktHeu56FHA.1484@.tk2msftngp13.phx.gbl...
> The feature matrix for SQL Server 2005 states:
> "Indexed view matching by the query processor is only supported in
> Enterprise Edition."
> What does this mean in English? We use indexed views *a lot*. What is
> "Indexed view matching by query processor" ?!?
> Thanks
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
|||"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eW2DUX66FHA.2176@.TK2MSFTNGP14.phx.gbl...
> Because SQL is on-disk compatible throughout all editions, indexed views
> exist in all editions. The query processor will only use them as a means
> to resolve a query in Enterprise Edition. In short, they exist but serve
> no useful function except in Enterprise Edition.
> --
Not true. Indexed views are quite usefull in all editions. You must
explicitly query them in other editions, however. To use the indexed view
you need to reference the view directly and use the NOEXPAND hint. In
Enterprise Edition the Query engine will consider rewriting queries against
the base table(s) to go against the indexed view.
"Indexed Views" are in all editions; "Indexed View Query Rewrite" is an EE
feature.
Here's an example:
drop table t
create table t(id int primary key, name varchar(50) null, status int)
insert into t(id,name,status) values (1,'joe',1)
insert into t(id,name,status) values (2,'fred',0)
insert into t(id,name,status) values (2,'alex',0)
go
create view vt
with schemabinding
as
select id,name from dbo.t where status = 1
go
create unique clustered index ix_vt
on vt(id)
go
set showplan_text on
go
select id,name from vt (noexpand)
outputs
StmtText
select id,name from vt (noexpand)
(1 row(s) affected)
StmtText
|--Clustered Index Scan(OBJECT[test].[dbo].[vt].[ix_vt]))
(1 row(s) affected)
David
|||Geoff N. Hiten wrote:

> Because SQL is on-disk compatible throughout all editions, indexed
> views exist in all editions. The query processor will only use them
> as a means to resolve a query in Enterprise Edition. In short, they
> exist but serve no useful function except in Enterprise Edition.
I apologize for beating a dead horse, but this information is so
shocking that I want to make sure I really understand.
With SQL Server 2005 Standard, there is absolutely zero benefit by
adding an index to a view?
If so, I expect this is going to cause a major performance impact to
our customers. And paying the extra $$$ just for indexed views will
not be an option for them.
I can't believe Microsoft took a feature available in SQL Server 2000
and promoted it to Enterprise only.
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com
|||> "Indexed Views" are in all editions; "Indexed View Query Rewrite" is
> an EE feature.
Thank you for the clarification!
If anyone from Microsoft marketing is listening, the product matrix at
http://www.microsoft.com/sql/prodinf...-features.mspx
should really be clarified.
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com
|||As David pointed out, you can create them and use them, but not
transparently. In EE, if you query a base table and you would have been
better off querying the indexed view, the optimizer uses the indexed view.
In other editions, you have to explicitly use the indexed view rather than
the base table. Sorry if that wasn't clear from my earlier post.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>
|||The feature usage is exactly the same as SQL Server 2000. Direct use of
Indexed Views is supported in all editions. Transparent use of them when
querying a base table is an EE-only feature.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>
|||Hi Jon
Microsoft did not do that. This is exactly the same behavior as in SQL 2000.
You can only use indexed views in Standard edition of SQL 2000 if you
reference them directly:
SELECT ... FROM my_indexed_view
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>
>
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23%23Mo$V86FHA.268@.TK2MSFTNGP10.phx.gbl...
> Hi Jon
> Microsoft did not do that. This is exactly the same behavior as in SQL
> 2000. You can only use indexed views in Standard edition of SQL 2000 if
> you reference them directly:
> SELECT ... FROM my_indexed_view
>
Should be:
SELECT ... FROM my_indexed_view (NOEXPAND)
BOL:
Indexed views can be created in any edition of SQL Server 2005. In SQL
Server 2005 Enterprise Edition, the query optimizer automatically considers
the indexed view. To use an indexed view in all other editions, the NOEXPAND
table hint must be used.
David
|||Kalen Delaney wrote:

> Microsoft did not do that. This is exactly the same behavior as in
> SQL 2000. You can only use indexed views in Standard edition of SQL
> 2000 if you reference them directly:
> SELECT ... FROM my_indexed_view
Thanks Kalen. By the way, Inside SQL Server 2000 is one of the best
SQL books ever published. Any chance of a 2005 edition?
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com

Indexed Views on SQL Server 2005 Non Enterprise

The feature matrix for SQL Server 2005 states:
"Indexed view matching by the query processor is only supported in
Enterprise Edition."
What does this mean in English? We use indexed views *a lot*. What is
"Indexed view matching by query processor" ?!?
Thanks
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.comBecause SQL is on-disk compatible throughout all editions, indexed views
exist in all editions. The query processor will only use them as a means to
resolve a query in Enterprise Edition. In short, they exist but serve no
useful function except in Enterprise Edition.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:uktHeu56FHA.1484@.tk2msftngp13.phx.gbl...
> The feature matrix for SQL Server 2005 states:
> "Indexed view matching by the query processor is only supported in
> Enterprise Edition."
> What does this mean in English? We use indexed views *a lot*. What is
> "Indexed view matching by query processor" ?!?
> Thanks
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com|||"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eW2DUX66FHA.2176@.TK2MSFTNGP14.phx.gbl...
> Because SQL is on-disk compatible throughout all editions, indexed views
> exist in all editions. The query processor will only use them as a means
> to resolve a query in Enterprise Edition. In short, they exist but serve
> no useful function except in Enterprise Edition.
> --
Not true. Indexed views are quite usefull in all editions. You must
explicitly query them in other editions, however. To use the indexed view
you need to reference the view directly and use the NOEXPAND hint. In
Enterprise Edition the Query engine will consider rewriting queries against
the base table(s) to go against the indexed view.
"Indexed Views" are in all editions; "Indexed View Query Rewrite" is an EE
feature.
Here's an example:
drop table t
create table t(id int primary key, name varchar(50) null, status int)
insert into t(id,name,status) values (1,'joe',1)
insert into t(id,name,status) values (2,'fred',0)
insert into t(id,name,status) values (2,'alex',0)
go
create view vt
with schemabinding
as
select id,name from dbo.t where status = 1
go
create unique clustered index ix_vt
on vt(id)
go
set showplan_text on
go
select id,name from vt (noexpand)
outputs
StmtText
--
select id,name from vt (noexpand)
(1 row(s) affected)
StmtText
---
|--Clustered Index Scan(OBJECT[test].[dbo].[vt].[ix_vt]))
(1 row(s) affected)
David|||Geoff N. Hiten wrote:

> Because SQL is on-disk compatible throughout all editions, indexed
> views exist in all editions. The query processor will only use them
> as a means to resolve a query in Enterprise Edition. In short, they
> exist but serve no useful function except in Enterprise Edition.
I apologize for beating a dead horse, but this information is so
shocking that I want to make sure I really understand.
With SQL Server 2005 Standard, there is absolutely zero benefit by
adding an index to a view?
If so, I expect this is going to cause a major performance impact to
our customers. And paying the extra $$$ just for indexed views will
not be an option for them.
I can't believe Microsoft took a feature available in SQL Server 2000
and promoted it to Enterprise only.
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com|||> "Indexed Views" are in all editions; "Indexed View Query Rewrite" is
> an EE feature.
Thank you for the clarification!
If anyone from Microsoft marketing is listening, the product matrix at
http://www.microsoft.com/sql/prodin...e-features.mspx
should really be clarified.
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com|||As David pointed out, you can create them and use them, but not
transparently. In EE, if you query a base table and you would have been
better off querying the indexed view, the optimizer uses the indexed view.
In other editions, you have to explicitly use the indexed view rather than
the base table. Sorry if that wasn't clear from my earlier post.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>|||The feature usage is exactly the same as SQL Server 2000. Direct use of
Indexed Views is supported in all editions. Transparent use of them when
querying a base table is an EE-only feature.
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>|||Hi Jon
Microsoft did not do that. This is exactly the same behavior as in SQL 2000.
You can only use indexed views in Standard edition of SQL 2000 if you
reference them directly:
SELECT ... FROM my_indexed_view
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23%23Mo$V86FHA.268@.TK2MSFTNGP10.phx.gbl...
> Hi Jon
> Microsoft did not do that. This is exactly the same behavior as in SQL
> 2000. You can only use indexed views in Standard edition of SQL 2000 if
> you reference them directly:
> SELECT ... FROM my_indexed_view
>
Should be:
SELECT ... FROM my_indexed_view (NOEXPAND)
BOL:
Indexed views can be created in any edition of SQL Server 2005. In SQL
Server 2005 Enterprise Edition, the query optimizer automatically considers
the indexed view. To use an indexed view in all other editions, the NOEXPAND
table hint must be used.
David|||Kalen Delaney wrote:

> Microsoft did not do that. This is exactly the same behavior as in
> SQL 2000. You can only use indexed views in Standard edition of SQL
> 2000 if you reference them directly:
> SELECT ... FROM my_indexed_view
Thanks Kalen. By the way, Inside SQL Server 2000 is one of the best
SQL books ever published. Any chance of a 2005 edition?
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com

Indexed Views in Enterprise Edition...?

We are using SQL Server 2000 Standard Edition. Among other things, the
"Enterprise" edition adds "indexed views".
Could someone tell me what indexed views are, and what they are good for?
Is this simply an index on a view? Do the underlying tables have to be
static for the index to be effective? Pros/cons?
Thanks!!"JM" <JM@.nospam.com> wrote in message
news:%23F5n2tjEGHA.140@.TK2MSFTNGP12.phx.gbl...
> We are using SQL Server 2000 Standard Edition. Among other things, the
> "Enterprise" edition adds "indexed views".
> Could someone tell me what indexed views are, and what they are good for?
> Is this simply an index on a view? Do the underlying tables have to be
> static for the index to be effective? Pros/cons?
> Thanks!!
>
The BOL has a pretty decent description. Search for the following:
"Designing an Indexed View"
Rick Sawtell
MCT, MCSD, MCDBA|||Also, take a look at this white paper:
http://www.microsoft.com/technet/pr.../ipsql05iv.mspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%230XpE0jEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> "JM" <JM@.nospam.com> wrote in message
> news:%23F5n2tjEGHA.140@.TK2MSFTNGP12.phx.gbl...
> The BOL has a pretty decent description. Search for the following:
> "Designing an Indexed View"
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Indexed views are also available in other SQL Server editions. However, in
Enterprise Edition, indexes on views are automatically considered by the
optimizer and even when the view is not referenced. A NOEXPAND hint is
needed to use view indexes in other editions.
The Books Online describes indexed views in much more detail than can be
discussed here but a short answer is that view indexes contain data
materialized from the underlying tables. This redundant data is
automatically maintained by SQL Server as the underlying data changes.
Consequently, data is dynamic rather than static.
Indexed views are especially nice for aggregated data and can also be used
to avoid complex joins. This can significantly reduce the work needed to
retrieve data by reporting applications with large data volumes. The
downsides are that there are many restrictions on using indexed views (see
BOL) and additional overhead is needed to maintain the view index(es). In
my experience, indexed views may be appropriate for reporting databases but
need to be used carefully in OLTP apps.
Hope this helps.
Dan Guzman
SQL Server MVP
"JM" <JM@.nospam.com> wrote in message
news:%23F5n2tjEGHA.140@.TK2MSFTNGP12.phx.gbl...
> We are using SQL Server 2000 Standard Edition. Among other things, the
> "Enterprise" edition adds "indexed views".
> Could someone tell me what indexed views are, and what they are good for?
> Is this simply an index on a view? Do the underlying tables have to be
> static for the index to be effective? Pros/cons?
> Thanks!!
>

Indexed Views are they supported?

According the documentation in 2000 and 2005 indexed views are only supporte
d
in the Enterprise edition. So please explain why I can create a unique
clustered index on a view that is schemabound on the standard edition of SQL
Server 2000? Is an index on a view different then and indexed view? If so
please explain. Could it be that creating indexed views is supported in all
versions, but only the using the GUI (EM or Management Studio) to create the
views is not supported in Enterprise and Developer edition? I noticed that
"Manage Indexes...", is grayed out of the "All Tasks" drop down on a view in
EM, and I am running standard edition.You can create the index on any edition, however on the lower editions, it
will not be automatically considered in the query plan unless you use a
specific hint in the query.
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:9A56678F-407D-41B2-BD4B-5A95C259EA97@.microsoft.com...
> According the documentation in 2000 and 2005 indexed views are only
> supported
> in the Enterprise edition. So please explain why I can create a unique
> clustered index on a view that is schemabound on the standard edition of
> SQL
> Server 2000? Is an index on a view different then and indexed view? If
> so
> please explain. Could it be that creating indexed views is supported in
> all
> versions, but only the using the GUI (EM or Management Studio) to create
> the
> views is not supported in Enterprise and Developer edition? I noticed
> that
> "Manage Indexes...", is grayed out of the "All Tasks" drop down on a view
> in
> EM, and I am running standard edition.|||So why is "Managed Indexes" grayed out in EM?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You can create the index on any edition, however on the lower editions, it
> will not be automatically considered in the query plan unless you use a
> specific hint in the query.
>
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:9A56678F-407D-41B2-BD4B-5A95C259EA97@.microsoft.com...
>
>|||> So why is "Managed Indexes" grayed out in EM?
I have no idea; I use EM for managing jobs and DTS and that's about it.
Stretching here, because I honestly don't believe EM is this smart, but is
it possible that this indexed view has the only index in the database?
A|||On Wed, 19 Apr 2006 09:17:03 -0700, Greg Larsen wrote:

>So why is "Managed Indexes" grayed out in EM?
Hi Greg,
I just ran a quick test on my copy of EM (connected to a developer
edition of SQL Server 2000). If I create a view with schemabinding, I
can access the "Manage indexes" option in EM. If I drop the view and
recreate it without schemabinding, then (after refreshing the list of
views) the "Manage indexes" option is greyed out.
Have yoou tried it on a view that was created with schemabinding and
that further also satisfies all requirements for creating an indexed
view?
Hugo Kornelis, SQL Server MVP