how do i optimize indexes on tables in a DataMart sql database

Hei!
      We use an ETL sofware from (SAP) and we use Buisness Objects (reporting software)
Our DM (DataMart) is giving poor respons ( i am told).

1. How do i get hold of the Queries (sql statments) from the ETL software and BO
 (Reporting Software) which runs on the DM. ( 2 tables i am told)
2. Once i get hold of the SQL than i can optimise the indexes.(which i need help too?)

Shan
shanjAsked:
Who is Participating?
 
shanjConnect With a Mentor Author Commented:
Thanks,
  on monday i will follow your advice, u will hear from me.
have a nice weekend.

//shan
0
 
derekkrommCommented:
You can run SQL Profiler (from SSMS, go to Tools, Profile) and run a trace to determine what the exact queries are that are running.

Once you have the queries, you can run the Query Optimization Wizard, supply the queries from step 1, and it will suggest (and create if you like) some indexes and statistics to help improve performance.

That will be the quickest way to go about it.
0
 
lcohanDatabase AnalystCommented:
I suggest use SQL Performance Dashborad reports that are free from Microsoft and provide excellent Missing Indexes report. Please see more at:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26822031.html
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
shanjAuthor Commented:
hello Derekkromm!
  thanks for the tip,but i do not know when these queries are running?
it is an 24 hour system, users are spread around europe.

does sql store queries on tables ?or is their a way to tell sql that he has to store the queris for analysing?

//shan
0
 
derekkrommCommented:
Within SQL Profiler you can run a trace on the entire database and filter for specific users, etc. So if there are specific users having issues, you can limit to those to keep from going through too much data.

You can also have it store the results in SQL or File.

Queries aren't "stored" in SQL. The SQL being run could originate from any number of places for a given report, including a stored procedure, view, or the SQL could be embedded into the report. The best way to determine the specific queries to investigate is to run the profiler trace and compare the results to the times when the users complain of poor performance.
0
 
shanjAuthor Commented:
Okay, but did not work for me
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.