Solved

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

Posted on 2011-02-18
6
341 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:shanj
[X]
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
  • 3
  • 2
6 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 34929808
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
 
LVL 40

Expert Comment

by:lcohan
ID: 34929883
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
 

Author Comment

by:shanj
ID: 34929965
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 15

Expert Comment

by:derekkromm
ID: 34929996
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
 

Accepted Solution

by:
shanj earned 0 total points
ID: 34930017
Thanks,
  on monday i will follow your advice, u will hear from me.
have a nice weekend.

//shan
0
 

Author Closing Comment

by:shanj
ID: 35045541
Okay, but did not work for me
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 69
Stored Proc - Rewrite 42 73
SQL Recursion 6 34
SQL - Curser to do an insert based on a select 2 26
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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