Indexes and Deadlocks: February 2012

Friday, February 24, 2012

Indexes and Statistics

Hi,
Do I need to create statistic on a column that already has an index?
Thanks in advance,
LeilaNo.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:OJU9AxzVGHA.4956@.TK2MSFTNGP09.phx.gbl...
Hi,
Do I need to create statistic on a column that already has an index?
Thanks in advance,
Leila|||The first column in the index already have statistics, with that index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Leila" <Leilas@.hotpop.com> wrote in message news:OJU9AxzVGHA.4956@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> Hi,
> Do I need to create statistic on a column that already has an index?
> Thanks in advance,
> Leila
>[/vbcol]|||Nope. The process of indexing does create the statistics in the background.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OJU9AxzVGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do I need to create statistic on a column that already has an index?
> Thanks in advance,
> Leila
>

Indexes and Statistics

Hi,
Do I need to create statistic on a column that already has an index?
Thanks in advance,
LeilaNo.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:OJU9AxzVGHA.4956@.TK2MSFTNGP09.phx.gbl...
Hi,
Do I need to create statistic on a column that already has an index?
Thanks in advance,
Leila|||The first column in the index already have statistics, with that index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Leila" <Leilas@.hotpop.com> wrote in message news:OJU9AxzVGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do I need to create statistic on a column that already has an index?
> Thanks in advance,
> Leila
>|||Nope. The process of indexing does create the statistics in the background.
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OJU9AxzVGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Do I need to create statistic on a column that already has an index?
> Thanks in advance,
> Leila
>

indexes and query optimization

I have web page that users use to specify sort and selection critieria for a query. So there is a "most common" type set of where clauses, but they can change. Every time the query is run, there are where clauses on multiple columns, and joins from some columns to other tables, and then it is sorted by a different column.

I am wondering if I should have a clustered index on this table since there are so many columns used in where clauses. Would it be useful to cluster on the column it is sorted by, or should I just stick with nonclustered indexes?
ThanksIf you have sort order by different columns, clustered index will not help.
You can have only one clustered index over a table.
But you can still have none clustered indexes over columns you do order by over.

indexes and merge replication

just putting out a feeler on this since I think I am about to be asked to do
this.
Can I add/remove indexes on replicated tables? If so, will that schema
change replicate to subscribers (sql server 2000 subscribers)?
I have used the sp_repladdcolumn stored procedure. Hopefully there is one
for indexes as well?
any info is appreciated. Thanks.
Thanks Paul. I'll checkout sp_addscriptexec in sql server books online.
I appreciate the direction.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1d3f01c49a59$3a4fed60$a501280a@.phx.gbl...
> DJC,
> you can use sp_addscriptexec to replicate index changes.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Indexes and keys are not synced w/ snapshot publication

Hello,
I am having trouble getting the indexes and primary keys copied to the
subscriber in a snapshot publication. I created a snapshot publication and
added an article to it using the wizard. Then I used the SQL Management
Studio to configure the properties of the article and set the following
options to true.
Copy primary key
Copy clustered index
Copy nonclustered indexes
Copy check constraints
Copy foreign keys
I generated the SQL script and got the code listed below to re-create the
article. When I use this script to create the article, the settings mentioned
above are "true" as expected. However, when the snapshot is applied to the
subscriber, none of the keys or indexes are transferred eventough everything
suceeds.
Does anyone know what else I can do to make this work? Did I not configure
it correctly?
Here is the code to recreate the article.
exec sp_addarticle
@.publication = @.PUBLICATON_NAME,
@.article = @.TABLE_NAME,
@.source_owner = N'dbo',
@.source_object = @.TABLE_NAME,
@.type = N'logbased',
@.description = null,
@.creation_script = null,
@.pre_creation_cmd = N'drop',
@.schema_option = 0x00000000080350DD,
@.identityrangemanagementoption = N'none',
@.destination_table = @.DESTINATION_TABLE_NAME,
@.destination_owner = N'dbo',
@.vertical_partition = N'false'
P.S - FYI, the destination table name is different than the source table
name as per our biz requirements. Could this be why?
Thanks!
Johnny
UPDATE: I just did some additional testing and found that if the destination
table stays the same as the source, the PKs and the indexes are copied to the
subscriber as they should be. It seems like this problem only occurs when the
destination table is different.
Does anyone know if and how I can work around this or force it to include
them even though the destination table is different?
Johnny
"Johnny" wrote:

> Hello,
> I am having trouble getting the indexes and primary keys copied to the
> subscriber in a snapshot publication. I created a snapshot publication and
> added an article to it using the wizard. Then I used the SQL Management
> Studio to configure the properties of the article and set the following
> options to true.
> Copy primary key
> Copy clustered index
> Copy nonclustered indexes
> Copy check constraints
> Copy foreign keys
>
> I generated the SQL script and got the code listed below to re-create the
> article. When I use this script to create the article, the settings mentioned
> above are "true" as expected. However, when the snapshot is applied to the
> subscriber, none of the keys or indexes are transferred eventough everything
> suceeds.
> Does anyone know what else I can do to make this work? Did I not configure
> it correctly?
>
> Here is the code to recreate the article.
> ----
> exec sp_addarticle
> @.publication = @.PUBLICATON_NAME,
> @.article = @.TABLE_NAME,
> @.source_owner = N'dbo',
> @.source_object = @.TABLE_NAME,
> @.type = N'logbased',
> @.description = null,
> @.creation_script = null,
> @.pre_creation_cmd = N'drop',
> @.schema_option = 0x00000000080350DD,
> @.identityrangemanagementoption = N'none',
> @.destination_table = @.DESTINATION_TABLE_NAME,
> @.destination_owner = N'dbo',
> @.vertical_partition = N'false'
>
> P.S - FYI, the destination table name is different than the source table
> name as per our biz requirements. Could this be why?
> Thanks!
> Johnny
|||Hi Johnny,
The indexes, constraints should be copied to the subscriber even though the
destination object name is different than source object name. I suspect that
you probably still have the "old" table with the source table name and the
same constraints at the subscriber. What happens in this case is that since
constraint names have to be unique across all tables, the distribution agent
simply cannot create a constraint on the "new" table with the same name as
one on the "old" table.
-Raymond
"Johnny" wrote:
[vbcol=seagreen]
> UPDATE: I just did some additional testing and found that if the destination
> table stays the same as the source, the PKs and the indexes are copied to the
> subscriber as they should be. It seems like this problem only occurs when the
> destination table is different.
> Does anyone know if and how I can work around this or force it to include
> them even though the destination table is different?
> Johnny
>
> "Johnny" wrote:
|||Wow!!!!! You hit it right on the money. That was the problem - I had the old
table with the same index names. After dropping this table, I was able to
successfully distribute the snapshot with a different destination name and
all indexes, primary keys, etc. were successfully transferred.
Thanks Raymond.
- Johnny
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Johnny,
> The indexes, constraints should be copied to the subscriber even though the
> destination object name is different than source object name. I suspect that
> you probably still have the "old" table with the source table name and the
> same constraints at the subscriber. What happens in this case is that since
> constraint names have to be unique across all tables, the distribution agent
> simply cannot create a constraint on the "new" table with the same name as
> one on the "old" table.
> -Raymond
> "Johnny" wrote:

Indexes and Filegroups

I'm fishing for some advice here.

I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.

I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)

I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?

I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.

So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?

The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.

many thanks

SteveI've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.

if data set weighs in at around 6Gb assignation each table into its own filegroup not requeried

The Access tables don't have primary keys. MS SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.

add new field int type with identity and set it as primary key

if you wont make quick application - do not use access 97 oledb connection to Mssql server. rebuld it to accessXP ADP project

Indexes and File groups

Something strange.

I have a database(SQL2000) with two file group(on seperate physical
drives).
One is meant for table data[PRIMARY] and one for indexes [INDEX].

So i create a table on the [PRIMARY] file group, and fill in
data.

Next I build a clustered index on the table, on the [INDEX] filegroup.

Once the index is built, the database now indicates that the filegroup
for the table [INDEX]! and not [PRIMARY] as i originally set it up for!

My question it then: Has the table been moved or is this somehow an
error in SQL server?
I would really appreciate any thought anyone might have on this?Jens

A clustered index is the table (Well not quiet, but close enough). It
is impossible to have a clustered index on a different filegroup from
the data. You can build non-clustered on a seperate filegroup.

I suggest you rebuild your clustered index on your primary filegroup.
Regards

John|||Aha! Solved some mysteries for me :-). Thank you very much.
I guess i didnt quite understand how clustered indexes worked.

Actually i have a bunch of tables with clustered indexes which
currently reside my file group for indexes. The good news is,if I
understand you correctly,
the if I simply rebuild the clustered index on my data file group
the table data will be moved back.|||Jens

Yes, rebuilding the clustered index will move the table. You can also
do it through enterprise manager, using design table, this rebuilds the
index for you.

Regards

John|||Im planning to recreate the clustered index like this:

CREATE
CLUSTERED INDEX [idx-clusteredindex]
ON
[dbo].[TABLE_NAME]([COLOUMN_NANE])
WITH
DROP_EXISTING,
FILLFACTOR = 90
ON
[PRIMARY]

As I understand this will alse cause all non-clustered index
on the table to be rebuilt/recalculated as well.
Is this infact the case of do I have to
do i have to do it explicitly afterwards like:

DBCC DBREINDEX ([dbo].[TABLE_NAME],[idx-nonclustered],90)

johnbandettini@.yahoo.co.uk wrote:
> Jens
> Yes, rebuilding the clustered index will move the table. You can also
> do it through enterprise manager, using design table, this rebuilds
the
> index for you.
>
> Regards
>
> John

Indexes and Deadlocks

What are the exact reasons heavily fragmented Indexes can cause Deadlocks?
Is it purely because queries will run faster without fragmentation,
therefore lock escalation will happen quicker?
TIA, ChrisRbasically, yes.
Greg Jackson
PDX, Oregon

Indexes and Deadlocks

Indexes and ADO.NET

I have an app that uses both SQL 2005 and SQL 2000 as the DB. My
question is when I run a query with ADO.NET throught VB.NET without
specifing the "WITH INDEX" in the query, does SQL automatically use any
indexes available?
Also, I tried running the SQL Tunning Wizard w/ SQL2005 and after I get
the results on which indexes to create How do I find out which
field/columns are being used in that index without first creating the
index and going to the table view?
Thanks in advance
Mike>I have an app that uses both SQL 2005 and SQL 2000 as the DB. My
> question is when I run a query with ADO.NET throught VB.NET without
> specifing the "WITH INDEX" in the query, does SQL automatically use any
> indexes available?
It will use an index if it makes sense to do so in the query. The mere
presence of an index does not guarantee that it is a useful index (either
overall or for a specific query).
If you can provide more specific details, we may be able to help. Index
consideration, in general, is a very broad topic and there is no brief,
unilateral, "do this and don't do that" answer.

> Also, I tried running the SQL Tunning Wizard w/ SQL2005 and after I get
> the results on which indexes to create How do I find out which
> field/columns are being used in that index
Can't you view the CREATE INDEX script? The table(s)/column(s) will be
specified there.|||ok. Here is a sample of the tables / Querys I am performing.
[--TicketsDetails TABLE --]
Ticket_Detail_ID int 4 0
lLocationID int 4 1
Ticket_ID int 4 1
dtCreated datetime 8 1
dtUpdated datetime 8 1
sTicket_Number nvarchar 10 1
sDescription nvarchar 50 1
Reference_ID int 4 1
PK_Ticket_Detail_ID on Ticket_Detail_ID
NDX_Ticket_ID on Ticket_ID
NDX_Description on sDescription
Querys I run
1- Select * from TicketsDetails where Ticket_Detail_ID = 231
2- Select count(*) as TotalSold from TicketsDetails where Ticket_ID =
12345 and Detail_Type_ID = 9222
3- Select * from TicketsDetails where sDescription = 'ABC123'
4- Select * from TicketsDetails where sDescription like 'ABC1%'
As far as vieweing the CREATE INDEX script I have not been able to find
anywhere in the tunning wizard where it will show me the script. It
tells me that I will get 60% improvement over my currrent configuration
but all I can see if the results report on which index names is going
to add but thats about it.
Thanks in advance
Mike

Indexes :-

hi,
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
--
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju

Indexes :-

hi,
one small ques on indexes, i added one composite index on table to avoud clu
s index scan(cost 70%) , when i checked executioin plan it is showing me lik
e clus index seek using the above defined index + bookmark lookup operator a
lso..
so which soln should i go for clus index scan or bookmark operator using clu
s index seek (for composit index0
SanjuRun the query both ways in Query Analyzer and look at the Total Plan cost
... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju

Indexes :-

hi,
one small ques on indexes, i added one composite index on table to avoud clus index scan(cost 70%) , when i checked executioin plan it is showing me like clus index seek using the above defined index + bookmark lookup operator also..
so which soln should i go for clus index scan or bookmark operator using clus index seek (for composit index0
Sanju
Run the query both ways in Query Analyzer and look at the Total Plan cost
.... use whichever is cheaper..
I suspect if you are returning more than (maybe) 5% of the rows in the table
the clustered index scan might be cheaper...
However it does depend on the percentage of the rows returned, a single or
very small number of rows returned will likely be best served by the
nonclust and bookmark lookup, a larger percentage of rows and the clustered
index scan will be faster...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:B0F41810-3BC3-4D2C-9996-7D08C46F614D@.microsoft.com...
> hi,
> one small ques on indexes, i added one composite index on table to avoud
clus index scan(cost 70%) , when i checked executioin plan it is showing me
like clus index seek using the above defined index + bookmark lookup
operator also..
> so which soln should i go for clus index scan or bookmark operator using
clus index seek (for composit index0
> --
> Sanju

Indexes & Statistics

1) We have SQL Server 2005 database on windows 2003 server.
2) We have CREATE INDEX & CREATE STATISTICS.
3) For indexes, we can query sysindexes. What about statistics? Are these objects since there is a CREATE & corresponding DROP.
4) Does creating indexes also create statistics & if so do we have to drop both of them. If we just drop index, will the corresponding left over statistic will have any effect.
5) How do indexes on individual PK, FK & other columns based on observation compare against indexes and statistics suggested by index tuning advisor?

