Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Index Tuning Wizard Problem

Posted on 2006-07-01
4
386 Views
Last Modified: 2008-02-01
I am trying to use Index Tuning Wizard for one of my Database, when i give the Wizard SQL Script i want database to check. It gives me an error as following:-

"The workload does not contain any events or queries that can be tuned against current database. Check to see the trace contains valid events or SQL script contains valid queries and you are tuning against the right database."

My SQL Query is as following:-

SELECT FZE_MATRIX_PL_QTY.OR03001,
FZE_MATRIX_PL_QTY.QTY,
FZE_MATRIX_PL_QTY.QTY/isnull(Factor,1) as Picks,
FZE_MATRIX_PL_QTY.OR03005,
FZE_MATRIX_PL_QTY.OR03002,
FZE_MATRIX_PL_QTY.OURSTATUS,
FZE_MATRIX_PL_QTY.LineValue,
case when year(FirstConfDelDate) =1900 then getdate()+3650 else FirstConfDelDate end as FirstConfDelDate ,
FZE_MATRIX_PL_QTY.SCALAPLNO,
FZE_MATRIX_PL_QTY.ADDWHEN,
FZE_MATRIX_PL_QTY.Priority,
FZE_MATRIX_PL_QTY.Country,
EXCEEDFlag,
WhenToShip,
NbOfLines,
SCALA_Status,
EXCEEDLineStatus,
EXCEEDSO,
EXCEEDOrderStatus
FROM
scalaDB.dbo.FZE_MATRIX_PL_QTY FZE_MATRIX_PL_QTY
left outer join FZE_QS_2005_BASE_TABLE on FZE_MATRIX_PL_QTY.OR03001=FZE_QS_2005_BASE_TABLE.SONumber
AND FZE_MATRIX_PL_QTY.OR03002 = FZE_QS_2005_BASE_TABLE.LineNumber
left outer join FZE_SO_TO_SHIP_TODAY on
FZE_SO_TO_SHIP_TODAY .SONumber =FZE_MATRIX_PL_QTY.OR03001
inner join SC010100 on SC01001=OR03005
left outer join UNITS on SC01035=UnitCode
left outer join FZE_PACKING_STATUS on OR03001=SO COLLATE SQL_Latin1_General_CP437_BIN and OR03002=ExternLineNo COLLATE SQL_Latin1_General_CP437_BIN and
FZE_MATRIX_PL_QTY.SCALAPLNO = SCALAPL
COLLATE SQL_Latin1_General_CP437_BIN
where ((FZE_MATRIX_PL_QTY.OURSTATUS='O') OR (FZE_MATRIX_PL_QTY.OURSTATUS='P')
) and FZE_MATRIX_PL_QTY .FLAG='O'



0
Comment
Question by:dduser
  • 3
4 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17025997
you have to change the query to ANSI format Inorder to use index tuning wizard.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17026016
take out COLLATE SQL_Latin1_General_CP437_BIN  from the above and then try
also i think you are running the query across different databases scalaDB.dbo should be taken out.
0
 
LVL 9

Author Comment

by:dduser
ID: 17030848
Hi Imran,

I have removed Collate stuff from my query and also removed scalaDB.dbo. Still this doesn't work can you please help me out why is this not working.

dduser
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 500 total points
ID: 17035276
Even if you remove scalaDB.dbo the FZE_MATRIX_PL_QTY FZE_MATRIX_PL_QTY  table wont be there in current database so it wont work. any how let me suggest indexes for the above query

on FirstConfDelDate column in which ever tabel it belongs

should have index on the follwoing column in table FZE_MATRIX_PL_QTY
1.OURSTATUS
2.FLAG
3.OR03001
4.OR03002

should have index on the follwoing column in table FZE_QS_2005_BASE_TABLE
1.SONumber
2.LineNumber

FZE_SO_TO_SHIP_TODAY
1.SONumber

UNITS
1.unitcode

FZE_PACKING_STATUS
1.SO
2.ExternLineNo
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question