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 RepliesHey 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
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
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.
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
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
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 RelatedI 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 RelatedI 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....
Thanks
Ananth
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.
I have two tables:
Headers(id int, time datetime)
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
Hello everyone ,
I am looking for an useful sql server 2005 performance tuning book. i have been searching for a real nice book as i m going to start my job from next month in a financial domain with one of the requirement as sql server 2005 performance tuning.so i m looking forward a book which can help me doing well at my workplace. Any suggestions and links appreciated in advance .