Performance tuning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance tuning

Author

Topic

srivathsani
Starting Member

2 Posts

Posted - 2008-12-11 : 07:44:15

We have a .Net Apllication which does the function of retrieving records from the database and displaying it to the user.The database is in SQL Server 2005.The query I need to tune has 5 tables.
The records in these tables range from 5 lakhs to 40 lakhs.The primary key of the table is uniqueidentifier column..The clustered index is also put on the sam ecolumn.Regular insertion of data takes place in this database.The number of row inserted will be around 40 lakhs.
The query I am tuning fetches records in 2 mins to 4 mins.I am supposed to tune it and make the query fetch it within a minute.
Steps i have taken to improve the performance:-
1)Put nonclustered indexes on the columns used in joins,on columns used in where clause,
2)Put effective indexes such that the index seek operation is only carried out.
3)Separated the heavily accessed tables and indexes of that table to a separate filegroup.
4)Modified teh database design.Added a new column.Put a identity key on it.Shifted the clustered index which was on the uniqueidentifier column to the newly added column.Retained the uniqueidentifier column as the primary key of the table but with non clustered index
All the above stated methods did not help me to much extent

Please find below the query

select csp.SpeakerRowGUID
, css.SessionRowGUID
, cut.FilePath
, cut.FileName
, cr.SDNCatNo
, cr.WaveFileRoot
, cut.Transcription
, cut.UtteranceRowGUID
, saf.LeadSilence
, saf.TrailSilence
,cpc.promptcatgname
from css join b csp on css.SpeakerRowGUID = csp.SpeakerRowGUID
join cr on cr.SDNCatNo = css.SDNCatNo
join sal
on sal.SDNCatNo=cr.SDNCatNo and sal.SDNCatNo=css.SDNCAtNo
join cut on css.SessionRowGUID = cut.SessionRowGUID AND css.SDNCatNo = cut.NSDNCatNo and sal.SDNCatNo=cut.NSDNCatNo
join cp on cp.PromptRowGuid = cut.PromptRowGuid
join cpc on cpc.PromptCatgRowGuid = cp.PromptCatgRowGuid
join #promptname pn on pn.promptname=cpc.promptcatgname
join saf on cut.UtteranceRowGUID = saf.UtteranceRowGUID
where cr.Platform = 'Server' and sal.Languagename = 'FRench'
and csp.DataUsagepurpose = 'Training'
order by csp.speakerrowguid,css.sessionrowguid

csp table contains 60000 records
css contains around 50000 records
cr contains around 1 lakh records
cut around 40 lakhs(Will increase)
cp arnd 5 lakhs
cpc arnd 2000 records
saf around 40 lakhs
#promptname is a table containing at the max 100 records

Please help me to tune the query

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-11 : 09:18:14

Have you rebuild indexes?stats?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-17 : 07:55:53

Are you using GUID's as primary keys? Because they are so large indexes based on them are slower. An int identity column would have better performance, a unique meaningful field used in queries would be better again.
RE: moving tables /indexes to new filegroups - to optimise JOINS: place joining tables in different filegroups rather than in same filegroup.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources