Tuning Advisor does not give me any recommandation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Tuning Advisor does not give me any recommandation

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2010-06-24 : 04:22:43

Hi
in my sql server 2008 database, i have a table with the following characteristics :

Table Name : Docs
PK : DocID uniqueidentifier
Have some FKs with int datatype
Have some DateTime fields
Have some string fields such as char[10], nvarchar[50], nvarchar[max]

For test pureposes, i insert 100,000 row and then execute a query to return all rows. it take 6 seconds to retrieve all records.
To improve performance, i monitor this query in Sql Server Profiler and create trace file, then pass it to Sql Server tuining Advisor to get some recommandations. but it does not give me any recommandation.
Also i create some indexes on some columns like unique columns or dateTime fields, but it does not take affects to my query, even it take longer than original time!
where is my problem and how to solve it ?
thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-24 : 06:10:20

Please post query, table definitions, index definitions and execution plan, as per [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

--
Gail Shaw
SQL Server MVP

hdv212
Posting Yak Master

140 Posts

Posted - 2010-06-24 : 07:28:02

Hi
I use this simple query :

SELECT * FROM dbo.Docs

Plz download my table definition and query plan from this link :
http://rapidshare.com/files/402296521/MyTableDefinition.rar.html

thanks

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-01 : 09:46:20

Can anybody help me ?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-01 : 10:33:32

If you're doing a simple SELECT * FROM Table, there's no optimisation that can be done. You're asking for all the columns, all the rows, there's only one way for SQL to run that and that is a table scan.

How many rows? Why are you doing an unrestricted select of the entire table?

--
Gail Shaw
SQL Server MVP

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-01 : 14:00:18

Thanks
As i told in my first post, i have 100,000 rows.
I use this simple query to for purepose. you mean because i use this simple query, sql server does not use my index or profiler does have recomandation ?
can u tell me how to assign a criteria to use my indexes ?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-01 : 15:15:40

quote:
Originally posted by hdv212

I use this simple query to for purepose. you mean because i use this simple query, sql server does not use my index or profiler does have recomandation ?

Precisely. There's no way to improve the performance of that query, it has to be a table scan

quote:
can u tell me how to assign a criteria to use my indexes ?

Realistic queries, ones that retrieve only some rows, ones that filter (where clause), ones that join to other tables.

--
Gail Shaw
SQL Server MVP

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-01 : 15:58:31

I use this query exactly in my app :
select * from docs where CategoryID in (1,2,3,4,5,6,7,8)

Now, i create index on categoryID column and use this query, but still use PK scan and don't use my index :
select DocID,DocCode,CategoryID from docs where CategoryID in (1,2,3,4,5,6,7,8)

Can u tell me more information how to optimize my first query ?
thanks i advance

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-07-01 : 16:46:05

Index recommendations is based on queries. With your query (SELECT * FROM Table), table scan is the best, so no recommendation. If your query is : SELECT * FROM Table where DocID = 3, it may recommend you to create a clusted index on DocId (if the index is not there).

Now, i create index on categoryID column and use this query, but still use PK scan and don't use my index :
select DocID,DocCode,CategoryID from docs where CategoryID in (1,2,3,4,5,6,7,8)

First, this query return the same result but better performance:
select DocID,DocCode,CategoryID from docs where CategoryID > 0 and CategoryID < 9

Second, if there are too many rows in that range (CategoryID in (1,2,3,4,5,6,7,8)), the engine may use index scan, not index seek.

In general, for index seek, you should put the index of unique column in the where clause of your query. Of course, this depends on your application and db design.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-01 : 17:01:44

quote:
Originally posted by hdv212

Now, i create index on categoryID column and use this query, but still use PK scan and don't use my index :
select DocID,DocCode,CategoryID from docs where CategoryID in (1,2,3,4,5,6,7,8)

What index do you have?
How many rows in that table? How many rows does that query return? How many different categories are there?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-01 : 17:02:53

quote:
Originally posted by namman

Second, if there are too many rows in that range (CategoryID in (1,2,3,4,5,6,7,8)), the engine may use index scan, not index seek.

If there are too many rows SQL won't use the nonclustered index at all, it'll do a table/clustered index scan.

--
Gail Shaw
SQL Server MVP

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-01 : 17:16:50

Thanks
You mean if create a composite index for my query and set unique, it make better performance ?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-01 : 17:57:27

Maybe. But only if the index supports the query. If it requires too many columns to make unique, it's a waste of space and resources.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources