I have a query that looks something like this:
select fields from tableA
select fields from tableB
select fields from tableC
...
I want to try to run the Index Tuning Wizard on all of the queries in the
window. However, whenever I try, the ITW tells me "There are no events in
the workload. Either the trace file contains no sql batch or RPC events or
the sql script contained no sql queries." If I select one line and run the
ITW, it works fine - no matter which line I run. I tried adding GO between
each line but that didn't help.
Any ideas how I can get this to work?
Thanks, AndreTurns out it was because there was a syntax error in one of the queries.
Even though the parse completed successfully, when i ran all 84 of the
queries, one of them bombed out. I fixed it, and the ITW is working it's
little self away.
Thanks for the help.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u0PefPTVDHA.2248@.TK2MSFTNGP10.phx.gbl...
> What version and service pack are you on ? The following works fine for me
> on SQL2000 SP3
> use Northwind
> go
> select * from Customers where Country = 'Mexico'
> select * from Customers where City = 'México D.F.'
> select * from Customers where CustomerID = 'ALFKI'
> If I highlight the above in QA and invoke the ITW it generates
> recommendations
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Andre" <AndreGetsEnoughSPAM@.nospam.com> wrote in message
> news:O5mb2LSVDHA.2364@.TK2MSFTNGP09.phx.gbl...
> I have a query that looks something like this:
> select fields from tableA
> select fields from tableB
> select fields from tableC
> ...
> I want to try to run the Index Tuning Wizard on all of the queries in the
> window. However, whenever I try, the ITW tells me "There are no events in
> the workload. Either the trace file contains no sql batch or RPC events or
> the sql script contained no sql queries." If I select one line and run
the
> ITW, it works fine - no matter which line I run. I tried adding GO
between
> each line but that didn't help.
> Any ideas how I can get this to work?
> Thanks, Andre
>
>
Showing posts with label tablea. Show all posts
Showing posts with label tablea. Show all posts
Friday, March 30, 2012
Wednesday, March 7, 2012
Index enquiry
hi all,
I have a table with 700K records with the primary key as cluster index.
TableA {
chrRef char(10), -- key
chrStatus char(2),
dtTrade datetime,
dtSettle datetime,
....
}
When query the table by filter records on non-primay key, the performance is
acceptable (less than 10K records)
e.g. select * from TableA where dtTrade = '20060101'
When records has been grown to 700K, the query is quite slow. I have added
an index (IX_dtTrade) on the column "dtTrade" in order to reduce the query
time. However, i found that SQL server did not use the index (IX_dtTrade) to
speed up the query. SQL server still using the cluster index to retrieve
records. From the help, i found that there was a method to force SQL server
to use the index. As a result, the query time reduce a lot.
e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
For this case,
1) Is there any setup so that the SQL server will use the index (IX_dtTrade)
automatically without explicit the cluase (with index ())?
2) When create index, what is the difference between single index and
compound index in SQL server? It seems that when an index is created on the
column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle" (IX_dtTrade_dtSettle),
the query time is same.
3) If there are many queries filter on columns "dtTrade", "dtSettle" and
"chrStatus", create an index on each column or a compound index on the three
columns?
3) Is it the only way to speed up the query time by creating index on target
column? (provided that no change on number of records)
Any suggestions? Thank in advance!!
Regards,
MartinCheck the execution plan to see how many rows are estimated to be returned. If SQL Server estimates
a large number of rows, it will consider a scan more efficient than using a non-clustered index.
This is because using a non-clustered index, SQL Server will navigate the index, and *for each row*
access the data page. Imagine if you return 10 000 rows, then you have 10 000 data page accesses,
even if the whole table perhaps fits on 5 000 pages. This is easier to explain with a white-board.
Assuming the estimate is off, we need to figure out why. It could be several reasons, for instance:
Bad statistics.
The query you showed us is not what you are running.
You use a stored procedure and the data you search for is a parameter.
The data you search for is a variable.
The condition for the data isn't expressed as in your example.
As for your questions:
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
There's no "magic button" for this. See my above elaboration.
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
An index on several columns, say (a, b) , can be good for conditions like:
A = 2 AND B = 7
But not for:
B = 45
So you need to know your queries in order to create a good indexing strategy. If you are uncertain,
start by one index per column.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
See above.
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
If you don't give SQL Server any way to limit which rows it need to look for in order to determine
which satisfies your condition, well, then SQL Server need to look at each row. An index does just
that.
Also, don't do SELECT *. Only return the columns you need. The main importance for this isn't
perhaps to reduce network bandwidth. It is that you probably lose the ability to cover your queries
with a non-clustered index. Such an index has all the columns that the query need in it and SQL
Server doesn't have to access the data page for each row, the answer is in the index page.
This is a big topic, so I suggest you start studying and reading a bit. Books Online has some good
sections which is a good start. Kalen Delaney's "Inside SQL Server" book is very good at describing
these constructs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
> hi all,
> I have a table with 700K records with the primary key as cluster index.
> TableA {
> chrRef char(10), -- key
> chrStatus char(2),
> dtTrade datetime,
> dtSettle datetime,
> ....
> }
> When query the table by filter records on non-primay key, the performance is acceptable (less than
> 10K records)
> e.g. select * from TableA where dtTrade = '20060101'
> When records has been grown to 700K, the query is quite slow. I have added an index (IX_dtTrade)
> on the column "dtTrade" in order to reduce the query time. However, i found that SQL server did
> not use the index (IX_dtTrade) to speed up the query. SQL server still using the cluster index to
> retrieve records. From the help, i found that there was a method to force SQL server to use the
> index. As a result, the query time reduce a lot.
> e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
> For this case,
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
> Any suggestions? Thank in advance!!
> Regards,
> Martin
>|||thank you for your suggestion!!! really useful!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23UbAQXn9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> Check the execution plan to see how many rows are estimated to be
> returned. If SQL Server estimates a large number of rows, it will consider
> a scan more efficient than using a non-clustered index. This is because
> using a non-clustered index, SQL Server will navigate the index, and *for
> each row* access the data page. Imagine if you return 10 000 rows, then
> you have 10 000 data page accesses, even if the whole table perhaps fits
> on 5 000 pages. This is easier to explain with a white-board.
> Assuming the estimate is off, we need to figure out why. It could be
> several reasons, for instance:
> Bad statistics.
> The query you showed us is not what you are running.
> You use a stored procedure and the data you search for is a parameter.
> The data you search for is a variable.
> The condition for the data isn't expressed as in your example.
> As for your questions:
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
> There's no "magic button" for this. See my above elaboration.
>
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
> An index on several columns, say (a, b) , can be good for conditions like:
> A = 2 AND B = 7
> But not for:
> B = 45
> So you need to know your queries in order to create a good indexing
> strategy. If you are uncertain, start by one index per column.
>
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
> See above.
>
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
> If you don't give SQL Server any way to limit which rows it need to look
> for in order to determine which satisfies your condition, well, then SQL
> Server need to look at each row. An index does just that.
> Also, don't do SELECT *. Only return the columns you need. The main
> importance for this isn't perhaps to reduce network bandwidth. It is that
> you probably lose the ability to cover your queries with a non-clustered
> index. Such an index has all the columns that the query need in it and SQL
> Server doesn't have to access the data page for each row, the answer is in
> the index page.
> This is a big topic, so I suggest you start studying and reading a bit.
> Books Online has some good sections which is a good start. Kalen Delaney's
> "Inside SQL Server" book is very good at describing these constructs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
>> hi all,
>> I have a table with 700K records with the primary key as cluster index.
>> TableA {
>> chrRef char(10), -- key
>> chrStatus char(2),
>> dtTrade datetime,
>> dtSettle datetime,
>> ....
>> }
>> When query the table by filter records on non-primay key, the performance
>> is acceptable (less than 10K records)
>> e.g. select * from TableA where dtTrade = '20060101'
>> When records has been grown to 700K, the query is quite slow. I have
>> added an index (IX_dtTrade) on the column "dtTrade" in order to reduce
>> the query time. However, i found that SQL server did not use the index
>> (IX_dtTrade) to speed up the query. SQL server still using the cluster
>> index to retrieve records. From the help, i found that there was a method
>> to force SQL server to use the index. As a result, the query time reduce
>> a lot.
>> e.g. select * from TableA with index (IX_dtTrade) where dtTrade =>> '20060101'
>> For this case,
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
>> Any suggestions? Thank in advance!!
>> Regards,
>> Martin
>
I have a table with 700K records with the primary key as cluster index.
TableA {
chrRef char(10), -- key
chrStatus char(2),
dtTrade datetime,
dtSettle datetime,
....
}
When query the table by filter records on non-primay key, the performance is
acceptable (less than 10K records)
e.g. select * from TableA where dtTrade = '20060101'
When records has been grown to 700K, the query is quite slow. I have added
an index (IX_dtTrade) on the column "dtTrade" in order to reduce the query
time. However, i found that SQL server did not use the index (IX_dtTrade) to
speed up the query. SQL server still using the cluster index to retrieve
records. From the help, i found that there was a method to force SQL server
to use the index. As a result, the query time reduce a lot.
e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
For this case,
1) Is there any setup so that the SQL server will use the index (IX_dtTrade)
automatically without explicit the cluase (with index ())?
2) When create index, what is the difference between single index and
compound index in SQL server? It seems that when an index is created on the
column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle" (IX_dtTrade_dtSettle),
the query time is same.
3) If there are many queries filter on columns "dtTrade", "dtSettle" and
"chrStatus", create an index on each column or a compound index on the three
columns?
3) Is it the only way to speed up the query time by creating index on target
column? (provided that no change on number of records)
Any suggestions? Thank in advance!!
Regards,
MartinCheck the execution plan to see how many rows are estimated to be returned. If SQL Server estimates
a large number of rows, it will consider a scan more efficient than using a non-clustered index.
This is because using a non-clustered index, SQL Server will navigate the index, and *for each row*
access the data page. Imagine if you return 10 000 rows, then you have 10 000 data page accesses,
even if the whole table perhaps fits on 5 000 pages. This is easier to explain with a white-board.
Assuming the estimate is off, we need to figure out why. It could be several reasons, for instance:
Bad statistics.
The query you showed us is not what you are running.
You use a stored procedure and the data you search for is a parameter.
The data you search for is a variable.
The condition for the data isn't expressed as in your example.
As for your questions:
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
There's no "magic button" for this. See my above elaboration.
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
An index on several columns, say (a, b) , can be good for conditions like:
A = 2 AND B = 7
But not for:
B = 45
So you need to know your queries in order to create a good indexing strategy. If you are uncertain,
start by one index per column.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
See above.
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
If you don't give SQL Server any way to limit which rows it need to look for in order to determine
which satisfies your condition, well, then SQL Server need to look at each row. An index does just
that.
Also, don't do SELECT *. Only return the columns you need. The main importance for this isn't
perhaps to reduce network bandwidth. It is that you probably lose the ability to cover your queries
with a non-clustered index. Such an index has all the columns that the query need in it and SQL
Server doesn't have to access the data page for each row, the answer is in the index page.
This is a big topic, so I suggest you start studying and reading a bit. Books Online has some good
sections which is a good start. Kalen Delaney's "Inside SQL Server" book is very good at describing
these constructs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
> hi all,
> I have a table with 700K records with the primary key as cluster index.
> TableA {
> chrRef char(10), -- key
> chrStatus char(2),
> dtTrade datetime,
> dtSettle datetime,
> ....
> }
> When query the table by filter records on non-primay key, the performance is acceptable (less than
> 10K records)
> e.g. select * from TableA where dtTrade = '20060101'
> When records has been grown to 700K, the query is quite slow. I have added an index (IX_dtTrade)
> on the column "dtTrade" in order to reduce the query time. However, i found that SQL server did
> not use the index (IX_dtTrade) to speed up the query. SQL server still using the cluster index to
> retrieve records. From the help, i found that there was a method to force SQL server to use the
> index. As a result, the query time reduce a lot.
> e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
> For this case,
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
> Any suggestions? Thank in advance!!
> Regards,
> Martin
>|||thank you for your suggestion!!! really useful!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23UbAQXn9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> Check the execution plan to see how many rows are estimated to be
> returned. If SQL Server estimates a large number of rows, it will consider
> a scan more efficient than using a non-clustered index. This is because
> using a non-clustered index, SQL Server will navigate the index, and *for
> each row* access the data page. Imagine if you return 10 000 rows, then
> you have 10 000 data page accesses, even if the whole table perhaps fits
> on 5 000 pages. This is easier to explain with a white-board.
> Assuming the estimate is off, we need to figure out why. It could be
> several reasons, for instance:
> Bad statistics.
> The query you showed us is not what you are running.
> You use a stored procedure and the data you search for is a parameter.
> The data you search for is a variable.
> The condition for the data isn't expressed as in your example.
> As for your questions:
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
> There's no "magic button" for this. See my above elaboration.
>
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
> An index on several columns, say (a, b) , can be good for conditions like:
> A = 2 AND B = 7
> But not for:
> B = 45
> So you need to know your queries in order to create a good indexing
> strategy. If you are uncertain, start by one index per column.
>
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
> See above.
>
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
> If you don't give SQL Server any way to limit which rows it need to look
> for in order to determine which satisfies your condition, well, then SQL
> Server need to look at each row. An index does just that.
> Also, don't do SELECT *. Only return the columns you need. The main
> importance for this isn't perhaps to reduce network bandwidth. It is that
> you probably lose the ability to cover your queries with a non-clustered
> index. Such an index has all the columns that the query need in it and SQL
> Server doesn't have to access the data page for each row, the answer is in
> the index page.
> This is a big topic, so I suggest you start studying and reading a bit.
> Books Online has some good sections which is a good start. Kalen Delaney's
> "Inside SQL Server" book is very good at describing these constructs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
>> hi all,
>> I have a table with 700K records with the primary key as cluster index.
>> TableA {
>> chrRef char(10), -- key
>> chrStatus char(2),
>> dtTrade datetime,
>> dtSettle datetime,
>> ....
>> }
>> When query the table by filter records on non-primay key, the performance
>> is acceptable (less than 10K records)
>> e.g. select * from TableA where dtTrade = '20060101'
>> When records has been grown to 700K, the query is quite slow. I have
>> added an index (IX_dtTrade) on the column "dtTrade" in order to reduce
>> the query time. However, i found that SQL server did not use the index
>> (IX_dtTrade) to speed up the query. SQL server still using the cluster
>> index to retrieve records. From the help, i found that there was a method
>> to force SQL server to use the index. As a result, the query time reduce
>> a lot.
>> e.g. select * from TableA with index (IX_dtTrade) where dtTrade =>> '20060101'
>> For this case,
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
>> Any suggestions? Thank in advance!!
>> Regards,
>> Martin
>
Subscribe to:
Posts (Atom)