Tuning Methodologies in SQL Server ?

Posted by Sarvesh under Sql Server on 9/28/2009 | Views : 1281 | Status : [Member] | Replies : 7
hi sql experts,

I have faced one interview question, can you please let me now answer ?

"What are the Tuning Methodologies in SQL Server ?"

thanks in advance
sarvesh




Responses

Posted by: Vuyiswamb on: 9/28/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Sarvesh

Go here

http://technet.microsoft.com/en-us/library/cc966413.aspx

Thank you for Posting at Dotnetfunda

Vuyiswa Maseko


Thank you for posting at Dotnetfunda
[Administrator]

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 9/28/2009 [Member] [MVP] Silver

Up
0
Down
Dear Sarvesh

Pl go thru the documents link sent by Mr.Vuyiswamb, Whish is very usefull, it will give you very clear picture.

Simply you can say 6 points

1. Analysing Waits at Instance Level
SELECT WAIT_TYPE,(WAIT_TIME_MS /1000.) 'WAIT TIME In Second(s)' FROM SYS.DM_OS_WAIT_STATS WHERE WAIT_TYPE NOT LIKE '%SLEEP%'
ORDER BY 2 DESC

2. Corelating Waits with Queues
SELECT [Object_Name] 'Object Name',Counter_Name,Instance_Name,Cntr_Value FROM SYS.DM_OS_PERFORMANCE_COUNTERS
ORDER BY Cntr_Value DESC

3. Determine the course of action

4. Drill down to the Database/File Level
SELECT * FROM SYS.DM_IO_VIRTUAL_FILE_STATS(<DataBase_ID>,<File_ID>)

5. Drill down to the Process Level(SQL Objects)

6. Tuning Tables/Indexes

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rtpharry on: 9/29/2009 [Member] [MVP] Bronze

Up
0
Down
Wow! Its conversations like these that really show that I am not an sql expert :)

I have never even heard of waits and queues before...

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 9/29/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
:) lol

Thank you for posting at Dotnetfunda
[Administrator]

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: RtpHarry on: 9/29/2009 [Member] [MVP] Bronze

Up
0
Down
I love living in my linq bubble and not having to deal with these complexities :)

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 9/29/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
lINQ , not even one of my Project uses Linq :)

Thank you for posting at Dotnetfunda
[Administrator]

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rtpharry on: 9/30/2009 [Member] [MVP] Bronze

Up
0
Down
I couldn't live without it, it makes data access hassle free.

Is it because your workplace doesn't use .net 3.5 or because you dont like it?

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response