Tuning Question

Feb 14, 2008



On an average system how long should SQL Server take to retrun 300,000 rows from an average table with Select * From? Milliseconds? Seconds? Minutes?

Thanks.

View 16 Replies


ADVERTISEMENT

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

SQL Tuning Help

Feb 21, 2005

Hey,

SQL Server 7.

I have a view which joins 3 tables. One has 15 million rows the next another 5 million and the third 500k.
When I join them the execution plan tells me that 15 million rows were retrieved from the first (taking about 5 mins) 1.5 million from the 2nd taking 3 mins and 4.5 million from the third taking almost no time.

The first two ause a clustered index, one being a seek the other a scan and the third a regular index seek. All followed by a hash match/inner join which takes 2 mins.

Any ideas on optimizing the SQL?

Here is the syntax:
SELECT b.packno, b.COMM_DATE, a.ben_grp_cr, a.ben_dsc_cr, c.gst_inc,
SUM(a.credit)
FROM TABC c INNER JOIN TABB B
ON b.PACKNO = c.PACKNO AND
b.COMM_DATE = BENDTL.COMM_DATE AND
b.BEN_NUM = c.BEN_NUM INNER JOIN
TABA a ON b.tran_id = a.tran_id
WHERE b.tran_date > '20040401' AND c.gst_inc = 0
GROUP BY b.packno, b.COMM_DATE, a.ben_grp_cr, a.ben_dsc_cr,
c.bendtl.gst_inc

View 6 Replies View Related

SQL Tuning

Jun 27, 2006

I tried on finding out the problem in a slow running sp with profiler. I found that there are some waiting resource 'tracewrite' and 'async_network_io'. Any idea on it? Thanks in advance

View 4 Replies View Related

Tuning (Urgent)

Jan 15, 2002

I need to find a way to make this query run faster. Please let me know if you can help. Here are some of the issues.

1) I have a database with one table, and No indexes can be put in place.

2) This is a very large database. (4,000,000) records, and growing daily.

3) the following query returns 720,000 records.

4) The query takes about 18 minutes to complete.


I understand that by doing a table scan, the way this has to run is difficult, but is there anything I can do?

The Problem: When I run this query, I also run performance monitor, I have found that my page file is at 100%. I have 1 Gig of Ram on the server with 2 P III Xeion Processors. I don't know what other information I can give, but please let me know.

Thanks for you assistance

Troy

View 2 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

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

Tuning SQL Server

Jan 25, 2000

Does anyone know of any good reference material on how to tune SQL Server 7?

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

Peformance Tuning

Sep 1, 2000

Good Morning Everybody,
I had a single table consists of one million records. To retrive data from that table it takes lot of time. how i can reduce execution time?
what is the procedures to tune the database. i implemented cluster index on primary key of that table. still i can't able to reduce execution time.
can anybody help me in this issue?

View 2 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

Disksystem Tuning

Jun 13, 2002

Hi !

In the older versions 6.5-7.0 you could adjust max_async_io setting if you had fast controllers and disks that could handle more IO from SQLServer. In 2000 this setting is removed. Are there any settings that I could adjust/tune regarding IO and disk handling ?

Regards Mattias

View 1 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

SQL Server Tuning

Aug 2, 2007

Hello-

Can some one give links to SQL Server tuning and applications performace.

I am looking for some examples which i can use in interviews .


thanks.

View 1 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

Query Tuning

May 28, 2008

Hi,

Mine Below Query takes considerable time at the time of execution.
Can any one help me, what is the other way to write this query?

Declare @p_Mkt_View_Id int
Set @p_Mkt_View_Id = 17


Select Distinct Customer_id From Active_Product_Cust_Dtl
Where Product_Group_Code in
(Select Distinct Product_Group_Code From Products
Where Product_code in (
select Distinct ProductId from pit
where pitid in (select pitid from marketviewdef
where mktviewid = @p_Mkt_View_Id)))

Thanks
Prashant

View 4 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

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

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

SQL Statement Tuning

Jul 23, 2005