K. Murli Krishna wrote:
3) For indexes, we can query sysindexes. What about statistics? Are these objects since there is a CREATE & corresponding DROP.

You will have to query sys.stats and sys.stats_columns.

K. Murli Krishna wrote:
4) Does creating indexes also create statistics & if so do we have to drop both of them. If we just drop index, will the corresponding left over statistic will have any effect.

Yes, creating index automatically creates the statistics also and it is part of the index. Dropping index will remove the statistics also.

K. Murli Krishna wrote:
5) How do indexes on individual PK, FK & other columns based on observation compare against indexes and statistics suggested by index tuning advisor?

Only primary key and unique key constraints are enforced using unique indexes. FK constraints do not create any indexes on the referenced columns. You will have to create it yourself. And I don't quite understand your question about index tuning advisor. Database Tuning Advisor suggests indexes on column(s) based on your workload and it will also consider existing indexes for analysis.

|||

Thanks.

DBCC SHOW_STATISTICS ( table , target ). What is target in this? Our tables are not in dbo. So, do we mention [schema_name].[table]?

How do we rebuid indexes & when all it is necessary. Are statistics better or indexes. Do statistics occupy disk/memory like indexes.

With 13 indexes & 59 statistics suggested by index tuning advisor, we are getting 95 % cost improvement. With 90 indexes on FK's & individual columns, we are getting 97 % improvement. Which should we retain? This is apart from PK's and UK's which are unavoidable.

Indexes & "OR"

Hi,
i have 2 tables:
table 1 - called Feed. contains the following fields:
productName
PartNumber
ManufacturerName
PartNumberManufacturerName (this field concatenates the part number field
and the manufacturer name field).
Table 2 - called Products. contains the following fields:
productID
ProductName
PartNumber1
PartNumber2
PartNumber3
ManufacturerName
Mapkey1 - (this field concatenates the PartNumber1 field and the
manufacturername field)
Mapkey2 - (this field concatenates the PartNumber2 field and the
manufacturername field)
Mapkey3 - (this field concatenates the PartNumber3 field and the
manufacturername field)
I have a query that finds the productID for the records in the feed table.
query is as follows
select a.productName, a.partnumber,
from feed a (NOLOCK),
products b (NOLOCK)
where a.partnumberManufacturerName = b.mapKey1 OR
a.partnumberManufacturerName = b.mapKey2 OR
a.partnumberManufacturerName = b.mapKey3
Indexes:
on the feed table, i have an index on the partnumberManufacturerName and on
the products table, i have multiple indexes:
Mapkey1 ,Mapkey2, and Mapkey3
and then on each individual field
Mapkey1
Mapkey2
Mapkey3
My question is which index should i keep and which index should i drop. i'm
not sure which index the query is going to use since i'm using the "OR".
thanks
rafaelwhy the concatentated columns? they don't help performance [and may
hinder it]
why the multiple part numbers in separate columns - is it possible to
have more than 3?
do you have a manufacturers table as well?
i'd seriously consider changing this un-normalized schema to have a
linking table between partnumbers and productIDs (and normalize to have
a manufacturers table)
how does the Feed table get populated?
if you fix the schema, with proper primary and foreign keys, you
probably won't need extra indexes [at least for this query].
Rafael Chemtob wrote:
> Hi,
> i have 2 tables:
> table 1 - called Feed. contains the following fields:
> productName
> PartNumber
> ManufacturerName
> PartNumberManufacturerName (this field concatenates the part number field
> and the manufacturer name field).
> Table 2 - called Products. contains the following fields:
> productID
> ProductName
> PartNumber1
> PartNumber2
> PartNumber3
> ManufacturerName
> Mapkey1 - (this field concatenates the PartNumber1 field and the
> manufacturername field)
> Mapkey2 - (this field concatenates the PartNumber2 field and the
> manufacturername field)
> Mapkey3 - (this field concatenates the PartNumber3 field and the
> manufacturername field)
> I have a query that finds the productID for the records in the feed table.
> query is as follows
> select a.productName, a.partnumber,
> from feed a (NOLOCK),
> products b (NOLOCK)
> where a.partnumberManufacturerName = b.mapKey1 OR
> a.partnumberManufacturerName = b.mapKey2 OR
> a.partnumberManufacturerName = b.mapKey3
> Indexes:
> on the feed table, i have an index on the partnumberManufacturerName and o
n
> the products table, i have multiple indexes:
> Mapkey1 ,Mapkey2, and Mapkey3
> and then on each individual field
> Mapkey1
> Mapkey2
> Mapkey3
> My question is which index should i keep and which index should i drop. i
'm
> not sure which index the query is going to use since i'm using the "OR".
> thanks
> rafael
>
>

Indexes - general question

, etc). This captures the real query plans used of most o
f
your SQL statements, not the stimated query plans. You can then summarize
what query operators are used in these plans and what indexes are used by th
e
query operators. Compare that with all the indexes you have in that database
would give you a list of indexes not being used.
To summarize the index usage this way, I typically dump the plans out to a
text file and then parse that text files for the query operators and index
names.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||Of course, if this is SQL2005, you can inspect DMV
sys.dm_db_index_usage_stats for (whatelse) the index usage info without goin
g
through all that trouble with tracing and parsing.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||... and also the ...missing_indexes... DMVs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FDC00205-B38B-455B-B8D5-8541FC5DD192@.microsoft.com...
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without go
ing
> through all that trouble with tracing and parsing.
> Linchi
> "Griff" wrote:
>|||The basics on how to use the missing index DMVs are listed in this blog
entry:
http://blogs.msdn.com/queryoptteam/.../01/613516.aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Z22fShmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> ... and also the ...missing_indexes... DMVs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:FDC00205-B38B-455B-B8D5-8541FC5DD192@.microsoft.com...
>|||Linchi Shea (LinchiShea@.discussions.microsoft.com) writes:
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without
> going through all that trouble with tracing and parsing. >
But beware that the information only applies to when SQL Server was
last restarted, or when the database was restored. So the indexes that
is needed for that report your boss runs at the end of the month may
appear as unused. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Indexes - general question

