Performance Tuning Using Index. How To Force The Query Engine To Use It?

Nov 14, 2007

Hello all.
I have the following table

Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);

Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);

It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!

1. "select I_Company from item group by I_Company " - This one takes 19 seconds

2. "select distinct(I_Company) from item" -This one takes 29 secons

When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By


"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.

Is this a bug?

Thanks in advance,
TipoMan

View 4 Replies


ADVERTISEMENT

How To Force The Query Engine To Use An Index With An OR Clause?

Nov 16, 2007

I have the following table:

Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()

);

Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);

Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )


While this one use:

select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )


Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423

Some feedback?
Thanks

View 5 Replies View Related

Index Performance Tuning Help

Oct 22, 2007

I'm using the Database Engine Tuning Advisor to do some performance evaluation on my database. I have one particular table that will potentially have a couple million rows. but this may not occur for a few months to a year from now.

when i run the advisor with 1.5 million rows, it recommends that i add an index across all columns in this table (covering index) for an estimated 91% improvement. when i run it with 1500 rows in it, it recommends that i add an index on 2 key columns for an 8% improvement.

how should i reconcile this? can i have both and what does that do to performance?

View 17 Replies View Related

DB Engine :: Performance Tuning Temp DB Slow INSERT From VIEW

May 16, 2015

I am running A View that INSERTS into #Temp Table - On Only Certain Days the INSERT Speed into #tempDB is so slow.
 
Attached snapshot that shows after one minute so many few records are inserted - and it dosent happen every day somedays its very fast. 

View 3 Replies View Related

DB Engine :: Query Tuning - IN Operator

Aug 26, 2015

I have a the following query which takes long time

DECLARE @ACCOUNTS TABLE(ACCOUNT_ID INT)
INSERT INTO @ACCOUNTS
SELECT ACCOUNT_ID FROM ACCOUNT
WHERE A_DESCR in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG')

I used the following key words: sql server query tuning in operator 

View 2 Replies View Related

Query Tuning And Stored Procedure Tuning

Nov 22, 2000

Hi

Is there any good books for Query Tuning and Stored procedure Tuning

Thanks

View 1 Replies View Related

Database Engine Tuning Advisor

Sep 7, 2007

Hi experts! I would like to ask for some help regarding Database Engine Tuning Advisor. I was trying to create Session Monitor then I choose TABLE as a workload. Then after creating and selecting the corresponding setup then I start the analysis, during the analysis it prompted an error?

