Performance Issues And Tuning (Rows Per Batch, MICS, Etc.)
Mar 6, 2008
I built an SSIS package to pull a large amount data (over 4 million records) from our legacy system into a SQL server for analysis. As it stands the package takes somethign like 30+ hours to run. I would really like to trim down the time it is taking. The process runs monthly.
We're connecting to a cache database via an ODBC connection and dumping to an OLEDB connection to the SQL server. Is there something I can change in the rows per batch, maximum commit size to improve performance. It looks like the ODBC connection returns chunks of about 3000 rows at a time.
Is anyone familiar with cache that might know where I can get the best performing ODBC driver from?
What about the table that I'm dumping the data to? I assume no (or very few) indexes is the way to go for speed of dropping the data into the table.
I don't feel that 30 hours is an acceptable amount of time for this process to run. Am I off base here?
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.
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 ?
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
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
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.
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.
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
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?
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.
I 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.
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?
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)
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
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
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.
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
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!
Below 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!
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
I have table A and B. A has column ID,A1,A2,A3,A4,A5 columns. B has ID,B1,B2, A1 columns. A table has a trigger. I defined the below trigger.
Solution 1 ALTER TRIGGER [dbo].[Tri_A] ON [dbo].[A] for UPDATE AS BEGIN UPDATE B SET B.A1= i.A1 FROM inserted i INNER JOIN B ON B.ID = i.ID END; GO
If I change the above solution 1 trigger to solution 2.Can I improve the trigger performance dramatically? I mean only A.A1 is changed then update B.A1. So when the other columns is changed, the update will not be required.
Solution 2 ALTER TRIGGER [dbo].[Tri_A] ON [dbo].[A] for UPDATE AS BEGIN IF ( UPDATE (A1) ) UPDATE B SET B.A1= i.A1 FROM inserted i INNER JOIN B ON B.ID = i.ID END; GO
Hello friends, I am in need to tune my stored procedures to get best performance. First I have a doubt in some SQL statement execution plan,
-- assume "Table_Heat" has one Million records. -- assume "Mill" column hold value either 1 or 2.
-- assume "HeatNumber" column is Unique.
1. If I have statement like this, -----SELECT * FROM Table_Heat WHERE HeatNumber = @nHeat AND Mill = @nMill-----
How WHERE clause filter the result from table??? -- In which order the AND statements of WHERE clause will be executed??
2. If I have created Indices for the table Table_Heat
For the above SQL statement - -- If Index is for HeatNumber --Execution Speed?? -- If Index is for HeatNumber, Mill -- Execution Speed?? -- If Index is for Mill, HeatNumber -- Execution Speed??
I know how to work with indices, but creating too much indices and unwanted indices also cause performance inefficiency, isn't it?. so please help me on this....
Hi all, My boss has asked me to research and recommend a product that our developers (not database developers, but front-end developers that *cringe* get into the database to make the occasional stored procedure) can use to determine the relative efficiency of thier code and/or the database structure and keys.
We currently have a few licensed versions of Quest's Coefficient software, which I think is very usable for such things (I do on a regular basis), but he doesn't think that is "friendly" enough for a GUI developer that is not a database expert.
Any thought and/or suggestions for a 3rd party software package that y'all have used or are using that might fit the bill? I don't mind doing the footwork and research, just thought I would "seek the advice of my peers" to get started.
Yeah, I know the design should drive the database, but we are in various stages of "getting there", so any help in the interim is a good thing.
Thanks for the thoughts and waste of brain cells on my behalf.
Data(id int, product_id int, property_id int, value float)
Data.id references Headers.id
Headers.id is a primary key, Data has clustered index (id, product_id, property_id)
Headers has several thousand rows, Data several million. I want to return all rows from Data for a given product_id and a given property_id such that Header.id is in a given range.
Right now I am doing
SELECT id, time, value FROM Headers H, Data D WHERE H.id = D.id AND H.time >= @StartTime AND H.time <= @EndTime AND D.product_id = @ProductID AND d.property_id = @PropertyID
This query can take 10+ seconds to run, though once I run it for a given product_id, queries for different values of property_id are much faster. Try a different product_id, and it takes longer. Given that there are millions of records in Data, is it reasonable for it to take this long? The index was suggested by Query Analyzer's Index Tuning Wizard, and I tried a couple variations on the query without any noticeable performance improvement. But, I'm no DBA...anyone have any tips? I googled a bit but couldn't figure out the right way to phrase my question to find any good info...thanks in advance