We have a database that has been in operation for several years now.
Periodically, as the data has grown, we've performed index analyses and
added new indexes to help boost performance.
Is there a way of running some sort of trace on SQL Server to show which
indexes were useful but are now no longer being used because they've been
superseded by other (possibly multi-columned) indexes? I don't see any
point in maintaining indexes that are of no use... Unfortunately, I can't
drop all indexes and start from scratch!
Thanks
GriffA good start would be to run your most frequently used SQL/Sps through the
Query Analyser Indeex Tuning Wizard and see what the results say. If you
have pretty good indexes in place, it shouldnt recommend too many changes.
But also, be warned, QA may not always be 100% correct with its decisions,
but will definately give you some good grounding!
Immy
"Griff" <howling@.the.moon> wrote in message
news:eNZfz7emGHA.2280@.TK2MSFTNGP03.phx.gbl...
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I
> can't drop all indexes and start from scratch!
> Thanks
> Griff
>|||See if this helps.
How to Identify Non-Active SQL Server Indexes
http://www.sql-server-performance.com/lm_index_elimination_english.asp
AMB
"Griff" wrote:
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can't
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||If your environment permits, trace for execution plans for a complete cycle
(e.g. a day, a week, etc). This captures the real query plans used of most of
your SQL statements, not the stimated query plans. You can then summarize
what query operators are used in these plans and what indexes are used by the
query operators. Compare that with all the indexes you have in that database
would give you a list of indexes not being used.
To summarize the index usage this way, I typically dump the plans out to a
text file and then parse that text files for the query operators and index
names.
Linchi
"Griff" wrote:
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can't
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||Of course, if this is SQL2005, you can inspect DMV
sys.dm_db_index_usage_stats for (whatelse) the index usage info without going
through all that trouble with tracing and parsing.
Linchi
"Griff" wrote:
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can't
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||... and also the ...missing_indexes... DMVs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FDC00205-B38B-455B-B8D5-8541FC5DD192@.microsoft.com...
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without going
> through all that trouble with tracing and parsing.
> Linchi
> "Griff" wrote:
>> We have a database that has been in operation for several years now.
>> Periodically, as the data has grown, we've performed index analyses and
>> added new indexes to help boost performance.
>> Is there a way of running some sort of trace on SQL Server to show which
>> indexes were useful but are now no longer being used because they've been
>> superseded by other (possibly multi-columned) indexes? I don't see any
>> point in maintaining indexes that are of no use... Unfortunately, I can't
>> drop all indexes and start from scratch!
>> Thanks
>> Griff
>>|||The basics on how to use the missing index DMVs are listed in this blog
entry:
http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Z22fShmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> ... and also the ...missing_indexes... DMVs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:FDC00205-B38B-455B-B8D5-8541FC5DD192@.microsoft.com...
>> Of course, if this is SQL2005, you can inspect DMV
>> sys.dm_db_index_usage_stats for (whatelse) the index usage info without
>> going
>> through all that trouble with tracing and parsing.
>> Linchi
>> "Griff" wrote:
>> We have a database that has been in operation for several years now.
>> Periodically, as the data has grown, we've performed index analyses and
>> added new indexes to help boost performance.
>> Is there a way of running some sort of trace on SQL Server to show which
>> indexes were useful but are now no longer being used because they've
>> been
>> superseded by other (possibly multi-columned) indexes? I don't see any
>> point in maintaining indexes that are of no use... Unfortunately, I
>> can't
>> drop all indexes and start from scratch!
>> Thanks
>> Griff
>>
>|||Linchi Shea (LinchiShea@.discussions.microsoft.com) writes:
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without
> going through all that trouble with tracing and parsing. >
But beware that the information only applies to when SQL Server was
last restarted, or when the database was restored. So the indexes that
is needed for that report your boss runs at the end of the month may
appear as unused. :-)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Indexes - general question

A good start would be to run your most frequently used SQL/Sps through the
Query Analyser Indeex Tuning Wizard and see what the results say. If you
have pretty good indexes in place, it shouldnt recommend too many changes.
But also, be warned, QA may not always be 100% correct with its decisions,
but will definately give you some good grounding!
Immy
"Griff" <howling@.the.moon> wrote in message
news:eNZfz7emGHA.2280@.TK2MSFTNGP03.phx.gbl...
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I
> can't drop all indexes and start from scratch!
> Thanks
> Griff
>See if this helps.
How to Identify Non-Active SQL Server Indexes
http://www.sql-server-performance.c...ion_english.asp
AMB
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||If your environment permits, trace for execution plans for a complete cycle
(e.g. a day, a week, etc). This captures the real query plans used of most o
f
your SQL statements, not the stimated query plans. You can then summarize
what query operators are used in these plans and what indexes are used by th
e
query operators. Compare that with all the indexes you have in that database
would give you a list of indexes not being used.
To summarize the index usage this way, I typically dump the plans out to a
text file and then parse that text files for the query operators and index
names.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||Of course, if this is SQL2005, you can inspect DMV
sys.dm_db_index_usage_stats for (whatelse) the index usage info without goin
g
through all that trouble with tracing and parsing.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||... and also the ...missing_indexes... DMVs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:FDC00205-B38B-455B-B8D5-8541FC5DD192@.microsoft.com...[vbcol=seagreen]
> Of course, if this is SQL2005, you can inspect DMV
> sys.dm_db_index_usage_stats for (whatelse) the index usage info without go
ing
> through all that trouble with tracing and parsing.
> Linchi
> "Griff" wrote:
>|||We have a database that has been in operation for several years now.
Periodically, as the data has grown, we've performed index analyses and
added new indexes to help boost performance.
Is there a way of running some sort of trace on SQL Server to show which
indexes were useful but are now no longer being used because they've been
superseded by other (possibly multi-columned) indexes? I don't see any
point in maintaining indexes that are of no use... Unfortunately, I can't
drop all indexes and start from scratch!
Thanks
Griff|||A good start would be to run your most frequently used SQL/Sps through the
Query Analyser Indeex Tuning Wizard and see what the results say. If you
have pretty good indexes in place, it shouldnt recommend too many changes.
But also, be warned, QA may not always be 100% correct with its decisions,
but will definately give you some good grounding!
Immy
"Griff" <howling@.the.moon> wrote in message
news:eNZfz7emGHA.2280@.TK2MSFTNGP03.phx.gbl...
> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I
> can't drop all indexes and start from scratch!
> Thanks
> Griff
>|||See if this helps.
How to Identify Non-Active SQL Server Indexes
http://www.sql-server-performance.c...ion_english.asp
AMB
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||If your environment permits, trace for execution plans for a complete cycle
(e.g. a day, a week, etc). This captures the real query plans used of most o
f
your SQL statements, not the stimated query plans. You can then summarize
what query operators are used in these plans and what indexes are used by th
e
query operators. Compare that with all the indexes you have in that database
would give you a list of indexes not being used.
To summarize the index usage this way, I typically dump the plans out to a
text file and then parse that text files for the query operators and index
names.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>|||Of course, if this is SQL2005, you can inspect DMV
sys.dm_db_index_usage_stats for (whatelse) the index usage info without goin
g
through all that trouble with tracing and parsing.
Linchi
"Griff" wrote:

> We have a database that has been in operation for several years now.
> Periodically, as the data has grown, we've performed index analyses and
> added new indexes to help boost performance.
> Is there a way of running some sort of trace on SQL Server to show which
> indexes were useful but are now no longer being used because they've been
> superseded by other (possibly multi-columned) indexes? I don't see any
> point in maintaining indexes that are of no use... Unfortunately, I can'
t
> drop all indexes and start from scratch!
> Thanks
> Griff
>
>

Indexes - Fill Factor option...