Error MSG:
The specified workload(file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, SP:StmtStarting or SP:StmtCompleted for workload trace file or table.

But if I tried to use the Workload FILE instead of the table the session is successful and completed the analysis. My SQL current setup is client only, I was just accessing the server. Pls help me how to fix or do I need to configure something? Badly needed your help experts. Thanks in advance.


Tatas move

View 3 Replies View Related

Database Engine Tuning Recommendations

Feb 11, 2008

Hi,

I'm just testing the database engine tuning advisor.

I created a basic trace file in SQL Server profiler by randomly opening a number of views in the Adventureworks db. I then used the Database Engine Tuning advisor to analyse this.

The tuning advisors recommendation was to drop about 2-3 dozen indexes, which sounds like a really dumb idea.

Is it normal for DET to recommend that any index not referenced by the workload file be dropped?

View 3 Replies View Related

The Best Way To Use The Database Engine Tuning Advisor

Oct 1, 2007

Hello all,I want to use the SQL Server 2005 Tuning Advisor on our database, andI'm hoping someone here can just confirm the steps for me. We have a10GB database that has a number of applications hitting it constantly,all performing many SELECTs, INSERTs and UPDATEs. Are the followingthe steps I need to take?1. Stop all the applications hitting the database.2. Make a backup of the database.3. Start the profiler on the original database.4. Turn on all the applications again.5. Wait for a time before turning off the profiler and saving theprofile.6. Run the tuning wizard against the backup database using the savedprofile.Do I have to do this every time or am I missing something obvious? I'munder the impression I need the backup because our database has a highvolume of INSERTs and UPDATEs so I have to make sure the state of thedatabase matches the state of the profile.As a matter of interest, why is it not possible for the tuning wizardto analyse the database using the *current* activity? Why does it haveto be a 'pre-recorded' workload?Many thanks,Tommy.

View 4 Replies View Related

Database Engine Tuning Advisor Does Not Support SQ

Jan 23, 2008

Hi to all,

I am using SQL 2005, when i am connecting to Database Engine Tuning Advisor i am getting an error messsage like this


Failed to open a new connection. Database Engine Tuning Advisor does not support SQL Express. (DTAClient)

Help me to slove my problem.
Thanks in advance.

Regards,
Jose.P

View 1 Replies View Related

SQL2K5 Database Engine Tuning Advisor

Jan 24, 2006

Hi,I find much regrettable that Database Engine Tuning Advisor be not part ofMS-Express Edition ... A server without such help is not a server.Besides, you've got the tutorials, but not the tool to play with :-(((Regards,JM Blaise

View 1 Replies View Related

Database Engine Tuning Advisor Configuration?

Nov 29, 2007

I was trying to get some indexing recommendations from the Database Engine Tuning Advisor by consuming a trace file with known indexing issues. After several minutes all it did was create a report or two which list existing indexes. Not very useful. I must be doing something wrong.

A message displays that "75% of the consumed workload has syntax errors." I guess it can't take a regular trace (.trc) file?

I am conderned that the trace file I used did not contain what the Advisor needs. Do I need to setup the trace a special way to support the Advisor?

Also, what does "Database for workload analysis" mean? I chose tempdb but there is no Help, and BOL is a maze of small uninformative snippets . . .

Thanks,

Michael

View 3 Replies View Related

Database Engine Tuning Advisor Error

Jul 3, 2006

I have a very irritating error with Database Engine Tuning Advisor. While processing traces from Sql Profiler I keep getting trace log full of syntax error of most of queries - some of them very simple and ALL of them perfectly correct ( I check them separatly in QueryAnaliser ) - program gives recomendtation when I enter queries one by one - in that case the syntax is correct for Database Engine Tuning Advisor, but I just can't imagine why quries given to analysis in the set aren't correct? I dont' want to enter trace query by query (while I have thousends of them) !

It all seem like a Database Engine Tuning Advisor bug, any ideas about this problem?

Joanna

View 1 Replies View Related

Database Engine Tuning Advisor Does Not Support SQL Express.

Jan 23, 2008

Hi to all,

I am using SQl 2005, when i am connecting to Database Engine Tuning Advisor i am getting an error message like this..

Failed to open a new connection. Database Engine Tuning Advisor does not support SQL Express. (DTAClient)

Help me to solve my problem. Thanks in advance.

Regards,
Jose.P

View 7 Replies View Related

INDEX TUNING

Jun 7, 2001

I want to use the Index tuning wizard on some of my tables. Is it OK if I use when people are on the server or to do it during off-pick period. Thanks!!!

View 1 Replies View Related

Index Tuning

Nov 29, 1999

Other than the SQL Server 7.0 Index Tuner wizard (which isn't suggesting anything). Is there a 3rd party Index Tuner piece of software out there? Or is there something special that needs ot be done to get the SQL Index Tuner to work?

View 3 Replies View Related

Index Tuning

Jul 26, 2007

for what purpose we are splitting the non-clustered index into 3 instead of 1

create index si_acct_info_dtl_INDX1 on si_acct_info_dtl(account_code, ctrl_acct_type)
create index si_doc_hdrfk_ci_acct_info_dtl on si_acct_info_dtl(tran_ou, tran_type, tran_no)


whether index rebuit everycolumn when search is given????.if we build the index in one
statement like this:create index si_acct on si_acct_info_dtl(batch_id)
create index si_acct_info_dtl_guid on si_acct_info_dtl(batch_id,account_code, ctrl_acct_type,tran_ou, tran_type, tran_no)

View 4 Replies View Related

Index Tuning

Apr 22, 2008

Hi,

Our reporting tool dynamically creates tables and fields based upon Excel spreadsheets that are imported. To improve query performance, we are attempting to create indices on these tables automatically. The problem we are facing is that the tool allows the user to group the data by a whole range of fields when generating reports, therefore making it hard to decide what indices to create.

So, as an example, we have a table that has 10 VARCHAR(250) fields (the default size for text based fields in the tool), each of which could potentially be in the group by clause, and 15 numeric fields on which calculations will be carried out.

Is there a single index that we can create dynamically that would improve performance for all potential queries that may be generated? Such as an index that contains all the 10 VARCHAR(250) fields, and the numeric values as Included Columns?

If so, what effect does the order of the fields in the index have, ie we have some fields that have a lot of distinct values and some that have only a few. Which ones should appear higher in the list?

Any help would be greatly appreciated.

James

View 5 Replies View Related

How To Force An Index On The View

Jan 21, 2004

I created a view from a table with out any where clause. All the rows from the table will be in the view with some extra info.

The table has a few indexes.

In my stored procedure I am storing two columns from the table ( IdHi , IdLo - primary key ) into a temp table and joining the temp to the view.
Here the query is taking too much time and not using the index. Can I force the primary key index on View?

Thanks!

View 1 Replies View Related

Database Engine Tuning Advisor - E000 SqlServer Errors

Nov 22, 2007

Does anyone know how to DTA to correctly read the output from the Profiler?

I get the error:

TITLE: DTAEngine
------------------------------
50% of consumed workload has syntax errors. Check tuning log for more information.
------------------------------
BUTTONS:
OK
------------------------------


And the log is full of lines like:

E000 SELECT COUNT(*) FROM Pictures WHERE AdRecId = 16329 2 [Microsoft][SQL Native Client][SQL Server]Invalid object name 'Pictures'.

E000 exec GetAd @RecId=15282 8 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'GetAd'.

E000 exec GetAd @RecId=15385 4 [Microsoft][SQL Native Client][SQL Server]SHOWPLAN permission denied in database 'xxx'.



Does anyone know what causes this?

I do have showplan permission on the login I use for tuning.

View 7 Replies View Related

Database Engine Tuning Advisor Gives Non Existent Errors In SQL 2005

Aug 14, 2006

Hello,

I am sure you have heard of Community server - if not you are just using it ;)

I decided to try to optimise the performance of my site, run a trace and then DETA.

And I am getting errors like these:



E000 exec dbo.cs_user_Get @UserName=N'jded',@UserID=0,@IsOnline=0,@LastAction=N'',@SettingsID=1000 122 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_user_Get'.
exec dbo.cs_thread_IsTracked @ThreadID=5969,@UserID=28236,@SettingsID=1000,@IsTracked=@p4 output
select @p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 declare @p4 bit
set @p4=0
exec dbo.cs_thread_IsTracked @ThreadID=414,@UserID=1001,@SettingsID=1000,@IsTracked=@p4 output
select @p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @SettingsID=1000,@PostID=44641 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'.





The "trouble" is that those sprocs do exist and that the site apparently is working fine. But not for DETA. As far as DETA is concerned... 54% of my processing power is used to serve syntax errors!

A couple of hints.
The database was an upgrade from 2000.:
- I changed the compatibility level to 2005 but no luck there. I tried with a brand new database, and the errors keep cropping up.
B. The errors were observed in a kit that comprises of a 32bit IIS and 64bit SQL2005 and thought that it had to do with the connectivity of those two.
- I run the traces in one (32bit) server that hosts both IIS and SQL and I am getting the same errors.


Any help will be greatly appreciated.

Thank you.


View 7 Replies View Related

Performance Tuning

Jul 9, 2001

Hey all,

I am interested in finding out if anyone out there has experience with extremely high-performance SQL Server applications. The I/O needs of my database server are growing very quickly, and I am on the verge of launching a major upgrade project.

We have done all the standard tuning tasks: proper indexing, stored procedure tuning, etc... and are running on good small-server scale hardware ( dual PIII 700s, 1G RAM, but no RAID). The only path I can see to achieving higher performance are:

- lots of RAID, perhaps on a SAN.
- server upgrade, maybe 4 proc? I've been looking at RAIDZONEs and Netfinity's
- data partitioning ( I REALLY want to avoid this if I can! )

What do you do when you need Major Enterprise scale database performance from SQL Server? I've found lots of resources for Oracle and DB2, but I can't find many case studies for serious SQL Server installations.

Help!

-Dave

View 1 Replies View Related

Performance Tuning

Jul 18, 2000

Hi,
does the upgradition SQL Server 6.5 to 7.0 will simply solve some problems which we are facing currently like ODBC errors Insert failed and update failed and also supporting more users ?
We have Access front end to SQL Server backend, so do we need to touch code in front end for optimizations ?

Can any pls guide me on this

Thanks

View 3 Replies View Related

Got The Pb Reg Performance Tuning

Nov 15, 1999

Hi, i am working on sql server 6.5 version.actually this is developed just one year back. but now the system is almost dead(low performance).i think the reasons r database design,networking,hardware etc.is it correct. and how to rectify these errors. i am suggesting that upgradation is the best option.so pl give the suggestions asap.
Thanx
Janreddy

View 1 Replies View Related

Performance Tuning?

Feb 27, 2001

Hi,

Anyone know any articles on Performance Tuning on the web? I'm trying to monitor one of my production boxes and don't know which counters to use.

Thanks!

Joe R.

View 4 Replies View Related

Performance Tuning

Feb 13, 2007

Hi All,

What are some of the things i can do to improve query performance if the querey performance is realatively slow today compared to yesterday's performance:
Here are some of things i looked at:
-updating statistics
-checking the execution plan
-DBCC showcontig

View 5 Replies View Related

Tuning Performance Of DB

May 17, 2007

What all possible ways there have been to maximizing performance of database?

View 4 Replies View Related

Performance Tuning

Aug 23, 2007

how to increase performance through management studio
except making indexes

View 4 Replies View Related

Performance Tuning

Mar 7, 2008

Hi Guys I need someone to assist me in having full understanding of what performance turning is. My Challenge is interpreting the System Monitor Graph of Performance tools.

I needed to know what does the value on vertical axis represent, while there is non on the horizontal axis rather I have Last, Average, Minimum, Maximum and Duration; Please What does all this value stands for, I indeed observe that when I click on any of the counters selected the value changes, therefore kindly assist me so that I can make meaning of this. Apart from all this please assist me with any material that can explain performance tuning to my maximum benefit, thanks in anticipation.

Pls Check below sample of the file is attached

View 1 Replies View Related

Performance Tuning

Jun 11, 2008

Hi All,

I am new to this forum, would like to know about performance tuning methods in sql and which is the best site to read about it?

Thanks

View 5 Replies View Related

Performance Tuning

Mar 13, 2007

Hi experts,
I've run sql profiler with %processor time counter .it showed a large value of 75
so please give me steps to tune the long running query with high cpu utilisation.


thanks in advance

View 20 Replies View Related

Performance Tuning

Aug 23, 2007

i just wanna know how to reduce the performance of the query
can any one pls help me in the gaining the performance of query
SELECT
tblBankruptcyInfo.MasterID,
tblBankruptcyInfo.bk_Case_Number

INTO #ActiveBK

FROM FNFBSDataMart.dbo.tblReferral tblReferral WITH (NOLOCK)
INNER JOIN FNFBSDataMart.dbo.tblBankruptcyInfo tblBankruptcyInfo WITH (NOLOCK)
ON tblReferral.RefID = tblBankruptcyInfo.RefID
AND tblReferral.CloseDate IS NULL
INNER JOIN FNFBSDataMart.dbo.tblSuperClientFile tblSuperClientFile WITH (NOLOCK)
ON tblReferral.ClientFileID = tblSuperClientFile.ClientFileID
AND tblSuperClientFile.SuperClientVendorID IN (1816,125,127,1706,766,1820,137,141,144,145,1593,1808,146,990,1745,149,1215,1854,1867)

GROUP BY
tblBankruptcyInfo.MasterID,
tblBankruptcyInfo.bk_Case_Number

View 6 Replies View Related

Performance Tuning Help Me

Nov 19, 2007

Hi all ,

I am doing performance tuning in sql

I have a query which it gives result in 70000 rows and time is taken 7 mints .

But i want one query is not more than 50000 row
my query is :-


SELECT
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_TRANSACTION_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.ORDER_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SHIP_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD,
QPDMADM.LAR_OMEGA_TRANS_SUM.DISCOUNT_AGREEMENT_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.PRODUCT_ID_07,
QPDMADM.LAR_OMEGA_TRANS_SUM.CUSTOMER_ID_14,
QPDMADM.LAR_OMEGA_TRANS_SUM.ISO_COUNTRY_CODE2,
QPDMADM.LAR_OMEGA_TRANS_SUM.ACCOUNT_TYPE_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.END_USER_CUSTOMER_ID,
sum(QPDMADM.LAR_OMEGA_TRANS_SUM.NIA_AMT),
QPDMADM.LAR_OMEGA_TRANS_SUM.INDUSTRY_CLUSTER_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_SYSTEM_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.LOAD_DATE
FROM
QPDMADM.LAR_OMEGA_TRANS_SUM
WHERE
(
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD = '200712'
)
GROUP BY
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_TRANSACTION_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.ORDER_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SHIP_DATE,
QPDMADM.LAR_OMEGA_TRANS_SUM.FISCAL_PERIOD,
QPDMADM.LAR_OMEGA_TRANS_SUM.DISCOUNT_AGREEMENT_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.PRODUCT_ID_07,
QPDMADM.LAR_OMEGA_TRANS_SUM.CUSTOMER_ID_14,
QPDMADM.LAR_OMEGA_TRANS_SUM.ISO_COUNTRY_CODE2,
QPDMADM.LAR_OMEGA_TRANS_SUM.ACCOUNT_TYPE_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.END_USER_CUSTOMER_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.INDUSTRY_CLUSTER_CODE,
QPDMADM.LAR_OMEGA_TRANS_SUM.SOURCE_SYSTEM_ID,
QPDMADM.LAR_OMEGA_TRANS_SUM.LOAD_DATE




Note:- FISCAL_PERIOD is index only


Thank & Regards,
Anil

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved