display SQL Server: index

Showing posts with label index. Show all posts

Friday, March 30, 2012

How do I make it use my index?

Hello!

I have two tables

users and pictures.

table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid

when I do this query:

"select userid from pictures where userid=123"

then It will do a clustered index seek

But If I do any of those:

"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"

It will do a clustered index scan.

How can I force it to seek my index instead of scan?

Thanks!do you have some where clause at the end of the query? when not, then the plan created by sql server is perhaps really the best!
if the where clause shrinks the number of rows in t1 to a fraction of total table row count, you could use

option(loop join)

at the end of the query. this should solve your problem

Monday, March 26, 2012

How do I know if an index column is in descending order from SQL Server?

Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
>
> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>> Hi Peter
>>
>> EXECUTE sp_MShelpindex N'authors', N'aunmind'
>>
>> try this in PUBS
>>
>> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
>>
>>
>> --
>> kind regards
>> Greg O
>> Need to document your databases. Use the firs and still the best AGS SQL
>> Scribe
>> http://www.ag-software.com
>>
>>
>> "Peter Reid" <noreply@.microsoft.com> wrote in message
>> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>> How do I know if an index column is in descending order from SQL Server?
>>
>> I don't want to use "indexkey_property" as it doesn't work from another
>> DB.
>> I also know that I can do something like this:
>>
>> USE <db1>
>> select into a temp table
>>
>> USE <db2>
>> select into another temp table
>>
>>
>> But what I'm actually interested in knowing is where this information is
>> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
>> and furthermore how to query it.
>>
>> Thanks
>>
>>
>>
>>
>
>Hi Peter
Did you try:
EXECUTE pubs..sp_MShelpindex N'authors', N'aunmind'
John
"Peter Reid" wrote:
> Hey all that I want is to be able to run from another DB.
> This "sp_MShelpindex" like the "indexkey_property" only work running from
> the DB where the index sits.
> I need to join this information from multiple DBs in a single query, and I
> was wondering if (and how) this is possible.
> Thanks
> >
> > "GregO" <grego@.community.nospam> wrote in message
> > news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> >> Hi Peter
> >>
> >> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> >>
> >> try this in PUBS
> >>
> >> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
> >>
> >>
> >> --
> >> kind regards
> >> Greg O
> >> Need to document your databases. Use the firs and still the best AGS SQL
> >> Scribe
> >> http://www.ag-software.com
> >>
> >>
> >> "Peter Reid" <noreply@.microsoft.com> wrote in message
> >> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> >> How do I know if an index column is in descending order from SQL Server?
> >>
> >> I don't want to use "indexkey_property" as it doesn't work from another
> >> DB.
> >> I also know that I can do something like this:
> >>
> >> USE <db1>
> >> select into a temp table
> >>
> >> USE <db2>
> >> select into another temp table
> >>
> >>
> >> But what I'm actually interested in knowing is where this information is
> >> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> >> and furthermore how to query it.
> >>
> >> Thanks
> >>
> >>
> >>
> >>
> >
> >
>
>sql

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
ThanksHi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.c...cumented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2
,2,'IsDescending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.c...cumented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks

> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
Thanks
Hi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.co...umented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>
|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2,2,'IsDesc ending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>
|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.co...umented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>
|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks

> "GregO" <grego@.community.nospam> wrote in message
> news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
>

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?
I don't want to use "indexkey_property" as it doesn't work from another DB.
I also know that I can do something like this:
USE <db1>
select into a temp table
USE <db2>
select into another temp table
But what I'm actually interested in knowing is where this information is
stored in SQL Server (as it doesn't seam to on the sysindexkeys table), and
furthermore how to query it.
ThanksHi Peter
EXECUTE sp_MShelpindex N'authors', N'aunmind'
try this in PUBS
http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Look up the INDEXKEY_PROPERTY function in BOL.
SELECT indexkey_property(OBJECT_ID('authors'),2,2,'IsDescending')
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Peter Reid" <noreply@.microsoft.com> wrote in message
news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
> How do I know if an index column is in descending order from SQL Server?
> I don't want to use "indexkey_property" as it doesn't work from another
> DB.
> I also know that I can do something like this:
> USE <db1>
> select into a temp table
> USE <db2>
> select into another temp table
>
> But what I'm actually interested in knowing is where this information is
> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
> and furthermore how to query it.
> Thanks
>|||Hey all that I want is to be able to run from another DB.
This "sp_MShelpindex" like the "indexkey_property" only work running from
the DB where the index sits.
I need to join this information from multiple DBs in a single query, and I
was wondering if (and how) this is possible.
Thanks
"GregO" <grego@.community.nospam> wrote in message
news:uX7vlRJrFHA.904@.tk2msftngp13.phx.gbl...
> Hi Peter
> EXECUTE sp_MShelpindex N'authors', N'aunmind'
> try this in PUBS
> http://www.sql-server-performance.com/ac_sql_server_7_undocumented_sp.asp
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Peter Reid" <noreply@.microsoft.com> wrote in message
> news:eubQTNIrFHA.904@.tk2msftngp13.phx.gbl...
>> How do I know if an index column is in descending order from SQL Server?
>> I don't want to use "indexkey_property" as it doesn't work from another
>> DB.
>> I also know that I can do something like this:
>> USE <db1>
>> select into a temp table
>> USE <db2>
>> select into another temp table
>>
>> But what I'm actually interested in knowing is where this information is
>> stored in SQL Server (as it doesn't seam to on the sysindexkeys table),
>> and furthermore how to query it.
>> Thanks
>>
>

Sunday, February 19, 2012

How do I determine the table from index name

I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?What query/mechanism are you using to generate the list of fragmented
indexes?
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
> >I ran a query to identify indexes with fragmentation problems, and it gives
> > me the index names. I cannot tell from the names what tables or views are
> > being indexed. I have been wondering through the system views, but so far
> > nothing jumps out at me (i.e. sys.table_indexes).
> > I don't think that this is something that has to be solved, since I rebuild
> > indexes once a week, but I would like to know: Starting wtih
> > sys.indexes.object_id, how can I determine the table or view name?
>|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and
>> for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes
>> a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it gives
>> > me the index names. I cannot tell from the names what tables or views are
>> > being indexed. I have been wondering through the system views, but so far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>> My question began with a defragmentation query that I found in an Sql
>> 2005
>> textbook, which produced a list of six suspects with OBJECT_NAME
>> (dt.object_id) = queue_messages_1003150619 or something similar. Since
>> that
>> certainly didn't match any table or view in the database, I assumed it
>> was
>> the name of an index. But the response from Dan Guzman includes a query
>> that
>> shows that it is actually the name of the table or view - which I just
>> said
>> doesn't exist. So now I'm really confused.
>> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books
>> Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC
>> SHOWCONTIG topic, and for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005
>> with sp2, this even takes a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it
>> >gives
>> > me the index names. I cannot tell from the names what tables or views
>> > are
>> > being indexed. I have been wondering through the system views, but so
>> > far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I
>> > rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?
>

Friday, March 30, 2012

How do I make it use my index?

Monday, March 26, 2012

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?

How do I know if an index column is in descending order from SQL Server?

Sunday, February 19, 2012

How do I determine the table from index name

How do I determine the table from index name

How do I determine the table from index name

display SQL Server

Blog Archive

About Me