Hi,
Lets suppose a table with 300.000+ rows. This table is heavily Updated,
SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
most of cases, a row is between 7 and 10 times updated, in different columns.
I have created 5 indexes in this table, corresponding to columns that I
frequently use in SELECTs or UPDATEs actions.
BOL tells me that a 100(0)% Fill Factor should not be used on heavily
updated table.
In this particular case, what Fill Factor percentage can be used in each
index?
Thanks in advance...
Marco.
It depends on the distribution of the data for each index. If, for an index, you always insert rows
at the end (increasing higher values), you won't get fragmentation. I recommend that you try a
fillfactor value, and monitor the fragmentation based on that, using DBCC SHOWCONTIG. And, read
this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Marco Pais" <MarcoPais@.discussions.microsoft.com> wrote in message
news:A5E66D4F-3978-4D0A-835B-BAAC1871A34A@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.

Indexes

Hi,
I hope someone can help me out with this matter.
I am trying to implement a DW Solution for an
Organization. I need to clarify the following points.
(1)what sort of indexes should i use on a DW Database to
optimize performance?
(2)Is there any easy to follow guides on DW Design?
pls advice me on this
regards
ImranThis may help, talks about OLAP and DW
http://www.sql-server-performance.c...performance.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"Imran" <imranv@.cntconnect.net> wrote in message
news:208201c3fc58$6cce5910$a501280a@.phx.gbl...
> Hi,
> I hope someone can help me out with this matter.
> I am trying to implement a DW Solution for an
> Organization. I need to clarify the following points.
> (1)what sort of indexes should i use on a DW Database to
> optimize performance?
> (2)Is there any easy to follow guides on DW Design?
> pls advice me on this
> regards
> Imran
>

Indexes

Hi,
We have a process which deletes and inserts between 100,000 to 150,000
records on a daily basis in 2-3 of our tables. These tables are queried on
certain fields. So we have created some non-clustered indexes on these
tables. The indexes gave us performance gain while querying however, our
deletion and insertion process has now started taking longer time.
To avoid the delay during deletion and insertion process i am planning to
drop the indexes before this process runs and then create them after the
process has ran.
Now the question is - Does dropping and creating indexes on a daily basis
will create any problems?
Thanks and Regards,
Parag> Now the question is - Does dropping and creating indexes on a daily basis
> will create any problems?
As long as your maintenance window allows this, there is no harm in
recreating indexes. Be aware that in the FULL or BULK_LOGGED recovery
model, log space and log backup space requirements will reflect the index
rebuild activity..
Hope this helps.
Dan Guzman
SQL Server MVP
"Parag Gaikwad" <Parag Gaikwad@.discussions.microsoft.com> wrote in message
news:4234885A-EF64-482F-B43E-08CA69699B21@.microsoft.com...
> Hi,
> We have a process which deletes and inserts between 100,000 to 150,000
> records on a daily basis in 2-3 of our tables. These tables are queried on
> certain fields. So we have created some non-clustered indexes on these
> tables. The indexes gave us performance gain while querying however, our
> deletion and insertion process has now started taking longer time.
> To avoid the delay during deletion and insertion process i am planning to
> drop the indexes before this process runs and then create them after the
> process has ran.
> Now the question is - Does dropping and creating indexes on a daily basis
> will create any problems?
> Thanks and Regards,
> Parag

Indexes

Hello!
How can I check indexes ond the SQL server 2000 database? I think that it
should work much faster so I would like to check do I need to reindex
something or not. Is there any built in tool for this? Can I loose something
if I reindex some table?
Thx,
MarcoYou should read this excellent whitepaper:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Marko" <marko@.marko.mr> wrote in message news:d2dgjs$m31$1@.ss405.t-com.hr...">
> Hello!
> How can I check indexes ond the SQL server 2000 database? I think that it
> should work much faster so I would like to check do I need to reindex
> something or not. Is there any built in tool for this? Can I loose somethi
ng
> if I reindex some table?
> Thx,
> Marco
>

Indexes

Hi, is there any type of monitor I can use to determine what affect an index
(or indexes) has?
We recently changed/added some indexes to a table, and since then,
performance has gone down hill...but I need proof that it was the indexes
before removing.
Thanks.You can run profiler to look at your worst performing queries. It can
capture the query text and execution plan. Then, look at the execution
plans for the queries. If the plans are less efficient, i.e. higher cost,
than they were before and use the new indexes, there is a good chance that
the indexes are at fault.
Christian Smith
"SQL" <nospam@.asdfadsf.com> wrote in message
news:OZBDsOP7DHA.3804@.tk2msftngp13.phx.gbl...
> Hi, is there any type of monitor I can use to determine what affect an
index
> (or indexes) has?
> We recently changed/added some indexes to a table, and since then,
> performance has gone down hill...but I need proof that it was the indexes
> before removing.
> Thanks.
>|||execution plan is key.
look for table scans (or index scans)
apply new index and hopefull the scans turn into SEEKS.
that is generally a good thing.
obviously this post is over-simplified, but that's it in a nutshell.
cheers,
Greg Jackson
PDX, Oregon

indexes

Does anyone know how to easily calculate an index? please
dont give me that site "Estimating the size of a table
with an index" because it's complicated to read much less
understand...PP,
if you want a non-analytical way, then empirically is the easiest, and often
the most accurate. Simply create the index and bulk load the table with 1000
rows then note the index sixe. Add another 1000 rows and note the size. Do
this several times then you can extrapolate to any number of rows to know
the index size.
HTH,
Paul Ibison

Indexes

Hello,
Anyone knows how to determine if an index is being used.
For example: I have several tables which have multiple indexes.
I want to know which of them are not being used so I can delete them and
release some space.
Is there a stored procedure to get these statistics?
TIA
Regards,
Eduardo SicouretWhat version of SQL Server? SQL Server 2000 doesn't keep track of such infor
mation, so you'd have to
do it using a Profiler trace or similar. 2005 does, and you can look at the
information using some
of the new dynamic management views (dm_db_missing_indexes_%).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl..
.
> Hello,
> Anyone knows how to determine if an index is being used.
> For example: I have several tables which have multiple indexes.
> I want to know which of them are not being used so I can delete them and r
elease some space.
> Is there a stored procedure to get these statistics?
> TIA
> Regards,
> Eduardo Sicouret
>|||I'm sorry...
I'm using SQL Server 2000...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server? SQL Server 2000 doesn't keep track of such
> information, so you'd have to do it using a Profiler trace or similar.
> 2005 does, and you can look at the information using some of the new
> dynamic management views (dm_db_missing_indexes_%).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:OBtesHpfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>|||You are in for a lot more work, then and the question is whether you want to
do this in the end. You
can use Profiler to catch the execution plans, save such a trace and parse t
he text for the
execution plans to see what indexes were used, compare that to the indexes i
n the database and see
which weren't used.
Also, I by mistake types the wrong name for the dynamic management views for
2005, it should be
sys.dm_db_index_usage_stats and possibly
dm_db_index_operational_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl..
.
> I'm sorry...
> I'm using SQL Server 2000...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> escribi
en el mensaje
> news:uRQfaKpfGHA.4496@.TK2MSFTNGP03.phx.gbl...
>|||I'm not sure what causes updates to that usage table. I've tested it by
doing queries against my tables, inspecting the execution plan and then
finding no usage entry for the index indicated by the execution plan...what
gives?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uan97RpfGHA.324@.TK2MSFTNGP02.phx.gbl...
> You are in for a lot more work, then and the question is whether you want
> to do this in the end. You can use Profiler to catch the execution plans,
> save such a trace and parse the text for the execution plans to see what
> indexes were used, compare that to the indexes in the database and see
> which weren't used.
> Also, I by mistake types the wrong name for the dynamic management views
> for 2005, it should be
> sys.dm_db_index_usage_stats and possibly
> dm_db_index_operational_stats.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:uY3aRNpfGHA.2068@.TK2MSFTNGP02.phx.gbl...
>

Indexes

Hi - Can anyone tell me the best approach to indexing a database I am using
for reporting?
I have a data table & a time table(below), the time table has a timeID field
with an incrementing integer ID & a clustered Index on this field.
Each time record represents a unique reporting time stamp with all time
details such as day of week, monthname, day of month etc... as other fields.
The data table joins to the time table with the timeID as a foreign key in
the data table.
I am wondering whether I should drop the TimeID field on the time table &
instead use the "localtime" field as the clustered index instead since all
localtime values will be unique (& remove the timeID foreign key from the
data table & replace it with a localtime field).
Would this be more efficient than an integer key as I am likely to query
data by time ranges' table definitions below.
Thanks for any advice on this!!
Mike Knee
CREATE TABLE [dbo].[Data] (
[DataID] [int] IDENTITY (1, 1) NOT NULL ,
[HeaderID] [int] NOT NULL ,
[Value] [float] NOT NULL ,
[TimeID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DimTime] (
[TimeID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeLocal] [smalldatetime] NOT NULL ,
[Year] [smallint] NULL ,
[MonthName] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[MonthNumber] [tinyint] NULL ,
[DayName] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[Quarter] [tinyint] NULL ,
[DayOfWeek] [tinyint] NULL ,
[DayOfMonth] [tinyint] NULL ,
[DayOfYear] [smallint] NULL ,
[WeekOfYear] [tinyint] NULL ,
[Hour] [tinyint] NULL ,
[IsHoliday] [tinyint] NULL ,
[IsWeekday] [tinyint] NULL ,
[IsWorkingDay] [tinyint] NULL ,
[Date] [smalldatetime] NULL
) ON [PRIMARY]
GOOn Thu, 2 Nov 2006 12:56:15 -0000, Michael Knee wrote:

>Hi - Can anyone tell me the best approach to indexing a database I am using
>for reporting?
(snip)
>I am wondering whether I should drop the TimeID field on the time table &
>instead use the "localtime" field as the clustered index instead since all
>localtime values will be unique (& remove the timeID foreign key from the
>data table & replace it with a localtime field).
>Would this be more efficient than an integer key as I am likely to query
>data by time ranges' table definitions below.
Hi Michael,
Based on the infoprmation given: yes.
But the best way to find out is to perform tests. On your hardware, with
your queries, and against your data.
Hugo Kornelis, SQL Server MVP

Indexes

I have a SQL Server 2000 database with numerous cluster and non-cluster
indexes.
I have a stored procedure that has several queries. This stored procedure
has query that uses index B3 first and then another query that uses B4 index
.
What is the best practice to have two separate indexes or one index (B3/B4)
that used by this stored procedure?
Thank You,
Table A
B1 : Cluster
B2
B3 :Non-Cluster
B4 :Non-ClusterThere is no best practice in that regard. Use whatever indexes improve your
performance, but try not to create too many indexes or you will end up
hurting data modification performance.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B66C6D12-3E2E-4171-AABB-6FA620517275@.microsoft.com...
> I have a SQL Server 2000 database with numerous cluster and non-cluster
> indexes.
> I have a stored procedure that has several queries. This stored procedure
> has query that uses index B3 first and then another query that uses B4
> index.
> What is the best practice to have two separate indexes or one index
> (B3/B4)
> that used by this stored procedure?
> Thank You,
>
> Table A
> B1 : Cluster
> B2
> B3 :Non-Cluster
> B4 :Non-Cluster
>
>

Indexes

Hi.
Where could I obtain some White Paper about how SQL Server 2000 manages
indexes?.
I woul like to know why the server reads so many records when doing a select
over the fields of the primary key in a table. (I think that the server read
s
so many records as many matches with the first field of the primary key. If
so, can I change it and improve performance?).
Note : I see the records readed in the SQL Analyser.Manuel
I'd start first reading BOL .
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
"Manuel Torres" <ManuelTorres@.discussions.microsoft.com> wrote in message
news:A5ED3D3D-4B8F-465D-B269-88A486F4B456@.microsoft.com...
> Hi.
> Where could I obtain some White Paper about how SQL Server 2000 manages
> indexes?.
> I woul like to know why the server reads so many records when doing a
> select
> over the fields of the primary key in a table. (I think that the server
> reads
> so many records as many matches with the first field of the primary key.
> If
> so, can I change it and improve performance?).
> Note : I see the records readed in the SQL Analyser.
>|||Thanks.
I will read it.
"Uri Dimant" wrote:

> Manuel
> I'd start first reading BOL .
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx[/ur
l]
> "Manuel Torres" <ManuelTorres@.discussions.microsoft.com> wrote in message
> news:A5ED3D3D-4B8F-465D-B269-88A486F4B456@.microsoft.com...
>
>

Indexes

Hi,
I am new to SQL 2000. We have bought the full version and
we have created some tables with large numbers of records
(20m, 60m etc...) but I wish to know more about Indexes
and all the different types within SQL 2000 and which one
to use and for what.
Clustered and Non-clustered needs to be explained in laman
terms please.
Thanks,
Skcsks
Look ath the following articles
http://www.sql-server-performance.c...ing_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...ite_indexes.asp
http://www.sql-server-performance.c...ing_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:e20f01c40b3f$88983c60$a101280a@.phx.gbl...
> Hi,
> I am new to SQL 2000. We have bought the full version and
> we have created some tables with large numbers of records
> (20m, 60m etc...) but I wish to know more about Indexes
> and all the different types within SQL 2000 and which one
> to use and for what.
> Clustered and Non-clustered needs to be explained in laman
> terms please.
> Thanks,
> Skc

Indexes

I have cluster index created on a composite primary key (acct_key,period).
I would like to create a non-cluster index on the acct_key field, since
there are numerous sql statements that extract single value from this field.
Please let me know if cluster index has a composite primary key neither
field should be in a non-cluster index?A) If you already have a clustered index on (acct_key, period), there is no
reason to create a non-clustered index on acct_key.
B) Non-clustered indexes always contain the columns from the clustered
index.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
> I have cluster index created on a composite primary key (acct_key,period).
> I would like to create a non-cluster index on the acct_key field, since
> there are numerous sql statements that extract single value from this
> field.
> Please let me know if cluster index has a composite primary key neither
> field should be in a non-cluster index?
>|||> A) If you already have a clustered index on (acct_key, period), there is no reason to crea
te a
> non-clustered index on acct_key.
... unless you do it to cover queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23VCUMJ49FHA.3980@.TK2MSFTNGP14.phx.gbl...
> A) If you already have a clustered index on (acct_key, period), there is n
o reason to create a
> non-clustered index on acct_key.
> B) Non-clustered indexes always contain the columns from the clustered ind
ex.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:36B52E1A-429C-487E-8BA5-7CDE2417A3A2@.microsoft.com...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> ... unless you do it to cover queries.
You'd have to have a pretty wide table for that to make a difference --
the clustered index already covers every possible query...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||* every possible query that uses acct_key, that is.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OwxGFZ$9FHA.3308@.TK2MSFTNGP11.phx.gbl...
> You'd have to have a pretty wide table for that to make a difference --
> the clustered index already covers every possible query...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||Hmm, yes, assuming this is the only column to be in the nc index (which I no
w see was the case,
re-reading the OP). I was thrown off a bit by this:

