Hi All,
Can anyone tell me if it makes any difference in Database Tuning advisor when is used against stored procedures workload and simple batch statements workload.
I used it against stored procedures first and got no recommendations for it, and when I used the statements of same stored procedures outside the procedure and used the worload against the DTA, I got some recommendations.
Also, in the reports section of DTA, the Database Access Report shows no database details, whereas in the other case, I got all the details for database, tables and columns accessed.
When you use DTA with a given workload of SQL queries and updates, DTA recommends an appropriate physical design, and generates a script to implement the recommended physical design.
There is no direct connection between VSTS for DB Pro and DTA. After you use DTA you can change your DB project and DB objects as the DTA recommended.
You can read great article about DTA and get more information from: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQL2005DTA.doc
Maor
|||DTA does not get inside my database for the stored procedures where there's a 'if' condition inside the SP.
For example,
I created a stored procedure as,
createproc testSP2
@.Group_ID_id int
as
begin
select * from PATIENTS.PATIENT_FAMILY_DETAILS where Patient_id in (select Patient_id from
PATIENTS.PATIENT_INFO where Group_ID=@.GroupID)
end
GO
exec testSP2
GO
The Database, Table, column Access reports shows the details of database,tables and columns accessed in the DTA and a set of recommendations are recieved.
But when the same procedure is modifies as,
create proc testSP2
@.Group_id int
as
begin
if(1=1)
begin
select * from PATIENTS.PATIENT_FAMILY_DETAILS where Patient_id in (select Patient_id from
PATIENTS.PATIENT_INFO where Group_ID=@.GroupID)
end
end
GO
exec testSP2
GO
The Database, Table, column Access reports don't give any details and no recommendations are made.
