hi
how should i monitor performjance of stored procedure and sql statements. i want to know that how much cpu time a query or stored procedure is taking??
r there any system table which give these information
please give suggestion ASAP
thanx
Maneesh
I have an SP that is big, huge, 700-800 lines.I am not an expert but I need to figure out every possible way thatI can improve the performance speed of this SP.In the next couple of weeks I will work on preparing SQL statementsthat will create the tables, insert sample record and run the SP.I would hope people will look at my SP and give me any hints on howI can better write the SP.In the meantime, after looking at the SP briefly, my first observations are:1- use SET NOCOUNT ON2- avoid using UNION statements3- use WITH (NOLOCK) with each SELECT statement4- avoid using NESTED Select statements5- use #temp tables6- avoid renaming tables in SELECT statements, for example SELECT * FROMtblClients CAm i correct to consider the above 6 points as valid things in terms ofcausingperformance problems?I would appreciate any comments/helpThank you very much
View 12 Replies View RelatedWe have an application that is based on several extended storedprocedures. When we run our application in house, or when most othercustomers run it, they see performance of about X transactions persecond. One customer is seeing performance of about X/5, and I'mhaving a hard time troubleshooting it. The performace bottleneck hasbeen narrowed to the execution of the extended stored procedures. Doesanyone know of tuneable SQL Server parameters that may specificallyaffect the performance of extended stored procedures. I know theprocedures get run by a scheduler. Is there some way the priority orfrequency of the scheduler can be modified? Thanks for any advice.
View 3 Replies View RelatedHi
Is there any good books for Query Tuning and Stored procedure Tuning
Thanks
HiI've heard 2 things recently, can I confirm if their true/false?(1) If you have a stored procedure and you want to optimise it you cancall exec proc1,you could also use define/set for each of the variables and copy thecode into query analyser,this then makes it easier to tune. However the optimiser worksdifferently for these variables than it does for variables passed intothe query via exec and will produce a less optimalplan(2) There is a different optimiser used in query analyser than thatused otherwise? A colleaguehad a problem where a stored procedure called from dotnet code wasrunning slowly butone run from query analyser via exec, with exactly the same arguments,was running quicklyta
View 1 Replies View Related
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
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
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
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
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.
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
What all possible ways there have been to maximizing performance of database?
View 4 Replies View Relatedhow to increase performance through management studio
except making indexes
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
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
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
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
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
I'm running Database Engine Tuning Advisor over a huge workload table, and getting many errors like the following:
quote:E000
declare @p19 int
set @p19=228
exec MySProc @Cod='T12',@Nro=default,@IdM=727,@Rec=@p19 output
select @p19
1
[Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'MySProc'.
MySProc exists in the database to tune, and I'm not sure if every record that references that SP fails or it is random, because once it also logged S001 messages for the declare, set and select lines of the same call with other parameters.
What's going on?
BTW, I uploaded my trace (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93801) to another 2K5 server where I also restored a backup of the traced database taken a couple of days later. It's strange that DETA requires that the database to tune and the database where the workload table resides must be on the same server!!!
Oh, DETA is still running after 23 hours of analisys
Thanks in advance.
++Vitoco
Hi,
Can some one please send me the Sql Server 2005 Performance Tuning artilce links?
Thanks
I have a problem that is more complex than the one that follows, but if someone can answer this question,
I believe I can take it from there in solving my real problem.
Why does:
create table #bbb (c int)
set nocount on
declare @a int
select @a = 1
while (@a < 5000)
begin
select @a = @a + 1
insert into #bbb values (@a)
end
run in about 2 seconds, while:
create table bbb (c int)
set nocount on
declare @a int
select @a = 1
while (@a < 5000)
begin
select @a = @a + 1
insert into bbb values (@a)
end
runs in about 20 seconds? (The only difference is that one uses a temp. table.)
What could I have configured incorrectly to cause this behavior?
Thanks in advance!
Palmer
Hi !
What are some basic tips and hints for better performance running SQL 7.0 ?
Thanx
Anybody have a suggestion on text books or other materials that deal with performance tuning in SQL 7.0. From what I have seen SQL 7.0 is far diffrent than 6.5. I'm specificaly interestd in information about query plans, profiler and what a table spool does.
View 1 Replies View RelatedI have a query which retrieves data from 4-5 tables. To restrict the acess directly to the tables, views have been created on all these tables. These views are just select * from the tables. Two of these 5 tables have 700 Million and 8 Million rows respectively. And all the tables are having indexes. My issue here is that my query on views take three times more then the duration it takes to retrive data directly from tables. e.g. To retrieve 1 Miliion rows, it takes just 7-10 minutes on tables but on views it takes more than 30 minutes. When I check the query plan for both the options, I can see that indexes are being picked up but still the views are very slow.
Creating indexes on views is not feasible option for me as it requires DDL changes and so much testing efforts.
Hi, I am new to database admin. Actually, I really have no experience what so ever. My boss has asked me to do some tests on our sql server to find bottlenecks or whatever is causing our server to respond so slowly.
Any help would be, ugh helpful.
I am loking for tools or information that will help troubleshoot any problems with the ms sql server 2k. I have tried sql profiler, but I found myself lost, not knowing what to look for.
Thank you
Hello everyone:
Can anyone list the general performance tuning methods? Thanks a lot.
ZYT
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?
Hi,
I have a stored procedure, I really need some one to help me for performance.
This stored procedure will generate dynamic sql and then paging depending on the @orderbystatement
@orderbystatement could be one column or multiple columns.
Join fields have indexes but execution plan still have hash match on Register table because a lot of duplicate records on
the columns of CalendarAcademicYearId and Worker.
The bottleneck is execution for dynamic sql statement with paging functionality. because all statement after set rowcount 0 only has 10 rows.
This performance task took me a few days and I still didn't get a good idea. Any tip will be appreciated.
Thanks.
Code Snippet
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_MyProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_MyProc]
GO
CREATE PROCEDURE [dbo].[usp_MyProc]
@Dob DATETIME = NULL,
@CtrlWorkerNo NVARCHAR(20) = NULL,
@Factory NVARCHAR(MAX) = NULL,
@Position VARCHAR(25) = NULL,
@Gender NVARCHAR(10) = NULL,
@Year uniqueidentifier = NULL,
@FamilyName NVARCHAR(50) = NULL,
@GivenNames NVARCHAR(50) = NULL,
@FirstRow INT = NULL,
@MaxRows INT = NULL,
@IsWorker INT = NULL,
@IsAcStaff INT = NULL,
@Order NVARCHAR(255) = 'FamName , FirstName , DOB',
@RegisterStatus NVARCHAR(255) = NULL,
@PersonGuid VARCHAR(36) = NULL,
@IncludeStarted BIT = 0,
@IncludeHistorical BIT = 0,
@IncludeNoRegisters BIT = 0
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #t
(Id INT Identity(1,1),EntityId uniqueidentifier)
SELECT @Factory = REPLACE(@Factory,',',''',''')
SELECT @RegisterStatus = REPLACE(@RegisterStatus,',',''',''')
DECLARE @RegisterYears INT
SET @RegisterYears = NULL
IF (@Factory IS NOT NULL )
OR (@Position IS NOT NULL)
OR (@Year IS NOT NULL)
OR (@RegisterStatus IS NOT NULL)
OR (@IsWorker = 1)
BEGIN
SELECT @RegisterYears = (SELECT DATEPART(year, getDate()))
DECLARE @RegisterYearsHistorical INT
SET @RegisterYearsHistorical = (SELECT HistoricalYears FROM Organisation)
IF (@IncludeHistorical = 0 AND @RegisterYearsHistorical IS NOT NULL AND @IsWorker = 1)
BEGIN
SET @RegisterYears = @RegisterYears - @RegisterYearsHistorical
END
ELSE IF (@IncludeHistorical = 1 OR @IsWorker IS NULL OR @IsWorker = 0)
BEGIN
SET @RegisterYears = NULL
END
END
DECLARE @OrderByStatement NVARCHAR(255)
SET @OrderByStatement = @Order
DECLARE @FactoryJoin nvarchar(1000)
SET @FactoryJoin = ''
IF (PATINDEX('%School%', @Order) > 0 OR @Factory IS NOT NULL)
BEGIN
SET @FactoryJoin = 'LEFT OUTER JOIN dbo.Factory AS Factory ON Worker.School = Factory.Code '
SET @OrderByStatement = REPLACE(@OrderByStatement, 'School', 'Factory.Code')
END
DECLARE @AddressJoin nvarchar(1000)
SET @AddressJoin = ''
IF (PATINDEX('%Suburb%', @Order) > 0)
BEGIN
SET @AddressJoin = 'LEFT OUTER JOIN dbo.hjEntityAddress EntityAddress ON Worker.EntityId=EntityAddress.EntityId AND(EntityAddress.AddressType=''Local'' ) ' +
'LEFT OUTER JOIN dbo.hjAddress [Address] ON EntityAddress.AddressId=[Address].AddressId '
SET @OrderByStatement = REPLACE(@OrderByStatement, 'Suburb', '[Address].Suburb')
END
DECLARE @MainContactJoin nvarchar(1000)
SET @MainContactJoin = ''
IF (PATINDEX('%MainContact%', @Order) > 0)
BEGIN
IF (PATINDEX('%DESC%', @Order) > 0)
BEGIN
SET @Order = REPLACE(@Order, ',MainContact', ' DESC,MainContact')
SET @OrderByStatement = @Order
END
SET @MainContactJoin = 'LEFT OUTER JOIN dbo.hjRelationship Related ON Person.EntityId = Related.EntityID1 AND Related.MainContact=1 AND Related.Active = 1 ' +
'LEFT OUTER JOIN dbo.hjPerson MainContact ON Related.EntityID2 = MainContact.EntityID '
SET @OrderByStatement = REPLACE(@OrderByStatement, 'MainContact', 'MainContact.')
END
DECLARE @IncNoRegister NVARCHAR(MAX)
SET @IncNoRegister = ''
IF (@IncludeNoRegisters = 0)
BEGIN
SET @IncNoRegister = '
INNER JOIN dbo.Register ON dbo.hjWorker.WorkerNo = dbo.Register.Worker
INNER JOIN dbo.YearTable ON dbo.YearTable.CalendarAcademicYearId = dbo.Register.
CalendarAcademicYearId
WHERE (dbo.Register.Factory IN ('''+ISNULL(@Factory, '')+'''))
AND ((dbo.Register.[CalendarAcademicYearId] = @CalendarAcademicYearId_) OR
(@CalendarAcademicYearId_ IS NULL))
AND ((dbo.Register.Position = @Position_) OR (@Position_ IS NULL))
AND ((DATEPART(yy, dbo.YearTable.StartDate) >= @RegisterYears_) OR (@RegisterYears_ IS
NULL) OR (dbo.YearTable.StartDate IS NULL))
AND ((dbo.Register.RegisterStatus IN ('''+ISNULL(@RegisterStatus, '')+''')) OR (@RegisterStatus_
IS NULL))'
END
ELSE
BEGIN
SET @IncNoRegister = '
LEFT JOIN dbo.Register ON dbo.hjWorker.WorkerNo = dbo.Register.Worker
LEFT JOIN dbo.YearTable ON dbo.YearTable.CalendarAcademicYearId = dbo.Register.
CalendarAcademicYearId
WHERE (dbo.Register.RegisterID IS NULL)
OR
(
((dbo.Register.Factory IN ('''+ISNULL(@Factory, '')+''')) OR (@Factory_ IS NULL) )
AND ((dbo.Register.[CalendarAcademicYearId] = @CalendarAcademicYearId_) OR
(@CalendarAcademicYearId_ IS NULL))
AND ((dbo.Register.Position = @Position_) OR (@Position_ IS NULL))
AND ((DATEPART(yy, dbo.YearTable.StartDate) >= @RegisterYears_) OR (@RegisterYears_ IS
NULL) OR (dbo.YearTable.StartDate IS NULL))
AND ((dbo.Register.RegisterStatus IN ('''+ISNULL(@RegisterStatus, '')+''')) OR (@RegisterStatus_
IS NULL))
)
'
END
DECLARE @StartedClause NVARCHAR(100)
SELECT @StartedClause = ''
IF @IncludeStarted = 0
SELECT @StartedClause = ' (Person.StartedDate IS NULL ) AND '
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = N'
set rowcount ' + convert(varchar,@FirstRow + @MaxRows -1) + '
INSERT INTO #t
SELECT Entity.EntityId
FROM dbo.hjEntity Entity
INNER JOIN dbo.hjPerson Person ON Entity.EntityId=Person.EntityId
LEFT OUTER JOIN dbo.hjWorker Worker ON Worker.EntityId=Entity.EntityId '
+ @FactoryJoin
+ @MainContactJoin
+ @AddressJoin
+ '
WHERE Entity.EntityID IN
(
SELECT dbo.hjEntity.EntityID
FROM dbo.hjEntity
LEFT OUTER JOIN dbo.hjWorker ON dbo.hjWorker.EntityId=dbo.hjEntity.EntityId
' + @IncNoRegister + '
)
AND
(' + @StartedClause + '((Person.IsWorker=@IsWorker_ )OR (@IsWorker_ IS NULL))
AND((Person.IsAcStaff=@IsAcStaff_) OR (@IsAcStaff_ IS NULL))
AND((Person.FamName like @FamilyName_) OR (@FamilyName_ IS NULL) )
AND((Person.FirstName like @GivenNames_) OR (Person.PrefName LIKE @GivenNames_) OR
(Person.SecName LIKE @GivenNames_) OR (@GivenNames_ IS NULL) )
AND((Person.DOB LIKE @Dob_) OR (@Dob_ IS NULL) )
AND((Worker.CtrlWorkerNo like @CtrlWorkerNo_) OR (@CtrlWorkerNo_ IS NULL) )
AND((Person.Gender LIKE @Gender_) OR (@Gender_ IS NULL) )
AND((Person.EntityId != @PersonGuid_) OR (@PersonGuid_ IS NULL) )
) ORDER BY ' + @OrderByStatement
print @sql
EXEC sp_executesql @sql,N'@Factory_ NVARCHAR(10),@CalendarAcademicYearId_ uniqueidentifier,@RegisterStatus_ VARCHAR(50),@IsWorker_ INT,@IsAcStaff_ INT,@FamilyName_ NVARCHAR(50),@GivenNames_ NVARCHAR(50),@Dob_ DATETIME,@CtrlWorkerNo_ NVARCHAR(20),@Gender_ NVARCHAR(10),@PersonGuid_ VARCHAR(36),@FirstRow_ INT, @MaxRows_ INT, @RegisterYears_ INT, @Position_ VARCHAR(25)
',@Dob_=@Dob,@CtrlWorkerNo_=@CtrlWorkerNo,@Factory_=@Factory,@Gender_=@Gender,@CalendarAcademicYearId_=@Year,@RegisterStatus_=@RegisterStatus,@FamilyName_=@FamilyName,@GivenNames_=@GivenNames,@IsWorker_=@IsWorker,@IsAcStaff_=@IsAcStaff,@PersonGuid_=@PersonGuid,@FirstRow_=@FirstRow,@MaxRows_=@MaxRows,@RegisterYears_=@RegisterYears,@Position_=@Position
set rowcount 0
SELECT
Entity.EntityId,
Person.Title,
Person.FamName,
Person.FirstName,
Person.SecName,
Person.PrefName,
Person.DOB,
Person.Gender,
Person.EmploymentType,
Person.HighestSchoolLevel,
Person.HighestQualificationLevel,
Worker.School,
Factory.Name as SchoolName,
Worker.CtrlWorkerNo,
[Address].StreetNumber,
[Address].Street1,
[Address].Street2,
[Address].Suburb,
[Address].City,
[Address].State,
[Address].Postcode,
[Address].Country,
RelPerson.FamName as MainContactFamName,
RelPerson.FirstName as MainContactFirstName,
RelPerson.SecName as MainContactSecName,
RelPerson.PrefName as MainContactPrefName,
RelPerson.Title as MainContactTitle,
(SELECT COUNT(dbo.hjContactMethod.ContactMethodID) FROM dbo.hjContactMethod WHERE Worker.EntityId=dbo.hjContactMethod.EntityId AND(dbo.hjContactMethod.Priority='1' )) as ContactMethods,
Person.StartedDate
FROM dbo.hjEntity Entity
INNER JOIN #t t on Entity.EntityId = t.EntityId
INNER JOIN dbo.hjPerson Person ON Entity.EntityId=Person.EntityId
LEFT OUTER JOIN dbo.hjWorker Worker ON Worker.EntityId=Entity.EntityId
LEFT OUTER JOIN dbo.hjRelationship Relationship ON Worker.EntityId=Relationship.EntityId1 AND(Relationship.MainContact=1 )
LEFT OUTER JOIN dbo.hjEntity RelEntity ON Relationship.EntityId2=RelEntity.EntityId
LEFT OUTER JOIN dbo.hjPerson RelPerson ON RelEntity.EntityId=RelPerson.EntityId
LEFT OUTER JOIN dbo.hjEntityAddress EntityAddress ON Entity.EntityId=EntityAddress.EntityId AND(EntityAddress.AddressType='Local' )
LEFT OUTER JOIN dbo.hjAddress [Address] ON EntityAddress.AddressId=[Address].AddressId
LEFT OUTER JOIN dbo.Factory ON Factory.Code = Worker.School
WHERE (t.Id BETWEEN @FirstRow AND (@FirstRow + @MaxRows -1))
Order by t.Id
END
Which is the best third party tool for SQL Server 2005 Performance Tuning/Optimization.
We purchased the Toad for SQL Server from Quest sofwater but it works only with dbo schemas....So if ur users were in schema xyz then it would not recognize it because they develop the tool for 2005 based on SQL Server 2000 where the schema ownesrhip is tied to the users.
I have built a solution which runs for two hours on a server with 4CPU 2GHz each and 2GB of RAM on windows 2000 server (CPU utilization almost 70% and almost out of RAM). I moved the two source databases and the solution to a new box runing 8 xeon's at 3GHz each and 16GB of RAM running widows 2003 server 32bit and it still runs for 2 hours (CPU utilization 10% and ample RAM left).
I was expecting it to run much faster. So I started exploring the performance tuning features in SSIS and started tweaking the following:
Control Flow:
DefaultBufferMaxRows: Based on row size and buffer size, calculated the max rows.
DefaultBufferSize: Set this to max 100MB
DataFlow Destination:
Rows Per Batch: Set this to equal to the numbe of rows expected from the source.
Maximum Insert Commit Size: Set this to zero since memory was not an issue
I took the recommendations from other threads on similar issues here including the excellent recommendations at http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx but now the job is running for 6 hours.
Can anyone explain what I am doing wrong here? I have tried each of the above one by one and all together. No matter what combination I try it does not work any faster and both source and destination database are on the same server. Even selects from the same database also slowed down from 10 minutes to one hour.
Any assistance is appreciated, I need to get this job run in an hour.
Thanks!
- Philips.
Hi everybody,
i would be thankful if anybody posts some references of the web sites
which relates to SQL Server Performance tuning.
Thanks in Advance
Suresh Kumar Goudampally
hiFor an unavoidable reason, I have to use row-by-row processing(update) on a temporary table to update a history table every day.I have around 60,000 records in temporary table and about 2 million inthe history table.Could any one please suggest different methods to imporve the runtimeof the query?Would highly appreciate!
View 9 Replies View RelatedBelow is a simple UPDATE that I have to perform on a table that hasabout 2.5 million rows (about 4 million in production) This queryruns for an enourmous amount of time (over 1 hour). Both theChangerRoleID and the ChangerID are indexed (not unique). Is thereany way to performance tune this?Controlling the physical drive of the log file isn't possible at ourclient sites (we don't have control) and the recovery model needs tobe set to "Full".UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHEREChangerRoleID IS NULLAny Help would be greatly appreciated!
View 2 Replies View Related