I have difficulties understanding what "extract single values from this fiel
d" means. My thinking
was that the NC index could cover queries where the restriction is for some
other column than the
first column in the CL index (to enable nc ix scan instead of cl ix scan).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23bXUAbC%23FHA.4004@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
>* every possible query that uses acct_key, that is.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eazdSDC%23FHA.2320@.TK2MSFTNGP11.phx.gbl...
>

Indexes

How can i send Indexes of all table in the sql database to a text file
--
Yousuf Khan
ProgrammerHi,
By using the generate sql script option we will be able to generate scripts
for any objects in the DB.
Select Only the script indexes option so u will have the script only for all
indexes and save this to a file.
any questions le me know.
--
Herbert
"Yousuf" wrote:

> How can i send Indexes of all table in the sql database to a text file
> --
> Yousuf Khan
> Programmer|||Hi Herbert
You can also run a small select statement againt sysindexes table and save
the output to a text file.
Someting like:
sp_MSForEachTable 'exec sp_helpindex ''?'''
Good luck
JP
"Herbert" wrote:
[vbcol=seagreen]
> Hi,
> By using the generate sql script option we will be able to generate script
s
> for any objects in the DB.
> Select Only the script indexes option so u will have the script only for a
ll
> indexes and save this to a file.
> any questions le me know.
> --
> Herbert
>
> "Yousuf" wrote:
>

Indexes

Hi,
Do I need to create non-clustered index on same columns as clustered index
has, if I have already created clustered index ? What I am really asking is
whether clustered index only order table physically or it also creates index
..., and if it orders table phusically only may I gain something when I make
also non-clustered index ...
Thank youB.J
No, you don't need to create NC on the column that has already CI.
http://www.sql-server-performance.com/clustered_indexes.asp
http://www.sql-server-performance.com/nonclustered_indexes.asp
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:FAED71F9-78BA-4E8D-B5A6-2BDB076B8AF6@.microsoft.com...
> Hi,
> Do I need to create non-clustered index on same columns as clustered index
> has, if I have already created clustered index ? What I am really asking
is
> whether clustered index only order table physically or it also creates
index
> ..., and if it orders table phusically only may I gain something when I
make
> also non-clustered index ...
> Thank you|||The clustered index does indeed come with an index tree. NC index on the same column *can* be
beneficial, however, in case you can cover queries using that nc index. This is because the nc index
only contains the columns over which you create the index, so more rows will fit on the leaf level
of that index. Imagine a scan now, where SQL Server can read fewer pages compared to of SLQ server
needed to read the data pages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:FAED71F9-78BA-4E8D-B5A6-2BDB076B8AF6@.microsoft.com...
> Hi,
> Do I need to create non-clustered index on same columns as clustered index
> has, if I have already created clustered index ? What I am really asking is
> whether clustered index only order table physically or it also creates index
> ..., and if it orders table phusically only may I gain something when I make
> also non-clustered index ...
> Thank you|||Hi, Tibor
> The clustered index does indeed come with an index tree. NC index on the
same column *can* be
> beneficial,
Can you elaborate a little bit under what circumstances it can be benefical?
I have just finished test with a larger table that one column has both
indexes and even if I got fewer rows it was using clustrerd index.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%231D6PFmGFHA.2900@.TK2MSFTNGP10.phx.gbl...
> The clustered index does indeed come with an index tree. NC index on the
same column *can* be
> beneficial, however, in case you can cover queries using that nc index.
This is because the nc index
> only contains the columns over which you create the index, so more rows
will fit on the leaf level
> of that index. Imagine a scan now, where SQL Server can read fewer pages
compared to of SLQ server
> needed to read the data pages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:FAED71F9-78BA-4E8D-B5A6-2BDB076B8AF6@.microsoft.com...
> > Hi,
> >
> > Do I need to create non-clustered index on same columns as clustered
index
> > has, if I have already created clustered index ? What I am really asking
is
> > whether clustered index only order table physically or it also creates
index
> > ..., and if it orders table phusically only may I gain something when I
make
> > also non-clustered index ...
> >
> > Thank you
>|||> Can you elaborate a little bit under what circumstances it can be benefical?
I'll try:-). Example:
CREATE TABLE t(c1 int, c2 char(5000))
Clustered index on c1. With the row size I specified above, only one row fit per data page (makes it
easy to do the maths). Imagine 100000 rows.
SELECT SUM(c1) FROM t
Above need to look at every row. Only one row fit per page, so SQL Server need to look at 100000
pages to do the SUM.
Now create a non-clustered index on c1. And do the same SELECT. A non-clustered index has one row in
the leaf page per row in the table. For the sake of discussion, say that 1000 rows fit per index
page. You have 100000 rows in the table and 1000 rows fit per index page. I.e., the leaf level of
the index has about 100 pages. SQL Server can do the SUM by scanning the leaf level of the NC index
and only have to read 100 pages. We cut down from reading 100000 pages to 100 pages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u9l07emGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> Hi, Tibor
>> The clustered index does indeed come with an index tree. NC index on the
> same column *can* be
>> beneficial,
> Can you elaborate a little bit under what circumstances it can be benefical?
> I have just finished test with a larger table that one column has both
> indexes and even if I got fewer rows it was using clustrerd index.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%231D6PFmGFHA.2900@.TK2MSFTNGP10.phx.gbl...
>> The clustered index does indeed come with an index tree. NC index on the
> same column *can* be
>> beneficial, however, in case you can cover queries using that nc index.
> This is because the nc index
>> only contains the columns over which you create the index, so more rows
> will fit on the leaf level
>> of that index. Imagine a scan now, where SQL Server can read fewer pages
> compared to of SLQ server
>> needed to read the data pages.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "B.J." <BJ@.discussions.microsoft.com> wrote in message
>> news:FAED71F9-78BA-4E8D-B5A6-2BDB076B8AF6@.microsoft.com...
>> > Hi,
>> >
>> > Do I need to create non-clustered index on same columns as clustered
> index
>> > has, if I have already created clustered index ? What I am really asking
> is
>> > whether clustered index only order table physically or it also creates
> index
>> > ..., and if it orders table phusically only may I gain something when I
> make
>> > also non-clustered index ...
>> >
>> > Thank you
>>
>|||Hi ,Tibor
In theoretics , looks fine.
I did the test based on example that you gave me,
In both queries ( after creation NCI) the optimizer was used clustered
indexe scan. Its ok, because we have a clustered index but I was expecting
to see that the optimizer was looked at NCI. The execution time is the same
as well.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eNJ5zHnGFHA.2736@.TK2MSFTNGP09.phx.gbl...
> > Can you elaborate a little bit under what circumstances it can be
benefical?
> I'll try:-). Example:
> CREATE TABLE t(c1 int, c2 char(5000))
> Clustered index on c1. With the row size I specified above, only one row
fit per data page (makes it
> easy to do the maths). Imagine 100000 rows.
> SELECT SUM(c1) FROM t
> Above need to look at every row. Only one row fit per page, so SQL Server
need to look at 100000
> pages to do the SUM.
> Now create a non-clustered index on c1. And do the same SELECT. A
non-clustered index has one row in
> the leaf page per row in the table. For the sake of discussion, say that
1000 rows fit per index
> page. You have 100000 rows in the table and 1000 rows fit per index page.
I.e., the leaf level of
> the index has about 100 pages. SQL Server can do the SUM by scanning the
leaf level of the NC index
> and only have to read 100 pages. We cut down from reading 100000 pages to
100 pages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u9l07emGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> > Hi, Tibor
> >> The clustered index does indeed come with an index tree. NC index on
the
> > same column *can* be
> >> beneficial,
> >
> > Can you elaborate a little bit under what circumstances it can be
benefical?
> > I have just finished test with a larger table that one column has both
> > indexes and even if I got fewer rows it was using clustrerd index.
> >
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%231D6PFmGFHA.2900@.TK2MSFTNGP10.phx.gbl...
> >> The clustered index does indeed come with an index tree. NC index on
the
> > same column *can* be
> >> beneficial, however, in case you can cover queries using that nc index.
> > This is because the nc index
> >> only contains the columns over which you create the index, so more rows
> > will fit on the leaf level
> >> of that index. Imagine a scan now, where SQL Server can read fewer
pages
> > compared to of SLQ server
> >> needed to read the data pages.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> >> news:FAED71F9-78BA-4E8D-B5A6-2BDB076B8AF6@.microsoft.com...
> >> > Hi,
> >> >
> >> > Do I need to create non-clustered index on same columns as clustered
> > index
> >> > has, if I have already created clustered index ? What I am really
asking
> > is
> >> > whether clustered index only order table physically or it also
creates
> > index
> >> > ..., and if it orders table phusically only may I gain something when
I
> > make
> >> > also non-clustered index ...
> >> >
> >> > Thank you
> >>
> >>
> >
> >
>|||I got a significantly lower cost with the non-clustered index. Here's a reproduction sript. It uses
only 10000 rows to make it faster to run, along with the STATISTICS IO result I got:
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE t(c1 int identity, c2 char(5000) default 'a')
--Insert values
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 10000
BEGIN
INSERT t DEFAULT VALUES
SET @.i = @.i + 1
END
GO
CREATE CLUSTERED INDEX t_cl ON t(c1)
SET STATISTICS IO ON
GO
SELECT SUM(c1) FROM t
--Table 't'. Scan count 1, logical reads 10002, physical reads 1, read-ahead reads 10016.
GO
CREATE NONCLUSTERED INDEX t_nc ON t(c1)
GO
SELECT SUM(c1) FROM t
--Table 't'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23ItcPwnGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> Hi ,Tibor
> In theoretics , looks fine.
> I did the test based on example that you gave me,
> In both queries ( after creation NCI) the optimizer was used clustered
> indexe scan. Its ok, because we have a clustered index but I was expecting
> to see that the optimizer was looked at NCI. The execution time is the same
> as well.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eNJ5zHnGFHA.2736@.TK2MSFTNGP09.phx.gbl...
>> > Can you elaborate a little bit under what circumstances it can be
> benefical?
>> I'll try:-). Example:
>> CREATE TABLE t(c1 int, c2 char(5000))
>> Clustered index on c1. With the row size I specified above, only one row
> fit per data page (makes it
>> easy to do the maths). Imagine 100000 rows.
>> SELECT SUM(c1) FROM t
>> Above need to look at every row. Only one row fit per page, so SQL Server
> need to look at 100000
>> pages to do the SUM.
>> Now create a non-clustered index on c1. And do the same SELECT. A
> non-clustered index has one row in
>> the leaf page per row in the table. For the sake of discussion, say that
> 1000 rows fit per index
>> page. You have 100000 rows in the table and 1000 rows fit per index page.
> I.e., the leaf level of
>> the index has about 100 pages. SQL Server can do the SUM by scanning the
> leaf level of the NC index
>> and only have to read 100 pages. We cut down from reading 100000 pages to
> 100 pages.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u9l07emGFHA.2976@.TK2MSFTNGP15.phx.gbl...
>> > Hi, Tibor
>> >> The clustered index does indeed come with an index tree. NC index on
> the
>> > same column *can* be
>> >> beneficial,
>> >
>> > Can you elaborate a little bit under what circumstances it can be
> benefical?
>> > I have just finished test with a larger table that one column has both
>> > indexes and even if I got fewer rows it was using clustrerd index.
>> >
>> >
>> >
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:%231D6PFmGFHA.2900@.TK2MSFTNGP10.phx.gbl...
>> >> The clustered index does indeed come with an index tree. NC index on
> the
>> > same column *can* be
>> >> beneficial, however, in case you can cover queries using that nc index.
>> > This is because the nc index
>> >> only contains the columns over which you create the index, so more rows
>> > will fit on the leaf level
>> >> of that index. Imagine a scan now, where SQL Server can read fewer
> pages
>> > compared to of SLQ server
>> >> needed to read the data pages.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "B.J." <BJ@.discussions.microsoft.com> wrote in message
>> >> news:FAED71F9-78BA-4E8D-B5A6-2BDB076B8AF6@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > Do I need to create non-clustered index on same columns as clustered
>> > index
>> >> > has, if I have already created clustered index ? What I am really
> asking
>> > is
>> >> > whether clustered index only order table physically or it also
> creates
>> > index
>> >> > ..., and if it orders table phusically only may I gain something when
> I
>> > make
>> >> > also non-clustered index ...
>> >> >
>> >> > Thank you
>> >>
>> >>
>> >
>> >
>>
>

Friday, February 24, 2012

Indexes and Statistics

Indexes and Statistics

indexes and query optimization

indexes and merge replication

Indexes and keys are not synced w/ snapshot publication

Indexes and Filegroups

Indexes and File groups

Indexes and Deadlocks

Indexes and Deadlocks

Indexes and Deadlocks

Indexes and ADO.NET

Indexes :-

Indexes :-

Indexes :-

Indexes & Statistics

Indexes & "OR"

Indexes - general question

Indexes - general question

Indexes - general question

Indexes - Fill Factor option...

Indexes

Indexes

Indexes

Indexes

indexes

Indexes

Indexes

Indexes

Indexes

Indexes

Indexes

Indexes

Indexes

Indexes

Indexes and Deadlocks

Blog Archive

About Me