hi!!!the following is the sql which is veri slow cos of the 'Not In' clause,would appreciate if u anyone can suggest any other way to bring aboutthe same resultSELECT Id, LOC, AGENCY, BATCHFROM tblRowsWHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCYFROM tblRows AS A, tblRows AS BWHERE A.LOC = "B"AND B.AGENCY = A.AGENCYAND B.BATCH = A.BATCH)tblRowsID LOC AGENCY BATCH1 B 1000 WAD2 R 1000 WAD3 B 1010 QAD4 B 1020 WAD5 R 1020 WAD6 R 1030 RRR7 I 1030 RRR8 V 1030 RRR9 B 1040 UIA10 R 1040 UIA11 I 1040 UIA12 V 1040 UIAthe subquery is to return the rows with LOC = B. the above query as thewhole should return the rows where LOC <> b and also must exclude rowsbelong to the LOC = B subset (that is for example the first two rowswith ID 1 and 2. the first row with ID 1 has LOC as B, Agency as 1000and BATCH as WAD. the second row with Id 2 has LOC as R as the sameAGENCY and BATCH as first row with ID 1 so is the subset of first row.similarly row with Id's 4 and 5.the above query must return the following the rows (that is we can saythe orphan rows which doesn't have LOC AS B nor belongs to the B'ssubset6 R 1030 RRR7 I 1030 RRR8 V 1030 RRRhope i am clear in my explanation and would appreciate if someone canpoint me in the right direction. the reason for posting this in msaccess group is because this is going to be a query in MS Access.regardsbala

View 7 Replies View Related

Tuning API CURSORS

Nov 23, 2005

Hi Guru,My company, every thing we need is to buy from a vendor. However, wehave PEOPLESOFT CRM app that has around 6000 tables and around 5000views and none stored proc. We start seeing the slowness of the app.When I started running a trace to capture some data, and there is nouseful info other than built-in API sp cursors in the following below:sp_cursoropensp_cursorfetchsp_cursorsp_cursorcloseWhat is the best way to capture SQL statements when I see very highREADS? If I tell the peoplesoft prorammer guy to capture SQL statementsfrom application, will he be able to do it?I just need the SQL statements to look at execution plan before I throwout some useful indexes.Please help!Thanks so much,Silaphet,

View 1 Replies View Related

Tuning A Query

Oct 25, 2007

Hi have this query which is taking too much time to execute..........I have tried the following options but not useful till now
1. NOLOCK
2. SET NO ROW COUNT
3. CHANGED DISK LOCATION OF TEMPDB
4. CHECKED %Processor Time
5. Checked pages.sec

Below is the query, any suggestions will be really helpful


SE IFRepository
--Query
--Returns count of txns whose status is not (10001 or 10002)
declare @fileruntimeuid int
declare @Pendingackcount int
set @Pendingackcount =0
set @fileruntimeuid =0
declare @clientname varchar(256)
set @clientname = NULL
declare @txncnt int
set @txncnt = 0
declare @FileNameClient varchar(256)
set @FileNameClient = NULL


declare @StageStatus int
set @StageStatus = 0
declare @StageDesc varchar(35)

declare PendingAcks cursor for
select distinct fileruntimeuid from tiffileruntime WITH (NOLOCK)
where filecreationdt >= convert(smalldatetime,'9-11-07')
and filecreationdt <= convert(smalldatetime,'9-12-07')
and statusid <> 2
--and filetypeuid in (1,8,16,17,18)
--and clientuid =1205
order by fileruntimeuid
--244873, 244883, 244885, 244892, 244893, 244925, 244926, 244966, 244967, 244873, 244883

Print 'File Life Cycle Viewer via Database'
Print '==========================================================================================='
Print 'Status FileRuntimeUID Client Name File Status File name'
Print '==========================================================================================='
Open PendingAcks
FETCH NEXT FROM PendingAcks
into @fileruntimeuid
WHILE @@FETCH_STATUS = 0
BEGIN
select top 1 @StageDesc = b.IFComponentDesc from TIFComponent b, TIFFIleProcessingStatus a
WITH (NOLOCK)
where a.IFComponentUID = b.IFComponentuid
and a.fileruntimeuid = @fileruntimeuid
order by a.FPROCStageStartDt desc-- a.IFComponentUID desc

select @clientname = ClientShortName from tifclientattrib
where clientuid = (select clientuid from tiffileruntime where fileruntimeuid = @fileruntimeuid)

select @StageStatus = statusid, @FileNameClient= FileNameClient from tiffileruntime where fileruntimeuid = @fileruntimeuid
select @txncnt = FProcTxnProcessedInTotal from tiffileprocessingstatus where fileruntimeuid = @fileruntimeuid and IFComponentUID = 5
--if @StageDesc = "" Begin @StageDesc = "------------" End
print RTRIM(convert(varchar(10),@StageStatus)) + ' ' + RTRIM(convert(varchar(10),@fileruntimeuid)) + ' ' + RTRIM(@clientname) + ' ' + @StageDesc + ' ' + RTRIM(@FileNameClient)
set @StageDesc = NULL
FETCH NEXT FROM PendingAcks
into @fileruntimeuid
END

Print '==========================================================================================='
close PendingAcks
deallocate PendingAcks

View 5 Replies View Related

Cursor Tuning Help!

Aug 2, 2007

This cursor is processing one row per second, all it is doing is appending a value to a column. There are 847,000 rows and I can't leave the query to run for 10 days! help!

CREATE procedure [dbo].sp_USR_TEMP_SESSIONAL_ATTENDANCE_APPENDasbegin DECLARE @I_UPN varchar(13) DECLARE @I_SURNAME varchar(50) DECLARE @I_FORENAME varchar(50) DECLARE @I_DOB datetime DECLARE @I_GENDER varchar(1) DECLARE @I_LEA varchar(3) DECLARE @I_DFES varchar(4) DECLARE @I_ATTEND_YEAR varchar(4) DECLARE @I_WEEK_BEGINNING datetime DECLARE @I_ATTEND_CODES varchar(14)declare @cnt intset nocount ondeclare cur CURSORfor select upn, surname, forename, dob, gender, '353' as lea, dfes, attend_year, week_beginning, attend_codes from tmpATTEND_IMPORTfor read onlyopen curfetch from cur into @I_UPN, @I_SURNAME, @I_FORENAME, @I_DOB, @I_GENDER, @I_LEA, @I_DFES, @I_ATTEND_YEAR, @I_WEEK_BEGINNING, @I_ATTEND_CODEStruncate table tmpATTEND_IMPORT_APPENDEDwhile @@fetch_status = 0 begin set @cnt = (select count(*) as cnt from tmpATTEND_IMPORT_APPENDED where upn = @I_UPN and surname = @I_SURNAME and forename = @I_FORENAME and dob = @I_DOB and gender = @I_GENDER and lea = @I_LEA and dfes = @I_DFES and attend_year = @I_ATTEND_YEAR)if @cnt = 0 insert tmpATTEND_IMPORT_APPENDED( upn, surname, forename, dob, gender, lea, dfes, attend_year, week_beginning, attend_codes) values ( @I_UPN, @I_SURNAME, @I_FORENAME, @I_DOB, @I_GENDER, @I_LEA, @I_DFES, @I_ATTEND_YEAR, @I_WEEK_BEGINNING, @I_ATTEND_CODES)else update tmpATTEND_IMPORT_APPENDED set attend_codes = attend_codes+@I_ATTEND_CODES where upn = @I_UPN and surname = @I_SURNAME and forename = @I_FORENAME and dob = @I_DOB and gender = @I_GENDER and lea = @I_LEA and dfes = @I_DFES and attend_year = @I_ATTEND_YEARfetch next from cur into @I_UPN, @I_SURNAME, @I_FORENAME, @I_DOB, @I_GENDER, @I_LEA, @I_DFES, @I_ATTEND_YEAR, @I_WEEK_BEGINNING, @I_ATTEND_CODESendclose curdeallocate curendGO

View 1 Replies View Related

Query Tuning

May 22, 2006

I want to take the execution plan of some transact sql queries, I took the execution plan as text based one, that having

index scan, index seek

Remote scan, remote Update

sort order by cluases

 

in the above clauses what is the high performance, and how will i change to the high performance clauses by changin the query to improve the execution speed of the query

 

Please guide me

 

 

 

the execution plan is

 

 

 



SELECT [Inventory_Profile].[InventoryID]        ,[Inventory_Profile].[Alias]        ,[Inventory_Profile].[InventoryStatusID]        ,[Inventory_Profile].[InventorySubTypeID]        ,[Inventory_Profile].[InventoryTypeID]        ,[Inventory_Profile].[AcquisitionDate]        ,[Inventory_Profile].[UnitNumber]        ,[Inventory_Profile].[YearOfManufacture]        ,[Inventory_Profile].[Manufacturer]        ,[Inventory_Profile].[Make]        ,[Inventory_Profile].[Model]        ,[Inventory_Profile].[SerialNumber]        ,[Inventory_Profile].[UsageConditionID]        ,[Inventory_Profile].[Description1]        ,[Inventory_Profile].[Description2]        ,[Inventory_Profile].[LocationEffectiveFromDate]        ,[Inventory_Profile].[IsFlaggedForSale]        ,[Inventory_Profile].[RentalPurchaseOrderNumber]        ,[Inventory_Profile].[AquisitionPurchaseOrderNumber]        ,[Inventory_Profile].[SortOrder]        ,[Inventory_Profile].[IsSaleLeaseBack]        ,[Inventory_Profile].[InterimRentReceivableUpfrontTaxModeID]        ,[Inventory_Profile].[LeaseRentalReceivableUpfrontTaxModeID]        ,[Inventory_Profile].[OverTermReceivableUpfrontTaxModeID]          ,[TaxDepreciation_Inventory].[IsTaxDepreciationRequired]        ,[TaxDepreciation_Inventory].[IsComputationPending]        ,[TaxDepreciation_Inventory].[TaxDepreciationTemplateID]        ,[TaxDepreciation_Inventory].[InventoryCostBasisAmount]        ,[TaxDepreciation_Inventory].[DepreciationBeginDate]        ,[TaxDepreciation_Inventory].[DepreciationEndDate]     ,[TaxDepreciation_Inventory].[IsTaxDepreciationTerminated]        ,[TaxDepreciation_Inventory].[IsStraightLineMethodUsed]        ,[TaxDepreciation_Inventory].[IsLeaseTermUsedForStraightLineMethod]           ,[Inventory_PTMS].[Division]        ,[Inventory_PTMS].[Branch]        ,[Inventory_PTMS].[SalesTaxPercent]        ,[Inventory_PTMS].[SalesTaxAmount]        ,[Inventory_PTMS].[IsSalesTaxIncluded]        ,[Inventory_PTMS].[GLExpenseAccount]        ,[Inventory_PTMS].[GLAssetAccount]        ,[Inventory_PTMS].[SoftwareExclusionAmount]        ,[Inventory_PTMS].[AssetCategoryCodeID]        ,[Inventory_PTMS].[OwnershipCodeID]        ,[Inventory_PTMS].[ManufacturingCodeID]        ,[Inventory_PTMS].[ReimburseCodeID]        ,[Inventory_PTMS].[BillingStatusID]        ,[Inventory_PTMS].[PropertyTaxExemptionCodeID]        ,[Inventory_PTMS].[UserDefinedField1]        ,[Inventory_PTMS].[UserDefinedField2]        ,[Inventory_PTMS].[Notes]      FROM [Inventory_Profile]    INNER JOIN [TaxDepreciation_Inventory]  ON  [Inventory_Profile].[InventoryID]=[TaxDepreciation_Inventory].[InventoryID]    INNER JOIN [Inventory_PTMS]    ON    [Inventory_Profile].[InventoryID]=[Inventory_PTMS].[InventoryID]    INNER JOIN [Inventory_Status_CnfgLocale]     ON  [Inventory_Profile].[InventoryStatusID] in (SELECT InventoryStatusID FROM  Inventory_Status_CnfgLocale               WHERE InventoryStatusName <> 'Donated'               and   InventoryStatusName <> 'Scrap'                and   InventoryStatusName <>'Write Off'               and   InventoryStatusName <> 'Sold')

 

 

 

 

 

  |--Nested Loops(Inner Join)
       |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
       |    |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]))
       |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
       |    |    |    |    |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_PTMS].[PK_IInvPTMS]))
       |    |    |    |    |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Profile].[PK_IInventory_InvID]), SEEK:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
       |    |    |    |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]), SEEK:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusID]=[LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]),  WHERE:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Donated' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Scrap' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Sold' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Write Off') ORDERED FORWARD)
       |    |    |--Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[UK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
       |    |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[PK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]=[LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]) LOOKUP ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]))

 

View 6 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

Sql Server Performance Tuning

Feb 7, 2008

Hi,
   Can some one please send me the Sql Server 2005 Performance Tuning artilce links?
Thanks

View 1 Replies View Related

Index Tuning Errors

Jul 28, 2000

Eveytime I feed a query into the Index Tuner
I get the usefull message Error analyzing query (60)

What is it and how do I get round it

View 2 Replies View Related







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